前言

該篇內(nèi)容：

• 結(jié)合實(shí)例，介紹常見的幾種索引失效場(chǎng)景，以及對(duì)每個(gè)場(chǎng)景會(huì)做一些額外的擴(kuò)展說明.
•  后面寫著寫著跑題了，為了想給大家講下什么是回表...然后。。。。

失效一

查詢的字段列未添加索引

ps：這...直接從失效二開始看吧，我承認(rèn)我在啰嗦。 

請(qǐng)看示例，表內(nèi) userProfession 列 存入的是人員的職業(yè)信息 ：

該字段列未使用索引，所以查詢無法命中索引 ：

EXPLAIN SELECT * FROM userinfo WHERE userProfession='教師';

分析結(jié)果：

失效二

建索引的字段 類型為 varchar ， 但是 查詢的 傳入值 沒有使用 ‘ ’ ，即沒加引號(hào)。

請(qǐng)看示例，表內(nèi) userCardNum 列 存入的是職工的工號(hào) ：

userCardNum 設(shè)置的是varchar 類型 ：

給 userCardNum 添加了唯一索引 ：

正常命中索引查詢SQL語句分析（使用 EXPLAIN ）：

EXPLAIN SELECT * FROM userinfo WHERE userCardNum= '10011';

索引命中分析結(jié)果：

索引失效，傳入值沒有帶 ‘ ’ 單引號(hào)：

EXPLAIN SELECT * FROM userinfo WHERE userCardNum= 10011;

索引失效分析結(jié)果：

切記： 傳入的值使用方式記得跟數(shù)據(jù)庫表內(nèi)列，索引設(shè)置字段保持一樣的類型，這樣萬無一失。

擴(kuò)展補(bǔ)充，為什么失效二情況 強(qiáng)調(diào)了 索引字段是 varchar ，傳入 值使用不加引號(hào) 呢？

因?yàn)橐徊糠秩嗽诶斫膺@種情況 有錯(cuò)誤的思想，理解為 涉及類型轉(zhuǎn)換 ，以為是因?yàn)閱渭兊淖侄晤愋筒粚?duì)應(yīng) 導(dǎo)致索引失效，這里必須補(bǔ)充一下一個(gè)示例：

字段列 userAge：

userAge類型為 int ：

給 userAge添加了索引 ：

SQL使用傳入值，添加了引號(hào) ：

EXPLAIN SELECT * FROM userinfo WHERE userAge  = '25';

結(jié)果分析 ,可以命中索引：

所以，咱們記住，這種失效場(chǎng)景是，數(shù)據(jù)庫表內(nèi)字段是varchar，給這字段添加了索引，傳入值sql未使用引號(hào)，這時(shí)候涉及隱式轉(zhuǎn)換，才會(huì)導(dǎo)致索引失效。 當(dāng)然這也是為什么我讓大家切記，保持與表內(nèi)數(shù)據(jù)類型一致，這不管是啥都很穩(wěn)妥。

失效三

使用 like 進(jìn)行 左模糊匹配查找 ‘%XXX’

請(qǐng)看示例，表內(nèi)字段列 userName

給userName 添加索引：

索引失效 ，使用LIKE 并使用的是左匹配（同樣左右一起用也是失效的）：

EXPLAIN SELECT * FROM userinfo WHERE userName LIKE '%一';

索引失效分析結(jié)果：

擴(kuò)展補(bǔ)充，當(dāng)使用LIKE ，索引就必然失效么？

并不是，這里強(qiáng)調(diào)了 左匹配。

請(qǐng)看 LIKE 右匹配：

EXPLAIN SELECT * FROM userinfo WHERE userName LIKE '王%';

索引命中分析結(jié)果：

失效四

索引字段作為查詢條件時(shí)，使用了計(jì)算或者函數(shù)

請(qǐng)看示例SQL ，索引字段使用了計(jì)算：

EXPLAIN SELECT * FROM userinfo WHERE userAge  +2= 37;

索引字段因使用計(jì)算，失效分析結(jié)果：

再看示例，索引字段使用函數(shù)：

EXPLAIN SELECT * FROM userinfo WHERE  TRIM(userName) = '陳七';

索引字段因使用函數(shù)，失效分析結(jié)果：

失效五

使用 OR ，條件字段中包含有未設(shè)索引字段列

請(qǐng)看示例，表內(nèi)userProfession字段沒有添加索引， 而 userAge添加了索引：

執(zhí)行SQL使用OR ，同時(shí)使用userProfession ，userAge作為條件查找：

EXPLAIN SELECT * FROM userinfo WHERE userProfession='教師' OR  userAge  = 25;

索引未命中分析結(jié)果：

擴(kuò)展補(bǔ)充，如果 使用 OR ，作為查找條件的字段都已經(jīng)添加了索引 ，會(huì)是什么情況？

例如 userAge 我們使用了索引，

EXPLAIN SELECT * FROM userinfo WHERE userAge  = 23 OR  userAge  = 35;

這樣會(huì)命中索引么？

注意了，分情況，因?yàn)槲覀兊膗serAge添加的不是唯一索引，因?yàn)槟挲g嘛，總會(huì)有重復(fù)：

再看當(dāng)前數(shù)據(jù)，可以看的 userAge 為 35 ，userAge為 23 的 都只有一條數(shù)據(jù)，也就是對(duì)應(yīng)目前表內(nèi)數(shù)據(jù)，其實(shí)是唯一數(shù)據(jù)：

這時(shí)候，索引分析結(jié)果，索引命中了：

可以看的，索引命中了。

那么接著，我們把一個(gè)userAge查找條件換成25， 數(shù)據(jù)表內(nèi)數(shù)據(jù) 25 的有兩條，不唯一：

EXPLAIN SELECT * FROM userinfo WHERE userAge  = 25 OR  userAge  = 35;

這時(shí)候，索引分析結(jié)果，索引失效了：

ps： 也就是，若把索引設(shè)置成為唯一索引，那么數(shù)據(jù)庫內(nèi)也就不存在重復(fù)數(shù)據(jù)了，這時(shí)候如果使用or 查詢同個(gè)索引字段列，那么就是命中的。當(dāng)時(shí)往往有時(shí)候，就是類似文中的情況。userAge就是設(shè)置為不唯一索引，那么就是使用 UNION ALL 去解決或者從代碼層面分開查詢。

配上使用 UNION ALL的方式：

EXPLAIN 
SELECT * FROM userinfo WHERE userAge  = 25
UNION ALL
SELECT * FROM userinfo WHERE userAge  = 35

索引分析結(jié)果，是命中的：

失效六

聯(lián)合索引，不滿足最佳左前綴原則，導(dǎo)致索引失效

請(qǐng)看示例，表內(nèi) userWeight 、userHeight、userSight 三個(gè)字段：

給 userWeight 、userHeight、userSight 這三個(gè)字段建立聯(lián)合索引 ：

注意順序，最左為 userWeight

這時(shí)候，如果SQL 在使用這些字段索引查找時(shí)，先看單個(gè)字段查找：

單個(gè)使用userWeight ：

EXPLAINSELECT * FROM userinfo WHERE  userWeight='50';

索引分析結(jié)果，索引命中，因?yàn)闈M足了最佳做前綴原則：

那么我們換成單個(gè)使用 userHeight，

EXPLAINSELECT * FROM userinfo WHERE  userHeight='180' ;

索引分析結(jié)果，索引失效了：

同樣換成 userSight 也是一樣，索引失效了，因?yàn)檫@種情形就是沒有滿足最佳做前綴原則。

對(duì)于使用聯(lián)合索引，還沒完。

當(dāng)我們同時(shí)使用聯(lián)合索引里面的2個(gè)或2個(gè)以上的字段列時(shí)：

使用 userWeight 和 userSight：

EXPLAINSELECT * FROM userinfo WHERE  userWeight='50' and userSight='5.2';

索引分析結(jié)果，索引命中（因?yàn)?userWeight=‘50’ ，滿足了最佳左前綴原則）：

那么如果我們把這兩個(gè)條件 換下前后順序呢？

EXPLAINSELECT * FROM userinfo WHERE userSight='5.2' AND userWeight='50';

索引分析結(jié)果，索引命中：

這是特意補(bǔ)充提一下的，這個(gè)最佳左前綴原則是對(duì)于建立的聯(lián)合索引里面字段的順序最左而言，不是sql語句寫的條件順序。

那么如果我們使用的是 userHeight 和 userSight 呢？

EXPLAIN SELECT * FROM userinfo WHERE  userHeight='180' and userSight='5.2';

這個(gè)不做解釋，連最左的邊都沒粘上，還想命中索引？ 怎么敢的。

失效七

使用了 is not null 、 is null ，索引不生效

前排說明，這個(gè)失效場(chǎng)景并不是如表述所言！情況以下分析！

請(qǐng)看示例表數(shù)據(jù)，里面userName里面，包含了一條數(shù)據(jù)為NULL的情況：

然后SQL使用了 is not null ：

EXPLAIN SELECT * FROM userinfo WHERE userName is not null;

索引分析結(jié)果，索引未命中：

那么SQL使用了 is null：

EXPLAIN SELECT * FROM userinfo WHERE userName is  null;

索引分析結(jié)果，索引命中：

為什么，為什么索引命中了？ 不是說使用了 is null 、is not null 會(huì)失效么？

這段話摘自mysql官方文檔，is null 不會(huì)影響索引的使用：

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

那可能有很多看官，確實(shí)遇到了使用is null 失效的問題，就會(huì)很疑惑。

是玄學(xué)么？ 并不是。這就是本文想補(bǔ)充的一種索引失效的場(chǎng)景：

當(dāng)執(zhí)行的sql語句，mysql認(rèn)為掃描全表都比使用索引快的時(shí)候，那么索引就不會(huì)被使用！

也就是mysql會(huì)去計(jì)算查詢成本，那個(gè)成本低就選擇哪種方式。

PS： 接下來我跑題了， 非常啰嗦，我跑出了 回表，聚集索引 ，非 聚集索引，索引覆蓋 這些不符合文章主題的內(nèi)容。不想閱讀的看官可以點(diǎn)個(gè)贊就走了。

ok，繼續(xù)回到跑題，那啥時(shí)候mysql才會(huì)有這種認(rèn)為呢？

通常mysql有這種想法的時(shí)候，大多數(shù)情況是因?yàn)樵搒ql 查詢中回表數(shù)量太多。

那么引申出一個(gè)概念，回表。

什么是回表（跑題，但是無所謂了）？

想深入了解的看官，可以去摸索深入一下，這里我用我的小白文給大家簡(jiǎn)單講一講。

結(jié)合實(shí)例講解（對(duì)了，該篇文章都是基于引擎InnoDB的）：

假如咱們現(xiàn)在有一張表，里面有 id （主鍵），userName（索引），userPorfession （無索引）

首先為了更好地解釋回表， 我逼不得已又得引出兩個(gè) 新概念 聚集索引 和 非 聚集索引 。

（哈哈麻煩了，感覺跑題越跑越遠(yuǎn)了，無所謂了）

聚集索引 ：

通常來說，就是咱們表的主鍵。

那如果這張表沒有主鍵，那么第一個(gè)創(chuàng)建的唯一非空索引，就是該表的聚集索引 。

那么你說，又沒主鍵，又沒創(chuàng)建唯一非空索引，我明白，你想搞事。 但是mysql不會(huì)被你搞，這種情況mysql會(huì)隱式地為該表創(chuàng)建一個(gè)聚集索引 ，具體是啥，創(chuàng)建規(guī)則，我就不細(xì)說了。（因?yàn)閷?duì)于我來說，我不想看到這種情況出現(xiàn)，就算沒必要很多時(shí)候我也會(huì)去建一個(gè)主鍵id作為偽列。）

好了，聚集索引 我們已經(jīng)了解它的由來。

那么它的作用：

簡(jiǎn)單一點(diǎn)來說，它就是這表數(shù)據(jù)的老大，只要命中這個(gè)家伙，其他字段它都能給你找齊，也就是說這個(gè)家伙是指向了整行數(shù)據(jù)的。

非聚集索引 ：

其他索引，類似這個(gè)表里，userName的索引 就是 非聚集索引 。

這個(gè)家伙的作用：

你找它，它只能幫你找到它的老大 聚集索引（主鍵），也就是這個(gè)家伙是指向聚集索引的。

ok，講到這里，回到我們的表示例，講講啥是回表場(chǎng)景： 

id （主鍵），userName（索引），userPorfession （無索引）

執(zhí)行SQL：

SELECT id , userName, userProfession  FROM userinfo  WHERE id  = 7;

執(zhí)行計(jì)劃分析：

這時(shí)候，索引命中的是id 主鍵 。

沒錯(cuò)，命中了老大， 不會(huì)回表。因?yàn)樵诶洗蟮乃饕龢淅锩?，啥玩意都能給你找齊了。

再看執(zhí)行SQL：

SELECT id , userName, userProfession FROM userinfo WHERE userName  = '劉二';

執(zhí)行計(jì)劃分析：

這時(shí)候，命中的不是老大，是一個(gè) 非聚集索引 , 這時(shí)候需要回表。

為什么?。?因?yàn)槲覀兪褂玫氖莝elect * ，意思是我們還得查找 userPorfession 字段數(shù)據(jù)。

那么現(xiàn)在命中了userName 的索引 nameIndex，它還能幫我們找到老大 id，但是它無法幫我們直接找到 userPorfession 。

其實(shí)這里涉及到一個(gè)概念，叫 索引覆蓋 。

什么是索引覆蓋？ （不能再跑題了，這里我就提一句吧，就是從索引樹里面指向的數(shù)據(jù)字段里已經(jīng)包含了select xx，xxx 這些字段，那么就是索引數(shù)據(jù)已經(jīng)夠用了，沒必要回表查額外的數(shù)據(jù)了。）

快速看圖了解：

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

回歸剛剛講到的，userName ，id 我們都能找到，但是為了找 userPorfession ，我們只能在找到id之后，再根據(jù)id再去找一遍主鍵的索引樹數(shù)據(jù)，找出與id綁定的userPorfession，這種情形就是 回表 。

什么叫回表？好的這里簡(jiǎn)單講述完畢。

那又又又回到我們最早提到的問題，

mysql認(rèn)為掃描全表都比使用索引快的時(shí)候，那么索引就不會(huì)被使用。

而通常mysql有這種想法的時(shí)候，大多數(shù)情況是因?yàn)樵搒ql 查詢中回表數(shù)量太多。

那么怎么盡可能避免這種情形呢？

剛剛已經(jīng)講了回表是啥原因?qū)е碌牧?，那么為了盡可能避免這種情形，那就是：

• 1.使用聚集索引 也就是主鍵進(jìn)行查找
• 2. select 查找的字段列 被 命中的索引的索引樹里的數(shù)據(jù) 包含，也就是索引覆蓋。
• 3.升級(jí)索引

1.2都看懂了。3是什么意思呢？

就是說針對(duì)某些場(chǎng)景，例如目前SQL：

SELECT id , userName, userProfession FROM userName  = '劉二';

因?yàn)槟壳癷d有主鍵索引，userName有索引，但是 userProfession沒索引導(dǎo)致需要回表。

那么我們把userName的單索引升級(jí)為 聯(lián)合索引 （userName，userProfession）。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論