MySQL 索引創建和優化實踐
本文轉載自微信公眾號「運維開發故事」，作者老鄭。轉載本文請聯系運維開發故事公眾號。
本文以 employees 表為例子，結合具體的索引運用實踐案例，通過分析 EXPLAIN 關鍵字獲取執行計劃，來驗證我們這些索引實踐。如果是執行計劃相關的詳細信息，大家可以參考 mysql 官網 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());
復合索引數據結構
下面是一個復合索引的是示意圖
索引最佳實踐
1. 全值匹配
對于全值匹配，就是查詢條件能夠命中索引的全部列，或者復合索引需要命中左邊的數據
- 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.不在索引列上做任何操作(計算、函數、(自動or手動)類型轉換)，會導致索引失效而轉向全表掃描
使用全值匹配能夠正常使用索引
- EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
使用 left 或者其他的數據庫函數導致索引未命中。會走一個全表掃描。
- EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
4.存儲引擎不能使用索引中范圍條件右邊的列
全值匹配如下。
- EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
如果我們中間增加一個范圍查找，會導致右邊的查詢條件無法使用索引。我們對比一下，上下兩條 sql 的差別。
- EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
5.盡量使用覆蓋索引(只訪問索引的查詢(索引列包含查詢列))，減少select *語句
對于 select 指定字段查詢，如果查詢的所有列都是索引上的數據，那么可以減少 “回表”。查詢的效率高于 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在使用不等于(!=或者<>)的時候無法使用索引會導致全表掃描
- EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
7.is null,is not null 也無法使用索引
因為在 mysql 變長的數據類型中，如 varchar 。null 不會存儲，它需要一個額外的標志位來存儲。所以如果我們使用 is null , is not null 是無法使用索引的。所以我們在 DDL 語句中盡量讓每個字段都有默認值。
- 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)當覆蓋索引指向的字段是varchar(380)及380以上的字段時，覆蓋索引會失效!
9. 如果查詢條件導致類型轉換會導致索引失效
字符串不加單引號索引失效
加上單引號，無需數據轉換 name 存儲數據也是 varchar 類型。
- EXPLAIN SELECT * FROM employees WHERE name = '1000';
如果不加單引號，發生數據類型轉換導致本次查詢索引失效。
- EXPLAIN SELECT * FROM employees WHERE name = 1000;
10.少用or,用它連接時很多情況下索引會失效
- EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
小總結
like KK%相當于=常量，%KK和%KK% 相當于范圍 下面是常見的幾種索引查詢失效或者，可用的判定
總結 1
總結 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
