我们已经知道了临时表 sort_buffer join_buffer
都是用来存放语句在执行过程中的中间数据,以及辅助SQL语句执行的
排序的时候使用到了sort buffer
join的时候用到了join buffer
那么,什么时候使用了内部临时表呢?
我们拿union执行流程来分析
假设有一个表,表上有1000数据,执行如下的语句
(select 1000 as f) union (select id from t1 order by id desc limit 2);
那么看一下explain语句的执行结果
这个语句使用了union语义,去这两个子查询结果的并集,并集的意思就是两个集合加一起,重复的只保留一行
可以看出,第一行没有使用任何数据表,查看第二行的key=PRIMARY,说明第二个语句使用了主键id,第三行的Extra字段,表示在对子查询的时候做了union,使用了临时表(Using temporary)
于是,整个执行流程如下
1.查询第一个表,发现没有数据表相关的信息
2.但是得到了1000这个值,存入临时表中
3.进入了第二个子查询,
拿到了第一行1000,发现临时表中已经存在了,插入失败,执行继续
拿到了第二行999,插入成功
4.从内存临时表中返回数据1000和999
如上就是执行流程
这里的内存临时表进行了数据的缓存,还利用了主键id的唯一性约束
如果上面的语句将union改为了union all的话,那么就没有去重的部分,那么就不需要临时表了
另外常见的临时表使用就是group by的执行流程
比如就是
select id%10 as m,count(*) as c from t1 group by m;
上面的流程为:
按照id%10进行统计,然后按照统计完成的结果排序后输出
上面的字段有
Using index 表示上面的语句使用了覆盖索引,使用了索引a,不需要回表
Using temporary 表示使用了临时表
Using filesort 表示需要排序
1.创建出一个内存临时表,上面有两个字段m和c,主键是m
2.扫描表t1的索引a,取出主键id,然后计算出id%10,即为x,放入临时表中,如果没有主键为x的行,就插入,有就更新
3.遍历完成,根据m做排序,结果集返回客户端
最后一步,对临时表进行排序
因为groupby后,必须是有序的
如果不需要拍下的话,可以使用order by null
当然,因为这个临时表只有10行,内存可以放得下,
临时表是具有大小的,tmp_table_size就是控制内存大小的,默认为16M
如果设置了内存大小 set tmp_table_size = 1024;内存临时表大小上限为1024字节
导致内存表大小不够,怎么办?
MySQL会转换为了磁盘临时表
group by的优化
如果表的数据量比较大,那么上面的group by执行起来会很慢
group by出来的结果是无序的,所以需要一个临时表来记录并统计数据
如果本身插入的数据是有序的呢,是不是就简单了呢
对此,我们可以利用一个5.7版本后,提供了generated column机制,实现了列数据的关联更新
可以通过下面的语句,创建一个列,专门用来存储%10后的数据
alter table t1 add column z int generated always as(id % 10), add index(z);
索引z上的数据上就是有有序的,在使用这个group by查询就不需要排序了
比如
select z count(*) as c from t1 group by z;
对于group by的一种优化,如果不通过加索引来完成group by,怎么办呢
加入一个查找所需的数据量特别大的话,如果按照基本流程,先出现一个内存临时表,再去使用磁盘临时表的话,有点迂腐
能不能直接使用磁盘临时表呢
可以在语句中加入SQL_BIG_RESULT这个提示,说明这个数据量很大
MySQL的优化器一看,就会使用磁盘临时表
比如,如下的语句
select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
这上面的执行流程为
sort_buffer 放入一个整型字段 即为m;
扫描t1的索引a,取出里面的id值,存入sort_buffer
扫描完成后,对sort_buffer中的字段m排序
得到有序数组
