在 MySQL 中优化排序与分页时，核心原则包括：ORDER BY 字段必须符合联合索引的最右连续前缀规则；WHERE 条件字段应位于 ORDER BY 字段左侧，用来锚定局部有序范围；面对深分页时，应尽量使用游标分页，而不是直接依赖 LIMIT OFFSET。
ORDER BY 字段必须是联合索引的最右连续前缀
在 MySQL 中，只有当 ORDER BY 的字段顺序与联合索引的右侧连续部分完全一致时，数据库才能直接利用索引顺序扫描（Using index）完成排序，从而避免额外的 filesort。需要注意的是，不只是字段存在于索引中就可以，字段的排列顺序和连续性同样必须严格匹配。
例如，你创建了索引 idx_user_status_created_at_id（status, created_at, id），那么下面这些 SQL 可以利用索引完成排序：
ORDER BY created_at, idORDER BY idWHERE status = 1 ORDER BY created_at, id
而以下写法则无法有效走索引排序：
ORDER BY status, id（跳过了created_at，不满足连续前缀）ORDER BY created_at DESC, id ASC（排序方向混用，MySQL 8.0 之前不支持这种混合排序索引）WHERE created_at > '2025-01-01' ORDER BY id（created_at属于范围条件，前导列顺序被打断，后续id无法继续继承索引有序性）
LIMIT 分页时，WHERE 条件字段要放在 ORDER BY 字段左边
联合索引本质上是 B+Tree 的有序结构：先按第一列排序，值相同再按第二列排序，依次类推。因此，在 MySQL 分页查询中，WHERE 条件字段最好放在 ORDER BY 字段左侧，这样才能先缩小范围，再在局部有序数据中完成排序。
假设查询语句如下：SELECT id FROM orders WHERE user_id = 123 AND display = 1 ORDER BY sort_by DESC LIMIT 20
比较理想的联合索引设计是：idx_user_id_display_sort_by（user_id, display, sort_by）
原因在于：
user_id = 123能先定位到索引中的一段数据display = 1继续把范围缩小到这段中的一个更小连续区间- 这个区间内部的
sort_by本身就是有序的，MySQL 可以直接按倒序扫描前 20 条记录，无需额外跳过大量数据
如果把 sort_by 放到索引前面，比如建立 idx_sort_by_user_id，那么 WHERE user_id = 123 就无法高效定位，只能进行全索引扫描或回表过滤，排序优化的意义基本就失去了。
深分页（大 OFFSET）必须放弃 LIMIT，改用游标（WHERE + 排序字段值）
像 LIMIT 100000, 20 这种深分页写法，并不是“直接取第 100001 条开始的 20 条”，而是 MySQL 需要先读取 100020 条记录，再丢弃前面的 100000 条。即使相关字段上有索引，这种跳过成本依然存在，性能通常会随着页数增加明显下降。
更高效的做法，是把“第 N 页”转换为“基于上一页最后一条记录继续往后查”的游标分页方式：
- 如果上一页最后一条记录的
sort_by = 98765，那么下一页可以这样写：WHERE user_id = 123 AND display = 1 AND sort_by < 98765 ORDER BY sort_by DESC LIMIT 20 - 必须保证
sort_by是唯一值，或者至少组合唯一（例如(sort_by, id)），否则容易出现数据重复或遗漏 - 第一页仍然可以使用普通
LIMIT，但从第二页开始，建议全部切换为游标分页模式
需要注意的是，游标分页不适合“直接跳转到任意页”的需求，但对于无限滚动、下拉加载、列表持续翻页等常见业务场景非常适用，而且查询性能更稳定，不会随着分页越来越深而持续变差。
覆盖索引 + 延迟关联，是 SELECT * 场景下的保底方案
如果业务场景中必须使用 SELECT *，又希望降低深分页导致的大量回表开销，那么通常可以采用“覆盖索引 + 延迟关联”的方式分两步查询：
- 第一步：先通过覆盖索引只查询主键，例如
SELECT id FROM orders WHERE ... ORDER BY sort_by DESC LIMIT 20（这一步速度快，因为只扫描索引） - 第二步：再根据这些
id到主键聚簇索引中查询完整数据行：SELECT * FROM orders WHERE id IN (123, 456, ...)
这种方案通常被称为“延迟关联”。它的优势在于，把原本昂贵的回表操作从“扫描 10 万条索引并回表 10 万次”，压缩成“扫描 20 条索引并回表 20 次”。实际效果取决于 IN 列表长度以及主键查询效率，但相较于直接执行 SELECT * ... LIMIT 100000, 20，整体性能往往更稳定。
还有一个容易忽视的细节：IN 列表不宜过长（通常建议 ≤ 500），否则可能导致优化器选择较差执行计划，甚至退化为全表扫描。同时要确保主键字段上有索引；在 InnoDB 中，主键本身就是聚簇索引，这一点一般默认成立。
