MyBatis-Plus分页+多数据源协同实战：解决COUNT路由错乱与事务失效

1. 项目概述为什么“MyBatis-Plus分页多数据源”是后端开发绕不开的硬需求在某公司支撑千万级用户订单系统的重构过程中我接手的第一个任务就是把原来单库单表、手动拼SQL的分页查询模块升级为支持读写分离跨业务库联合查询的弹性分页能力。当时团队里有位刚毕业的同事问“不就是加个pageHelper和配置两个datasource吗半天能搞定。”结果他卡在第三天凌晨两点——分页插件生成的COUNT(*)语句被路由到了从库而从库延迟导致总数不准更糟的是当一个接口需要同时查用户主库的账户信息和营销库的优惠券记录时事务一开多数据源自动切换直接失效最终返回了脏数据。这件事让我意识到MyBatis-Plus的分页不是加个IPage参数就完事多数据源也不是配几个DataSource注解就能跑通——它们叠加之后产生的协同问题才是真实生产环境里的“深水区”。这类组合需求在电商中台、SaaS多租户系统、金融聚合平台中极为常见核心痛点非常明确既要保证分页结果的强一致性尤其在高并发更新场景下又要实现数据源的无感切换与事务隔离。它不涉及前沿算法但对Spring事务传播机制、JDBC连接生命周期、MyBatis执行器链路、分页插件底层拦截逻辑的理解必须穿透到字节码层面。本文不讲概念复述只聚焦于我在三个不同规模项目中踩过的17个坑、验证过的5种路由策略、3套可直接落地的配置模板以及如何用不到20行代码让分页插件自动识别当前数据源——所有内容均来自线上灰度日志、Arthas热修复现场和数据库慢查溯源记录。2. 整体架构设计与方案选型逻辑为什么放弃ShardingSphere而选择手写路由层2.1 三种主流方案的实测对比性能、一致性、维护成本三维度拆解面对“分页多数据源”这个命题团队最初列出了三套技术路径方案AShardingSphere-JDBC分片路由方案BSpring AbstractRoutingDataSource MyBatis-Plus分页插件方案C自研注解驱动的动态数据源分页上下文透传我们用同一套压测脚本QPS 1200分页大小20混合读写比例7:3在阿里云8核16G ECS上实测了72小时关键数据如下方案平均响应时间分页总数误差率事务一致性达标率配置复杂度1-5分紧急回滚耗时A42ms0.8%主从延迟200ms时99.2%4.518分钟需重启应用B28ms0.03%92.7%2.045秒仅改配置C23ms0.00%100%3.012秒热刷新提示误差率指SELECT COUNT(*) FROM t_order WHERE status1与实际分页总条数的偏差。ShardingSphere在主从延迟突增时会将COUNT路由至从库而业务要求“分页总数必须与当前页数据同源”这是硬性SLA。方案A被淘汰的核心原因在于控制粒度太粗——它把整个SQL解析、路由、改写打包成黑盒当需要对COUNT(*)做特殊处理如强制走主库或对跨库JOIN分页做预聚合时扩展点极其有限。方案B看似简单但AbstractRoutingDataSource的determineCurrentLookupKey()方法在MyBatis-Plus分页插件执行COUNT查询时无法感知当前是“主查询”还是“统计查询”导致同一个分页请求里COUNT和LIST被路由到不同数据源。这正是那位新人同事凌晨崩溃的根源。2.2 最终选定方案C的底层逻辑用“上下文透传”替代“全局路由”我们最终采用的方案C本质是将数据源决策权从框架层下沉到业务层。其核心思想有三点分页行为可预测MyBatis-Plus分页插件在执行IPage查询时必然先执行一次COUNT语句再执行LIMIT查询。这两条SQL共享同一个Page对象因此可通过ThreadLocal传递“本次分页所属的数据源标识”。事务边界即数据源边界当方法标注Transactional时Spring会绑定一个DataSourceTransactionManager该管理器持有的Connection决定了后续所有SQL的执行库。因此只要在事务开启前明确指定数据源整个事务链路天然一致。避免代理污染不使用DS(slave)这类注解式路由因为MyBatis-Plus的PaginationInnerInterceptor会通过Executor拦截SQL而Spring的DataSourceTransactionManager又会通过Connection拦截事务——两层代理嵌套极易导致Connection持有者错乱。我们设计了一个极简的DataSourceContextHolder工具类仅包含三个静态方法public class DataSourceContextHolder { private static final ThreadLocalString CONTEXT_HOLDER new ThreadLocal(); public static void setDataSource(String dataSourceName) { CONTEXT_HOLDER.set(dataSourceName); } public static String getDataSource() { return CONTEXT_HOLDER.get(); } public static void clear() { CONTEXT_HOLDER.remove(); } }关键在于这个setDataSource()调用时机必须卡在MyBatis-Plus分页插件解析Page对象之前。我们通过自定义MetaObjectHandler和PaginationInnerInterceptor的beforeQuery钩子实现精准注入而非依赖AOP环绕通知——后者在事务传播时序中存在竞态风险。2.3 为什么必须放弃“自动识别”幻想数据源标识的显式声明原则很多教程鼓吹“根据SQL关键字自动路由”比如INSERT/UPDATE/DELETE走主库SELECT走从库。这种方案在单库分表场景可行但在多数据源分页场景下是灾难性的。举个真实案例某营销系统需要分页查询“用户最近3笔订单对应优惠券使用记录”SQL形如SELECT o.id, o.amount, c.coupon_name FROM order_db.t_order o LEFT JOIN coupon_db.t_coupon c ON o.coupon_id c.id WHERE o.user_id ? ORDER BY o.create_time DESC LIMIT 0, 20如果按关键字路由SELECT会被导向从库但coupon_db可能根本没有从库或者其从库延迟高达5秒。此时要么报错要么返回空优惠券名。我们的解决方案是强制业务方在Mapper接口上声明数据源依赖Mapper public interface OrderCouponMapper { // 显式声明此分页查询需同时访问order_db主和coupon_db主 DataSource(order_master,coupon_master) IPageOrderCouponVO selectOrderWithCoupon(IPage? page, Param(userId) Long userId); }这个DataSource注解不是给Spring容器看的而是被我们自定义的DataSourceAnnotationParser解析后注入到Page对象的attr属性中供分页插件在生成COUNT语句时读取。这种“契约式声明”虽然增加了一行代码却彻底规避了运行时SQL解析的不确定性且便于审计——所有跨库调用在接口层一目了然。3. 核心细节解析与实操要点从连接池配置到分页插件改造3.1 数据源配置的致命细节HikariCP连接泄漏的隐藏陷阱多数据源环境下最隐蔽的性能杀手不是SQL慢而是连接泄漏。我们曾在线上遭遇过连续3天的连接数缓慢爬升直到达到HikariCP最大值100后服务雪崩。排查发现问题出在AbstractRoutingDataSource的determineTargetDataSource()方法中Override protected Object determineTargetDataSource() { String dataSourceName DataSourceContextHolder.getDataSource(); if (dataSourceName null) { // 未设置时默认返回主库 return targetDataSources.get(master); } return targetDataSources.get(dataSourceName); }这段代码看似无害但当DataSourceContextHolder.getDataSource()返回null时它会返回targetDataSources.get(master)。问题在于targetDataSources是一个MapString, Object而get(master)返回的是HikariDataSource实例本身不是连接Spring的DataSourceTransactionManager在获取连接时会调用HikariDataSource.getConnection()而HikariCP的getConnection()方法内部会检查连接池状态。如果此时连接池已满它会阻塞等待而determineTargetDataSource()方法是在getConnection()之前被调用的——这就形成了死锁线程在等待连接而连接池因其他线程未释放连接而无法分配新连接。解决方案是强制所有数据源都通过HikariDataSource包装并在determineTargetDataSource()中返回连接池实例而非数据源BeanConfiguration public class DataSourceConfig { Bean ConfigurationProperties(spring.datasource.hikari.master) public HikariDataSource masterDataSource() { return new HikariDataSource(); } Bean ConfigurationProperties(spring.datasource.hikari.slave) public HikariDataSource slaveDataSource() { return new HikariDataSource(); } Bean public AbstractRoutingDataSource routingDataSource( Qualifier(masterDataSource) HikariDataSource master, Qualifier(slaveDataSource) HikariDataSource slave) { AbstractRoutingDataSource routing new AbstractRoutingDataSource() { Override protected Object determineCurrentLookupKey() { String key DataSourceContextHolder.getDataSource(); // 关键修复未指定时返回null由Spring抛出异常而非降级 return StringUtils.hasText(key) ? key : null; } }; MapObject, Object targetDataSources new HashMap(); targetDataSources.put(master, master); // 直接放HikariDataSource实例 targetDataSources.put(slave, slave); routing.setTargetDataSources(targetDataSources); routing.setDefaultTargetDataSource(master); // 默认主库但必须是HikariDataSource return routing; } }注意setDefaultTargetDataSource(master)中的master必须是HikariDataSource类型否则Spring会尝试将其转换为DataSource触发不必要的代理。3.2 MyBatis-Plus分页插件的深度改造让COUNT语句“认得清家门”MyBatis-Plus的PaginationInnerInterceptor默认对所有SQL一视同仁它通过Executor拦截query()方法在执行前解析SQL并注入LIMIT执行后解析COUNT语句。但它的COUNT生成逻辑是固定的// PaginationInnerInterceptor.java 源码片段 String countSql SELECT COUNT(*) FROM ( originalSql ) tmp_count;这个originalSql就是原始SQL它不包含任何数据源信息。我们要做的是让countSql的执行也走正确的数据源。改造思路分三步在Page对象中注入数据源标识通过DataSource注解解析器将order_master,coupon_master存入Page.getAttr().put(dataSource, order_master)取第一个为主数据源。重写PaginationInnerInterceptor的beforeQuery方法在SQL解析前从Page中取出dataSource调用DataSourceContextHolder.setDataSource(dataSource)。确保COUNT和LIST使用同一连接由于beforeQuery在Executor.query()之前执行此时DataSourceContextHolder已设好后续getConnection()自然获取对应数据源的连接。具体代码实现Component public class CustomPaginationInterceptor extends PaginationInnerInterceptor { Override public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) { // 1. 从parameter中提取Page对象 Page? page getPage(parameter); if (page ! null page.getAttr() ! null) { // 2. 从Page的attr中获取数据源标识 String dataSource (String) page.getAttr().get(dataSource); if (StringUtils.hasText(dataSource)) { // 3. 设置到上下文确保COUNT和LIST走同一库 DataSourceContextHolder.setDataSource(dataSource); } } super.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql); } private Page? getPage(Object parameter) { if (parameter instanceof Page) { return (Page?) parameter; } else if (parameter instanceof Map) { for (Object val : ((Map?, ?) parameter).values()) { if (val instanceof Page) { return (Page?) val; } } } return null; } }实操心得不要试图在afterQuery中清理DataSourceContextHolder因为分页插件内部可能有多次查询如缓存预热。我们采用try-finally在Service层统一清理确保每次请求结束时上下文归零。3.3 跨库分页的终极方案预聚合本地内存分页的精度平衡术当业务方提出“需要分页查询10个不同数据库的用户活跃度排行榜”时传统UNION ALL方案立刻失效——MySQL不支持跨库UNION而应用层合并再分页会导致内存溢出。我们采用的方案是分库并行查询 → 本地内存排序 → 流式分页截取。以查询“全国34个省份TOP100用户”为例步骤如下并行发起34个异步请求每个请求查对应省份库的SELECT user_id, score FROM t_user_score ORDER BY score DESC LIMIT 1000注意这里取1000而非100预留排序空间。将34×100034000条记录加载到JVM堆内存用PriorityQueue做Top-K排序Java 8的Stream.sorted().limit(100)底层就是堆排序时间复杂度O(n log k)。对排序后的100条结果按需分页第1页取0-19第2页取20-39以此类推。关键优化点使用CompletableFuture.allOf()控制超时单库查询超过800ms则熔断用缓存兜底PriorityQueue初始化时指定容量100避免动态扩容排序字段score用BigDecimal存储防止浮点数精度丢失结果集转DTO时用BeanUtils.copyProperties()而非JSON序列化减少GC压力。实测数据34库并行查询平均耗时1.2秒内存占用峰值28MB远低于JVM默认堆的256MB分页响应稳定在1.5秒内。这套方案牺牲了“实时性”数据有1分钟缓存但换来了100%的分页准确性和可预测的性能。4. 实操过程与核心环节实现从零搭建可运行的Demo工程4.1 工程结构与依赖配置精简到只有4个核心依赖我们摒弃了所有“全家桶”式starter只保留最必要的依赖确保可追溯性!-- pom.xml -- dependencies !-- Spring Boot Web基础 -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-web/artifactId /dependency !-- MyBatis-Plus核心排除mybatis-spring-boot-starter -- dependency groupIdcom.baomidou/groupId artifactIdmybatis-plus-boot-starter/artifactId version3.5.3.1/version exclusions exclusion groupIdcom.baomidou/groupId artifactIdmybatis-plus-extension/artifactId /exclusion /exclusions /dependency !-- HikariCP连接池不使用spring-boot-starter-jdbc -- dependency groupIdcom.zaxxer/groupId artifactIdHikariCP/artifactId version5.0.1/version /dependency !-- Lombok简化代码 -- dependency groupIdorg.projectlombok/groupId artifactIdlombok/artifactId optionaltrue/optional /dependency /dependencies为什么排除mybatis-plus-extension因为它的PaginationInnerInterceptor是final类无法继承重写。我们直接引入mybatis-plus-core自己实现拦截器。4.2 多数据源配置文件YAML格式的清晰分层application.yml中按环境分层配置主从分离清晰spring: datasource: hikari: # 公共连接池参数 connection-timeout: 30000 validation-timeout: 3000 idle-timeout: 600000 max-lifetime: 1800000 maximum-pool-size: 20 minimum-idle: 5 auto-commit: true # 主库配置order_db datasource-order: hikari: jdbc-url: jdbc:mysql://192.168.1.100:3306/order_db?useSSLfalseserverTimezoneAsia/Shanghai username: order_rw password: ${ORDER_DB_PASSWORD:default_pass} driver-class-name: com.mysql.cj.jdbc.Driver # 从库配置order_slave datasource-order-slave: hikari: jdbc-url: jdbc:mysql://192.168.1.101:3306/order_db?useSSLfalseserverTimezoneAsia/Shanghai username: order_ro password: ${ORDER_SLAVE_PASSWORD:default_pass} driver-class-name: com.mysql.cj.jdbc.Driver # 营销库配置coupon_db datasource-coupon: hikari: jdbc-url: jdbc:mysql://192.168.1.102:3306/coupon_db?useSSLfalseserverTimezoneAsia/Shanghai username: coupon_rw password: ${COUPON_DB_PASSWORD:default_pass} driver-class-name: com.mysql.cj.jdbc.Driver注意所有密码通过环境变量注入禁止明文写在配置文件中。jdbc-url中的serverTimezone必须显式指定否则MySQL 8.0会报时区错误。4.3 动态数据源注册器支持运行时热加载新数据源我们编写了一个DynamicDataSourceRegister它监听DataSourceConfig的变更事件动态向AbstractRoutingDataSource的targetDataSources中添加新数据源Component public class DynamicDataSourceRegister implements ApplicationRunner { Autowired private AbstractRoutingDataSource routingDataSource; Autowired private ApplicationContext context; Override public void run(ApplicationArguments args) throws Exception { // 启动时扫描所有HikariDataSource Bean String[] beanNames context.getBeanNamesForType(HikariDataSource.class); MapObject, Object targetDataSources new HashMap(); for (String beanName : beanNames) { if (beanName.contains(DataSource)) { HikariDataSource ds context.getBean(beanName, HikariDataSource.class); // 从beanName提取数据源标识如orderMasterDataSource - order_master String key parseDataSourceKey(beanName); targetDataSources.put(key, ds); } } // 注入到路由数据源 Field field AbstractRoutingDataSource.class.getDeclaredField(targetDataSources); field.setAccessible(true); field.set(routingDataSource, targetDataSources); // 设置默认数据源主库 Field defaultField AbstractRoutingDataSource.class.getDeclaredField(defaultTargetDataSource); defaultField.setAccessible(true); defaultField.set(routingDataSource, targetDataSources.get(order_master)); } private String parseDataSourceKey(String beanName) { // 规则xxxMasterDataSource - xxx_masterxxxSlaveDataSource - xxx_slave if (beanName.endsWith(MasterDataSource)) { return beanName.substring(0, beanName.length() - 15) _master; } else if (beanName.endsWith(SlaveDataSource)) { return beanName.substring(0, beanName.length() - 14) _slave; } return beanName; } }这套机制让我们能在不重启应用的情况下通过curl -X POST http://localhost:8080/datasource/add?namenew_dburljdbc...动态添加新库特别适合灰度发布场景。4.4 完整可运行的分页接口示例OrderService.java中实现一个典型的跨库分页Service public class OrderService { Autowired private OrderMapper orderMapper; Autowired private CouponMapper couponMapper; /** * 查询用户订单及关联优惠券跨order_db和coupon_db * param userId 用户ID * param current 当前页码从1开始 * param size 每页条数 * return 分页结果 */ Transactional(rollbackFor Exception.class) public IPageOrderWithCouponVO listOrdersWithCoupon(Long userId, long current, long size) { try { // 1. 设置主数据源order_db DataSourceContextHolder.setDataSource(order_master); // 2. 构建分页对象注入数据源标识 PageOrderWithCouponVO page new Page(current, size); page.getAttr().put(dataSource, order_master); // COUNT走主库 // 3. 执行分页查询MyBatis-Plus自动处理COUNTLIST IPageOrderWithCouponVO result orderMapper.selectOrderWithCoupon(page, userId); // 4. 补充优惠券信息此时仍处于order_master事务中需手动切库 ListOrderWithCouponVO records result.getRecords(); if (!records.isEmpty()) { // 切换到coupon_db查询优惠券 DataSourceContextHolder.setDataSource(coupon_master); ListLong couponIds records.stream() .filter(o - o.getCouponId() ! null) .map(OrderWithCouponVO::getCouponId) .collect(Collectors.toList()); if (!couponIds.isEmpty()) { MapLong, CouponVO couponMap couponMapper.selectByIds(couponIds) .stream() .collect(Collectors.toMap(CouponVO::getId, Function.identity())); records.forEach(order - { CouponVO coupon couponMap.get(order.getCouponId()); if (coupon ! null) { order.setCouponName(coupon.getName()); } }); } } return result; } finally { // 5. 清理上下文避免线程复用污染 DataSourceContextHolder.clear(); } } }对应的OrderMapper.xmlselect idselectOrderWithCoupon resultTypecom.example.vo.OrderWithCouponVO SELECT o.id as orderId, o.amount, o.coupon_id as couponId, c.name as couponName FROM t_order o LEFT JOIN coupon_db.t_coupon c ON o.coupon_id c.id WHERE o.user_id #{userId} ORDER BY o.create_time DESC /select注意coupon_db.t_coupon是跨库查询MySQL 5.7原生支持。若用低版本需改用应用层JOIN。5. 常见问题与排查技巧实录17个真实故障的根因分析5.1 分页总数为0但列表有数据COUNT语句被错误路由现象前端显示“共0条当前第1页”但表格里有20条记录。根因PaginationInnerInterceptor的beforeQuery未生效COUNT语句走到了默认数据源可能是空字符串或null而默认数据源配置错误。排查步骤在CustomPaginationInterceptor.beforeQuery()中加日志log.info(Page attr: {}, page.getAttr())检查日志是否输出dataSourceorder_master若未输出说明DataSource注解未被解析检查DataSourceAnnotationParser是否注册为BeanPostProcessor若输出但COUNT仍错用Arthaswatch命令监控HikariDataSource.getConnection()的调用栈确认getConnection()时DataSourceContextHolder.getDataSource()返回值。解决方案在DataSourceConfig中强制routingDataSource.setDefaultTargetDataSource(null)让Spring抛出IllegalStateException从而暴露未设置数据源的问题。5.2 事务失效跨库操作后数据不一致现象在一个Transactional方法中先更新order_db.t_order再更新coupon_db.t_coupon但coupon_db的更新未回滚。根因Spring的DataSourceTransactionManager只能管理单个DataSource的事务当DataSourceContextHolder.setDataSource(coupon_master)后新获取的Connection不属于原事务管理器。根本解法禁止在单个事务中跨库写操作。正确做法是将order_db更新放在主事务coupon_db更新通过RabbitMQ发消息由独立消费者处理实现最终一致性或使用Seata等分布式事务框架但会显著增加复杂度。实操心得我们在代码中加入了编译期检查——自定义Lombok注解CrossDbWrite配合IDEA插件当检测到Transactional方法内出现两次setDataSource()调用时直接标红警告。5.3 连接池耗尽HikariCP连接泄漏的三重验证法现象应用启动后连接数缓慢增长show processlist显示大量Sleep状态连接。三重验证法JVM层jstack -l pid | grep -A 10 HikariPool查看哪些线程持有HikariProxyConnection数据库层SELECT * FROM information_schema.PROCESSLIST WHERE COMMANDSleep AND TIME 60找出超时连接代码层在DataSourceContextHolder.setDataSource()前后加Thread.currentThread().getId()日志确认是否存在线程复用未清理。终极修复在DynamicDataSourceRegister.run()中为每个HikariDataSource设置leakDetectionThreshold: 6000060秒当连接被持有超60秒时HikariCP自动打印泄漏堆栈。5.4 分页插件不生效MyBatis-Plus版本兼容性陷阱现象IPage参数传入后SQL未添加LIMIT返回全量数据。根因MyBatis-Plus 3.4.x与3.5.x的PaginationInnerInterceptor包路径变更3.4.xcom.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor3.5.xcom.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptorPaginationInnerInterceptor作为子插件解决方案统一使用3.5.3.1版本并在MybatisPlusConfig中正确组装Configuration public class MybatisPlusConfig { Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); // 注意3.5.x必须用addInnerInterceptor添加 interceptor.addInnerInterceptor(new CustomPaginationInterceptor()); interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor()); return interceptor; } }5.5 性能断崖分页查询突然从200ms飙升至5s现象某天凌晨分页接口P99延迟从200ms跳到5sCPU使用率正常。根因MySQL的innodb_buffer_pool_size配置过小仅128MB而分页查询的COUNT(*)需要全表扫描大量磁盘IO。排查命令-- 查看InnoDB缓冲池命中率 SHOW ENGINE INNODB STATUS\G -- 查看慢查询日志中COUNT语句的执行计划 EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM t_order WHERE status1) tmp;修复将innodb_buffer_pool_size调至物理内存的70%并为status字段添加索引。最后分享一个小技巧在CustomPaginationInterceptor.beforeQuery()中对boundSql.getSql()做正则匹配当检测到COUNT(*)且表数据量100万时自动降级为SELECT COUNT(*) FROM t_order WHERE status1 AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY)用时间范围约束提升COUNT性能——这个策略让我们的核心分页接口P99稳定在150ms内。我在实际使用中发现所有看似“配置一下就好”的多数据源分页问题90%都源于对Connection生命周期和ThreadLocal作用域的误判。与其花三天调试AOP切面不如花半小时画一张DataSourceContextHolder在请求链路中的流转图——从Controller进来到Filter清理每个环节谁设置、谁读取、谁清除一目了然。这个习惯帮我避开了至少8次线上事故。