分库分表后跨分页查询的完整方案

一、问题的本质

分库分表后，数据分散在多个物理表中。要做 ORDER BY time LIMIT 10 OFFSET 100，你无法知道第 101-110 条数据在哪个分片上，必须查询所有分片再合并。

假设有 10 个分片：

• 每个分片需要查 LIMIT 110（offset + limit）
• 总共取 10 × 110 = 1100 条数据
• 合并排序后取最后 10 条

深分页问题：OFFSET 越大，性能越差（数据膨胀严重）。

---

二、五种方案详解

方案1：各分片查询 limit offset + N，归并再取 N

原理：

每个分片：SELECT * FROM t ORDER BY time LIMIT offset + N
应用层：合并所有结果 → 全局排序 → 取第 offset 到 offset+N 条

示例（offset=100, limit=10, 2个分片）：

分片1: 查 110 条 → 返回 110 条
分片2: 查 110 条 → 返回 110 条
应用层: 220 条排序 → 取第 101-110 条

优缺点：

• ✅ 实现简单
• ❌ offset 越大性能越差
• ❌ 网络传输数据量大

适用场景：数据量小、并发低、不会深分页

---

方案2：禁止深分页，改用游标（Cursor）

原理：不用 offset，用上一页最后一条数据的排序字段值作为下一页的起点。

第一次请求：

-- 前端传：page=1, size=10
SELECT * FROM t ORDER BY id LIMIT 10
返回：last_id = 第10条的id = 100

下一页请求：

-- 前端传：cursor=100, size=10
SELECT * FROM t WHERE id > 100 ORDER BY id LIMIT 10

分库分表改造：

-- 每个分片执行
SELECT * FROM t WHERE id > 100 ORDER BY id LIMIT 10

-- 应用层合并所有分片结果（最多 10 × 分片数条）→ 取最小的 10 条

优缺点：

• ✅ 性能稳定（不受页数影响）
• ✅ 适合滚动加载/瀑布流
• ❌ 不能跳页（不能直接去第 100 页）

适用场景：移动端列表、网页滚动加载、时间线

---

方案3：离线查询走 ES / ClickHouse

原理：分库分表只做单点查询（按 ID 精确查），复杂排序/聚合/搜索走搜索引擎。

架构：
    复杂查询请求 → ES/ClickHouse（同步数据）→ 返回 ID 列表
    再根据 ID 列表 → 分库分表中批量取详情

数据同步方案：

• CDC（Canal/Debezium）：MySQL binlog → Kafka → ES/CK
• 双写：业务写入时同时写 DB 和 ES

优缺点：

• ✅ 支持复杂查询、全文搜索、聚合分析
• ✅ 与分库分表解耦
• ❌ 引入新组件，增加运维成本
• ❌ 数据同步有延迟（秒级）

适用场景：后台管理、运营系统、数据报表

---

方案4：二次查询法（理论方案）

原理：利用相对位置，减少数据膨胀。以 ORDER BY id LIMIT 10 OFFSET 100 为例：

步骤1：每个分片查 LIMIT 100+10，记录每个分片的最小 id

分片1: 查110条，最小id=100
分片2: 查110条，最小id=200
分片3: 查110条，最小id=150

步骤2：全局排序这 3 个最小值，取第 100 条落在哪个分片

排序: [100(分片1), 150(分片3), 200(分片2)]
第100条在分片1范围内 → 目标在分片1

步骤3：精确计算 offset，只查目标分片

分片1：目标 id 范围 = 100 + (100 - 前一个分片的总数)
...

优缺点：

• ✅ 性能较好（减少数据放大）
• ❌ 实现极其复杂（要处理各种边界情况）
• ❌ 只适用于排序字段全局唯一（如 id）
• ❌ 实际项目中几乎没人用

结论：理论方案，工业界基本放弃。

---

三、方案对比总结

方案	实现难度	性能	是否支持跳页	生产落地	推荐指数
归并取N	简单	差（深分页线性下降）	✅	✅ 小数据量	⭐⭐
游标	中等	好（稳定）	❌	✅ 广泛应用	⭐⭐⭐⭐⭐
ES/CK	复杂	好	✅	✅ 中大型项目	⭐⭐⭐⭐
二次查询	极难	较好	✅	❌ 极少用	⭐

---

四、实际落地方案推荐

场景	推荐方案
C端列表（如订单列表）	游标分页（不用 offset）
B端管理后台	ES/CK + 游标分页
导数据/批量任务	游标分页 + 流式处理
数据量小（< 100 万）	归并取N + 限制最大 offset
现有业务已用 offset	限制 offset ≤ 1000，超过则拒绝

---

五、一句话总结

跨分页查询的核心痛点是 offset 越大性能越差，业界主流方案是改用游标分页（滚动加载），复杂查询走 ES/ClickHouse，不再纠结于在分库分表中实现深分页。