誰還沒碰過索引失效呢

在工作中，如果我們想提高一條語句查詢速度，通常都會想對字段建立索引。

但是索引并不是萬能的。建立了索引，并不意味著任何查詢語句都能走索引掃描。

稍不注意，可能你寫的查詢語句是會導致索引失效，從而走了全表掃描，雖然查詢的結(jié)果沒問題，但是查詢的性能大大降低。

今天就來跟大家盤一盤，常見的 6 種會發(fā)生索引失效的場景。

不僅會用實驗案例給大家說明，也會清楚每個索引失效的原因。

發(fā)車！

索引存儲結(jié)構(gòu)長什么樣？

我們先來看看索引存儲結(jié)構(gòu)長什么樣？因為只有知道索引的存儲結(jié)構(gòu)，才能更好的理解索引失效的問題。

索引的存儲結(jié)構(gòu)跟 MySQL 使用哪種存儲引擎有關(guān)，因為存儲引擎就是負責將數(shù)據(jù)持久化在磁盤中，而不同的存儲引擎采用的索引數(shù)據(jù)結(jié)構(gòu)也會不相同。

MySQL 默認的存儲引擎是 InnoDB，它采用 B+Tree 作為索引的數(shù)據(jù)結(jié)構(gòu)，至于為什么選擇 ?B+ 樹作為索引的數(shù)據(jù)結(jié)構(gòu) ，詳細的分析可以看我這篇文章：為什么 MySQL 喜歡 B+ 樹？

在創(chuàng)建表時，InnoDB 存儲引擎默認會創(chuàng)建一個主鍵索引，也就是聚簇索引，其它索引都屬于二級索引。

MySQL 的 MyISAM 存儲引擎支持多種索引數(shù)據(jù)結(jié)構(gòu)，比如 B+ 樹索引、R 樹索引、Full-Text 索引。MyISAM 存儲引擎在創(chuàng)建表時，創(chuàng)建的主鍵索引默認使用的是 B+ 樹索引。

雖然，InnoDB 和 MyISAM 都支持 B+ 樹索引，但是它們數(shù)據(jù)的存儲結(jié)構(gòu)實現(xiàn)方式不同。不同之處在于：

• InnoDB 存儲引擎：B+ 樹索引的葉子節(jié)點保存數(shù)據(jù)本身；

• MyISAM 存儲引擎：B+ 樹索引的葉子節(jié)點保存數(shù)據(jù)的物理地址；

接下來，我舉個例子，給大家展示下這兩種存儲引擎的索引存儲結(jié)構(gòu)的區(qū)別。

這里有一張 t_user 表，其中 id 字段為主鍵索引，其他都是普通字段。

如果使用的是 MyISAM 存儲引擎，B+ 樹索引的葉子節(jié)點保存數(shù)據(jù)的物理地址，即用戶數(shù)據(jù)的指針，如下圖：

如果使用的是 InnoDB 存儲引擎， B+ 樹索引的葉子節(jié)點保存數(shù)據(jù)本身，如下圖所示：

InnoDB 存儲引擎根據(jù)索引類型不同，分為聚簇索引（上圖就是聚簇索引）和二級索引。它們區(qū)別在于，聚簇索引的葉子節(jié)點存放的是實際數(shù)據(jù)，所有完整的用戶數(shù)據(jù)都存放在聚簇索引的葉子節(jié)點，而二級索引的葉子節(jié)點存放的是主鍵值，而不是實際數(shù)據(jù)。

如果將 name 字段設(shè)置為普通索引，那么這個二級索引長下圖這樣，葉子節(jié)點僅存放主鍵值。

知道了 InnoDB 存儲引擎的聚簇索引和二級索引的存儲結(jié)構(gòu)后，接下來舉幾個查詢語句，說下查詢過程是怎么選擇用哪個索引類型的。

在我們使用「主鍵索引」字段作為條件查詢的時候，如果要查詢的數(shù)據(jù)都在「聚簇索引」的葉子節(jié)點里，那么就會在「聚簇索引」中的 B+ 樹檢索到對應(yīng)的葉子節(jié)點，然后直接讀取要查詢的數(shù)據(jù)。如下面這條語句：

//?id?字段為主鍵索引
select?*?from?t_user?where?id=1;

在我們使用「二級索引」字段作為條件查詢的時候，如果要查詢的數(shù)據(jù)都在「聚簇索引」的葉子節(jié)點里，那么需要檢索兩顆B+樹：

• 先在「二級索引」的 B+ 樹找到對應(yīng)的葉子節(jié)點，獲取主鍵值；

• 然后用上一步獲取的主鍵值，在「聚簇索引」中的 B+ 樹檢索到對應(yīng)的葉子節(jié)點，然后獲取要查詢的數(shù)據(jù)。

上面這個過程叫做回表，如下面這條語句：

//?name?字段為二級索引
select?*?from?t_user?where?name="林某";

在我們使用「二級索引」字段作為條件查詢的時候，如果要查詢的數(shù)據(jù)在「二級索引」的葉子節(jié)點，那么只需要在「二級索引」的 B+ 樹找到對應(yīng)的葉子節(jié)點，然后讀取要查詢的數(shù)據(jù)，這個過程叫做覆蓋索引。如下面這條語句：

//?name?字段為二級索引
select?id?from?t_user?where?name="林某";

上面這些查詢語句的條件都用到了索引列，所以在查詢過程都用上了索引。

但是并不意味著，查詢條件用上了索引列，就查詢過程就一定都用上索引，接下來我們再一起看看哪些情況會導致索引實現(xiàn)，而發(fā)生全表掃描。

首先說明下，下面的實驗案例，我使用的 MySQL 版本為 8.0.26。

對索引使用左或者左右模糊匹配

當我們使用左或者左右模糊匹配的時候，也就是 like %xx 或者 like %xx% 這兩種方式都會造成索引失效。

比如下面的 like 語句，查詢 name 后綴為「林」的用戶，執(zhí)行計劃中的 type=ALL 就代表了全表掃描，而沒有走索引。

//?name?字段為二級索引
select?*?from?t_user?where?name?like?'%林';

如果是查詢 name 前綴為林的用戶，那么就會走索引掃描，執(zhí)行計劃中的 type=range 表示走索引掃描，key=index_name 看到實際走了 index_name 索引：

//?name?字段為二級索引
select?*?from?t_user?where?name?like?'林%';

為什么 like 關(guān)鍵字左或者左右模糊匹配無法走索引呢？

因為索引 B+ 樹是按照「索引值」有序排列存儲的，只能根據(jù)前綴進行比較。

舉個例子，下面這張二級索引圖，是以 name 字段有序排列存儲的。

假設(shè)我們要查詢 name 字段前綴為「林」的數(shù)據(jù)，也就是 name like '林%'，掃描索引的過程：

• 首節(jié)點查詢比較：林這個字的拼音大小比首節(jié)點的第一個索引值中的陳字大，但是比首節(jié)點的第二個索引值中的周字小，所以選擇去節(jié)點2繼續(xù)查詢；

• 節(jié)點 2 查詢比較：節(jié)點2的第一個索引值中的陳字的拼音大小比林字小，所以繼續(xù)看下一個索引值，發(fā)現(xiàn)節(jié)點2有與林字前綴匹配的索引值，于是就往葉子節(jié)點查詢，即葉子節(jié)點4；

• 節(jié)點 4 查詢比較：節(jié)點4的第一個索引值的前綴符合林字，于是就讀取該行數(shù)據(jù)，接著繼續(xù)往右匹配，直到匹配不到前綴為林的索引值。

如果使用 name like '%林' 方式來查詢，因為查詢的結(jié)果可能是「陳林、張林、周林」等之類的，所以不知道從哪個索引值開始比較，于是就只能通過全表掃描的方式來查詢。

想要更詳細了解 InnoDB 的 B+ 樹查詢過程，可以看我寫的這篇：B+ 樹里的節(jié)點里存放的是什么呢？查詢數(shù)據(jù)的過程又是怎樣的？

對索引使用函數(shù)

有時候我們會用一些 MySQL 自帶的函數(shù)來得到我們想要的結(jié)果，這時候要注意了，如果查詢條件中對索引字段使用函數(shù)，就會導致索引失效。

比如下面這條語句查詢條件中對 name 字段使用了 LENGTH 函數(shù)，執(zhí)行計劃中的 type=ALL，代表了全表掃描：

//?name?為二級索引
select?*?from?t_user?where?length(name)=6;

為什么對索引使用函數(shù)，就無法走索引了呢？

因為索引保存的是索引字段的原始值，而不是經(jīng)過函數(shù)計算后的值，自然就沒辦法走索引了。

不過，從 MySQL 8.0 開始，索引特性增加了函數(shù)索引，即可以針對函數(shù)計算后的值建立一個索引，也就是說該索引的值是函數(shù)計算后的值，所以就可以通過掃描索引來查詢數(shù)據(jù)。

舉個例子，我通過下面這條語句，對 length(name) 的計算結(jié)果建立一個名為 idx_name_length 的索引。

alter?table?t_user?add?key?idx_name_length?((length(name)));

然后我再用下面這條查詢語句，這時候就會走索引了。

對索引進行表達式計算

在查詢條件中對索引進行表達式計算，也是無法走索引的。

比如，下面這條查詢語句，執(zhí)行計劃中 type = ALL，說明是通過全表掃描的方式查詢數(shù)據(jù)的：

explain?select?*?from?t_user?where?id?+?1?=?10;

但是，如果把查詢語句的條件改成 where id ?= 10 - 1，這樣就不是在索引字段進行表達式計算了，于是就可以走索引查詢了。

為什么對索引進行表達式計算，就無法走索引了呢？

原因跟對索引使用函數(shù)差不多。

因為索引保存的是索引字段的原始值，而不是 id + 1 表達式計算后的值，所以無法走索引，只能通過把索引字段的取值都取出來，然后依次進行表達式的計算來進行條件判斷，因此采用的就是全表掃描的方式。

有的同學可能會說，這種對索引進行簡單的表達式計算，在代碼特殊處理下，應(yīng)該是可以做到索引掃描的，比方將 ?id + 1 = 10 變成 id ?= 10 - 1。

是的，是能夠?qū)崿F(xiàn)，但是 MySQL 還是偷了這個懶，沒有實現(xiàn)。

我的想法是，可能也是因為，表達式計算的情況多種多樣，每種都要考慮的話，代碼可能會很臃腫，所以干脆將這種索引失效的場景告訴程序員，讓程序員自己保證在查詢條件中不要對索引進行表達式計算。

對索引隱式類型轉(zhuǎn)換

如果索引字段是字符串類型，但是在條件查詢中，輸入的參數(shù)是整型的話，你會在執(zhí)行計劃的結(jié)果發(fā)現(xiàn)這條語句會走全表掃描。

我在原本的 t_user 表增加了 phone 字段，是二級索引且類型是 varchar。

然后我在條件查詢中，用整型作為輸入?yún)?shù)，此時執(zhí)行計劃中 type = ALL，所以是通過全表掃描來查詢數(shù)據(jù)的。

select?*?from?t_user?where?phone?=?1300000001;

但是如果索引字段是整型類型，查詢條件中的輸入?yún)?shù)即使字符串，是不會導致索引實現(xiàn)，還是可以走索引掃描。

我們再看第二個例子，id 是整型，但是下面這條語句還是走了索引掃描的。

?explain?select?*?from?t_user?where?id?=?'1';

為什么第一個例子會導致索引失效，而第二例子不會呢？

要明白這個原因，首先我們要知道 MySQL 的數(shù)據(jù)類型轉(zhuǎn)換規(guī)則是什么？就是看 MySQL 是會將字符串轉(zhuǎn)成數(shù)字處理，還是將數(shù)字轉(zhuǎn)換成字符串處理。

我在看《mysql45講的時候》看到一個簡單的測試方式，就是通過 select “10” > 9 的結(jié)果來知道MySQL 的數(shù)據(jù)類型轉(zhuǎn)換規(guī)則是什么：

• 如果規(guī)則是 MySQL 會將自動「字符串」轉(zhuǎn)換成「數(shù)字」，就相當于 select 10 > 9，這個就是數(shù)字比較，所以結(jié)果應(yīng)該是 1；

• 如果規(guī)則是 MySQL 會將自動「數(shù)字」轉(zhuǎn)換成「字符串」，就相當于 select "10" > "9"，這個是字符串比較，字符串比較大小是逐位從高位到低位逐個比較（按ascii碼） ，那么"10"字符串相當于 “1”和“0”字符的組合，所以先是拿 “1” 字符和 “9” 字符比較，因為 “1” 字符比 “9” 字符小，所以結(jié)果應(yīng)該是 0。

在 MySQL 中，執(zhí)行的結(jié)果如下圖：

上面的結(jié)果為 1，說明 MySQL 在遇到字符串和數(shù)字比較的時候，會自動把字符串轉(zhuǎn)為數(shù)字，然后再進行比較。

前面的例子一中的查詢語句，我也跟大家說了是會走全表掃描：

//例子一的查詢語句
select?*?from?t_user?where?phone?=?1300000001;

這是因為 phone 字段為字符串，所以 MySQL 要會自動把字符串轉(zhuǎn)為數(shù)字，所以這條語句相當于：

select?*?from?t_user?where?CAST(phone?AS?signed?int)?=?1300000001;

可以看到，CAST 函數(shù)是作用在了 phone 字段，而 phone 字段是索引，也就是對索引使用了函數(shù)！而前面我們也說了，對索引使用函數(shù)是會導致索引失效的。

例子二中的查詢語句，我跟大家說了是會走索引掃描：

//例子二的查詢語句
select?*?from?t_user?where?id?=?"1";

這時因為字符串部分是輸入?yún)?shù)，也就需要將字符串轉(zhuǎn)為數(shù)字，所以這條語句相當于：

select?*?from?t_user?where?id?=?CAST("1"?AS?signed?int);

可以看到，索引字段并沒有用任何函數(shù)，CAST 函數(shù)是用在了輸入?yún)?shù)，因此是可以走索引掃描的。

聯(lián)合索引非最左匹配

對主鍵字段建立的索引叫做聚簇索引，對普通字段建立的索引叫做二級索引。

那么多個普通字段組合在一起創(chuàng)建的索引就叫做聯(lián)合索引，也叫組合索引。

創(chuàng)建聯(lián)合索引時，我們需要注意創(chuàng)建時的順序問題，因為聯(lián)合索引 (x, y, z) 和 (z, y, x) 在使用的時候會存在差別。

聯(lián)合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優(yōu)先的方式進行索引的匹配。

比如，如果創(chuàng)建了一個 (a, b, c) 聯(lián)合索引，如果查詢條件是以下這幾種，就可以匹配上聯(lián)合索引：

• where a=1；

• where a=1 and b=2 and c=3；

• where a=1 and b=2；

需要注意的是，因為有查詢優(yōu)化器，所以 x 字段在 where 子句的順序并不重要。

但是，如果查詢條件是以下這幾種，因為不符合最左匹配原則，所以就無法匹配上聯(lián)合索引，聯(lián)合索引就會失效:

• where b=2；

• where c=3；

• where b=2 and c=3；

有一個比較特殊的查詢條件：where a = 1 and c = 3 ，符合最左匹配嗎？

這種其實嚴格意義上來說是屬于索引截斷，不同版本處理方式也不一樣。

MySQL 5.5 的話，前面 a 會走索引，在聯(lián)合索引找到主鍵值后，開始回表，到主鍵索引讀取數(shù)據(jù)行，然后再比對 z 字段的值。

從 MySQL5.6 之后，有一個索引下推功能，可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。

大概原理是：截斷的字段會被下推到存儲引擎層進行條件判斷（因為 c 字段的值是在 (a, b, c) 聯(lián)合索引里的），然后過濾出符合條件的數(shù)據(jù)后再返回給 Server 層。由于在引擎層就過濾掉大量的數(shù)據(jù)，無需再回表讀取數(shù)據(jù)來進行判斷，減少回表次數(shù)，從而提升了性能。

比如下面這條 where a = 1 and c = 0?語句，我們可以從執(zhí)行計劃中的 Extra=Using index condition 使用了索引下推功能。

為什么聯(lián)合索引不遵循最左匹配原則就會失效？

原因是，在聯(lián)合索引的情況下，數(shù)據(jù)是按照索引第一列排序，第一列數(shù)據(jù)相同時才會按照第二列排序。

也就是說，如果我們想使用聯(lián)合索引中盡可能多的列，查詢條件中的各個列必須是聯(lián)合索引中從最左邊開始連續(xù)的列。如果我們僅僅按照第二列搜索，肯定無法走索引。

WHERE 子句中的 OR

在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 后的條件列不是索引列，那么索引會失效。

舉個例子，比如下面的查詢語句，id 是主鍵，age 是普通列，從執(zhí)行計劃的結(jié)果看，是走了全表掃描。

select?*?from?t_user?where?id?=?1?or?age?=?18;

這是因為 OR 的含義就是兩個只要滿足一個即可，因此只有一個條件列是索引列是沒有意義的，只要有條件列不是索引列，就會進行全表掃描。

要解決辦法很簡單，將 age 字段設(shè)置為索引即可。

可以看到 type=index merge， index merge 的意思就是對 id 和 age 分別進行了掃描，然后將這兩個結(jié)果集進行了合并，這樣做的好處就是避免了全表掃描。

總結(jié)

今天給大家介紹了 6 種會發(fā)生索引失效的情況：

• 當我們使用左或者左右模糊匹配的時候，也就是 like %xx 或者 like %xx% 這兩種方式都會造成索引失效；

• 當我們在查詢條件中對索引列使用函數(shù)，就會導致索引失效。

• 當我們在查詢條件中對索引列進行表達式計算，也是無法走索引的。

• MySQL 在遇到字符串和數(shù)字比較的時候，會自動把字符串轉(zhuǎn)為數(shù)字，然后再進行比較。如果字符串是索引列，而條件語句中的輸入?yún)?shù)是數(shù)字的話，那么索引列會發(fā)生隱式類型轉(zhuǎn)換，由于隱式類型轉(zhuǎn)換是通過 CAST 函數(shù)實現(xiàn)的，等同于對索引列使用了函數(shù)，所以就會導致索引失效。

• 聯(lián)合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優(yōu)先的方式進行索引的匹配，否則就會導致索引失效。

• 在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 后的條件列不是索引列，那么索引會失效。

最后留一個很有意思的思考題給大家。

• 題目1：一個表有多個字段，其中 name 是索引字段，其他非索引，id 擁有自增主鍵索引。

• 題目2：一個表有2個字段，其中 name 是索引字段，id 擁有自增主鍵索引。

上面兩張表，分別執(zhí)行以下查詢語句：

• select * from s where name like "xxx"

• select * from s where name like "xxx%"

• select * from s where name like "%xxx"

• select * from s where name like "%xxx%"

針對題目 1 和題目 2 的數(shù)據(jù)表，哪些觸發(fā)索引查詢，哪些沒有？