ShardingSphere中如何进行Sharding-JDBC分库的实战

这篇文章将为大家详细讲解有关ShardingSphere中如何进行Sharding-JDBC分库的实战，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

我们使用SpringBoot+Mybaits-plus来搭建。数据库表我们使用 User、HealthRecord、HealthLevel 和 HealthTask 这四个业务对象。在下面这张图中，对每个业务对象给出最基础的字段定义，以及这四个对象之间的关联关系：

pom.xml结构如下

          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#test0spring.shardingsphere.datasource.test0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.test0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.test0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test0spring.shardingsphere.datasource.test0.username=devadminspring.shardingsphere.datasource.test0.password=#test1spring.shardingsphere.datasource.test1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1spring.shardingsphere.datasource.test1.username=devadminspring.shardingsphere.datasource.test1.password=

设置分库的策略

# 指定分片列名称的 shardingColumnspring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id# 指定分片算法行表达式的 algorithmExpressionspring.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_recordspring.shardingsphere.sharding.tables.health_record.actual-data-nodes=test$->{0..1}.health_recordspring.shardingsphere.sharding.tables.health_record.key-generator.column=record_idspring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE# health_taskspring.shardingsphere.sharding.tables.health_task.actual-data-nodes=test$->{0..1}.health_taskspring.shardingsphere.sharding.tables.health_task.key-generator.column=task_idspring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE

完整配置如下 （application-sharding-database.properties）

server.port=8080#打印sqlspring.shardingsphere.props.sql.show=true#配置数据源spring.shardingsphere.datasource.names=test0,test1#test0spring.shardingsphere.datasource.test0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.test0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.test0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test0spring.shardingsphere.datasource.test0.username=devadminspring.shardingsphere.datasource.test0.password=#test1spring.shardingsphere.datasource.test1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1spring.shardingsphere.datasource.test1.username=devadminspring.shardingsphere.datasource.test1.password=# 指定分片列名称的 shardingColumnspring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id# 指定分片算法行表达式的 algorithmExpressionspring.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_recordspring.shardingsphere.sharding.tables.health_record.actual-data-nodes=test$->{0..1}.health_recordspring.shardingsphere.sharding.tables.health_record.key-generator.column=record_idspring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE# health_taskspring.shardingsphere.sharding.tables.health_task.actual-data-nodes=test$->{0..1}.health_taskspring.shardingsphere.sharding.tables.health_task.key-generator.column=task_idspring.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分库的实战就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。