MySQL order by与group by查询优化实现详解

前言

order by满足两种情况，会使用 index 方式排序：

• order by语句使用索引最左前列(最左匹配法则)

• where子句和order by子句条件列组合满足最左匹配法则(where条件使用索引的最左前缀为常量)

下面给出几个实例来说明，如下所示我们创建表并为其创建组合索引(c1,c2,c3)。

CREATE TABLE `testc` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `c1` varchar(100) DEFAULT NULL,
 `c2` varchar(100) DEFAULT NULL,
 `c3` varchar(100) DEFAULT NULL,
 `c4` varchar(100) DEFAULT NULL,
 `c5` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `testc_c1_IDX` (`c1`,`c2`,`c3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

where与order by满足最左匹配法则

# c1 c2满足最左匹配法则
explain select * from testc where c1='a1' order by c2
# 与上面等价
explain select * from testc where c1='a1' order by c2,c3

key_len标明查找用到了索引 c1，Extra中是Using index condition 没有同时出现using where ，表明 c2 索引用来读取数据而非执行查找动作。

MySQL Innodb下的B+树本身就是多路平衡树，那么索引换句话就是排好序的快速查找数据结构。如果order by用到了索引且排序和索引次序一样，那么无疑效果是最好的。

中间断裂

如下所示，缺少了c2，order by不满足最左匹配法则。

explain select * from testc where c1='a1' order by c3

可以看到Extra中Using index condition; Using filesort说明虽然where可以用到索引(单独c1满足最左匹配)，但是排序不满足，故而出现了filesort。

大哥不在

如下c1不在，那么很显然无论查找还是排序都用不到索引。

explain select * from testc where c2='a2' order by c3

这里Extra是Using where; Using filesort，说明通过where子句过滤结果，然后对结果进行文件排序。

范围失效

如下所示，中间c2是个范围搜索，那么其后索引将失效也就是order by c3无法与where连接满足最左匹配法则。

explain select * from testc where c1='a1' and c2 > 'a2' order by c3

如下图所示，这里type = range,ken_len表示用到了 c1,c2索引。Extra是Using index condition; Using filesort表示查询用到了索引但是无法利用索引完成的排序操作。

这种情况如何优化呢？order by c2,c3！这样就可以保证索引排序而不需要filesort。

explain select * from agriculture.testc where c1='a1' and c2  > 'a2'
order by  c2,c3

order by 次序相反

如下所示，order by的次序没有与索引次序保持一致。这里Extra为Using index condition; Using filesort。

explain select * from testc where c1='a1' order by c3,c2

覆盖索引

前面几个都是select *，这里查找索引列。

没有where，order by满足全值匹配，select查询的数据是索引列。

explain select c1 from testc order by c1, c2,c3

这里Extra中只有Using index;

没有where，order by 大哥丢失，select查询的数据是索引列。

explain select c1 from testc order by c2,c3

这里Extra中是Using index; Using filesort 。

这里Extra信息为Using where; Using index; Using filesort。

explain select c1 from testc where c1='a1' order by c3,c2

filesort的两种算法

filesort有两种机制：双路排序和单路排序。双路排序简单来讲就是两次扫描磁盘，最终得到数据。单路排序则是只需要读取一次，也就是一次磁盘IO。

双路排序

MySQL4.1之前是使用双路排序，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出（可以理解为从磁盘读取排序字段，在buffer进行排序，然后再从磁盘读取其他字段）。

取一批数据要进行两次磁盘IO，这是很耗时的。故而在MySQL4.1之后，出现了第二种改进的算法，也就是单路排序。

单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出。它的效率更快一点，避免了第二次读取数据，并且把随机IO变成了顺序IO。但是其会使用更多的空间，因为其缓存了数据在内存中。

单路的问题

可能取出的数据大小超过了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小…从而多次IO(可能比双路更多)。

可以尝试增大sort_buffer_size参数的设置或者max_length_for_sort_data参数的设置。

总结

order by时select * 是一个大忌，应该是查询需要的字段。

当query的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob类型时，会用改进后的算法–单路排序，否则使用双路排序。

两种算法的数据都有可能超出sort_buffer的容量，超出之后会创建tmp文件进行合并排序导致多次IO。尤其对于单路排序来说风险更大，所以需要适当调整sort_buffer的容量。

提高max_length_for_sort_data会增加使用单路排序算法的概率。但是如果设置的太高，数据总容量超过sort_buffer的概率就增大，明显症状是磁盘IO高，CPU使用率低。

group by

前面提到的规则针对group by均适用，group by 实质是先排序后分组，遵照索引建的最佳左前缀。当无法使用索引时，增大max_length_for_sort_data和sort_buffer参数的值。

需要注意的是where优先级高于having，能写在where限定的条件尽量不要通过having。

来源：https://janus.blog.csdn.net/article/details/126750827