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

語言: CN / TW / HK

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

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

explain 使用如下,只需要在查詢的 SQL 前面新增上 explain 關鍵字即可,如下圖所示: image.png 而以上查詢結果的列中,我們最主要觀察 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 個索引,如下圖所示: image.png

PS:本文以下內容基於 MySQL 5.7 InnoDB 資料引擎下。

索引失效情況1:非最左匹配

最左匹配原則指的是,以最左邊的為起點欄位查詢可以使用聯合索引,否則將不能使用聯合索引。 我們本文的聯合索引的欄位順序是 sn + name + age,我們假設它們的順序是 A + B + C,以下聯合索引的使用情況如下: image.png 從上述結果可以看出,如果是以最左邊開始匹配的欄位都可以使用上聯合索引,比如:

  • 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 種查詢方式可以使用到索引,具體執行結果如下: image.png

索引失效情況3:列運算

如果索引列使用了運算,那麼索引也會失效,如下圖所示: image.png

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

查詢列如果使用任意 MySQL 提供的函式就會導致索引失效,比如以下列使用了 ifnull 函式之後的執行計劃如下: image.png

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

如果索引列存在型別轉換,那麼也不會走索引,比如 address 為字串型別,而查詢的時候設定了 int 型別的值就會導致索引失效,如下圖所示: image.png

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

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

總結

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

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

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

公眾號:Java面試真題解析

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