MySQL查询出现临时表磁盘溢出_优化排序与聚合语句-mysql教程-PHP中文网

order by 和 group by 会写临时表到磁盘，主因是内存不足（sort_buffer_size 小）、未走索引、字段类型不匹配或结果集过大；关键看 created_tmp_disk_tables 上涨及执行计划中“copying to tmp table on disk”。

mysql查询出现临时表磁盘溢出_优化排序与聚合语句

为什么 `ORDER BY` 和 `GROUP BY` 会写临时表到磁盘

MySQL 在执行排序或聚合时，如果内存不够装下中间结果，就会把临时数据写到磁盘（/tmp 或 tmpdir），这比内存操作慢几十倍。关键不是“用了临时表”，而是“写了磁盘”——SHOW STATUS LIKE 'Created_tmp_disk_tables' 持续上涨就是信号。

常见诱因：没走索引的 ORDER BY、GROUP BY 字段类型不匹配（比如 VARCHAR 列用 INT 值比较）、sort_buffer_size 太小、或者结果集本身太大（比如分页查第10万页）。

ORDER BY 必须和 WHERE 条件共用同一索引的最左前缀，否则大概率落盘
GROUP BY 后字段若不在索引中，或含函数（如 DATE(created_at)），也会强制走磁盘临时表
sort_buffer_size 是每个连接独占的，设太大反而导致内存碎片或被系统 kill，建议单实例不超过 4MB

怎么快速定位哪条语句在刷磁盘临时表

别靠猜。打开慢日志并记录临时表信息：SET GLOBAL slow_query_log = ON; SET GLOBAL log_queries_not_using_indexes = ON;，再加 long_query_time = 0 抓全量。重点看日志里带 Using temporary; Using filesort 的行。

更直接的方法是执行前开 profiling：SET profiling = 1;，跑完 SHOW PROFILES; 找耗时长的 query id，再 SHOW PROFILE FOR QUERY N; 看 Copying to tmp table on disk 这一行是否出现、耗时占比多少。

注意 Using temporary 不一定坏——内存临时表没问题，关键是看有没有 on disk
如果 EXPLAIN 显示 type=ALL 或 key=NULL，基本就是索引没生效，先别调 buffer
某些 ORM 自动生成的 SELECT * + ORDER BY，实际只用几列，却把大字段（如 TEXT）也拖进排序，极易溢出

`GROUP BY` 优化：避免隐式转换和冗余字段

最常踩的坑是字段类型不一致导致索引失效。比如 user_id 是 BIGINT，但 WHERE user_id = '123' 传了字符串，MySQL 会转成函数式扫描，GROUP BY user_id 就没法复用索引。

MySQL

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

下载

另一个隐形杀手是 SELECT 里写了没出现在 GROUP BY 中的非聚合字段（SQL mode 开了 ONLY_FULL_GROUP_BY 会报错，关了则行为不可控，还可能让优化器放弃索引）。

检查 GROUP BY 字段是否已建索引，且顺序和查询中一致（如 GROUP BY a, b 要建 (a,b) 联合索引）
避免 SELECT name, COUNT(*) FROM users GROUP BY dept_id 这种写法——name 未聚合也未分组，MySQL 可能随机取值，还强制走临时表
用 GROUP BY ... WITH ROLLUP 时，如果结果行数多，同样容易落盘，不如应用层聚合

排序语句加索引的实操边界

给 ORDER BY 加索引不是“有就行”，得看查询模式。比如 SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20，建 (status, created_at) 索引才有效；只建 (created_at) 没用，因为 WHERE 条件没命中索引开头。

还要注意方向一致性：ORDER BY a ASC, b DESC 在 MySQL 8.0+ 才支持混合方向索引，老版本必须全 ASC 或全 DESC，否则仍会 filesort。