MySQL 之临时表

1、概述

MySQL 中临时表主要有两类，包括外部临时表和内部临时表。

• 内部临时表：内部临时表主要有两类

  • 一类是 information_schema 中临时表
  • 一类是会话执行查询时，数据库优化器为了协助复杂SQL的执行而自行创建的临时表，用户可以通过 explain 命令，在 Extra 列中，看是否有 Using temporary，如果有就是用了内部临时表

• 外部临时表：用户通过语句 create temporary table... 创建的表是临时表，临时表只在本会话有效，会话断开后，临时表数据会自动清理。

区别：内部临时表与外部临时表的一个区别在于，我们看不到内部临时表的表结构定义文件 frm。外部临时表的表定义文件 frm，一般是以 #sql{进程id}_{线程id}_序列号 组成，因此不同会话可以创建同名的临时表。

2、临时表的特点

临时表VS普通表

临时表与普通表的主要区别在于是否在实例，会话，或语句结束后，自动清理数据。比如，内部临时表，我们在一个查询中，如果要存储中间结果集，而查询结束后，临时表就会自动回收，不会影响用户表结构和数据。

另外就是，不同会话的临时表可以重名，所有多个会话执行查询时，如果要使用临时表，不会有重名的担忧。5.7引入了临时表空间后，所有临时表都存储在临时表空间(非压缩)中，临时表空间的数据可以复用。

临时表并非只支持Innodb引擎，还支持myisam引擎，memory引擎等。因此，临时表我们看不到实体(idb文件)，但其实不一定是内存表，也可能存储在临时表空间中。

临时表 VS 内存表

内存表，是说 存储引擎为 memory 的表，建表语句是 create table ...engine=memory，数据全部在内存，因此如果是重启的话，数据不会保留，但表结构是保留的，表结构通过 frm 管理，但是我们看不到frm，甚至看不到 information_schema 在磁盘上的目录。

总结：内存表就是正常的表，只不过是存储引擎为memory，且重启后数据不会保留

memory 引擎表实际上也是可以创建索引的，包括 Btree 索引和 Hash 索引，所以查询速度很快，主要缺陷是内存资源有限。

临时表可以是任何存储引擎，可以 innodb 引擎表，也可以是 memory 引擎表。

在 MySQL 内部，information_schema 里面的临时表就包含两类：innodb 引擎的临时表和 memory 引擎的临时表。比如 TABLES 表属于 memory 临时表，而 columns,processlist 属于 innodb 引擎临时表。

3、内部临时表

内部临时表 用来存储某些操作的【中间结果】，这些操作可能包括在【优化阶段】或者【执行阶段】，这种内部表对用户来说是不可见的。通常在执行复杂 SQL 语句时，比如 group by，distinct，union 等语句时，MySQL内部将使用【自动生成的临时表】，以辅助SQL的执行。我们可以使用执行计划查看，如果一条sql语句的执行计划中【列extra】结果为Using temporary，那么也就说明这个查询要使用到临时表。

• union

  union 操作的含义是，取两个子查询结果的并集，重复的数据只保留一行，通过建立一个带主键的临时表，就可以解决“去重”问题，通过临时表存储最终的结果集，所以能看到执行计划中Extra这一项里面有“Using temporary”。

  与union相关的一个操作是union all，后者也是将两个子查询结果合并，但不解决重复问题。所以对于union all，没有“去重”的含义，因此也就不需要临时表了。

• group by

  group by 的含义是按指定列分组，并默认按照指定列有序。

  对于 group by 而言，我们首先需要统计每个值出现的数目，这就需要借助临时表来快速定位，如果不存在，则插入一条记录，如果存在，并累加计数，所以看到了”Using temporary”；

  group by 并非一定依赖临时表，临时表在 group by 中的作用主要是“去重”。

  所以，实际上有另外一种方式，不使用临时表，直接利用 sort_buffer 排序(sort_buffer 不够时，进行文件排序，具体而言是每一个有序数组作为一个单独文件，然后进行外排归并)，然后再扫描得到聚合后的结果集。

4、外部临时表

-- 通过create temporary table语句创建的临时表为外部临时表
CREATE TEMPORARY TABLE table_name(
	id int,
	name varchar(10)
) ENGINE = InnoDB;

-- 插入
insert into table_name values (1,'1');

-- 查询
select * from table_name;
 
-- 删除临时表
DROP TEMPORARY TABLE table_name;

MySQL 要给这个临时表创建一个 frm 文件保存表结构定义，还要有地方保存表数据。

• 保存表结构定义的 frm 文件放在临时文件目录下，文件名的后缀是 .frm，前缀是 #sql{进程 id}_{线程 id}_序列号 **。可以使用 select @@tmpdir 命令，来显示实例的临时文件目录。

• 表中数据的存放方式，在不同的 MySQL 版本中有着不同的处理方式：

  • 5.6 以及之前的版本，MySQL 会在临时文件目录下创建一个相同前缀、以 .ibd 为后缀的文件，用来存放数据文件。
  • 5.7 版本开始，MySQL 引入了一个临时文件表空间，专门用来存放临时文件的数据。因此，就不需要再创建 ibd 文件了。

从文件名的前缀规则，可以看到，其实创建一个叫作 t1 的 InnoDB 临时表，MySQL 在存储上认为创建的表名跟普通表 t1 是不同的，因此同一个库下面已经有普通表 t1 的情况下，还是可以再创建一个临时表 t1 的。而且不同的线程也可以重名。

MySQL 维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个 table_def_key。

• 普通表的 table_def_key 的值由 库名 + 表名 得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现 table_def_key 已经存在了。
• 临时表的 table_def_key 的值在 库名 + 表名 基础上，又加入了 server_id + thread_id。

也就是说，session A 和 sessionB 创建的两个临时表 t1，它们的 table_def_key 不同，磁盘文件名也不同，因此可以并存。

在实现上，每个线程都维护了自己的临时表链表。这样每次 session 内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在 session 结束的时候，对链表里的每个临时表，执行 DROP TEMPORARY TABLE + 表名 操作。

这时候你会发现，binlog 中也记录了 DROP TEMPORARY TABLE 这条命令。你一定会觉得奇怪，临时表只在线程内自己可以访问，为什么需要写到 binlog 里面？主要是为了主从同步。因为对于主库来说，如果当前线程退出了，这个临时表就删除了。对于从库来说，这个线程是复制线程，不会退出，就会导致这个临时表一直存在，而占用空间。，

Reference

• https://www.cnblogs.com/wqbin/p/13219025.html
• https://blog.csdn.net/DBdoctor_off/article/details/141668013