Mysql索引的优化原则

目录

1、全值匹配和key_len长度计算

2、最左前缀匹配原则

3、避免在索引列上计算

4、存储引擎不能使用索引中范围条件右边的列

5、索引列尽量包含查询列

6、Mysql在使用不等于，not in，not exists 时无法使用索引

7、is null，is not null 一般情况下也无法使用索引

8、like 以通配符开头('%abc...')，索引失效会变成全表扫描

9、字段类型转换导致索引失效

10、少用or或in进行查询

11、索引使用总结

---

案例前准备

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` (`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、全值匹配和key_len长度计算

--以下三条语句都遵循联合索引的定义顺序
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';

以下分别为三次执行结果

全值匹配：联合索引中的索引字段用得越多，筛选出来的结果越少，sql执行效率越高。

三次查询都使用到了索引，区别在于使用的索引长度不同，关于key_len长度的计算：

• 查询结果一：使用到`name` varchar(24)，那么key_len=24x3+2=74；
• 查询结果二：使用到`name`和`age` int(11)，那么key_len=74 + 4 =78；
• 查询结果三：使用到`name`和`age` 和 `position` varchar(20) ，那么key_len=78+(20x3+2)=140；

// int(11)中，11 代表的并不是长度，而是字符的显示宽度，int 固定占用4字节

key_len的计算规则

字符串类型：char(n) 和 varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节。

• char(n)：如果存汉字长度就是 3n 字节
• varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

字符串类型	长度	数值类型	长度	时间类型	长度
char(n)	存汉字长度为 3n 字节	tinyint	1字节	date	3字节
varchar(n)	存汉字长度为 3n + 2 字节	smallint	2字节	timestamp	4字节
		int	4字节	datetime	8字节
		bigint	8字节

注：如果字段允许为 NULL，则需要额外1字节记录是否为 NULL，索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

2、最左前缀匹配原则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

如果不遵循最左前缀的，但是索引字段都用上了，结果会怎么样呢？

mysql底层执行sql时自动进行优化，但是一般不建议把这种优化让mysql底层来做，而是我们写sql时，就按照联合索引定义的顺序来写。

如果不遵循最左前缀的，且不用联合索引第一个索引字段，结果会怎么样呢？

mysql将进行全表扫描，且不能使用索引。

3、避免在索引列上计算

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

案例一

--对比两条语句的执行结果
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

案例二

给hire_time字段增加一个普通索引（案例执行完后再删除）：

--添加索引
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE;

--删除索引
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

执行如下两条sql，第一条索引失效，第二条使用到了索引

explain select * from employees where date(hire_time)='2018-09-30';
explain select * from employees where hire_time >= '2018-09-30 00:00:00' and hire_time <= '2018-09-30 23:59:59';

在索引列上使用函数，索引会失效，原因是定义的索引跟函数计算后的结果无关，因此也就无法使用到索引

4、存储引擎不能使用索引中范围条件右边的列

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

根据索引构建的B+树可知，age字段根据范围查找后的结果中第三个字段position的排列并不是有序的。如上，范围查找打乱了第三个索引的有序性，所以只能用到前两个索引。

5、索引列尽量包含查询列

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句。//减少回表操作

--需要回表
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
--不需要回表
EXPLAIN SELECT name, age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

6、Mysql在使用不等于，not in，not exists 时无法使用索引

mysql在使用不等于( != 或者 < > )，not in ，not exists 的时候无法使用索引，会导致全表扫描。

注：< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

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

mysql内部优化器会进行评估，数据量小的情况下，相比使用索引，使用全表扫描，反而效率更高。

7、is null，is not null 一般情况下也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null;

8、like 以通配符开头('%abc...')，索引失效会变成全表扫描

--对表如下两条sql
EXPLAIN SELECT * FROM employees WHERE name like '%Lei';
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';

Mysql 底层对 like 进行了优化，相当于等号匹配；而对于相关函数，执行起来会比较复杂(需要计算)，Mysql无相应的优化。

like KK%相当于=常量，%KK和%KK% 相当于范围。

问题：解决like'%字符串%'索引不被使用的方法？

使用覆盖索引，查询字段必须是建立覆盖索引字段，如果不能使用覆盖索引则可能需要借助搜索引擎

9、字段类型转换导致索引失效

比如：字符串不加单引号索引失效

10、少用or或in进行查询

用or或in查询时，mysql不一定使用索引，当需要多次扫描索引树时，可能性能不如全表扫描

mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

11、索引使用总结

假设联合索引index(a,b,c) 

序号	where语句	索引是否被引用
1	where a = 3	Y，用到a
2	where a = 3 and b = 5	Y，用到a,b
3	where a = 3 and b = 5 and c =4	Y，用到a,b,c
4	where b =3 或者 where b = 3 and c =4 或者 where c = 4	N
5	where a = 3 and c = 5	Y，使用到a
6	where a = 3 and b > 4 and c = 5	Y，使用到a,b
7	where a = 3 and b like 'KK%' and c = 4	Y，使用到a,b,c
8	where a = 3 and b like '%KK' and c = 4	Y，只用到a
9	where a = 3 and b like '%KK%' and c = 4	Y，只用到a
10	where a = 3 and b like 'K%KK%' and c = 4	Y，用到a,b,c

总结：所有的sql优化，都是针对索引来说的，因此如果彻底理解了mysql底层索引和数据的存储结构(B+树)和逻辑，那么任何优化都将变得简单。