MySQL临时表落盘排查：从内存表到磁盘表

  1.  Home
  2. Text  
    3. Share to：

一个下午的卡顿：从「网站慢」到「临时表落盘」

去年夏天，一个跑在 2 核 4G 小机器上的博客站出现过一次很有代表性的卡顿。现象是：平时正常的页面，偶尔有几秒钟完全没有响应，后台日志里查不到 PHP 报错，慢查询日志里也没有记录超过 2 秒的 SQL。我一开始怀疑是网络抖动，换了几个运营商测，又怀疑是 FPM 进程池被打满，调大了 pm.max_children，卡顿一点没少。

真正找到原因，是在一次卡顿时我正好开着 iotop。屏幕上 mysqld 的写盘速度瞬间冲到了 40MB/s，持续两三秒，然后又掉回零。这台机器上没有定时备份在那个时间点跑，也没有 binlog 大量写入，唯一可能的就是 MySQL 自己在写盘——而 MySQL 在查询过程中会写盘的事情，最主要的一件就是临时表落盘。

事后复盘：那个页面的侧边栏有一个「按分类统计最近 30 天热门标签」的小模块。数据量不大，但它对一个 TEXT 类型的标签字段做了 GROUP BY，而且用的是 DISTINCT。当文章越来越多，这个中间结果超过了 tmp_table_size，MySQL 就把内存临时表换成了磁盘临时表，写到 /tmp 或者 mysqld_tmpdir 下。而那个目录恰好和系统的其他临时文件挤在同一个分区上，还正好在机械盘上。

这件事教会我一件事：临时表是 MySQL 里最安静的性能杀手。它不报错、不出现在慢查询日志的显眼位置（除非你正好调低了阈值）、不占用连接数，但它能在几秒内吃掉你的 IO 带宽，让同期所有查询一起变慢。这篇文章就把临时表相关的一整套东西讲清楚：什么时候落盘、怎么看、怎么防、以及防不住的时候怎么把代价限制在一个可控范围内。

先分清三件容易混为一谈的事

在讲临时表之前，必须先把三个经常被混在一起的概念拆开，否则后面所有判断都会错。

第一件是「内存临时表」。执行 GROUP BY、DISTINCT、UNION、子查询物化、ORDER BY 配合非索引列排序时，MySQL 需要一块中间结果集。默认情况下这块东西放在内存里，用的是 MEMORY 存储引擎（内部叫 HEAP）。它有上限，超过上限就要么换成磁盘表，要么直接报错。

第二件是「磁盘临时表」。当内存临时表超过阈值，MySQL 会创建一张以 #sql_ 或 #sql_tmp_ 开头的隐藏表，默认落在 tmpdir 指向的目录。从 MySQL 8.0.16 开始，磁盘临时表默认使用 TempTable 引擎（这是独立于 InnoDB 和 MEMORY 的第三种临时表引擎），它会先尝试用大页内存分配，分配失败才真正落盘。

第三件是「InnoDB 内部临时表」。这是最容易被忽略的一类：当查询涉及 InnoDB 表的 GROUP BY 且优化器选择走「内部临时表 + 后续排序」路径时，8.0 之后的版本可能会直接使用 InnoDB 临时表（表空间位于 #innodb_temp 目录下的 ibtmp1 或会话临时表空间），而不是 TempTable 引擎。这类临时表写的是 InnoDB 的共享临时表空间文件，跟你 /tmp 的剩余空间没关系，反而跟 innodb_temp_data_file_path 的设置有关。

为什么要分这么细？因为三种情况的排查手段完全不同：内存临时表看 Created_tmp_tables，磁盘临时表看 Created_tmp_disk_tables 和 tmpdir 的空间，InnoDB 内部临时表要看 ibtmp1 的体积增长。混着看，就会得出「临时表不多」的错误结论。

两个计数器：Created_tmp_tables 与 Created_tmp_disk_tables

MySQL 在 SHOW GLOBAL STATUS 里给了几个直接相关的计数器，最小的一套判断只看两个就够：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 18422   |
| Created_tmp_files       | 96      |
| Created_tmp_tables      | 612874  |
+-------------------------+---------+

三个数字的含义分别是：

  • Created_tmp_tables：一共创建了多少张临时表（内存 + 磁盘都算）。
  • Created_tmp_disk_tables：其中有多少张「最终」变成了磁盘临时表。注意是「最终」，一张表可能先在内存里建、超限后转成磁盘，这时候两个计数器都会 +1，而不是各加一个。
  • Created_tmp_files：mysqld 创建的临时文件个数。这个数字通常远小于磁盘临时表数，因为一张磁盘临时表对应两个文件（.frm 和 .ibd 或数据文件），而且它是累计值，不是当前值。

关键比值是 Created_tmp_disk_tables / Created_tmp_tables。上面这组数字是 3%，看起来还行。业界的经验线是 25%：超过这个比例，说明有相当一部分查询的中间结果撑爆了内存，值得逐个去找。低于 10% 一般不用管。

但这两个计数器有个坑：它们是累计值，从 mysqld 启动开始加，不会自己归零。所以你不能直接拿生产环境跑了一个月的数字来判断「现在的问题」，必须做区间采样：

SELECT VARIABLE_VALUE FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'Created_tmp_disk_tables';
-- 等 60 秒，比如插一段业务高峰
SELECT VARIABLE_VALUE FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'Created_tmp_disk_tables';

两次相减，才是这一分钟里真正发生的磁盘临时表数。我在排查上面那个博客站的时候，就是靠这个办法发现：高峰一分钟能产生 300 多张磁盘临时表，而全天累计才 18422，说明问题高度集中在某几个页面上。这个「集中」的结论非常关键，它把排查范围从「整个站点」压缩到了「几个 URL」，后面去找那个标签统计模块就快多了。

tmp_table_size 与 max_heap_table_size：两个都要改

决定一张临时表能不能留在内存里的，是两个参数，而取它们两个的较小值作为实际上限：

  • tmp_table_size：临时表体积上限，默认 16M。
  • max_heap_table_size：用户显式创建的 MEMORY 表的上限，默认 16M，但它同时也约束隐式临时表。

这里有个极其常见的事故：很多人只把 tmp_table_size 调到 64M，以为问题解决了，结果 max_heap_table_size 还是默认的 16M，实际上限仍然是 16M，一点没变。我见过至少三次这种情况，包括我自己第一次调的时候。所以这两个参数必须一起改：

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

那到底调多大？这里必须说清楚一个容易被忽略的代价：这个上限是「每连接」的。如果 max_connections = 200，理论上一瞬间可以有 200 个连接同时在用 64M 的临时表，总量就是 12.8G。当然实际并发远小于这个数，但你需要按「真实并发 × 上限」来算内存账。

我的做法是先算出「真实同时存在临时表的连接数」。这个可以从 performance_schema 里拿到比较靠谱的数：

SELECT COUNT(*) FROM performance_schema.events_statements_current
 WHERE SQL_TEXT LIKE '%GROUP BY%' OR SQL_TEXT LIKE '%DISTINCT%';

这只是个粗估，更准确的办法是在 SHOW PROCESSLIST 里看 Copying to tmp table 状态的连接数：

SELECT COUNT(*) FROM information_schema.processlist
 WHERE state LIKE '%tmp table%';

在 4G 内存的小机器上，我的经验值是：把上限设在「可用内存 ÷ 预期并发 ÷ 2」，不要贪心。比如 4G 机器，给 MySQL 分 1.5G，预期同时最多 8 个带临时表的连接，那么设 64M 就已经很宽裕了（8 × 64M = 512M）。设成 256M 反而会在某次突发时把整台机器拖进 swap，比磁盘临时表慢十倍。

另一个必须知道的点：tmp_table_size 不是硬上限。 当临时表要突破上限时，MySQL 会当场把它从内存转成磁盘表，而不是报错。所以「调小了会报错」这个担心是不存在的，代价只是「落盘」。反过来，MEMORY 表本身有个硬限制：单个 MEMORY 表的行长不能超过 max_heap_table_size，但隐式临时表不受此约束，它超了就转磁盘。

临时表一定会落盘的四种情形

即使内存够，有些临时表是「注定要落盘」的。了解这些情形，能让你在调参数之前就知道哪些查询是白调的。

情形一：含有 BLOB 或 TEXT 列的临时表。 这是最硬的一条规则。MySQL 官方文档写得很直接：内部临时表如果包含 BLOB 或 TEXT 列（包括 VARCHAR 超过一定长度的变体），一律使用磁盘临时表，跟 tmp_table_size 设多大无关。这意味着那些「对文章正文做 GROUP BY」「对 JSON 字段做 DISTINCT」的查询，无论怎么调参数都会写盘。唯一的解决方式是改查询——先去 TEXT 再聚合，或者干脆用索引覆盖掉。

情形二：中间结果集真的超过上限。 这个最容易理解，也确实是调参数能解决的部分。一个 GROUP BY 产生 500 万行中间结果，靠调参数是救不了的，那已经说明查询本身有问题。

情形三：MySQL 8.0 对分区表的某些操作。 涉及分区表的 GROUP BY 在部分版本里会强制使用临时表，而且不区分内存/磁盘。这类查询更该关注的是「能不能避免临时表」，而不是调大小。

情形四：UNION DISTINCT 与子查询物化。 UNION ALL 不需要去重，可以直接流式输出；而 UNION（即 UNION DISTINCT）必须先去重，就要建临时表。如果业务上不要求去重，把 UNION 改成 UNION ALL 是零成本的优化，这一点在分页列表拼接、多来源数据合并的查询里非常常见。

我在那个博客站上做的最有效的一次改动，就是把侧边栏那个「最近 30 天热门标签」从「一次 SQL 聚合 TEXT 字段」改成了「先只取 tag 这一列（VARCHAR(32)，不带 TEXT），再用 PHP 的 array_count_values 聚合前 20 个」。看起来是「把活从数据库挪到了 PHP」，但因为这个页面的数据本身就是从 APCu 缓存里出的，改完之后那个模块一次 SQL 都不发了，磁盘临时表数直接掉到了个位数。

用 EXPLAIN 判断一个查询会不会建临时表

调参数之前，先确认这个查询到底用不用临时表。EXPLAIN 的 Extra 列会直接告诉你：

EXPLAIN SELECT tag, COUNT(*) AS c FROM posts
 WHERE created_at > '2026-09-01'
 GROUP BY tag ORDER BY c DESC LIMIT 20;

如果 Extra 里出现 Using temporary，说明需要临时表；出现 Using filesort，说明还要额外排序。两个同时出现是最坏的情况：先把结果塞进临时表，再对临时表排序，等于两次额外开销。

8.0 里还可以用 EXPLAIN FORMAT=JSON 或者 EXPLAIN ANALYZE 看到更精确的信息。EXPLAIN ANALYZE 会实际执行查询并打印每个节点的真实行数，这是判断「临时表里到底装了多少行」的唯一可靠办法：

EXPLAIN ANALYZE
SELECT tag, COUNT(*) FROM posts
 WHERE created_at > '2026-09-01' GROUP BY tag;

输出里会有一个 -> Table scan on <temporary> 或者 -> Group aggregate 的节点，旁边的 actual rows 就是临时表里的行数。如果这个数字是 80 万，那你就别调参数了，直接去优化 WHERE 条件或者加索引。

还有一个容易被忽略的细节：Using temporary 并不总是意味着「落盘」。 优化器只是说「需要一个中间容器」，容器在内存还是磁盘，取决于大小和上面说的四种情形。所以看到 Using temporary 不必紧张，看到 Created_tmp_disk_tables 增长才需要紧张。

把 tmpdir 挪到合适的地方

如果确认有些临时表必然落盘，那能做的就是让落盘这件事便宜一点。默认 tmpdir 指向 /tmp，在不少云主机上 /tmp 是根分区的一部分，或者是一个很小的 tmpfs。这两种情况都很糟：

  • 根分区：临时表写入会跟日志、uploads、备份文件抢同一块盘，IO 竞争直接让全站变慢。
  • tmpfs：临时表会被当成内存使用，等于把磁盘临时表强行变成了「吃内存」，反而更容易触发 OOM。

先确认现在的设置：

SHOW VARIABLES LIKE 'tmpdir';
-- 顺带确认这台机器上 /tmp 的类型
df -hT /tmp
findmnt -no FSTYPE,SIZE,USED /tmp

如果 /tmp 是 tmpfs，强烈建议把 tmpdir 改到一个真实的磁盘目录，比如 /var/lib/mysql-tmp：

mkdir -p /var/lib/mysql-tmp
chown mysql:mysql /var/lib/mysql-tmp
chmod 750 /var/lib/mysql-tmp

# my.cnf
[mysqld]
tmpdir = /var/lib/mysql-tmp

改完要 重启，tmpdir 是只读变量，SET GLOBAL 改不了。这里还要提醒一句：如果你的 /tmp 挂载了 noexec，某些 MySQL 版本的临时文件操作会失败，因此最好把 tmpdir 单独放到一个没有 noexec 的目录。

另外，如果你用的是独立的数据盘，tmpdir 应该和数据文件放在同一块盘上，而不是放在系统盘。原因很简单：临时表的读写是随机 IO，和数据文件的随机 IO 性质类似，放在同一块盘上至少能让文件系统调度器统一排序，分开放反而让两块盘都在做随机 IO。

监控：每天盯一个数字，比每周看一次报表有用

临时表这个问题，事后分析价值有限，因为它高度依赖当下的数据量和查询模式。真正有用的是每天同一个时刻采样一次，看趋势。下面这个小脚本，我把它放进了每日巡检：

#!/bin/bash
# tmpcheck.sh —— 输出一行：磁盘表占比、区间增量、tmpdir 使用量
M="mysql -uroot -pPASS -N -B"

read a b <<< $($M -e "SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_disk_tables'),
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_tables')")
sleep 30
read c d <<< $($M -e "SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_disk_tables'),
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_tables')")

disk_inc=$((c - a)); total_inc=$((d - b))
if [ "$total_inc" -gt 0 ]; then
  pct=$(( disk_inc * 100 / total_inc ))
else
  pct=0
fi
used=$(du -sm "$($M -e 'SELECT @@tmpdir')" 2>/dev/null | cut -f1)
echo "$(date '+%F %T') disk_inc=${disk_inc} total_inc=${total_inc} pct=${pct}% tmpdir_mb=${used}"

三个观察点：

  1. pct 长期高于 25%：说明有一批固定查询在撑爆内存，去 events_statements_summary_by_digest 里按 SUM_CREATED_TMP_DISK_TABLES 排序，第一名的 SQL 就是元凶。
  2. disk_inc 从个位数突然跳到几百：大概率是某个新上线的功能引入了新的聚合查询。
  3. tmpdir_mb 只增不减：正常情况下一张临时表用完就删，如果这个数字持续上涨，说明有连接处于泄漏状态，或者有超大查询在跑。

那个 events_statements_summary_by_digest 的查询写法值得记下来，因为它一次就能定位到具体 SQL 模板：

SELECT DIGEST_TEXT, COUNT_STAR AS cnt,
       SUM_CREATED_TMP_DISK_TABLES AS disk_tmp,
       SUM_CREATED_TMP_TABLES AS all_tmp,
       ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;

注意 DIGEST_TEXT 会把具体的参数值替换成 ?，所以看到的是 SQL 模板，这正好是你想要的——你要改的是那个模板对应的代码位置，而不是某一次具体的调用。

真正该做的优化：把临时表消掉，而不是喂大它

调参数是止血，改查询才是治病。下面五种改法按性价比排序，前三种我自己用过，收益都很明显。

第一种：给 GROUP BY / ORDER BY 的列建合适的索引。 如果 GROUP BY 的列上有一个有序索引，MySQL 可以直接按索引顺序遍历并分组，Using temporary 和 Using filesort 都会消失。这是最彻底的解决方式。反过来，如果 GROUP BY 的列上有索引但被 WHERE 条件废掉了（比如对索引列做了函数运算），那就白搭。

第二种：把 UNION 改成 UNION ALL。 前提是业务上允许重复，或者在应用层去重。零成本，改一行。

第三种：拆开聚合。 对 TEXT / JSON 列做 GROUP BY 是最常见的事故源。正确做法是先只 SELECT 需要的标量列，聚合做完再回头取正文。如果业务必须对正文聚合，那就说明这个查询需求本身需要重新设计表结构——把要聚合的信息单独抽成一列。

第四种：分页时避免 ORDER BY 大表。 像 ORDER BY created_at DESC LIMIT 20 OFFSET 100000 这种深分页，中间结果集是 10 万行，临时表必然落盘。改成游标分页（WHERE created_at < ? ORDER BY created_at DESC LIMIT 20）能直接把中间结果集压到 20 行。

第五种：预聚合。 如果某个统计模块每天被请求上万次，而数据本身一天才变一次，那最好的办法是每天凌晨算一次结果写进一张汇总表，页面直接读汇总表。这看起来是「多了一张表」，实际上把每天上万次聚合变成了一次。

几个我踩过的具体坑

坑一：把 tmp_table_size 设成 1G，然后机器开始 swap。 这不是参数设错了，而是没考虑到「每连接」这个乘数。建议在小内存机器上永远不要超过 128M，超过就该改查询了。

坑二：以为 Created_tmp_disk_tables 是当前值。 它是累计值。我曾经在一个跑了半年的库上看到这个数字是 200 万，以为问题严重，采样一分钟后发现每分钟只新增 2 张，其实完全正常。

坑三：改了 tmpdir 但忘了检查 /var/lib/mysql-tmp 的权限。 MySQL 以 mysql 用户运行，目录属主不对就会在第一次创建临时表时报 Can't create/write to file，而且这个错误在客户端看来是「查询失败」，很难第一时间联想到临时表。

坑四：把 sort_buffer_size 和临时表混为一谈。 这两个是完全独立的机制。sort_buffer_size 影响的是「排序能不能在内存里完成」，超过就会用临时文件排序（Sort_merge_passes 计数）；临时表影响的是「中间结果集放哪」。一个查询可能既有临时表又有文件排序，需要分别调。判断文件排序看 Sort_merge_passes 是否在增长，判断临时表看 Created_tmp_disk_tables。

坑五：在从库上看到磁盘临时表暴涨，却去主库调参数。 从库如果承担了报表查询，那些重聚合都跑在从库上。调参和优化都应该在真正承载负载的那一台做，读从库和主库的参数可以不一样。

一张排查顺序表

最后把整篇的排查顺序压成一张表，出问题时从上往下走，每步都有明确的判据和动作：

步骤看什么判据动作
1Created_tmp_disk_tables 一分钟增量个位数 = 正常；上百 = 有问题继续往下
2磁盘表占比 disk / total> 25% 需要处理，< 10% 忽略继续往下
3events_statements_summary_by_digest 按磁盘临时表排序找出前 3 条 SQL 模板定位到具体页面/代码
4EXPLAIN 看 Using temporary确认确实建临时表看能不能加索引消掉
5查询是否涉及 TEXT/BLOB 列是 = 必然落盘，调参无用改查询，先不取大字段
6tmpdir 的类型与分区tmpfs 或与日志同盘 = 有害迁到数据盘的独立目录
7内存余量与并发数收益 > 风险时才调参两个参数一起调，上限不超 128M

这张表的顺序是有讲究的：先确认有没有问题，再定位到哪条 SQL，再判断能不能消掉，最后才轮到调参数。我见过太多人反过来——一上来就调参数，把 16M 改成 256M，内存涨了，问题还在，只是变得不那么容易观测了。临时表的本质是「查询需要一块中间场地」，场地不够大是结果，不是原因。找到那个需要场地的查询，才是解决问题的起点。

Last modification：October 24th, 2026 at 01:25 pm
© 允许规范转载

相关文章

Leave a Comment