group by 优化大法

首先直接结论：

1. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

2. 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using

temporary 和 Using filesort；

3. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大

tmp_table_size 参数，来避免用到磁盘临时表；

4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序

算法得到 group by 的结果。

group by 执行流程：

select id%10 as m, count(*) as c from t1 group by m;

上面sql语句的执行流程如下：

1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m；

2. 扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；

如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);

如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；

3. 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。（为什么有的个结论的目的）。

SQL 语句末尾增加 order by null，这样就跳过了最后排序的阶段，直接从临时表中取数据返回。

参数 tmp_table_size 就是控制这个内存临时表大小的，默认是 16M。

如果执行中存放的数据超过内存临时表的上限，这个时候就会把内存临时表转为磁盘临时表。

不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯

一索引的表，执行代价都是比较高的。我们有什么优化的方法呢？

group by field //给field加索引

在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个

语句涉及的数据量很大，请直接用磁盘临时表。

MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既

然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

这个语句的执行没有再使用临时表，而是直接用了排序算法。

注意：union 跟 union all的一个性能区别是：union涉及到去重，所以，用到了临时表。