如何通过延迟关联与书签法优化MySQL深度分页查询效率？

本文共计791个文字，预计阅读时间需要4分钟。

由于MySQL在执行`SELECT * FROM t ORDER BY id LIMIT 100000, 20`时，必须先扫描前100000行，再取后面的20行，这种跳跃操作无法利用索引下推，完全依赖服务器的逐行计数。这会导致I/O和CPU消耗增加，性能下降。

常见错误是以为加了 id 上的索引就万事大吉——其实索引能加速排序和定位，但改变不了“必须跳过 N 行”这一执行逻辑。

用延迟关联（Deferred Join）绕过 OFFSET

核心思路：先用覆盖索引快速拿到分页所需的主键，再回表查完整字段。避免在大偏移时扫描大量无关记录。

• 原始低效写法：SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20
• 优化后写法：SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20) AS tmp ON o.id = tmp.id

关键点：子查询只查 id（假设 created_at + id 有联合索引），走索引且不回表；外层仅对 20 个 id 做等值回查，成本可控。

注意：子查询里的 ORDER BY 必须和外层一致，且排序字段+主键要组成最左匹配的复合索引，否则仍可能触发 filesort。

用书签记录法（Keyset Pagination）彻底去掉 OFFSET

适用于用户按固定顺序浏览、且能记住“上一页最后一条”的场景（比如无限滚动）。不再依赖行号，而是用上一页末尾的排序键值作为下一页起点。

• 第一页：SELECT * FROM posts ORDER BY updated_at DESC, id DESC LIMIT 20
• 第二页（假设第一页最后一条是 updated_at='2024-05-01 10:20:30', id=8823）：SELECT * FROM posts WHERE (updated_at, id)

这种写法让 MySQL 直接用索引定位起点，完全跳过前面所有数据。性能几乎不随页码增长而下降。

坑点：必须使用行比较语法（(a,b) ），不能拆成 <code>WHERE updated_at ，否则会漏掉同时间戳不同 ID 的记录；复合索引顺序必须与 <code>ORDER BY 完全一致。

什么时候该选哪种方案？

延迟关联适合已有分页接口、不想改前端逻辑的场景，兼容性好，但仍有少量冗余扫描；书签法性能最优，但要求前端能保存并传递上一页末尾的排序键值，且不支持“跳转到第 1000 页”这类随机访问。

真实项目里最容易被忽略的是排序字段的重复性问题——如果 created_at 大量重复，单靠它做书签会分页错乱，必须引入 id 或其他唯一列补全排序维度，否则 LIMIT 结果不可控。