目录
6、Mysql在使用不等于,not in,not exists 时无法使用索引
7、is null,is not null 一般情况下也无法使用索引
8、like 以通配符开头('%abc...'),索引失效会变成全表扫描
案例前准备
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+树)和逻辑,那么任何优化都将变得简单。