这篇文章将为大家详细讲解有关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分库的实战就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。