ORDER BY 和 GROUP BY 会写临时表到磁盘，主因是内存不足（sort_buffer_size 小）、未走索引、字段类型不匹配或结果集过大；关键看 Created_tmp_disk_tables 上涨及执行计划中“Copying to tmp table on disk”。
为什么 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 就没法复用索引。
另一个隐形杀手是 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。
- 覆盖索引优先：把
SELECT中的字段也加进索引末尾，避免回表，减少排序数据体积 -
LIMIT越大越危险——LIMIT 10000, 20要先排10020行，哪怕只取20条，buffer 不够就溢出 - 时间范围查询慎用
ORDER BY id DESC：如果id很大但新数据集中在某几个区间，索引效率反而不如按时间字段建倒序索引
复杂点在于，有些业务场景根本绕不开大数据集排序，比如后台导出全量报表。这时候与其硬扛，不如拆成应用层流式处理，或者加 SQL_BIG_RESULT 提示让优化器提前走磁盘策略，避免撑爆内存。
