延迟关联先用覆盖索引查主键再回表，主键游标用上页末记录值替代offset实现高效分页，配合业务侧限制深度分页与提供搜索替代。
延迟关联：用覆盖索引减少回表开销
当大表分页查询涉及多字段、且 ORDER BY 字段与 WHERE 条件字段未全部落在同一索引中时，数据库常需回表获取完整行数据，导致 I/O 激增。延迟关联的核心思路是：先通过最小必要字段（如主键）快速定位目标页的 ID 集合，再用这些 ID 回原表精准拉取所需列。
例如，对 orders 表按 created_at 分页查用户订单详情：
SELECT o.id, o.user_id, o.amount, o.status, u.username FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'paid' ORDER BY o.created_at DESC LIMIT 20 OFFSET 10000;
若 orders(status, created_at, id) 有联合索引，可改写为：
SELECT o.id, o.user_id, o.amount, o.status, u.username FROM ( SELECT id FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20 OFFSET 10000 ) t JOIN orders o ON t.id = o.id JOIN users u ON o.user_id = u.id;
子查询只走索引，不回表；外层 JOIN 才加载实际数据，大幅降低磁盘扫描量。
主键游标：用 where + order by 替代 offset
OFFSET 越大，数据库仍需扫描前 N 行，性能线性退化。主键游标（也称“键集分页”）利用上一页最后一条记录的主键值作为下一页起点，跳过全量偏移计算。
要求：排序字段必须包含主键（或至少有唯一性保障），推荐组合如 ORDER BY created_at DESC, id DESC。
第一页（取前 20 条）：
SELECT id, user_id, amount, status FROM orders WHERE status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20;
假设第 20 条的 (created_at, id) 是 ('2024-05-10 14:22:03', 88765)，则第二页查询为：
SELECT id, user_id, amount, status
FROM orders
WHERE status = 'paid'
AND (created_at < '2024-05-10 14:22:03'
OR (created_at = '2024-05-10 14:22:03' AND id < 88765))
ORDER BY created_at DESC, id DESC
LIMIT 20;
这种写法让数据库直接定位起点，避免跳过大量中间行，响应时间稳定在毫秒级。
索引设计必须匹配分页路径
延迟关联和主键游标能否生效，高度依赖索引是否覆盖查询路径。关键原则：
- WHERE 条件字段必须前置在索引中，且顺序与查询条件一致（等值 > 范围 > ORDER BY）
- ORDER BY 字段应紧随 WHERE 字段之后，含方向一致性（如都 DESC）
- 主键游标需确保排序组合具备唯一性，否则可能漏行或重复——可在索引末尾显式添加主键
- 覆盖查询所需字段可进一步避免回表，例如建立 (status, created_at, id, user_id, amount, status) 索引用于子查询
业务侧配合：限制深度分页与提供搜索替代
技术优化不能解决所有问题。真实场景中，用户极少真正翻到第 500 页。建议：
- 前端限制最大页码（如仅允许到第 100 页），或禁用“跳转到指定页”输入框
- 对长列表提供时间范围筛选、状态筛选、关键词搜索，把“翻页”转化为“缩小结果集”
- 对后台导出类需求，改用游标分批拉取 + 流式处理，而非单次大 offset 查询
- 监控慢查询日志，识别高频大 offset 场景，针对性推动产品逻辑调整
