MySQL 临时表落盘导致磁盘占用和查询延迟升高的排查实践

适用场景

本文适用于线上 MySQL 出现以下情况：

接口偶发变慢，慢查询集中在 ORDER BY、GROUP BY、DISTINCT、复杂分页或报表 SQL 上。
数据库实例的磁盘使用率短时间上涨，过一段时间又自动回落。
tmpdir 所在分区 IO 使用率升高，甚至出现 No space left on device。
慢查询日志里能看到 Copying to tmp table、Creating sort index、Using temporary、Using filesort 等特征。

这类问题容易被误判为单纯磁盘容量不足。真正需要关注的是：哪些 SQL 触发了内部临时表、为什么临时表从内存转成磁盘、临时目录是否和数据目录争抢 IO。

现象描述

一次线上报表接口在上午高峰期从 300ms 上升到 8s 以上，同时监控显示：

MySQL 主机 iowait 升高。
/var/lib/mysql 所在磁盘空间快速下降。
慢查询数量增加，集中在几个带 GROUP BY 和 ORDER BY 的统计 SQL。
业务重试后连接数也随之上升，进一步放大数据库压力。

登录数据库主机后，能看到 MySQL 临时文件快速创建和删除：

sudo ls -lh /var/lib/mysql/#sql* 2>/dev/null | head
sudo lsof +L1 | grep mysqld | head

如果配置了独立 tmpdir，也要检查该目录：

mysql -NBe "SHOW VARIABLES LIKE 'tmpdir';"
df -h "$(mysql -NBe "SHOW VARIABLES LIKE 'tmpdir';" | awk '{print $2}')"

关键点是不要只看最终磁盘占用。临时表可能在查询结束后被删除，问题发生时需要结合慢查询、状态变量和 IO 监控一起判断。

可能原因

MySQL 内部临时表通常由以下 SQL 模式触发：

GROUP BY 和 ORDER BY 字段不一致，无法直接利用同一个索引完成排序。
DISTINCT、UNION、派生表、子查询或窗口函数需要中间结果集。
排序字段、分组字段或返回列太多，导致中间结果超过内存临时表限制。
字段包含较大的 TEXT、BLOB、JSON，更容易落盘。
缺少合适复合索引，导致扫描大量行后再排序或分组。
tmp_table_size 和 max_heap_table_size 设置过小，内存临时表很快转成磁盘临时表。
tmpdir 与数据目录共用慢盘，临时表 IO 和 InnoDB 数据读写互相影响。

需要注意，简单调大内存参数并不总是正确方案。如果 SQL 本身扫描上百万行，调大参数只会让单个查询占用更多内存，可能把磁盘问题变成内存问题。

排查思路

1. 确认临时表是否明显增加

先看 MySQL 全局状态变量：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

重点字段：

Created_tmp_tables       内部临时表总数
Created_tmp_disk_tables  落到磁盘的内部临时表数量
Created_tmp_files        创建的临时文件数量

建议连续采样两次，计算增量：

mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
sleep 60
mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"

如果 Created_tmp_disk_tables 在高峰期持续快速增长，说明落盘临时表是重点排查方向。

2. 找出正在执行的可疑 SQL

查看当前执行中的 SQL：

SHOW FULL PROCESSLIST;

关注 State 字段：

Creating tmp table
Copying to tmp table
Creating sort index
Sending data

如果某条 SQL 长时间停在这些状态，优先拿到完整 SQL、执行用户、来源主机和运行时间。

也可以从 performance_schema 查看历史语句摘要：

SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
  SUM_CREATED_TMP_TABLES AS tmp_tables,
  SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,
  SUM_SORT_ROWS AS sort_rows,
  SUM_ROWS_EXAMINED AS rows_examined
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;

关键字段怎么看：

disk_tmp_tables 越高，越说明该类 SQL 经常把临时表写到磁盘。
sort_rows 很高，通常和大范围排序有关。
rows_examined 远大于返回行数，说明索引或过滤条件可能不合理。

3. 用 EXPLAIN 验证执行计划

对可疑 SQL 执行：

EXPLAIN SELECT ...;

重点看 Extra：

Using temporary
Using filesort
Using where
Using index

Using temporary 表示需要内部临时表，Using filesort 表示需要额外排序。它们不一定必然有问题，但如果同时出现在大结果集 SQL 上，就很容易引发磁盘临时表和 IO 抖动。

MySQL 8.0 可以使用：

EXPLAIN ANALYZE SELECT ...;

它会显示实际执行耗时和行数，更适合判断优化是否有效。生产环境执行前要确认 SQL 对业务影响，优先在只读副本或低峰期验证。

定位示例

假设慢查询如下：

SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM orders
WHERE created_at >= '2026-07-01'
  AND status = 'paid'
GROUP BY user_id
ORDER BY total_amount DESC
LIMIT 100;

慢查询日志显示扫描行数很高：

Query_time: 8.421
Rows_examined: 1849320
Rows_sent: 100

执行计划：

EXPLAIN SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
WHERE created_at >= '2026-07-01'
  AND status = 'paid'
GROUP BY user_id
ORDER BY total_amount DESC
LIMIT 100;

可能看到：

type: range
key: idx_created_at
rows: 1800000
Extra: Using index condition; Using where; Using temporary; Using filesort

这说明 MySQL 先按时间范围扫描大量订单，再按 user_id 聚合，最后按计算出来的 total_amount 排序。因为 total_amount 是聚合结果，无法直接通过普通索引完成排序，中间结果较大时就会落盘。

修复方案

方案一：补齐过滤条件索引

如果查询经常按 status 和 created_at 过滤，可以先减少扫描行数：

ALTER TABLE orders
  ADD INDEX idx_status_created_user (status, created_at, user_id);

验证重点：

EXPLAIN SELECT ...;

优化后至少应该看到 rows 明显下降，key 使用新索引。即使仍然存在 Using temporary，扫描行数减少后临时表规模也会下降。

方案二：把实时聚合改成预聚合

对于高频报表，不建议每次请求都扫描订单明细。可以维护按天或按小时的汇总表：

CREATE TABLE order_user_daily_stats (
  stat_date date NOT NULL,
  user_id bigint NOT NULL,
  paid_order_count int NOT NULL DEFAULT 0,
  paid_total_amount decimal(18,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (stat_date, user_id),
  KEY idx_stat_amount (stat_date, paid_total_amount)
);

查询改为：

SELECT
  user_id,
  SUM(paid_order_count) AS order_count,
  SUM(paid_total_amount) AS total_amount
FROM order_user_daily_stats
WHERE stat_date >= '2026-07-01'
GROUP BY user_id
ORDER BY total_amount DESC
LIMIT 100;

预聚合不能完全消除排序，但能把扫描规模从订单明细降低到用户维度统计，通常收益更稳定。

方案三：拆分大查询或限制查询窗口

如果业务允许，可以限制报表查询时间范围：

WHERE created_at >= NOW() - INTERVAL 7 DAY

或者将大区间拆成多个小区间离线计算，避免一个请求内生成巨大的临时表。对后台导出、运营报表尤其适合。

方案四：调整临时目录和容量

确认 tmpdir：

SHOW VARIABLES LIKE 'tmpdir';

如果临时目录和数据目录共用同一块小磁盘，可以将临时目录迁移到容量更大、IO 更稳定的独立磁盘，例如：

[mysqld]
tmpdir=/data/mysqltmp

目录权限示例：

sudo mkdir -p /data/mysqltmp
sudo chown mysql:mysql /data/mysqltmp
sudo chmod 750 /data/mysqltmp

修改后需要在维护窗口重启 MySQL：

sudo systemctl restart mysqld

方案五：谨慎调整内存临时表参数

查看当前值：

SHOW VARIABLES WHERE Variable_name IN ('tmp_table_size', 'max_heap_table_size');

内存临时表上限取两者较小值。如果确实只是略微超过默认值，可以适当调整：

[mysqld]
tmp_table_size=128M
max_heap_table_size=128M

注意事项：

这是单个临时表的上限，不是全局总上限。
并发查询多时，过大配置可能导致内存压力。
对包含 TEXT、BLOB 的中间结果，调参未必能避免落盘。

应急处理

线上已经出现磁盘告警时，建议按以下顺序处理：

找出正在制造大量临时表的 SQL。

SHOW FULL PROCESSLIST;

和业务确认后终止明显异常的查询。

KILL 12345;

其中 12345 是 SHOW FULL PROCESSLIST 里的 Id。

临时关闭或降级触发问题的报表、导出、批处理任务。
扩容或迁移 tmpdir 所在分区。
再做索引、SQL 或预聚合优化，避免问题复发。

不要直接删除 MySQL 正在使用的 #sql 临时文件。这样可能导致查询异常，甚至引发更难定位的问题。应通过终止查询、释放空间、扩容或调整 tmpdir 来处理。

预防措施

为核心报表 SQL 建立慢查询基线，记录正常情况下的 Rows_examined、耗时和执行计划。
对 Created_tmp_disk_tables、磁盘使用率、磁盘 IO 延迟设置告警。
新增后台导出和统计接口前，必须评审 EXPLAIN，避免大范围实时排序和聚合。
对高频排行榜、汇总报表优先使用预聚合表或缓存。
将 MySQL tmpdir 放在容量和 IO 都可控的分区，不要和系统根分区混用。
大促、月初报表、数据补算前，提前评估临时表空间和查询并发。

总结

MySQL 临时表落盘不是单一配置问题，通常是 SQL 形态、索引、数据规模、并发和磁盘布局共同作用的结果。排查时先用 Created_tmp_disk_tables、慢查询日志和 performance_schema 确认问题，再通过 EXPLAIN 判断是否存在大范围扫描、排序和聚合。修复时优先减少扫描行数、改造实时聚合、拆分大查询，并把 tmpdir 放到可控磁盘上。调大 tmp_table_size 只能作为辅助手段，不能替代 SQL 和架构层面的治理。