springboot集成mysql、mybatis-plus、shardingsphere-jdbc实现读写分离、分表：VOLUME_RANGE基于分片容量的范围分片算法

详情请见上一篇读写分离

《springboot集成mysql、mybatis-plus、shardingsphere-jdbc实现读写分离》

警告：一定要先创建好表！！！

提供yml和properties2种配置

yml

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml

spring:
  shardingsphere:
    props:
      sql-show: true
    datasource:
      names: master,slave1
      master:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://192.168.1.11:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://192.168.1.22:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    rules:
      readwrite-splitting:
        data-sources:
          myds:
            static-strategy:
              write-data-source-name: master
              read-data-source-names:
                - slave1
            load-balancer-name: round-robin
        load-balancers:
          round-robin:
            type: ROUND_ROBIN
      sharding:
        tables:
          user:
          	#数据库表
            actual-data-nodes: myds.user_$->{0..1}
            table-strategy:
              standard:
              	#基于分片容量的范围分片算法的表的字段
                sharding-column: num
                #使用的算法别名
                sharding-algorithm-name: user-volume-range
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        key-generators:
          snowflake:
            type: SNOWFLAKE
        sharding-algorithms:
          user-volume-range:
          	#基于分片容量的范围分片算法
            type: VOLUME_RANGE
            props:
              #范围下界，超过边界的数据会报错，存储到数据库的数据会小于range-lower定义的值
              range-lower: '6'
              #范围上界，超过边界的数据会报错，存储到数据库的数据会小于range-upper定义的值
              range-upper: '11'
              #分片容量=range-upper -1 / 表的数量
              sharding-volume: '5'

properties

#打印sql
spring.shardingsphere.props.sql-show=true
# 配置真实数据源
spring.shardingsphere.datasource.names=master,slave1
# 配置第 1 个数据源
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.1.11:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.1.22:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

#写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.write-data-source-name=master
#读数据源名称，多个从数据源用逗号分隔。如：slave1,slave2
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.read-data-source-names=slave1
#负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=round_robin
#负载均衡算法类型
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN

#分表
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=myds.user_$->{0..1}
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=num
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=user-volume-range
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=snowflake
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.sharding-algorithms.user-volume-range.type=VOLUME_RANGE
#不要加''单引号或者""双引号包住数字
spring.shardingsphere.rules.sharding.sharding-algorithms.user-volume-range.props.range-lower=6
#不要加''单引号或者""双引号包住数字
spring.shardingsphere.rules.sharding.sharding-algorithms.user-volume-range.props.range-upper=11
#不要加''单引号或者""双引号包住数字
spring.shardingsphere.rules.sharding.sharding-algorithms.user-volume-range.props.sharding-volume=5

测试

数据库表：

数据库表字段：分片容量只是用来测试的字段，不一样要跟我一样，也可以自增ID等，是数值类型即可。

写入master主库：localhost:88/user/insert

	@PutMapping("insert")
    public boolean insert(){
        Collection<User> users = new ArrayList<>();
        for (int i = 0;i<11;i++){
            User user = new User();
            user.setUsername("Meta39");
            user.setNum(i);
            user.setCreateTime(LocalDateTime.now());
            users.add(user);
        }
        return userService.saveBatch(users);
    }

user_0表的数据如下所示：

user_1表的数据如下所示：

三张表的配置：

#修改参数为0..2
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes.actual-data-nodes=myds.user_$->{0..2}
#假设数据总量为12，有3张表的配置，range-lower = 12/3 + 1，range-upper = 12 + 1，sharding-volume = 12/表的数量
range-lower=5
range-upper=13
sharding-volume=4

修改代码

@PutMapping("insert")
    public boolean insert(){
        Collection<User> users = new ArrayList<>();
        for (int i = 0;i<13;i++){
            User user = new User();
            user.setUsername("Meta39");
            user.setNum(i);
            user.setCreateTime(LocalDateTime.now());
            users.add(user);
        }
        return userService.saveBatch(users);
    }

增加新表：user_2，清空表数据

写入master主库：localhost:88/user/insert