为什么MySQL执行过程中会产生Copying to tmp table on disk？

“Copying to tmp table on disk”本质是内存临时表溢出，即当中间结果超出tmp_table_size与max_heap_table_size的较小值时，MySQL被迫将MEMORY引擎临时表转为磁盘表；该状态无法通过EXPLAIN预判，需依赖Created_tmp_disk_tables增量或SHOW PROFILE确认。

“Copying to tmp table on disk”本质是内存临时表溢出

这不是 MySQL 主动选择写磁盘，而是它被迫落盘：当查询中间结果（比如 GROUP BY 后的聚合行、ORDER BY 排序数据、多表 JOIN 后的笛卡尔积）超出 tmp_table_size 与 max_heap_table_size 的较小值时，MySQL 会把原本在 MEMORY 引擎中运行的临时表，转成 MyISAM 或 InnoDB 类型的磁盘临时表。这个转换过程就显示为 Copying to tmp table on disk。

关键点在于：

• EXPLAIN 看不到这个状态——它只预估计划，不模拟内存压力
• 哪怕只返回 10 行，只要 SELECT 中包含大字段（如 TEXT、BLOB 或超长 VARCHAR），定义长度就会被按最大值计入内存估算，极易触发落盘
• 每个连接独占一份 tmp_table_size 内存，高并发下总开销翻倍，实际可用远低于配置值

哪些 SQL 模式最容易触发磁盘临时表

不是所有 GROUP BY 或 ORDER BY 都危险，但以下组合几乎必落盘：

• 没索引的 ORDER BY 字段 + LIMIT 偏移量大（如 LIMIT 100000, 20）
• GROUP BY 字段类型和 WHERE 条件不一致（如 WHERE user_id = '123' 对比 BIGINT 列），导致隐式转换、索引失效
• 多表 JOIN 后再 GROUP BY 或 ORDER BY，且关联字段无联合索引覆盖（例如 ON t.a = r.a WHERE r.status = 1 ORDER BY t.updated_at，缺 r(a, status) 和 t(updated_at)）
• SELECT * + ORDER BY —— 大字段全拖进排序，内存瞬间爆掉

怎么确认真是磁盘临时表在拖慢查询

别只看 SHOW PROCESSLIST 里那个卡住的状态，它可能只是“结果”。更准的方式是抓实时指标：

• 执行 SHOW GLOBAL STATUS LIKE 'Created_tmp%'，重点盯 Created_tmp_disk_tables 是否在查询前后明显上涨
• 用 SET profiling = 1 开启会话分析，跑完查 SHOW PROFILE FOR QUERY N，确认 Copying to tmp table on disk 阶段耗时占比是否 > 50%
• 配合 SELECT * FROM information_schema.PROCESSLIST WHERE STATE = 'Copying to tmp table on disk'，记下 ID 和 INFO，反查真实 SQL

调大 tmp_table_size 是最懒的解法

设成 512M 甚至 1G 确实能压住症状，但代价不小：

• 服务器内存够，不代表 MySQL 能稳住——max_heap_table_size 还管着 MEMORY 表上限，设太大可能让其他功能异常
• OOM killer 可能在半夜干掉 mysqld，尤其当多个慢查询并发触发时
• 根本问题没解决：一条没索引的 JOIN 语句，调到 2G 也还是会落盘，只是晚一点
• 真正有效的顺序是：先加索引（覆盖 JOIN/WHERE/GROUP BY/ORDER BY 字段）→ 再精简 SELECT 字段 → 最后才考虑适度调参

最常被忽略的一点：tmp_table_size 和 max_heap_table_size 必须设为相等才安全。因为生效的是两者中的较小值，单独调一个等于白调。