group by 是怎样工作的

数据准备

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_a` (`a`)
) ENGINE=InnoDB;

在表 t2 中插入数据 100 条数据
for(int i=1; i<=100; i++) {
    insert into t2(id, a, b) values(i, i, i);
}

group by 执行流程

sql select b, count(*) as count from t2 group by b;

1：创建临时表，表里有两个字段 b 和 count，主键是 b

2：对表 t2 进行全表扫描，依次取出每条记录的字段 b（该步骤会扫描临时表的主键来定位记录）

• 如果临时表中没有主键为 b 的行，就插入一个记录 (b,1)
• 如果表中有主键为 b 的行，就将 b 这一行的 count字段对应的值加 1

3：遍历完成后，再根据字段 b 做排序（参考 order by 是怎样工作的），得到结果集返回给客户端

这里说明一下，通过查看慢日志，sql的实际扫描行数是300行，为什么是300行。

• 首先对t2进行全表扫描，这就是100行
• 在扫描临时表主键的时候，每次会扫描 1 行，会扫描 100次，这就是 100行（扫描100次是，因为 t2 表有 100条记录）
• 步骤3，要对临时表根据字段 b 做排序（这里假设 sort buffer 足够大，并且使用的是全字段排序），所以对临时表进行一次全表扫描，临时表的记录条数100，这就是 100 行。

在sql语句后面添加 order by null 可以避免步骤 3 的排序工作

select b, count(*) as count from t2 group by b order by null；

扫描行数为 200 行

使用 group by 为什么需要创建临时表。

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

group by 如何优化

方式1：加索引

索引天然就保证了数据的有序性，所以执行 group by 的时候也就不需要创建临时表来记录并统计结果。

select b, count(*) as count from t2 group by b; 字段 b 是索引，sql的执行流程就是下面这样

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

通过对比，有索引的 group by，和没索引的 group by 两者的执行过程，就可以发现，有索引的group by只需要扫描索引就可以了。而没索引的 group by

还需要维护临时表（临时表可能是内存临时表【memory引擎】或者磁盘临时表【innodb引擎】），还需要进行 order by 排序

方式2：直接排序

如果可以通过加索引来完成 group by 逻辑就再好不过了。但是，如果碰上不适合创建索引的场景，那就只能使用创建临时表。如果再碰上结果集数量大的情况，那性能就更糟糕了

那么，这时候的 group by 要怎么优化呢？可以在sql语句中加上 SQL_BIG_RESULT

select SQL_BIG_RESULT b, count(*) as count from t2 group by b;

sql的执行流程就变成这样了

1. 初始化 sort_buffer，确定放入一个整型字段，记为 m；
2. 扫描表 t1 依次取出里面的 字段 b 的值存入 sort_buffer 中；
3. 扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；
4. 排序完成后，就得到了一个有序数组。
5. 然后对有序数组做如下判断

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