面試必問：MySQL索引失效的場景有哪些？

作者：磊哥 2024-01-05 14:20:55

const — 將一個主鍵放置到 where 后面作為條件查詢， MySQL 優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個常量，如何轉(zhuǎn)化以及何時轉(zhuǎn)化，這個取決于優(yōu)化器，這個比 eq_ref 效率高一點。

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

explain 使用如下，只需要在查詢的 SQL 前面添加上 explain 關(guān)鍵字即可，如下圖所示：

圖片

而以上查詢結(jié)果的列中，我們最主要觀察 key 這一列，key 這一列表示實際使用的索引，如果為 NULL 則表示未使用索引，反之則使用了索引。

以上所有結(jié)果列說明如下：

id — 選擇標(biāo)識符，id 越大優(yōu)先級越高，越先被執(zhí)行；
select_type — 表示查詢的類型；
table — 輸出結(jié)果集的表；
partitions — 匹配的分區(qū)；
type — 表示表的連接類型；
possible_keys — 表示查詢時，可能使用的索引；
key — 表示實際使用的索引；
key_len — 索引字段的長度；
ref— 列與索引的比較；
rows — 大概估算的行數(shù)；
filtered — 按表條件過濾的行百分比；
Extra — 執(zhí)行情況的描述和說明。

其中最重要的就是 type 字段，type 值類型如下：

all — 掃描全表數(shù)據(jù)；
index — 遍歷索引；
range — 索引范圍查找；
index_subquery — 在子查詢中使用 ref；
unique_subquery — 在子查詢中使用 eq_ref；
ref_or_null — 對 null 進行索引的優(yōu)化的 ref；
fulltext — 使用全文索引；
ref — 使用非唯一索引查找數(shù)據(jù)；
eq_ref — 在 join 查詢中使用主鍵或唯一索引關(guān)聯(lián)；
const — 將一個主鍵放置到 where 后面作為條件查詢， MySQL 優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個常量，如何轉(zhuǎn)化以及何時轉(zhuǎn)化，這個取決于優(yōu)化器，這個比 eq_ref 效率高一點。

創(chuàng)建測試表和數(shù)據(jù)

為了演示和測試那種情況下會導(dǎo)致索引失效，我們先創(chuàng)建一個測試表和相應(yīng)的數(shù)據(jù)：

-- 創(chuàng)建表
drop table if exists student;
create table student(
  id int primary key auto_increment comment '主鍵',
  sn varchar(32) comment '學(xué)號',
  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;
-- 添加測試數(shù)據(jù)
insert into student(id,sn,name,age,sex,address) 
  values(1,'cn001','張三',18,1,'高老莊'),
  (2,'cn002','李四',20,0,'花果山'),
  (3,'cn003','王五',50,1,'水簾洞');

當(dāng)前表中總共有 3 個索引，如下圖所示：

圖片