文章目录
本篇基于ShardingSphere5.1.1验证
官网地址: https://shardingsphere.apache.org/document/5.1.1/cn/user-manual/shardingsphere-jdbc/spring-boot-starter/
本篇涉及代码地址: https://github.com/woodpecker007/ShardingSphere.git
读写分离
需要数据库至少是一主一从才能完成验证读写分离。部署MySQL一主一从可以参考笔者这篇文章基于Docker搭建MySQL一主一从。
引入 Maven 依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</dependency>
配置文件
# 内存模式
spring.shardingsphere.mode.type=Memory
# 配置真实数据源
spring.shardingsphere.datasource.names=master,slave1,slave2
# 配置第 1 个数据源,数据库连接关闭SSL
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://127.0.0.1:3306/aurora?allowPublicKeyRetrieval=true&useSSL=false
spring.datasource.username=root
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.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://127.0.0.1:3307/aurora?allowPublicKeyRetrieval=true&useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456
# 配置第 3 个数据源
spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://127.0.0.1:3308/aurora?allowPublicKeyRetrieval=true&useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=123456
# 读写分离类型,如: Static,Dynamic
spring.shardingsphere.rules.readwrite-splitting.data-sources.aurora.type=Static
# 写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.aurora.props.write-data-source-name=master
# 读数据源名称,多个从数据源用逗号分隔
spring.shardingsphere.rules.readwrite-splitting.data-sources.aurora.props.read-data-source-names=slave1,slave2
# 负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.aurora.load-balancer-name=aurora_round
# 负载均衡算法配置,此配置对多slave情况有效,有三种负载算法可以选择,
# 1.ROUND_ROBIN(轮询)2.RANDOM(随机)3.WEIGHT(权重,使用中的从库必须配置权重)
spring.shardingsphere.rules.readwrite-splitting.load-balancers.aurora_round.type=ROUND_ROBIN
#spring.shardingsphere.rules.readwrite-splitting.load-balancers.aurora_random.type=RANDOM
#spring.shardingsphere.rules.readwrite-splitting.load-balancers.aurora_weight.type=WEIGHT
#spring.shardingsphere.rules.readwrite-splitting.load-balancers.aurora_weight.props.slave1=1
#spring.shardingsphere.rules.readwrite-splitting.load-balancers.aurora_weight.props.slave2=3
# 打印SQl
spring.shardingsphere.props.sql-show=true
代码
@Data
@TableName("elephant")
@ToString
public class Elephant {
@TableId(type = IdType.AUTO)
private Long id;
private String uname;
}
@Mapper
public interface ElephantMapper extends BaseMapper<Elephant> {
}
结果验证
垂直分片
准备工作
通过Docker创建两个服务,server-driver,server-bus,对应创建数据库db_driver,db_bus,在数据库中创建对应的表aurora_driver,aurora_bus。
基于Docker创建MySQL实例详细过程,可以参考笔者这篇文章基于Docker搭建MySQL一主一从。
docker run -d \
-p 3301:3306 \
-v /Users/woodpecker/aurora/server/driver/conf:/etc/mysql/conf.d \
-v /Users/woodpecker/aurora/server/driver/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-driver \
mysql:8.0.29
docker run -d \
-p 3302:3306 \
-v /Users/woodpecker/aurora/server/bus/conf:/etc/mysql/conf.d \
-v /Users/woodpecker/aurora/server/bus/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-bus \
mysql:8.0.29
-- 建表语句
CREATE TABLE aurora_driver (
id BIGINT AUTO_INCREMENT,
user_id BIGINT,
licence_no VARCHAR(30),
PRIMARY KEY(id)
);
CREATE TABLE aurora_bus (
id BIGINT AUTO_INCREMENT,
licence_plate VARCHAR(30),
PRIMARY KEY(id)
);
配置文件
# vertical垂直分片配置
# 配置真实数据源
spring.shardingsphere.datasource.names=server-driver,server-bus
# 配置第 1 个数据源
spring.shardingsphere.datasource.server-driver.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-driver.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-driver.jdbc-url=jdbc:mysql://127.0.0.1:3301/db_driver?allowPublicKeyRetrieval=true&useSSL=false
spring.shardingsphere.datasource.server-driver.username=root
spring.shardingsphere.datasource.server-driver.password=123456
# 配置第 2 个数据源
spring.shardingsphere.datasource.server-bus.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-bus.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-bus.jdbc-url=jdbc:mysql://127.0.0.1:3302/db_bus?allowPublicKeyRetrieval=true&useSSL=false
spring.shardingsphere.datasource.server-bus.username=root
spring.shardingsphere.datasource.server-bus.password=123456
# 标准分片表配置(数据节点)
# spring.shardingsphere.rules.sharding.tables.<table-name>.actual-data-nodes=值
# 值由数据源名 + 表名组成,以小数点分隔。
# <table-name>:逻辑表名
spring.shardingsphere.rules.sharding.tables.aurora_driver.actual-data-nodes=server-driver.aurora_driver
spring.shardingsphere.rules.sharding.tables.aurora_bus.actual-data-nodes=server-bus.aurora_bus
# 打印SQL
spring.shardingsphere.props.sql-show=true
代码
@Data
@TableName("aurora_driver")
public class Driver {
@TableId(type = IdType.AUTO)
private Long id;
private Long userId;
private String licenceNo;
}
@Data
@TableName("aurora_bus")
public class Bus {
@TableId(type = IdType.AUTO)
private Long id;
private String licencePlate;
}
@Mapper
public interface DriverMapper extends BaseMapper<Driver> {
}
@Mapper
public interface BusMapper extends BaseMapper<Bus> {
}
结果验证
水平分片
同一个库水平分表
准备工作
在上一小节db_driver库中添加,new_driver_0,new_driver_1两张表。
CREATE TABLE new_driver_0 (
id BIGINT,
user_id BIGINT,
licence_no VARCHAR(30),
PRIMARY KEY(id)
);
CREATE TABLE new_driver_1 (
id BIGINT,
user_id BIGINT,
licence_no VARCHAR(30),
PRIMARY KEY(id)
);
配置文件
# horizontal1分表配置
# 配置真实数据源
spring.shardingsphere.datasource.names=server-driver
# 配置第 1 个数据源
spring.shardingsphere.datasource.server-driver.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-driver.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-driver.jdbc-url=jdbc:mysql://127.0.0.1:3301/db_driver?allowPublicKeyRetrieval=true&useSSL=false
spring.shardingsphere.datasource.server-driver.username=root
spring.shardingsphere.datasource.server-driver.password=123456
# 标准分片表配置(数据节点)
# spring.shardingsphere.rules.sharding.tables.<table-name>.actual-data-nodes=值
# 值由数据源名 + 表名组成,以小数点分隔。
# <table-name>:逻辑表名
#spring.shardingsphere.rules.sharding.tables.new_driver_.actual-data-nodes=server-driver.new_driver_0,server-driver.new_driver_1
# 表达式语法
spring.shardingsphere.rules.sharding.tables.new_driver_.actual-data-nodes=server-driver.new_driver_$->{0..1}
# ============== 分表策略 ==============
# 分片列名称,数据库表的字段,不是java实体对象中的字段
spring.shardingsphere.rules.sharding.tables.new_driver_.table-strategy.standard.sharding-column=licence_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.new_driver_.table-strategy.standard.sharding-algorithm-name=alg_hash_mod
# ============== 分片算法配置 ==============
# 哈希取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.type=HASH_MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.props.sharding-count=2
# 打印SQL
spring.shardingsphere.props.sql-show=true
代码
@Data
@ToString
@TableName("new_driver_")
public class NewDriver {
// 此处要使用分布式ID,不能使用数据库主键自增ID,这里用的是mybatis-plus内置的snowflake。
@TableId(type = IdType.ASSIGN_ID)
private Long id;
private Long userId;
private String licenceNo;
}
@Mapper
public interface NewDriverMapper extends BaseMapper<NewDriver> {
}
结果验证
多个库水平分库分表
准备工作
创建数据库db_driver_0,db_driver_1,在数据库中创建对应的表new_driver_0,new_driver_1。
create database db_driver_0;
create database db_driver_1;
CREATE TABLE new_driver_0 (
id BIGINT,
user_id BIGINT,
licence_no VARCHAR(30),
PRIMARY KEY(id)
);
CREATE TABLE new_driver_1 (
id BIGINT,
user_id BIGINT,
licence_no VARCHAR(30),
PRIMARY KEY(id)
);
配置文件
# horizontal2分库分表配置
# 配置真实数据源
spring.shardingsphere.datasource.names=server-driver0, server-driver1
# 配置第 1 个数据源,数据库db_driver_0
spring.shardingsphere.datasource.server-driver0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-driver0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-driver0.jdbc-url=jdbc:mysql://127.0.0.1:3301/db_driver_0?allowPublicKeyRetrieval=true&useSSL=false
spring.shardingsphere.datasource.server-driver0.username=root
spring.shardingsphere.datasource.server-driver0.password=123456
# 配置第 2 个数据源,数据库db_driver_1
spring.shardingsphere.datasource.server-driver1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-driver1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-driver1.jdbc-url=jdbc:mysql://127.0.0.1:3301/db_driver_1?allowPublicKeyRetrieval=true&useSSL=false
spring.shardingsphere.datasource.server-driver1.username=root
spring.shardingsphere.datasource.server-driver1.password=123456
# ============== 标准分片表配置(数据节点)==============
# spring.shardingsphere.rules.sharding.tables.<table-name>.actual-data-nodes=值
# 值由数据源名 + 表名组成,以小数点分隔。
# <table-name>:逻辑表名
#spring.shardingsphere.rules.sharding.tables.new_driver_.actual-data-nodes=server-driver.new_driver_0,server-driver.new_driver_1
# 表达式语法
spring.shardingsphere.rules.sharding.tables.new_driver_.actual-data-nodes=server-driver$->{0..1}.new_driver_$->{0..1}
# ============== 分库策略 ==============
# 分片列名称
spring.shardingsphere.rules.sharding.tables.new_driver_.database-strategy.standard.sharding-column=user_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.new_driver_.database-strategy.standard.sharding-algorithm-name=alg_mod
# ============== 分片算法配置 ==============
# 行表达式分片算法
# 分片算法类型
#spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.type=INLINE
# 分片算法属性配置
#spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.props.algorithm-expression=server-order$->{user_id % 2}
# 取模分片算法,和上面行表达式分片算法二选一
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.type=MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.props.sharding-count=2
# ============== 分表策略 ==============
# 分片列名称,数据库表的字段,不是java实体对象中的字段
spring.shardingsphere.rules.sharding.tables.new_driver_.table-strategy.standard.sharding-column=licence_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.new_driver_.table-strategy.standard.sharding-algorithm-name=alg_hash_mod
# ============== 分片算法配置 ==============
# 哈希取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.type=HASH_MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.props.sharding-count=2
# 打印SQL
spring.shardingsphere.props.sql-show=true
代码
@Data
@ToString
@TableName("new_driver_")
public class NewDriver {
// 此处要使用分布式ID,不能使用数据库主键自增ID,这里用的是mybatis-plus内置的snowflake。
@TableId(type = IdType.ASSIGN_ID)
private Long id;
private Long userId;
private String licenceNo;
}
@Mapper
public interface NewDriverMapper extends BaseMapper<NewDriver> {
}
结果验证
insert验证
配置文件中的server-driver0对应的数据库是db_driver_0,server-driver1对应的数据库是db_driver_1,分库策略是userId % 2,分表策略是hash(licenceNo) % 2。
select验证
select查询会先将db_driver_0数据库中的两张表查询结果聚合,再将db_driver_1数据库中两张表查询结果聚合,最后再将两个聚合结果再次聚合并返回。
Tips~
「多个数据源多个库分库分表」、「绑定表」、「关联表」敬请期待。