為了驗證 MySQL 中哪些情況下會導致索引失效，我們可以借助 explAIn 執行計劃來分析索引失效的具體場景。
explain 使用如下，只需要在查詢的 SQL 前面添加上 explain 關鍵字即可，如下圖所示：
而以上查詢結果的列中，我們最主要觀察 key 這一列，key 這一列表示實際使用的索引，如果為 NULL 則表示未使用索引，反之則使用了索引。
以上所有結果列說明如下：
-
id — 選擇標識符，id 越大優先級越高，越先被執行； -
select_type — 表示查詢的類型； -
table — 輸出結果集的表； -
partitions — 匹配的分區； -
type — 表示表的連接類型； -
possible_keys — 表示查詢時，可能使用的索引； -
key — 表示實際使用的索引； -
key_len — 索引字段的長度； -
ref— 列與索引的比較； -
rows — 大概估算的行數； -
filtered — 按表條件過濾的行百分比； -
Extra — 執行情況的描述和說明。
其中最重要的就是 type 字段，type 值類型如下：
-
all — 掃描全表數據；
-
index — 遍歷索引；
-
range — 索引范圍查找；
-
index_subquery — 在子查詢中使用 ref；
-
unique_subquery — 在子查詢中使用 eq_ref；
-
ref_or_null — 對 null 進行索引的優化的 ref；
-
fulltext — 使用全文索引；
-
ref — 使用非唯一索引查找數據；
-
eq_ref — 在 join 查詢中使用主鍵或唯一索引關聯；
-
const — 將一個主鍵放置到 where 后面作為條件查詢， MySQL 優化器就能把這次查詢優化轉化為一個常量，如何轉化以及何時轉化，這個取決于優化器，這個比 eq_ref 效率高一點。
創建測試表和數據
為了演示和測試那種情況下會導致索引失效，我們先創建一個測試表和相應的數據：
-- 創建表 drop table if exists student; create table student( id int primary key auto_increment comment '主鍵', sn varchar(32) comment '學號', name varchar(250) comment '姓名', age int comment '年齡', sex bit comment '性別', address varchar(250) comment '家庭地址', key idx_address (address), key idx_sn_name_age (sn,name,age) )ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 添加測試數據 insert into student(id,sn,name,age,sex,address) values(1,'cn001','張三',18,1,'高老莊'), (2,'cn002','李四',20,0,'花果山'), (3,'cn003','王五',50,1,'水簾洞');當前表中總共有 3 個索引，如下圖所示：
PS：本文以下內容基于 MySQL 5.7 InnoDB 數據引擎下。
索引失效情況1：非最左匹配
最左匹配原則指的是，以最左邊的為起點字段查詢可以使用聯合索引，否則將不能使用聯合索引。我們本文的聯合索引的字段順序是 sn + name + age，我們假設它們的順序是 A + B + C，以下聯合索引的使用情況如下：
從上述結果可以看出，如果是以最左邊開始匹配的字段都可以使用上聯合索引，比如：
-
A+B+C
-
A+B
-
A+C
其中：A 等于字段 sn，B 等于字段 name，C 等于字段 age。
而 B+C 卻不能使用到聯合索引，這就是最左匹配原則。
索引失效情況2：錯誤模糊查詢
模糊查詢 like 的常見用法有 3 種：
-
模糊匹配后面任意字符：like '張%' -
模糊匹配前面任意字符：like '%張' -
模糊匹配前后任意字符：like '%張%'
而這 3 種模糊查詢中只有第 1 種查詢方式可以使用到索引，具體執行結果如下：
索引失效情況3：列運算
如果索引列使用了運算，那么索引也會失效，如下圖所示：
索引失效情況4：使用函數
查詢列如果使用任意 MySQL 提供的函數就會導致索引失效，比如以下列使用了 ifnull 函數之后的執行計劃如下：
索引失效情況5：類型轉換
如果索引列存在類型轉換，那么也不會走索引，比如 address 為字符串類型，而查詢的時候設置了 int 類型的值就會導致索引失效，如下圖所示：
索引失效情況6：使用 is not null
當在查詢中使用了 is not null 也會導致索引失效，而 is null 則會正常觸發索引的，如下圖所示：
總結
導致 MySQL 索引失效的常見場景有以下 6 種：
-
聯合索引不滿足最左匹配原則。 -
模糊查詢最前面的為不確定匹配字符。 -
索引列參與了運算。 -
索引列使用了函數。 -
索引列存在類型轉換。 -
索引列使用 is not null 查詢。
