ShardingSphere中如何進(jìn)行Sharding-JDBC分庫(kù)的實(shí)戰(zhàn)

這篇文章將為大家詳細(xì)講解有關(guān)ShardingSphere中如何進(jìn)行Sharding-JDBC分庫(kù)的實(shí)戰(zhàn)，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

我們使用SpringBoot+Mybaits-plus來(lái)搭建。數(shù)據(jù)庫(kù)表我們使用 User、HealthRecord、HealthLevel 和 HealthTask 這四個(gè)業(yè)務(wù)對(duì)象。在下面這張圖中，對(duì)每個(gè)業(yè)務(wù)對(duì)象給出最基礎(chǔ)的字段定義，以及這四個(gè)對(duì)象之間的關(guān)聯(lián)關(guān)系：

pom.xml結(jié)構(gòu)如下

  <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>

項(xiàng)目結(jié)構(gòu)如

構(gòu)造測(cè)試數(shù)據(jù)

@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);
    }
}

分庫(kù)配置

配置數(shù)據(jù)源，這里分庫(kù)配置了兩個(gè)數(shù)據(jù)源分別為 test0、test1

#配置數(shù)據(jù)源
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=

設(shè)置分庫(kù)的策略

# 指定分片列名稱的 shardingColumn
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
# 指定分片算法行表達(dá)式的 algorithmExpression
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=test$->{user_id % 2}

設(shè)置綁定表和廣播表

綁定表

所謂綁定表，是指與分片規(guī)則一致的一組主表和子表。例如，在我們的業(yè)務(wù)場(chǎng)景中，health_record 表和 health_task 表中都存在一個(gè) record_id 字段。如果我們?cè)趹?yīng)用過(guò)程中按照這個(gè) record_id 字段進(jìn)行分片，那么這兩張表就可以構(gòu)成互為綁定表關(guān)系。

引入綁定表概念的根本原因在于，互為綁定表關(guān)系的多表關(guān)聯(lián)查詢不會(huì)出現(xiàn)笛卡爾積，因此關(guān)聯(lián)查詢效率將大大提升。舉例說(shuō)明，如果所執(zhí)行的為下面這條 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);

如果沒有綁定關(guān)系就會(huì)出現(xiàn)為笛卡爾積：

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);

然后，在配置綁定表關(guān)系后，路由的 SQL 就會(huì)減少到 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），是指所有分片數(shù)據(jù)源中都存在的表，也就是說(shuō)，這種表的表結(jié)構(gòu)和表中的數(shù)據(jù)在每個(gè)數(shù)據(jù)庫(kù)中都是完全一樣的。廣播表的適用場(chǎng)景比較明確，通常針對(duì)數(shù)據(jù)量不大且需要與海量數(shù)據(jù)表進(jìn)行關(guān)聯(lián)查詢的應(yīng)用場(chǎng)景，典型的例子就是每個(gè)分片數(shù)據(jù)庫(kù)中都應(yīng)該存在的字典表。

廣播表在插入數(shù)據(jù)的時(shí)候每個(gè)數(shù)據(jù)庫(kù)都插入一樣的數(shù)據(jù)

配置如下：

# 設(shè)置綁定表
spring.shardingsphere.sharding.binding-tables[0]=health_record,health_task
# 設(shè)置廣播表
spring.shardingsphere.sharding.broadcast-tables[0]=health_level

設(shè)置分片規(guī)則

# user 如果不加這個(gè)，數(shù)據(jù)會(huì)隨機(jī)插入數(shù)據(jù)庫(kù)中 ;  {[0,1]}和{0..1} 兩種獲取的結(jié)果一樣，只是方式不同
spring.shardingsphere.sharding.tables.user.actual-data-nodes=test$->{[0,1]}.user
#路由到 test0 否則會(huì)隨意添加到兩個(gè)數(shù)據(jù)庫(kù)中
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
#配置數(shù)據(jù)源
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
# 指定分片算法行表達(dá)式的 algorithmExpression
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=test$->{user_id % 2}
# 設(shè)置綁定表
spring.shardingsphere.sharding.binding-tables[0]=health_record,health_task
# 設(shè)置廣播表
spring.shardingsphere.sharding.broadcast-tables[0]=health_level
# user 如果不加這個(gè)，數(shù)據(jù)會(huì)隨機(jī)插入數(shù)據(jù)庫(kù)中
spring.shardingsphere.sharding.tables.user.actual-data-nodes=test$->{[0,1]}.user
#路由到 test0 否則會(huì)隨意添加到兩個(gè)數(shù)據(jù)庫(kù)中
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

數(shù)據(jù)庫(kù)中的結(jié)果如下：

兩個(gè)數(shù)據(jù)庫(kù)的結(jié)構(gòu)如下圖

health_level 數(shù)據(jù)如下

health_level是廣播表，兩個(gè)庫(kù)中的數(shù)據(jù)是完全一致的

user 表在兩個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)分布如下

分庫(kù)的策略 test$->{user_id % 2} ，根據(jù)user_id 奇偶 分布插入 test1和test0

health_record 數(shù)據(jù)如下：

health_task 數(shù)據(jù)如下：

查詢測(cè)試

測(cè)試 health_record 和 health_task 關(guān)聯(lián)，并通過(guò) user_id進(jìn)行過(guò)濾

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

執(zhí)行日志：

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]

根據(jù)日志可以看出，由于 user_id=2 會(huì)被路由到 test0表中進(jìn)行查詢。

*測(cè)試 health_record 和 health_task 關(guān)聯(lián)不進(jìn)行過(guò)濾

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

執(zhí)行日志：

 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

關(guān)于ShardingSphere中如何進(jìn)行Sharding-JDBC分庫(kù)的實(shí)戰(zhàn)就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。