分库分表后如何进行分页查询？

结论：分片后 LIMIT offset, size 无法下推——每个分片都得返回 offset + size 行，再由中间件归并排序。偏移越大，扫描量按「分片数 × offset」放大。工程解法有四类：游标分页（Seek Method）、二次查询、限制最大页深、把列表查询交给搜索引擎或宽表。

一、代价从哪来

SELECT * FROM t_order ORDER BY id DESC LIMIT 100000, 20;
        │
        ├─ ds0.t_order_0 ── 取前 100020 行 ──┐
        ├─ ds0.t_order_1 ── 取前 100020 行 ──┤   堆归并排序
        ├─ ds1.t_order_0 ── 取前 100020 行 ──┤   丢弃前 100000 行
        └─ ds1.t_order_1 ── 取前 100020 行 ──┘   只返回 20 行
              总扫描 400080 行；offset 翻倍，扫描量翻倍

二、游标分页

-- 第一页
SELECT id, user_id, amount, create_time
  FROM t_order
 WHERE user_id = 42
 ORDER BY id DESC
 LIMIT 20;

-- 后续页：用上一页最后一条的 id 作为游标
SELECT id, user_id, amount, create_time
  FROM t_order
 WHERE user_id = 42 AND id < 10086
 ORDER BY id DESC
 LIMIT 20;

游标分页有两个前提：排序字段唯一且稳定（通常用主键）；不能跳页。它把每页代价压成常数，是信息流与订单列表的首选。

三、二次查询与批量回查

// 1. 各分片只取主键区间，不取整行
List<Long> candidateIds = new ArrayList<>();
for (String shard : shards) {
    candidateIds.addAll(orderMapper.selectIds(shard, userId, offset, size));
}

// 2. 全局排序后截取本页
List<Long> pageIds = candidateIds.stream()
        .distinct()
        .sorted(Comparator.reverseOrder())
        .skip(offset % size)
        .limit(size)
        .toList();

// 3. 按主键批量回查明细，各分片并行
Map<Long, Order> detail = orderMapper.selectByIds(pageIds).stream()
        .collect(Collectors.toMap(Order::getId, o -> o));

只传主键能显著降低网络传输量；总页数若必须精确，用计数表或预聚合指标替代每次 COUNT(*)。

四、配置与 SQL 约束

spring:
  shardingsphere:
    props:
      sql-show: true          # 观察改写后的实际 SQL
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds$->{0..3}.t_order_$->{0..3}

-- 反例：无分片键的深分页，四个库十六张表全部扇出
SELECT * FROM t_order ORDER BY id LIMIT 500000, 20;

-- 正例：带上分片键，直接路由到单表
SELECT * FROM t_order WHERE user_id = 42 ORDER BY id LIMIT 20;

五、常见追问

Q1：为什么分片后 COUNT(*) 也慢？ 总数要各分片分别 COUNT(*) 再求和，无法走覆盖索引时每片都是全索引扫描。数据量大时建议维护计数表，或接受近似值。

Q2：产品一定要跳页怎么办？ 限制最大页深（例如只允许前 100 页），超过后引导用户增加筛选条件；或把结果集写入临时表、缓存快照后分页。

Q3：归并排序会不会 OOM？ 会。中间件一般有内存阈值，超过后退化为磁盘归并或直接报错。所以深分页必须从业务侧禁止，而不是靠调大内存。

小结

• 分页代价等于分片数乘 offset，深分页在分片架构里是放大器。
• 首选游标分页；需要总数与跳页时用二次查询或搜索引擎。
• 限制最大页深是最便宜、最有效的保护。