你知道MySQL中 group by 怎么优化吗

更好的阅读体验，请点击 YinKai 's Blog。

​ 在 MySQL 中 group by 用于按照一个或多个列对结果集进行分组。在讨论 group by 怎么优化之前，我们先来看看 group by 的执行流程，这样我们才能对症下药。

group by 执行流程

​ 我们先用下面的 sql 语句创建一个表，并输入一些数据，模拟真实环境。

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
  declare i int;
 
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

​ 然后我们执行下面的语句：

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

​ 这个语句的逻辑是把表 t1 里的数据，按照 id%10 进行分组统计，并按照 m 的结果排序后输出。它的 explain 结果如下：

​ 在 Extra 字段我们可以看到三个信息：

• Using index：表示这个语句使用了索引覆盖，选择了索引 a，不需要回表
• Using temporary，表示使用了临时表
• Using filesort，表示需要排序

​ 这个语句的执行过程是：

1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m
2. 扫描表 t1 的索引 a，依次取出叶子结点上的 id 值，计算 id%10 的结果，记为 x；
   • 如果临时表中没有主键为 x 的行，就插入一个记录 (x, 1)；
   • 如果临时表中有主键为 x 的行，就将 x 这一行的 c 值加 1；；
3. 遍历完成后，再根据字段 m 做排序，得到的结果返回给客户端。

​ 这个流程的执行图如下：

​ 图中最后一步，对内存临时表的排序过程如下：

​ 上面的例子，由于临时表只有 10 行，内存可以放得下，因此只使用了内存临时表。但内存临时表是有大小限制的，可以通过参数 tmp_table_size 修改，默认是 16M。

​ 如果我执行下面这个语句序列：

set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

​ 把内存临时表的大小限制为最大 1024 字节，并把语句改成 id % 100，这样返回结果里有 100 行数据。但是，这时的内存临时表大小不够存下这 100 行数据，也就是说，执行过程中会发现内存临时表大小到达了上限（1024 字节）。

​ 那么这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的是 InnoDB，结果如下：

​ 如果这个表 t1 的数据量很大，很可能这个查询的磁盘临时表需要用到很大的磁盘空间，查询生成大型临时表，占用大量磁盘空间可能导致查询变慢，引起磁盘空间不足，影响系统稳定性。

​ 因此，这就是为什么我们需要去优化 group_by 的原因。

group by 优化方法——索引

​ 要解决 group by 的优化问题，我们需要从根本上去解决问题，即执行 group by 语句创建的临时表。

​ group by 的语义逻辑，是统计不同的值出现的个数。但是由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。

​ 那我们假想出现的数据都是有序的，看看 group by 会怎么做。

​ 如果我们可以确保输入的数据都是有序的，那么计算 group by 的时候，就只需要从左往右顺序扫描，依次累加，即：

• 当碰到第一个 1 的时候，已经知道累积了 X 个 0，结果集里的第一行就是 (0,X);
• 当碰到第一个 2 的时候，已经知道累积了 Y 个 1，结果集里的第二行就是 (1,Y);

​ 按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序。

​ 不难想到，InnoDB 的索引就可以满足这个输入有序的条件。

​ 我们可以 MySQL5.7 版本的 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引

alter table t1 add column z int generated always as(id % 100), add index(z);

​ 这样，索引 z 上的数据就是类似上图那样有序的了。上面的 group by 语句就可以改成：

select z, count(*) as c from t1 group by z;

​ 优化后的 group by 语句的 explain 结果，如下图所示：

​ 从 Extra 字段可以看出，这个语句的执行不再需要临时表了，也不需要排序了。

group by 优化方法 – 直接排序

​ 如果能使用创建索引的方式来优化那再好不过，万一要是遇到了不适合创建索引的创建，我们又该怎么办呢？

​ 当我们明确知道一个 GROUP BY 语句中涉及的数据量非常大，而 MySQL 的默认行为是首先尝试在内存中创建临时表，然后在内存不足的情况下将其转为磁盘临时表，我们可能希望直接走磁盘临时表的方式，以避免不必要的内存消耗。MySQL 提供了一个查询提示 SQL_BIG_RESULT 来实现这一点。

具体而言，你可以在 GROUP BY 语句中加入 SQL_BIG_RESULT 提示，告诉优化器：由于数据量较大，请直接使用磁盘临时表。这样，优化器会考虑在磁盘上存储临时表，而不是首先尝试在内存中完成这一操作。

以下是使用 SQL_BIG_RESULT 提示的一个示例：

SELECT SQL_BIG_RESULT id % 100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;

这个查询的执行流程可以描述为：

1. 初始化 sort_buffer，确定放入一个整型字段 m。
2. 扫描表 t1 的索引 a，依次取出其中的 id 值，将 id % 100 的值存入 sort_buffer 中。
3. 扫描完成后，对 sort_buffer 的字段 m 进行排序。如果 sort_buffer 内存不足，将会利用磁盘临时文件辅助排序。
4. 排序完成后，得到一个有序数组。
5. 根据有序数组，获取数组中的不同值以及每个值的出现次数。

​ 这样，通过使用 SQL_BIG_RESULT 提示，你可以明确告知 MySQL 优化器，考虑到数据量很大，直接使用磁盘临时表。

​ 执行 explain 的结果如下图：

​ 从 Extra 字段可以看到，这个语句的执行没有再使用临时表，而是直接用了排序算法。

小结

​ 现在我们来总结一下使用 group by 需要注意的一些点：

1. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
2. 使用 group by 的时候，尽可能用上表的索引，确认的方法是查看 explain 结果里有没有 Using temporary 和 Using filesort；
3. 如果 gruop by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数避免使用磁盘临时表；
4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

​ 最后，我们来看一看文章开头的问题：

​ MySQL中 group by 怎么优化？

1. 尽可能保证 group by 语句上存在索引，这样有助于数据引擎更有效地执行分组操作，我们可以通过查看执行计划 explain 的输出，来确认是否使用了索引。
2. 如果内存临时表足够容纳 group by 的结果集的话，可以适当增加内存临时表的参数大小，使 MySQL 更倾向于使用内存临时表，因为内存的读写速度远高于磁盘，这样可以显著提高查询性能。
3. 如果 GROUP BY 的字段是通过某个表达式计算而来，考虑使用生成列，并在生成列上创建索引。
4. 在 GROUP BY 的数据量非常大且无法通过其他手段优化时，可以使用 SQL_BIG_RESULT 提示，**让优化器直接使用排序算法而不是创建临时表，**这样 MySQL 就可以直接通过遍历数组获取我们想要的结果。