确认“Copying to tmp table on disk”拖慢查询需交叉验证:执行前后比对Created_tmp_disk_tables是否增长,或启用SET profiling = 1后查SHOW PROFILE中该阶段耗时占比超50%;EXPLAIN无法预判此状态。

怎么确认真是“Copying to tmp table on disk”在拖慢查询
只看 SHOW PROCESSLIST 里状态是 Copying to tmp table 不够——它可能是内存表,也可能是磁盘表。真正要命的是后者。
必须用两个指标交叉验证:
-
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables':执行查询前后对比,值明显上涨就说明落盘了 -
SET profiling = 1后跑目标 SQL,再SHOW PROFILE FOR QUERY N,盯住Copying to tmp table on disk阶段的耗时占比。超过 50%,基本就是瓶颈所在
注意:EXPLAIN 看不到这个状态,它不模拟内存压力,只做逻辑计划预估。
为什么调大 tmp_table_size 没用,甚至更糟
根本原因是 MySQL 实际生效的内存临时表上限,取的是 tmp_table_size 和 max_heap_table_size 的较小值。你设了 tmp_table_size = 1G,但 max_heap_table_size 还是默认 16MB,那所有临时表超 16MB 就强制落盘。
更危险的是:落盘目标(ibtmp1 或 /tmp)往往没空间限制,一写满就卡死 mysqld。
检查和修复方式:
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 查真实值:
SELECT @@tmp_table_size, @@max_heap_table_size - 必须在
my.cnf中同时显式配置两行:tmp_table_size = 256M和max_heap_table_size = 256M - 重启生效;
SET GLOBAL不持久,5.7.20 以下版本不支持SET PERSIST - 别超物理内存 20%:32GB 机器设到 6GB 是高危线
哪些 SQL 写法会绕过内存临时表,直接建磁盘表
就算把两个参数都调到 1G,只要满足下面任一条件,MySQL 就跳过 MEMORY 引擎,直奔磁盘:
- 查询里含
TEXT、BLOB字段(MEMORY引擎不支持) -
ORDER BY或GROUP BY用了函数或表达式,比如UPPER(name)、JSON_EXTRACT(data, '$.id') - 排序字段类型不匹配,例如
WHERE status = '1'对比INT列,触发隐式转换 - 显式指定
ENGINE=MyISAM或ENGINE=InnoDB创建临时表(少见但存在)
这类问题无法靠调参解决,必须重写 SQL 或加函数索引。比如把 GROUP BY UPPER(name) 改成应用层处理,或 MySQL 5.7+ 上建生成列 + 索引。
索引怎么补,才能让 MySQL 不建临时表
核心原则:让 JOIN、WHERE、GROUP BY、ORDER BY 所涉及的字段,在单次索引扫描中就能完成过滤、排序、去重——避免中间结果集膨胀。
典型场景举例:
-
SELECT * FROM t LEFT JOIN r ON t.order_no = r.order_no WHERE r.status = 1 ORDER BY t.gmt_modified DESC→ 至少需要r(order_no, status)和t(gmt_modified)索引 -
SELECT COUNT(*) FROM user WHERE age > 25 GROUP BY gender→ 单列idx_age和idx_gender不够,应建联合索引idx_age_gender (age, gender) - 带
LIMIT的分页慎用OFFSET:大偏移量(如LIMIT 100000, 20)必然拉全量再截断,改用游标式(WHERE id > last_id ORDER BY id LIMIT 20)
最后提醒一句:EXPLAIN 中出现 Using temporary + Using filesort 是强信号,说明优化器已放弃索引有序性——这时候补索引,比调任何内存参数都管用。

















