SpringBoot + ShardingJdbc + MyBatisPlus 配置详解

SpringBoot + ShardingJdbc + MyBatisPlus 配置详解

1. 依赖配置

首先需要在pom.xml中引入必要的依赖：

<!-- Spring Boot 基础依赖 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!-- MyBatis Plus 依赖 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

<!-- Sharding-JDBC 依赖 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

<!-- 数据库连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.16</version>
</dependency>

<!-- MySQL 驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- Lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

2. application.yml 配置

2.1 数据源配置

spring:
  shardingsphere:
    datasource:
      names: ds0, ds0_slave, ds1, ds1_slave
      
      # 主库1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
        username: root
        password: 123456
        
      # 从库1
      ds0_slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3307/db0?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
        username: root
        password: 123456
        
      # 主库2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
        username: root
        password: 123456
        
      # 从库2
      ds1_slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3307/db1?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
        username: root
        password: 123456

2.2 分库分表配置

    sharding:
      # 分库策略
      default-database-strategy:
        inline:
          sharding-column: user_id
          algorithm-expression: ds${user_id % 2}
      
      # 表分片策略
      tables:
        order_info:
          actual-data-nodes: ds${0..1}.order_info_${0..1}
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: order_info_${order_id % 2}
          key-generator:
            column: order_id
            type: SNOWFLAKE
        
        user_info:
          actual-data-nodes: ds${0..1}.user_info_${0..1}
          table-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: user_info_${user_id % 2}

2.3 读写分离配置

      # 读写分离配置
      master-slave-rules:
        ds0:
          master-data-source-name: ds0
          slave-data-source-names: ds0_slave
          load-balance-algorithm-type: ROUND_ROBIN
        ds1:
          master-data-source-name: ds1
          slave-data-source-names: ds1_slave
          load-balance-algorithm-type: ROUND_ROBIN

2.4 其他配置

    # 打印SQL
    props:
      sql:
        show: true
    # 开启模糊表匹配
    tables:
      # 全局表配置（在每个数据库中都有完整的表结构和数据）
      dict_info:
        actual-data-nodes: ds${0..1}.dict_info
        key-generator:
          column: dict_id
          type: SNOWFLAKE

# MyBatis Plus 配置
mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml
  type-aliases-package: com.example.entity
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: AUTO
      logic-delete-value: 1
      logic-not-delete-value: 0

3. 自定义分片策略（Java配置）

如果需要更复杂的分片策略，可以通过Java代码实现：

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * 自定义分片算法
 */
public class CustomDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String columnName = shardingValue.getColumnName();
        Long value = shardingValue.getValue();
        
        // 根据user_id进行分片，偶数分到ds0，奇数分到ds1
        if ("user_id".equals(columnName)) {
            return "ds" + (value % 2);
        }
        return availableTargetNames.iterator().next();
    }
}

4. 配置类（Bean配置方式）

import org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.context.annotation.Bean;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;

/**
 * MyBatis Plus 配置
 */
@SpringBootConfiguration
public class MyBatisPlusConfig {

    /**
     * 添加分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

5. 实体类定义

import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import java.time.LocalDateTime;

/**
 * 订单实体类
 */
@Data
@TableName("order_info")
public class OrderInfo {

    @TableId(type = IdType.ASSIGN_ID)
    private Long orderId;
    
    private Long userId;
    
    private String orderNo;
    
    private BigDecimal amount;
    
    private Integer status;
    
    private LocalDateTime createTime;
    
    private LocalDateTime updateTime;
}

6. Mapper接口

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.OrderInfo;
import org.apache.ibatis.annotations.Mapper;

/**
 * 订单Mapper
 */
@Mapper
public interface OrderInfoMapper extends BaseMapper<OrderInfo> {
    // 可以添加自定义查询方法
}

7. Service层实现

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.entity.OrderInfo;
import com.example.mapper.OrderInfoMapper;
import com.example.service.OrderInfoService;
import org.springframework.stereotype.Service;

/**
 * 订单服务实现
 */
@Service
public class OrderInfoServiceImpl extends ServiceImpl<OrderInfoMapper, OrderInfo> implements OrderInfoService {
    // 可以添加自定义业务逻辑
}

8. Controller实现

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.entity.OrderInfo;
import com.example.service.OrderInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * 订单控制器
 */
@RestController
@RequestMapping("/api/order")
public class OrderInfoController {

    @Autowired
    private OrderInfoService orderInfoService;

    /**
     * 创建订单
     */
    @PostMapping
    public OrderInfo createOrder(@RequestBody OrderInfo orderInfo) {
        orderInfoService.save(orderInfo);
        return orderInfo;
    }

    /**
     * 查询订单列表
     */
    @GetMapping
    public List<OrderInfo> getOrderList(@RequestParam Long userId) {
        QueryWrapper<OrderInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("user_id", userId);
        return orderInfoService.list(queryWrapper);
    }

    /**
     * 查询订单详情
     */
    @GetMapping("/{orderId}")
    public OrderInfo getOrderDetail(@PathVariable Long orderId) {
        return orderInfoService.getById(orderId);
    }
}

9. 常见问题与解决方案

9.1 版本兼容性问题

<!-- 注意版本匹配，避免启动报错 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version> <!-- 与Spring Boot 2.5.x兼容 -->
</dependency>

9.2 分片键不存在问题

确保所有涉及分片的SQL操作都包含分片键，或者在配置中指定默认分片策略。

9.3 读写分离数据不一致

设置合理的事务隔离级别，在需要强一致性的场景下，可以强制走主库查询：

// 在Service方法上添加注解强制走主库
@DS("ds0") // 指定数据源
public OrderInfo getOrderById(Long orderId) {
    return baseMapper.selectById(orderId);
}

9.4 性能优化建议

1. 合理设计分片键，避免热点数据
2. 使用复合分片键分散数据分布
3. 合理设置连接池参数
4. 对频繁查询的字段建立索引
5. 避免跨库联表查询，尽量在应用层处理

10. 高级特性

10.1 分布式事务支持

<!-- 添加分布式事务依赖 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-transaction-xa-core</artifactId>
    <version>4.1.1</version>
</dependency>

10.2 广播表配置

广播表是在所有分片库中都存在的表，适用于数据量小且需要跨库关联的表：

spring:
  shardingsphere:
    sharding:
      broadcast-tables: dict_info, config_info

通过以上配置，您可以实现SpringBoot、ShardingJdbc和MyBatisPlus的完整整合，实现分库分表和读写分离功能，有效应对大数据量场景下的数据库性能挑战。