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: updatedialect: org.hibernate.dialect.MySQL5Dialectshow_sql: falseshardingsphere:datasource:names: master0,slave0,master1,slave1common:driver-class-name: com.mysql.cj.jdbc.Drivertype: com.zaxxer.hikari.HikariDataSourcemaster0:jdbc-url: jdbc:mysql://172.28.128.3:3306/master0?serverTimezone=UTC&useSSL=falseusername: rootpassword: Test@122master1:jdbc-url: jdbc:mysql://172.28.128.3:3306/master1?serverTimezone=UTC&useSSL=falseusername: rootpassword: Test@122slave0:jdbc-url: jdbc:mysql://172.28.128.4:3306/master0?serverTimezone=UTC&useSSL=falseusername: rootpassword: Test@123slave1:jdbc-url: jdbc:mysql://172.28.128.4:3306/master1?serverTimezone=UTC&useSSL=falseusername: rootpassword: Test@123rules:sharding:sharding-algorithms:database-inline:type: INLINEprops:algorithm-expression: master$->{ id % 2 }table-inline:type: INLINEprops:algorithm-expression: user_$->{ id % 3 }key-generators:snowflake:type: SNOWFLAKEprops:worker-id: 123tables:user:actual-data-nodes: master$->{0..1}.user_$->{0..2}key-generate-strategy:column: idkey-generator-name: snowflakedatabase-strategy:standard:sharding-column: idsharding-algorithm-name: database-inlinetable-strategy:standard:sharding-column: idsharding-algorithm-name: table-inlinereadwrite-splitting:load-balancers:round_robin:type: ROUND_ROBINdata-sources:master0:write-data-source-name: master0read-data-source-names: slave0master1:write-data-source-name: master1read-data-source-names: slave1props: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数据库查询结果如下：

在这里插入图片描述