优化ORDER BY与LIMIT分页性能的核心是利用覆盖索引避免排序和回表，索引需按WHERE等值条件+ORDER BY字段顺序构建，禁用函数、大OFFSET及SELECT *，改用游标分页并验证执行计划无Using filesort。
ORDER BY 和 LIMIT 联合使用时，若缺乏合适索引或写法不当，容易触发全表扫描、临时表排序甚至磁盘排序，导致分页性能急剧下降。核心优化思路是：让数据库能直接利用索引完成排序+取数，避免回表和排序操作。
用覆盖索引消除排序与回表
当 ORDER BY 字段和查询字段都能被同一索引覆盖时，MySQL 可直接按索引顺序读取数据，无需额外排序，也无需回表查主键以外的列。
- 例如查询 SELECT id, name, created_at FROM users ORDER BY created_at DESC LIMIT 20，可建联合索引 (created_at, id, name)（注意：排序字段必须放最左，且方向一致）
- 若还需 WHERE 条件（如 status=1），索引应调整为 (status, created_at, id, name)，把等值条件放前，排序字段紧随其后
- 避免在 ORDER BY 中使用函数或表达式（如 ORDER BY YEAR(created_at)），这会让索引失效
避免 OFFSET 大偏移量的深分页
LIMIT 10000,20 这类写法会让 MySQL 先扫描并跳过前 10000 行，即使有索引，I/O 和 CPU 开销仍很大。
- 改用“游标分页（Cursor-based Pagination）”：记录上一页最后一条的排序字段值，下一页用 WHERE created_at < '2024-05-01' ORDER BY created_at DESC LIMIT 20
- 对高并发深分页场景，可预生成分页映射表，或用 ES 等专用检索引擎承担排序分页压力
- 前端限制最大页码（如只允许到第 200 页），后端直接拒绝超限请求
慎用 SELECT * 配合 ORDER BY + LIMIT
SELECT * 会强制回表获取所有字段，即使索引能支持排序，也会因回表放大 I/O，尤其当表宽大、行数多时更明显。
- 只查真正需要的字段，减少网络传输和内存占用
- 如果业务必须返回全字段，但排序字段有索引，可先用覆盖索引查出主键，再用 IN 子查询或 JOIN 回表——但要注意 IN 列表长度限制及执行计划是否走索引
- 确认执行计划中 Extra 字段不含 Using filesort 或 Using temporary
ORDER BY 多字段时注意索引顺序与方向一致性
ORDER BY a ASC, b DESC 在 MySQL 8.0+ 支持混合方向索引，但低版本仅支持全部同向；索引字段顺序必须严格匹配 ORDER BY 的顺序。
- ORDER BY user_id ASC, score DESC → 索引应为 (user_id, score) 并指定 DESC（8.0+），否则降级为 filesort
- 不要写成 ORDER BY score DESC, user_id ASC，除非索引也是该顺序
- 含 NULL 值时，注意默认排序中 NULL 是最小值（ASC）还是最大值（DESC），必要时用 COALESCE 或 WHERE 过滤
