企业官网建设流程全解析

一次线上排查让我彻底把MySQL临时表这个知识点重新捋了一遍。某同事的实例压测时磁盘占用疯涨，点开状态变量一看，Created_tmp_disk_tables一小时涨几万，问题直指临时表失控。MySQL临时表这个词，面试题里天天见，生产环境里却很少有人真把它用明白。很多人只记得"临时表会自动删除"这一条，却不清楚会话私有的真实含义、内存态与磁盘态的切换条件、更不知道优化器什么时候会偷偷替你建一张临时表，然后狠狠拖垮性能。

所以这篇我把临时表完整串一遍：从它的价值边界、存储机制，到两次真实的排查和改造案例，再到连接池残留这种隐蔽坑，以及我碰过之后得出的"不要用临时表"清单。偏实战，适合正在做SQL优化、设计报表查询、或者被临时表坑过的同学。看完你至少能回答自己三个问题：什么时候手动建临时表值当？什么时候是瞎折腾？以及怎么快速定位是不是内部临时表在作怪。

1. 临时表的价值边界：它不是"用完即焚的垃圾桶"

很多同学对临时表的理解就一句话："用完自动删"。这句话没错，但太粗糙了。临时表真正的价值在于会话级的数据隔离，以及把复杂查询拆成多步可控操作。用不好它，它就是你SQL里藏得最深的性能炸弹。

1.1 临时表的三条铁律

第一，会话私有。你在一个连接里创建的临时表，其他任何连接都看不到。它特别像会议室白板上的草稿，在座的人看得见，门外的人完全不知道。这个特性决定了它适合做"当前请求的中间结果缓存"，不适合做跨请求共享数据。

第二，生命周期绑会话。连接断开、会话结束，临时表连同数据和索引一起被MySQL清掉，不需要你手动清理。如果你的连接长期存活（比如连接池里的物理连接），临时表会一直存在，这个特性后面章节单独讲，是个大坑。

第三，它可以有结构、索引、甚至引擎类型，用法和普通表几乎一样。你可以CREATE TEMPORARY TABLE然后INSERT、JOIN、GROUP BY随便折腾。但要注意，临时表上的索引需要你自己定义，它不会像普通表那样被优化器自动"照顾"太多。

1.2 和普通表、MEMORY表放在一起看差异

很多人分不清临时表和MEMORY引擎表，这里直接给一张对比表：

MEMORY引擎表是真实存在的表，多个会话能同时操作，数据虽然不落盘但表结构定义是持久化的。临时表则完全相反，它从出生到销毁都在会话内部完成。通俗点说：MEMORY表是"所有人的公共便签本"，临时表是"你自己手里的草稿纸"。搞清楚这个区别，你就不会在"要不要把临时表换成MEMORY表给其他接口用"这种问题上纠结——答案是不能。

2. 临时表的内存态与磁盘态——存储机制的切换逻辑

聊临时表，绕不开内存态和磁盘态。这个概念很多人理解成"临时表先放内存，放不下就放磁盘"，但真实切换逻辑在5.7和8.0里差别很大。我看到太多网上结论直接拿5.7的经验套8.0，结果踩了更深的坑。

2.1 版本差异：MEMORY引擎与TempTable引擎

MySQL 5.7及更早版本里，内存临时表用的是MEMORY引擎，它有两个明显限制：一是不支持TEXT和BLOB字段，二是有严格的行大小限制。一旦临时表里涉及TEXT字段，或者行大小超限，MySQL完全不跟你商量，直接落到磁盘临时表。

MySQL 8.0开始默认改用TempTable引擎，内存分配方式变了，也支持了BLOB/TEXT字段。TempTable引擎先在内存分配空间，超过内存阈值后不直接落盘，而是先使用内存映射文件，再用到一定量级才真正写入磁盘临时文件。也就是说，8.0下"临时表是否落盘"的判断点比5.7宽松不少，但一旦落盘，文件增长速度依然很吓人。

这里要特别注意：网上那些"临时表超过16MB就转磁盘"的说法，本质是讲5.7的行为。放到8.0，你需要关心的参数完全不一样。

2.2 影响切换的关键参数

临时表从内存态切到磁盘态，主要受这几个参数影响：

在5.7里，真正决定内存临时表上限的是tmp_table_size和max_heap_table_size的较小值。也就是说你就算把tmp_table_size调到1GB，如果max_heap_table_size还是16MB，那内存临时表照样16MB就转磁盘。8.0里TempTable引擎则主要被temptable_max_ram约束，默认1GB，看起来宽裕，但如果表里数据量大，落到磁盘后同样会冲击tmpdir所在的分区。

所以生产环境建议：把tmpdir指到独立的大容量目录，不要让它和根目录放在一起。这是花钱买教训换来的。某次磁盘写满的事故，就是根目录被临时表文件撑爆，服务直接只读。

2.3 MySQL什么时候会偷偷替你建临时表

除了你手动CREATE TEMPORARY TABLE，MySQL优化器在执行很多SQL时也会内部生成临时表，这类叫内部临时表。它不用你授权，也不显式出现在SQL里，但资源照吃。常见的触发场景：

• UNION合并结果集，尤其UNION默认还会去重，去重就得排序，排序就可能建临时表；
• GROUP BY或ORDER BY的字段没有可用索引，优化器需要把数据先装进临时表再做分组排序；
• DISTINCT和GROUP BY混用，又出现无法走索引的情况；
• 派生表（子查询）无法合并到外层查询时，优化器会把子查询结果物化成一张临时表；
• 查询涉及BLOB/TEXT字段，5.7下几乎直接落磁盘。

怎么发现？最直接的办法是EXPLAIN看结果，Extra列出现Using temporary就是内部临时表的明确标记。还有两个状态变量好用：Created_tmp_tables是累计创建的临时表数量，Created_tmp_disk_tables是其中落到磁盘的临时表数量。两者一对比，磁盘临时表占比高，说明内存参数不够或SQL本身需要优化。

3. 一次磁盘写满事故的排查复盘：磁盘临时表失控

前面讲的是原理，这部分我完整复盘一次磁盘临时表失控的排查过程。整个链路走一遍，你会发现这类问题没有想象中难定位，但一旦定位错了方向，会很耽误时间。

3.1 事故现场

某实例在某天大促压测夜出现磁盘告警，根目录使用率从40%一路冲到90%。上去看进程列表，没有特别离谱的长事务SQL，也没有大查询在跑。再看磁盘文件，发现tmpdir目录下出现了大量#sql开头的临时文件，大小从几百MB到几个GB不等。第一反应就是：内部临时表疯狂落盘。

但问题来了——当时并没有慢查询日志命中这些SQL，因为单个SQL执行时间并没有超阈值。这就是内部临时表的隐蔽性：SQL跑得不算慢，但你架不住它被高频调用，每次创建一张几GB的磁盘临时表，频率一高，磁盘就撑不住了。

3.2 如何找出谁在建临时表

排查这种问题，我建议这样做：

1. 先看状态变量确认方向：SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'记录一次，等几分钟再查一次，算增量。增量大，说明磁盘临时表在持续产生。
2. 用performance_schema的events_statements_summary_by_digest按SUM_CREATED_TMP_DISK_TABLES排序，能直接捞出创建磁盘临时表最多的SQL语句模板。
3. 再对疑似SQL做EXPLAIN，确认Using temporary出现在哪一步。

这次定位到的SQL大概长这样：

SELECT user_id, COUNT(*), SUM(amount) FROM payment_flow WHERE create_time BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 23:59:59' GROUP BY user_id;

payment_flow表当时大概九百万行，索引建立在create_time上。按时间过滤没问题，但GROUP BY user_id没有对应索引，优化器只能把所有命中的行倒进临时表，再在临时表上分组排序。单看执行计划，Extra列里就是Using temporary; Using filesort。

3.3 根治手段与验证

方案有很多，我按优先级排：

• 方案A：给user_id加联合索引，让GROUP BY能走松散索引扫描。实际做的是ALTER TABLE payment_flow ADD INDEX idx_user_time(user_id, create_time)，建完之后EXPLAIN里Using temporary消失，分组直接在索引上进行。
• 方案B：SQL改写，先尽量缩小参与分组的数据量。比如把时间范围缩减，或者把部分聚合逻辑提前用子查询做掉。
• 方案C：如果确实需要临时表，注意字段宽度。SELECT里别把varchar(255)的大字段带进来分组，建议先取主键和分组字段，后续需要明细再回表。

最后验证不是看磁盘占用降了就算完，而是看Created_tmp_disk_tables的增量是否趋近于0。我们当时加完索引之后，增量从每小时几万直接掉到偶尔个位数，磁盘文件也自动清理了。整个过程最值得记住的一点是：磁盘临时表一旦占比高，首选看索引，次选改SQL，最后才考虑调参数。调参数只是把线往后移，问题SQL还在。

4. 手动创建临时表拆解大报表查询——实战笔记

讲完事故，再讲一个主动用临时表的案例。这类场景恰恰是很多人不知道什么时候该用临时表的地方——报表查询、多表JOIN后的大聚合。

4.1 原始查询的问题出在哪

某报表需求，要统计一段时间内每个用户在各渠道的消费汇总。MySQL大概要执行：

SELECT u.user_id, c.channel_name, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(o.pay_amount) AS total_amount FROM users u JOIN orders o ON o.user_id = u.user_id AND o.pay_time >= '2024-01-01 00:00:00' JOIN order_items oi ON oi.order_id = o.order_id JOIN channels c ON oi.channel_id = c.channel_id GROUP BY u.user_id, c.channel_name ORDER BY total_amount DESC;

表面上逻辑不复杂，但这四条JOIN会把订单明细和渠道信息拍在一起，中间结果集非常膨胀，然后再对这个巨型结果集做GROUP BY和ORDER BY，优化器不得不生成一张巨大的内部临时表，最后还可能落到磁盘。

4.2 拆两步的执行步骤

我的做法是把大查询拆成两步临时表操作：

第一步，先把订单和渠道明细筛选到一个临时表里，只留后面聚合需要的字段：

CREATE TEMPORARY TABLE tmp_order_channel AS SELECT o.user_id, o.order_id, oi.channel_id, o.pay_amount FROM orders o JOIN order_items oi ON oi.order_id = o.order_id WHERE o.pay_time >= '2024-01-01 00:00:00'; ALTER TABLE tmp_order_channel ADD INDEX idx_user_channel(user_id, channel_id);

第二步，拿临时表和users表做用户维度的补全，再做聚合：

SELECT t.user_id, c.channel_name, COUNT(DISTINCT t.order_id) AS order_cnt, SUM(t.pay_amount) AS total_amount FROM tmp_order_channel t JOIN channels c ON c.channel_id = t.channel_id JOIN users u ON u.user_id = t.user_id GROUP BY t.user_id, c.channel_name ORDER BY total_amount DESC;

注意我在临时表上手动建了(user_id, channel_id)的联合索引。这一步很多人会忽略，临时表建完就直接GROUP BY，结果速度没提升多少，因为二次聚合依然在扫全表。临时表创建的时候就是SELECT结果，不会自动带索引，必须手动补。

4.3 这一步到底省了什么

有人会说："这不是多写了几段SQL吗？有什么本质区别？"

核心在于控制了中间结果集的膨胀规模。原来的查询是四大表做JOIN之后才分组，参与分组的数据量可能是千万级；拆成两步之后，第一步先把符合条件的订单和渠道明细压缩到一张几百万行的窄表，字段少、行数可控，第二步再聚合时输入数据量大幅缩减。更重要的是，GROUP BY需要的排序不再作用于原始宽表的巨型结果集，而是作用于窄表，排序代价完全不同。

分组聚合后如果还需要和用户表关联，我甚至会把聚合结果再存一张更小的临时表，然后再JOIN。这种"宽表转窄表、大结果集转小结果集"的操作，就是临时表最典型的生产价值。用大白话讲：你没有改变业务逻辑，只是把一次"把整个菜市场翻一遍"的动作，变成了"先挑出需要的菜、再清点数量"，后者自然快得多。

当然，这套写法的前提是你能确认临时表的数据量落在可控范围。如果第一步的临时表依然是几千万行，那就要考虑是不是查询条件本身有问题，或者干脆该上数仓了。

5. 连接池复用下的临时表残留陷阱

这个坑是我在一套Java服务里实际踩过的，问题很隐蔽，也很值得单独讲一章。如果你用的连接池，并且业务代码里曾经创建过临时表，请务必注意。

5.1 现象描述

某服务有一个导出接口，第一次调用时结果正常，第二次第三次调用开始出现"能查到别人的数据"这种极其诡异的问题。最开始怀疑缓存，排查了业务缓存没有命中；后来怀疑是普通表数据串了，看了表数据也没问题。最后有人指了指SQL日志，发现两次请求用的竟然是同一个MySQL连接ID。

再一查，第一次请求里创建的一张临时表tmp_user_filter竟然还在。第二次请求复用了同一个物理连接，MySQL会话并没有真正结束，会话级的临时表全都还在，于是第二个请求直接查到了第一个请求写入的中间数据。

5.2 成因：会话生命周期比你想的长

这就是我在第一章提到的"生命周期绑连接"的另一面。应用层看"请求结束了"，但连接池里的物理连接没有断开，MySQL会话一直活着，临时表也就一直活着。连接池复用的本质是复用了MySQL会话，而不是每请求新建一个会话。所以"用完自动删"这句话在连接池场景下是不成立的——自动删是连接断开时才删，而不是事务结束或接口返回时删。

如果连接池里一个物理连接被几十上百个请求循环复用，而你恰好在一个请求里建了临时表又没清理，后面的所有请求都能看到这张表里的残留数据。这种问题的危险程度很高，因为它属于"偶发数据错乱"，最容易被误判成业务bug。

5.3 应对：代码里及时DROP

解决方案其实很简单，三条：

1. 在业务代码里建临时表后，一定要try/finally里执行DROP TEMPORARY TABLE IF EXISTS 表名，确保无论后续逻辑是否报错，临时表都被清掉。
2. 如果框架有连接归还钩子，可以在连接归还时统一清理会话临时表，但这依赖框架能力，不如代码里显式DROP来得直接。
3. 假如只是想在单条查询内用中间结果，优先考虑派生表（子查询）或者公用表表达式（CTE）。它们的作用域限于单条SQL，天然不存在跨请求残留。

这里给个代码层面的参考写法：

try: cursor.execute("CREATE TEMPORARY TABLE tmp_user_filter AS SELECT id FROM users WHERE status=1") cursor.execute("SELECT ... FROM target t JOIN tmp_user_filter f ON t.user_id=f.id") finally: cursor.execute("DROP TEMPORARY TABLE IF EXISTS tmp_user_filter")

这种写法看起来多了一句DROP，但它能堵住连接池复用这个最大的漏洞。尤其是你先建临时表、中间又抛了异常的情况下，没有finally的DROP，表就留到下一个请求了。

6. 什么时候别碰临时表——量力而行

讲了很多临时表的正确用法，最后得泼一盆冷水。临时表是有适用边界的，很多场景下强行用它，不但没有收益，还会把系统搞得更复杂。

6.1 不要用临时表替代索引优化

最典型的错误是：一条JOIN查询慢，不先看执行计划、不加索引，直接"CREATE TEMPORARY TABLE ..."。这属于治标不治本。临时表本质是"把中间结果切出来重算"，如果你的原始SQL就是因为缺索引导致的大连接，那你建临时表只是把缺索引的问题从这次查询挪到了临时表的构建过程里。构建临时表本身又是一次全表扫描和写入，开销并没有消失，甚至可能更大。

优化顺序应该永远是：先看索引能不能走、SQL能不能改写、数据量能不能提前过滤。最后才轮到临时表。临时表是策略，不是补丁。

6.2 临时表的索引与统计信息局限

临时表上建的索引要靠你手动补，但统计信息往往是不完整甚至没有的，优化器在给临时表选择执行计划时容易误判。典型的表现是：你创建了一张临时表，可能行数就几十万，但优化器估算不准，JOIN时还是选了全表扫描或者错误的驱动顺序。

所以一旦发现"手动建临时表之后，后续查询还是慢"，不要死磕，先EXPLAIN看临时表被怎么访问的，再考虑是否需要调整临时表本身的索引设计。我自己的经验是：临时表的索引尽量建在分组字段和连接字段上，字段类型要和外层JOIN完全一致，否则隐式类型转换一出现，索引直接失效。

6.3 会话私有的另一面：跨会话需求

临时表会话私有，意味着它无法承担"多个接口共享中间结果"这类需求。有些人为了让结果缓存，想把临时表改成普通表，这就引出了新的管理负担：谁来清理、什么时候清理、并发写入会不会互相干扰。跨会话共享的数据，老老实实用普通表或者缓存中间件，别让临时表干它干不了的活。

还有一个场景也不建议碰临时表：高频短查询。每次请求都建一张临时表，哪怕数据量很小，也是实打实的DDL操作，涉及元数据锁、会话资源分配。高频场景下频繁建删临时表，性能反而比纯SQL查询更差。适用临时表的查询，一定得是"计算过程重、重复利用价值高"的场景。

结尾：一点个人习惯

处理完这几次临时表的事故之后，我给自己定了两个规矩：任何SQL评审先看EXPLAIN里有没有Using temporary，有就先过一遍索引；任何监控面板必须带上Created_tmp_disk_tables这个状态变量，不用解释含义，就看它在某个时段有没有突然抬头。临时表本身是个好工具，很多时候也是优化器无奈之下的折中，关键是你要能看见它、说得清它为什么出现、控制得住它的成本。能做到这三点，临不临时表，你都不会被它坑到。