為了驗證 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 種：

1. 模糊匹配后面任意字符：like '張%'
2. 模糊匹配前面任意字符：like '%張'
3. 模糊匹配前后任意字符：like '%張%'

而這 3 種模糊查詢中只有第 1 種查詢方式可以使用到索引，具體執行結果如下：

圖片

索引失效情況3：列運算

如果索引列使用了運算，那么索引也會失效，如下圖所示：

圖片

索引失效情況4：使用函數

查詢列如果使用任意 MySQL 提供的函數就會導致索引失效，比如以下列使用了 ifnull 函數之后的執行計劃如下：

圖片

索引失效情況5：類型轉換

如果索引列存在類型轉換，那么也不會走索引，比如 address 為字符串類型，而查詢的時候設置了 int 類型的值就會導致索引失效，如下圖所示：

圖片

索引失效情況6：使用 is not null

當在查詢中使用了 is not null 也會導致索引失效，而 is null 則會正常觸發索引的，如下圖所示：

圖片

總結

導致 MySQL 索引失效的常見場景有以下 6 種：

1. 聯合索引不滿足最左匹配原則。
2. 模糊查詢最前面的為不確定匹配字符。
3. 索引列參與了運算。
4. 索引列使用了函數。
5. 索引列存在類型轉換。
6. 索引列使用 is not null 查詢。