溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關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進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
