union 执行流程
union的语义是,取这两个子查询结果的并集。并集的意思就是这两个集合加起来,重复的行只保留一行。
内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键id的唯一性约束,实现了union的语义。但是如果把上面这个语句中的union改成union all的话,就没有了“去重”的语义。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不需要临时表了。
group by 执行流程
group by 优化方法 --索引
group by的语义逻辑,是统计不同的值出现的个数。但是,由于每一行的id%100的结果是无序的,所以我们就需要有一个临时表,来记录并统计结果。使用索引可以避免排序。
group by优化方法 --直接排序
在group by语句中加入SQL_BIG_RESULT这个提示(hint),就可以告诉优化器:这个语句涉及的数据量很大,请直接用磁盘临时表。
小结
group by的几种实现算法,从中可以总结一些使用的指导原则:
- 如果对group by语句的结果没有排序要求,要在语句后面加 order by null;
- 尽量让group by过程用上表的索引,确认方法是explain结果里没有Using temporary 和 Using filesort;
- 如果group by需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大tmp_table_size参数,来避免用到磁盘临时表;
- 如果数据量实在太大,使用SQL_BIG_RESULT这个提示,来告诉优化器直接使用排序算法得到group by的结果。
文章中图8和图9都是order by null,为什么图8的返回结果里面,0是在结果集的最后一行,而图9的结果里面,0是在结果集的第一行?