关于Explain的介绍可前往MySQL Explain执行计划详解查看
现有如下表:
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=7 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
根据这张表的查询来介绍一些优化原则(下面介绍的是一些常用的优化原则,但是不是100%覆盖所有的场景,有些特殊场景是不适用的)
一、全值匹配
有以下三条SQL:
均使用到了索引如下索引:
1.1 条件使用索引的一个字段
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
这条SQL语句使用了索引idx_name_age_position中的 name字段。
ken_len的计算方式:
1)name的类型为varchar
2)name字段的长度为24
3)name字段是not null
在上篇MySQL Explain执行计划详解中介绍了varchar的计算方式为:varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2
所以,这条SQL语句的ken_len为:3*24+2=74
1.2 条件使用索引的两个字段
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
这条SQL语句使用了索引idx_name_age_position中的 name字段和age字段。
ken_len的计算方式:
1)在1.1中计算name的ken_len为74
2)age字段的类型是int
3)age字段是not null
在上篇MySQL Explain执行计划详解中介绍了int的计算方式为:int:4字节
所以,这条SQL语句的ken_len为:74+4=78
1.3 条件使用索引的全部字段
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
这条SQL语句使用了索引idx_name_age_position中的 name字段、age字段和position字段。
ken_len的计算方式:
1)在1.2中计算name的ken_len为78
2)position字段的类型是varchar
3)position字段的长度是20
4)position字段是not null
在上篇MySQL Explain执行计划详解中介绍了varchar的计算方式为:varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2
所以,这条SQL语句的ken_len为:78+20*3+2=140
ps:如果计算出的key_len的长度多了1,看下是否哪个字段是允许为空的,MySQL中如果字段允许为 NULL,需要1字节记录是否为 NULL
综上所述:
全值匹配针对联合索引来说就是尽可能多的使用索引包含的字段,字段使用的越多,效率会更高,扫描行数会更少
通过计算key_len可以判断是否索引使用到了哪些字段
所以,全值匹配优化就是尽可能多的使用索引的字段
二、最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
比如以下索引:
为什么age字段和position字段明明是在索引里面的,但是没有走索引,最终全表扫描???
因为联合索引在查询的时候会先去比对第一个字段,第一个字段比对完成之后再去比对第二个字段…,以此类推。
现在联合索引的第一个字段是name,但是上述SQL中直接是从第二个字段开始查询,根据后面的字段,无法精确定位到索引树的某个节点,导致无法根据索引树查找。
现在相当于不知道第一个字段是什么,那么就需要把索引树中所有的节点都过一遍,其实就相当于是全表扫描了,索引用了和没用没有太大作用。
所以,如果使用了联合索引,查找条件必须要按照联合索引字段出现的顺序排列,否则会导致索引失效。
ps:如果查询条件是:以下SQL,那么会不会走索引呢?
SELECT * FROM employees WHERE age = 22 and name = 'HanMeimei' and position = 'manager';
可以执行看下结果。
那么为什么第一个字段不是name也走了索引呢?
因为mysql 在真正执行SQL之前,内部会进行优化(可以使用explain EXTENDED + show warnings查看),真正执行的时候会按照联合索引的顺序去执行。
三、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转 向全表扫描
比如有如下两个SQL:
直接根据name查询是会走索引的
将name字段left之后,不走索引了。
为什么会出现这种情况呢?
因为SQL中对取了name字段的前3位,索引树中没有执行函数之后对应的值,所以根本就走不了索引。
可以看下联合索引的底层存储结构,如果是从name字段中截取一部分来查询,整棵树就不再是有序的,是没有办法走索引的。
所以就需要考虑使用函数的结果是否可以在索引树中定位,而且还可以保持索引树依然有序。
四、存储引擎不能使用索引中范围条件右边的列
SQL走了索引全值匹配,ken_len为140,是name字段、age字段、position字段之和
SQL走了部分字段,key_len为78是name字段和age字段之和,position字段并没有走索引。
为什么不走position字段的索引呢??
SQL在查询name字段的时候是等值,走索引;查询age的时候是查询大于,范围查找也是走索引的。
在第一个字段相等的情况下,第二个字段肯定还是有序的。
SQL查询的时候其实是先根据第一个条件name=“LiLei”,把结果集加载出来,这个时候数据还是有序的,然后根据第二个条件age>22查询,数据就不一定有序了,所以在查询第三个条件的时候,没办法利用索引的有序性,所以就不会走索引了
所以总的来说:存储引擎不能使用索引中范围条件右边的列(范围后面的字段都不会再走索引)
五、尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句
查询尽量不要用select *,建议指明具体的字段,而且这些字段尽量能被联合索引覆盖(如果不能全部覆盖,尽量多覆盖),要是实在覆盖不到,数据量有非常大,对效率要求又很高,这个时候就需要使用到搜索引擎 。
六、mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
看上去name列是应该要走索引的(possible_keys是有值的),但是其实效率和全表扫描没有什么太大的区别,所以就直接走了全表扫描(key列是空的)
不等于的结果集有可能太多了,走索引总的来说没有什么太大的用处
七、is null,is not null 也无法使用索引
因为null值比较特殊,在索引树中的字段不好比对,它和某些字段比较还是null值,所以没办法很方便的走索引。
那么怎么优化呢?
一般建议SQL语句不要这么写,大部分公司要求字段要设置为not null,SQL中是不会出现这种条件的,即便字段没有值,也要设置一个默认值,方便走索引,提高效率。
八、like以通配符开头(’$abc…’)mysql索引失效会变成全表扫描操作
为什么%在前面不走索引,在后面走索引??
因为%在前面,意味着LiLei前面可能还有一些其他的字符,相当于查询是跳过了前面的部分字符,截取部分去查询,这剩下的部分已经不再有序了,所以不会用到索引
而%在后面,也就相当于最左前缀(使用了name字段的最左前缀),从最开始的字符开始匹配,在索引树中的数据还是有序的,所以可以使用索引
问题:上面说过的left(name,3)其实是和name%是类似的,为什么后者走了索引,而前者没有走呢?
因为mysql底层中会进行判断,如果对字段加了函数,就不会走索引,尽管可以很好的利用索引,也不会走。
问题:解决like’%字符串%'索引不被使用的方法?
a)使用覆盖索引,查询字段必须是建立覆盖索引字段
b)如果不能使用覆盖索引则可能需要借助搜索引擎
九、字符串不加单引号索引失效
为什么类型不一样,就不会走索引??
因为mysql在真正执行SQL语句的时候,检测到类型不匹配,底层可能会强转类型或者使用函数从而导致不走索引(虽然有的时候会做优化不一定会转类型,但是大部分时候还是会去做转换的)
所以查询的时候尽量使用相同的类型
十、少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、 表大小等多个因素整体评估是否使用索引,详见范围查询优化
十一、范围查询优化
给年龄添加单值索引
ALTER TABLE `employees` 2 ADD INDEX `idx_age` (`age`) USING BTREE ;
没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索 引。比如这个例子,可能是由于单次数据量查询过大导致优化器最终选择不走索引
优化方法:可以将大的范围拆分成多个小范围
索引使用总结
假设index(a,b,c)
where语句 | 索引是否使用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b= 5 | Y,使用到a,b |
where a = 3 and b= 5 and c= 4 | Y,使用到a,b,c |
where b = 3 或者 where b= 3 and c= 4 或者 where c=4 | N |
where a = 3 and c= 5 | Y,使用到a,但是不可以使用c,b中间断了 |
where a = 3 and b>4 and c= 5 | Y,使用到a和b,c不能用在范围之后,b断了 |
where a = 3 and b like ‘kk%’ and c= 4 | Y,使用到a,b,c |
where a = 3 and b like ‘%kk’ and c= 4 | Y,只用到a |
where a = 3 and b like ‘%kk%’ and c= 4 | Y,只用到a |
where a = 3 and b like ‘k%kk%’ and c= 4 | Y,使用到a,b,c |
更多内容可前往MySQL是如何选择索引的?查看。