内部临时表应用-union与group by

[MySQL-order by工作原理](/MySQL/order by工作原理)
MySQL-join执行原理

sort buffer、join buffer、内部临时表都是用来存放语句执行过程中的中间数据，以辅助SQL语句的执行。

union执行流程

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 where;  
end;;  
delimiter ;  
call idata();

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

id	select_type	table	partitions	tyoe	possible_keys	key	key_len	ref	rows	filtered	Extra
1	primary	null	null	null	null	null	null	null	null	null	No tables used
2	union	t1	null	index	null	primary	4	null	2	100.00	Using index
null	union result	<uoion1,2>	null	all	null	null	null	null	null	null	Using temporary

第二行的key=primary，说明第二个子句用到了索引id。
第三行的Extra字段，表示在对子查询的结果集做union的时候，使用了临时表(Using temporary)。

执行流程：

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

这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键id的唯一性约束，实现了union的语义。

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

id	select_type	table	partition	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	primary	null	null	null	null	null	null	null	null	null	No tables used
2	union	t1	null	index	null	primary	4	null	2	100.00	Using index

第二行的Extra字段显示的是Using index，表示只使用了覆盖索引。没有用临时表的记录。

group by执行流程

1 2	select id%10 as m, count(*) as c from t1 group by m; -- 把表t1里的数据，按照id%10进行分组统计，并按照m的结果排序后输出。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	simple	t1	null	index	primary,a	a	5	null	1000	100.00	Using index; Using temporary;

Using index，表示这个语句使用了覆盖索引，选择了索引a，不需要回表；
Using temporary，表示使用了临时表；
Using filesort，表示需要排序。

执行流程：

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

内存临时表+(sort buffer)进行排序MySQL-随机排序

如果不需要对结果进行排序，可以在SQL语句末尾增加order by null。

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

表t1中的id值是从1开始的，返回的结果集中第一行是id=1；扫描到id=10的时候才插入m=0这一行，结果集里最后一行才是m=0。

示例中临时表只有10行，内存可以放得下，全程只使用了内存临时表。内存临时表的大小是有限制的，参数tmp_table_size控制其内存大小，默认16M。

1
2
3

set tmp_table_size=1024;  
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;  
-- 把内存临时表的大小限制为最大1024字节，并把语句改成id%100，这样返回结果里有100行数据。

内存临时表大小不够存下100行数据，执行过程中发现内存临时表大小到达了上限，这时会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB。如果这个表t1的数据量很大，很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。

group by优化方法–索引

不论是使用内存临时表还是磁盘临时表，group by逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。

group by的语句逻辑，是统计不同的值出现的个数。但是由于每一行的id%100的结果是无序的，所以需要有一个临时表，来记录并统计结果。

如果可以确保输入的数据是有序的，那么计算group by的时候，就只需要从左到右顺序扫描，依次累加。

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

按照这个逻辑执行，扫描到整个输入的数据结束，就可以拿到group by的结果，不需要临时表，也不需要再额外排序。

InnoDB的索引，可以满足这个输入有序的条件。
在MySQL5.7版本支持了generated column机制，用来实现列数据的关联更新。

1
2
3

alter table t1 add column z int generated always as(id%100), add index(z);  
-- 创建一个列z，然后在z列上创建一个索引。这样索引z上的数据就是有序的了。  
-- MySQL5.6及之前的版本，可以创建普通列和索引来解决这个问题。

原始group by语句优化：

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

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	simple	t1	null	index	z	z	5	null	1000	100.00	Using index

从Extra字段可以看到，语句的执行不再需要临时表，也不需要排序。

group by优化方法–直接排序

不适合创建索引的场景，老老实实做排序。

针对语句要放到临时表上的数据量特别大，超过内存临时表，需要磁盘临时表的group by语句。在语句中加入SQL_BIG_RESULT这个提示(hint)，告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

磁盘临时表是B+树存储，存储效率不如数组来的搞。优化器从磁盘空间考虑，直接用数组来存。

1	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内存不够用，就会利用磁盘临时文件辅助排序)；
排序完成后，就得到了一个有序数组。

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

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	simple	t1	null	index	primary,a	a	5	null	1000	100.00	Using index; Using filesort

Extra字段分析，语句的执行没有使用临时表，而是直接使用了排序算法。

如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；
如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。union需要用到唯一索引约束，group by还需要用到另外一个字段来存累积计数。

总结

如果对group by语句的结果没有排序要求，要在语句后面加order by null(跳过最后排序的阶段不需要sort buffer，直接从临时表中取数据返回)；
尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary和Using filesort；(Using index表示使用了覆盖索引，如果覆盖索引的顺序扫描不能满足需求，只能排序，排序过程中用到临时表，就会三个Using都有)
如果group by需要统计的数据量不大，尽量只适用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。