springboot集成ShardingSphere-JDBC（5.0.0-bata）

一、背景：因为项目需要，要求对两个大表进行分表，每个月分一个表，保证单表数据不会特别大，后续查询限定时间段，保证整体查询和统计性能。作为搞java的农民工，自然选择了java契合度比较高的分库中间件：shardingjdbc，使用也肯定使用最新版本5.0.0-beta，经过一顿折腾，终于分库成功，直接上代码。

二、开发环境：springboot(2.3.5.RELEASE)+mybatis-plus（3.4.3）+mysql（8.0.26）+shardingSphere-jdbc（5.0.0-beta）

三、依赖引入

        
        
            com.alibaba
            druid
            1.2.8
        
        
        
            com.baomidou
            mybatis-plus-boot-starter
            3.4.3
        
        
        
            mysql
            mysql-connector-java
            8.0.21
        
        
            org.apache.shardingsphere
            shardingsphere-jdbc-core-spring-boot-starter
            5.0.0-beta
        

说明：sharding不同版本很容易产生包冲突，sharding引入的自启动包就不能引入druid-spring-boot-starter和dynamic-datasource-spring-boot-starter依赖

四、application.yml文件配置

server:
  port: 8080
  tomcat:
    max-swallow-size: -1
  error:
    include-exception: true
    include-stacktrace: ALWAYS
    include-message: ALWAYS
  compression:
    enabled: true
    min-response-size: 1024
    mime-types: application/javascript,application/json,application/xml,text/html,text/xml,text/plain,text/css,image/*
spring:
  shardingsphere:
    enabled: true
    datasource:
    #指定数据源 名称可以自定义，注意：名称要跟后面的配置一致
      names: ds0
      #配置数据源的连接信息
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: root
        password: root
    rules:
      sharding:
        default-data-source-name: ds0
        props:
          #是否输出sql
          sql-show: true
        #绑定表规则列表,多个以逗号分割
        binding-tables[0]: device_pass_record,device_alarm_record
        tables:
          #设置device_pass_record表分表策略
          device_pass_record:
            actual-data-nodes: ds0.device_pass_record20$->{21..99}$->{1..12}
            table-strategy:
              standard:
                sharding-column: pass_month
                sharding-algorithm-name: sharding-pass-by-month
          #设置device_pass_record表分表策略
          device_alarm_record:
            actual-data-nodes: ds0.device_alarm_record20$->{21..99}$->{1..12}
            table-strategy:
              standard:
                sharding-column: pass_month
                sharding-algorithm-name: sharding-alarm-by-month
        #分库或者分表算法配置
        sharding-algorithms:
          #device_pass_record分表算法配置
          sharding-pass-by-month:
            type: INLINE
            props:
              algorithm-expression: device_pass_record$->{pass_month}
          # device_alarm_record分表策略配置
          sharding-alarm-by-month:
            type: INLINE
            props:
              algorithm-expression: device_alarm_record$->{pass_month}
#mybatis plus 设置
mybatis-plus:
  mapper-locations: classpath*:/org/grandhonor/community/mapper/xml/*Mapper.xml
  global-config:
    # 关闭MP3.0自带的banner
    banner: false
    db-config:
      #主键类型  0:"数据库ID自增",1:"该类型为未设置主键类型", 2:"用户输入ID",3:"全局唯一ID (数字类型唯一ID)", 4:"全局唯一ID UUID",5:"字符串全局唯一ID (idWorker 的字符串表示)";
      id-type: ASSIGN_ID
      # 默认数据库表下划线命名
      table-underline: true
      logic-delete-field: delFlag
      logic-delete-value: 1
      logic-not-delete-value: 0
  configuration:
    # 这个配置会将执行的sql打印出来，在开发或测试的时候可以用
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 返回类型为Map,显示null对应的字段
    call-setters-on-nulls: true

说明：本配置实现了 device_pass_record,device_alarm_record两个表，以pass_month字段作为分片健进行分表。ds0库其他表没有配置分片算法，默认不进行分表

五、扩展：后续可以添加多个数据源，实现横向分库分表，除了以上配置，需要配置分库策略 database-strategy，如下：

spring:
  shardingsphere:
    enabled: false
    datasource:
    #指定数据源 名称可以自定义，注意：名称要跟后面的配置一致
      names: ds0,ds1
      #配置数据源的连接信息
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding1?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: root
        password: root
      #配置数据源的连接信息
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding2?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: root
        password: root
    rules:
      sharding:
        props:
          #是否输出sql
          sql-show: true
        tables:
          #设置device_pass_record表分库分表策略
          device_pass_record:
            #需要进行分片的库中实际存在的表
            actual-data-nodes: ds$->{0..1}.device_pass_record20$->{21..30}$->{1..12}
            #分库策略
            database-strategy:
              standard:
                #根据id分库
                sharding-column: id
                sharding-algorithm-name: sharding-pass-database
            #分表策略
            table-strategy:
              standard:
                #根据pass_month分表
                sharding-column: pass_month
                sharding-algorithm-name: sharding-pass-by-month
        #分库或者分表算法配置
        sharding-algorithms:
          #分库策略配置
          sharding-pass-database:
            type: INLINE
            props:
              algorithm-expression: ds$->{id % 2}
          #分表策略配置
          sharding-pass-by-month:
            type: INLINE
            props:
              algorithm-expression: device_pass_record$->{pass_month}