MySQL查詢為什麼沒走索引?這篇文章帶你全面解析

一燈架構發表於2022-07-08

工作中,經常遇到這樣的問題,我明明在MySQL表上面加了索引,為什麼執行SQL查詢的時候卻沒有用到索引?

同一條SQL有時候查詢用到了索引,有時候卻沒用到索引,這是咋回事?

原因可能是索引失效了,失效的原因有以下幾種,看你有沒有踩過類似的坑?

1. 資料準備:

有這麼一張使用者表,在name欄位上建個索引:

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年齡',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB COMMENT='使用者表';

2. Explain詳解:

想要檢視一條SQL是否用到索引?用到了哪種型別的索引?

可以使用explain關鍵字,檢視SQL執行計劃。例如:

explain select * from user where id=1;

可以看到type=const,表示使用了主鍵索引。

explain的所有type型別如下:

3. 失效原因

1. 資料型別隱式轉換

name欄位是varchar型別,如果我們使用資料型別查詢,就會產生資料型別轉換,雖然不會報錯,但是無法用到索引。

explain select * from user where name='一燈';

explain select * from user where name=18;

2. 模糊查詢 like 以%開頭

explain select * from user where name like '張%';

explain select * from user where name like '%張';

3. or前後沒有同時使用索引

雖然name欄位上加了索引,但是age欄位沒有索引,使用or的時候會全表掃描。

# or前後沒有同時使用索引,導致全表掃描
explain select * from user where name='一燈' or age=18;

4. 聯合索引,沒有使用第一列索引

如果我們在(name,age)上,建立聯合索引,但是查詢條件中只用到了age欄位,也是無法用到索引的。

使用聯合索引,必須遵循最左匹配原則,首先使用第一列欄位,然後使用第二列欄位。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年齡',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB COMMENT='使用者表';

5. 在索引欄位進行計算操作

如果我們在索引列進行了計算操作,也是無法用到索引的。

# 在主鍵索引上進行計算操作,導致全表掃描
explain select * from user where id+1=2;

6. 在索引欄位欄位上使用函式

如果我們在索引列使用函式,也是無法用到索引的。

7. 優化器選錯索引

同一條SQL有時候查詢用到了索引,有時候卻沒用到索引,這是咋回事?

這可能是優化器選擇的結果,會根據表中資料量選擇是否使用索引。

當表中大部分name都是一燈,這時候用name='一燈'做查詢,還會不會用到索引呢?

索引優化器會認為,用索引還不如全表掃描來得快,乾脆不用索引了。

當然我們認為優化器優化的不對,也可以使用force index強制使用索引。

知識點總結:

文章持續更新,可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。

相關文章