如何优化深分页场景下的回表代价_延迟关联与主键游标分页

OFFSET 越大查询越慢因MySQL需扫描并丢弃前NM行深分页时即使走索引也要回表读取百万级主键再判断条件本质是“假走索引、真全扫”。为什么 OFFSET 越大查询越慢MySQL 的 OFFSET 不是跳过前 N 行再取数据而是让引擎先扫描并丢弃前 NM 行M 是 LIMIT 值再返回结果。尤其在深分页比如 OFFSET 1000000时即使有索引也要回表读取 100 万行的主键或聚簇索引记录再逐条判断是否满足 WHERE 条件——这本质是「假走索引、真全扫」。真实场景用户翻到第 5000 页每页 20 条 → OFFSET 99980EXPLAIN 显示 rows 高达百万级但 key 显示用了索引误以为高效 回表代价来自二级索引查到主键后再根据主键去聚簇索引捞完整行——深分页放大了这个随机 IO 即使加了覆盖索引只要 SELECT 中包含未被索引覆盖的字段仍会触发回表 延迟关联Deferred Join怎么写才有效核心思路用子查询先拿到精准的主键集合只走索引、不回表再用这些主键 JOIN 原表取完整字段。关键是子查询必须只 SELECT 主键且外层 JOIN 不能破坏驱动表顺序。子查询里禁止出现 SELECT * 或非主键字段否则优化器可能放弃延迟关联策略 外层 JOIN 必须用 INNER JOIN且主键字段要显式出现在 ON 条件中例如SELECT t.* FROM (SELECT id FROM article WHERE status1 ORDER BY id LIMIT 100000, 20) AS tmp JOIN article AS t ON t.id tmp.id 如果排序字段不是主键比如按 created_at 分页子查询的 ORDER BY 和 LIMIT 必须基于同一索引否则可能索引失效 注意 MySQL 8.0 对这种写法优化更好5.7 下需确认执行计划中子查询确实走了 index 类型而非 ALL 主键游标分页为什么比 OFFSET 更稳游标分页不依赖偏移量而是用上一页最后一条记录的主键值作为下一页起点把「跳过 N 行」变成「查找大于 X 的前 M 行」彻底规避 OFFSET 的扫描成本。必须确保排序字段有唯一性约束推荐直接用主键否则 WHERE id ? ORDER BY id LIMIT 20 可能漏数据或重复 前端必须保存上一页末尾的 id 值不能靠页码计算用户跳页如从第 1 页直跳第 100 页无法支持这是设计取舍 如果业务允许用 WHERE (created_at, id) (?, ?) 处理时间相同的情况避免因时间重复导致分页错位 游标值不能为 NULL否则 比较失效若首条记录 id 为 1下一页参数就是 1不是 0 哪些情况延迟关联或游标都救不了当排序字段和过滤条件无法共用一个高效索引时两种方案都会退化——比如 WHERE category_id 123 ORDER BY view_count DESC而 view_count 更新频繁、无有效索引。覆盖索引建不出来比如 view_count 是表达式或函数结果延迟关联的子查询仍要回表 数据实时写入频繁游标分页会出现「新插入记录挤在中间」导致漏显示幻读需配合业务接受最终一致性 分库分表环境下全局主键不连续或跨节点排序难保证游标值失去单调性此时只能降级为「查出 ID 列表再分片拉取」但网络和内存开销陡增 游标分页看着简单但真正落地时那个传给下一页的 last_id 值到底该从哪一行取、是否带条件校验、前端缓存是否过期——这些细节一松动就又掉回深分页陷阱里。 VWO 一个A/B测试工具