MySQL45讲 第三十六讲 为什么临时表可以重名？——阅读总结

MySQL45讲 第三十六讲 为什么临时表可以重名？——阅读总结

一、引言

在 MySQL 数据库的使用中，临时表是一个非常有用的工具。在之前优化 join 查询的文章里，我们就用到了临时表。那么，临时表到底是什么？它有哪些特性使得它适用于特定场景？

二、临时表与内存表的区别

（一）内存表

内存表使用 Memory 引擎，建表语法为 create table … engine=memory。其数据保存在内存中，系统重启时数据会被清空，但表结构依然存在。从其他特征来看，它和普通表类似。

（二）临时表

临时表可以使用多种引擎类型，如 InnoDB 或 MyISAM 引擎的临时表在写数据时会写到磁盘上，当然也可以使用 Memory 引擎。

三、临时表的特性

（一）可见性与生命周期

1. 一个临时表只能被创建它的 session 访问，对其他线程不可见。例如，在图 1 中，session A 创建的临时表 t，session B 无法看到。

2. session 结束时，临时表会自动删除。

   

（二）与普通表的关系

1. 临时表可以与普通表同名。
2. 当 session 内有同名的临时表和普通表时，show create 语句以及增删改查语句访问的是临时表。例如，在图 1 中，session A 创建了临时表 t 和普通表 t 后，show create table t 显示的是临时表 t 的结构。
3. show tables 命令不显示临时表。

四、临时表的应用场景

（一）分库分表系统的跨库查询

1. 分库分表架构
   **在分库分表场景中，常将一个大表分散到不同数据库实例上，中间层 proxy 负责解析 SQL 语句并路由到相应分表查询。**例如，将大表 ht 按字段 f 拆分成 1024 个分表分布到 32 个数据库实例上。

2. 跨库查询问题与解决方案

对于包含分区字段 f 的等值条件查询，可直接路由到对应分表查询。但当查询条件不包含分区字段时，如 select v from ht where k>= M order by t_modified desc limit 100;，需要在所有分区查找数据后统一排序。

• proxy 层排序：优势是处理速度快，在内存中计算，但开发工作量大，对 proxy 端压力大，易出现内存和 CPU 瓶颈。
• 使用临时表汇总数据后排序：在汇总库创建临时表 temp_ht，包含相关字段，从各分库获取数据插入临时表后再排序查询。实际中常将临时表放到某个分库上，其查询逻辑与图 3 类似。

五、临时表重名的原理

（一）文件存储方式

1. 执行 create temporary table 语句时，MySQL 会为 InnoDB 临时表创建 frm 文件保存表结构定义，文件存放在临时文件目录下，文件名后缀为.frm，前缀是 “#sql {进程 id}_{线程 id}_序列号”，可通过 select @@tmpdir 查看临时文件目录。
2. 在 5.6 及之前版本，会在临时文件目录下创建以.ibd 为后缀的文件存放数据；5.7 版本开始，引入临时文件表空间存放数据，不再创建 ibd 文件。由于文件名前缀规则，同名临时表在存储上被视为不同表，因此可与普通表同名创建。

（二）内存中表的区分机制

1. 普通表的 table_def_key 由 “库名 + 表名” 构成，而临时表的 table_def_key 在 **“库名 + 表名” 基础上加入了 “server_id + thread_id”。**例如，session A 和 session B 创建的同名临时表 t1，其 table_def_key 不同，磁盘文件名也不同，所以可以并存。
2. 每个线程维护自己的临时表链表，session 内操作表时先遍历链表，优先操作临时表，session 结束时自动删除链表里的临时表。

六、临时表与主备复制

（一）binlog 记录规则

1. 当 binlog_format = row 时，临时表操作不记录到 binlog 中；当 binlog_format = statment/mixed 时，会记录临时表操作。例如，主库执行包含临时表操作的语句序列时，若 binlog 为 row 格式，与临时表相关语句不会记录，若为 statment/mixed 格式，则会记录。
2. drop table 命令记录 binlog 时会改写，如主库执行 drop table t_normal 时，binlog 中记录为 “DROP TABLE t_normal /* generated by server */”，原因是 drop table 命令可一次删除多个表，改写后可避免备库同步线程停止。

（二）主备库临时表处理

1. 主库不同线程创建同名临时表，其操作会传到备库执行。备库应用线程执行时，根据主库执行语句的线程 id 构造临时表的 table_def_key，从而区分不同的临时表，避免冲突。
2. 例如，主库 M 上 session A 和 session B 创建同名临时表 t1，传到备库 S 后，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”。

七、总结

1. 临时表适用于复杂计算逻辑场景，因其线程内可见且自动删除，无需考虑重名和收尾工作。
2. binlog_format 的选择会影响临时表操作的记录，这在实际应用中是一个需要考虑的因素。