SQL 如何高效分页（OFFSET 大值时性能爆炸的优化）

OFFSET 超过 10 万行后变慢，本质是数据库必须扫描并丢弃前 N 行，造成大量 I/O 和 CPU 浪费；游标分页通过索引范围查询避免跳过，是更可扩展的替代方案。

OFFSET 超过 10 万行后查询变慢，本质是数据库在做什么？

PostgreSQL、MySQL（8.0+）和 SQLite 在执行 OFFSET N LIMIT M 时，即使只返回 M 行，也必须先扫描并跳过前 N 行——这些行可能已从磁盘读入内存、经过 WHERE 过滤、排序缓冲区重排，最后才丢弃。N 越大，I/O 和 CPU 浪费越严重，尤其当排序字段无索引或存在 JOIN 时，性能呈线性甚至次平方级下降。

关键不是“分页逻辑错了”，而是 OFFSET 本身在 大数据 集上不具备可伸缩性。它适合展示前几页（比如管理后台查最新 100 条），但不适合“翻到第 5000 页”这种场景。

用游标分页（Cursor-based Pagination）替代 OFFSET

游标分页要求数据有 严格、唯一、单调的排序字段（如 id、created_at + id 组合），不依赖行号，而是基于上一页最后一条记录的值来定位下一页。

典型写法（以按 id 升序为例）：

SELECT * FROM orders  WHERE id > 12345  ORDER BY id  LIMIT 20;

相比 OFFSET 100000 LIMIT 20，这条语句能直接走 id 索引的范围扫描，跳过所有无关行。前提是：

• id 是主键或有高效索引
• 查询中不能出现 OFFSET、不能混用 ORDER BY …… DESC 后再用 >（应改用 ）
• 前端需保存上一页末尾的 id 值，而不是页码
• 若排序字段非唯一（如多个记录 created_at 相同），必须补一个唯一字段（如 id）避免漏 / 重数据：WHERE (created_at, id) > ('2024-01-01', 999)

MySQL 8.0+ 的窗口函数能否救场？

ROW_NUMBER() 看似能绕过 OFFSET，但实际效果有限：

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn   FROM orders ) t WHERE rn BETWEEN 100001 AND 100020;

这个查询仍需对全表（或 WHERE 范围内全部数据）排序并编号，性能不会比 OFFSET 更好，只是把“跳过”逻辑从存储引擎移到了执行器层。它适合小结果集或配合强过滤条件（如加 WHERE status = 'paid' 且该字段有索引），否则仍是全量扫描。

真正有效的优化路径只有两条：

• 业务侧放弃“跳转任意页”，改用游标 + 下一页 / 上一页按钮（推荐）
• 数据侧预计算分页锚点（如每 1000 条存一个最小 id 到缓存），再结合游标二次定位（适合后台批处理）

为什么 LIMIT 后加 ORDER BY 就容易出问题？

如果 ORDER BY 字段没索引，数据库必须先对整个结果集排序，再取前 N 行——此时 LIMIT 失去剪枝能力。更糟的是，OFFSET 会强制排序完成后再丢弃前 K 行。

常见陷阱：

• ORDER BY created_at DESC 但 created_at 没索引 → 全表文件排序
• ORDER BY user_id, created_at 但只对 user_id 建了单列索引 → 无法利用索引做范围 + 排序
• JOIN 多张表后 ORDER BY → 排序发生在临时表，索引失效

验证方式：在 MySQL 中看 EXPLAIN 的 Extra 列是否含 Using filesort；在 PostgreSQL 中看 EXPLAIN ANALYZE 是否出现 Sort 节点且 rows= 巨大值。

游标分页不是银弹——它不支持随机跳页、对排序稳定性敏感、需要客户端配合维护状态。但只要接受“只能向前 / 向后翻”，它就是目前最可靠的大偏移分页解法。其他方案（如延迟关联、子查询 ID 预取）往往只在特定 schema 和数据分布下有效，而游标逻辑清晰、可预测、易于监控慢查询来源。