使用到的表

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` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT 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('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

1、全值匹配

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

image.png

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

image.png

image.png

2、最左前綴法則

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

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';（全表掃描，不會(huì)使用索引）

EXPLAIN SELECT * FROM employees WHERE position = 'manager';（全表掃描，不會(huì)使用索引）

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';（會(huì)使用索引）

image.png

注： 由建表的時(shí)候我們是建了一個(gè)聯(lián)合索引，順序依次是name,age,position。
所以在查詢的時(shí)候我們要按照索引順序依次查詢，不然無(wú)法使用到索引，這個(gè)和Mysql 的底層數(shù)據(jù)結(jié)構(gòu)存儲(chǔ)有關(guān)系（B+ 樹(shù)）

聯(lián)合索引底層數(shù)據(jù)結(jié)構(gòu).png

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

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

image.png

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

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

image.png

注： 這個(gè)時(shí)候position這列 則不會(huì)使用到索引。

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

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

image.png

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

image.png

6.mysql在使用不等于（！=或者<>）的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描

XPLAIN SELECT * FROM employees WHERE name != 'LiLei'

image.png

7.is null,is not null 也無(wú)法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null

image.png

8.like以通配符開(kāi)頭（'$abc...'）mysql索引失效會(huì)變成全表掃描操作
EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

image.png

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

image.png

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

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

image.png

b）當(dāng)覆蓋索引指向的字段是varchar(380)及380以上的字段時(shí)，覆蓋索引會(huì)失效！

9.字符串不加單引號(hào)索引失效
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

image.png

10.少用or,用它連接時(shí)很多情況下索引會(huì)失效
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

image.png

總結(jié)：

image.png

like KK%相當(dāng)于=常量，%KK和%KK% 相當(dāng)于范圍
1、Mysql支持兩種的排序filesort和index，Using index 是指MySql掃描本身完成的排序。index效率高，filesort 效率低。
2、order by 滿足兩種情況會(huì)使用Using index.
（1）使用where 子居與order by 子句條件組合滿足索引最左前列。
（2）order by 語(yǔ)句使用索引最左前列。
3、盡量在索引上完成排序，遵循索引建立（索引創(chuàng)建的順序）時(shí)的最佳左前綴法則。
4、如果order by 的條件不存在索引條件列上，就會(huì)產(chǎn)生 Using filesort.
5、group by 和order by 很類似，其實(shí)質(zhì)是先排序后分組，遵照索引創(chuàng)建順序的最佳左前綴法則。注意where高于having,能在where的限定條件就不要去having限定了。

這里有個(gè)網(wǎng)上比較流行的順口溜也給大家分享一下。

全值匹配我最愛(ài)，最左前綴要遵守。
帶頭大哥不能死，中間兄弟不能斷。
索引列上少計(jì)算，范圍之后全失效。
LIKe百分寫(xiě)最右，覆蓋索引不寫(xiě)星。
不等空值還有or，索引失效要少用。

以上就是一些比較常見(jiàn)的索引優(yōu)化方案，當(dāng)然了索引優(yōu)化遠(yuǎn)遠(yuǎn)不止這么多，在這里知識(shí)列舉了一些平時(shí)開(kāi)發(fā)中常用的，在平時(shí)開(kāi)發(fā)中還要多多注意與積累。