如何通过子查询和延迟关联技术优化MySQL大分页查询？

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

MySQL中使用`LIMIT`语句时，`OFFSET`参数并非直接跳到第`OFFSET`行再取数据，而是从表头开始扫描，逐行计数，直到累计计数达到`OFFSET+size`行，然后返回这部分数据。当`OFFSET`非常大时，例如`OFFSET=100000`，它会返回从第100020条到第100120条的数据，即最后20条数据。

二级索引查ID时，再用ID回主键索引取整行，每一步都在随机IO，缓存命中率骤降，性能断崖式下滑。

SELECT * FROM t JOIN (SELECT id FROM t ... LIMIT N,M) AS tmp ON t.id = tmp.id 怎么写才真正生效

延迟关联的核心是“先轻后重”：子查询只走索引取 id，外层再按主键精准拉数据。但必须满足几个硬条件，否则照样慢：

• 子查询的 ORDER BY 字段必须有**覆盖索引**（比如 ORDER BY create_time DESC, id DESC，对应联合索引 KEY idx_ct_id (create_time, id)），否则子查询本身就会全表扫描
• 外层 JOIN 的关联字段必须是主键或唯一键，否则 MySQL 可能放弃使用 eq_ref 访问类型，退化为 ALL 或 range
• 避免在子查询里 SELECT * 或带非索引字段，否则无法触发 Using index，EXPLAIN 的 Extra 列会显示 Using where; Using filesort
• 示例正确写法：

  SELECT a.* FROM articles a INNER JOIN ( SELECT id FROM articles WHERE status = 1 ORDER BY created_at DESC, id DESC LIMIT 100000, 20 ) AS tmp ON a.id = tmp.id;

和 WHERE id >= (SELECT id FROM ... LIMIT N,1) 对比，哪种更适合你的场景

两者都绕开了大 offset，但适用边界很不同：

• WHERE id >= (SELECT ... LIMIT N,1) 要求排序字段与主键强相关（如自增 id），且数据不能大量删除，否则页码错位；适合简单主键分页，但不支持多条件过滤 + 复合排序（比如 ORDER BY category, updated_at）
• 延迟关联对排序字段无强依赖，只要该排序能走索引（哪怕是联合索引），就能稳定生效；支持任意 WHERE 条件 + ORDER BY 组合，业务适配性更强
• 性能上，延迟关联在 offset > 50w 时通常更稳；而子查询取单个起始 id 的方式，在高并发下可能因子查询被重复执行多次带来额外开销
• 注意：如果业务允许“只能下一页”，优先用游标式（WHERE created_at < ? AND id < ?），它比两种方案都快一个数量级

容易被忽略的三个坑

延迟关联不是套个 JOIN 就万事大吉，真实线上踩过这些坑的人才知道：

• 没加 STRAIGHT_JOIN 时，MySQL 优化器可能把外层表当驱动表，导致全表扫描；强制指定顺序：SELECT STRAIGHT_JOIN a.* FROM (SELECT id ...) AS tmp JOIN articles a ON a.id = tmp.id
• 子查询结果集若包含重复 id（比如 GROUP BY 或 DISTINCT 没处理好），外层 JOIN 会放大结果行数，甚至翻倍
• 如果分页字段存在大量相同值（如千万条记录 status = 1），ORDER BY status, id 中的 status 区分度太低，B+ 树遍历时仍要比较大量 id，此时必须补上足够区分的第二排序字段，否则延迟关联优势大幅缩水

实际效果取决于索引设计是否真正覆盖了子查询路径，而不是 SQL 表面上用了 JOIN。很多团队测完发现没提速，问题八成出在子查询那一步根本没走索引。