Spring 系列之整合ShardingSphere-JDBC

一、实现读写分离

1.1、引入依赖

<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  <version>4.1.1</version>
</dependency>

1.2、配置属性

spring:
  shardingsphere:
    datasource:
      names: master,slave1,slave2
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: master
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:4401/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: slave1
      slave2:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:4402/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: slave2
    # 开启 sql 日志打印
    props:
      sql.show: true
    masterslave:
      # 负载均衡算法
      load-balance-algorithm-type: round_robin
    sharding:
      master-slave-rules:
        master:
          master-data-source-name: master
          slave-data-source-names:
            - slave1
            - slave2

1.3、引入MybatisPlus 测试

1.3.1、依赖

<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.5.1</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

1.3.2、配置包扫描

@MapperScan("cn.hdj.ShardingSphere.dao")
@SpringBootApplication
public class ShardingSphereApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingSphereApplication.class, args);
    }
}

1.3.3、代码生成后测试

• controller ```java @RestController @RequestMapping(value = “/course”) public class CourseController {

  @Autowired private CourseService courseService;

  @PostMapping(value = “/create”) public ResponseEntity create(@RequestBody CoursePO coursePO) {

    this.courseService.save(coursePO);
    return ResponseEntity.ok().body("ok");

  }

  @GetMapping(value = “/query”) public ResponseEntity query() {

    List<CoursePO> list = this.courseService.list();
    return ResponseEntity
            .ok(list);

  } }

- 测试输出
```shell
-- 采用  round_robin（轮询调度） 负载均衡读取数据
-- 查询第一次  slave2
Logic SQL: SELECT  CNO,CNAME,TNO  FROM COURSE
Actual SQL: slave2 ::: SELECT  CNO,CNAME,TNO  FROM COURSE
-- 查询第二次  slave1
Logic SQL: SELECT  CNO,CNAME,TNO  FROM COURSE
Actual SQL: slave1 ::: SELECT  CNO,CNAME,TNO  FROM COURSE

二、水平分表

2.1、分表需求

• 按照水平分表来创建数据库
• 创建表 course_1 、 course_2
• 约定规则：如果添加的课程 id 为偶数添加到 course_1 中，奇数添加到 course_2 中。

2.2、表结构

DROP TABLE IF EXISTS `course_1`;
CREATE TABLE `course_1` (
  `ID` bigint NOT NULL,
  `CNO` varchar(5) NOT NULL COMMENT '课程号',
  `CNAME` varchar(104) DEFAULT NULL COMMENT '课程名称',
  `TNO` varchar(10) DEFAULT NULL COMMENT '老师编号',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `COURSE_un` (`CNO`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='课程表1';
DROP TABLE IF EXISTS `course_2`;
CREATE TABLE `course_2` (
  `ID` bigint NOT NULL,
  `CNO` varchar(5) NOT NULL COMMENT '课程号',
  `CNAME` varchar(104) DEFAULT NULL COMMENT '课程名称',
  `TNO` varchar(10) DEFAULT NULL COMMENT '老师编号',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `COURSE_un` (`CNO`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='课程表2';

2.3、配置 Sharding-JDBC 分片策略

spring:
  shardingsphere:
    datasource:
      # 数据源名称
      names: master,slave1,slave2
      # 数据源配置
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:4400/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: master
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:4401/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: slave1
      slave2:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:4402/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: slave2
    #  打开 sql 输出日志
    props:
      sql.show: true
    masterslave:
      # 负载均衡算法
      load-balance-algorithm-type: round_robin
    sharding:
      master-slave-rules:
        master:
          master-data-source-name: master
          slave-data-source-names:
            - slave1
            - slave2
      tables:
        course:
          # 指定course表分布的情况，配置表在哪个数据库里，表的名称都是什么 m1.course_1,m1.course_2
          actual-data-nodes: master.course_$->{1..2}
          # 指定 course 表里面主键 id 的生成策略 SNOWFLAKE
          key-generator:
            column: id
            type: SNOWFLAKE
            # 配置分表策略    约定 id 值偶数添加到 course_1 表，如果 id 是奇数添加到 course_2 表
          table-strategy:
            inline:
              algorithm-expression: course_$->{id % 2 + 1}
              sharding-column: id
  main:
    # 一个实体类对应两张表，覆盖
    allow-bean-definition-overriding: true

三、水平分库分表

四、垂直分表

参考

• https://github.com/apache/shardingsphere/tree/master/examples/shardingsphere-jdbc-example/single-feature-example/readwrite-splitting-example
• https://www.cnblogs.com/fengzheng/p/13401783.html
• https://shardingsphere.apache.org/document/4.1.1/en/manual/sharding-jdbc/usage/read-write-splitting/
• https://juejin.cn/post/7012113650534055966
• https://segmentfault.com/a/1190000038241298