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 ANDposition =‘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’;
EXPLAIN SELECT * FROM employees WHERE left(name,3) = ‘LiLei’;
給hire_time增加一個普通索引：

EXPLAIN select * from employees where date(hire_time) =‘2018-09-30’

EXPLAIN select * from employees where hire_time >=‘2018-09-30 00:00:00’ andhire_time <=‘2018-09-30 23:59:59’

還原最初索引狀態

4.存儲引擎不能使用索引中范圍條件右邊的列
EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age = 22 ANDposition =‘manager’;
EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age > 22 ANDposition =‘manager’

5.盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），減少select *語句
EXPLAIN SELECT name,age FROM employees WHERE name= ‘LiLei’ AND age = 23AND position =‘manager

EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age = 23 ANDposition =‘manager’

6.mysql在使用不等于（！=或者<>）的時候無法使用索引會導致全表掃描
EXPLAIN SELECT * FROM employees WHERE name != ‘LiLei’;

7.is null,is not null 也無法使用索引
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）如果不能使用覆蓋索引則可能需要借助搜索引擎

9.字符串不加單引號索引失效
EXPLAIN SELECT * FROM employees WHERE name = ‘1000’;
EXPLAIN SELECT * FROM employees WHERE name = 1000;

10.少用or或in，
用它查詢時，mysql不一定使用索引，mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引，詳見范圍查詢優化EXPLAIN SELECT * FROM employees WHERE name = ‘LiLei’ or name = ‘HanMeimei’

11.范圍查詢優化
給年齡添加單值索引

explain select * from employees where age >=1 and age <=2000;

沒走索引原因：mysql內部優化器會根據檢索比例、表大小等多個因素整體評估是否使用索引。
比如這個例子，可能是由于單次數據量查詢過大導致優化器最終選擇不走索引優化方法：可以講大的范圍拆分成多個小范圍
explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000

還原最初索引狀態
1ALTER TABLE employees
2DROP INDEX idx_age;
索引使用總結：

like KK%相當于=常量，%KK和%KK% 相當于范圍

總結

以上是生活随笔為你收集整理的MySQL调优（二）——索引最佳实践的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。