Mysql索引的最左前綴原則是什么

這篇文章主要介紹了Mysql索引的最左前綴原則是什么的相關(guān)知識(shí)，內(nèi)容詳細(xì)易懂，操作簡(jiǎn)單快捷，具有一定借鑒價(jià)值，相信大家閱讀完這篇Mysql索引的最左前綴原則是什么文章都會(huì)有所收獲，下面我們一起來(lái)看看吧。

前言

之所以有這個(gè)最左前綴索引

歸根結(jié)底是mysql的數(shù)據(jù)庫(kù)結(jié)構(gòu) B+樹(shù)

在實(shí)際問(wèn)題中 比如

索引index (a,b,c)有三個(gè)字段，

使用查詢(xún)語(yǔ)句select * from table where c = '1' ，sql語(yǔ)句不會(huì)走index索引的

select * from table where b =‘1’ and c ='2' 這個(gè)語(yǔ)句也不會(huì)走index索引

1. 定義

最左前綴匹配原則：在MySQL建立聯(lián)合索引時(shí)會(huì)遵守最左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開(kāi)始匹配

為了更好辨別這種情況，通過(guò)建立表格以及索引的情況進(jìn)行分析

2. 全索引順序

建立一張表，建立一個(gè)聯(lián)合索引，如果順序顛倒，其實(shí)還是可以識(shí)別的，但是一定要有它的全部部分

建立表

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年齡',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'職位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入職時(shí)間'
)CHARSET utf8 COMMENT'員工記錄表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

建立索引ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

索引的順序位name-age-pos

顯示其索引有沒(méi)有show index from staffs;

通過(guò)顛倒其左右順序，其執(zhí)行都是一樣的

主要的語(yǔ)句是這三句

1. explain select *from staffs where name='z3'and age=22 and pos='manager';

2. explain select *from staffs where pos='manager' and name='z3'and age=22;

3. explain select *from staffs where age=22 and pos='manager' and name='z3';

以上三者的順序顛倒，都使用到了聯(lián)合索引

最主要是因?yàn)镸ySQL中有查詢(xún)優(yōu)化器explain，所以sql語(yǔ)句中字段的順序不需要和聯(lián)合索引定義的字段順序相同，查詢(xún)優(yōu)化器會(huì)判斷糾正這條SQL語(yǔ)句以什么樣的順序執(zhí)行效率高，最后才能生成真正的執(zhí)行計(jì)劃

不論以何種順序都可使用到聯(lián)合索引

3. 部分索引順序

3.1 正序

如果是按照順序（缺胳膊斷腿的），都是一樣的

1. explain select *from staffs where name=‘z3’;

2. explain select *from staffs where name='z3’and age=22;

3. explain select *from staffs where name='z3’and age=22;

其type都是ref類(lèi)型，但是其字段長(zhǎng)度會(huì)有微小變化，也就是它定義的字長(zhǎng)長(zhǎng)度變化而已

3.2 亂序

如果部分索引的順序打亂

1. 只查第一個(gè)索引explain select *from staffs where name='z3';

2. 跳過(guò)中間的索引 explain select *from staffs where name='z3' and pos='manager';

3. 只查最后的索引 explain select *from staffs where pos='manager';

可以發(fā)現(xiàn)正序的時(shí)候

如果缺胳膊少腿，也是按照正常的索引

即使跳過(guò)了中間的索引，也是可以使用到索引去查詢(xún)

但是如果只查最后的索引

type就是all類(lèi)型，直接整個(gè)表的查詢(xún)了（這是因?yàn)闆](méi)有從name一開(kāi)始匹配，直接匹配pos的話(huà)，會(huì)顯示無(wú)序，）

有些時(shí)候type就是index類(lèi)型，這是因?yàn)檫€是可以通過(guò)索引進(jìn)行查詢(xún)

index是對(duì)所有索引樹(shù)進(jìn)行掃描，而all是對(duì)整個(gè)磁盤(pán)的數(shù)據(jù)進(jìn)行全表掃描

4. 模糊索引

類(lèi)似模糊索引就會(huì)使用到like的語(yǔ)句

所以下面的三條語(yǔ)句

如果復(fù)合最左前綴的話(huà)，會(huì)使用到range或者是index的類(lèi)型進(jìn)行索引

1. explain select *from staffs where name like '3%'; 最左前綴索引，類(lèi)型為index或者range

2. explain select *from staffs where name like '%3%'; 類(lèi)型為all，全表查詢(xún)

3. explain select *from staffs where name like '%3%';，類(lèi)型為all，全表查詢(xún)

5. 范圍索引

如果查詢(xún)多個(gè)字段的時(shí)候，出現(xiàn)了中間是范圍的話(huà)，建議刪除該索引，剔除中間索引即可

具體思路如下

建立一張單表

CREATE TABLE IF NOT EXISTS article(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) UNSIGNED NOT NULL,
	category_id INT(10) UNSIGNED NOT NULL,
	views INT(10) UNSIGNED NOT NULL,
	comments INT(10) UNSIGNED NOT NULL,
	title VARCHAR(255) NOT NULL,
	content TEXT NOT NULL
);

INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

經(jīng)過(guò)如下查詢(xún)：

explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

發(fā)現(xiàn)其上面的單表查詢(xún)，不是索引的話(huà)，他是進(jìn)行了全表查詢(xún)，而且在extra還出現(xiàn)了Using filesort等問(wèn)題

所以思路可以有建立其復(fù)合索引

具體建立復(fù)合索引有兩種方式：

1. create index idx_article_ccv on article(category_id,comments,views);

2. ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );

但這只是去除了它的范圍，如果要去除Using filesort問(wèn)題的話(huà)，還要將其中間的條件范圍改為等于號(hào)才可滿(mǎn)足

發(fā)現(xiàn)其思路不行，所以刪除其索引 DROP INDEX idx_article_ccv ON article;

主要的原因是：

這是因?yàn)榘凑誃Tree索引的工作原理，先排序category_id，如果遇到相同的category_id則再排序comments,如果遇到相同的comments 則再排序views。

當(dāng)comments字段在聯(lián)合索引里處于中間位置時(shí)，因comments > 1條件是一個(gè)范圍值(所謂range)，MySQL無(wú)法利用索引再對(duì)后面的views部分進(jìn)行檢索，即range類(lèi)型查詢(xún)字段后面的索引無(wú)效。

所以建立復(fù)合索引是對(duì)的

但是其思路要避開(kāi)中間那個(gè)范圍的索引進(jìn)去

只加入另外兩個(gè)索引即可create index idx_article_cv on article(category_id, views);

關(guān)于“Mysql索引的最左前綴原則是什么”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！相信大家對(duì)“Mysql索引的最左前綴原則是什么”知識(shí)都有一定的了解，大家如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。