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

一燈架構發表於2022-07-08
MySql索引

工作中，經常遇到這樣的問題，我明明在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強制使用索引。

知識點總結：

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

相關文章