1. 首頁 > 生活百科 > >

MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。( 二 )

生活百科


  • 先在「二級索引」的 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 '%林';
MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
如果是查詢 name 前綴為林的用戶 ， 那么就會走索引掃描 ， 執行計劃中的 type=range 表示走索引掃描，key=index_name 看到實際走了 index_name 索引：
// name 字段為二級索引select * from t_user where name like '林%';
MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
為什么 like 關鍵字左或者左右模糊匹配無法走索引呢？
因為索引 B+ 樹是按照「索引值」有序排列存儲的，只能根據前綴進行比較 。
舉個例子，下面這張二級索引圖，是以 name 字段有序排列存儲的 。
MySQL 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
假設我們要查詢 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 索引失效-模糊查詢，最左匹配原則，OR條件等。

文章插圖
為什么對索引使用函數 ， 就無法走索引了呢？
因為索引保存的是索引字段的原始值 ， 而不是經過函數計算后的值，自然就沒辦法走索引了 。
不過，從 MySQL 8.0 開始，索引特性增加了函數索引，即可以針對函數計算后的值建立一個索引，也就是說該索引的值是函數計算后的值，所以就可以通過掃描索引來查詢數據 。
舉個例子，我通過下面這條語句，對 length(name) 的計算結果建立一個名為 idx_name_length 的索引 。
alter table t_user add key idx_name_length ((length(name)));

推薦閱讀