SpringBoot+JPA整合ShardingShpere实现分表-读写分离

ShardingShpere 分表分库+读写分离

​ ShardingShpere 提供来了根据某个字段分库分表的功能和读写分离。

读写分离

当主服务有写入（insert/update/delete）语句时，从服务器自动获取。

• 写入线程从 master 数据库查询
• 查询线程从 salve 数据库获取数据

ShardingShpere 源码

版本依赖

• JDK 8
• SpringBoot 2.4.3
• ShardingShpere 5.0

创建两个数据库 master0、master1，每个库包含三张表 user_0、user_1、user_2。

CREATE DATABASE `master0` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `master1` CHARACTER SET utf8 COLLATE utf8_general_ci;
use master0;
DROP TABLE IF EXISTS user_0;
CREATE TABLE `user_0` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS user_1;
CREATE TABLE `user_1` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS user_2;
CREATE TABLE `user_2` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

添加 application.yml

spring:
  jpa:
    properties:
      hibernate:
        hbm2ddl:
          auto: update
        dialect: org.hibernate.dialect.MySQL5Dialect
        show_sql: false
  shardingsphere:
    datasource:
      names: master0,slave0,master1,slave1
      common:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
      master0:
        jdbc-url: jdbc:mysql://172.28.128.3:3306/master0?serverTimezone=UTC&useSSL=false
        username: root
        password: Test@122
      master1:
        jdbc-url: jdbc:mysql://172.28.128.3:3306/master1?serverTimezone=UTC&useSSL=false
        username: root
        password: Test@122
      slave0:
        jdbc-url: jdbc:mysql://172.28.128.4:3306/master0?serverTimezone=UTC&useSSL=false
        username: root
        password: Test@123
      slave1:
        jdbc-url: jdbc:mysql://172.28.128.4:3306/master1?serverTimezone=UTC&useSSL=false
        username: root
        password: Test@123
    rules:
      sharding:
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: master$->{ id % 2 }
          table-inline:
            type: INLINE
            props:
              algorithm-expression: user_$->{ id % 3 }
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123
        tables:
          user:
            actual-data-nodes: master$->{0..1}.user_$->{0..2}
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
            database-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: database-inline
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: table-inline
      readwrite-splitting:
        load-balancers:
          round_robin:
            type: ROUND_ROBIN
        data-sources:
          master0:
            write-data-source-name: master0
            read-data-source-names: slave0
          master1:
            write-data-source-name: master1
            read-data-source-names: slave1
    props:
      sql-show: true

运行测试类，登陆Mysql服务器，查看结果：

验证：写入线程从 master 数据库查询

主从数据库进程启动。模拟新增请求：curl -X POST -d 'name="test10"&email="mail10"' http://127.0.0.1:8080/user/add，关闭slave数据库进程，模拟请求查询：http://localhost:8080/user/all，master数据库查询结果如下：

验证：查询线程从 salve 数据库获取数据

关闭master数据库进程，slave数据库进程开启。模拟新增请求：$ curl -X POST -d 'name="test13"&email="mail13"' http://127.0.0.1:8080/user/add，后端日志提示连接失败。 模拟查询请求： http://localhost:8080/user/all，slave数据库查询结果如下：

参考

https://shardingsphere.apache.org/document/current/cn/overview/

https://github.com/apache/shardingsphere/tree/master/examples

问题：ERROR 12506 — [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : Data truncation: Out of range value for column ‘id’ at row 1

解决：数据库字段 id 改为 bitInt 类型，实体类 id 改为 Long 类型。