面試突擊60：什麼情況會導致 MySQL 索引失效？_Java中文社群

持續創作，加速成長！這是我參與「掘金日新計劃 · 6 月更文挑戰」的第12天，點選檢視活動詳情

為了驗證 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 效率高一點。

## 建立測試表和資料

為了演示和測試那種情況下會導致索引失效，我們先建立一個測試表和相應的資料：

sql -- 建立表 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，以下聯合索引的使用情況如下：從上述結果可以看出，如果是以最左邊開始匹配的欄位都可以使用上聯合索引，比如：