内部临时表的运用

Union

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

这个语句的执行流程是这样的：

创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段。
执行第一个子查询，得到1000这个值，并存入临时表中。
执行第二个子查询：
- 拿到第一行id=1000，试图插入临时表中。但由于1000这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；
- 取到第二行id=999，插入临时表成功。
从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是1000和999。

如果把上面这个语句中的union改成union all的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

group by

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

这个语句的执行流程是这样的：

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

优化：

保证分组字段有序

如果在group by扫描过程中可以保证出现的数据是有序的，这样就不需要临时表了。

在MySQL 5.7版本支持了generated column机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列z，然后在z列上创建一个索引

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

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

直接排序

在group by语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。MySQL的优化器一看，磁盘临时表是B+树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

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

执行流程就是这样的：

初始化sort_buffer，确定放入一个整型字段，记为m；

扫描表t1的索引a，依次取出里面的id值, 将 id%100的值存入sort_buffer中；

扫描完成后，对sort_buffer的字段m做排序（如果sort_buffer内存不够用，就会利用磁盘临时文件辅助排序）；

排序完成后，就得到了一个有序数组。

根据有序数组，得到数组里面的不同值，以及每个值的出现次数。