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），那以下查询能走索引排序：
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 的有序结构：先按第一列排序，相同再按第二列，以此类推。所以 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 - 必须确保
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 主键就是聚簇索引，这点默认满足）。
