MySQL索引优化实例-Order by 与Group by优化

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
) 

实际案例

案例一

select * from employees where name = 'LiLei' and position='dev' order by age;

可以先看下这条SQL语句，是否会走索引？如果走索引，会走几个字段？
首先，肯定是会走索引的，至少name字段是会走索引的，但是position不会走，因为中间缺少了age字段。
接下来使用explain来看下执行结果：

key_len长度为74:在MySQL Explain执行计划详解中介绍了varchar的计算方式为：varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
所以，这条SQL语句的ken_len为：3*24+2=74

在这里可以思考下，age有没有走索引呢？
答：age其实是走索引的，只是age不是等值查询，而是走的排序。在上面explain的结果中可以看到Extra中的内容是Using index condition，那么证明走了索引的排序。（因为name字段已经确定了，在name字段一定的情况下，age字段在索引树中是有序的，所以走了索引）

总的分析如下：
利用最左前缀法则：中间字段不能断，因此查询用到了name索引 ，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

案例二

select * from employees where name = 'LiLei' order by position;

分析如下：
从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。（跳过了age字段，position字段不一定有序了，所以没有用到索引）

案例三

select * from employees where name = 'LiLei' order by age,position;

结合上述描述，思考下这条SQL是否会走排序的索引？？？
使用Explain查看：

分析如下：
查找只用到索引name，age和position用于排序，无Using filesort。（因为索引的创建顺序是name,age,position,在name相同的情况下，先根据age排序，再根据是position排序符合索引树的顺序，所以走了索引）

案例四

select * from employees where name = 'LiLei' order by position,age;

那么如果把排序字段的顺序换一下，还会不会走索引呢？？？
可以使用Explain来看下：

分析如下：
和案例三中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为 name,age,position，但是排序的时候age和position颠倒位置了。

那么有小伙伴可能会有疑问：为什么where后面的条件不按照索引创建的顺序出现，SQL优化的时候，会优化顺序，但是order by确不能优化成索引的顺序呢？？？
这个问题其实很简单，大家可以想一下：where后面的条件，不管怎么交换位置，不会影响到查询的结果，但是order by交换位置的话就是影响到最终的排序结果，有多个字段排序，会先按照第一个字段排，第一个字段相同排不了序的会按照第二个字段排。。。以此类推，如果SQL帮我们优化了order by的顺序，结果就不是我们想要的排列顺序了，所以MySQL底层就没有优化。
因此需要注意：在使用order by的时候，必须按照索引创建的顺序出现。

案例五

select * from employees where name = 'LiLei' and age = 18 order by position,age;

如果，where后面增加一个age条件，会走索引吗？？？
可以使用Explain来看下：

分析如下：
在Extra中并未出现Using filesort，因为age为常量，在排序中被优化（相当于age排序并没有用，因为所有的数据age都为18），所以索引未颠倒， 不会出现Using filesort。

案例六

select * from employees where name = 'LiLei'  order by age asc ,position desc;

先思考下一个字段升序，一个字段降序，会不会走索引？？
可以使用Explain来看下：

分析如下：
虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

案例七

select * from employees where name in('LiLei','Hanmeimei')  order by age,position;

如果把等值找换成范围查找，还会不会走索引呢？？
使用Explain来看下结果：

in很有可能不走索引，name列虽然有索引但是不一定走，有可能走，也有可能不走，具体为什么走或者为什么不走可以使用trace工具来查一下，看查询成本cost是多少，具体可前往MySQL是如何选择索引的？查看

in类似于范围查找，而第一个字段不能确定，是按照范围查找，查出的数据不一定是按照索引树的顺序，再按照剩余字段的去排序，根据索引是排不了的。

对于in，一般来说主键而且是整型大多数情况下是会走索引的，但是如果in里面的元素太多，也不会走索引
如果是非主键索引，一般都不会走。具体走不走索引是有很多因素的，结果会有差别。

案例八

select * from employees where name > 'a' order by name;

先思考下像这种情况会走索引吗？？？
使用explain来查看下结果

这种情况不一定走索引，按照我们常规的理解是会走索引的，但是最终MySQL优化器决定是否走索引是有各种因素影响的。

可以使用覆盖索引优化：

优化总结

1、MySQL支持两种方式的排序filesort和index。
Using index是指MySQL扫描索引本身完成排序，index 效率高，filesort效率低。

2、order by满足两种情况会使用Using index。
     1) order by语句使用索引最左前列。
     2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。
对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

ps:备注
在上面的案例中，我们看到了有index排序和filesort排序两种
index排序是很快的，大家都知道index排序实际上就是在索引树上去排，有B+ Tree的算法，查找是非常快的。
filesort实际上是文件排序，相当于在磁盘上进行排序，逐行去扫描，然后比较大小排序，性能是非常低的

如果到这里小伙伴们对filesort感兴趣的话，可前往Using filesort文件排序原理详解查看