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

[git-zjx]

两个容易误解的概念

• 内存表：指的是使用 Memory 引擎的表，表的数据都保存在内存里，系统重启的时候会被情况，但是表结构还在
• 临时表：可以使用各种引擎类型。如果使用 InnoDB 引擎或者 MyISAM 引擎的临时表，数据是写到磁盘上的，也可以使用 Memory 引擎

临时表的特性

1. 建表语法是 creat temporary table ...
2. 一个临时表只能被创建它的 session 访问，对其他线程不可见
3. 临时表可以和普通表同名
4. session 内有同名的临时表和普通表时，show create 语句，以及增删改查语句访问的是临时表
5. show tables 命令不显示临时表
6. session 结束时，临时表会自动销毁

临时表适合 join 优化的原因

1. 不同 session 的临时表是可以重名的，如果多个 session 同时执行 join 优化，不需要担心因表名重复导致建表失败的问题
2. 不需要担心数据删除的问题，临时表会自动回收

临时表在分库分表场景下的应用

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

在这个架构中，是以字段 f 作为分区键，如果查询条件中没有字段 f，例如：

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

这种情况就只能到所有分区中查询满足条件的数据，一般有两种解决方案：

1. 在 proxy 层的进程代码中实现排序
   优势是速度快，拿到分库的数据之后，直接在内存中参与计算。
   缺点在于需要开发的工作量比较大，特别是涉及到复杂的操作，而且对 proxy 端的压力比较大，容易出现内存不够和 CPU 瓶颈问题
2. 将各分库的数据汇总到一个 MySQL 实例的表中，然后操作该表
   执行流程大致为：

• 在汇总库上创建一个临时表 temp_ht
• 在各个分库上执行

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 引入了一个临时文件表空间，专门用来存放临时文件数据

从文件的前缀规则来看，创建一个叫做 t1 的临时表和普通表 t1 在存储上是不同的，所以可以和普通表重名。
而且 MySQL 维护数据表时，除了物理上有文件外，内存里也有一套机制区别不同的表，每个表都对应一个 table_def_key

• 一个普通表的 table_def_key 的值是由 库名+表名 得到的
• 一个临时表的 table_def_key 的值在 库名+表名 的基础上又加入了 server_id + thread_id
  也就是说，session A 和 session B 创建的两个临时表 t1，它们的 table_def_key 不同，磁盘文件名也不同，所以可以并存。

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

临时表和主备复制

在 statement 和 mixed 格式下，所有对临时表的操作都要记录 binlog，但在 row 格式下，只有 Drop table 才会记录 binlog。

因为在一些特殊情况下，还是需要将主机的临时表同步到备机的，比如主机上执行 insert into t1 select * from temp1，其中t1是普通表，而temp1是临时表。当binlog格式为statement时，这条语句会被记录到binlog，然后同步到备机，在备机上replay，若备机之前没有将主机上的临时表同步过来，那这条语句的replay就会出现问题。因此在statement格式下，对临时表的操作如创建、删除及其它DML语句都必须记录binlog，然后同步到备机执行replay。但在row格式下，因为binlog中已经记录了实际的row，那么对临时表的创建、DML语句是不是记录binlog就不是那么重要了

对临时表的删除还是要记录binlog。因为用户可以随时修改binlog的格式，若之前创建临时表时是statement格式，而创建成功后，又修改为row格式，若row格式下删除表不记录binlog，那么在备机上就会产生问题，创建了临时表，但却没有删除它。因此对drop table语句，无论binlog格式采用statement或是row格式，都会记录binlog

[git-zjx]

主库上不同的线程创建同名的临时表是没关系的，但是传到备库执行是怎么处理的呢？

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

1. session A 的临时表 t1，在备库的 table_def_key 就是：库名 + t1 + M 的 server_id + session A 的 thread_id
2. session B 的临时表 t1，在备库的 table_def_key 就是：库名 + t1 + M 的 server_id + session B 的 thread_id

[git-zjx]

为什么不能用 rename 修改临时表的表名？

在实现上，执行 rename table 语句的时候，要求按照 库名/表名.frm 的规则去磁盘找文件，但是临时表在磁盘上的 frm 文件是放在 tmpdir 目录下的，并且文件名的规则是 #sql{进程id}_{线程id}_序列号.frm，因此会报 找不到文件名 的错误