MySQL与SQL Server临时表实战：从Using temporary到性能优化

1. 从 EXPLAIN 里的 Using temporary 聊起1.1 Using temporary 到底在提示什么如果你用 MySQL 做开发或者维护大概率在 EXPLAIN 输出里见过这一行Extra: Using temporary; Using filesort我第一次见到这个标记时第一反应是“临时表我没建过临时表啊哪来的”后来才明白这里说的是内部临时表跟你在业务 SQL 里手动CREATE TEMPORARY TABLE是两码事。优化器在执行过程中发现靠现有索引和内存结构没法直接完成排序、分组或去重就会自己搭一张隐藏的临时表来过渡而这个动作会被记录在 Extra 列里。简单说Using temporary是优化器在告诉你我在后台偷偷用了一张临时表帮你干活。至于这张临时表是在内存里还是落到了磁盘上得看数据量和参数配置。真正需要警惕的是“落盘”的那部分——一旦临时表写进磁盘IO 开销会瞬间拉高查询响应时间可能从几十毫秒涨到几秒甚至更久。1.2 什么情况下最容易触发 Using temporary根据我的实际排查经验下面几类查询最容易撞上这个标记GROUP BY 的字段和 ORDER BY 字段不一致且都没有可用索引。使用 DISTINCT 去重数据又来自多表 JOIN 之后的中间结果。UNION 或者 UNION ALL 需要合并多个结果集时MySQL 会先建立临时表去重或排序。子查询被优化器物化Materialization派生表需要落地。对未索引列执行ORDER BY同时还有聚合函数参与。查询里同时出现GROUP BY和LIMIT且分组字段无法走索引。举一个非常典型的触发场景SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE created_at 2024-01-01 GROUP BY user_id ORDER BY order_cnt DESC;假设orders表的 user_id 有索引但 GROUP BY 和 ORDER BY 使用的字段不同优化器在完成分组后还需要对order_cnt排序而order_cnt是聚合结果无法直接走索引于是只能先构造临时表再对临时表做 filesort。这种情况下 Extra 列几乎必然出现Using temporary; Using filesort。你可能会说数据量不大出现就出现呗不影响。确实几百行的小表建临时表的花费可以忽略不计。但如果是几百万行的订单表这个“临时表”就有可能是压垮查询性能的那根稻草。1.3 见到它别急着动手先判断是否真的影响性能看到Using temporary就立刻去改 SQL是很多新手容易犯的毛病。优化器的每个选择都是基于成本估算做出的Using temporary本身只是一个中性描述不等于“这条 SQL 必须改”。我的处理原则是三步先看扫描行数再看临时表是否落盘最后用实际执行时间佐证。EXPLAIN 里rows字段能大致反映参与计算的数据量如果只有几千行哪怕临时表走磁盘也快得很如果rows是百万级同时系统变量Created_tmp_disk_tables在增长那才需要认真对待。真正需要关心的不是标记本身而是这个标记背后有没有引发大量磁盘 IO。后面第 5 部分我会用一个完整案例演示什么样的场景用临时表反而能把复杂查询改得更快。2. MySQL 临时表内部的与外部的两套机制2.1 内部临时表优化器自己搭的“草台班子”内部临时表是 MySQL 在执行 SQL 过程中自动创建的用户感知不到也不需要手动管理。它的生命周期由优化器控制查询执行完就销毁。早期的 MySQL 版本里内部临时表默认使用 MyISAM 引擎5.7 之后默认改成了 InnoDB由参数internal_tmp_disk_storage_engine控制。内部临时表有两条路可走优先在内存中创建也就是使用 MEMORY 引擎当数据量超过阈值时自动转换为磁盘临时表。这个阈值不是单一参数决定的而是取tmp_table_size和max_heap_table_size两者中的较小值。举个例子我手头一台 8G 内存的测试库tmp_table_size设置的是 64Mmax_heap_table_size设置的是 32M。那么内存临时表的最大体积就是 32M一旦估算数据超过 32M优化器直接把临时表搬到磁盘此时Using temporary的成本就会急剧上升。监控内部临时表是否频繁落盘主要看两个状态变量SHOW GLOBAL STATUS LIKE Created_tmp_tables; SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables;如果Created_tmp_disk_tables和Created_tmp_tables的比值持续偏高说明有大量查询在生成磁盘临时表这是性能优化的重点信号。我看过不少开发环境tmp_table_size还是默认的 16M结果报表类查询频繁落盘调高到 64M 之后很多“慢查询”直接消失了。2.2 显式临时表由你掌控的中间层和内部临时表相对应开发者还可以手动创建显式临时表语法是CREATE TEMPORARY TABLE。这种表有几个非常实用的特性只对当前会话可见其他连接看不到也不会发生名字冲突。会话结束时自动删除不需要手动清理。可以和普通表同名且在当前会话中临时表会“遮蔽”普通表。支持创建索引、删除、修改结构使用方式与普通表基本一致。显式临时表最大的价值在于拆分复杂逻辑。比如一个需要跑十秒的报表查询由三段子查询嵌套实现中间结果反复被引用。与其让优化器反复物化派生表不如手动把中间结果落到临时表里后续步骤直接查临时表。这样既能控制查询计划的走向也方便排查每一段数据的准确性。我在实际项目中有一个习惯只要发现同一条 SQL 里出现多个子查询嵌套每一层还都要聚合、排序我就会先把第一层结果写入临时表再基于临时表做第二层。虽然步骤变多了但每一段都能单独验证出了问题也好定位。2.3 临时表落盘与内存参数怎么调关于临时表调参我见过不少一刀切的方案什么“把 tmp_table_size 调到 1G”之类。这种思路并不推荐原因是内存是公共资源临时表吃掉了内存留给 buffer pool 的空间就会变少换来的可能是整体性能下降。比较稳妥的做法是分两阶段调优。第一阶段先确认当前库的磁盘临时表比例SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; SHOW GLOBAL STATUS LIKE Created_tmp_tables;如果每 100 个临时表里超过 30 个落在磁盘先看是否有高频 SQL 可以优化通过加索引、调整 GROUP BY 顺序来消除临时表。第二再来适当调整上限一般从当前值翻倍开始测试观察一段时间确认内存和 CPU 都没压力再逐步增加。还有一个容易被忽略的参数max_heap_table_size。它不光限制临时表大小也限制 MEMORY 引擎表而tmp_table_size只在内存临时表场景生效。记住那句老话决定内存临时表上限的是这两个参数里更小的那个。3. 实操在 MySQL 中创建临时表的完整姿势3.1 基本创建语法与结构设计先看最基础的建表语句CREATE TEMPORARY TABLE tmp_user_order_stats ( user_id INT NOT NULL, order_cnt INT NOT NULL DEFAULT 0, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0, PRIMARY KEY (user_id) ) ENGINE InnoDB;创建完成后这张表在当前会话里可以直接像普通表一样 INSERT、SELECT、UPDATE。需要注意如果使用连接池长连接会话不会频繁关闭临时表可能会一直存在到连接被回收。所以最好在逻辑结束的地方显式删除DROP TEMPORARY TABLE tmp_user_order_stats;有人会问临时表用什么存储引擎比较合适如果数据量小、且查询以等值查询为主可以用 MEMORY 引擎读取速度快如果数据量大、并发写多或者需要事务支持就选 InnoDB。MEMORY 引擎表不支持 BLOB/TEXT 字段行长度也有上限设计时要提前规避。默认情况下MySQL 还支持用CREATE TEMPORARY TABLE ... LIKE复制已有表的结构以及CREATE TEMPORARY TABLE ... SELECT直接建表并插入数据比如CREATE TEMPORARY TABLE tmp_order_2024 SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE created_at 2024-01-01 GROUP BY user_id;注意用SELECT方式建表时字段类型和索引不会完整继承比如源表的 PRIMARY KEY 不会自动带过来。要保留索引的话要么提前建好临时表再 INSERT要么建完临时表后手动补索引。3.2 临时表的索引与填充策略临时表同样可以建索引而且索引能显著提升后续查询效率。结合上面的报表场景我一般这样操作先建一张带主键的临时表CREATE TEMPORARY TABLE tmp_user_order_stats ( user_id INT NOT NULL, order_cnt INT NOT NULL, total_amount DECIMAL(12,2) NOT NULL, PRIMARY KEY (user_id), KEY idx_order_cnt (order_cnt) ) ENGINE InnoDB;然后从业务表灌入聚合数据INSERT INTO tmp_user_order_stats (user_id, order_cnt, total_amount) SELECT user_id, COUNT(*), SUM(amount) FROM orders WHERE created_at 2024-01-01 GROUP BY user_id;接着在临时表上做二次查询比如取订单量最多的前 100 个用户ORDER BY order_cnt DESC LIMIT 100可以直接走idx_order_cnt索引效率比每次都重算一遍快得多。填充临时表时有一个容易忽略的问题如果业务表中存在大量NULL值COUNT(*)和COUNT(column)的结果完全不同。COUNT(*)统计行数COUNT(amount)只统计非 NULL 的 amount。聚合逻辑写错后面整个报表都跟着错。我一般会在填充后用几条抽样 SQL 核对数据确认数量和金额都准确再继续后续步骤。3.3 生命周期管理与清理习惯显式临时表的生命周期虽然会随会话结束自动终结但在生产环境里不能依赖这个机制。原因前面提过连接池会复用连接一个连接退出后回到池中临时表并不一定会立刻删除。我在项目里定的规矩是临时表一律在存储过程或者事务代码里显式 DROP如果是在脚本里使用则包在 try-finally 块中无论如何都执行清理。下面是一个简单的伪代码示意CREATE TEMPORARY TABLE tmp_xxx (...); -- 业务逻辑 INSERT INTO tmp_xxx ...; SELECT ... FROM tmp_xxx ...; -- 清理 DROP TEMPORARY TABLE IF EXISTS tmp_xxx;不建议随便用DROP TEMPORARY TABLE去删除一个不存在的表除非带IF EXISTS否则会直接报错。DROP TEMPORARY TABLE和DROP TABLE还有一个不同点临时表只删除当前会话中同名的临时表绝不会误删物理表安全性更高。另外临时表名不要和正式表同名不只是因为遮蔽机制容易让人混淆更关键的是排查问题时很容易产生误判。工程师在运维平台看慢日志看到一张不存在的表名可能会误以为是数据丢失这种“灵异事件”我遇到过不止一次。4. SQL Server 场景把存储过程结果塞进临时表4.1 为什么会有这个需求“mssql如何将过程执行的结果插入一个临时表”是个高频搜索问题。这在 SQL Server 里是相当常见的需求尤其是在报表开发和 ETL 数据流转场景中。比如有一个存储过程dbo.GetOrderStats负责返回用户订单统计你在存储过程外面做进一步加工就需要把它的执行结果放到临时表里继续操作。或者你在一个批处理任务里调用了多个存储过程希望把不同过程的结果集合并起来同样需要临时表作为载体。SQL Server 的临时表也分两种局部临时表#TempTable和全局临时表##TempTable。局部临时表只对创建它的会话可见会话关闭即自动删除全局临时表对所有会话可见直到所有引用它的会话关闭后才被清理。大部分业务场景用局部临时表就足够了全局临时表使用时要格外小心避免多个会话互相干扰。4.2 INSERT INTO ... EXEC 标准写法SQL Server 里最常见的做法是先建临时表再用INSERT INTO ... EXEC把存储过程的结果插进去CREATE TABLE #OrderStats ( UserId INT, OrderCount INT, TotalAmount DECIMAL(12,2) ); INSERT INTO #OrderStats (UserId, OrderCount, TotalAmount) EXEC dbo.GetOrderStats StartDate 2024-01-01, EndDate 2024-12-31; SELECT * FROM #OrderStats;注意几个容易踩雷的地方临时表的列数必须与存储过程返回的结果集列数完全一致列名可以不一样但位置和数量必须匹配。数据类型要兼容比如目标列是 INT结果集返回的是 VARCHAR插入时可能发生隐式转换某些情况下会报转换错误。存储过程返回多个结果集时INSERT INTO ... EXEC只会处理第一个结果集其余结果集被忽略。还有一个隐藏较深的限制存储过程内部如果已经使用了INSERT INTO ... EXEC那么这个存储过程不能再被另一个INSERT INTO ... EXEC调用。SQL Server 不允许嵌套执行INSERT INTO ... EXEC否则直接报错。这个限制经常出现在存储过程 A 调用存储过程 B且两者都使用INSERT INTO ... EXEC的场景。解决嵌套问题的方法通常是把内层存储过程的INSERT INTO ... EXEC改成先插入物理表或临时表然后再由外层读取。或者用 OPENROWSET 这类方式绕开限制但 OPENROWSET 涉及权限和分布式查询配置不是首选方案。4.3 SELECT INTO 与临时表的作用域SQL Server 里还可以直接用SELECT INTO一次完成建表和插入这种方式更简洁SELECT UserId, OrderCount, TotalAmount INTO #OrderStats FROM dbo.Orders;但SELECT INTO在存储过程场景中有个局限它不能直接从EXEC的结果集建表。-- 这一段会报错 SELECT * INTO #OrderStats EXEC dbo.GetOrderStats;SQL Server 不支持这种写法所以“把过程执行结果插入临时表”这个需求标准答案还是先CREATE TABLE #Temp然后INSERT INTO #Temp EXEC 存储过程名。还有一个作用域细节如果在存储过程中创建了#TempTable这个临时表只存在于该存储过程的会话内过程执行结束就会被删除。如果存储过程外部的调用方也需要这张临时表必须在调用方会话中先创建好再传给存储过程使用。这个“共享临时表”的模式在复杂存储过程设计中非常实用但要注意列结构必须提前定义一旦存储过程里写入的列和外部定义不一致就会报错。至于表变量Table它和临时表也能存储中间结果但表变量更适合数据量小、不需要索引优化的场景。表变量不支持创建索引只能加主键约束优化器对它的基数估算也经常不准数据量一大就可能导致执行计划出现偏差。我在存储过程里数据量超过几千行的中间结果一律用#临时表不使用表变量。4.4 嵌套执行与动态 SQL 的坑讲一个真实案例。有一次我接手一个报表存储过程内部调了另外三个存储过程每个都返回结果集。最初的实现是把三个过程的结果统一插到一张临时表里CREATE TABLE #ReportData (...); INSERT INTO #ReportData EXEC proc_A; INSERT INTO #ReportData EXEC proc_B; INSERT INTO #ReportData EXEC proc_C;这个写法本身没问题三个过程顺序执行结果汇总到一张临时表。问题出在 proc_A 本身内部还有个INSERT INTO #Temp EXEC proc_A_Inner这就触发了嵌套INSERT INTO ... EXEC的限制SQL Server 直接抛错An INSERT EXEC statement cannot be nested.当时排查了很久才确认原因。解决思路并不复杂把 proc_A 内部的INSERT INTO #Temp EXEC改成先把结果插到一张物理临时表里再由外层读取。改成这种结构之后所有INSERT INTO ... EXEC都保持在同一层级不再嵌套问题就消失了。如果你在存储过程中使用动态 SQL 往临时表插入数据也要注意作用域问题CREATE TABLE #T (Id INT); DECLARE sql NVARCHAR(4000); SET sql NINSERT INTO #T (Id) VALUES (1); EXEC sp_executesql sql;这段代码可以正常工作因为sp_executesql虽然有自己的会话上下文但依然能看到临时表。但如果你在动态 SQL 内部使用SELECT INTO创建临时表却想在外部访问它就会失败——动态 SQL 创建的表只在动态执行期间存在执行完就销毁了。5. 一个完整案例把 Using temporary 吃掉的复杂查询改造成临时表方案5.1 原始查询与性能瓶颈前面把概念和基础用法都过了一遍这一节用一个实际案例把两套知识串起来。假设业务库中有三张表users用户基础信息主键 user_id。orders订单主表含 order_id、user_id、order_amount、created_at。order_items订单明细含 item_id、order_id、product_id、quantity。业务需求是统计 2024 年每个用户的订单总额、订单数量以及下单次数最多的商品分类。加了一个条件只要订单总金额排名前 500 的用户需要展示其用户信息和商品分类 Top 1。第一版 SQL 直接一把梭SELECT u.user_id, u.user_name, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(o.order_amount) AS total_amount, ( SELECT oi.category_id FROM order_items oi WHERE oi.order_id IN ( SELECT o2.order_id FROM orders o2 WHERE o2.user_id u.user_id ) GROUP BY oi.category_id ORDER BY SUM(oi.quantity) DESC LIMIT 1 ) AS top_category FROM users u LEFT JOIN orders o ON u.user_id o.user_id LEFT JOIN order_items oi ON o.order_id oi.order_id WHERE o.created_at 2024-01-01 GROUP BY u.user_id, u.user_name ORDER BY total_amount DESC LIMIT 500;这条 SQL 在测试库只有几十万订单时还勉强能跑但数据量到了千万级别就直接拉胯。EXPLAIN 的 Extra 列里同时出现了Using temporary和Using filesort内部临时表被优化器建了好几次特别是子查询里的相关子查询每一行用户数据都需要重新计算一次。整个查询跑了快 40 秒业务方根本没法接受。5.2 拆解改造过程这是一条典型的“该拆不拆”的 SQL。我的改造方案分三步。第一步把订单维度的聚合先做出来写入临时表。CREATE TEMPORARY TABLE tmp_user_agg AS SELECT o.user_id, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(o.order_amount) AS total_amount FROM orders o WHERE o.created_at 2024-01-01 GROUP BY o.user_id;第二步单独统计每个用户最容易下单的商品分类。为了避免在临时表里做复杂嵌套我再建一张分类聚合的临时表先算出每个用户在每个分类下的下单数量CREATE TEMPORARY TABLE tmp_user_category AS SELECT o.user_id, oi.category_id, COUNT(*) AS category_cnt FROM orders o JOIN order_items oi ON o.order_id oi.order_id WHERE o.created_at 2024-01-01 GROUP BY o.user_id, oi.category_id;有了这两张临时表第三步只需要做简单的 JOIN 和排序SELECT u.user_id, u.user_name, t1.order_cnt, t1.total_amount, t2.category_id AS top_category FROM users u JOIN tmp_user_agg t1 ON u.user_id t1.user_id LEFT JOIN ( SELECT user_id, category_id FROM tmp_user_category t WHERE t.category_cnt ( SELECT MAX(t2.category_cnt) FROM tmp_user_category t2 WHERE t2.user_id t.user_id ) ) t2 ON u.user_id t2.user_id ORDER BY t1.total_amount DESC LIMIT 500;这里用了一个子查询取每个用户的分类最大值也可以换成窗口函数ROW_NUMBER()效果类似。5.3 改造后的效果与要点改造后查询时间从 40 秒降到了 3 秒左右。原因并不复杂原来一条 SQL 里要反复 JOIN、聚合、相关子查询优化器多次创建磁盘临时表IO 压力巨大。拆成两张临时表后每张表的聚合逻辑只需要扫描一次订单主表和明细表中间结果被复用整体扫描的数据量大幅减少。这个案例里有几个改造要点值得记住临时表只是载体真正提升性能的是让每一段查询都做“最小必要扫描”。一次性大查询拆开后每一段都更容易利用索引。临时表拆解后可以分阶段验证数据正确性。比如先检查tmp_user_agg里的金额合计是否与订单表 SUM 一致再检查分类统计是否准确。这个优势在复杂报表场景中极为重要。分步操作让执行计划的可控性上升不再依赖优化器对复杂查询的成本判断。优化器面对复杂查询时并不总是能选到最优路径手动拆分相当于替优化器做了更明确的决策。值得注意的是CREATE TEMPORARY TABLE ... AS SELECT创建的临时表默认不建索引。在tmp_user_agg上给 user_id 加主键在tmp_user_category上给 user_id、category_id 加复合索引后续 JOIN 的效率还能进一步提升。索引的创建时机也很有讲究先建表后灌数据或者先灌数据再补索引各有适用场景。大批量数据灌入时先不加索引反而更快灌完再一次性建索引通常更优。6. 常见问题与排查技巧实录6.1 排查手册问题与解法速查日常工作中关于临时表的问题来来去去就那么几类。我整理了一张速查表都是实际踩过的坑。问题现象可能原因处理方式EXPLAIN 出现 Using temporary且查询变慢未索引字段参与 GROUP BY/ORDER BY优先补索引必要时手动拆分查询Created_tmp_disk_tables 快速增长tmp_table_size 或 max_heap_table_size 偏小调大较小值并观察内存压力MySQL 临时表查询结果和主表对不上会话内临时表和物理表同名遮蔽了物理表修改临时表名避免同名遮蔽SQL Server 报 “An INSERT EXEC statement cannot be nested”INSERT INTO ... EXEC 嵌套使用拆成同一层级或者改用物理表中转SQL Server 临时表在过程结束就没了局部临时表会话级生命周期在调用方会话创建临时表再传入过程使用SELECT INTO 不能直接接 EXEC 结果集SQL Server 语法限制先 CREATE TABLE再 INSERT INTO ... EXEC表变量数据量大时查询缓慢表变量基数估算不准、无法建索引改用 # 临时表6.2 三条实操心得第一临时表不是用来掩盖 SQL 问题的东西。如果一个简单查询本身就触发了Using temporary最优先的解法永远是加索引或者改写 SQL而不是直接套一层临时表。临时表适合的是那些业务逻辑确实需要多阶段处理的场景用错了地方反而会增加维护成本和额外的 IO。第二监控数据比直觉更可靠。我在临时表调优上吃过亏凭感觉把tmp_table_size调高结果内存被占满整个实例差点 OOM。后来学乖了每次调整参数前都先记录Created_tmp_disk_tables和Created_tmp_tables的基线值调整后观察 24 小时再下结论。生产环境里的参数调整没有监控数据支撑就是耍流氓。第三跨数据库的临时表语法差异很大。MySQL 的CREATE TEMPORARY TABLE、SQL Server 的#TempTable、Oracle 的全局临时表GLOBAL TEMPORARY TABLE三者虽然都叫临时表但在作用域、事务行为和清理机制上各有不同。如果你平时在多个数据库之间切换最稳妥的办法是每到一个新环境就先把临时表的生命周期和行为测一遍别拿经验直接套。比如 MySQL 临时表在事务回滚时不会自动删除而 Oracle 全局临时表的数据在提交或会话结束后是否保留取决于ON COMMIT的设置这些差异只有实测才靠得住。排除问题的时候我还会习惯性地看一眼临时表相关的状态值。如果一条 SQL 执行后Created_tmp_tables从 1000 涨到 2000说明它在执行过程中创建了大量临时表即使 EXPLAIN 里没有直接标记也有优化空间。这种从全局视角排查问题的方式比只看一条 SQL 的 EXPLAIN 更能发现系统性隐患。最后再分享一个小习惯写复杂 SQL 时我会把临时表名统一加上tmp_前缀并在注释里写明这张表的作用和来源。看起来是个小事但在接手别人代码或者三天后再看自己的 SQL 时这种命名规范能省下大量沟通和排查时间。临时表虽然“临时”但写清楚它的用途能让一段临时逻辑真正沉淀为团队可以复用的资产。