2.3.4. 索引失效

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

使用左或者左右模糊匹配的時候，也就是 like %xx 或者 like %xx% 這兩種方式都會造成索引失效。但是如果前綴是確定的那么就可以使用到索引，例如 name like '許%'。

因為索引 B+ 樹是按照「索引值」有序排列存儲的，只能根據前綴進行比較。如果使用 name like '%許' 方式來查詢，查詢的結果可能是「小許、大許、老許」等之類的，存儲引擎不知道從哪個索引值開始比較，于是就只能通過全表掃描的方式來查詢。

1. 對索引使用函數

查詢條件中對索引字段使用函數，就會導致索引失效。因為索引保存的是索引字段的原始值，而不是經過函數計算后的值，自然就沒辦法走索引了。

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

-- 對 length(name) 的計算結果建立一個名為 idx_name_length 的索引。
alter table t_user add key idx_name_length ((length(name)));

1. 對索引進行表達式計算

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

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

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

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

個人覺得這種方式完全可以不使用沒必要這么麻煩。

1. 對索引隱式類型轉換

索引字段是字符串類型，查詢條件種輸入的參數是整型，在執行計劃里發現這條語句會走全表掃描。

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

-- 例1、索引是字符串 參數是整型
select * from t_user where phone = 1300000001;
-- 例2、索引是整型 參數是字符串
select * from t_user where id = '1';

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

-- 例1
select * from t_user where CAST(phone AS signed int) = 1300000001;-- 例2
select * from t_user where id = CAST("1" AS signed int);

例1，CAST 函數是作用在了 phone 字段，而 phone 字段是索引，也就是對索引使用了函數！

例2，索引字段并沒有用任何函數，CAST 函數是用在了輸入參數，所以可以走索引掃描的。

1. 組合索引非最左匹配

如果創建了一個 (a, b, c) 組合索引，因為有查詢優化器，所以 a 字段在 where 子句的順序并不重要。

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

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

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

MySQL 5.5 的話，前面 a 會走索引，在聯合索引找到主鍵值后，開始回表，到主鍵索引讀取數據行，Server 層從存儲引擎層獲取到數據行后，然后在 Server 層再比對 c 字段的值。

從 MySQL 5.6 之后，有一個索引下推功能，可以在存儲引擎層進行索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，再返還給 Server 層，從而減少回表次數。

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

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

1. WHERE 子句中的 OR

在 WHERE 子句中，如果在 OR 前的條件列是索引列，而在 OR 后的條件列不是索引列，那么索引會失效。只有一個條件列是索引列是沒有意義的，只要有條件列不是索引列，就會進行全表掃描，所以需要兩個條件都是索引列。