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尽可能避免隐式排序/显示排序的发生,本篇内容希望大家可以自己动手去实验一下,一定会有不一样的理解。
如果本篇内容有什么地方有问题,或者有的地方需要补充,欢迎评论留言,共同进步!