MySQL进阶之路（十九）—— Order By 和 Group By 优化

Order By 和 Group By 优化

一、Order By 优化

​ 在平时工作中肯定很多地方会需要用到Order By进行排序，比如总是展示最新的数据，这个其实就是按照时间排序，再比如，根据积分等进行排名。等等等等。真的太普遍了。

​ 对于排序，你可能会觉得是不是根据where条件选出一波数据，然后加载到内存中进行排序，又或者是使用一个临时文件存储暂存，在里面排好序之后再将数据返回。但是上面那样速度通常会比较慢，并且上面的方法会存在一个问题——当数据量比较大的时候,且你没有使用limit，比如要对全表进行排序的时候，显然放不下这么多数据。此时MySQL的做法是基于磁盘来排序，专业名词叫**“filesort”**。磁盘中进行排序，尤其是数据量大的时候，速度会尤为明显的慢。

​ 优化Order By的目的就只有一个——尽可能的避免filesort的发生。换句话说，在进行排序的时候尽可能的使用索引。原因很简单，索引的叶子节点本身就是有序的。

示例

假设目前有这样一条SQL

SELECT * FROM t1 ORDER BY key_part1, key_part2, key_part3;

​ 为了提高查询效率，可以通过建立一个(key_part1, key_part2, key_part3)联合索引。在使用order by的时候也要遵循最左匹配原则，这样在进行order by的时候不论是ASC 还是DESC都不会进行filesort。

注意事项

1、要遵循最左匹配原则，下面是错误示例：

SELECT * FROM t1 ORDER BY key1_part1, key1_part3;

2、对于联合索引中的所有列要么都是ASC要么都是DESC，不能混合。下面是错误示例:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC, key_part3 DESC;

3、Order By中只能列名，不能和表达式一起使用

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

小结

​ 如果排序经常要被执行的话，就要考虑是否要为其建立一个联合索引，并且在书写SQL时要尽可能的去使用到索引，减少filesort。

二、Group By 优化

​ 在一般情况下，分组的最通用方法是扫描整个表并且创建一个临时表，每个组内的所有行都是连续的，如果有聚合函数的话，就应用在这个临时表上。

​ 如果有索引的话，则会使用索引来访问从而避免创建临时表。

​ 在Group By的时候想要使用索引，遵循的规则其实和Order By差不多，也需要遵循同样的原则。这里要说的不一样的点就是Group By中的隐式排序或显示排序。

隐式排序与显示排序

​ 所谓的 隐式就是你Group By的列没有ASC 或者 DESC 指示符。而显示排序就是你加了指示符。

​ 隐式排序或者显示排序。就是说，默认情况下，MySQL会对分组后的结果进行排序，通俗的说，分组后每组显示的是这一组中最小/大的值。这样说可能有点抽象，我们直接来看示例：

示例

数据：

结果：

​ 上图展示了隐式排序的结果，按照age分组查询后，默认显示的结果是每组中最小的值，也就是说每个组都是按照从小到大的顺序排列的，显示的是每组排在前面的最小的那条数据。

证明：

​ 通过Explain分析后可以看到，使用了临时表并且进行了filesort，很明显是进行排序了！但是一般情况下我们并不希望他在group by的时候进隐式排序或者显示排序。这个时候，我们可以使用ORDER BY NULL来阻止排序的发生，或者使用自己的Order By来进行排序。

作用

​ 当我们Group By后的数据不是我们期望的时候，我们就知道该怎么做了。就比如上面的例子，我希望每组显示的是名字最大的，也就是name这一列倒序排列（这里只是举个例子，实际情况要看业务），那么我们应该怎么做呢？是在group by之后再order by吗？这个其实是对group by后的结果进行排序，并不是在每一组内进行排序。

那么，如何在每一组内进行排序呢？那就是先将所有数据先排序好，然后再分组。就像下面这样：

SQL:

SELECT
	temp.* 
FROM
	(	#先排好序
		SELECT * FROM USER ORDER BY `name` DESC
	) temp
#再分组	
GROUP BY
	temp.age

结果：

三、本篇总结

​ 以上就是本篇的全部内容了，介绍了Order By 和 Group By如何尽可能的走索引，以及，Group By尽可能避免隐式排序/显示排序的发生，本篇内容希望大家可以自己动手去实验一下，一定会有不一样的理解。

​ 如果本篇内容有什么地方有问题，或者有的地方需要补充，欢迎评论留言，共同进步！