分库分表（Sharding）后的分页查询问题，本质上是 「局部有序」与「全局有序」之间的矛盾。

在单表场景下，数据库利用 B+ 树索引可以快速定位 offset；但在分片环境下，数据分散在不同的物理节点，没有任何一个节点拥有全局视图。

当然，在单表场景下，如果 offset 太大，依旧需要白白扫前面的 offset 条数据，然后才取 limit 要的数据，很浪费。

我们可以从 问题本源（第一性原理）、通用方案、深度分页优化 以及 业务折衷 四个层面来思考解决方案。

1. 为何分表后分页变难了？

假设我们有 3 个分片（Node A, Node B, Node C），按照 ID 取模分片。现在我们要按时间排序，查询第 10 页的数据（每页 10 条，即 LIMIT 10 OFFSET 90）。

1.1 错误的直觉

直觉告诉我们：在每个分片上执行 LIMIT 10 OFFSET 90，然后把结果拿回来合并。

这是错误的。因为 Node A/B/C 的第 91 条数据，可能是全局第 91 条，也可能是全局第 1 条（如果 Node A 的数据时间都很新），也可能是全局第 270 条。你无法确定每个分片内部数据的全局相对位置。

1.2 正确但低效的做法（Broadcast & Merge）

为了保证数据的准确性，你必须确保不错过任何可能的记录。

  • 你需要向 所有分片 发送请求：SELECT * FROM table ORDER BY time LIMIT 100 (OFFSET + LIMIT)。
  • 汇总所有分片返回的数据：\(3 \times 100 = 300\) 条。
  • 在内存中对这 300 条数据进行全局排序。
  • 取第 91-100 条，抛弃其余 270 条。

这种做法被称为 全局视野法。它的代价是随着页码（OFFSET）的增加，查询代价呈指数级（或线性倍数）增长。

如果用户查询第 10,000 页（Offset 100,000, Limit 10），有 N 个分片：

  • 网络 I/O： 需要传输 \(N \times (100,000 + 10)\) 条数据。
  • 内存 CPU： 应用层或中间件需要对 \(N \times 100,010\) 条数据进行排序。

这就是著名的 分库分表深分页（Deep Paging）问题。

2. 通用解决方案

针对上述问题，工界通常有以下几种演进方案：

  • 全局视野法：中间件代理，即上述的 broadcast & merge。
  • 二次查询法：新增一个全局 ID 映射表，先查 ID 后查数据。
  • 同步至异构数据源：利用专门的 OLAP 数据库进行处理。

2.1 全局视野法（中间件代理）

即上述的 "Broadcast & Merge"。

  • 实现： 依赖 ShardingSphere、MyCat 等中间件，或者在代码层并发调用。
  • 适用场景： 分页深度较浅（前 10-20 页），对性能要求不极致的后台管理系统。
  • 缺点： 越往后翻，数据库压力越大，最终会导致 OOM（内存溢出）或超时。

2.2 二次查询法（全局 ID 映射）

如果必须精确分页且页码较深，可以建立一张 「索引表」。

  • 原理： 建立一张只有 (排序字段, 主键 ID) 的表，这张表不分片（或者按照排序字段分片）。
  • 步骤：
    1. 先在索引表中执行 LIMIT 10 OFFSET N，拿到 10 个 ID。
    2. 拿着这 10 个 ID，去各个分片中查询具体数据（利用 IN 查询，命中分片键）。
  • 适用场景： 排序字段单一，且索引表数据量在单机可承受范围内（例如数据量虽大但每行很小）。
  • 缺点： 多了一次查询；索引表本身可能成为瓶颈。

2.3 同步至异构数据源（大数据方案）

这是最通用的重型解决方案。MySQL 只做 OLTP（事务处理），复杂的查询交给专门的检索引擎。

  • 原理： 通过 Binlog（Canal/Debezium）将数据实时同步到 Elasticsearch (ES) 或 ClickHouse。
  • 步骤： 分页查询直接走 ES，ES 天然支持分布式搜索和排序。拿到 ID 后，如果需要最新鲜的详情，再回查 MySQL（可选）。
  • 适用场景： C 端搜索、复杂条件筛选、海量数据分页。
  • 缺点： 架构复杂，存在数据同步延迟（秒级或毫秒级）。

3. 深度分页的优化技巧

如果不想引入 ES，必须在 MySQL 体系内解决深分页，可以使用以下方法：

  • 游标法：不用 offset，改用 where + limit。
  • 只有 ID 的全局排序：分片只查询 ID，然后再逐个去查询其他字段。

3.1 游标法

这是最高效的方案，将 \(O(N)\) 的复杂度降为 \(O(1)\)。

  • 核心思想： 抛弃 OFFSET，使用 "上一页的最后一条记录" 作为锚点。
  • 前提： 排序字段必须有唯一性（通常用 order by time, id）。
  • SQL 变化：
    • 第 1 页：LIMIT 10 -> 记录最后一条的 time=T1, id=ID1。
    • 第 2 页：WHERE time < T1 OR (time = T1 AND id < ID1) ORDER BY time DESC, id DESC LIMIT 10。
  • 优点： 无论翻到第几万页，每个分片只需要扫描 10 条数据，性能恒定。
  • 缺点： 不支持跳页（只能点击"下一页"或"加载更多"），不适合需要跳转到"第 X 页"的场景。适用于 App 的无限流（Feed 流）。

单表场景其实也建议使用这个方案，不然 offset 会白白扫描很多的数据。

3.2 只有 ID 的全局排序

如果必须支持跳页，可以结合方案一进行优化。

步骤：

  1. 每个分片只查询 id 和 排序字段（不查询 select *），减少网络传输和内存消耗。
  2. 在内存中对 ID 列表排序，截取需要的 ID。
  3. 用 ID 回表查询完整数据。

效果： 缓解了网络带宽压力，但没有解决数据库扫描的 I/O 压力。

4. 业务折衷与产品设计

很多时候，技术上的难题可以通过修改产品逻辑来规避。

4.1 限制最大页码

真的有用户会看电商商品的第 5000 页吗？通常 Google 搜索也只给你看前几十页。

可以限制只能查看前 100 页。超过 100 页提示“请输入更精确的搜索条件”。

4.2 牺牲精度（模糊分页）

当数据量达到亿级，用户并不在乎显示的 "共 10000+ 条" 是否精确，也不在乎第 100 页的第一条数据和第 99 页的最后一条是否严格连续。

可以每个分片各取一部分数据，按照某种权重拼凑一页给用户。或者，每隔一定时间预计算一次全局 Count。

5. 总结

方案 关键技术点 复杂度 适用场景 备注
全局视野 (Standard) 中间件广播合并 \(O(Offset \times Shards)\) 后台管理，前几页 开发成本最低，深分页必死
异构索引 (ES) MySQL -> ES \(O(1)\) ~ \(O(logN)\) C 端搜索，复杂查询 架构重，有延迟
游标法 (Seek) WHERE id > last_id \(O(1)\) 移动端 Feed 流 性能最好，但不能跳页
二次查询 全局索引表 \(O(logN)\) 排序维度单一 维护额外的表

推荐方案：

  • 首选游标法（Cursor-based pagination）：设计 API 时，参数不要用 page_number，而是用 next_cursor（加密的 token，包含上一页的 ID）。这是现代 API（如 Twitter, Stripe）的标准做法。
  • 兜底方案：如果必须用传统分页，限制 max_offset。
许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。

分享文章

快来参与讨论吧~