哪些情況會導致索引失效？

查詢條件用上了索引列並不意味著查詢過程就一定會用上索引，接下來我們一起看看哪些情況會導致索引失效，而發生全表掃描。

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

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

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

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

如果是查詢 name 前綴為林的用戶，那麼就會走索引掃描。

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

為什麼 like 關鍵字左或者左右模糊匹配無法走索引呢？

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

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

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

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

Question

為什麼對索引使用函數，就無法走索引了呢？

因為索引保存的是索引字段的原始值，而不是經過函數計算後的值。

但是從 MySQL 8.0 開始，索引特性增加了函數索引，即可以針對函數計算後的值建立一個索引。

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

比如，下面這條查詢語句，執行計劃中 type = ALL，說明是通過全表掃描的方式查詢資料的：

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

Question

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

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

如果索引字段是字符串類型，但是在條件查詢中，輸入的參數是整數類型的話，這條語句則會走全表掃描。

但是如果索引字段是整數類型，查詢條件中的輸入參數即使是字符串，仍不會導致索引失效，還是可以走索引掃描。

MySQL 在遇到字符串和數字比較的時候，會自動把字符串轉為數字，然後再進行比較。

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

那麼多個普通字段組合在一起創建的索引就叫做聯合索引，也叫組合索引。

創建聯合索引時，我們需要注意創建時的順序問題，因為聯合索引 (a, b, c) 和 (c, b, a) 在使用的時候會存在差別。

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

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

需要注意的是，因為有查詢優化器，所以 a 字段在 where 子句的順序並不重要。

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

Question

為什麼聯合索引不遵循最左匹配原則就會失效？

在聯合索引的情況下，資料是按照索引第一列排序，第一列資料相同時才會按照第二列排序。

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

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

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

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

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