前面介绍了sort buffer，内存临时表和join buffer，这3个数据结构都是用来存放语句执行过程中的中间数据，以辅助sql语句的执行的，在排序的时候用sort buffer，在使用join语句的时候使用了join_buffer。在mysql里，什么时候使用内部临时表呢？
create table t37(id int primary key, a int, b int, index(a)); delimiter ;; create procedure idata_t37() begin declare i int; set i=1; while(i<=1000)do insert into t37 values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata_t37(); 执行语句 (select 1000 as f) union (select id from t37 order by id desc limit 2);
第二行 key=PRIMARY，用了索引 id
第三行Extra ：结果集union用临时表 (Using temporary)。
创建一个内存临时表，只有整型字段 f，主键。
第一个子查询，得到 1000 这个值，存入临时表中。
执行第二个子查询：拿到 id=1000，违反了唯一性约束，插入失败；取第二行 id=999，插入成功。
包含 1000 和 999。
图 2 union 执行流程
union 改成 union all 的话，不去重”的语义。依次子查询。不需临时表。
图 3 union all 的 explain 结果
Using index，表示只使用了覆盖索引，没用临时表。
select id%10 as m, count(*) as c from t1 group by m;
t1 数据，按id%10 分组统计，按m 结果排序后输出
[endif]不支持该协议取消重新上传图 4 group by 的 explain 结果
Using index，覆盖索引 a，不需回表；Using temporary临时表；Using filesort，需排序
创建内存临时表，字段 m （主键）和 c
[扫描 t1 的索引 a，依次取出叶子节点上id 值，计算 id%10 的结果，记为 x；
临时表中没有主键 x 行，插入(x,1)；有，这一行的 c 值加 1；
遍历完，根据m排序返回
图 5 group by 执行流程
图 6 内存临时表排序流程 （虚线框内）
图 7 group by 执行结果
不需排序，末尾增加 order by null：select id%10 as m, count(*) as c from t1 group by m order by null;
图 8 group + order by null 的结果（内存临时表）
t1 中的 id 从 1 开始，第一行是 id=1，临时表只有 10 行，内存放得下，tmp_table_size 控内存大小，默认16M。
set tmp_table_size=1024;//最大 1024 字节 select id%100 as m, count(*) as c from t1 group by m order by null limit 10;
id % 100，返回结果 100 行不够存，转成磁盘临时表：数据量大，查询磁盘临时表会占用大量磁盘空间。
图 9 group + order by null 的结果（磁盘临时表）
group by 需要唯一索引，执行代价高。数据量大，group by 慢：统计不同值出现个数。 id%100 结果是无序，需个临时表，记录并统计结果。
图 10 group by 算法优化 - 有序输入
数据有序，group by 的时候，只需左到右，顺序扫描，不需临时表和排序：
碰到第一个1 时，已知累积 X 个 0，结果集第一行(0,X);
碰到第一个 2 时，已知累积Y 个 1，结果集第二行(1,Y);
InnoDB 索引，满足输入有序的条件。MySQL 5.7 版本支持generated column 实现列数据关联更新。创建列 z， z 上创建索引（5.6 之前，可以创建普通列和索引解决）。
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;
Extra 看到，不再需要临时表和排序
图 11 group by 优化的 explain 结果
不适合建索引场景，怎么优化呢？放到临时表数据量大，直接走磁盘临时表
group by 加SQL_BIG_RESULT 提示（hint）优化器：量大用磁盘临时表。
磁盘临时表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 排序（内存不够，用磁盘临时文件辅助排序）；
排序完成后，得有序数组（不同值，每个值出现次数）。
图 12 使用 SQL_BIG_RESULT 的执行流程图
图 13 使用 SQL_BIG_RESULT 的 explain 结果
Extra ：没用临时表，直接用排序算法。
1. 可边读数据，边得结果，不额外内存，否则就需额外内存，保存中间结果；
2. 用到二维表特性，用临时表。union用到唯一索引约束， group by 用另外一个字段存累积计数。
join_buffer 是无序数组，sort_buffer 是有序数组，临时表二维表结构；
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 结果
原文链接: https://www.yukx.com/bingningm/article/details/1785.html 优科学习网什么时候会使用内部临时表
