内部临时表
union中的临时内存表应用
（ sql1 ）union （sql2） 语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行。union改成union all的话，就没有了“去重”的语义。 在对子查询的结果集做union的时候，使用了临时表(Using temporary)
union的执行流程：
- 创建一个内存临时表
- 执行sql1 取到结果，存入临时表
- 执行第二个查询，插入临时表。
- 从临时表中按行取出数据吗，返回结果，并删除临时表。
group by 中的临时内存表应用
select id%10 as m, count(*) as c from t1 group by m;
语句的执行流程是这样的：
- 创建内存临时表，表里有字段m和c，PK：m
- 扫描表t1的索引a，依次取出叶子节点的id值，计算id%10的结果，记为x 如果临时表中没有逐渐为x，则插入记录（x,1）又则将这一行的c值+1
- 遍历完成后，根据m字段做排序，得到结果后返回给客户端
如果你的需求并不需要对结果进行排序，那你可以在SQL语句末尾增加order by null。
内存临时表的大小是有限制的，参数tmp_table_size就是控制这个内存大小的，默认是16M。如果超过，mysql就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB。数据量很大，很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。
优化方法
group by的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的id%10的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。 如果可以确保输入的数据是有序的，那么计算group by的时候，就只需要从左到右，顺序扫描，依次累加。（ 用索引解决）
在group by语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。MySQL优化器会判断磁盘临时表是B+树存储，存储效率不如数组来得高，于是MySQL优化器就直接用数组来存。
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
SQL_BIG_RESULT的执行流程：
- 初始化sort_buffer，确定放入一个整型字段，记为m；
- 扫描表t1的索引a，依次取出里面的id值, 将 id%100的值存入sort_buffer中；
- 扫描完成后对sort_buffer的字段m进行排序（不够用就利用磁盘临时文件辅助排序）
- 排序完成后得到一个有序数组
- 根据有序数组，得到数组里面的不同值，以及每个值的出现次数。
优化思路
-
如果对group by语句的结果没有排序要求，要在语句后面加 order by null；
-
尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；
-
如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
-
如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。
总结
MySQL什么时候会使用内部临时表？
-
如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
-
join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；
-
如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数。
