今天繼續(xù)數(shù)據(jù)庫(kù)的話題，更進(jìn)一步聊聊索引的相關(guān)問(wèn)題，因?yàn)樗饕谴蠹叶急容^關(guān)心的公共話題，確實(shí)有很多坑。

不知道你在實(shí)際工作中，有沒(méi)有遇到過(guò)下面的這兩種情況：

• 明明在某個(gè)字段上加了索引，但實(shí)際上并沒(méi)有生效。

• 索引有時(shí)候生效了，有時(shí)候沒(méi)有生效。

今天就跟大家一起聊聊，mysql數(shù)據(jù)庫(kù)索引失效的10種場(chǎng)景，給曾經(jīng)踩過(guò)坑，或者即將要踩坑的朋友們一個(gè)參考。圖片

1. 準(zhǔn)備工作

所謂空口無(wú)憑，如果我直接把索引失效的這些場(chǎng)景丟出來(lái)，可能沒(méi)有任何說(shuō)服力。

所以，我決定建表和造數(shù)據(jù)，給大家一步步演示效果，盡量做到有理有據(jù)。

我相信，如果大家耐心的看完這篇文章，一定會(huì)有很多收獲的。

1.1 創(chuàng)建user表

創(chuàng)建一張user表，表中包含：id、code、age、name和height字段。

CREATE TABLE `user` (   `id` int NOT NULL AUTO_INCREMENT,   `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,   `age` int DEFAULT '0',   `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,   `height` int DEFAULT '0',   `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_code_age_name` (`code`,`age`,`name`),   KEY `idx_height` (`height`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

此外，還創(chuàng)建了三個(gè)索引：

id：數(shù)據(jù)庫(kù)的主鍵 idx_code_age_name：由code、age和name三個(gè)字段組成的聯(lián)合索引。 idx_height：普通索引

1.2 插入數(shù)據(jù)

為了方便給大家做演示，我特意向user表中插入了3條數(shù)據(jù)：

INSERT INTO sue.user (id, code, age, name, height) VALUES (1, '101', 21, '周星馳', 175,'香港'); INSERT INTO sue.user (id, code, age, name, height) VALUES (2, '102', 18, '周杰倫', 173,'臺(tái)灣'); INSERT INTO sue.user (id, code, age, name, height) VALUES (3, '103', 23, '蘇三', 174,'成都');

周星馳和周杰倫是我偶像，在這里自戀了一次，把他們和我放到一起了。哈哈哈。

1.3 查看數(shù)據(jù)庫(kù)版本

為了防止以后出現(xiàn)不必要的誤會(huì)，在這里有必要查一下當(dāng)前數(shù)據(jù)庫(kù)的版本。不說(shuō)版本就直接給結(jié)論，是耍流氓，哈哈哈。

select version();

查出當(dāng)前的mysql版本號(hào)為：8.0.21

1.4 查看執(zhí)行計(jì)劃

在mysql中，如果你想查看某條sql語(yǔ)句是否使用了索引，或者已建好的索引是否失效，可以通過(guò)explain關(guān)鍵字，查看該sql語(yǔ)句的執(zhí)行計(jì)劃，來(lái)判斷索引使用情況。

例如：

explain select * from user where id=1;

執(zhí)行結(jié)果：

從圖中可以看出，由于id字段是主鍵，該sql語(yǔ)句用到了主鍵索引。

當(dāng)然，如果你想更深入的了解explain關(guān)鍵字的用法，可以看看我的另一篇文章《explain | 索引優(yōu)化的這把絕世好劍，你真的會(huì)用嗎？》，里面更為詳細(xì)的介紹。

2. 不滿足最左匹配原則

之前我已經(jīng)給code、age和name這3個(gè)字段建好聯(lián)合索引：idx_code_age_name。

該索引字段的順序是：

code age name

如果在使用聯(lián)合索引時(shí)，沒(méi)注意最左前綴原則，很有可能導(dǎo)致索引失效喔，不信我們一起往下看。

2.1 哪些情況索引有效？

先看看哪些情況下，能走索引。

explain select * from user where code='101'; explain select * from user where code='101' and age=21  explain select * from user where code='101' and age=21 and name='周星馳';

執(zhí)行結(jié)果：

上面三種情況，sql都能正常走索引。

其實(shí)還有一種比較特殊的場(chǎng)景：

explain select * from user where code = '101'  and name='周星馳';

執(zhí)行結(jié)果：

查詢條件原本的順序是：code、age、name，但這里只有code和name中間斷層了，掉了age字段，這種情況也能走code字段上的索引。

看到這里，不知道聰明的你，有沒(méi)有發(fā)現(xiàn)這樣一個(gè)規(guī)律：這4條sql中都有code字段，它是索引字段中的第一個(gè)字段，也就是最左邊的字段。只要有這個(gè)字段在，該sql已經(jīng)就能走索引。

這就是我們所說(shuō)的最左匹配原則。

2.2 哪些情況索引失效？

前面我已經(jīng)介紹過(guò)，建立了聯(lián)合索引后，在查詢條件中有哪些情況索引是有效的。

接下來(lái)，我們重點(diǎn)看看哪些情況下索引會(huì)失效。

explain select * from user where age=21; explain select * from user where name='周星馳'; explain select * from user where age=21 and name='周星馳';

執(zhí)行結(jié)果：

從圖中看出這3種情況下索引確實(shí)失效了。

說(shuō)明以上3種情況不滿足最左匹配原則，說(shuō)白了是因?yàn)椴樵儣l件中，沒(méi)有包含給定字段最左邊的索引字段，即字段code。

3. 使用了select *

在《阿里巴巴開(kāi)發(fā)手冊(cè)》中明確說(shuō)過(guò)，查詢sql中禁止使用select * 。

那么，你知道為什么嗎？

廢話不多說(shuō)，按照國(guó)際慣例先上一條sql：

explain  select * from user where name='蘇三';

執(zhí)行結(jié)果：

在該sql中用了select *，從執(zhí)行結(jié)果看，走了全表掃描，沒(méi)有用到任何索引，查詢效率是非常低的。

如果查詢的時(shí)候，只查我們真正需要的列，而不查所有列，結(jié)果會(huì)怎么樣？

非常快速的將上面的sql改成只查了code和name列，太easy了：

explain  select code,name from user  where name='蘇三';

執(zhí)行結(jié)果：

從圖中執(zhí)行結(jié)果不難看出，該sql語(yǔ)句這次走了全索引掃描，比全表掃描效率更高。

其實(shí)這里用到了：覆蓋索引。

如果select語(yǔ)句中的查詢列，都是索引列，那么這些列被稱為覆蓋索引。這種情況下，查詢的相關(guān)字段都能走索引，索引查詢效率相對(duì)來(lái)說(shuō)更高一些。

而使用select *查詢所有列的數(shù)據(jù)，大概率會(huì)查詢非索引列的數(shù)據(jù)，非索引列不會(huì)走索引，查詢效率非常低。

4. 索引列上有計(jì)算

介紹本章節(jié)內(nèi)容前，先跟大家一起回顧一下，根據(jù)id查詢數(shù)據(jù)的sql語(yǔ)句：

explain select * from user where id=1;

執(zhí)行結(jié)果：

從圖中可以看出，由于id字段是主鍵，該sql語(yǔ)句用到了主鍵索引。

但如果id列上面有計(jì)算，比如：

explain select * from user where id+1=2;

執(zhí)行結(jié)果：

從上圖中的執(zhí)行結(jié)果，能夠非常清楚的看出，該id字段的主鍵索引，在有計(jì)算的情況下失效了。

5. 索引列用了函數(shù)

有時(shí)候我們?cè)谀硹lsql語(yǔ)句的查詢條件中，需要使用函數(shù)，比如：截取某個(gè)字段的長(zhǎng)度。

假如現(xiàn)在有個(gè)需求：想查出所有身高是17開(kāi)頭的人，如果sql語(yǔ)句寫成這樣：

explain select * from user  where height=17;

該sql語(yǔ)句確實(shí)用到了普通索引：

但該sql語(yǔ)句肯定是有問(wèn)題的，因?yàn)樗荒懿槌錾砀哒玫扔?7的，但對(duì)于174這種情況，它沒(méi)辦法查出來(lái)。

為了滿足上面的要求，我們需要把sql語(yǔ)句稍稍改造了一下：

explain select * from user  where SUBSTR(height,1,2)=17;

這時(shí)需要用到SUBSTR函數(shù)，用它截取了height字段的前面兩位字符，從第一個(gè)字符開(kāi)始。

執(zhí)行結(jié)果：

你有沒(méi)有發(fā)現(xiàn)，在使用該函數(shù)之后，該sql語(yǔ)句竟然走了全表掃描，索引失效了。

6. 字段類型不同

在sql語(yǔ)句中因?yàn)樽侄晤愋筒煌?，而?dǎo)致索引失效的問(wèn)題，很容易遇到，可能是我們?nèi)粘９ぷ髦凶钊菀缀雎缘膯?wèn)題。

到底怎么回事呢？

請(qǐng)大家注意觀察一下t_user表中的code字段，它是varchar字符類型的。

在sql語(yǔ)句中查詢數(shù)據(jù)時(shí)，查詢條件我們可以寫成這樣：

explain  select * from user where code="101";

執(zhí)行結(jié)果：

從上圖中看到，該code字段走了索引。

溫馨提醒一下，查詢字符字段時(shí)，用雙引號(hào)“和單引號(hào)'都可以。

但如果你在寫sql時(shí)，不小心把引號(hào)弄掉了，把sql語(yǔ)句變成了：

explain  select * from user where code=101;

執(zhí)行結(jié)果：

你會(huì)驚奇的發(fā)現(xiàn)，該sql語(yǔ)句竟然變成了全表掃描。因?yàn)樯賹懥艘?hào)，這種小小的失誤，竟然讓code字段上的索引失效了。

這時(shí)你心里可能有一萬(wàn)個(gè)為什么，其中有一個(gè)肯定是：為什么索引會(huì)失效呢？

答：因?yàn)閏ode字段的類型是varchar，而傳參的類型是int，兩種類型不同。

此外，還有一個(gè)有趣的現(xiàn)象，如果int類型的height字段，在查詢時(shí)加了引號(hào)條件，卻還可以走索引：

explain select * from user  where height='175';

執(zhí)行結(jié)果：

從圖中看出該sql語(yǔ)句確實(shí)走了索引。int類型的參數(shù)，不管在查詢時(shí)加沒(méi)加引號(hào)，都能走索引。

這是變魔術(shù)嗎？這不科學(xué)呀。

答：mysql發(fā)現(xiàn)如果是int類型字段作為查詢條件時(shí)，它會(huì)自動(dòng)將該字段的傳參進(jìn)行隱式轉(zhuǎn)換，把字符串轉(zhuǎn)換成int類型。

mysql會(huì)把上面列子中的字符串175，轉(zhuǎn)換成數(shù)字175，所以仍然能走索引。

接下來(lái)，看一個(gè)更有趣的sql語(yǔ)句：

select 1 + '1';

它的執(zhí)行結(jié)果是2，還是11呢？

好吧，不賣關(guān)子了，直接公布答案執(zhí)行結(jié)果是2。

mysql自動(dòng)把字符串1，轉(zhuǎn)換成了int類型的1，然后變成了：1+1=2。

但如果你確實(shí)想拼接字符串該怎么辦？

答：可以使用concat關(guān)鍵字。

具體拼接sql如下：

select concat(1,'1');

接下來(lái)，關(guān)鍵問(wèn)題來(lái)了：為什么字符串類型的字段，傳入了int類型的參數(shù)時(shí)索引會(huì)失效呢？

答：根據(jù)mysql官網(wǎng)上解釋，字符串'1'、' 1 '、'1a'都能轉(zhuǎn)換成int類型的1，也就是說(shuō)可能會(huì)出現(xiàn)多個(gè)字符串，對(duì)應(yīng)一個(gè)int類型參數(shù)的情況。那么，mysql怎么知道該把int類型的1轉(zhuǎn)換成哪種字符串，用哪個(gè)索引快速查值?

感興趣的小伙伴可以再看看官方文檔：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

7. like左邊包含%

模糊查詢，在我們?nèi)粘５墓ぷ髦校褂妙l率還是比較高的。

比如現(xiàn)在有個(gè)需求：想查詢姓李的同學(xué)有哪些?

使用like語(yǔ)句可以很快的實(shí)現(xiàn)：

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

但如果like用的不好，就可能會(huì)出現(xiàn)性能問(wèn)題，因?yàn)橛袝r(shí)候它的索引會(huì)失效。

不信，我們一起往下看。

目前l(fā)ike查詢主要有三種情況：

like '%a' like 'a%' like '%a%'

假如現(xiàn)在有個(gè)需求：想查出所有code是10開(kāi)頭的用戶。

這個(gè)需求太簡(jiǎn)單了吧，sql語(yǔ)句如下：

explain select * from user where code like '10%';

執(zhí)行結(jié)果：

圖中看出這種%在10右邊時(shí)走了索引。

而如果把需求改了：想出現(xiàn)出所有code是1結(jié)尾的用戶。

查詢sql語(yǔ)句改為：

explain select * from user where code like '%1';

執(zhí)行結(jié)果：

從圖中看出這種%在1左邊時(shí)，code字段上索引失效了，該sql變成了全表掃描。

此外，如果出現(xiàn)以下sql：

explain select * from user where code like '%1%';

該sql語(yǔ)句的索引也會(huì)失效。

下面用一句話總結(jié)一下規(guī)律：當(dāng)like語(yǔ)句中的%，出現(xiàn)在查詢條件的右邊時(shí)，索引會(huì)失效。

那么，為什么會(huì)出現(xiàn)這種現(xiàn)象呢？

答：其實(shí)很好理解，索引就像字典中的目錄。一般目錄是按字母或者拼音從小到大，從左到右排序，是有順序的。

我們?cè)诓槟夸洉r(shí)，通常會(huì)先從左邊第一個(gè)字母進(jìn)行匹對(duì)，如果相同，再匹對(duì)左邊第二個(gè)字母，如果再相同匹對(duì)其他的字母，以此類推。

通過(guò)這種方式我們能快速鎖定一個(gè)具體的目錄，或者縮小目錄的范圍。

但如果你硬要跟目錄的設(shè)計(jì)反著來(lái)，先從字典目錄右邊匹配第一個(gè)字母，這畫面你可以自行腦補(bǔ)一下，你眼中可能只剩下絕望了，哈哈。

8. 列對(duì)比

上面的內(nèi)容都是常規(guī)需求，接下來(lái)，來(lái)點(diǎn)不一樣的。

假如我們現(xiàn)在有這樣一個(gè)需求：過(guò)濾出表中某兩列值相同的記錄。比如user表中id字段和height字段，查詢出這兩個(gè)字段中值相同的記錄。

這個(gè)需求很簡(jiǎn)單，sql可以這樣寫：

explain select * from user  where id=height

執(zhí)行結(jié)果：

意不意外，驚不驚喜？索引失效了。

為什么會(huì)出現(xiàn)這種結(jié)果？

id字段本身是有主鍵索引的，同時(shí)height字段也建了普通索引的，并且兩個(gè)字段都是int類型，類型是一樣的。

但如果把兩個(gè)單獨(dú)建了索引的列，用來(lái)做列對(duì)比時(shí)索引會(huì)失效。

感興趣的朋友可以找我私聊。

9. 使用or關(guān)鍵字

我們平時(shí)在寫查詢sql時(shí)，使用or關(guān)鍵字的場(chǎng)景非常多，但如果你稍不注意，就可能讓已有的索引失效。

不信一起往下面看。

某天你遇到這樣一個(gè)需求：想查一下id=1或者h(yuǎn)eight=175的用戶。

你三下五除二就把sql寫好了：

explain select * from user  where id=1 or height='175';

執(zhí)行結(jié)果：

沒(méi)錯(cuò)，這次確實(shí)走了索引，恭喜被你蒙對(duì)了，因?yàn)閯偤胕d和height字段都建了索引。

但接下來(lái)的一個(gè)夜黑風(fēng)高的晚上，需求改了：除了前面的查詢條件之后，還想加一個(gè)address='成都'。

這還不簡(jiǎn)單，sql走起：

explain select * from user  where id=1 or height='175' or address='成都';

執(zhí)行結(jié)果：

結(jié)果悲劇了，之前的索引都失效了。

你可能一臉懵逼，為什么？我做了什么？

答：因?yàn)槟阕詈蠹拥腶ddress字段沒(méi)有加索引，從而導(dǎo)致其他字段的索引都失效了。

注意：如果使用了or關(guān)鍵字，那么它前面和后面的字段都要加索引，不然所有的索引都會(huì)失效，這是一個(gè)大坑。

10. not in和not exists

在我們?nèi)粘９ぷ髦杏玫靡脖容^多的，還有范圍查詢，常見(jiàn)的有：

in exists not in not exists between and

今天重點(diǎn)聊聊前面四種。

10.1 in關(guān)鍵字

假如我們想查出height在某些范圍之內(nèi)的用戶，這時(shí)sql語(yǔ)句可以這樣寫：

explain select * from user where height in (173,174,175,176);

執(zhí)行結(jié)果：

從圖中可以看出，sql語(yǔ)句中用in關(guān)鍵字是走了索引的。

10.2 exists關(guān)鍵字

有時(shí)候使用in關(guān)鍵字時(shí)性能不好，這時(shí)就能用exists關(guān)鍵字優(yōu)化sql了，該關(guān)鍵字能達(dá)到in關(guān)鍵字相同的效果：

explain select * from user  t1 where  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

執(zhí)行結(jié)果：

從圖中可以看出，用exists關(guān)鍵字同樣走了索引。

10.3 not in關(guān)鍵字

上面演示的兩個(gè)例子是正向的范圍，即在某些范圍之內(nèi)。

那么反向的范圍，即不在某些范圍之內(nèi)，能走索引不？

話不多說(shuō)，先看看使用not in的情況：

explain select * from user where height not in (173,174,175,176);

執(zhí)行結(jié)果：

你沒(méi)看錯(cuò)，索引失效了。

看如果現(xiàn)在需求改了：想查一下id不等于1、2、3的用戶有哪些，這時(shí)sql語(yǔ)句可以改成這樣：

explain select * from user where id  not in (173,174,175,176);

執(zhí)行結(jié)果：

你可能會(huì)驚奇的發(fā)現(xiàn)，主鍵字段中使用not in關(guān)鍵字查詢數(shù)據(jù)范圍，任然可以走索引。而普通索引字段使用了not in關(guān)鍵字查詢數(shù)據(jù)范圍，索引會(huì)失效。

10.4 not exists關(guān)鍵字

除此之外，如果sql語(yǔ)句中使用not exists時(shí)，索引也會(huì)失效。具體sql語(yǔ)句如下：

explain select * from user  t1 where  not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

執(zhí)行結(jié)果：

從圖中看出sql語(yǔ)句中使用not exists關(guān)鍵后，t1表走了全表掃描，并沒(méi)有走索引。

11. order by的坑

在sql語(yǔ)句中，對(duì)查詢結(jié)果進(jìn)行排序是非常常見(jiàn)的需求，一般情況下我們用關(guān)鍵字：order by就能搞定。

但我始終覺(jué)得order by挺難用的，它跟where或者limit關(guān)鍵字有很多千絲萬(wàn)縷的聯(lián)系，一不小心就會(huì)出問(wèn)題。

Let go

11.1 哪些情況走索引？

首先當(dāng)然要溫柔一點(diǎn)，一起看看order by的哪些情況可以走索引。

我之前說(shuō)過(guò)，在code、age和name這3個(gè)字段上，已經(jīng)建了聯(lián)合索引：idx_code_age_name。

11.1.1 滿足最左匹配原則

order by后面的條件，也要遵循聯(lián)合索引的最左匹配原則。具體有以下sql：

explain select * from user order by code limit 100; explain select * from user order by code,age limit 100; explain select * from user order by code,age,name limit 100;

執(zhí)行結(jié)果：

從圖中看出這3條sql都能夠正常走索引。

除了遵循最左匹配原則之外，有個(gè)非常關(guān)鍵的地方是，后面還是加了limit關(guān)鍵字，如果不加它索引會(huì)失效。

11.1.2 配合where一起使用

order by還能配合where一起遵循最左匹配原則。

explain select * from user where code='101' order by age;

執(zhí)行結(jié)果：

code是聯(lián)合索引的第一個(gè)字段，在where中使用了，而age是聯(lián)合索引的第二個(gè)字段，在order by中接著使用。

假如中間斷層了，sql語(yǔ)句變成這樣，執(zhí)行結(jié)果會(huì)是什么呢？

explain select * from user where code='101' order by name;

執(zhí)行結(jié)果：

雖說(shuō)name是聯(lián)合索引的第三個(gè)字段，但根據(jù)最左匹配原則，該sql語(yǔ)句依然能走索引，因?yàn)樽钭筮叺牡谝粋€(gè)字段code，在where中使用了。只不過(guò)order by的時(shí)候，排序效率比較低，需要走一次filesort排序罷了。

11.1.3 相同的排序

order by后面如果包含了聯(lián)合索引的多個(gè)排序字段，只要它們的排序規(guī)律是相同的（要么同時(shí)升序，要么同時(shí)降序），也可以走索引。

具體sql如下：

explain select * from user order by code desc,age desc limit 100;

執(zhí)行結(jié)果：

該示例中order by后面的code和age字段都用了降序，所以依然走了索引。

11.1.4 兩者都有

如果某個(gè)聯(lián)合索引字段，在where和order by中都有，結(jié)果會(huì)怎么樣？

explain select * from user where code='101' order by code, name;

執(zhí)行結(jié)果：

code字段在where和order by中都有，對(duì)于這種情況，從圖中的結(jié)果看出，還是能走了索引的。

11.2 哪些情況不走索引？

前面介紹的都是正面的用法，是為了讓大家更容易接受下面反面的用法。

好了，接下來(lái)，重點(diǎn)聊聊order by的哪些情況下不走索引？

11.2.1 沒(méi)加where或limit

如果order by語(yǔ)句中沒(méi)有加where或limit關(guān)鍵字，該sql語(yǔ)句將不會(huì)走索引。

explain select * from user order by code, name;

執(zhí)行結(jié)果：

從圖中看出索引真的失效了。

11.2.2 對(duì)不同的索引做order by

前面介紹的基本都是聯(lián)合索引，這一個(gè)索引的情況。但如果對(duì)多個(gè)索引進(jìn)行order by，結(jié)果會(huì)怎么樣呢？

explain select * from user order by code, height limit 100;

執(zhí)行結(jié)果：

從圖中看出索引也失效了。

11.2.3 不滿足最左匹配原則

前面已經(jīng)介紹過(guò)，order by如果滿足最左匹配原則，還是會(huì)走索引。下面看看，不滿足最左匹配原則的情況：

explain select * from user order by name limit 100;

執(zhí)行結(jié)果：

name字段是聯(lián)合索引的第三個(gè)字段，從圖中看出如果order by不滿足最左匹配原則，確實(shí)不會(huì)走索引。

11.2.4 不同的排序

前面已經(jīng)介紹過(guò)，如果order by后面有一個(gè)聯(lián)合索引的多個(gè)字段，它們具有相同排序規(guī)則，那么會(huì)走索引。

但如果它們有不同的排序規(guī)則呢？

explain select * from user order by code asc,age desc limit 100;

執(zhí)行結(jié)果：

從圖中看出，盡管order by后面的code和age字段遵循了最左匹配原則，但由于一個(gè)字段是用的升序，另一個(gè)字段用的降序，最終會(huì)導(dǎo)致索引失效。

好了今天分享的內(nèi)容就先到這里，我們下期再見(jiàn)。

聲明：本文轉(zhuǎn)載于：蘇三呀，如有侵犯，請(qǐng)聯(lián)系admin@php.cn刪除