MySQL 内部临时表（group by工作原理）

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();

使用内部临时表的场景？

• union
• group by

union 使用内部临时表

explain (select 1000 as f) union (select id from t1 order by id desc limit 2);

通过上图可以看出，在我们进行union的时候使用了临时表，上述语句执行过程如下：

1. 创建一个内存临时表，临时表只有一个整型字段f，并且f是主键字段
2. 执行第一个子查询，将1000这个值插入临时表中
3. 执行第二个子查询：获取第一行（id=1000），插入临时表中，由于1000已经在临时表中存在，因此插入失败继续执行；取第二行（id=999）插入成功
4. 从临时表中逐行取出数据，返回结果，并删除临时表

注意：union需要使用到临时表，但是union all不需要。

group by使用内部临时表

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

通过上图可以看出，在我们进行group by 的时候使用了临时表，上述语句执行过程如下：

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

内存临时表转磁盘临时表

当临时表的数据量没有超过限制时，会使用内存临时表，但如果超过了内存的限制，将会转为磁盘临时表，引擎默认使用InnoDB。该限制由参数tmp_table_size决定（默认值16M）：

show global variables like 'tmp_table_size';

group by优化之索引

group by之所以需要临时表，是因为id%100的结果是无序的，我们需要一个临时表来统计结果，但是如果可以保证id%100的结果是有序的，那么在计算group by的时候，只需要从左往右顺序扫描。依次累加：

• 当碰到第一个1时，可以得出累积了X个0，结果集里面的第一行就是(0, X)
• 当碰到第二个2时，可以得出累积了Y个1，结果集里面的第二行就是(1, Y)

InnoDB的索引就可以满足上述有序条件，MySQL 5.7版本以后支持了generated column机制，用来实现列数据的关联更新，可以用以下语句进行优化：

-- 该语句创建了一个列Z，并且在Z上创建了一个索引
alter table t1 add column z int generated always as(id % 100), add index(z);

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

group by优化直接排序

如果group by的数据量比较大，先插入内存临时表一部分数据后，发现内存临时表放不下了需要再转成磁盘临时表，这部分过程也是耗时的，那么如何让group by直接走磁盘临时表呢？

在group by语句中加入SQL_BIG_RESULT提示，告诉优化器使用磁盘临时表。但是MySQL优化器出于对存储效率的考虑，不会使用B+数存储，而是直接使用数组。

explain 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做排序
4. 排序完成后，得到一个有序数组
5. 根据有序数组，得到数组里面不同的值以及每个值出现的次数