没有合适的资源?快使用搜索试试~ 我知道了~
资源详情
资源评论
资源推荐
37 | 什么时候会使用内部临时表?
2019-02-06 林晓斌
今天是大年初二,在开始我们今天的学习之前,我要先和你道一声春节快乐!
在第16和第34篇文章中,我分别和你介绍了sort buffer、内存临时表和join buffer。这三个数据
结构都是用来存放语句执行过程中的中间数据,以辅助SQL语句的执行的。其中,我们在排序的
时候用到了sort buffer,在使用join语句的时候用到了join buffer。
然后,你可能会有这样的疑问,MySQL什么时候会使用内部临时表呢?
今天这篇文章,我就先给你举两个需要用到内部临时表的例子,来看看内部临时表是怎么工作
的。然后,我们再来分析,什么情况下会使用内部临时表。
union union 执行流程执行流程
为了便于量化分析,我用下面的表t1来举例。
然后,我们执行下面这条语句:
这条语句用到了union,它的语义是,取这两个子查询结果的并集。并集的意思就是这两个集合
加起来,重复的行只保留一行。
下图是这个语句的explain结果。
图1 union语句explain 结果
可以看到:
第二行的key=PRIMARY,说明第二个子句用到了索引id。
第三行的Extra字段,表示在对子查询的结果集做union的时候,使用了临时表(Using
temporary)。
这个语句的执行流程是这样的:
1. 创建一个内存临时表,这个临时表只有一个整型字段f,并且f是主键字段。
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 while;
end;;
delimiter ;
call idata();
(select 1000 as f) union (select id from t1 order by id desc limit 2);
2. 执行第一个子查询,得到1000这个值,并存入临时表中。
3. 执行第二个子查询:
拿到第一行id=1000,试图插入临时表中。但由于1000这个值已经存在于临时表了,违
反了唯一性约束,所以插入失败,然后继续执行;
取到第二行id=999,插入临时表成功。
4. 从临时表中按行取出数据,返回结果,并删除临时表,结果中包含两行数据分别是1000和
999。
这个过程的流程图如下所示:
图 2 union 执行流程
可以看到,这里的内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键id的唯
一性约束,实现了union的语义。
顺便提一下,如果把上面这个语句中的union改成union all的话,就没有了“去重”的语义。这样执
行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不
需要临时表了。
图3 union all的explain结果
可以看到,第二行的Extra字段显示的是Using index,表示只使用了覆盖索引,没有用临时表
了。
group by group by 执行流程执行流程
另外一个常见的使用临时表的例子是group by,我们来看一下这个语句:
这个语句的逻辑是把表t1里的数据,按照 id%10 进行分组统计,并按照m的结果排序后输出。它
的explain结果如下:
图4 group by 的explain结果
在Extra字段里面,我们可以看到三个信息:
Using index,表示这个语句使用了覆盖索引,选择了索引a,不需要回表;
Using temporary,表示使用了临时表;
Using filesort,表示需要排序。
这个语句的执行流程是这样的:
1. 创建内存临时表,表里有两个字段m和c,主键是m;
2. 扫描表t1的索引a,依次取出叶子节点上的id值,计算id%10的结果,记为x;
如果临时表中没有主键为x的行,就插入一个记录(x,1);
如果表中有主键为x的行,就将x这一行的c值加1;
3. 遍历完成后,再根据字段m做排序,得到结果集返回给客户端。
这个流程的执行图如下:
select id%10 as m, count(*) as c from t1 group by m;
剩余15页未读,继续阅读
金山文档
- 粉丝: 22
- 资源: 306
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论0