盤一盤常見的6種索引失效情況
摘要：今天就來跟大家盤一盤，常見的 6 種會發生索引失效的場景。
本文分享自華為雲社區《面試官：聊聊索引失效？失效的原因是什麼？》，作者：小林coding 。
在工作中，如果我們想提高一條語句查詢速度，通常都會想對欄位建立索引。
但是索引並不是萬能的。建立了索引，並不意味著任何查詢語句都能走索引掃描。
稍不注意，可能你寫的查詢語句是會導致索引失效，從而走了全表掃描，雖然查詢的結果沒問題，但是查詢的性能大大降低。
今天就來跟大家盤一盤，常見的 6 種會發生索引失效的場景。
不僅會用實驗案例給大家說明，也會清楚每個索引失效的原因。
發車！
索引存儲結構長什麼樣？
我們先來看看索引存儲結構長什麼樣？因為只有知道索引的存儲結構，才能更好的理解索引失效的問題。
索引的存儲結構跟 MySQL 使用哪種存儲引擎有關，因為存儲引擎就是負責將數據持久化在磁碟中，而不同的存儲引擎採用的索引數據結構也會不相同。
MySQL 默認的存儲引擎是 InnoDB，它採用 B+Tree 作為索引的數據結構，至於為什麼選擇 B+ 樹作為索引的數據結構 ，詳細的分析可以看我這篇文章：為什麼 MySQL 喜歡 B+ 樹？
在創建表時，InnoDB 存儲引擎默認會創建一個主鍵索引，也就是聚簇索引，其它索引都屬於二級索引。
MySQL 的 MyISAM 存儲引擎支援多種索引數據結構，比如 B+ 樹索引、R 樹索引、Full-Text 索引。MyISAM 存儲引擎在創建表時，創建的主鍵索引默認使用的是 B+ 樹索引。
雖然，InnoDB 和 MyISAM 都支援 B+ 樹索引，但是它們數據的存儲結構實現方式不同。不同之處在於：
- InnoDB 存儲引擎：B+ 樹索引的葉子節點保存數據本身；
- MyISAM 存儲引擎：B+ 樹索引的葉子節點保存數據的物理地址；
接下來，我舉個例子，給大家展示下這兩種存儲引擎的索引存儲結構的區別。
這裡有一張 t_user 表，其中 id 欄位為主鍵索引，其他都是普通欄位。
如果使用的是 MyISAM 存儲引擎，B+ 樹索引的葉子節點保存數據的物理地址，即用戶數據的指針，如下圖：
如果使用的是 InnoDB 存儲引擎， B+ 樹索引的葉子節點保存數據本身，如下圖所示：
InnoDB 存儲引擎根據索引類型不同，分為聚簇索引（上圖就是聚簇索引）和二級索引。它們區別在於，聚簇索引的葉子節點存放的是實際數據，所有完整的用戶數據都存放在聚簇索引的葉子節點，而二級索引的葉子節點存放的是主鍵值，而不是實際數據。
如果將 name 欄位設置為普通索引，那麼這個二級索引長下圖這樣，葉子節點僅存放主鍵值。
知道了 InnoDB 存儲引擎的聚簇索引和二級索引的存儲結構後，接下來舉幾個查詢語句，說下查詢過程是怎麼選擇用哪個索引類型的。
在我們使用「主鍵索引」欄位作為條件查詢的時候，如果要查詢的數據都在「聚簇索引」的葉子節點裡，那麼就會在「聚簇索引」中的 B+ 樹檢索到對應的葉子節點，然後直接讀取要查詢的數據。如下面這條語句：
// id 欄位為主鍵索引 select * from t_user where id=1;
在我們使用「二級索引」欄位作為條件查詢的時候，如果要查詢的數據都在「聚簇索引」的葉子節點裡，那麼需要檢索兩顆B+樹：
- 先在「二級索引」的 B+ 樹找到對應的葉子節點，獲取主鍵值；
- 然後用上一步獲取的主鍵值，在「聚簇索引」中的 B+ 樹檢索到對應的葉子節點，然後獲取要查詢的數據。
上面這個過程叫做回表，如下面這條語句：
// name 欄位為二級索引 select * from t_user where name="林某";
在我們使用「二級索引」欄位作為條件查詢的時候，如果要查詢的數據在「二級索引」的葉子節點，那麼只需要在「二級索引」的 B+ 樹找到對應的葉子節點，然後讀取要查詢的數據，這個過程叫做覆蓋索引。如下面這條語句：
// name 欄位為二級索引 select id from t_user where name="林某";
上面這些查詢語句的條件都用到了索引列，所以在查詢過程都用上了索引。
但是並不意味著，查詢條件用上了索引列，就查詢過程就一定都用上索引，接下來我們再一起看看哪些情況會導致索引實現，而發生全表掃描。
首先說明下，下面的實驗案例，我使用的 MySQL 版本為 8.0.26。
對索引使用左或者左右模糊匹配
當我們使用左或者左右模糊匹配的時候，也就是 like %xx 或者 like %xx% 這兩種方式都會造成索引失效。
比如下面的 like 語句，查詢 name 後綴為「林」的用戶，執行計劃中的 type=ALL 就代表了全表掃描，而沒有走索引。
// name 欄位為二級索引 select * from t_user where name like '%林';
如果是查詢 name 前綴為林的用戶，那麼就會走索引掃描，執行計劃中的 type=range 表示走索引掃描，key=index_name 看到實際走了 index_name 索引：
// name 欄位為二級索引 select * from t_user where name like '林%';
為什麼 like 關鍵字左或者左右模糊匹配無法走索引呢？
因為索引 B+ 樹是按照「索引值」有序排列存儲的，只能根據前綴進行比較。
舉個例子，下面這張二級索引圖，是以 name 欄位有序排列存儲的。
假設我們要查詢 name 欄位前綴為「林」的數據，也就是 name like ‘林%’，掃描索引的過程：
- 首節點查詢比較：林這個字的拼音大小比首節點的第一個索引值中的陳字大，但是比首節點的第二個索引值中的周字小，所以選擇去節點2繼續查詢；
- 節點 2 查詢比較：節點2的第一個索引值中的陳字的拼音大小比林字小，所以繼續看下一個索引值，發現節點2有與林字前綴匹配的索引值，於是就往葉子節點查詢，即葉子節點4；
- 節點 4 查詢比較：節點4的第一個索引值的前綴符合林字，於是就讀取該行數據，接著繼續往右匹配，直到匹配不到前綴為林的索引值。
如果使用 name like ‘%林’ 方式來查詢，因為查詢的結果可能是「陳林、張林、周林」等之類的，所以不知道從哪個索引值開始比較，於是就只能通過全表掃描的方式來查詢。
想要更詳細了解 InnoDB 的 B+ 樹查詢過程，可以看我寫的這篇：B+ 樹里的節點裡存放的是什麼呢？查詢數據的過程又是怎樣的？
對索引使用函數
有時候我們會用一些 MySQL 自帶的函數來得到我們想要的結果，這時候要注意了，如果查詢條件中對索引欄位使用函數，就會導致索引失效。
比如下面這條語句查詢條件中對 name 欄位使用了 LENGTH 函數，執行計劃中的 type=ALL，代表了全表掃描：
// name 為二級索引 select * from t_user where length(name)=6;
為什麼對索引使用函數，就無法走索引了呢？
因為索引保存的是索引欄位的原始值，而不是經過函數計算後的值，自然就沒辦法走索引了。
不過，從 MySQL 8.0 開始，索引特性增加了函數索引，即可以針對函數計算後的值建立一個索引，也就是說該索引的值是函數計算後的值，所以就可以通過掃描索引來查詢數據。
舉個例子，我通過下面這條語句，對 length(name) 的計算結果建立一個名為 idx_name_length 的索引。
alter table t_user add key idx_name_length ((length(name)));
然後我再用下面這條查詢語句，這時候就會走索引了。
對索引進行表達式計算
在查詢條件中對索引進行表達式計算，也是無法走索引的。
比如，下面這條查詢語句，執行計劃中 type = ALL，說明是通過全表掃描的方式查詢數據的：
explain select * from t_user where id + 1 = 10;
但是，如果把查詢語句的條件改成 where id = 10 – 1，這樣就不是在索引欄位進行表達式計算了，於是就可以走索引查詢了。
為什麼對索引進行表達式計算，就無法走索引了呢？
原因跟對索引使用函數差不多。
因為索引保存的是索引欄位的原始值，而不是 id + 1 表達式計算後的值，所以無法走索引，只能通過把索引欄位的取值都取出來，然後依次進行表達式的計算來進行條件判斷，因此採用的就是全表掃描的方式。
有的同學可能會說，這種對索引進行簡單的表達式計算，在程式碼特殊處理下，應該是可以做到索引掃描的，比方將 id + 1 = 10 變成 id = 10 – 1。
是的，是能夠實現，但是 MySQL 還是偷了這個懶，沒有實現。
我的想法是，可能也是因為，表達式計算的情況多種多樣，每種都要考慮的話，程式碼可能會很臃腫，所以乾脆將這種索引失效的場景告訴程式設計師，讓程式設計師自己保證在查詢條件中不要對索引進行表達式計算。
對索引隱式類型轉換
如果索引欄位是字元串類型，但是在條件查詢中，輸入的參數是整型的話，你會在執行計劃的結果發現這條語句會走全表掃描。
我在原本的 t_user 表增加了 phone 欄位，是二級索引且類型是 varchar。
然後我在條件查詢中，用整型作為輸入參數，此時執行計劃中 type = ALL，所以是通過全表掃描來查詢數據的。
select * from t_user where phone = 1300000001;
但是如果索引欄位是整型類型，查詢條件中的輸入參數即使字元串，是不會導致索引失效，還是可以走索引掃描。
我們再看第二個例子，id 是整型，但是下面這條語句還是走了索引掃描的。
explain select * from t_user where id = '1';
為什麼第一個例子會導致索引失效，而第二例子不會呢？
要明白這個原因，首先我們要知道 MySQL 的數據類型轉換規則是什麼？就是看 MySQL 是會將字元串轉成數字處理，還是將數字轉換成字元串處理。
我在看《mysql45講的時候》看到一個簡單的測試方式，就是通過 select 「10」 > 9 的結果來知道MySQL 的數據類型轉換規則是什麼：
- 如果規則是 MySQL 會將自動「字元串」轉換成「數字」，就相當於 select 10 > 9，這個就是數字比較，所以結果應該是 1；
- 如果規則是 MySQL 會將自動「數字」轉換成「字元串」，就相當於 select 「10」 > 「9」，這個是字元串比較，字元串比較大小是逐位從高位到低位逐個比較（按ascii碼） ，那麼”10″字元串相當於 「1」和「0」字元的組合，所以先是拿 「1」 字元和 「9」 字元比較，因為 「1」 字元比 「9」 字元小，所以結果應該是 0。
在 MySQL 中，執行的結果如下圖：
上面的結果為 1，說明 MySQL 在遇到字元串和數字比較的時候，會自動把字元串轉為數字，然後再進行比較。
前面的例子一中的查詢語句，我也跟大家說了是會走全表掃描：
//例子一的查詢語句 select * from t_user where phone = 1300000001;
這是因為 phone 欄位為字元串，所以 MySQL 要會自動把字元串轉為數字，所以這條語句相當於：
select * from t_user where CAST(phone AS signed int) = 1300000001;
可以看到，CAST 函數是作用在了 phone 欄位，而 phone 欄位是索引，也就是對索引使用了函數！而前面我們也說了，對索引使用函數是會導致索引失效的。
例子二中的查詢語句，我跟大家說了是會走索引掃描：
//例子二的查詢語句 select * from t_user where id = "1";
這時因為字元串部分是輸入參數，也就需要將字元串轉為數字，所以這條語句相當於：
select * from t_user where id = CAST("1" AS signed int);
可以看到，索引欄位並沒有用任何函數，CAST 函數是用在了輸入參數，因此是可以走索引掃描的。
聯合索引非最左匹配
對主鍵欄位建立的索引叫做聚簇索引，對普通欄位建立的索引叫做二級索引。
那麼多個普通欄位組合在一起創建的索引就叫做聯合索引，也叫組合索引。
創建聯合索引時，我們需要注意創建時的順序問題，因為聯合索引 (a, b, c) 和 (c, b, a) 在使用的時候會存在差別。
聯合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優先的方式進行索引的匹配。
比如，如果創建了一個 (a, b, c) 聯合索引，如果查詢條件是以下這幾種，就可以匹配上聯合索引：
- where a=1；
- where a=1 and b=2 and c=3；
- where a=1 and b=2；
需要注意的是，因為有查詢優化器，所以 a 欄位在 where 子句的順序並不重要。
但是，如果查詢條件是以下這幾種，因為不符合最左匹配原則，所以就無法匹配上聯合索引，聯合索引就會失效:
- where b=2；
- where c=3；
- where b=2 and c=3；
有一個比較特殊的查詢條件：where a = 1 and c = 3 ，符合最左匹配嗎？
這種其實嚴格意義上來說是屬於索引截斷，不同版本處理方式也不一樣。
MySQL 5.5 的話，前面 a 會走索引，在聯合索引找到主鍵值後，開始回表，到主鍵索引讀取數據行，然後再比對 c 欄位的值。
從 MySQL5.6 之後，有一個索引下推功能，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。
大概原理是：截斷的欄位會被下推到存儲引擎層進行條件判斷（因為 c 欄位的值是在 (a, b, c) 聯合索引里的），然後過濾出符合條件的數據後再返回給 Server 層。由於在引擎層就過濾掉大量的數據，無需再回表讀取數據來進行判斷，減少回表次數，從而提升了性能。
比如下面這條 where a = 1 and c = 0 語句，我們可以從執行計劃中的 Extra=Using index condition 使用了索引下推功能。
為什麼聯合索引不遵循最左匹配原則就會失效？
原因是，在聯合索引的情況下，數據是按照索引第一列排序，第一列數據相同時才會按照第二列排序。
也就是說，如果我們想使用聯合索引中儘可能多的列，查詢條件中的各個列必須是聯合索引中從最左邊開始連續的列。如果我們僅僅按照第二列搜索，肯定無法走索引。
WHERE 子句中的 OR
在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 後的條件列不是索引列，那麼索引會失效。
舉個例子，比如下面的查詢語句，id 是主鍵，age 是普通列，從執行計劃的結果看，是走了全表掃描。
select * from t_user where id = 1 or age = 18;
這是因為 OR 的含義就是兩個只要滿足一個即可，因此只有一個條件列是索引列是沒有意義的，只要有條件列不是索引列，就會進行全表掃描。
要解決辦法很簡單，將 age 欄位設置為索引即可。
可以看到 type=index merge， index merge 的意思就是對 id 和 age 分別進行了掃描，然後將這兩個結果集進行了合併，這樣做的好處就是避免了全表掃描。
總結
今天給大家介紹了 6 種會發生索引失效的情況：
- 當我們使用左或者左右模糊匹配的時候，也就是 like %xx 或者 like %xx%這兩種方式都會造成索引失效；
- 當我們在查詢條件中對索引列使用函數，就會導致索引失效。
- 當我們在查詢條件中對索引列進行表達式計算，也是無法走索引的。
- MySQL 在遇到字元串和數字比較的時候，會自動把字元串轉為數字，然後再進行比較。如果字元串是索引列，而條件語句中的輸入參數是數字的話，那麼索引列會發生隱式類型轉換，由於隱式類型轉換是通過 CAST 函數實現的，等同於對索引列使用了函數，所以就會導致索引失效。
- 聯合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優先的方式進行索引的匹配，否則就會導致索引失效。
- 在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 後的條件列不是索引列，那麼索引會失效。
