Spring Boot项目里，Mybatis-Plus和ShardingJDBC多数据源整合的完整配置流程（含Druid连接池调优）

Spring Boot整合Mybatis-Plus与ShardingJDBC多数据源实战指南1. 环境准备与依赖配置在开始之前确保你已经具备以下基础环境JDK 1.8Maven 3.6MySQL 5.7Spring Boot 2.7.x核心依赖配置需要特别注意版本兼容性以下是经过验证的稳定版本组合dependencies !-- Spring Boot基础依赖 -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-web/artifactId /dependency !-- Mybatis-Plus核心依赖 -- dependency groupIdcom.baomidou/groupId artifactIdmybatis-plus-boot-starter/artifactId version3.5.3/version /dependency !-- ShardingSphere JDBC -- dependency groupIdorg.apache.shardingsphere/groupId artifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactId version5.2.1/version /dependency !-- 动态数据源 -- dependency groupIdcom.baomidou/groupId artifactIddynamic-datasource-spring-boot-starter/artifactId version3.5.2/version /dependency !-- Druid连接池 -- dependency groupIdcom.alibaba/groupId artifactIddruid-spring-boot-starter/artifactId version1.2.15/version /dependency !-- MySQL驱动 -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.30/version /dependency /dependencies提示建议使用dependencyManagement统一管理版本号避免潜在的版本冲突问题2. 数据源配置架构设计2.1 整体架构思路多数据源整合的核心在于处理好三层关系基础数据源层Druid连接池的物理数据源实例ShardingSphere层负责分库分表逻辑的路由与执行动态数据源层Mybatis-Plus的多数据源动态切换配置流程关键点需要排除Spring Boot自动配置的DataSourceAutoConfiguration确保ShardingSphere数据源能被动态数据源管理合理配置Druid连接池参数2.2 数据库表结构准备我们以订单表为例演示水平分表创建以下表结构-- 逻辑表结构实际会分成4个物理表 CREATE TABLE order_info ( id bigint NOT NULL COMMENT 主键ID, order_no varchar(32) NOT NULL COMMENT 订单编号, user_id bigint NOT NULL COMMENT 用户ID, amount decimal(10,2) DEFAULT 0.00 COMMENT 订单金额, status tinyint DEFAULT 0 COMMENT 订单状态, create_time datetime DEFAULT CURRENT_TIMESTAMP, update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci; -- 实际物理表按id取模分片 CREATE TABLE order_info_0 LIKE order_info; CREATE TABLE order_info_1 LIKE order_info; CREATE TABLE order_info_2 LIKE order_info; CREATE TABLE order_info_3 LIKE order_info;3. 核心配置实现3.1 application.yml完整配置spring: autoconfigure: exclude: - com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure - org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration datasource: dynamic: primary: master strict: false datasource: master: url: jdbc:mysql://localhost:3306/demo?useSSLfalseserverTimezoneAsia/Shanghai username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver druid: initial-size: 5 max-active: 20 min-idle: 10 max-wait: 60000 validation-query: SELECT 1 test-while-idle: true shardingsphere: datasource: names: sharding sharding: type: com.alibaba.druid.pool.DruidDataSource url: jdbc:mysql://localhost:3306/demo?useSSLfalseserverTimezoneAsia/Shanghai username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver druid: initial-size: 5 max-active: 20 min-idle: 10 rules: sharding: tables: order_info: actual-data-nodes: sharding.order_info_$-{0..3} table-strategy: standard: sharding-column: id precise-algorithm-name: orderHashMod sharding-algorithms: orderHashMod: type: HASH_MOD props: sharding-count: 4 key-generators: snowflake: type: SNOWFLAKE mybatis-plus: mapper-locations: classpath*:/mapper/**/*.xml configuration: map-underscore-to-camel-case: true log-impl: org.apache.ibatis.logging.stdout.StdOutImpl3.2 动态数据源配置类Configuration AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class}) public class DataSourceConfig { private static final String SHARDING_DATASOURCE_NAME sharding; Autowired private DynamicDataSourceProperties properties; Lazy Resource(name shardingSphereDataSource) private DataSource shardingDataSource; Bean public DynamicDataSourceProvider dynamicDataSourceProvider() { return new AbstractDataSourceProvider() { Override public MapString, DataSource loadDataSources() { MapString, DataSource dataSourceMap createDataSourceMap(properties.getDatasource()); dataSourceMap.put(SHARDING_DATASOURCE_NAME, shardingDataSource); return dataSourceMap; } }; } Primary Bean public DataSource dataSource(DynamicDataSourceProvider provider) { DynamicRoutingDataSource dataSource new DynamicRoutingDataSource(); dataSource.setPrimary(properties.getPrimary()); dataSource.setProvider(provider); return dataSource; } }4. 业务层实现4.1 实体类与MapperData TableName(order_info) public class OrderInfo { TableId(type IdType.ASSIGN_ID) private Long id; private String orderNo; private Long userId; private BigDecimal amount; private Integer status; private Date createTime; private Date updateTime; } Mapper public interface OrderMapper extends BaseMapperOrderInfo { DS(sharding) ListOrderInfo selectByUserId(Param(userId) Long userId); }4.2 Service层实现public interface OrderService extends IServiceOrderInfo { boolean createOrder(OrderCreateDTO dto); ListOrderInfo queryByUser(Long userId); } Service public class OrderServiceImpl extends ServiceImplOrderMapper, OrderInfo implements OrderService { Override public boolean createOrder(OrderCreateDTO dto) { OrderInfo order new OrderInfo(); // 设置订单属性... return save(order); } Override public ListOrderInfo queryByUser(Long userId) { return baseMapper.selectByUserId(userId); } }5. Druid连接池深度调优5.1 生产级参数配置spring: datasource: dynamic: datasource: master: druid: # 连接池大小配置 initial-size: 10 min-idle: 10 max-active: 50 max-wait: 60000 # 连接有效性检测 validation-query: SELECT 1 test-while-idle: true test-on-borrow: false test-on-return: false time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 # 监控统计 stat-view-servlet: enabled: true url-pattern: /druid/* login-username: admin login-password: admin filter: stat: enabled: true log-slow-sql: true slow-sql-millis: 1000 merge-sql: true5.2 关键参数说明参数推荐值说明maxActive50-100最大连接数根据服务器配置和并发量调整minIdle10-20最小空闲连接避免频繁创建连接maxWait1000-3000获取连接超时时间(ms)timeBetweenEvictionRunsMillis60000检测间隔(ms)minEvictableIdleTimeMillis300000最小空闲时间(ms)性能优化建议对于高并发系统适当增大maxActive和minIdle设置合理的超时时间避免连接泄漏开启慢SQL监控定位性能瓶颈6. 常见问题解决方案6.1 启动时报错处理问题1Failed to determine a suitable driver class解决方案spring: autoconfigure: exclude: - org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration问题2ShardingSphereDataSource cannot be cast to DruidDataSource确保配置了正确的Druid依赖dependency groupIdcom.alibaba/groupId artifactIddruid-spring-boot-starter/artifactId /dependency6.2 分片不生效排查检查DS注解是否标注在正确的方法上确认ShardingSphere配置的actual-data-nodes与实际表名匹配查看SQL日志确认是否执行了真实的分片SQL6.3 事务管理要点在多数据源环境下需要注意避免跨数据源的事务操作在需要事务的方法上明确指定数据源考虑使用分布式事务解决方案如SeataDS(sharding) Transactional(rollbackFor Exception.class) public void batchCreate(ListOrderInfo orders) { saveBatch(orders); }7. 性能监控与调优7.1 Druid监控中心配置spring: datasource: druid: stat-view-servlet: enabled: true url-pattern: /druid/* login-username: admin login-password: admin reset-enable: false web-stat-filter: enabled: true url-pattern: /* exclusions: *.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*访问http://localhost:8080/druid可查看数据源状态SQL监控URI监控慢SQL记录7.2 连接泄漏检测配置连接泄漏检测参数spring: datasource: druid: remove-abandoned: true remove-abandoned-timeout: 1800 log-abandoned: true注意此配置会影响性能建议仅在调试阶段开启8. 生产环境最佳实践分片键选择避免选择可能产生热点的字段作为分片键优先选择查询频率高的字段索引优化为分片键创建索引避免跨分片的复杂查询批量操作对于批量插入建议使用Mybatis-Plus的saveBatch方法控制单批次操作的数据量建议500-1000条/批DS(sharding) public void batchInsert(ListOrderInfo orders) { int batchSize 500; ListListOrderInfo partitions Lists.partition(orders, batchSize); partitions.forEach(this::saveBatch); }监控告警设置Druid连接池使用率告警监控慢SQL数量变化定期检查连接泄漏情况实际项目中我们通过合理配置Druid参数将系统在高并发场景下的数据库连接等待时间从平均200ms降低到了50ms以下。特别是在处理批量订单时采用分批次提交的方式避免了长事务问题。