这篇文章将为大家详细讲解有关ShardingSphere中如何进行Sharding-JDBC分库的实战，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
创新互联公司专业为企业提供老河口网站建设、老河口做网站、老河口网站设计、老河口网站制作等企业网站建设、网页设计与制作、老河口企业网站模板建站服务，10年老河口做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。
我们使用SpringBoot+Mybaits-plus来搭建。数据库表我们使用 User、HealthRecord、HealthLevel 和 HealthTask 这四个业务对象。在下面这张图中，对每个业务对象给出最基础的字段定义，以及这四个对象之间的关联关系：
1.8 UTF-8 UTF-8 2.3.0.RELEASE org.springframework.boot spring-boot-starter-web org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.1.1 com.baomidou mybatis-plus-boot-starter 3.4.0 org.projectlombok lombok true MySQL mysql-connector-java runtime org.springframework.boot spring-boot-starter-test test org.junit.vintage junit-vintage-engine
@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分库的实战就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
