面試突擊60：什麼情況會導致 MySQL 索引失效？

為了驗證 MySQL 中哪些情況下會導致索引失效，我們可以藉助 explain 執行計劃來分析索引失效的具體場景。

以上所有結果列說明如下：

• 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：非最左匹配

• 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：列運算

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

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

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

總結

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

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

是非審之於己，譭譽聽之於人，得失安之於數。

公眾號：Java面試真題解析

面試合集： https://gitee.com/mydb/interview