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

OFFSET 越大查询越慢，因MySQL需扫描并丢弃前N+M行，深分页时即使走索引也要回表读取百万级主键再判断条件，本质是“假走索引、真全扫”。为什么 OFFSET 越大，查询越慢？MySQL 的 OFFSET 不是跳过前 N 行再取数据，而是让引擎先扫描并丢弃前 N+M 行（M 是 LIMIT 值），再返回结果。尤其在深分页（比如 OFFSET 1000000）时，即使有索引，也要回表读取 100 万行的主键或聚簇索引记录，再逐条判断是否满足 WHERE 条件——这本质是「假走索引、真全扫」。真实场景：用户翻到第 5000 页（每页 20 条 → OFFSET 99980），EXPLAIN 显示 rows 高达百万级，但 key 显示用了索引，误以为高效 回表代价来自二级索引查到主键后，再根据主键去聚簇索引捞完整行——深分页放大了这个随机 IO 即使加了覆盖索引，只要 SELECT 中包含未被索引覆盖的字段，仍会触发回表 延迟关联（Deferred Join）怎么写才有效？核心思路：用子查询先拿到精准的主键集合（只走索引、不回表），再用这些主键 JOIN 原表取完整字段。关键是子查询必须只 SELECT 主键，且外层 JOIN 不能破坏驱动表顺序。子查询里禁止出现 SELECT * 或非主键字段，否则优化器可能放弃延迟关联策略 外层 JOIN 必须用 INNER JOIN，且主键字段要显式出现在 ON 条件中，例如：SELECT t.* FROM (SELECT id FROM article WHERE status=1 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测试工具