深度分页（Deep Pagination）问题，指的是在查询数据时，页码（Offset）过深导致数据库或搜索引擎性能急剧下降的现象。其根源在于，大多数系统执行 LIMIT {offset}, {size} 时，并非直接跳到 offset 位置，而是先扫描并排序 offset + size 条数据，再丢弃前 offset 条。

当 offset 达到百万甚至千万级时，排序和扫描的 I/O 与内存开销极大，轻则查询超时，重则导致内存溢出（OOM）。结合你之前关注的 Spark、Redis 等内容，这个问题在 SQL 数据库、Elasticsearch 和大数据场景中都极为常见。

下面我从通用数据库（MySQL/PostgreSQL）、搜索引擎（Elasticsearch） 和大数据（Spark） 三个维度，给出对应的最佳实践方案。

---

1. 通用关系型数据库（MySQL / PostgreSQL）

方案一：游标分页（Cursor-based Pagination / Keyset Pagination）

这是最推荐的方案。它利用索引过滤，而非偏移量。

• 核心逻辑：记住上一页最后一条数据的唯一标识（如 id）或排序字段，下一页查询时带上 WHERE id > last_id。
• SQL 示例：

  -- 第一页
  SELECT * FROM orders ORDER BY id LIMIT 10;
  
  -- 第二页（假设上一页最后 id = 100）
  SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;
  

• 优点：利用 B+Tree 索引直接定位，复杂度 O(log N)，数据量再大也毫秒级返回。
• 缺点：不支持跳页（只能“上一页/下一页”），不能直接跳到第 100 页。

方案二：覆盖索引 + 延迟关联（Covering Index & Deferred Join）

如果业务必须支持跳页，为了减少回表产生的随机 I/O，采用此方案。

• 核心逻辑：先只查询主键（利用覆盖索引快速定位），再用主键关联回原表取出完整数据。
• SQL 示例：

  SELECT * FROM orders t1 
  JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 100000, 10) t2 
  ON t1.id = t2.id;
  

• 优点：MySQL 内层子查询只需扫描索引，外层通过主键快速回表，比直接 LIMIT 100000, 10 快数倍。
• 缺点：深度极深时（如百万级），扫描索引的开销依然存在，只是缓解。

方案三：限制最大页数

最简单的产品策略。在搜索页面限制最多只能看 100 页（Google、百度均如此）。从业务层面彻底杜绝深度遍历。
限制最大页数之所以有效，是因为它从根源上“阉割”了深度分页产生的条件，让数据库查询永远跑在性能的“舒适区”内。

---

2. 搜索引擎（Elasticsearch）

ES 的 from + size 分页原理与数据库类似：协调节点需要从每个分片拉取 from + size 条数据，在内存中全局排序后再丢弃。深度分页会导致协调节点内存飙升甚至 OOM。

方案一：Search After（官方首选）

相当于数据库的“游标分页”，是 ES 官方唯一推荐用于深度分页的方案。

• 核心逻辑：传入上一页最后一条数据的排序值（sort 字段值），配合 search_after 参数。
• 注意：必须确保排序字段唯一（通常追加 _id 作为 tie-breaker），且需要开启 pit（Point In Time）来保证数据一致性。
• 适用：滚动加载（如手机下拉刷新）。

方案二：Scroll API（仅适用于批量导出）

滚动上下文，会生成数据快照。

• 优点：适合一次性拉取大量数据（如导出全量数据给 Spark 做离线分析）。
• 致命缺点：不适用于实时分页。它占用大量内存，且数据有滞后性（快照时刻的数据）。

---

3. 大数据场景（Spark SQL / Hive）

在大数据离线数仓中，如果使用 ROW_NUMBER() OVER (ORDER BY col) WHERE rn BETWEEN 100000 AND 100010，Spark 会进行全局排序（Full Sort），导致所有数据发生 Shuffle，性能极差。

• 解决思路：放弃全局唯一有序分页。
• 替代方案：利用 分桶（Bucketing） 或 分区（Partitioning）。
  • 例如，按 dt 分区，按 user_id 分桶。
  • 查询时采用随机抽样或聚合统计，而非精确的全局深分页。如果必须导出明细，使用 df.write 直接按分区输出文件，避免在交互式查询中做深分页。

---

方案对比与选型总结

场景	推荐方案	是否支持跳页	性能表现
业务列表（App/Web）	游标分页 (Cursor) / Search After	❌ 不支持	极高，数据量无上限
后台管理（需跳页）	限制页数 + 覆盖索引关联	✅ 有限支持	较高，极深时仍会变慢
全量数据导出	Scroll (ES) / df.write.parquet (Spark)	❌ 不支持	高吞吐，顺序读取
离线分析/报表	分区+分桶，避免全局排序	❌ 不支持	极快，避免 Shuffle

关键建议

在设计技术方案时，可以遵循以下原则：

1. 业务优先：如果产品经理允许，直接禁用深分页或改为“加载更多”（游标模式），这是成本最低、效果最好的方式。
2. 索引覆盖：如果必须支持跳页，务必让 ORDER BY 和 LIMIT 涉及的字段组成复合索引，并利用覆盖索引（延迟关联）减少回表。
3. 数据异构：对于极其复杂的深分页（如多表 Join + 排序），可以考虑将数据同步到 Elasticsearch 或 ClickHouse，利用其列存和倒排索引特性来抗住深度分页压力。

你现在的业务场景是偏向 Web 后台管理（需要跳页），还是 App 客户端的无限滚动加载（游标模式）？如果是前者，我可以给你展示一个完美的 MySQL 覆盖索引写法。