MySQL 索引創(chuàng)建和優(yōu)化實(shí)踐
本文轉(zhuǎn)載自微信公眾號(hào)「運(yùn)維開(kāi)發(fā)故事」，作者老鄭。轉(zhuǎn)載本文請(qǐng)聯(lián)系運(yùn)維開(kāi)發(fā)故事公眾號(hào)。
本文以 employees 表為例子，結(jié)合具體的索引運(yùn)用實(shí)踐案例，通過(guò)分析 EXPLAIN 關(guān)鍵字獲取執(zhí)行計(jì)劃，來(lái)驗(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 '入職時(shí)間',
- 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)
下面是一個(gè)復(fù)合索引的是示意圖
索引最佳實(shí)踐
1. 全值匹配
對(duì)于全值匹配，就是查詢條件能夠命中索引的全部列，或者復(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.最佳左前綴法則
如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開(kāi)始并且不跳過(guò)索引中的列。
- 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ù)、(自動(dòng)or手動(dòng))類型轉(zhuǎn)換)，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
使用全值匹配能夠正常使用索引
- EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
使用 left 或者其他的數(shù)據(jù)庫(kù)函數(shù)導(dǎo)致索引未命中。會(huì)走一個(gè)全表掃描。
- EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
4.存儲(chǔ)引擎不能使用索引中范圍條件右邊的列
全值匹配如下。
- EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
如果我們中間增加一個(gè)范圍查找，會(huì)導(dǎo)致右邊的查詢條件無(wú)法使用索引。我們對(duì)比一下，上下兩條 sql 的差別。
- EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
5.盡量使用覆蓋索引(只訪問(wèn)索引的查詢(索引列包含查詢列))，減少select *語(yǔ)句
對(duì)于 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在使用不等于(!=或者<>)的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描
- EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
7.is null,is not null 也無(wú)法使用索引
因?yàn)樵?mysql 變長(zhǎng)的數(shù)據(jù)類型中，如 varchar 。null 不會(huì)存儲(chǔ)，它需要一個(gè)額外的標(biāo)志位來(lái)存儲(chǔ)。所以如果我們使用 is null , is not null 是無(wú)法使用索引的。所以我們?cè)?DDL 語(yǔ)句中盡量讓每個(gè)字段都有默認(rèn)值。
- EXPLAIN SELECT * FROM employees WHERE name is null;
8.like以通配符開(kāi)頭('$abc...')mysql索引失效會(huì)變成全表掃描操作
- EXPLAIN SELECT * FROM employees WHERE name like '%Lei';
- EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';
問(wèn)題：解決like'%字符串%'索引不被使用的方法?a)使用覆蓋索引，查詢字段必須是建立覆蓋索引字段
- EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
b)當(dāng)覆蓋索引指向的字段是varchar(380)及380以上的字段時(shí)，覆蓋索引會(huì)失效!
9. 如果查詢條件導(dǎo)致類型轉(zhuǎn)換會(huì)導(dǎo)致索引失效
字符串不加單引號(hào)索引失效
加上單引號(hào)，無(wú)需數(shù)據(jù)轉(zhuǎn)換 name 存儲(chǔ)數(shù)據(jù)也是 varchar 類型。
- EXPLAIN SELECT * FROM employees WHERE name = '1000';
如果不加單引號(hào)，發(fā)生數(shù)據(jù)類型轉(zhuǎn)換導(dǎo)致本次查詢索引失效。
- EXPLAIN SELECT * FROM employees WHERE name = 1000;
10.少用or,用它連接時(shí)很多情況下索引會(huì)失效
- EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
小總結(jié)
like KK%相當(dāng)于=常量，%KK和%KK% 相當(dāng)于范圍 下面是常見(jiàn)的幾種索引查詢失效或者，可用的判定
總結(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
