索引并不是時(shí)時(shí)都會(huì)生效的，比如以下幾種情況，將導(dǎo)致索引失效 

最左前綴法則

如果使用了聯(lián)合索引，要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始， 并且不跳過(guò)索引中的列。如果跳躍某一列，索引將會(huì)部分失效( 后面的字段索引失效 ) 。查看tb_user 表所創(chuàng)建的索引 。 這個(gè)聯(lián)合索引涉及到三個(gè)字段，順序分別為：profession，age，status。

show index from tb_user;

對(duì)于最左前綴法則指的是，查詢時(shí)，最左變的列，也就是profession必須存在，否則索引全部失效。

 explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

SQL 查詢時(shí)，存在 profession 字段，最左邊的列是存在的，索引滿足最左前綴法則的基本條 件。但是查詢時(shí)，跳過(guò)了 age 這個(gè)列，所以后面的列索引是不會(huì)使用的，也就是索引部分生效，所以索引的長(zhǎng)度就是47 。

explain select * from tb_user where profession = '軟件工程' and status = '0';

思考 

當(dāng)執(zhí)行 SQL 語(yǔ)句 : explain select * from tb_user where age = 31 and status = '0' and profession = '軟件工程 ' ； 時(shí)，是否滿足最左前綴法則，走不走聯(lián)合索引，

可以看到，是完全滿足最左前綴法則的，索引長(zhǎng)度 54 ，聯(lián)合索引是生效的。注意 ： 最左前綴法則中指的最左邊的列，是指在查詢時(shí)，聯(lián)合索引的最左邊的字段( 即是第一個(gè)字段) 必須存在，與我們編寫 SQL 時(shí)，條件編寫的先后順序無(wú)關(guān)。

范圍查詢

聯(lián)合索引中，出現(xiàn)范圍查詢 (>,<) ，范圍查詢右側(cè)的列索引失效。

explain select * from tb_user where profession = '軟件工程' and age > 30 and status = '0' ;

當(dāng)范圍查詢使用 > 或 < 時(shí)，走聯(lián)合索引了，但是索引的長(zhǎng)度為 49 ，就說(shuō)明范圍查詢右邊的 status 字 段是沒(méi)有走索引的。

explain select * from tb_user where profession = '軟件工程' and age >= 30 and status = '0';

當(dāng)范圍查詢使用 >= 或 <= 時(shí)，走聯(lián)合索引了，但是索引的長(zhǎng)度為 54，就說(shuō)明所有的字段都是走索引的。 所以，在業(yè)務(wù)允許的情況下，盡可能的使用類似于 >= 或 <= 這類的范圍查詢，而避免使用 > 或 < 。

索引列運(yùn)算

不要在索引列上進(jìn)行運(yùn)算操作， 索引將失效。在tb_user表中，除了前面介紹的聯(lián)合索引之外，還有一個(gè)索引，是phone字段的單列索引。

當(dāng)根據(jù) phone 字段進(jìn)行等值匹配查詢時(shí) , 索引生效。

explain select * from tb_user where phone = '17799990015';

當(dāng)根據(jù)phone字段進(jìn)行函數(shù)運(yùn)算操作之后，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引號(hào)

字符串類型字段使用時(shí)，不加引號(hào)，索引將失效。 字符串類型的字段，加單引號(hào)

 explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

 字符串類型的字段，不加單引號(hào)

 explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';

我們會(huì)明顯的發(fā)現(xiàn)，如果字符串不加單引號(hào)，對(duì)于查詢結(jié)果，沒(méi)什么影響， 但是數(shù)據(jù)庫(kù)存在隱式類型轉(zhuǎn)換，索引將失效。 模糊查詢 如果僅僅是尾部模糊匹配，索引不會(huì)失效。如果是頭部模糊匹配，索引失效。 模糊查詢時(shí)， % 加在關(guān)鍵字之后

explain select * from tb_user where profession like '軟件%';

模糊查詢時(shí)， % 加在關(guān)鍵字之前

explain select * from tb_user where profession like '%工程';

我們發(fā)現(xiàn)，在 like 模糊查詢中，在關(guān)鍵字后面加 % ，索引可以生效。而如果在關(guān)鍵字 前面加了 % ，索引將會(huì)失效。

or連接條件

用 or 分割開的條件， 如果 or 前的條件中的列有索引，而后面的列中沒(méi)有索引，那么涉及的索引都不會(huì)被用到。

explain select * from tb_user where profession like '%工程';

由于age沒(méi)有索引，所以即使id、phone有索引，索引也會(huì)失效。所以需要針對(duì)于age也要建立索引。

create index idx_user_age on tb_user(age);

再次執(zhí)行上述的SQL語(yǔ)句

 當(dāng)or連接的條件，左右兩側(cè)字段都有索引時(shí)，索引才會(huì)生效。

數(shù)據(jù)分布影響

如果 MySQL 評(píng)估使用索引比全表更慢，則不使用索引。

explain select * from tb_user where phone >= '17799990005';
explain select * from tb_user where phone >= '17799990015';

MySQL 在查詢時(shí)，會(huì)評(píng)估使用索引的效率與走全表掃描的效率，如果走全表掃描更快，則放棄 索引，走全表掃描。 因?yàn)樗饕怯脕?lái)索引少量數(shù)據(jù)的，如果通過(guò)索引查詢返回大批量的數(shù)據(jù)，則還不如走全表掃描來(lái)的快，此時(shí)索引就會(huì)失效。

 SQL提示

SQL 提示，是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段，簡(jiǎn)單來(lái)說(shuō)，就是在 SQL 語(yǔ)句中加入一些人為的提示來(lái)達(dá)到優(yōu)化操作的目的。

use index

建議 MySQL 使用哪一個(gè)索引完成此次查詢（僅僅是建議， mysql 內(nèi)部還會(huì)再次進(jìn)行評(píng)估）

explain select * from tb_user use index(idx_user_pro) where profession = '軟件工程';

 ignore index

忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工程';

force index

強(qiáng)制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = '軟件工程';

覆蓋索引

盡量使用覆蓋索引，減少 select * 。 那么什么是覆蓋索引呢？ 覆蓋索引是指 查詢使用了索引，并 且需要返回的列，在該索引中已經(jīng)全部能夠找到 。

查詢id,profession,age, status字段

explain select id,profession,age, status from tb_user where profession = '軟件工程' and age = 31 and status = '0' ;

 查詢id,profession,age, status,name字段

explain select id,profession,age, status,name from tb_user where profession = '軟件工程' and age = 31 and status = '0' \G;

因?yàn)?，?tb_user 表中有一個(gè)聯(lián)合索引 idx_user_pro_age_sta ，該索引關(guān)聯(lián)了三個(gè)字段profession、 age 、 status ，而這個(gè)索引也是一個(gè)二級(jí)索引，所以葉子節(jié)點(diǎn)下面掛的是這一行的主鍵id 。 所以當(dāng)我們查詢返回的數(shù)據(jù)在 id 、 profession 、 age 、 status 之中，則直接走二級(jí)索引 直接返回?cái)?shù)據(jù)了。 如果超出這個(gè)范圍，就需要拿到主鍵 id，再去掃描聚集索引，再獲取額外的數(shù)據(jù)了，這個(gè)過(guò)程就是回表。 而我們?nèi)绻恢笔褂胹elect * 查詢返回所有字段值，很容易就會(huì)造成回表查詢（除非是根據(jù)主鍵查詢，此時(shí)只會(huì)掃描聚集索引）

前綴索引

當(dāng)字段類型為字符串（ varchar ， text ， longtext 等）時(shí)，有時(shí)候需要索引很長(zhǎng)的字符串，這會(huì)讓 索引變得很大，查詢時(shí)，浪費(fèi)大量的磁盤 IO ， 影響查詢效率。此時(shí)可以只將字符串的一部分前綴，建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。

語(yǔ)法

create index idx_xxxx on table_name(column(n)) ; 1

前綴長(zhǎng)度

可以根據(jù)索引的選擇性來(lái)決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值，索引選擇性越高則查詢效率越高， 唯一索引的選擇性是1 ，這是最好的索引選擇性，性能也是最好的。

select count(distinct substring(email,1,5)) / count(*) from tb_user ;

創(chuàng)建前綴索引

create index idx_email_5 on tb_user(email(5));

單列索引與聯(lián)合索引

• 單列索引：即一個(gè)索引只包含單個(gè)列。
• 聯(lián)合索引：即一個(gè)索引包含了多個(gè)列。

我們先來(lái)看看 tb_user 表中目前的索引情況， 在查詢出來(lái)的索引中，既有單列索引，又有聯(lián)合索引。

在業(yè)務(wù)場(chǎng)景中，如果存在多個(gè)查詢條件，考慮針對(duì)于查詢字段建立索引時(shí)，建議建立聯(lián)合索引， 而非單列索引。

總結(jié)

針對(duì)于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引。
針對(duì)于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立引。
盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高。
如果是字符串類型的字段，字段的長(zhǎng)度較長(zhǎng)，可以針對(duì)于字段的特點(diǎn)，建立前綴索引。
盡量使用聯(lián)合索引，減少單列索引，查詢時(shí)，聯(lián)合索引很多時(shí)候可以覆蓋索引，節(jié)省存儲(chǔ)空間， 避免回表，提高查詢效率。
要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護(hù)索引結(jié)構(gòu)的代價(jià)也就越大，會(huì)影響增刪改的效率。
如果索引列不能存儲(chǔ)NULL值，請(qǐng)?jiān)趧?chuàng)建表時(shí)使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含NULL值時(shí)，它可以更好地確定哪個(gè)索引最有效地用于查詢。

到此這篇關(guān)于MYSQL的索引使用注意小結(jié)的文章就介紹到這了,更多相關(guān)mysql索引使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論