SQL 大数据量分页的性能瓶颈几乎都集中在 OFFSET 上。传统 LIMIT n OFFSET m 翻页时，数据库必须先扫描并丢弃前 m 行，才返回目标数据——偏移越深，浪费越严重。优化核心只有一句话：减少扫描行数、消除无用回表。据 OneUptime 的 MySQL 分页教程实测，LIMIT 10 OFFSET 100000 会让 MySQL 扫描 100010 行、丢弃 10 万行；改用游标分页（Keyset）后 EXPLAIN 显示 rows: 10，只查 10 行。主流方案有三类：游标分页（最推荐）、延迟关联（需跳页时）、以及覆盖索引配合限制深度。
一、OFFSET 深分页为什么崩
OFFSET 的语义是”跳过前 m 行”。无论你要的是第 100 页还是第 10 万页，数据库都得从第一条开始读、排序、再丢弃前面的全部。据 tsight.io 的性能对比：1000 万行表、单行 1KB，LIMIT 0,100 扫 100 行约 1ms；LIMIT 1000000,100 却要扫 1000100 行、涉及百万次随机回表 IO，响应可能飙到数秒甚至超时。
zooz engineering 把这种浪费量化成”99.99% 的废弃比”：为返回 10 行，数据库干了 100010 份活。更严重的是，它呈线性退化——页码越深，成本 O(Offset + Limit) 越高，在百万级表上深分页可能直接打满 CPU 与 IO，相当于对自家数据库发起 DoS。
二、OFFSET 还有”数据漂移”隐患
性能之外，OFFSET 在活跃系统里不稳定。zooz engineering 举例：用户看第 1 页（1–10 条），此时列表顶部插入一条新记录，整列下移一位；点”下一页”用 OFFSET 10 时，原本第 10 条被推到第 11，于是用户重复看到了它。反过来若中间删了一条，下一页会跳过某条，数据凭空消失在两页缝隙里。游标分页因依赖”上一页最后一条的 ID”而非绝对位置，天然规避此问题。
三、方案 A：游标分页（Keyset / Seek Method）
这是大数据量下最推荐的方案。思想是把”跳过 m 行”改成”从上一页最后一条之后开始取”。据 techbaithak 与 OneUptime 的对照：
-- 第一页
SELECT id, name, created_at FROM orders ORDER BY id ASC LIMIT 10;
-- 下一页：用上一页末的 last_id 续查
SELECT id, name, created_at FROM orders
WHERE id > 10 ORDER BY id ASC LIMIT 10;
WHERE id > 10 让优化器直接用 B+ 树索引定位到 1000001 的位置，顺序读 100 行即可——扫描行数从百万级降到 100 级。OneUptime 的 EXPLAIN 对比显示：游标写法 type: range, key: PRIMARY, rows: 10，无论翻到第几页，工作量都是”索引定位 + 取 Limit 行”，性能稳定毫秒级。
优点：任意深度性能一致；结果稳定无漂移。缺点：不能跳到任意页；必须有一个可排序的唯一列（通常主键）作游标。
四、方案 B：延迟关联（Deferred Join）
当业务必须支持”跳页”（用户直接点第 10000 页），游标无法胜任，此时用延迟关联。据 tsight.io 与 tsight 架构文章的解析，其核心是”先在索引树完成分页过滤、只拿主键，再回表取完整行”：
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY create_time LIMIT 1000000, 100
) tmp USING (id);
子查询 SELECT id FROM orders ORDER BY create_time LIMIT 1000000,100 只扫主键/覆盖索引，不取宽行；外层 JOIN 只回表那 100 个命中的 id。代价从”扫描整个 OFFSET 范围的宽行随机 IO”压缩成”最终 100 行的回表”，大幅减负。OneUptime 与 techbaithak 都把它列为”必须保留 OFFSET 时”的优化手段。
五、多列排序的游标写法
排序列非唯一（如 created_at 可能重复）时，单凭一列游标会漏数据。解决方法是元组比较 + 主键兜底（综合 zooz engineering 与 OneUptime）：
-- 第一页
SELECT id, created_at, total FROM orders
ORDER BY created_at ASC, id ASC LIMIT 10;
-- 下一页：用 (上一页末 created_at, id) 作游标
SELECT id, created_at, total FROM orders
WHERE (created_at, id) > ('2026-01-01 10:00:00', 12345)
ORDER BY created_at ASC, id ASC LIMIT 10;
(created_at, id) > (...) 是行值比较语法，确保并列时也能精确接续，不重不漏。注意要给 (created_at, id) 建联合索引，否则游标仍会退化成文件排序。
六、覆盖索引与深度限制兜底
据 tsight 架构建议与 CloudBase 文档：
- 覆盖索引：确保查询字段都在索引树里，
SELECT只取索引列可完全避免回表，深分页扫描成本进一步下降。 - 限制翻页深度：对”必须 OFFSET”的场景，设硬上限（如
OFFSET > 10000直接拒绝），引导用户用日期筛选而非狂点下一页。多数用户与爬虫从不过第 10 页。 - 搜索引擎兜底：若业务强依赖复杂条件跳页（如全文检索），引入 Elasticsearch 做倒排索引分页，而非压榨关系型数据库。
七、三种方案选型对照
| 方案 | 性能特征 | 能否跳页 | 适用场景 |
|---|---|---|---|
| OFFSET 分页 | 随深度线性退化 | 能 | 小表、浅页、SEO 友好链接 |
| 游标分页（Keyset） | 任意深度 O(Limit) 稳定 | 不能 | 无限滚动、下一页模式 |
| 延迟关联 | 比裸 OFFSET 快数倍 | 能 | 必须跳页的大表 |
| 覆盖索引 + 深度限制 | 减少回表、防滥用 | 视实现 | 配合上述任一方案 |
八、落地执行步骤
- 评估业务：是”无限滚动/下一页”还是”可跳页”？前者直接上游标。
- 选唯一可排序列（主键或
(排序列, id)）作游标，建对应索引。 - 前端改用
cursor_id驱动，弃用page_index传参。 - 必须跳页时，用延迟关联 + 覆盖索引，并对
OFFSET设硬上限。 - 复杂检索场景引入 ES 等外部引擎，关系库只做精确查询。
- 用
EXPLAIN核对rows是否从百万级降到 Limit 级，确认优化生效。
常见问题（FAQ）
游标分页能跳到第 10000 页吗？
不能。它依赖上一页末的游标，天然只支持”下一页”，跳页需改用延迟关联或搜索引擎。
延迟关联为什么比裸 OFFSET 快？
子查询只扫索引拿主键，外层只对命中 id 回表，把随机 IO 限制在极少数行而非整个 OFFSET 范围。
多列排序怎么保证游标不重不漏？
用 (排序列, 主键) 元组比较作游标，并建对应联合索引，并列值也能精确接续。
