本文目录导读：
- 基于游标的分页（Keyset / Seek Method）⭐ 最推荐
- 延迟连接（Deferred Join / Covered Index Only）
- 使用索引覆盖排序（Covering Index）
- 预估分页（Pre-computed / Snapshot）
- 不要精确分页（Approximate Pagination）
- 数据库特定优化
- 选择策略
优化大结果集的分页性能是数据库和应用程序开发中的常见挑战,传统的 OFFSET 分页方式在大偏移量下性能极差，以下是几种高效的分页优化策略，按推荐程度排序：
基于游标的分页（Keyset / Seek Method）⭐ 最推荐
这是处理大数据量分页最有效的方法，它不依赖 OFFSET，而是通过 WHERE 子句过滤掉已看过的数据。
原理：记住上一页最后一条记录的某个唯一键值（通常是主键或联合唯一索引），查询下一页时，直接定位到这个键值之后的数据。
SQL 示例：
假设按 id 升序分页，每页20条。
-
第1页：常规查询
SELECT id, name, created_at FROM users ORDER BY id LIMIT 20;
假设最后一条记录的
id = 100。 -
第2页（及后续）：
SELECT id, name, created_at FROM users WHERE id > 100 -- 直接跳过已看过的数据 ORDER BY id LIMIT 20;
优点：
- 无论页码多大,速度恒定（O(log n) 跳表/索引扫描）。
- 避免读取和丢弃大量行。
- 数据变化时（插入/删除），页面顺序稳定。
缺点：
- **无法跳转到中间页（如“跳到第100页”），通常需要提供“上一页/下一页”按钮。
- 排序条件必须包含独特值（通常是主键+排序列），或多字段联合唯一索引。
适用场景：社交媒体动态、无限滚动、实时日志或时间线。
延迟连接（Deferred Join / Covered Index Only）
用于 OFFSET 无法避免时，但可优化单行扫描的代价，先仅从索引中获取主键，再根据主键回表获取完整行。
原理：LIMIT ... OFFSET 时，数据库需要跳过 OFFSET 数量的行，如果使用覆盖索引，只需扫描索引（通常比全表扫描快得多），然后只对最终需要的N行进行回表查询。
SQL 示例：
-- 低效：扫描大量行并回表
SELECT *
FROM users
ORDER BY id
LIMIT 20 OFFSET 100000;
-- 高效：先只查索引获取主键
SELECT id
FROM users
ORDER BY id
LIMIT 20 OFFSET 100000;
-- 然后用主键关联获取完整行
SELECT u.*
FROM (
SELECT id
FROM users
ORDER BY id
LIMIT 20 OFFSET 100000
) AS o
JOIN users u ON o.id = u.id
ORDER BY u.id;
优点：避免回表大量行，显著减少IO。
缺点：OFFSET 本身仍要扫描大量索引条目（只是比全表行快）。
使用索引覆盖排序（Covering Index）
确保 ORDER BY 和 LIMIT 涉及的字段完全被索引覆盖。
原理：如果查询的字段全部在索引中，数据库可直接从索引读取数据（无需回表），通常使用索引顺序扫描。
适用：当查询列较少时，可建复合索引 (order_column, select_column1, select_column2)。
示例：若经常按 created_at 排序，查询 id, title。
-- 应建立索引：id, created_at -- 或更好的复合索引：(created_at, id, title) 如果查询字段固定
预估分页（Pre-computed / Snapshot）
如果数据变化不频繁,或精确行数不重要，可提前计算或缓存每一页的主键列表。
方法：
- 缓存页映射：将每页的起始ID列表存入Redis或内存（如
page:1=id1, page:2=id2）。 - 使用分段键：例如按时间分区，每页对应一个时间区间，查询时直接指定
WHERE time BETWEEN x AND y。
不要精确分页（Approximate Pagination）
对于“总页数”不重要的场景（如“加载更多”），可以放弃精确总数。
- 使用 游标分页。
- 或
LIMIT N+1做法：查询LIMIT 21，若返回21行则证明还有下一页，实际只显示20条。
数据库特定优化
MySQL
- *避免 `COUNT()
大表**：使用EXPLAIN` 估算行数，或用缓存计数。 - 使用
max(OFFSET_ID)+ 游标：结合索引。 - 分区表：按月或按ID范围分区，分页查询仅在单分区内
OFFSET。
PostgreSQL
- 使用
SCROLL游标（仅在事务内有效）。 SP-GiST或BRIN索引：适用于范围查询。
Elasticsearch / NoSQL
- 使用
search_after或scrollAPI（本质也是游标）。
选择策略
| 场景 | 推荐方案 |
|---|---|
| 需要跳页（如搜索引擎结果页） | 使用 延迟连接 + 覆盖索引，若数据巨大则考虑 近似分页（只显示“约多少条”）。 |
| 无限滚动 / 加载更多 | 游标分页（唯一键 + WHERE > last_value）。 |
| 固定排序且数据不频繁变化 | 预计算页起始键 缓存。 |
| 对性能要求极高，且可以接受不精确 | limit N+1 或直接放弃 COUNT(*)。 |
核心原则：永远不要让数据库扫描并丢弃数百万行。 使用索引定位+WHERE过滤来替代OFFSET。
