別再問我MySQL為啥沒走索引?就這幾種原因,全都告訴你

語言: CN / TW / HK

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

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

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

1. 資料準備:

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

mysql 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執行計劃。例如:

sql explain select * from user where id=1;

image-20220629230715808.png 可以看到type=const,表示使用了主鍵索引。

explain的所有type型別如下:

image-20220630000000083.png

3. 失效原因

1. 資料型別隱式轉換

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

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

image-20220629231442732.png

sql explain select * from user where name=18;

image-20220629231513592.png

2. 模糊查詢 like 以%開頭

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

image-20220629231905411.png

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

image-20220629231938893.png

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

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

```sql

or前後沒有同時使用索引,導致全表掃描

explain select * from user where name='一燈' or age=18; ```

image-20220629232130791.png

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

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

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

mysql 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='使用者表';

image-20220630000609202.png

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

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

```sql

在主鍵索引上進行計算操作,導致全表掃描

explain select * from user where id+1=2; ```

image-20220629233208133.png

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

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

image-20220629233447426.png

7. 優化器選錯索引

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

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

image-20220629234641204.png

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

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

image-20220629234900354.png

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

image-20220629235137298.png

知識點總結:

image-20220630171538701.png