如何排查MySQL中临时表创建过大导致磁盘报警的问题并利用sys库定位SQL？-mysql教程-PHP中文网

临时表写磁盘而非内存，是因为mysql取tmp_table_size与max_heap_table_size的较小值作为内存阈值，超限即强制落盘；含text/blob、无索引排序或聚合、字段定义过宽等均会触发该机制。

如何排查mysql中临时表创建过大导致磁盘报警的问题并利用sys库定位sql？

为什么临时表会写到磁盘而不是内存？

MySQL 默认用 tmp_table_size 和 max_heap_table_size 中的较小值，来限制内存中临时表的大小。一旦 SQL 生成的临时结果集超过这个阈值，就会自动落盘到 /tmp（或 tmpdir 指定路径），使用 MyISAM 或 InnoDB 引擎创建磁盘临时表——这正是磁盘空间突增的直接原因。

常见触发场景包括：ORDER BY、GROUP BY、DISTINCT、子查询中间结果、多表 JOIN 未走索引等。尤其当字段含 TEXT/BLOB 或排序字段无索引时，极易突破内存上限。

tmp_table_size 和 max_heap_table_size 值过小（如默认 16MB）是多数生产环境的共性问题
执行计划中出现 Using temporary 且同时带 Using filesort，基本可判定会落盘
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables' 持续增长，说明磁盘临时表高频产生

用 sys.schema_table_statistics_with_buffer 快速定位“造表大户”

sys 库中的 schema_table_statistics_with_buffer 视图能关联查询语句与临时表开销，比单纯看 slow_log 更直接。它基于 performance_schema 的事件聚合，包含 tmp_tables 和 tmp_disk_tables 计数，且按 query 归类。

执行以下查询可找出近期最“浪费”的 SQL：

SELECT query, tmp_tables, tmp_disk_tables, exec_count
FROM sys.schema_table_statistics_with_buffer
WHERE tmp_disk_tables > 0
ORDER BY tmp_disk_tables DESC
LIMIT 5;

注意：该视图依赖 performance_schema 开启，且需确保以下配置已启用：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

performance_schema = ON
events_statements_history_long 和 events_stages_history_long 保留足够长（默认 10000 行，建议调大）
若结果为空，先确认是否采集了足够时间的负载（冷启动后需等待一段时间）

结合 performance_schema 找出具体执行上下文

sys.schema_table_statistics_with_buffer 给出的是归一化后的 query digest，丢失了参数和具体库表信息。要还原真实语句，得查 performance_schema.events_statements_history_long：

SELECT SQL_TEXT, DIGEST_TEXT, CURRENT_SCHEMA, TIMER_WAIT
FROM performance_schema.events_statements_history_long
WHERE DIGEST = '你从上一步拿到的 DIGEST 值'
ORDER BY TIMER_START DESC
LIMIT 3;

关键点：

DIGEST 是哈希值，不是原始 SQL，必须从上一步视图里取对应字段
TIMER_WAIT 单位是皮秒（ps），除以 10^12 得秒，可用于判断是否真慢
若 SQL_TEXT 显示为 NULL，说明该语句被截断或未开启 performance_schema.setup_actors 的采集权限

临时表过大但无法改 SQL 怎么办？

有些业务 SQL 是 ORM 自动生成或第三方系统下发的，短期内无法重构。此时优先控制落盘行为本身：

临时调高 tmp_table_size 和 max_heap_table_size（例如设为 256M），但需评估内存压力，避免 OOM
把 tmpdir 指向独立 SSD 分区，避免影响系统盘或数据盘 IO
设置 internal_tmp_mem_storage_engine = TEMPORARY（8.0.16+），强制内存临时表用 TempTable 引擎，比 MEMORY 更省空间
监控 Created_tmp_tables 和 Created_tmp_disk_tables 的比值，理想应 > 90%，低于 70% 就值得干预