第36讲的标题是“为什么临时表可以重名”。我第一次读到这个标题时心里其实有点不服气临时表不也是表吗表名不冲突是数据库的基本规则凭什么临时表能例外结果在自己的环境里一测发现还真是这样——两个会话各自CREATE TEMPORARY TABLE tMySQL连个警告都不给两边该插插、该查查数据各是各的。这个现象如果用普通表那套经验去理解完全解释不通。普通表重名直接抛ERROR 1050临时表却能同名共存。搞清楚背后的原因等于把 MySQL 表对象管理机制撕开了一个口子。这篇笔记就把我理解到的内容重新梳理一遍聊聊临时表为什么“可以重名”以及它在实际使用中有哪些值得注意的细节。1. 临时表重名不是巧合而是设计出来的会话隔离1.1 先做一个最直观的复现我在本地 MySQL 8.0 实例上开了两个连接姑且叫连接 A 和连接 B。连接 A 执行CREATE TEMPORARY TABLE tmp_user ( id INT PRIMARY KEY, name VARCHAR(32) ) ENGINEInnoDB; INSERT INTO tmp_user VALUES (1, A); SELECT * FROM tmp_user;结果正常返回一行1 | A。连接 B 也执行完全相同的建表语句CREATE TEMPORARY TABLE tmp_user ( id INT PRIMARY KEY, name VARCHAR(32) ) ENGINEInnoDB; INSERT INTO tmp_user VALUES (100, B); SELECT * FROM tmp_user;同样正常返回100 | B。两个连接里都有一张名为tmp_user的表互相不报错查询结果也不互相干扰。更特别的是如果你在任意一个连接里执行SHOW TABLES根本看不到这张临时表。换句话说临时表的存在方式和普通表在元数据层面就不是一回事。1.2 临时表和普通表的本质差异临时表和普通表最核心的区别可以浓缩成三个词会话私有、自动清理、隐藏于元数据。会话私有临时表只对创建它的会话可见其他会话无论用SELECT还是DESC都访问不到。自动清理连接断开时临时表会被自动删除不需要你手动DROP。隐藏于元数据SHOW TABLES不显示临时表information_schema.TABLES里也查不到它的完整记录。这三个特点放在一起其实已经揭示了重名的答案MySQL 压根没把临时表当作“全局共享的一张表”来管理而是把它看作当前会话的一块私有工作区。既然每个会话的工作区互相隔离那不同会话里出现同名临时表就像两个程序员各自在自己电脑上建了一个都叫tmp的文件夹谁也不影响谁。不过如果你到此为止只记住了“临时表是会话私有的”还是不够的。真正值得追问的是MySQL 底层是怎么做到这种隔离的是名字上做了区分还是存储上做了区分2. 临时表为什么敢和正式表重名命名空间与物理存储的抽屉模型2.1 每个会话的临时表藏在独立的“命名空间抽屉”里MySQL 在打开一张表时内存中要维护表对象也要维护表名到表对象的映射关系。普通表的映射是全局的库名.表名这个 key 在整个实例里只能对应一个表对象。所以两个会话同时CREATE TABLE t第二个会话会撞上已经存在的 key报ERROR 1050。临时表不一样。MySQL 在构造临时表的标识时并不是只拿库名.表名作为唯一 key而是会把会话信息一起加进去。相当于每个会话都有一个独立的“抽屉”你的临时表放在你的抽屉里我的临时表放在我的抽屉里。两个抽屉里的表都叫tmp_user但它们的完整标识一个是会话A的抽屉 tmp_user另一个是会话B的抽屉 tmp_user根本不是一个东西。这也是为什么同一个会话里你不能创建两张同名临时表——因为同一个抽屉内部表名依然必须唯一。你可以试试CREATE TEMPORARY TABLE t (id INT); CREATE TEMPORARY TABLE t (id INT); -- 这里会报错第二次执行会直接提示表已存在。这说明“重名”不是无条件的而是“跨会话可以重名同会话依然冲突”。理解到这个粒度才算真正理解了临时表的命名空间模型。2.2 物理存储层面临时表的落盘方式也做了隔离除了内存里的命名空间隔离落盘时临时表也不是随便乱写的。早期版本里MyISAM 临时表在磁盘上生成的文件名会带上线程 ID 之类的后缀比如#sql1234_5_0.MYD、#sql1234_5_0.MYI。不同的会话线程 ID 不同即使逻辑表名一样物理文件名也不一样自然互不覆盖。到了 InnoDB 时代临时表的物理存储更讲究。MySQL 8.0 专门为临时表准备了独立的临时表空间会话临时表空间会按需创建和回收。这里面的实现细节不同版本有差异但思想是一致的临时表的物理对象必须是会话私有的不能和其他会话共享同一个文件或表空间。所以从内存映射到物理文件临时表从头到尾都在做隔离。重名现象不是“数据库碰巧允许重名”而是“数据库一开始就没打算让不同会话的临时表共用同一个名字空间”。2.3 临时表和永久表同名时谁说了算重名问题还有另一个容易踩坑的变体同一个会话里已经有一张永久表t能不能再建一张临时表t答案是能。而且一旦建了临时表t当前会话里所有访问t的 SQL走的都是临时表永久表相当于被“遮住”了。比如CREATE TABLE t (id INT); -- 普通表 CREATE TEMPORARY TABLE t (id INT); -- 临时表 INSERT INTO t VALUES (1); -- 插入的是临时表 SELECT * FROM t; -- 读到的也是临时表这一点非常容易让人翻车。如果你在某个连接里建了和业务表同名的临时表接下来这个连接的所有 SQL 都会跑偏而且可能直到业务诡异报错你才意识到自己用了临时表“遮蔽”了正式表。更隐蔽的是DROP TABLE的行为。如果同名临时表和正式表同时存在DROP TABLE t默认先删掉临时表正式表还在。如果你以为删的是正式表后续 SQL 又继续操作这个连接仍然会读到那张还活着的正式表逻辑上可能产生二次迷惑。所以要养成一个习惯临时表命名尽量带明确前缀比如tmp_并且不要在同一个会话里和正式表重名。MySQL 允许你用同一个名字不代表业务层不会被坑。3. 临时表内部是怎么存数据的内存与磁盘的切换路径3.1 用户临时表和内部临时表两种“临时表”要分清MySQL 里的“临时表”其实是两个概念。一种是我们刚刚在聊的用户临时表由开发者手动CREATE TEMPORARY TABLE创建另一种是内部临时表是优化器在执行某些 SQL 时自动生成的中间结果集。两者都会用到临时表机制但触发条件和控制参数不同。内部临时表的经典触发场景包括UNION需要去重时优化器可能生成临时表。ORDER BY的结果集过大无法在 sort buffer 里完成排序。GROUP BY在某些聚合方式下需要临时表来分组。子查询展开后可能把中间结果物化到临时表。判断 SQL 是否使用了内部临时表最直接的手段是看执行计划里的Extra列。如果出现Using temporary就说明这条 SQL 在某个环节使用了临时表。注意Using temporary不代表一定很差但往往意味着一次额外的读改写过程。当数据量大时这个临时表可能从内存落到磁盘性能就会急剧下降。3.2 内存临时表什么时候会变成磁盘临时表我经常被问到一个问题临时表是不是都在内存里答案是否定的。MySQL 的临时表有两条存储路径。当临时表很小优先放内存。内存放不下了再转磁盘。控制这条路径的关键参数有几组tmp_table_size用户临时表的内存上限。max_heap_table_sizeMEMORY 引擎表的内存上限。internal_tmp_mem_storage_engineMySQL 8.0 中控制内部临时表内存引擎默认是TempTable。temptable_max_ramMySQL 8.0 中TempTable引擎能占用的最大内存。简单说临时表占用的内存达到阈值后执行引擎会把数据搬到磁盘上继续处理内存里的那份释放掉。这时你会看到状态变量里有变化SHOW GLOBAL STATUS LIKE %tmp%;重点关注两个值Created_tmp_tables累计创建的临时表数。Created_tmp_disk_tables累计创建的磁盘临时表数。如果Created_tmp_disk_tables增长很快说明有大量临时表落盘通常意味着 SQL 需要内存不够用、排序或分组数据量太大或者临时表字段里有BLOB/TEXT这类大字段不得不直接走磁盘。这里有个实操经验TEXT、BLOB字段很容易把临时表挤出内存因为引擎没法像处理定长字段一样高效管理它们。即使表里只有一行大文本一旦需要临时表承载就可能直接转磁盘。设计表结构时如果某个字段是长文本但实际占用很小可以考虑拆表或用VARCHAR控制长度来避免内部临时表落盘。3.3 临时表的数据清理时机用户临时表的生命周期很清楚会话结束即删除。但“会话结束”里有不少细节。如果你用连接池连接没有真正断开临时表也不会自动消失。特别是长连接场景下连接被反复复用临时表会一直留在会话里占用内存或者磁盘空间。很多“连接池越用越慢”的问题其实不是连接池本身的问题而是某个业务逻辑创建了临时表却忘了删连接又一直不回收。内部临时表则不同它由优化器管理语句执行完就会释放。所以内部临时表一般不会长期占用资源问题主要集中在执行过程中的瞬时压力上。一个项目里如果频繁出现大查询导致磁盘 I/O 抖动排查时要去看看是不是内部临时表落盘太频繁。有时候一个GROUP BY造成的临时表文件能瞬间把临时目录写满。临时目录一旦满了后续所有需要临时表的 SQL 都会报磁盘空间不足影响面非常大。4. 什么场景适合建临时表什么场景踩坑4.1 典型用法分批处理、中间结果、数据对比用户临时表在业务开发里最常见的价值是保存中间结果把一个复杂问题拆成多个简单步骤。比如你要从一张千万级订单表里分批取出满足复杂条件的订单 ID再回原表关联明细。不用临时表的写法可能是SELECT ... FROM orders WHERE order_id IN ( SELECT order_id FROM orders WHERE ... 一大堆条件 ) AND ...这种写法容易出现两个问题子查询被多次执行或者优化器生成的执行计划不是你以为的样子。用临时表的写法会更可控CREATE TEMPORARY TABLE tmp_order_ids AS SELECT order_id FROM orders WHERE ... 一大堆条件; ALTER TABLE tmp_order_ids ADD PRIMARY KEY (order_id); SELECT ... FROM orders JOIN tmp_order_ids USING (order_id) WHERE ...先把要处理的订单 ID 固化下来再回表关联逻辑清晰执行计划也稳定。数据对比也是好场景。比如要找出两张表的数据差异可以分批次把两条数据源分别导入临时表再做LEFT JOIN或者NOT IN比对避免每条记录都远程查一次。4.2 不适合场景跨会话共享数据、热点更新、长事务临时表最不适合做的事就是跨会话共享数据。原因前面已经说了临时表的可见性只限于创建会话。有些人图方便在一个接口里建了临时表想让另一个接口去读结果另一个接口永远查不到数据。遇到这种情况诉求应该改用普通表或者走缓存、消息队列等组件而不是临时表。临时表也不适合做高并发热点更新的存储。临时表的资源是会话私有的但底层的临时表空间仍然是共享资源。如果你的服务开了 200 个连接每个连接都创建一张大临时表并频繁写入底层磁盘 I/O 很快会被打爆。临时表用于离线计算、跑批任务比用于在线高并发更适合。长事务里使用临时表也要谨慎。虽然临时表的数据不会永久保存但长事务本身会占用 undo log、行锁等资源。临时表里面的写操作也会产生事务行为事务一直不提交相关资源就释放不了。我在一些跑批场景里见过开发同学把一个多小时的大事务拆开之后临时表导致的锁等待瞬间少了很多。4.3 使用临时表的几条实操建议根据我自己的使用经验有几点比较重要临时表命名统一加tmp_前缀别和正式表重名。用完立刻DROP TEMPORARY TABLE不要依赖连接关闭时自动清理。创建完临时表后如果需要按某个字段关联查询顺手把索引加上。临时表默认不带完整索引结构不加索引的大关联照样慢。不要在高频、短连接、热点的路径上创建临时表。建表有开销如果每条请求都建一张性能损耗会被放大。5. 复现、验证与常见坑位速查5.1 三步复现临时表重名如果你想在自己环境里快速验证“临时表可以重名”不需要复杂工具三步就够了。第一步准备两个终端分别连到同一个 MySQL 实例。第二步在连接 A 执行CREATE TEMPORARY TABLE t_demo (id INT PRIMARY KEY, val VARCHAR(20)); INSERT INTO t_demo VALUES (1, from_A); SELECT * FROM t_demo;第三步在连接 B 执行CREATE TEMPORARY TABLE t_demo (id INT PRIMARY KEY, val VARCHAR(20)); INSERT INTO t_demo VALUES (2, from_B); SELECT * FROM t_demo;两边都能看到自己的数据。然后在连接 A 里再看一次仍然是from_A。这就能证明临时表的重名是跨会话隔离的。接着可以做一个小实验在同一个连接里再执行一次同名建表会报错。这个实验能帮你更容易理解“命名空间隔离”的边界到底划在哪。5.2 排查临时表占用内存或磁盘过大的思路遇到临时表相关的性能问题我的排查顺序一般是这样的先看状态值。SHOW GLOBAL STATUS LIKE Created_tmp%;如果Created_tmp_disk_tables / Created_tmp_tables的比例很高比如超过 20%就要怀疑有大量临时表在落盘。再看执行计划。找出有Using temporary的 SQL和业务方确认是否真的有大量中间结果集。临时表落盘通常不是问题的根源根源在于需要临时表处理的中间数据量太大。然后看系统参数。确认tmp_table_size、temptable_max_ram、tmpdir的设置。临时目录所在文件系统的剩余空间也要纳入监控。很多生产事故都是临时目录先被写满然后连新建临时表都失败最终表现为大量 SQL 报错。如果临时表落盘不可避免可以考虑优化 SQL让排序和分组阶段尽量走索引或者把大查询拆小分批处理减少单条 SQL 的临时表数据量。5.3 常见误区速查表误区实际情况建议临时表是内存表不会落盘内存放不下会转磁盘大字段更容易落盘关注Created_tmp_disk_tables临时表其他会话也能访问临时表会话私有其他会话访问不到跨会话共享请用普通表临时表不需要手动删除长连接下会长期占用内存和磁盘用完及时DROP TEMPORARY TABLE临时表可以和正式表同名随便用同名后会遮蔽正式表DROP也可能只删临时表尽量加tmp_前缀避免遮蔽同一个会话也能建同名临时表同一会话内表名仍然唯一不要在同一个连接里重复创建同名临时表Using temporary一定代表慢查询只代表使用了临时表机制是否慢取决于数据量和落盘结合状态变量和实际耗时判断5.4 一个小技巧临时表也能帮你验证优化器行为最后分享一个我在读这一讲时觉得挺有用的技巧。当你不确定自己的 SQL 是否会产生内部临时表时可以手动创建一个同名的临时表然后对比执行计划的变化来理解优化器的决策路径。比如你怀疑某个GROUP BY走了临时表可以先建一张带合适索引的临时表把数据导入进去然后手动执行同样的聚合逻辑看看性能差距。这种方法不需要改业务代码就能把“优化器内部生成的临时表”和“你手动控制的临时表”两种方案放在同一个公平环境里对比。很多时候你会发现手动控制临时表反而更可控因为它允许你精确加索引、精确控制数据量而不是让优化器在最终执行瞬间做临时决策。我在实际项目中遇到过一次慢查询一条分组统计 SQL数据量不大却要跑好几秒。EXPLAIN显示Using temporary但临时表里的数据其实只有几万行。后来手动建临时表、加上合适的联合索引再替代原 SQL 的重聚合过程响应时间降到了几十毫秒。这个优化思路本质上就是借助了临时表“重名但隔离”以及“可手动控制结构”这两个特性。理解底层机制之后你会发现它不只是面试题在真实排查中非常能落地。这一讲读下来记得最深的一句话临时表的命名空间是会话级的它和普通表完全不在一个维度上。很多日常工作里的困惑比如“为什么重名了不报错”“为什么连接一断数据就没了”都是因为没把这两类表分开来看。希望能帮到同样被这个问题卡住的人。