MySQL什么时候会使用内部临时表？

该文章为《MySQL实战45讲》课程学习笔记及部分摘抄，原课程链接MySQL 实战 45 讲 (geekbang.org)

        在第 16和第 34篇文章中，我分别和你介绍了 sort buffer、内存临时表和 join buffer。这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助 SQL 语句的执行的。其中，我们在排序的时候用到了 sort buffer，在使用 join 语句的时候用到了 join buffer。

        MySQL 什么时候会使用内部临时表呢？

        今天这篇文章，举两个需要用到内部临时表的例子，来看看内部临时表是怎么工作的。然后，我们再来分析，什么情况下会使用内部临时表。

一、union 执行流程

        为了便于量化分析，我用下面的表 t1 来举例。

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);

        这条语句用到了 union，它的语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行。

        下图是这个语句的 explain 结果。

 图 1 union 语句 explain 结果

        可以看到：

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

        这个语句的执行流程是这样的：

1. 创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。
2. 执行第一个子查询，得到 1000 这个值，并存入临时表中。
3. 执行第二个子查询：
   1. 拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；
   2. 取到第二行 id=999，插入临时表成功。

        4.从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999。

        这个过程的流程图如下所示：