36 | 为什么临时表可以重名？

---

MySQL45讲

实践篇

36 | 为什么临时表可以重名？

临时表和内存表是两个完全不同的概念。

• 内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。表结构创建在磁盘上，表数据保存在内存里，系统重启的时候会被清空，但是表结构还在。 除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
• 临时表，可以使用各种引擎类型 。 如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上（表结构 .frm 和临时文件表空间）。当然，临时表也可以使用 Memory 引擎。

临时表的特性

临时表在使用上有以下几个特点：

• 建表语法是 create temporary table …。
• 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。
• 临时表可以与普通表同名。
• session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表，即临时表优先级高于普通表。
• show tables 命令不显示临时表。

由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。

正是由于这个特性，临时表就特别适合 join 优化这种场景。原因主要包括以下两个方面：

• 不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。
• 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

临时表的应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景。

一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。比如。将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上。如下图所示：

在这个架构中，分区 key 的选择是以“减少跨库和跨表查询”为依据。如果大部分的语句都会包含 f 的等值条件，那么就要用 f 做分区键。

比如下面这条语句：

select v from ht where f=N;

这时，可以通过分表规则（比如，N%1024) 来确认需要的数据被放在了哪个分表上。

但是，如果这个表上还有另外一个索引 k，并且查询语句是这样的：

select v from ht where k >= M order by t_modified desc limit 100;

由于查询条件里面没有用到分区字段 f，只能到所有的分区中去查找满足条件的所有行，然后统一做 order by 的操作。这种情况下，有两种比较常用的思路：

第一种思路是，在 proxy 层的进程代码中实现排序。

这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。不过，这个方案的缺点也比较明显：

• 需要的开发工作量比较大。如果涉及到复杂的操作，比如 group by，甚至 join 这样的操作，对中间层的开发能力要求比较高；
• 对 proxy 端的压力比较大，尤其是很容易出现内存不够用和 CPU 瓶颈的问题。

另一种思路就是，把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。

以上面这条语句为例，执行流程可以如下：

• 在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；

• 在各个分库上执行

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

• 把分库执行的结果插入到 temp_ht 表中；

• 执行

select v from temp_ht order by t_modified desc limit 100; 

为什么临时表可以重名？

create temporary table temp_t(id int primary key)engine=innodb;

在执行这个语句的时候，MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义，还要有地方保存表数据。

这个 frm 文件放在临时文件目录下，文件名的后缀是 .frm，前缀是 “#sql{进程 id} _ {线程 id} _ 序列号”。使用 select @@tmpdir 命令，来显示实例的临时文件目录。

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

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

简单来说，MySQL 5.7 后的 frm 文件保存临时表结构，临时文件表空间保存临时表数据。

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

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

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

临时表和主备复制

有些时候会遇见这种情况，binlog 中记录了 DROP TEMPORARY TABLE 命令。

疑问：临时表只在线程内自己可以访问，为什么需要写到 binlog 里面？

既然写 binlog，就意味着备库需要。

假设在主库上执行下面这个语句序列：

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录，那么在备库就只有 create table t_normal 表和 insert into t_normal select * from temp_t 这两个语句的 binlog 日志，备库在执行到 insert into t_normal 的时候，就会报错“表 temp_t 不存在”。

当 binlog_format=row，跟临时表有关的语句，不会记录到 binlog 里；
当 binlog_format=statment/mixed，binlog 中会记录临时表的操作。

在 binlog_format=statment/mixed 的时候，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程却是在持续运行。所以，这时候需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行。

疑问：主库上不同的线程可以创建同名的临时表，但是传到备库执行怎么处理？

备库线程在执行的时候，把这两个 t1 表当做两个不同的临时表来处理。

MySQL 在记录 binlog 的时候，会把主库执行这个语句的线程 id 写到 binlog 中。这样，在备库的应用线程就能够知道执行每个语句的主库线程 id，并利用这个线程 id 来构造临时表的 table_def_key：

• session A 的临时表 t1，在备库的 table_def_key 就是：库名 +t1+“M 的 serverid”+“session A 的 thread_id”；
• session B 的临时表 t1，在备库的 table_def_key 就是 ：库名 +t1+“M 的 serverid”+“session B 的 thread_id”。

由于 table_def_key 不同，所以这两个表在备库的应用线程里面不会冲突。