这篇文章将为大家详细讲解有关ShardingSphere中如何进行Sharding-JDBC分库的实战,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。
我们使用SpringBoot+Mybaits-plus来搭建。数据库表我们使用 User、HealthRecord、HealthLevel 和 HealthTask 这四个业务对象。在下面这张图中,对每个业务对象给出最基础的字段定义,以及这四个对象之间的关联关系:
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.0.RELEASE</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
@SpringBootTest
@ActiveProfiles("sharding-database")
public class InitData {
@Autowired
private UserService userService;
@Autowired
private HealthLevelService healthLevelService;
@Autowired
private HealthRecordMapper healthRecordMapper;
@Autowired
private HealthTaskMapper healthTaskMapper;
@Autowired
private OtherTableMapper otherTableMapper;
@Test
public void init() {
insertUser();
}
public int insertHealthLevel(int count) {
for (int i = 1; i <= count; i++) {
HealthLevel healthLevel = new HealthLevel();
healthLevel.setLevelId((long) i);
healthLevel.setLevelName(i + "_level");
healthLevelService.insert(healthLevel);
}
return count;
}
public void insertUser() {
int level = insertHealthLevel(5);
for (int i = 1; i < 15; i++) {
User user = new User();
user.setUserId((long) i);
user.setUserName(i + "_userName");
userService.insertUser(user);
insertHealthRecord(level, i, user);
}
}
public void insertHealthRecord(int levelCount, int i, User user) {
HealthRecord healthRecord = new HealthRecord();
healthRecord.setUserId(user.getUserId());
healthRecord.setLevelId((long) (i % levelCount));
healthRecord.setRemark("u:" + user.getUserId());
healthRecordMapper.insert(healthRecord);
insertHealthTask(user, healthRecord);
}
public void insertHealthTask(User user, HealthRecord healthRecord) {
HealthTask healthTask = new HealthTask();
healthTask.setRecordId(healthRecord.getRecordId());
healthTask.setUserId(user.getUserId());
healthTask.setTaskName("u:" + user.getUserId() + " h:" + healthRecord.getRecordId());
healthTaskMapper.insert(healthTask);
}
}
配置数据源,这里分库配置了两个数据源分别为 test0、test1
#配置数据源
spring.shardingsphere.datasource.names=test0,test1
#test0
spring.shardingsphere.datasource.test0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.test0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test0
spring.shardingsphere.datasource.test0.username=devadmin
spring.shardingsphere.datasource.test0.password=
#test1
spring.shardingsphere.datasource.test1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1
spring.shardingsphere.datasource.test1.username=devadmin
spring.shardingsphere.datasource.test1.password=
设置分库的策略
# 指定分片列名称的 shardingColumn
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
# 指定分片算法行表达式的 algorithmExpression
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=test$->{user_id % 2}
设置绑定表和广播表
绑定表
所谓绑定表,是指与分片规则一致的一组主表和子表。例如,在我们的业务场景中,health_record 表和 health_task 表中都存在一个 record_id 字段。如果我们在应用过程中按照这个 record_id 字段进行分片,那么这两张表就可以构成互为绑定表关系。
引入绑定表概念的根本原因在于,互为绑定表关系的多表关联查询不会出现笛卡尔积,因此关联查询效率将大大提升。举例说明,如果所执行的为下面这条 SQL:
SELECT record.remark_name FROM health_record record JOIN health_task task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
如果没有绑定关系就会出现为笛卡尔积:
SELECT record.remark_name FROM health_record0 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
SELECT record.remark_name FROM health_record0 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
SELECT record.remark_name FROM health_record1 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
SELECT record.remark_name FROM health_record1 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
然后,在配置绑定表关系后,路由的 SQL 就会减少到 2 条:
SELECT record.remark_name FROM health_record0 record JOIN health_task0 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
SELECT record.remark_name FROM health_record1 record JOIN health_task1 task ON record.record_id=task.record_id WHERE record.record_id in (1, 2);
广播表
所谓广播表(BroadCastTable),是指所有分片数据源中都存在的表,也就是说,这种表的表结构和表中的数据在每个数据库中都是完全一样的。广播表的适用场景比较明确,通常针对数据量不大且需要与海量数据表进行关联查询的应用场景,典型的例子就是每个分片数据库中都应该存在的字典表。
广播表在插入数据的时候每个数据库都插入一样的数据
配置如下:
# 设置绑定表
spring.shardingsphere.sharding.binding-tables[0]=health_record,health_task
# 设置广播表
spring.shardingsphere.sharding.broadcast-tables[0]=health_level
设置分片规则
# user 如果不加这个,数据会随机插入数据库中 ; {[0,1]}和{0..1} 两种获取的结果一样,只是方式不同
spring.shardingsphere.sharding.tables.user.actual-data-nodes=test$->{[0,1]}.user
#路由到 test0 否则会随意添加到两个数据库中
spring.shardingsphere.sharding.tables.other_table.actual-data-nodes=test$->{0}.other_table
# health_record
spring.shardingsphere.sharding.tables.health_record.actual-data-nodes=test$->{0..1}.health_record
spring.shardingsphere.sharding.tables.health_record.key-generator.column=record_id
spring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE
# health_task
spring.shardingsphere.sharding.tables.health_task.actual-data-nodes=test$->{0..1}.health_task
spring.shardingsphere.sharding.tables.health_task.key-generator.column=task_id
spring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE
完整配置如下 (application-sharding-database.properties)
server.port=8080
#打印sql
spring.shardingsphere.props.sql.show=true
#配置数据源
spring.shardingsphere.datasource.names=test0,test1
#test0
spring.shardingsphere.datasource.test0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.test0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test0
spring.shardingsphere.datasource.test0.username=devadmin
spring.shardingsphere.datasource.test0.password=
#test1
spring.shardingsphere.datasource.test1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1
spring.shardingsphere.datasource.test1.username=devadmin
spring.shardingsphere.datasource.test1.password=
# 指定分片列名称的 shardingColumn
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
# 指定分片算法行表达式的 algorithmExpression
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=test$->{user_id % 2}
# 设置绑定表
spring.shardingsphere.sharding.binding-tables[0]=health_record,health_task
# 设置广播表
spring.shardingsphere.sharding.broadcast-tables[0]=health_level
# user 如果不加这个,数据会随机插入数据库中
spring.shardingsphere.sharding.tables.user.actual-data-nodes=test$->{[0,1]}.user
#路由到 test0 否则会随意添加到两个数据库中
spring.shardingsphere.sharding.tables.other_table.actual-data-nodes=test$->{0}.other_table
# health_record
spring.shardingsphere.sharding.tables.health_record.actual-data-nodes=test$->{0..1}.health_record
spring.shardingsphere.sharding.tables.health_record.key-generator.column=record_id
spring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE
# health_task
spring.shardingsphere.sharding.tables.health_task.actual-data-nodes=test$->{0..1}.health_task
spring.shardingsphere.sharding.tables.health_task.key-generator.column=task_id
spring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE
两个数据库的结构如下图
health_level 数据如下
health_level是广播表,两个库中的数据是完全一致的
user 表在两个数据库中的数据分布如下
分库的策略 test$->{user_id % 2} ,根据user_id 奇偶 分布插入 test1和test0
health_record 数据如下:
health_task 数据如下:
测试 health_record 和 health_task 关联,并通过 user_id进行过滤
SELECT t.task_id,t.record_id,t.user_id,t.task_name,r.level_id,r.remark
FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id
WHERE t.user_id =2
执行日志:
Actual SQL: test0 ::: SELECT t.task_id,t.record_id,t.user_id,t.task_name,r.level_id,r.remark FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id WHERE t.user_id =? ::: [2]
根据日志可以看出,由于 user_id=2 会被路由到 test0表中进行查询。
*测试 health_record 和 health_task 关联不进行过滤
SELECT t.task_id,t.record_id,t.user_id,t.task_name,r.level_id,r.remark
FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id
执行日志:
Actual SQL: test0 ::: SELECT t.task_id,t.record_id,t.user_id,t.task_name,r.level_id,r.remark FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id
Actual SQL: test1 ::: SELECT t.task_id,t.record_id,t.user_id,t.task_name,r.level_id,r.remark FROM health_task t INNER JOIN health_record r ON t.record_id = r.record_id
关于ShardingSphere中如何进行Sharding-JDBC分库的实战就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
亿速云「云服务器」,即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘,价格低至29元/月。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。
原文链接:https://my.oschina.net/u/3350266/blog/4645944