★ FEATURED ARTICLE 你在两个连接里分别执行CREATE TEMPORARY TABLE t1两边都能建成功数据互不干扰更离谱的是同一个库里已经有一张普通表t1你照样能建一张临时表t1。最初看到 MySQL 里这个“重名”现象我是很疑惑的普通表在同一个库里重名早就报错了凭什么临时表可以后来读 MySQL45 讲第 36 讲才彻底想明白临时表从设计上就不是“表名 库名”这种全局命名逻辑而是一套完全独立的会话级命名空间。这篇文章就把这个知识点从现象到原理完整拆一遍顺便把临时表使用中容易踩的坑也一起说清。适合刚接触临时表、经常写存储过程或复杂 SQL、以及被“临时表为什么这么奇怪”困扰过的开发者。1. 先弄清楚临时表和普通表到底差在哪很多人对临时表的理解就停留在“用完自动删”这一层其实临时表最核心的特征不是删除机制而是“会话私有”。只有真正理解了这层隔离逻辑你才会明白为什么重名是被允许的、为什么有些 SQL 表现怪异。1.1 临时表的三个基础认知临时表用CREATE TEMPORARY TABLE创建它的生命周期、可见性、清理方式都和普通表有本质区别。第一临时表只对创建它的会话可见。会话 A 建了一张临时表会话 B 无论怎么SELECT都看不到也不会收到“表存在”的提示。对 B 来说这张表根本不存在。第二临时表在会话正常结束时自动清理。不需要你手动DROP连接一关闭MySQL 会把属于这个会话的所有临时表一并清理掉不占用持久化空间。第三临时表可以重复创建、重复使用。因为它不进入共享的元数据空间所以它很适合做复杂 SQL 的中间结果缓存、存储过程里的分步计算、以及“不想动线上表结构但又要跑临时数据”的场景。如果你写过存储过程应该深有体会一段逻辑要拆成几步每一步都有中间结果总不能每跑一次就往线上库塞一张普通表。用临时表代码干净又不影响库里原有对象。1.2 临时表 vs 普通表一张表看清关键差异我整理了一张对比表方便你对照着看。这张表会帮助理解后面所有“怪异现象”。对比维度普通表临时表命名空间库名 表名全局唯一会话 ID 库名 表名会话内唯一生命周期持久存在直到 DROP会话结束自动清理跨会话可见性所有有权限的会话可见仅创建会话可见存储位置正式表空间/数据文件临时表空间或临时目录元数据记录共享数据字典会话私有上下文对已有同名表的影响不可重名可与普通表同名且临时表优先复制行为正常同步到从库需要 binlog 特殊处理这张表里最值得关注的是“命名空间”这一行。普通表的唯一性由库名.表名决定临时表的唯一性多了“会话 ID”这个维度所以不同会话之间的临时表名可以相同同一会话里临时表也可以和普通表同名。2. 从现象出发两个会话同名临时表互不干扰的实验理论知识说多了容易飘直接用实验验证一遍。下面我模拟三个连接观察同一个临时表名在不同会话里的表现。2.1 实验设计连接一CREATE TEMPORARY TABLE t_user_temp ( id INT PRIMARY KEY, name VARCHAR(32) ); INSERT INTO t_user_temp VALUES (1, ctx_a); SELECT * FROM t_user_temp;连接二CREATE TEMPORARY TABLE t_user_temp ( id INT PRIMARY KEY, name VARCHAR(64) ); SELECT * FROM t_user_temp;连接三SELECT * FROM t_user_temp;在这个实验里连接一和连接二都创建了名为t_user_temp的临时表而且表结构都不同。连接三没有创建过这张临时表直接查询。实验结果很直观连接一能够查到ctx_a这条数据连接二创建成功后查询结果是空的因为它的临时表里还没插入数据连接三直接报“表不存在”之类的错误。关键是连接二并没有因为连接一已经有t_user_temp而失败连接一也完全没有感知到连接二创建了同名临时表。这说明“重名”不是巧合而是被明确允许的。2.2 实验补充普通表和临时表同名时到底查的是谁再做一个小实验在同一个会话里先建一张普通表再建一张同名的临时表CREATE TABLE t_user_temp (id INT, name VARCHAR(32)); INSERT INTO t_user_temp VALUES (100, normal_table); CREATE TEMPORARY TABLE t_user_temp (id INT, name VARCHAR(64)); INSERT INTO t_user_temp VALUES (200, temp_table); SELECT * FROM t_user_temp;这时SELECT结果返回的是200, temp_table。也就是说同一会话里如果同时存在普通表和临时表SQL 优先访问的是临时表。普通表并没有被删除它还在只是被临时表“遮蔽”了。等会话结束临时表被清理普通表t_user_temp又重新成为这个库里唯一可见的t_user_temp表。这两个实验合起来基本可以得出一个结论MySQL 在处理临时表时用的是一套和普通表完全不同的查找逻辑。接下来看底层机制。3. 为什么可以重名会话级命名空间的内部机制临时表能重名不是 MySQL 偷懒不去检查表名恰恰相反MySQL 做了非常严格的隔离。要理解这一点先要明白普通表为什么不能重名。3.1 普通表的命名规则只有一个维度普通表在同一个库中的唯一性由库名.表名这个二元组决定。MySQL 维护了一个全局共享的数据字典所有库、所有表都登记在里面。当你执行CREATE TABLE t1时服务端会在共享字典里查一下“这个库名下有没有同名表”有就直接报错。也就是说普通表使用的是一套“中心化”的命名空间。任何一个会话创建了t1其他所有会话都能看到它、竞争它、甚至锁定它。这套机制保证了数据的一致性也导致了普通表不能随便重名。3.2 临时表在服务端被“二次改名”隔离出单独空间临时表则走了一条完全不同的路。MySQL 在处理CREATE TEMPORARY TABLE t1时并不是直接在当前库下新建一个叫t1的表而是在会话内部做了重新映射。你可以这样理解你在 SQL 里写的t1是逻辑表名真正落到存储引擎层的物理表名已经被 MySQL 改成了一个带有会话标识的内部名称。这个内部名称通常类似#sql_会话标识_随机串不同会话生成的内部名完全不同底层对象天然隔离。同时MySQL 会维护一棵“当前会话可见表”的映射树把你说的t1映射到刚才那个内部对象上。这个映射关系只存在于当前会话的上下文中其他会话根本拿不到。所以两个会话创建同名临时表时逻辑上是“大家都叫 t1”物理上却是两个互不相干的对象。这就是“重名”得以成立的底层原因临时表的完整唯一键实际上是(会话 ID, 库名, 表名)而不是普通的(库名, 表名)。从实现版本上看MySQL 5.7 及之前多用文件或内存缓存来保存这些临时表元数据MySQL 8.0 则把临时表字典也纳入统一数据字典管理。但不管底层怎么改会话隔离这个核心设计一直没变。3.3 SQL 解析时临时表永远排在普通表前面既然临时表和普通表可以同名MySQL 在解析SELECT * FROM t1时必须决定先找谁。答案是先找当前会话的临时表找不到再找普通表。这个优先级顺序非常重要。它保证了你可以在不修改线上表结构的前提下在当前会话里用一个同名临时表“覆盖”业务表专门用来调试、测试、灌数据。调试完会话结束一切恢复原样线上表没有任何改动痕迹。同样的逻辑也适用于DROP TABLE。如果你当前会话同时存在普通表t1和临时表t1执行删除操作时要格外小心。我的建议很明确凡是删临时表一律显式写DROP TEMPORARY TABLE t1不要用裸的DROP TABLE t1以免误删到不该删的普通表。4. 这个设计背后的工程考量不是缺陷是刻意的取舍理解了内部机制接下来要回答“为什么 MySQL 要设计成可以重名”。这不是一个可有可无的小功能它解决了真实场景里的几个大问题。4.1 存储过程和触发器并发执行时的隔离需求假设有一段存储过程每次被调用时都创建一张临时表tmp_result在里面做几步计算最后返回结果。如果临时表的命名空间不是会话私有的而是全局共享的那么两个连接同时调用这段存储过程时就会互相踩踏连接 A 刚把数据写进tmp_result连接 B 把同一张表清了重填A 后面的计算结果就全错了。会话级命名空间让“并发执行同一段代码”变成一件安全的事。每个连接都拥有自己对tmp_result的独占版本互不干扰。这对所有依赖存储过程、批量任务脚本的系统来说是刚需能力。4.2 不破坏线上表结构的前提下做中间计算很多数据平台团队会定期跑数据修正任务把线上表数据筛出来做清洗再写回去。这类任务如果用普通表做中间存储你要么新建一堆业务表要么和 DBA 申请临时表空间做完还要清理非常繁琐。临时表允许你直接在当前会话里创建一个和业务表同名的临时表利用“临时表优先”的特性把所有复杂计算都放在临时表层面完成业务表全程不动。会话一结束临时表自动消失线上 schema 干干净净。4.3 复制场景下的特殊处理临时表会话私有的特性在复制场景里也带来了一些特殊行为。在基于语句的 binlog 格式下主库执行的临时表相关 SQL 会被写进 binlog备库需要把这些临时表操作也重放一遍。但因为主库的临时表是跟着会话走的备库并没有一个“相同的会话”所以 MySQL 需要额外记录信息才能保证备库上的临时表操作能够正确回放。这也带来一个经典坑如果主库的连接异常断开主库会自动清理临时表但 binlog 里不一定有对应的删除语句备库有可能残留一张临时表。不同版本对这类问题的处理细节不太一样但在做主从复制环境时尽量不要过度依赖临时表来完成复杂跨会话逻辑这是一个很实用的经验。5. 临时表重名之外实际使用中的五个关键建议原理讲透了再来说说落地。临时表用起来虽然方便但细节里全是坑下面几个点是我实际项目中踩过或反复提醒别人的地方。5.1 用完尽量手动 DROP别只依赖会话结束虽然会话结束会自动清理临时表但很多应用用的是连接池一条连接创建后会被反复复用会话可能长时间不断开。如果你在代码里创建了大量临时表却不显式删除临时表会一直挂在当前连接下占用临时表空间资源。尤其在一些批量任务里循环每次创建临时表又不 DROP跑了几个小时后临时表空间会涨得非常夸张。正确做法是在存储过程或脚本逻辑里用完临时表立刻DROP TEMPORARY TABLE并且把清理逻辑放到异常处理里保证无论如何都会执行。5.2 不要把“用户临时表”和“内部临时表”混为一谈这算是很多人最容易混淆的一点。CREATE TEMPORARY TABLE创建的临时表是显式的、用户主动创建的。但 MySQL 在执行GROUP BY、ORDER BY、UNION、子查询等操作时优化器可能会在内部自动生成一张临时表这被称为“内部临时表”。这两种临时表生命周期不同用户临时表由会话管理内部临时表由 SQL 执行过程管理语句一结束就被清理。重名隔离机制在内部临时表里同样存在但触发条件和调优手段完全不同。遇到“临时表磁盘占用暴涨”的问题时先搞清楚是哪种临时表在涨不然你改了参数也白改。5.3 事务回滚不会删除临时表临时表虽然可以配合事务使用但它不受事务回滚影响。你在一个事务里创建了临时表插入了数据随后事务回滚数据会回滚但这张临时表本身还在结构还在后续依然可以使用。这个特性有利有弊。好处是临时表的 DDL 不会破坏事务流程坏处是如果你希望“事务回滚就清理临时表”那无法实现必须显式 DROP 或等会话结束。5.4 注意内存临时表和磁盘临时表的转换如果你创建的临时表用的是内存引擎就要受tmp_table_size和max_heap_table_size两个参数影响。临时表数据量超过限制时MySQL 会把内存临时表转换为磁盘临时表转换过程本身就有开销而且磁盘 IO 会拖慢整个 SQL。实际调优时我会先看SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables和Created_tmp_tables两个状态值如果磁盘临时表占比很高再考虑调整参数或改写 SQL而不是盲目调大内存限制。5.5 排查问题时临时表“看不见”不代表不需要关注因为临时表只存在于当前会话你在另一个连接里看数据库发现表列表里根本没有这张表很容易忽略它的存在。但它的存在会影响当前连接的执行计划、占用内存、甚至导致临时表空间暴涨。排查线上问题时如果某个连接跑了很久没结束而且状态是Copying to tmp table或类似临时表操作就要考虑是不是有用户临时表或内部临时表在工作。信息可以从performance_schema和状态变量里拿而不是单纯看表列表。6. 常见问题与排查速查表把前面拆过的知识点整理成一张速查表方便你遇到具体问题时快速对照。问题结论说明同库已存在普通表 t1还能建临时表 t1 吗可以两者处于不同命名空间临时表优先会话 A 的临时表会话 B 能查吗不能临时表只对创建会话可见同名临时表在不同会话里会互相覆盖吗不会物理对象由会话 ID 隔离会话断开后临时表会自动删除吗会正常断开时临时表会被自动清理删除临时表用什么语句DROP TEMPORARY TABLE避免裸用DROP TABLE误删普通表临时表会占用正式表空间吗一般不会用户临时表存储在临时表空间或临时目录事务回滚会删除临时表吗不会临时表结构保留数据随事务回滚主从复制中临时表会同步吗依赖 binlog 格式语句级复制需要特殊处理注意备库残留风险内部临时表是什么时候产生的排序、去重、UNION、子查询等由优化器自动生成语句结束即清理这张表里最想让你记住的是第一行和第三行临时表能重名不是因为 MySQL 放松了表名校验而是因为它的唯一性本来就多了一个“会话 ID”维度。理解了这点再看临时表的其他行为基本都能顺下来。就我个人的使用体会来说临时表的会话级命名空间设计在复杂 SQL 拆分、存储过程并发执行和调试场景里简直是救命级功能。最后再分享一个小技巧调试一段很长的 SQL 时先把中间结果CREATE TEMPORARY TABLE tmp_step1 AS SELECT ...然后单独EXPLAIN或SELECT去验证每一步比把整段 SQL 揉在一起看执行计划高效得多。利用好“临时表优先”这个特性你能在不碰生产表的前提下把一条慢查询拆得明明白白。
阅读完成 · 觉得有帮助？
