MySQL ORDER BY 和 GROUP BY 如何优化？

简化版

ORDER BY 和 GROUP BY 优化的关键是让过滤、排序、分组尽量利用同一个联合索引，减少额外排序和临时表。索引列顺序、排序方向、范围条件位置、返回行数都会影响优化效果，不能只看到 Using filesort 就机械加索引。

详细版

优化要点：

where 等值过滤列放联合索引前面，排序或分组列接在后面。
排序字段顺序要和索引顺序匹配。
混合升降序要结合 MySQL 版本和索引定义验证。
范围条件可能影响后续排序列的利用。
limit 可以降低排序成本，但深分页仍然慢。
GROUP BY 如果不能走索引，可能使用临时表。
减少返回列和参与排序的数据量。

例子：

where user_id = ?
order by create_time desc
limit 20

可以考虑 (user_id, create_time)，让同一个用户的数据按时间有序读取。

完整版教学

一、排序优化要利用索引有序性

B+ 树索引天然有序。如果查询结果刚好可以按索引顺序读出，MySQL 就可以减少额外排序。

例如索引 (user_id, create_time)：

select *
from orders
where user_id = ?
order by create_time desc
limit 20;

当 user_id 是等值条件时，同一个用户范围内的记录按 create_time 有序，数据库可以沿索引顺序取数据。

这里的关键是“同一个用户范围内”。如果没有 where user_id = ?，索引 (user_id, create_time) 的第一排序键是 user_id，全局并不是按 create_time 排序。联合索引是否能服务排序，取决于前面的列是否被等值条件固定住，后面的排序列是否保持连续顺序。

索引 (user_id, create_time):
user=1: t1, t2, t3
user=2: t1, t2, t3

固定 user_id 后 create_time 有序；
不固定 user_id 时 create_time 不是全局有序。

二、为什么有索引仍可能 filesort

Using filesort 表示 MySQL 需要额外排序算法，不等于一定写磁盘文件。出现它的原因可能是：

排序列不符合联合索引顺序；
where 中范围条件破坏后续有序性；
多表 JOIN 后排序列来自非驱动路径；
排序方向和索引不匹配；
优化器认为走排序比走索引更划算。

所以看到 Using filesort 要分析成本，而不是无脑消灭。有些小结果集排序很便宜。

比如查询只返回 30 行，内存中额外排序可能很轻；但查询返回 30 万行并发执行，排序会占 CPU、内存甚至临时文件。Using filesort 是风险信号，不是绝对错误。优化重点是判断排序输入规模，以及是否能通过索引顺序、提前过滤、减少返回列降低成本。

场景	filesort 风险
过滤后几十行	通常可接受
过滤后几十万行	高风险
排序列很宽	内存和临时空间压力大
深分页排序	需要处理大量被丢弃记录

记忆钩子：排序优化不是消灭 Using filesort 四个字，而是减少参与排序的数据量，最好让索引天然吐出有序结果。

三、GROUP BY 和索引的关系

GROUP BY 需要把相同分组键的数据聚到一起。如果分组列本身按索引有序，数据库可以顺着索引处理，减少临时表和排序压力。

例如：

select status, count(*)
from orders
where user_id = ?
group by status;

索引 (user_id, status) 可以先定位用户，再按状态有序分组。这样比扫描用户所有订单后再额外分组更稳定。

GROUP BY 的成本来自“把相同分组放在一起并聚合”。如果索引顺序刚好是 (user_id,status)，同一个用户下相同 status 的记录相邻，聚合可以顺序进行；如果没有合适索引，MySQL 可能要使用临时表保存分组状态。分组列数量越多、输入行数越大、聚合表达式越复杂，临时表风险越高。

四、排序分页要一起看

order by ... limit 20 通常比全量排序便宜，因为只需要找到前 20 条。但如果是：

order by create_time desc
limit 1000000, 20

仍然会遇到深分页问题。此时要考虑游标分页或延迟关联，而不是只优化排序索引。

limit 对浅分页很友好，对深分页不友好。order by create_time desc limit 20 可以用索引取前 20 条；limit 1000000,20 即使走索引，也要跳过前 100 万条。排序、分页、索引必须一起分析，不能只说“给 order by 字段加索引”。

五、联合索引如何同时服务 WHERE、ORDER BY、GROUP BY

一个常见原则是：等值过滤列在前，排序或分组列接在后面。例如：

select status, count(*)
from orders
where tenant_id = ?
  and create_time >= ?
group by status
order by status;

这个 SQL 里既有时间范围，又有 status 分组排序。索引设计要结合真实目标：如果租户过滤后数据仍很多，而时间范围是主要裁剪条件，可能需要 (tenant_id, create_time, status)；如果按租户状态聚合是高频固定报表，可能要考虑汇总表或 (tenant_id,status,create_time)。没有脱离业务的唯一正确索引，必须结合过滤强度和结果规模判断。

六、怎么验证排序分组优化

Explain 中重点看 Extra 是否出现 Using filesort、Using temporary，再看 rows 是否可控。优化后不要只看 Extra 变漂亮，还要比较真实耗时和资源消耗。有时为了消灭 filesort 建了很宽索引，写入变慢、缓存变差，总体反而不划算。

验证路径：
where 是否先缩小范围
 -> order/group 字段是否接在左前缀后
 -> rows 是否足够小
 -> filesort/temporary 是否仍可接受

七、常见误区与追问

误区：Using filesort 一定代表磁盘排序。 它表示额外排序算法，是否落盘取决于数据量、内存和执行过程。
误区：给排序列单独建索引就一定能优化 ORDER BY。 排序能否用索引还取决于 where 前缀、联合索引顺序、排序方向和返回规模。
误区：范围条件之后的排序列还能完整利用索引顺序。 范围扫描可能破坏后续列的全局有序性，需要结合具体 SQL 和执行计划判断。
误区：GROUP BY 数据量很大也能单靠索引解决。 索引能减少排序或临时表压力，但大规模聚合可能更适合汇总表或离线分析。
追问：为什么 (user_id,create_time) 不能直接服务全局 order by create_time？ 因为索引首先按 user_id 排序，只有固定 user_id 后，create_time 才在局部范围内有序。
追问：看到 Using temporary 怎么办？ 先看输入行数和分组规模，再评估联合索引、提前过滤、减少列、改写 SQL 或使用汇总表。

八、加强记忆

ORDER BY 和 GROUP BY 优化靠索引有序性，但前提是过滤、排序、分组能沿同一条联合索引路径连续使用。等值过滤列负责固定左前缀，排序或分组列接在后面才容易发挥作用；范围条件、混合排序方向、JOIN 顺序和深分页都会改变成本。看到 Using filesort 或 Using temporary，先评估输入行数和真实耗时，再决定建索引、改 SQL、限制结果集还是做汇总。

简化版

详细版