ORDER BY 是 SQL 中最常用也最容易拖慢查询性能的子句之一。它本身不筛选数据，但会触发排序操作，尤其在大数据量、无合适索引或内存不足时，可能引发磁盘临时文件（tempfile）甚至 OOM。优化核心就三点：让排序走索引、减少排序数据量、控制排序开销。
用覆盖索引避免文件排序（Using filesort）
当 ORDER BY 字段不在 WHERE 条件使用的索引中，或索引顺序与排序需求不匹配时，MySQL/PostgreSQL 就不得不回表+额外排序，即触发 filesort。解决方法是创建符合“WHERE + ORDER BY + SELECT”顺序的联合索引。
- 例如查询 SELECT name, age FROM users WHERE city = 'Beijing' ORDER BY age DESC，应建索引 (city, age)（注意：age 要与 ORDER BY 方向一致，DESC 需显式声明）
- 若还需返回 id，且 id 是主键，可扩展为 (city, age, id)——构成覆盖索引，连回表都省了
- 注意：ORDER BY 中混用 ASC/DESC（如 ORDER BY a ASC, b DESC）在 MySQL 8.0+ 才支持多方向索引；旧版本需统一方向或改写逻辑
限制结果集，用 LIMIT 配合 ORDER BY 提前终止
数据库引擎在遇到 LIMIT 时，可能启用“优先队列排序”（priority queue sort），只维护前 N 条有序数据，大幅降低内存和 CPU 开销。但前提是 LIMIT 必须明确、且不能被隐藏在子查询或分页偏移过大时失效。
- 推荐写法：SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 —— 引擎只需找最新 20 条，不用全表排序
- 避免写法：SELECT * FROM orders ORDER BY created_at DESC LIMIT 10000, 20 —— 偏移量大时仍要跳过前 10000 行，性能陡降；改用游标分页（如 WHERE created_at ）
- PostgreSQL 中可配合 FETCH FIRST 20 ROWS ONLY，语义更清晰，优化器也更易识别
避免在 ORDER BY 中使用表达式或函数
ORDER BY field + 1、UPPER(name)、DATE(created_at) 等写法会强制计算每行值，无法利用索引，必然触发 filesort。即使字段上有函数索引（如 MySQL 5.7+ 的函数索引、PG 的表达式索引），也要确保查询写法与索引定义完全一致。
- 错误示例：ORDER BY YEAR(created_at) → 即使有 INDEX(YEAR(created_at))，MySQL 通常也不走（需检查执行计划）
- 正确做法：提前物化时间维度字段，如加一列 created_year TINYINT UNSIGNED 并建索引，查询改用 ORDER BY created_year
- 字符串排序慎用 COLLATE：不同 collation 可能导致索引失效，如 ORDER BY name COLLATE utf8mb4_0900_as_cs 要求索引也按该规则创建
调优服务器参数与监控排序行为
即使 SQL 写得合理，系统配置不当也会让排序掉到磁盘。关键参数需结合实际负载调整，并持续观察是否真走了索引、是否用了临时表。
- MySQL：增大 sort_buffer_size（线程级，非全局）、read_rnd_buffer_size；监控 Sort_merge_passes（越少越好，突增说明内存不够）
- PostgreSQL：调高 work_mem（影响单个排序操作内存上限），但不宜设过高以防并发多时耗尽内存；用 EXPLAIN (ANALYZE, BUFFERS) 查看是否出现 “External merge” 或 “Disk:”
- 通用技巧：在慢查日志里开启 log_queries_not_using_indexes（MySQL）或 log_min_duration_statement = 0 + 检查 plan 输出，快速定位未走索引的 ORDER BY
