[[434021]]

本文轉(zhuǎn)載自微信公眾號「運(yùn)維開發(fā)故事」，作者老鄭。轉(zhuǎn)載本文請聯(lián)系運(yùn)維開發(fā)故事公眾號。

本文以 employees 表為例子，結(jié)合具體的索引運(yùn)用實(shí)踐案例，通過分析 EXPLAIN 關(guān)鍵字獲取執(zhí)行計(jì)劃，來驗(yàn)證我們這些索引實(shí)踐。如果是執(zhí)行計(jì)劃相關(guān)的詳細(xì)信息，大家可以參考 mysql 官網(wǎng) explain 介紹。

mysql 版本: 5.7.23

使用的表

CREATE TABLE employees ( 
 id int(11) NOT NULL AUTO_INCREMENT, 
 name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', 
 age int(11) NOT NULL DEFAULT '0' COMMENT '年齡', 
 position varchar(20) NOT NULL DEFAULT '' COMMENT '職位', 
 hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間', 
 PRIMARY KEY (id), 
 KEY idx_name_age_position USING BTREE (name, age, position) 
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARSET = utf8 COMMENT '員工記錄表'; 
 
INSERT INTO employees (name, age, position, hire_time) VALUES ('LiLei', 22, 'manager', NOW()); 
 
INSERT INTO employees (name, age, position, hire_time) VALUES ('WaKen', 23, 'dev', NOW()); 
 
INSERT INTO employees (name, age, position, hire_time) VALUES ('Lucy', 23, 'dev', NOW()); 

復(fù)合索引數(shù)據(jù)結(jié)構(gòu)

下面是一個復(fù)合索引的是示意圖

索引最佳實(shí)踐

1. 全值匹配

對于全值匹配，就是查詢條件能夠命中索引的全部列，或者復(fù)合索引需要命中左邊的數(shù)據(jù)

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei'; 

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22; 

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 

2.最佳左前綴法則

如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager'; 

EXPLAIN SELECT * FROM employees WHERE position = 'manager'; 

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 

3.不在索引列上做任何操作(計(jì)算、函數(shù)、(自動or手動)類型轉(zhuǎn)換)，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

使用全值匹配能夠正常使用索引

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 

使用 left 或者其他的數(shù)據(jù)庫函數(shù)導(dǎo)致索引未命中。會走一個全表掃描。

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 

4.存儲引擎不能使用索引中范圍條件右邊的列

全值匹配如下。

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 

如果我們中間增加一個范圍查找，會導(dǎo)致右邊的查詢條件無法使用索引。我們對比一下，上下兩條 sql 的差別。

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 

5.盡量使用覆蓋索引(只訪問索引的查詢(索引列包含查詢列))，減少select *語句

對于 select 指定字段查詢，如果查詢的所有列都是索引上的數(shù)據(jù)，那么可以減少 “回表”。查詢的效率高于 select *

explain select name,age from employees where name = 'Lilei' and age = 23 and position= 'manage'; 

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager'; 

6.mysql在使用不等于(!=或者<>)的時候無法使用索引會導(dǎo)致全表掃描

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei'; 

7.is null,is not null 也無法使用索引

因?yàn)樵?mysql 變長的數(shù)據(jù)類型中，如 varchar 。null 不會存儲，它需要一個額外的標(biāo)志位來存儲。所以如果我們使用 is null , is not null 是無法使用索引的。所以我們在 DDL 語句中盡量讓每個字段都有默認(rèn)值。

EXPLAIN SELECT * FROM employees WHERE name is null; 

8.like以通配符開頭('$abc...')mysql索引失效會變成全表掃描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'; 

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'; 

問題：解決like'%字符串%'索引不被使用的方法?a)使用覆蓋索引，查詢字段必須是建立覆蓋索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%'; 

b)當(dāng)覆蓋索引指向的字段是varchar(380)及380以上的字段時，覆蓋索引會失效!

9. 如果查詢條件導(dǎo)致類型轉(zhuǎn)換會導(dǎo)致索引失效

字符串不加單引號索引失效

加上單引號，無需數(shù)據(jù)轉(zhuǎn)換 name 存儲數(shù)據(jù)也是 varchar 類型。

EXPLAIN SELECT * FROM employees WHERE name = '1000'; 

如果不加單引號，發(fā)生數(shù)據(jù)類型轉(zhuǎn)換導(dǎo)致本次查詢索引失效。

EXPLAIN SELECT * FROM employees WHERE name = 1000; 

10.少用or,用它連接時很多情況下索引會失效

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei'; 

小總結(jié)

like KK%相當(dāng)于=常量，%KK和%KK% 相當(dāng)于范圍 下面是常見的幾種索引查詢失效或者，可用的判定

總結(jié) 1

總結(jié) 2

參考文檔

https://blog.csdn.net/qq_38138069/article/details/82998658

https://www.processon.com/u/5e26625de4b00fbcc45e576d

https://zhuanlan.zhihu.com/p/94190700

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html