一、问题背景:分表之后,分页为什么变难了
单表数据量突破千万级后,很多团队会选择水平分表(sharding),把一张大表按某个分片键拆成 n 张子表。写入和按分片键查询的问题解决了,但分页查询立刻变得棘手:
- 数据分散在多个物理表中,
limit offset, size无法直接跨表生效; - 全局的
order by create_time desc需要在多个分片上分别排序后再归并; - 深分页时
offset越大,需要丢弃的数据越多,性能急剧下降; - 分片键与排序字段不一致时,无法通过路由直接定位到单个分片。
下面先明确一个前提:分页查询的难点在于跨分片排序 + 深分页。如果查询条件里带了分片键,问题会退化成单表分页,几乎不需要特殊处理。
二、方案一:全局视野法(内存归并排序)
最直观的做法是把每个分片都查一遍,在应用层做归并排序,再截取目标区间。
-- 假设按 user_id 取模分成 4 张表:t_order_0 ~ t_order_3 -- 查询第 3 页,每页 10 条,按 create_time 倒序 select * from t_order_0 order by create_time desc limit 0, 30; select * from t_order_1 order by create_time desc limit 0, 30; select * from t_order_2 order by create_time desc limit 0, 30; select * from t_order_3 order by create_time desc limit 0, 30;
应用层把 4 份结果合并排序,取第 21~30 条返回。关键点是:每个分片都要取 offset + size 条,而不是只取 size 条,否则会漏数据。
这种方式的优缺点非常明显:
| 维度 | 说明 |
|---|---|
| 优点 | 实现简单,结果绝对准确,不依赖额外组件 |
| 缺点 | 分片数 × (offset + size) 的数据量全部拉回内存,深分页时内存和网络开销巨大 |
| 适用 | 分片数少(如 4~8 张)、页码较浅(前几十页)的场景 |
shardingsphere 等中间件默认就是这种归并策略,它适合作为兜底方案,但不适合深分页。
三、方案二:二次查询法(最小主键回查)
为了减少数据传输量,可以先只查主键,再回表取详情。核心思路是:
- 每个分片执行
select id from t_order_x order by create_time desc limit 0, 30,只返回主键; - 应用层归并排序,确定第 21~30 条记录的主键集合;
- 用这批主键去各分片回查完整字段(
where id in (...))。
-- 第一步:只取主键,减少网络传输 select id, create_time from t_order_0 order by create_time desc limit 0, 30; -- 第三步:拿到目标主键后回表 select * from t_order_0 where id in (1001, 1002, 1003);
相比方案一,网络传输量显著下降,但排序字段仍需随主键一起返回,且回表会产生额外的 rtt。适合字段较多、单行数据较大的宽表场景。
四、方案三:禁止跳页,改用游标分页(推荐)
这是生产环境最常用的方案。放弃「跳到第 n 页」,改为「从上一页最后一条之后继续取」,即基于游标(cursor)或 where id > last_id 的翻页方式。
-- 第一页 select * from t_order_0 where create_time <= '2026-10-22 00:00:00' order by create_time desc, id desc limit 10; -- 后续页:带上上一页最后一条的 create_time 和 id select * from t_order_0 where (create_time < '2026-10-21 18:30:00') or (create_time = '2026-10-21 18:30:00' and id < 10086) order by create_time desc, id desc limit 10;
跨分片时,每个分片各取 10 条,归并后取前 10 条,并把最后一条的 (create_time, id) 作为下一页的游标。由于每个分片只返回 size 条,数据量恒定,深分页性能不会退化。
注意:排序字段必须能唯一确定顺序,否则要用 (排序字段, 主键) 组成联合游标,避免同一时间戳下记录重复或遗漏。
代价是不能随机跳页,产品交互上通常表现为「加载更多」或「下一页」,而不是页码条。对于信息流、订单列表、日志查询这类场景,这几乎不是问题。
五、方案四:引入中间件或搜索引擎
当业务确实需要随机跳页、多条件组合筛选时,可以把分页能力下沉到专门的组件:
- shardingsphere:配置分片规则后,由中间件统一处理归并排序,业务代码无感知,但深分页性能仍受方案一限制;
- elasticsearch:把需要检索和排序的字段同步到 es,分页、聚合、多条件筛选都交给 es,mysql 只负责按主键回表;
- 数据仓库 / 宽表:通过 cdc 把分表数据汇聚到一张宽表或 olap 引擎,分页查询走分析型存储。
这类方案的共同点是「用空间和同步成本换查询灵活性」,适合数据量极大、查询维度多的中大型系统。
六、深分页优化:延迟关联与覆盖索引
无论采用哪种方案,单分片内部的深分页同样需要优化。经典做法是延迟关联:先用覆盖索引拿到主键,再回表。
-- 不推荐:offset 很大时扫描大量无用行
select * from t_order_0 order by create_time desc limit 1000000, 10;
-- 推荐:先用索引取主键,再关联回表
select o.* from t_order_0 o
inner join (
select id from t_order_0
order by create_time desc
limit 1000000, 10
) t on o.id = t.id;同时确保 order by 字段上有合适的联合索引,例如 (create_time, id),让排序走索引而不是 filesort。
七、方案对比与选型建议
| 方案 | 结果准确性 | 深分页性能 | 支持跳页 | 实现成本 | 推荐场景 |
|---|---|---|---|---|---|
| 全局视野法 | 准确 | 差 | 支持 | 低 | 分片少、浅分页 |
| 二次查询法 | 准确 | 一般 | 支持 | 中 | 宽表、字段多 |
| 游标分页 | 准确 | 好 | 不支持 | 低 | 信息流、列表类业务 |
| 中间件 / es | 准确 | 好 | 支持 | 高 | 多条件检索、大型系统 |
实践中的常见组合是:优先让查询带上分片键,把跨分片分页降级为单表分页;确实需要全局分页时,用游标分页替代页码跳转;只有在产品强依赖跳页时,才引入中间件或搜索引擎。
八、总结
分表后的分页查询没有银弹,本质是在「结果准确性、查询性能、交互灵活性、实现成本」之间做取舍。记住三条原则:
- 能带分片键就带分片键,让查询路由到单表;
- 能游标分页就不要深 offset,性能差距是数量级的;
- 必须跳页时,用中间件或搜索引擎承接,而不是在应用层硬扛。
到此这篇关于mysql分表后做分页查询的四种主流方案的文章就介绍到这了,更多相关mysql分表后做分页查询方案内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网!
发表评论