分库分表后分页查询的坑与解法：从数据库到ES深分页实战

分页查询是 CRUD 系统里最基础的能力平时写 limit offset 眼睛都不眨一下。但当你把一张千万级的大表按用户维度或者按订单维度拆成 128 张甚至更多张分表之后再回头看看当初那个朴素的分页语句——直接原地裂开。这不仅仅是“查询慢”的问题更核心的矛盾是每一张分表返回的局部前 10 条合在一起根本不是全局前 10 条。我这两年干的最多的事就是和大规模数据的存储计算打交道分库分表后的分页查询这个坑踩了无数遍也积累了一些相对成熟的解法。这篇文章就把我实际测试过、上线跑过流量的一些方案和细节整理出来重点讲讲“分库分表下分页为什么会错”“有哪些可落地的方案”“每个方案适合什么场景”顺便把最近很多人在问的“ Java 对接 Elasticsearch 分页查询超过 10000 条的处理”也一并讲透。文字偏工程实操向有一些代码片段但不会堆源码核心是思路和参数。1. 分库分表之后为什么普通分页直接废了1.1 你需要先理解查询下推的语义错乱在没有分库分表之前一个分页查询的语义非常清晰SELECT * FROM t_order ORDER BY create_time DESC LIMIT 0, 10;数据库层面负责把全量数据按 create_time 排序然后只取出前 10 条返回逻辑和语义完全一致。分库分表改造完成之后这张 t_order 被按 order_id 的哈希值拆分成了 32 张物理表分散在 4 台或者 16 台机器上。此时同样的查询发送到中间件或者应用层如果中间件做的是“查询下推结果合并”每个分表都会执行一次ORDER BY create_time DESC LIMIT 0, 10各自返回自己那 10 条。中间件把所有分表的结果合并后再统一做一次排序取前 10 条。问题就出在这里每个分片只保留自己那部分数据里的 Top 10但全局 Top 10 很可能全部集中在某一个分片上其它分片返回的前 10 条数据也许真正排名在 100 名开外。最终合并排序的结果自然就是错的。我举个极端例子帮你建立直觉。假设 A、B、C 三张分表全局数据按时间排序最新的 10 条订单全部落在 A 表里B 和 C 表里最新的一条在全局排名已经是第 50 名了。此时每张分表各自取 Top 10中间件合并后一共拿到 30 条数据再排序取前 10。A 表会贡献 10 条正确数据B、C 表的 20 条全都是“虚假的局部 Top”虽然排序后前 10 仍然大概率是 A 表的那 10 条看起来结果没错但这个“没错”完全是侥幸。一旦全局 Top 10 分散在多张分表中计算偏差立刻暴露。1.2 偏移量的病态放大效应你以为只有第一页会出错其实第一页往往侥幸能看真正致命的是深翻页。当查询到了第 100 页即LIMIT 990, 10时我们最常见的做法是把 offset 下推给各个分片让每个分片执行LIMIT 990, 10。这会造成两个问题每个分片都需要扫描并丢弃 990 条数据然后拿出 10 条返回。32 个分片加起来实际扫描丢弃的记录数是 990 × 32 31680是单表单库场景的 32 倍。越靠后的页中间件需要汇聚的数据量越大排序耗时呈线性甚至超线性增长。如果中间件不允许下推 offset而是要求每个分片返回最大量的数据再归并那内存和网络开销直接爆掉查询接口的延迟会从几十毫秒飙升到几秒甚至直接把中间件内存打满。1.3 In 查询引起的笛卡尔积放大还有一个很多人没意识到的问题分库分表后为了拼装最终结果业务查询往往需要关联查询。比如先查 order 表拿到一批订单 ID再用 id IN (...) 去查 order_detail 表。由于两张表的分片键一致这个 IN 查询会被路由到不同的分片分别执行。但中间件在合并时可能对子查询结果做笛卡尔积式处理数据量瞬间扩大排序和分页的耗时也跟着成倍增长。这些因素叠加带来的直观结果就是分了表之后分页接口要么结果错要么性能烂两者总要占一个。理解了这个问题才能明白后面所有方案的出发点。2. 方案选型不同场景下什么才是真正可落地的分页方案2.1 禁止深分页只提供“游标式”翻页这是目前业界最推荐、应用最广泛的方案没有之一。互联网产品中用户的翻页行为高度集中在前面几页。用户翻到 100 页以后的情况在绝大多数 2C 场景下都可以忽略。既然深分页是用户根本不会触达的场景那最好的做法就是从产品层面干掉它。具体实现上可以使用“游标分页”替代“偏移量分页”。应用层接收到请求时携带上一页最后一条记录的唯一排序 ID查询时把它转成带条件的位置查询-- 客户端传入 last_seen_id 10086 SELECT * FROM t_order WHERE order_id 10086 ORDER BY order_id DESC LIMIT 20;这个方案在分库分表下的执行效果极好每个分片只需要执行WHERE order_id ?的索引范围扫描直接砍掉大偏移量的 Scan 开销。中间件只需要收集各分片返回的 20 条数据归并排序后取前 20 条数据量恒定为“单页大小 × 分片数”。查询性能和偏移量完全无关第一页和第五百页的耗时几乎一致。代价也很明显不能自由跳页只能上一页下一页。这个需要提前和产品经理沟通好给用户提供“加载更多”而不是“跳页”的交互形态。电商订单列表、消息列表、支付流水这些场景用户根本不需要真正跳到第 500 页用游标分页体验反而更顺。2.2 允许跳页但把查询范围物理缩小确实有一些场景比如运营后台的订单筛选、财务对账用户就想要跳页而且查询条件可能多达十几个。这种后台场景的数据量通常比前台小但也存在跨分片查询。这时候我见过比较有效的做法是引入“按维度前置分片”的结构。比如所有后台查询都强制携带一个“商家 ID”或者“门店 ID”而分库分表的分片键恰好也是商家 ID那么查询天然只会路由到一个分片这就不存在跨分片分页的问题了。如果确实做不到按分片键过滤我会对后台查询做“时间兜底限制”。比如订单查询默认只能查最近 90 天的数据并且分页深度不能超过 200 页。在这个前提下允许中间件把偏移量下推各分片统一归并配合全局排序取模性能勉强可接受。需要注意一个细节禁止不带任何强制过滤条件的全表跨分片跳页查询。这种查询一旦上线流量冲击下你连优化空间都没有中间件直接就扛不住了。2.3 全表扫描归并适合数据总量可控、分片数少的情况如果业务确实需要全局跳页而且数据量不大比如几百万级、分片数 8 个以内最朴素但足够正确的方法就是每个分片都查全量满足条件的数据的主键和排序字段中间件统一归并排序再决定取哪些主键最后去分片回表捞全行。// 伪代码思路 ListShard shards getAllShards(); ListPairLong, Date allKeys new ArrayList(); for (Shard shard : shards) { // 每个分片只查 id 和排序字段避免回表 allKeys.addAll(shard.query(SELECT id, create_time FROM t_order WHERE ...)); } allKeys.sort((a, b) - b.createTime.compareTo(a.createTime)); ListPairLong, Date pageKeys allKeys.subList(offset, offset pageSize); // 根据主键路由到对应分片批量回表 ListOrder orders batchGetByIds(pageKeys.stream().map(id - id).collect(toList()));这个方案在两个前提下表现良好每个分片返回的数据总量可控最好控制在十万条以内不然网络传输和内存排序会成为瓶颈。分片数少总数据量没有大到恐怖的程度。常见的失败场景是千万级大表分 64 个分片每个分片扫全量数据一下捞几十万条主键到应用层排序不管用什么语言写内存都吃不消延迟也直线上升。所以这个方案我只会在“数据总量 ≤ 500 万”“分片数 ≤ 8”的内部系统里用对外的用户系统我基本不用它。2.4 二次查询法搜索领域和中间件项目的经典思路大型中间件在处理跨分片排序分页时经常会用到“二次查询法”。核心思路是先猜出全局目标页码的数据分布区间再精确补齐数据。不过这个方案对中间件能力要求高需要支持自定义合并策略并且在业务快速变化时维护成本不低。二次查询法的简化逻辑是这样的假设要查第 100 页每页 10 条全局需要第 991 到 1000 条数据每个分片返回LIMIT 991, 10也就是每个分片返回自己分区中的第 991 到 1000 条数据。中间件把所有分片返回的数据收集起来找出这些数据中全局最小值和最大值。根据最小值和最大值区间去各分片查询区间内数据的完整集合以及每个值的总量。通过总量计算可以精确推断出目标页的完整数据范围再次查询补齐。这过程描述起来很绕但它确实能以相对更少的代价得到正确结果。遗憾的是大部分业务团队不会自己实现这套逻辑而是直接依赖成熟的中间件能力比如 ShardingSphere 的归并引擎。这里就不展开中间件源码了只是让你知道如果你用的是中间件方案它的底层原理大概率就是类似思路。3. 一个容易踩的价值陷阱性能优化前先想清楚“要不要在数据库层做分页”我个人强烈建议从架构层面重新审视分页需求能异步就异步能离线就离线别都压在数据库查询上。这是我们团队最深刻的一个教训。之前我们有一个管理后台的报表页需要按天查看全量订单的明细分页数据数据量大概 3000 万分布在 32 张分表里。一开始我们用 ShardingSphere 做全局分页用户翻页时实时查询结果每次翻到十几页以后接口延迟就飙到 4 秒以上DBA 天天来问是不是又跑大查询了。后来我们做了一个很小的改动直接把问题解决了每天凌晨用异步任务把昨天的订单明细按查询维度生成一份扁平化的快照表冗余存储到单独的 ES 或者宽表里。后台查询不再实时分库分表而是查快照。快照表虽然也是大表但没有分片分页查询完全走单库单表常规逻辑几百毫秒内搞定。把所有“深度查询 复杂筛选 跨分片”的需求全部拉到独立的查询引擎数据库只负责基于分片键的精确点查和短事务。这个思路在分库分表后不是“锦上添花”而是“必需品”。4. 实战Java 对接 Elasticsearch 实现大数据量分页超过 10000 条4.1 ES 的 10000 条限制到底是什么“Java 使用 ES 分页查询超过 10000”这句话翻译成技术问题其实是ES 的 from size 分页机制单次查询最大不能越过 index.max_result_window 的限制默认值是 10000。也就是说你直接用 from 10000, size 10或者 from 9995, size 10只要 from size 10000ES 就会直接报错Result window is too large, from size must be less than or equal to: [10000]很多同学第一次踩到这个坑时第一反应是“调大 max_result_window 不就行了”。比如调成 100000等于告诉 ES 允许 from size 最大 100000。这我不会拦着你但是希望你先想清楚两点ES 的 from size 深分页本质上要维护一个全局优先队列from 越大需要丢弃的数据越多性能和内存开销急剧上升。普通用户的正常翻页根本不会超过 10000 条被迫突破这个限制的要么是爬虫式的全量抽取场景要么是业务上错误的深翻页需求。所以正确的思路不是和 limit 较劲而是换分页方式。4.2 方案一search_after PIT推荐适合深度游标ES 官方推荐的翻页方式是 search_after它会基于上一页的排序值继续向后查找避免维护大偏移量。这套方案可以安全地翻到百万条以后。Java High Level REST Client 和 Elasticsearch Java API Client新客户端都支持 search_after但要注意必须配合 PITPoint In Time使用否则在翻页过程中索引数据发生变更会出现数据错乱或重复。核心思路是这样// 1. 开启 PIT指定 keep_alive 时间 OpenPointInTimeRequest pitRequest new OpenPointInTimeRequest(your_index); pitRequest.keepAlive(TimeValue.timeValueMinutes(5)); OpenPointInTimeResponse pitResponse client.openPointInTime(pitRequest, RequestOptions.DEFAULT); String pitId pitResponse.getPointInTimeId(); // 2. 第一次查询使用 PIT sort SearchSourceBuilder sourceBuilder new SearchSourceBuilder(); sourceBuilder.query(QueryBuilders.termQuery(status, SUCCESS)); sourceBuilder.sort(create_time, SortOrder.DESC); sourceBuilder.sort(_shard_doc, SortOrder.ASC); // 保证唯一排序 sourceBuilder.size(20); sourceBuilder.pointInTimeBuilder(new PointInTimeBuilder(pitId)); SearchRequest searchRequest new SearchRequest(); searchRequest.source(sourceBuilder); SearchResponse response client.search(searchRequest, RequestOptions.DEFAULT); // 3. 取最后一条的 sort 值 Object[] lastSort response.getHits().getAt(response.getHits().getHits().length - 1).getSortValues(); // 4. 下一页直接 search_after sourceBuilder.searchAfter(lastSort); SearchResponse nextResponse client.search(searchRequest, RequestOptions.DEFAULT);几个要点sort 字段必须有全局唯一值否则使用_shard_doc兜底。如果只用 create_time 排序同一毫秒内多条数据时会出现重复或遗漏。PIT 的有效期要大于整个翻页过程的耗时翻完需要手动关闭 PIT。search_after 不支持随机跳页只能一页页往后翻。这一点和数据库游标一样但正因为如此它的效率远高于 fromsize且不受 10000 限制。4.3 方案二Scroll适合全量导出不适合交互式翻页Scroll 是非常古早的深分页方案原理是服务端生成一个快照游标客户端通过游标持续拉取数据。SearchRequest searchRequest new SearchRequest(your_index); SearchSourceBuilder sourceBuilder new SearchSourceBuilder(); sourceBuilder.query(QueryBuilders.matchAllQuery()); sourceBuilder.size(1000); // 推荐一批 1000 条 searchRequest.source(sourceBuilder); searchRequest.scroll(TimeValue.timeValueMinutes(3)); SearchResponse response client.search(searchRequest, RequestOptions.DEFAULT); String scrollId response.getScrollId(); while (response.getHits().getHits().length 0) { // 处理本批数据 SearchScrollRequest scrollRequest new SearchScrollRequest(scrollId); scrollRequest.scroll(TimeValue.timeValueMinutes(3)); response client.scroll(scrollRequest, RequestOptions.DEFAULT); }Scroll 的坑在于它是快照读在 scroll 生命周期内的数据更新不会反映到结果里适合离线导数据不适合在线查询。会占用 ES 服务端资源长轮询的 scroll 多了之后旧节点内存不友好。不建议在用户端交互式翻页场景使用。我的建议是如果你是在写一个把全量数据从 ES 导出的离线任务或者从 ES 迁移数据到另一个存储用 Scroll 没毛病。如果用户在线一页页翻老老实实用 search_after。4.4 方案三调大 max_result_window不推荐但有适用范围如果业务方硬要随机跳页且数据总量不大比如最多就几万条在明确知晓代价后可以通过调大限制暂时止血PUT your_index/_settings { index.max_result_window: 50000 }这个配置确实能在 fromsize 模式下支持到 5 万条以内的随机翻页。但你要意识到深分页的内存成本是巨大的ES 每个分片上都要维持大小为 fromsize 的优先队列5 万条的查询会让协调节点的内存压力明显上升。一旦并发起来GC 和熔断都有可能发生。所以我的实际态度是这个设置只能用在内部查询、数据量明确可控、并发几乎为零的场景。对外的用户端查询谁提议调这个参数我都要多问一句是不是产品逻辑有问题5. 配置与参数背后的门道分页性能调优离不开的细节5.1 max_result_window 设置的边界验证设置 ES 参数之前建议先做一个很简单的推算。假设你的索引有 20 个分片每批查询 size 10那么协调节点需要排序的数据规模大概是20分片 × (from size)如果 from 10000size 10协调节点需要处理的数据规模就是 20 × 10010约 20 万条。这个规模还好但是如果并发 20 个这样的查询协调节点要同时维护 400 万条的排序队列内存直接告急。所以当你看到max_result_window设置过大时先算算这个乘法基本就知道后果了。5.2 数据库侧的 offset 下推 vs 归并取模在用 ShardingSphere 这类中间件时有一个配置会影响分页查询的准确度查询下推时offset 是怎么处理的。有些中间件为了性能会把 offset 下推给分片同时假设“每片同样稀疏”来取模合并。在数据分布均匀的情况下结果往往是正确的一旦某一片的数据量明显偏少合并结果就会错位。如果你在中间件配置里见到 “每个分片返回 offset size” 或 “先归并后取模”的选项务必测试一下数据分布极端情况。我当时就因为没注意这个出现过线上用户分页数据在一批订单里反复出现的情况。5.3 排序字段的唯一性不管在数据库分页还是 ES 分页里这个坑都值得单独拎出来说。假设排序字段是 create_time业务里同一毫秒内插入了 10 条订单。分页查询第一期返回了这 10 条中的前 5 条第二期再次按 create_time 排序时这 10 条数据的顺序在数据库底层可能是任意返回的。于是第二期可能又返回了第一期已经展示过的数据用户看到的就是“重复数据”。解决办法就是加一个唯一的二级排序字段比如ORDER BY create_time DESC, id DESC。在 ES 里则是 sort 里同时加上_shard_doc或_id。这一点说起来简单但实际工作中我看到太多系统因为漏了它被用户投诉“分页重复”。6. 常见问题排查与运维实录6.1 翻页结果错乱、重复、丢数据这是分布式分页里最频发的问题排查顺序我推荐是先确认排序字段是否加了唯一二级排序。再确认查询是否跨多个分片如果跨分片看中间件的归并策略。再看分页过程中是否有数据写入。如果有写入判断是否要引入一致性快照ES 里用 PIT数据库里可以用只读副本或开启事务隔离级别。最后检查你是否用了多个维度同时排序比如 order by 时间 金额的组合排序要保证排序条件稳定且可比较。6.2 ES 10000 分页报错如果你是 Java 客户端报错信息一般在带有Result window is too large的异常里。处理步骤确认业务能否改成游标翻页search_after PIT能改就优先改。如果是离线全量导出改用 Scroll。如果只是后台小范围临时查询才考虑调大 max_result_window。调大参数后一定要关注协调节点的堆内存和 GC必要时控制并发。6.3 中间件分页偶发超时分库分表中间件在分页场景下超时大多数不是中间件本身性能差而是单个分片执行了过重的深分页查询。这种时候不要急着提高超时时间先把 SQL 拿下来EXPLAIN 看看分片上的执行计划确认排序字段有没有走索引以及每个分片是否真的只执行了必要的逻辑。有可能你的一条分页查询经过中间件改写后在分片上执行的是全表扫描。遇到这种情况优先考虑给排序字段建联合索引(查询条件字段, 排序字段, 主键)而不是把超时从 3 秒调到 10 秒。6.4 分片键选错导致的数据倾斜分片键如果选得不好比如按订单状态分片会导致 “已完成” 状态的订单全部堆在一个分片上查询时大量局部数据集中在一个分片全局分页时这个分片返回的数据容易垄断结果页其它分片数据很少出现。这种情况不是换分页方式能解决的得在设计期就把分片键的选择和查询模式放一起想。如果已经上线了要么接受局部倾斜带来的结果分布不均要么做二次分片。6.5 页面跳页和实际数据总量对不上有一种情况经常出现在后台报表里分页接口返回总条数是按每个分片 count 的结果累加的但是分页深度超过某个值后某些分片的数据被过滤条件排除导致总条数和实际可翻页数不一致。排查时先确认 count 的统计口径是不是也走全局聚合如果 count 是独立统计的和查询结果集不一致完全正常。如果业务要求严格一致就需要在同一个查询里做 count 和数据的归并这会对性能产生额外影响。我的建议是页面上显示“约 XX 条”而不是精确条数绝大多数后台场景都可以接受。7. 最后分享一个我常用的架构决策套路每到要做分页需求评审的时候我都会按下面这个顺序来思考用户真的需要随机跳页吗如果需要频次高吗这个查询能不能通过强制过滤条件缩小到单分片能不能不做实时查询改成异步导出或快照查询如果用中间件能不能接受它的归并策略和额外开销权衡下来绝大多数业务场景都会收敛到“游标翻页 快照查询”的组合方案。只有极少数真正需要全局深翻页的后台内部系统才需要硬啃归并排序的性能问题。如果你现在正陷在“分页结果不对”或者“ES 查询超过 10000 条报错”的坑里按我上面给的几条路线先确定自己的业务场景再选对应的方案大概率一晚上就能支撑起一套新的实现。你要想清楚的是深分页本身不是一个“功能需求”而是业务设计上值得重新审视的入口很多时候砍掉它比优化它更划算。