文章目录
- MySQL45讲 第三十六讲 为什么临时表可以重名？——阅读总结
- 一、引言
- 二、临时表与内存表的区别
- （一）内存表
- （二）临时表
- 三、临时表的特性
- （一）可见性与生命周期
- （二）与普通表的关系
- 四、临时表的应用场景
- （一）分库分表系统的跨库查询
- 五、临时表重名的原理
- （一）文件存储方式
- （二）内存中表的区分机制
- 六、临时表与主备复制
- （一）binlog 记录规则
- （二）主备库临时表处理
- 七、总结
MySQL45讲 第三十六讲 为什么临时表可以重名？——阅读总结
一、引言
在 MySQL 数据库的使用中，临时表是一个非常有用的工具。在之前优化 join 查询的文章里，我们就用到了临时表。那么，临时表到底是什么？它有哪些特性使得它适用于特定场景？
二、临时表与内存表的区别
（一）内存表
内存表使用 Memory 引擎，建表语法为 create table … engine=memory。其数据保存在内存中，系统重启时数据会被清空，但表结构依然存在。从其他特征来看，它和普通表类似。
（二）临时表
临时表可以使用多种引擎类型，如 InnoDB 或 MyISAM 引擎的临时表在写数据时会写到磁盘上，当然也可以使用 Memory 引擎。
三、临时表的特性
（一）可见性与生命周期
-
一个临时表只能被创建它的 session 访问，对其他线程不可见。例如，在图 1 中，session A 创建的临时表 t，session B 无法看到。
-
session 结束时，临时表会自动删除。
（二）与普通表的关系
- 临时表可以与普通表同名。
- 当 session 内有同名的临时表和普通表时，show create 语句以及增删改查语句访问的是临时表。例如，在图 1 中，session A 创建了临时表 t 和普通表 t 后，show create table t 显示的是临时表 t 的结构。
- show tables 命令不显示临时表。
四、临时表的应用场景
（一）分库分表系统的跨库查询
- 分库分表架构
**在分库分表场景中，常将一个大表分散到不同数据库实例上，中间层 proxy 负责解析 SQL 语句并路由到相应分表查询。**例如，将大表 ht 按字段 f 拆分成 1024 个分表分布到 32 个数据库实例上。
- 跨库查询问题与解决方案
对于包含分区字段 f 的等值条件查询，可直接路由到对应分表查询。但当查询条件不包含分区字段时，如 select v from ht where k>= M order by t_modified desc limit 100;，需要在所有分区查找数据后统一排序。
- proxy 层排序：优势是处理速度快，在内存中计算，但开发工作量大，对 proxy 端压力大，易出现内存和 CPU 瓶颈。
- 使用临时表汇总数据后排序：在汇总库创建临时表 temp_ht，包含相关字段，从各分库获取数据插入临时表后再排序查询。实际中常将临时表放到某个分库上，其查询逻辑与图 3 类似。
五、临时表重名的原理
（一）文件存储方式
- 执行
create temporary table语句时，MySQL 会为 InnoDB 临时表创建 frm 文件保存表结构定义，文件存放在临时文件目录下，文件名后缀为.frm，前缀是 “#sql {进程 id}_{线程 id}_序列号”，可通过 select @@tmpdir 查看临时文件目录。 - 在 5.6 及之前版本，会在临时文件目录下创建以.ibd 为后缀的文件存放数据；5.7 版本开始，引入临时文件表空间存放数据，不再创建 ibd 文件。由于文件名前缀规则，同名临时表在存储上被视为不同表，因此可与普通表同名创建。
（二）内存中表的区分机制
- 普通表的
table_def_key由 “库名 + 表名” 构成，而临时表的 table_def_key 在 **“库名 + 表名” 基础上加入了 “server_id + thread_id”。**例如，session A 和 session B 创建的同名临时表 t1，其 table_def_key 不同，磁盘文件名也不同，所以可以并存。 - 每个线程维护自己的临时表链表，session 内操作表时先遍历链表，优先操作临时表，session 结束时自动删除链表里的临时表。
六、临时表与主备复制
（一）binlog 记录规则
- 当
binlog_format = row时，临时表操作不记录到 binlog 中；当binlog_format = statment/mixed时，会记录临时表操作。例如，主库执行包含临时表操作的语句序列时，若 binlog 为 row 格式，与临时表相关语句不会记录，若为 statment/mixed 格式，则会记录。 - drop table 命令记录 binlog 时会改写，如主库执行 drop table t_normal 时，binlog 中记录为 “DROP TABLE
t_normal/* generated by server */”，原因是 drop table 命令可一次删除多个表，改写后可避免备库同步线程停止。
（二）主备库临时表处理
- 主库不同线程创建同名临时表，其操作会传到备库执行。备库应用线程执行时，根据主库执行语句的线程 id 构造临时表的 table_def_key，从而区分不同的临时表，避免冲突。
- 例如，主库 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”。
七、总结
- 临时表适用于复杂计算逻辑场景，因其线程内可见且自动删除，无需考虑重名和收尾工作。
- binlog_format 的选择会影响临时表操作的记录，这在实际应用中是一个需要考虑的因素。
