如何通过延迟关联与书签法优化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。