前言

該篇內(nèi)容：

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

失效一

查詢的字段列未添加索引

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

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

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

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

分析結(jié)果：

失效二

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

請看示例，表內(nèi) userCardNum 列 存入的是職工的工號 ：

userCardNum 設置的是varchar 類型 ：

給 userCardNum 添加了唯一索引 ：

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

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

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

索引失效，傳入值沒有帶 ‘ ’ 單引號：

EXPLAIN SELECT * FROM userinfo WHERE userCardNum= 10011;

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

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

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

因為一部分人在理解這種情況 有錯誤的思想，理解為 涉及類型轉(zhuǎn)換 ，以為是因為單純的字段類型不對應 導致索引失效，這里必須補充一下一個示例：

字段列 userAge：

userAge類型為 int ：

給 userAge添加了索引 ：

SQL使用傳入值，添加了引號 ：

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

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

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

失效三

使用 like 進行 左模糊匹配查找 ‘%XXX’

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

給userName 添加索引：

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

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

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

擴展補充，當使用LIKE ，索引就必然失效么？

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

請看 LIKE 右匹配：

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

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

失效四

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

請看示例SQL ，索引字段使用了計算：

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

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

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

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

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

失效五

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

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

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

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

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

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

例如 userAge 我們使用了索引，

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

這樣會命中索引么？

注意了，分情況，因為我們的userAge添加的不是唯一索引，因為年齡嘛，總會有重復：

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

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

可以看的，索引命中了。

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

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

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

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

配上使用 UNION ALL的方式：

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

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

失效六

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

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

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

注意順序，最左為 userWeight

這時候，如果SQL 在使用這些字段索引查找時，先看單個字段查找：

單個使用userWeight ：

EXPLAINSELECT * FROM userinfo WHERE  userWeight='50';

索引分析結(jié)果，索引命中，因為滿足了最佳做前綴原則：

那么我們換成單個使用 userHeight，

EXPLAINSELECT * FROM userinfo WHERE  userHeight='180' ;

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

同樣換成 userSight 也是一樣，索引失效了，因為這種情形就是沒有滿足最佳做前綴原則。

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

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

使用 userWeight 和 userSight：

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

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

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

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

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

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

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

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

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

失效七

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

前排說明，這個失效場景并不是如表述所言！情況以下分析！

請看示例表數(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 會失效么？

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

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.

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

是玄學么？ 并不是。這就是本文想補充的一種索引失效的場景：

當執(zhí)行的sql語句，mysql認為掃描全表都比使用索引快的時候，那么索引就不會被使用！

也就是mysql會去計算查詢成本，那個成本低就選擇哪種方式。

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

ok，繼續(xù)回到跑題，那啥時候mysql才會有這種認為呢？

通常mysql有這種想法的時候，大多數(shù)情況是因為該sql 查詢中回表數(shù)量太多。

那么引申出一個概念，回表。

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

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

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

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

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

（哈哈麻煩了，感覺跑題越跑越遠了，無所謂了）

聚集索引 ：

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

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

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

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

那么它的作用：

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

非聚集索引 ：

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

這個家伙的作用：

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

ok，講到這里，回到我們的表示例，講講啥是回表場景： 

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

執(zhí)行SQL：

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

執(zhí)行計劃分析：

這時候，索引命中的是id 主鍵 。

沒錯，命中了老大， 不會回表。因為在老大的索引樹里面，啥玩意都能給你找齊了。

再看執(zhí)行SQL：

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

執(zhí)行計劃分析：

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

為什么??？ 因為我們使用的是select * ，意思是我們還得查找 userPorfession 字段數(shù)據(jù)。

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

其實這里涉及到一個概念，叫 索引覆蓋 。

什么是索引覆蓋？ （不能再跑題了，這里我就提一句吧，就是從索引樹里面指向的數(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，這種情形就是 回表 。

什么叫回表？好的這里簡單講述完畢。

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

mysql認為掃描全表都比使用索引快的時候，那么索引就不會被使用。

而通常mysql有這種想法的時候，大多數(shù)情況是因為該sql 查詢中回表數(shù)量太多。

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

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

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

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

就是說針對某些場景，例如目前SQL：

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

因為目前id有主鍵索引，userName有索引，但是 userProfession沒索引導致需要回表。

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

總結(jié)

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

最新評論