前言

之前有看過(guò)許多類似的文章內(nèi)容，提到過(guò)一些sql語(yǔ)句的使用不當(dāng)會(huì)導(dǎo)致MySQL的索引失效。還有一些MySQL“軍規(guī)”或者規(guī)范寫(xiě)明了某些sql不能這么寫(xiě)，否則索引失效。

絕大部分的內(nèi)容筆者是認(rèn)可的，不過(guò)部分舉例中筆者認(rèn)為用詞太絕對(duì)了，并沒(méi)有說(shuō)明其中的原由，很多人不知道為什么。所以筆者絕對(duì)再整理一遍MySQL中索引失效的常見(jiàn)場(chǎng)景，并分析其中的原由供大家參考。

當(dāng)然請(qǐng)記住，explain是一個(gè)好習(xí)慣！

MySQL索引失效的常見(jiàn)場(chǎng)景

在驗(yàn)證下面的場(chǎng)景時(shí)，請(qǐng)準(zhǔn)備足夠多的數(shù)據(jù)量，因?yàn)閿?shù)據(jù)量少時(shí)，MySQL的優(yōu)化器有時(shí)會(huì)判定全表掃描無(wú)傷大雅，就不會(huì)命中索引了。

1. where語(yǔ)句中包含or時(shí)，可能會(huì)導(dǎo)致索引失效

使用or并不是一定會(huì)使索引失效，你需要看or左右兩邊的查詢列是否命中相同的索引。

假設(shè)USER表中的user_id列有索引，age列沒(méi)有索引。

下面這條語(yǔ)句其實(shí)是命中索引的（據(jù)說(shuō)是新版本的MySQL才可以，如果你使用的是老版本的MySQL，可以使用explain驗(yàn)證下）。

select * from `user` where user_id = 1 or user_id = 2;

但是這條語(yǔ)句是無(wú)法命中索引的。

select * from `user` where user_id = 1 or age = 20;

假設(shè)age列也有索引的話，依然是無(wú)法命中索引的。

select * from `user` where user_id = 1 or age = 20;

因此才有建議說(shuō)，盡量避免使用or語(yǔ)句，可以根據(jù)情況盡量使用union all或者in來(lái)代替，這兩個(gè)語(yǔ)句的執(zhí)行效率也比or好些。

2. where語(yǔ)句中索引列使用了負(fù)向查詢，可能會(huì)導(dǎo)致索引失效

負(fù)向查詢包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。

某“軍規(guī)”中說(shuō)，使用負(fù)向查詢一定會(huì)索引失效，筆者查了些文章，有網(wǎng)友對(duì)這點(diǎn)進(jìn)行了反駁并舉證。

其實(shí)負(fù)向查詢并不絕對(duì)會(huì)索引失效，這要看MySQL優(yōu)化器的判斷，全表掃描或者走索引哪個(gè)成本低了。

3. 索引字段可以為null，使用is null或is not null時(shí)，可能會(huì)導(dǎo)致索引失效

其實(shí)單個(gè)索引字段，使用is null或is not null時(shí)，是可以命中索引的，但網(wǎng)友在舉證時(shí)說(shuō)兩個(gè)不同索引字段用or連接時(shí)，索引就失效了，筆者認(rèn)為確實(shí)索引失效，但這個(gè)鍋應(yīng)該由or來(lái)背，屬于第一種場(chǎng)景~~

假設(shè)USER表中的user_id列有索引且允許null，age列有索引且允許null。

select * from `user` where user_id is not null or age is not null;

不過(guò)某些“軍規(guī)”和規(guī)范中都有強(qiáng)調(diào)，字段要設(shè)為not null并提供默認(rèn)值，是有原因值得參考的。

• null的列使索引/索引統(tǒng)計(jì)/值比較都更加復(fù)雜，對(duì)MySQL來(lái)說(shuō)更難優(yōu)化。
• null 這種類型MySQL內(nèi)部需要進(jìn)行特殊處理，增加數(shù)據(jù)庫(kù)處理記錄的復(fù)雜性；同等條件下，表中有較多空字段的時(shí)候，數(shù)據(jù)庫(kù)的處理性能會(huì)降低很多。
• null值需要更多的存儲(chǔ)空，無(wú)論是表還是索引中每行中的null的列都需要額外的空間來(lái)標(biāo)識(shí)。
• 對(duì)null 的處理時(shí)候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in這些操作符號(hào)。如：where name!='shenjian'，如果存在name為null值的記錄，查詢結(jié)果就不會(huì)包含name為null值的記錄。

4. 在索引列上使用內(nèi)置函數(shù)，一定會(huì)導(dǎo)致索引失效

比如下面語(yǔ)句中索引列l(wèi)ogin_time上使用了函數(shù)，會(huì)索引失效：

select * from `user` where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;

優(yōu)化建議，盡量在應(yīng)用程序中進(jìn)行計(jì)算和轉(zhuǎn)換。

其實(shí)還有網(wǎng)友提到的兩種索引失效場(chǎng)景，應(yīng)該都?xì)w于索引列使用了函數(shù)。

4.1 隱式類型轉(zhuǎn)換導(dǎo)致的索引失效

比如下面語(yǔ)句中索引列user_id為varchar類型，不會(huì)命中索引：

select * from `user` where user_id = 12;

這是因?yàn)镸ySQL做了隱式類型轉(zhuǎn)換，調(diào)用函數(shù)將user_id做了轉(zhuǎn)換。

select * from `user` where CAST(user_id AS signed int) = 12;

4.2 隱式字符編碼轉(zhuǎn)換導(dǎo)致的索引失效

當(dāng)兩個(gè)表之間做關(guān)聯(lián)查詢時(shí)，如果兩個(gè)表中關(guān)聯(lián)的字段字符編碼不一致的話，MySQL可能會(huì)調(diào)用CONVERT函數(shù)，將不同的字符編碼進(jìn)行隱式轉(zhuǎn)換從而達(dá)到統(tǒng)一。作用到關(guān)聯(lián)的字段時(shí)，就會(huì)導(dǎo)致索引失效。

比如下面這個(gè)語(yǔ)句，其中d.tradeid字符編碼為utf8，而l.tradeid的字符編碼為utf8mb4。因?yàn)閡tf8mb4是utf8的超集，所以MySQL在做轉(zhuǎn)換時(shí)會(huì)用CONVERT將utf8轉(zhuǎn)為utf8mb4。簡(jiǎn)單來(lái)看就是CONVERT作用到了d.tradeid上，因此索引失效。

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

這種情況一般有兩種解決方案。

方案1: 將關(guān)聯(lián)字段的字符編碼統(tǒng)一。

方案2: 實(shí)在無(wú)法統(tǒng)一字符編碼時(shí)，手動(dòng)將CONVERT函數(shù)作用到關(guān)聯(lián)時(shí)=的右側(cè)，起到字符編碼統(tǒng)一的目的，這里是強(qiáng)制將utf8mb4轉(zhuǎn)為utf8，當(dāng)然從超集向子集轉(zhuǎn)換是有數(shù)據(jù)截?cái)囡L(fēng)險(xiǎn)的。如下：

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

5. 對(duì)索引列進(jìn)行運(yùn)算，一定會(huì)導(dǎo)致索引失效

運(yùn)算如+，-，*，/等，如下：

select * from `user` where age - 1 = 10;

優(yōu)化的話，要把運(yùn)算放在值上，或者在應(yīng)用程序中直接算好，比如：

select * from `user` where age = 10 - 1;

6. like通配符可能會(huì)導(dǎo)致索引失效

like查詢以%開(kāi)頭時(shí)，會(huì)導(dǎo)致索引失效。解決辦法有兩種：

將%移到后面，如：

select * from `user` where `name` like '李%';

利用覆蓋索引來(lái)命中索引。

select name from `user` where `name` like '%李%';

7. 聯(lián)合索引中，where中索引列違背最左匹配原則，一定會(huì)導(dǎo)致索引失效

當(dāng)創(chuàng)建一個(gè)聯(lián)合索引的時(shí)候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了(k1)、(k1,k2)和(k1,k2,k3)三個(gè)索引，這就是最左匹配原則。

比如下面的語(yǔ)句就不會(huì)命中索引：

select * from t where k2=2;
select * from t where k3=3;
slect * from t where k2=2 and k3=3;

下面的語(yǔ)句只會(huì)命中索引(k1):

slect * from t where k1=1 and k3=3;

8. MySQL優(yōu)化器的最終選擇，不走索引

上面有提到，即使完全符合索引生效的場(chǎng)景，考慮到實(shí)際數(shù)據(jù)量等原因，最終是否使用索引還要看MySQL優(yōu)化器的判斷。當(dāng)然你也可以在sql語(yǔ)句中寫(xiě)明強(qiáng)制走某個(gè)索引。

優(yōu)化索引的一些建議

• 禁止在更新十分頻繁、區(qū)分度不高的屬性上建立索引。
  • 更新會(huì)變更B+樹(shù)，更新頻繁的字段建立索引會(huì)大大降低數(shù)據(jù)庫(kù)性能。
  • “性別”這種區(qū)分度不大的屬性，建立索引是沒(méi)有什么意義的，不能有效過(guò)濾數(shù)據(jù)，性能與全表掃描類似。
• 建立組合索引，必須把區(qū)分度高的字段放在前面。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。

參考

《為什么這些SQL語(yǔ)句邏輯相同，性能卻差異巨大？》

《后端程序員必備：索引失效的十大雜癥》

《58到家數(shù)據(jù)庫(kù)30條軍規(guī)解讀》

《MySQL的or/in/union與索引優(yōu)化 | 架構(gòu)師之路》

最新評(píng)論