分库分表架构下的分页查询解决方案与实践

1. 分库分表架构下的分页查询困境当数据库达到千万级数据量时单表查询性能会显著下降。去年我们电商系统就遇到了这个问题——订单表超过5000万条记录后最简单的分页查询都要花费3秒以上。这时候分库分表就成了必然选择但随之而来的就是分页查询这个看似简单的需求变得异常复杂。我见过不少团队在分库分表后发现原本流畅的分页功能突然变得卡顿甚至出错。典型症状包括页码跳转时出现重复数据每页显示数量不稳定深度分页如第100页查询超时排序结果与预期不符这些问题本质上是因为数据被分散到不同物理节点后传统的LIMIT offset, size分页方式完全失效了。下面这张对比表能清晰说明问题查询类型单库单表分库分表SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20直接定位到10000行开始取20条需要在每个分片取10020条再合并性能影响线性增长指数级恶化结果准确性完全准确可能出现重复/遗漏2. 分页查询的四种解决方案实践2.1 全局视野法简单但昂贵的方案这是最直观的解决方案——在所有分片执行完整查询然后在内存中合并排序。我们最初在订单系统就是这么实现的-- 每个分片执行 SELECT * FROM orders_${shard} ORDER BY create_time DESC -- 应用层合并所有结果后 ListOrder allResults mergeFromAllShards(); return allResults.stream() .skip(offset).limit(pageSize) .collect(Collectors.toList());警告当单分片数据量达到百万级时这种方案会导致JVM内存溢出。我们曾因此导致整个订单查询服务崩溃。适用场景数据量较小单分片10万条分片数量少2个对查询延迟不敏感的后台系统2.2 二次查询法平衡性能与准确性的选择这是目前我们生产环境采用的方案核心思想是先获取符合条件的ID集合再根据ID精确查询完整数据具体实现代码// 第一阶段获取分页ID ListLong ids shardJdbcTemplate.queryForList( SELECT id FROM orders WHERE status? ORDER BY create_time DESC LIMIT ?,?, Long.class, status, offset, pageSize); // 第二阶段获取完整数据 ListOrder orders shardJdbcTemplate.query( SELECT * FROM orders WHERE id IN (:ids) ORDER BY create_time DESC, new MapSqlParameterSource(ids, ids), new OrderRowMapper());性能对比测试环境数据数据量传统分页二次查询50万1200ms300ms500万超时800ms2.3 分片路由法需要业务配合的优化如果查询条件包含分片键如user_id可以直接路由到特定分片执行传统分页。我们用户中心的查询就采用了这种方式-- 根据user_id确定分片 SELECT * FROM orders_${user_id % 4} WHERE user_id ? ORDER BY create_time DESC LIMIT ?,?关键限制必须包含完整分片键不支持跨分片排序分片策略变更时需要数据迁移2.4 游标分页法适合无限滚动的场景类似于微博、抖音的加载更多模式使用上一页最后一条记录作为锚点// 请求参数包含last_id和last_create_time ListOrder orders shardJdbcTemplate.query( SELECT * FROM orders WHERE create_time ? OR (create_time ? AND id ?) ORDER BY create_time DESC, id DESC LIMIT ?, lastCreateTime, lastCreateTime, lastId, pageSize);优势完全避免offset问题性能稳定不受页码影响天然防翻页过程中的数据变动3. ShardingSphere的实践方案对于使用ShardingSphere的项目它提供了内置的分页优化方案。在我们的财务系统中这样配置spring: shardingsphere: props: max.connections.size.per.query: 5 # 控制每次查询的最大连接数 sql.show: true sharding: tables: t_order: actual-data-nodes: ds${0..3}.t_order_${0..15} table-strategy: inline: sharding-column: order_id algorithm-expression: t_order_${order_id % 16} database-strategy: inline: sharding-column: user_id algorithm-expression: ds${user_id % 4}关键配置项说明max.connections.size.per.query控制分片并行度allow.range.query.with.inline.sharding是否允许范围查询executor.size执行线程池大小实测发现当分片数超过8个时需要适当调大executor.size我们设置为分片数的1.5倍4. 分页查询的避坑指南4.1 排序字段选择的三原则我们踩过的坑曾经使用更新时间作为排序字段结果出现大量相同值导致分页混乱。现在强制要求必须包含主键作为次要排序条件优先选择值唯一的字段如创建时间避免使用频繁更新的字段错误示例-- 可能导致分页异常 SELECT * FROM orders ORDER BY update_time DESC LIMIT 100, 20正确写法SELECT * FROM orders ORDER BY create_time DESC, id DESC LIMIT 100, 204.2 深度分页的优化技巧对于必须支持跳转到任意页码的场景如后台管理系统我们采用ID分段缓存方案建立ID索引表CREATE TABLE order_id_index ( seq_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_id BIGINT, create_time DATETIME, INDEX idx_ct (create_time) );分页查询改为SELECT o.* FROM orders o JOIN ( SELECT order_id FROM order_id_index ORDER BY create_time DESC, order_id DESC LIMIT 10000, 20 ) tmp ON o.id tmp.order_id性能提升对比方案第10页(200条)第100页(2000条)直接分页450ms超时ID索引80ms120ms4.3 分布式事务下的特殊处理在支付系统中我们发现分页查询可能漏掉刚提交但未同步的数据。解决方案为所有写操作添加binlog监听查询时先检查本地事务快照必要时进行二次验证核心代码逻辑public ListOrder queryWithConsistency(String userId, int page) { ListOrder orders queryFromDB(userId, page); ListLong recentTxIds getRecentTransactions(userId); if (!recentTxIds.isEmpty()) { ListOrder supplemental querySupplementalOrders(recentTxIds); orders mergeResults(orders, supplemental); } return orders; }5. 性能压测数据参考我们在4分片、每分片500万数据的集群上进行了对比测试方案QPS平均延迟99分位延迟适用场景全局视野12850ms2.1s小数据量后台二次查询23545ms120ms通用场景分片路由18008ms15ms带分片键查询游标分页62022ms55msC端无限滚动硬件配置8核16G服务器SSD存储千兆网络一个有趣的发现当使用MyBatis-Plus的分页插件时需要特别注意拦截器顺序Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); // 必须先添加分片插件再添加分页插件 interceptor.addInnerInterceptor(new ShardingPaginationInnerInterceptor()); interceptor.addInnerInterceptor(new PaginationInnerInterceptor()); return interceptor; }配置错误会导致分页计算完全错误这是我们通过3次线上事故才排查出的问题。