如何通过延迟关联优化大数据量分页查询速度慢的问题？

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

由于MySQL在执行这类查询时，必须先扫描并跳过前10000+行（只想获取后面的20+行），再取结果。如果id上有索引，排序快，但跳过动作仍需逐行计数；如果没有索引或涉及回表，则更慢。数据量越大、偏移量越大，在性能断崖式下降的情况下，查询速度会显著降低。

常见错误现象：EXPLAIN 显示 rows 高达几十万甚至百万，Extra 里出现 Using filesort 或 Using temporary；实际执行耗时从毫秒级升到秒级甚至十几秒。

• 不是数据量大才慢，是 OFFSET 大才慢 —— 即使总数据 100 万行，LIMIT 999990, 10 也会极慢
• 加 WHERE id > ? 手动模拟分页，只在主键/有序字段上可行，且要求严格单调、无删除空洞
• 用 ORDER BY create_time 分页时，时间可能重复，导致漏数据或重复翻页

延迟关联（Deferred Join）怎么写：用子查询先捞 ID，再关联原表

核心思路是把“找第 N 页的 ID”和“查这些 ID 对应的完整字段”拆成两步。第一步只走索引，极快；第二步用主键等值 JOIN，也快。

假设表 t 主键为 id，要查第 5001 页（每页 20 条）：

SELECT t.* FROM t INNER JOIN ( SELECT id FROM t ORDER BY id LIMIT 100000, 20 ) AS tmp ON t.id = tmp.id;

对比原始写法：SELECT * FROM t ORDER BY id LIMIT 100000, 20，这个优化能减少 90%+ 的 I/O 和 CPU 消耗（尤其当 t 表有宽字段或 TEXT/BLOB 时）。

• 子查询 SELECT id ... 只读索引，不回表，rows 就是 LIMIT 数（20），不是 100020
• 外层 JOIN 必须用主键或唯一索引字段，否则无法保证高效匹配
• 如果排序字段不是主键（比如 ORDER BY status, id），子查询需包含所有排序字段，且外层 WHERE 要补全等值条件，否则可能丢失排序稳定性

MySQL 8.0+ 的窗口函数替代方案：ROW_NUMBER() 不适合分页

ROW_NUMBER() OVER (ORDER BY id) 看起来直观，但实际执行会强制对全表排序并编号，内存/磁盘开销巨大，比 LIMIT OFFSET 更慢，不能用于线上分页场景。

正确做法仍是延迟关联；窗口函数更适合“取 TOP N”或“按组排名”，而非分页。

• SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM t) t1 WHERE rn BETWEEN 100001 AND 100020 —— 这个语句逻辑对，但性能灾难
• MySQL 8.0 的 OFFSET 本身没优化，只是语法支持更全，底层执行策略未变
• 如果业务允许，用游标分页（WHERE id > last_seen_id LIMIT 20）比任何基于 OFFSET 的方案都可靠、快

容易被忽略的边界问题：排序字段有重复值怎么办

如果用 ORDER BY create_time，而多行 create_time 相同，MySQL 排序结果不稳定，延迟关联子查询返回的 ID 顺序可能和外层 JOIN 时不一致，导致漏行或重复。

解决方案是在排序字段后追加主键，确保唯一性：

SELECT t.* FROM t INNER JOIN ( SELECT id FROM t ORDER BY create_time, id LIMIT 100000, 20 ) AS tmp ON t.id = tmp.id;

• 不加 id 的后果：两次执行同一语句，可能拿到不同 20 行，前端翻页“闪退”或“卡住”
• 即使业务上 create_time 理论不会重复，也要防数据库时钟精度、批量插入等现实因素
• 不要依赖 ORDER BY create_time DESC, id DESC 后再用 id < ? 游标 —— 方向反了容易错乱，统一用升序 + > 最稳妥

分页慢的本质不是数据多，是 MySQL 不得不“数着走”。延迟关联绕开了数，但前提是排序字段可收敛、关联字段够快。真正上线前，务必用真实数据量 + 真实偏移量跑 EXPLAIN FORMAT=JSON 看 sort_buffer_size 是否溢出、是否用到覆盖索引。