依靠shardingsphere中间件实现服务的主从数据源，读写分离、分库分表，实战代码演示

项目中用到了分库分表对数据做了读写分离，分散了数据库服务的压力
依靠的中间件是shardingsphere开源产品，先演示最简单的读写分离下面上代码：

1、读写分离

首先搭建测试所需要的环境：两个数据库，当然也可以一主多从，根据需要来进行配置多个从库（搭建数据库就不做演示了）
引入依赖：

        
            org.apache.shardingsphere
            >sharding-jdbc-spring-boot-starter
            4.1.1

编写需要的实体类文件：model: Test数据库映射类、mapper: TestMapperDAO层接口、TestMapper.xmlDAO层sql文件、和一个TestController测试类。service层就暂且跳过了，因为service主要是根据项目中的业务来实现的，与demo关联不大

Test数据库映射类：

@Data
@Table(name = "test")
public class Test {
    
    @Id 
    // 项目中采用了雪花算法来生成ID
    @KeySql(genId = SnowflakeIdGenId.class)
    private Long id;

    
    private String name;

    
    @Column(name = "create_time")
    private LocalDateTime createTime;
}

mapper: TestMapperDAO层接口：定义了两个查询来测试

public interface TestMapper extends MyMapper {

    
    List selectByPage();
	
    Test> selectById();


}

TestMapper.xmlDAO层sql文件：xml中写了两个sql用来测试






    
        select * from test where id  = 1

TestController测试类

@Slf4j
@RestController
@RequestMapping("/test")
@Api(value = "测试", tags = {"测试"}, consumes = "application/json")
public class AOOrganController {

    @Autowired
    private TestMapper testMapper;

    @PostMapping("/list")
    @ApiOperation(value = "测试列表", notes = "测试列表", produces = "application/json")
    public ResponseMessage list(@RequestBody AOListRequest request) {

//        PageHelper.startPage(request.currentPageNum(), request.currentPageSize());
//        List tests = testMapper.selectByPage();
        Test tests = testMapper.selectById();

        for (int i = 0; i < 10; i++) {
            Test test = new Test();
            test.setName("测试000");
            test.setCreateTime(LocalDateTime.now());
            testMapper.insertSelective(test);
        }

        System.out.println();

        return ResponseMessage.ok();
    }
 }

接下来是配置文件中的属性：

spring:
  shardingsphere:
    props:
      # 打印SQL属性为true
      sql:
        show: true
    datasource:
      # 定义数据源名称，名称支持自定义
      names: master, slave
      # 主数据源参数
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test_1?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
        connectionProperties:
          - druid.stat.mergeSql=true
          - druid.stat.slowSqlMillis=5000
        filters:
          - stat
          - wall
          - slf4j
        maxActive: 50
        testOnReturn: false
        validationQuery: SELECT 1 FROM dual
        initial-size: 5
        removeAbandoned: true
        removeAbandonedTimeout: 180
        logAbandoned: true
      # 从数据源参数
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test_2?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
        connectionProperties:
          - druid.stat.mergeSql=true
          - druid.stat.slowSqlMillis=5000
        filters:
          - stat
          - wall
          - slf4j
        maxActive: 50
        testOnReturn: false
        validationQuery: SELECt 1 FROM dual
        initial-size: 5
        removeAbandoned: true
        removeAbandonedTimeout: 180
        logAbandoned: true
    # 读写分离配置，
    masterslave:
      name: datasource
      # 指定主数据库
      master-data-source-name: master
      # 指定从数据库 多个从数据库用 , 隔开：slave1, slave2
      slave-data-source-names: slave

启动项目测试：
首先是查询，通过控制台打印的信息可以看出，选用的是Actual SQL: slave ::: select * from test order by create_time 从节点进行查询

[2022-05-31 16:47:53.684][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Logic SQL: select * from test order by create_time 
[2022-05-31 16:47:53.685][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: slave ::: select * from test order by create_time 
[2022-05-31 16:47:53.686][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.selectByPage :159] ==> Parameters:  
[2022-05-31 16:47:53.691][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.selectByPage :159] <==      Total: 41

下面写入

[2022-05-31 16:50:53.236][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Logic SQL: INSERT INTO test  ( id,`name`,create_time ) VALUES( ?,?,? ) 
[2022-05-31 16:50:53.237][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: master ::: INSERT INTO test  ( id,`name`,create_time ) VALUES( ?,?,? ) 
[2022-05-31 16:50:53.261][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.insertSelective :159] ==> Parameters: 1531558823779766272(Long), 测试000(String), 2022-05-31 16:50:53.228(Timestamp) 
[2022-05-31 16:50:53.266][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.insertSelective :159] <==    Updates: 1

放开for循环的断点可以看出 Actual SQL: master ::: INSERT INTO test ( id,name,create_time ) VALUES( ?,?,? ) 是用过主节点进行写入的

到此，读写分离演示完毕。

2、分表策略

下面是对分库策略的演示，这里使用的环境是一个数据源：

在上面的映射类不改动的环境下，对配置文件的修改：

spring:
  shardingsphere:
    props:
      # 打印SQL
      sql:
        show: true
    # 规则配置
    sharding:
      tables:
        # 逻辑表名称
        test:
          # 行表达式标识符可以使用 ${...} 或 $->{...}，但前者与 Spring 本身的属性文件占位符冲突，因此在 Spring 环境中使用行表达式标识符建议使用 $->{...}
          # 表的位置 在那个数据源（数据库）,哪个表,表示表的范围，例如: test01,test02...
          actual-data-nodes: master.test0$->{1..2}
          # 分表策略
          table-strategy:
            inline:
            # 分表列 通过id分表
              sharding-column: id
            # 分表规则 (id % 2    id求余 0则保存在第一个数据库 1则保存在第二个数据库)
              algorithm-expression: test0$->{id % 2}
              
    datasource:
      names: master
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/nucleic_acid_dev?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
        connectionProperties:
          - druid.stat.mergeSql=true
          - druid.stat.slowSqlMillis=5000
        filters:
          - stat
          - wall
          - slf4j
        maxActive: 50
        testOnReturn: false
        validationQuery: SELECT 1 FROM dual
        initial-size: 5
        removeAbandoned: true
        removeAbandonedTimeout: 180
        logAbandoned: true

下面演示下通过selectById()该方法，测试id=1的数据是不是在test01的库里去查
由打印的sql：Actual SQL: master ::: select * from test01 where id = 1 ，可以看到，只针对test01库执行了一次sql

[2022-05-31 17:52:18.539][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Logic SQL: select * from test where id  = 1 
[2022-05-31 17:52:18.541][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test01 where id  = 1 
[2022-05-31 17:52:18.561][DEBUG][http-nio-9999-exec-5][com.web.mapper.simple.TestMapper.selectById :159] <==      Total: 1

会有人问，是不是只去查了test01表，或者说分页怎么做的呢？下面就演示下分页的情景。
这里先要强调下逻辑：首先它是根据id的规则去查询的test01表。
另外复合表的规则是：如果是查询10条数据，它会从两个库中分别查出符合分页条件的10条数据，然后会对其做个汇总，然后返回十条数据。
那么会有人问了，他通过什么规则返回失调，是平均取test01库中5条，test02中5条还是怎样的？
这就是第二层的问题，它对数据处理的顺序是怎样的。处理的顺序：如果没有指定order by 的字段，那么会优先取出test01的表数据，不足十条的才会去后面的表中拿数据。
我们继续来看演示
未指定order by时的查询，sql语句，数据库原有信息(注意看id为1、2、3、4的时间，后面排序的时候有用)：

       select * from test

执行结果可以看到只有test01中的十条数据：

[2022-05-31 18:06:31.363][INFO][http-nio-9999-exec-8][ShardingSphere-SQL :74] Logic SQL: select * from test LIMIT ?  
[2022-05-31 18:06:31.364][INFO][http-nio-9999-exec-8][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test01 LIMIT ?  ::: [10] 
[2022-05-31 18:06:31.364][INFO][http-nio-9999-exec-8][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test02 LIMIT ?  ::: [10]

指定order by 后，可以发现执行的sql是不变的，但是返回的数据却变了：

[2022-05-31 18:11:30.987][INFO][http-nio-9999-exec-4][ShardingSphere-SQL :74] Logic SQL: select * from test order by create_time LIMIT ?  
[2022-05-31 18:11:30.987][INFO][http-nio-9999-exec-4][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test01 order by create_time LIMIT ?  ::: [10] 
[2022-05-31 18:11:30.988][INFO][http-nio-9999-exec-4][ShardingSphere-SQL :74] Actual SQL: master ::: select * from test02 order by create_time LIMIT ?  ::: [10]

原理：分页是两个表中都会取复合条件的相同条数的数据，然后再由框架进行处理后返回，并把分页信息给写入

3、分库策略

懒得写了，下面直接写分库+ 分表

4、分库+分表策略

分库分表理解上会有点复杂，选用一个情景来演示：系统中某个业务表数据比较多，将根据id分为两个库，每个库依据月份来存储数据。

环境的配置：我这里用了两个数据源（同一个数据源，复制了一份数据库而已），然后将test01、test02表名修改成test_2022_1、test_2022_2，由于两份库中的两表完全一致，我只对其中一个库做展示。
代码调整：已有代码不做改动，对配置文件进行调整已经加入自定义分表策略方法处理类

配置文件：

spring:
  shardingsphere:
    props:
      # 打印SQL
      sql:
        show: true
    # 规则配置
    sharding:
      tables:
        # 逻辑表名称
        test:
          # 行表达式标识符可以使用 ${...} 或 $->{...}，但前者与 Spring 本身的属性文件占位符冲突，因此在 Spring 环境中使用行表达式标识符建议使用 $->{...}
          actual-data-nodes: m$->{0..1}.test_2022_$->{1..2}
          # 分库策略
          database-strategy:
            # 行内算法
            inline:
              # 分库列 (id % 2    id求余 0则保存在第一个数据库 1则保存在第二个数据库)
              shardingColumn: id
              algorithmExpression: m$->{id % 2}
          # 分表策略
          table-strategy:
            standard:
            # 分表列 通过create_time分表，按照数据的时间，每个月份存储一张表
              sharding-column: create_time
            # 分表规则 自定义分表策略方法
              precise-algorithm-class-name: com.web.controller.admin.sys.TableShardingAlgorithm

    datasource:
      names: m1,m0
      m1: # 数据源1
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
        connectionProperties:
          - druid.stat.mergeSql=true
          - druid.stat.slowSqlMillis=5000
        filters:
          - stat
          - wall
          - slf4j
        maxActive: 50
        testOnReturn: false
        validationQuery: SELECt 1 FROM dual
        initial-size: 5
        removeAbandoned: true
        removeAbandonedTimeout: 180
        logAbandoned: true
      m0: # 数据源2
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: root
        connectionProperties:
          - druid.stat.mergeSql=true
          - druid.stat.slowSqlMillis=5000
        filters:
          - stat
          - wall
          - slf4j
        maxActive: 50
        testOnReturn: false
        validationQuery: SELECt 1 FROM dual
        initial-size: 5
        removeAbandoned: true
        removeAbandonedTimeout: 180
        logAbandoned: true

自定义分表策略方法

@Slf4j
public class TableShardingAlgorithm implements PreciseShardingAlgorithm {
    @Override
    public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {


        // availableTargetNames，数据库的id规则选出哪个库后，库中表的集合 test_2022_$->{1..2}
        System.out.println(StrUtil.format("actual node tables:{}", availableTargetNames));

        // logicTableName: 逻辑表名 test，分表规则的字段 create_time
        System.out.println(StrUtil.format("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName()));

        String tbName = shardingValue.getLogicTableName() + "_";


        // 根据插入对象的日期来分表
        Date date = shardingValue.getValue();
        String year = String.format("%tY", date);
        // 去掉前缀0
        String mon = String.valueOf(Integer.parseInt(String.format("%tm", date)));

        // 选择表
        tbName = tbName + year + "_" + mon;
        System.out.println("tbName:" + tbName);

        for (String each : availableTargetNames) {
            // 匹配到后即返回
            if (each.equals(tbName)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }
}

测试controller代码：

    public ResponseMessage list(@RequestBody AOOrganListRequest request) {

        PageHelper.startPage(request.currentPageNum(), request.currentPageSize());
        List tests = testMapper.selectByPage();
//        Test tests = testMapper.selectById();

        for (int i = 0; i < 10; i++) {
            Test test = new Test();
            test.setName("测试000");
            test.setCreateTime(LocalDateTime.now().withMonth(1));
            testMapper.insertSelective(test);
        }

        System.out.println();

        return ResponseMessage.ok();
    }

进行测试演示：
先进行分页查询，从打印日志中可以看出每个库每个表都会查询十条记录，然后通过框架内部进行处理返回排序后的十条。

[2022-06-01 13:55:56.083][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m0 ::: select id, name, create_time createTime from test_2022_1 order by create_time LIMIT ?  ::: [10] 
[2022-06-01 13:55:56.083][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m0 ::: select id, name, create_time createTime from test_2022_2 order by create_time LIMIT ?  ::: [10] 
[2022-06-01 13:55:56.083][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m1 ::: select id, name, create_time createTime from test_2022_1 order by create_time LIMIT ?  ::: [10] 
[2022-06-01 13:55:56.084][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m1 ::: select id, name, create_time createTime from test_2022_2 order by create_time LIMIT ?  ::: [10] 
[2022-06-01 13:55:56.095][DEBUG][http-nio-9999-exec-5]

插入演示：debug进自定义分表逻辑方法中，shardingValue对象有三个属性：逻辑表名称、分表字段、属性值，availableTargetNames对象是一个集合，通过数据库的id规则选出哪个库后，库中表的集合这里是： test_2022_$->{1…2}，后面通过代码中进行逻辑的处理后，返回响应的表名称即可。

结果：
m1 库根据id的规则计算后表中数据一定是基数：

m0 库根据id的规则计算后表中数据一定是偶数：

id精确查询，通过selectById()方法来查询，看看是怎么执行sql的：可以看到仅仅在符合id为基数的m1数据源中查询，查询两个库哪个中有id=1的数据

[2022-06-01 14:28:11.933][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Logic SQL: select * from test where id  = 1 
[2022-06-01 14:28:11.934][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m1 ::: select * from test_2022_1 where id  = 1 
[2022-06-01 14:28:11.934][INFO][http-nio-9999-exec-5][ShardingSphere-SQL :74] Actual SQL: m1 ::: select * from test_2022_2 where id  = 1 
[2022-06-01 14:28:11.937][DEBUG][http-nio-9999-exec-5][com.nucleic.acid.web.mapper.simple.TestMapper.selectById :159] <==      Total: 1

总结

从上面几个例子可以看出shardingsphere在读写分离上无代码侵入，还是比较好用的，缺点是两个数据源一定是主从复制，且同步数据的时效性比较重要，例如：账号修改密码功能是在主节点进行写入，从节点同步不够及时，登录时就会校验失败。
在分库分表上会有少量代码入侵，两者都需要配置文件进行配置，也符合springboot的理念：约定大于配置

依靠shardingsphere中间件实现服务的主从数据源，读写分离、分库分表，实战代码演示

Java相关栏目本月热门文章