shardingsphere-jdbc5.5.1分庫(kù)分表實(shí)戰(zhàn)

更新時(shí)間：2026年02月03日 09:10:02 作者：荒古廢體

本文主要介紹了shardingsphere-jdbc5.5.1分庫(kù)分表實(shí)戰(zhàn),解決了分表和讀寫(xiě)分離的問(wèn)題,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

如何設(shè)計(jì)一個(gè)支持海浪訂單存儲(chǔ)的高擴(kuò)展性能？
在做分庫(kù)分表時(shí)，sharding key 的依據(jù)？
在做分庫(kù)分表時(shí)，基于hash取模和一致性hash的數(shù)據(jù)分片是如何實(shí)現(xiàn)的？
如何通過(guò)虛擬節(jié)點(diǎn)提高均衡度？

前言：

高性能數(shù)據(jù)庫(kù)集群的第一種方式是“讀寫(xiě)分離”，第二種方式是“數(shù)據(jù)庫(kù)分片”。

版本依賴：

主要版本：

Springboot 3.3.4

shardingsphere-jdbc 5.5.1

mybatis-plus-spring-boot3-starter 3.5.8

有三種方案：

只分庫(kù)不分表
只分表不分庫(kù)
既分庫(kù)又分表

本帖子只記錄單表多庫(kù)，多庫(kù)多表的例子。

功能實(shí)現(xiàn)

目錄結(jié)構(gòu)如下：

maven版本

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.atdx</groupId>
    <artifactId>sharding_sphere</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingSphere</name>

    <description>分庫(kù)分表案例</description>

    <properties>
        <java.version>17</java.version>
        <snakeyaml.version>2.2</snakeyaml.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.32</version>
        </dependency>

        <!-- 解決自帶 snakeyaml 版本過(guò)低的問(wèn)題-->
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>${snakeyaml.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc</artifactId>
            <version>5.5.1</version>
            <!-- 排除沖突的 SnakeYAML -->
            <exclusions>
                <exclusion>
                    <groupId>org.yaml</groupId>
                    <artifactId>snakeyaml</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
            <scope>runtime</scope>
        </dependency>

        <!-- MyBatis Plus Spring Boot 3兼容版本 -->
<!--        <dependency>-->
<!--            <groupId>com.baomidou</groupId>-->
<!--            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>-->
<!--            <version>3.5.8</version>-->
<!--        </dependency>-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </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>


    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.yaml</groupId>
                <artifactId>snakeyaml</artifactId>
                <version>${snakeyaml.version}</version>
            </dependency>
        </dependencies>
    </dependencyManagement>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

application.yml

########### V1 實(shí)現(xiàn)了 單庫(kù)，多個(gè)表 #############
spring:
  application:
    name: sharding-jdbc-demo
  # 使用ShardingSphere Driver + 獨(dú)立配置文件
  # 配置數(shù)據(jù)庫(kù)的連接信息
  datasource:
    #動(dòng)態(tài)數(shù)據(jù)源配置
    dynamic:
      #主數(shù)據(jù)源，默認(rèn)啟用
      primary: master
      datasource:
        #數(shù)據(jù)源 本地庫(kù)
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/wedding_bill_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true&allowMultiQueries=true
          username: root
          password: rootroot
          # 分庫(kù)分表邏輯
        shardingSphere:
          driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
          url: jdbc:shardingsphere:classpath:sharding.yaml
          # 移除username和password，因?yàn)樵趕harding.yaml中配置
#          username: root
#          password: rootroot


# MyBatis Plus 配置
mybatis-plus:
  mapper-locations: classpath:mapper/shard/**/*.xml
  type-aliases-package: com.**.**.entity
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      # 雪花算法
      id-type: ASSIGN_ID

# 分庫(kù)分表的密鑰 用于手機(jī)號(hào)接收加密和解密用
sharding:
  sphere:
    secretKey:
      phoneNum: T9ycWqd/VgQ32x0MRxU0cQ==

1，單庫(kù)多表

sharding.yaml 配置如下

####################### V1 第一版 簡(jiǎn)單實(shí)現(xiàn)單個(gè)庫(kù)，每個(gè)庫(kù)有3個(gè)表的 分庫(kù)邏輯############################################################################
dataSources:
  ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/db_user?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot

rules:
  - !SHARDING
    tables:
      t_user:
        actualDataNodes: ds.t_user_$->{0..2}
        tableStrategy:
          standard:
            # 分表字段 phone_num 根據(jù)手機(jī)號(hào)進(jìn)行分表
            shardingColumn: phone_num
            # 保持一致性
            shardingAlgorithmName: db-user-inline
    shardingAlgorithms:
      db-user-inline:
        type: INLINE
        props:
          # 根據(jù)手機(jī)號(hào)取模 hash 運(yùn)算
          algorithm-expression: t_user_$->{Math.abs(phone_num.hashCode()) % 3}
    defaultDatabaseStrategy:
      none:

props:
  sql-show: true

自動(dòng)生成手機(jī)號(hào)工具類(lèi)：

package com.at.ss.shardingsphere.utils;

import java.util.*;
import java.util.concurrent.atomic.AtomicLong;

/**
 * 手機(jī)號(hào)生成工具類(lèi)
 * @author daxigua
 */
public class PhoneNumberGenerator {

    // 手機(jī)號(hào)前綴（中國(guó)大陸）
    private static final String[] PHONE_PREFIXES = {
            "130", "131", "132", "133", "134", "135", "136", "137", "138", "139",
            "145", "147", "149", "150", "151", "152", "153", "155", "156", "157", "158", "159",
            "165", "166", "167", "170", "171", "172", "173", "174", "175", "176", "177", "178",
            "180", "181", "182", "183", "184", "185", "186", "187", "188", "189",
            "191", "198", "199"
    };

    private static final Random random = new Random();
    private static final AtomicLong counter = new AtomicLong(1000000000L);

    /**
     * 生成隨機(jī)手機(jī)號(hào)
     */
    public static String generateRandomPhone() {
        String prefix = PHONE_PREFIXES[random.nextInt(PHONE_PREFIXES.length)];
        String suffix = String.format("%08d", random.nextInt(100000000));
        return prefix + suffix;
    }

    /**
     * 生成序列化手機(jī)號(hào)（避免重復(fù)）
     */
    public static String generateSequentialPhone() {
        long number = counter.getAndIncrement();
        String prefix = PHONE_PREFIXES[(int) (number % PHONE_PREFIXES.length)];
        String suffix = String.format("%08d", number % 100000000);
        return prefix + suffix;
    }

    /**
     * 批量生成手機(jī)號(hào)
     */
    public static List<String> generateBatchPhones(int count, boolean sequential) {
        Set<String> phones = new HashSet<>(count) {
        };
        for (int i = 0; i < count; i++) {
            if (sequential) {
                phones.add(generateSequentialPhone());
            } else {
                phones.add(generateRandomPhone());
            }
        }
        return new ArrayList<>( phones);
    }
}

雪花算法ID 工具類(lèi)

package com.at.ss.shardingsphere.utils;

import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class SnowflakeIdGenerator {

    // ==============================Fields===========================================
    /** 開(kāi)始時(shí)間截 (2024-01-01) */
    private final long startTimestamp = 1704067200000L;

    /** 機(jī)器id所占的位數(shù) */
    private final long workerIdBits = 5L;

    /** 數(shù)據(jù)標(biāo)識(shí)id所占的位數(shù) */
    private final long datacenterIdBits = 5L;

    /** 支持的最大機(jī)器id，結(jié)果是31 (這個(gè)移位算法可以很快的計(jì)算出幾位二進(jìn)制數(shù)所能表示的最大十進(jìn)制數(shù)) */
    private final long maxWorkerId = -1L ^ (-1L << workerIdBits);

    /** 支持的最大數(shù)據(jù)標(biāo)識(shí)id，結(jié)果是31 */
    private final long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);

    /** 序列在id中占的位數(shù) */
    private final long sequenceBits = 12L;

    /** 機(jī)器ID向左移12位 */
    private final long workerIdShift = sequenceBits;

    /** 數(shù)據(jù)標(biāo)識(shí)id向左移17位(12+5) */
    private final long datacenterIdShift = sequenceBits + workerIdBits;

    /** 時(shí)間截向左移22位(5+5+12) */
    private final long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;

    /** 生成序列的掩碼，這里為4095 (0b111111111111=0xfff=4095) */
    private final long sequenceMask = -1L ^ (-1L << sequenceBits);

    /** 工作機(jī)器ID(0~31) */
    private long workerId;

    /** 數(shù)據(jù)中心ID(0~31) */
    private long datacenterId;

    /** 毫秒內(nèi)序列(0~4095) */
    private long sequence = 0L;

    /** 上次生成ID的時(shí)間截 */
    private long lastTimestamp = -1L;

    // ==============================Constructors=====================================
    /**
     * 構(gòu)造函數(shù)
     */
    public SnowflakeIdGenerator() {
        if (workerId > maxWorkerId || workerId < 0) {
            throw new IllegalArgumentException(String.format("worker Id can't be greater than %d or less than 0", maxWorkerId));
        }
        if (datacenterId > maxDatacenterId || datacenterId < 0) {
            throw new IllegalArgumentException(String.format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId));
        }
        this.workerId = workerId;
        this.datacenterId = datacenterId;
    }

    // ==============================Methods==========================================
    /**
     * 獲得下一個(gè)ID (該方法是線程安全的)
     * @return SnowflakeId
     */
    public synchronized long nextId() {
        long timestamp = timeGen();

        // 如果當(dāng)前時(shí)間小于上一次ID生成的時(shí)間戳，說(shuō)明系統(tǒng)時(shí)鐘回退過(guò)這個(gè)時(shí)候應(yīng)當(dāng)拋出異常
        if (timestamp < lastTimestamp) {
            throw new RuntimeException(
                    String.format("Clock moved backwards. Refusing to generate id for %d milliseconds", lastTimestamp - timestamp));
        }

        // 如果是同一時(shí)間生成的，則進(jìn)行毫秒內(nèi)序列
        if (lastTimestamp == timestamp) {
            sequence = (sequence + 1) & sequenceMask;
            // 毫秒內(nèi)序列溢出
            if (sequence == 0) {
                // 阻塞到下一個(gè)毫秒,獲得新的時(shí)間戳
                timestamp = tilNextMillis(lastTimestamp);
            }
        }
        // 時(shí)間戳改變，毫秒內(nèi)序列重置
        else {
            sequence = 0L;
        }

        // 上次生成ID的時(shí)間截
        lastTimestamp = timestamp;

        // 移位并通過(guò)或運(yùn)算拼到一起組成64位的ID
        return ((timestamp - startTimestamp) << timestampLeftShift) //
                | (datacenterId << datacenterIdShift) //
                | (workerId << workerIdShift) //
                | sequence;
    }

    /**
     * 阻塞到下一個(gè)毫秒，直到獲得新的時(shí)間戳
     * @param lastTimestamp 上次生成ID的時(shí)間截
     * @return 當(dāng)前時(shí)間戳
     */
    protected long tilNextMillis(long lastTimestamp) {
        long timestamp = timeGen();
        while (timestamp <= lastTimestamp) {
            timestamp = timeGen();
        }
        return timestamp;
    }

    /**
     * 返回以毫秒為單位的當(dāng)前時(shí)間
     * @return 當(dāng)前時(shí)間(毫秒)
     */
    protected long timeGen() {
        return System.currentTimeMillis();
    }

    /**
     * 生成指定數(shù)量的ID數(shù)組（用于測(cè)試分庫(kù)分表）
     */
    public long[] generateIds(int count) {
        long[] ids = new long[count];
        for (int i = 0; i < count; i++) {
            ids[i] = nextId();
        }
        return ids;
    }

    /**
     * 生成指定數(shù)量的ID并放入ArrayList集合
     */
    public List<Long> generateIdList(int count) {
        List<Long> idList = new ArrayList<>(count);
        for (int i = 0; i < count; i++) {
            idList.add(nextId());
        }
        return idList;
    }

    /**
     * 生成指定數(shù)量的ID并放入LinkedList集合
     */
    public List<Long> generateIdLinkedList(int count) {
        List<Long> idList = new LinkedList<>();
        for (int i = 0; i < count; i++) {
            idList.add(nextId());
        }
        return idList;
    }

    /**
     * 生成指定數(shù)量的ID并放入線程安全的CopyOnWriteArrayList集合
     */
    public List<Long> generateIdConcurrentList(int count) {
        List<Long> idList = new CopyOnWriteArrayList<>();
        for (int i = 0; i < count; i++) {
            idList.add(nextId());
        }
        return idList;
    }

    /**
     * 批量生成ID并放入指定集合（通用方法）
     */
    public <T extends Collection<Long>> T generateIdsToCollection(int count, T collection) {
        for (int i = 0; i < count; i++) {
            collection.add(nextId());
        }
        return collection;
    }

    /**
     * 生成ID并分批放入多個(gè)集合（用于分庫(kù)分表測(cè)試）
     */
    public Map<Integer, List<Long>> generateIdsForSharding(int totalCount, int shardCount) {
        Map<Integer, List<Long>> shardMap = new HashMap<>();

        // 初始化每個(gè)分片的集合
        for (int i = 0; i < shardCount; i++) {
            shardMap.put(i, new ArrayList<>());
        }

        // 生成ID并根據(jù)分片規(guī)則分配到不同集合
        for (int i = 0; i < totalCount; i++) {
            long id = nextId();
            int shardIndex = (int) (id % shardCount); // 簡(jiǎn)單的取模分片
            shardMap.get(shardIndex).add(id);
        }

        return shardMap;
    }

    /**
     * 異步生成ID集合（高性能版本）
     */
    public CompletableFuture<List<Long>> generateIdsAsync(int count) {
        return CompletableFuture.supplyAsync(() -> generateIdList(count));
    }

    /**
     * 流式生成ID集合（Java 8 Stream API）
     */
    public List<Long> generateIdsStream(int count) {
        return Stream.generate(this::nextId)
                .limit(count)
                .collect(Collectors.toList());
    }
}

測(cè)試方法：

  /**
     * 測(cè)試單庫(kù)分表，每個(gè)庫(kù)3張表
     */
    @Test
    public void singleDatabaseWithTablePartitioning(){
        SnowflakeIdGenerator snowflakeIdGenerator = new SnowflakeIdGenerator();
        List<String> phones = PhoneNumberGenerator.generateBatchPhones(500, false);
        ArrayList<Long> snowflakeIds = snowflakeIdGenerator.generateIdsToCollection(500, new ArrayList<>());
        for (int i = 0; i < 500; i++) {
            User user = new User();
            user.setId(snowflakeIds.get(i));
            user.setUname("張三豐");
            user.setPhoneNum(phones.get(i));
            // 驗(yàn)證分表計(jì)算
            String tableName = this.calculateTableName(user.getPhoneNum());
            log.info("手機(jī)號(hào) " + user.getPhoneNum() + " 應(yīng)該分配到表: " + tableName);
            userMapper.insert(user);
            log.info("插入用戶ID: " + user.getId() + ", 手機(jī)號(hào): " + user.getPhoneNum());
        }
    }
    /**
     * 計(jì)算手機(jī)號(hào)對(duì)應(yīng)的分表
     */
    public String calculateTableName(String phone) {
        String cleanPhone = phone.replaceAll("[^0-9]", "");
        int tableIndex = Math.abs(cleanPhone.hashCode()) % 3;
        return "t_user_" + tableIndex;
    }

2，多庫(kù)多表

application.yml的配置和上面一樣

sharding.yaml 配置如下

######################## V2 第二版 實(shí)現(xiàn)10個(gè)庫(kù)，每個(gè)庫(kù)有3個(gè)表的 分庫(kù)邏輯############################################################################
dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_0?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_1?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_2:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_2?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_3:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_3?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_4:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_4?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_5:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_5?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_6:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_6?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_7:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_7?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_8:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_8?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot
  ds_9:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/db_user_9?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: rootroot

rules:
  - !SHARDING
    tables:
      t_stu:
        actualDataNodes: ds_$->{0..9}.t_stu_$->{0..2}
        # 分庫(kù)策略 - 基于ID取模
        databaseStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: database-sharding
        # 分表策略
        tableStrategy:
          standard:
            shardingColumn: phone_num
            shardingAlgorithmName: table-sharding

      # 教師表 分庫(kù)分表策略
      t_tea:
        actualDataNodes: ds_$->{0..9}.t_tea_$->{0..2}
        # 分庫(kù)策略 - 基于ID取模
        databaseStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: database-sharding
        # 分表策略
        tableStrategy:
          standard:
            shardingColumn: phone_num
            shardingAlgorithmName: table-sharding

    shardingAlgorithms:
      # 分庫(kù)算法取模
      database-sharding:
        type: CLASS_BASED
        props:
          strategy: standard
          algorithmClassName: com.at.ss.shardingsphere.algorithm.DatabaseShardingAlgorithm
      # 分表算法 - 修正算法名稱(chēng)
      table-sharding:
        type: CLASS_BASED
        props:
          strategy: standard
          algorithmClassName: com.at.ss.shardingsphere.algorithm.MobileHashShardingAlgorithm

props:
  sql-show: true

數(shù)據(jù)庫(kù)的雪花ID分庫(kù)算法如下

package com.at.ss.shardingsphere.algorithm;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.util.Collection;

/**
 * @author daxigua 基于雪花算法ID的分庫(kù)算法
 */
@Slf4j
public class DatabaseShardingAlgorithm implements StandardShardingAlgorithm<Long> {

/*  根據(jù)雪花算法 ID 取模進(jìn)行 分庫(kù) */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long id = shardingValue.getValue();
        // 雪花算法ID取模分庫(kù)
        int databaseIndex = Math.abs(id.intValue() % availableTargetNames.size());
        for (String databaseName : availableTargetNames) {
            if (databaseName.endsWith("_" + databaseIndex)) {
                log.info("最終落到數(shù)據(jù)庫(kù)是====>>>> ：{}" , databaseName);
                return databaseName;
            }
        }
        throw new IllegalArgumentException("未找到對(duì)應(yīng)的分庫(kù)，計(jì)算的庫(kù)索引: " + databaseIndex);
    }

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        // 范圍查詢返回所有的庫(kù)
        log.info("范圍查詢返回所有的庫(kù):{}" , availableTargetNames);
        return availableTargetNames;
    }
}

分表的算法如下：

package com.at.ss.shardingsphere.algorithm;

import com.at.ss.shardingsphere.utils.CryptoUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.util.Collection;

/**
 * @author daxigua 根據(jù)加密后的手機(jī)號(hào)進(jìn)行分表
 *  手機(jī)號(hào)分表算法
 */
@Slf4j
public class MobileHashShardingAlgorithm implements StandardShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        String phoneNum = shardingValue.getValue();
        // 基于手機(jī)號(hào)計(jì)算分片表位置
        int tableIndex = CryptoUtils.calculateTableShard(phoneNum);
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith("_" + tableIndex)){
                log.info("手機(jī)號(hào) " + phoneNum + " 應(yīng)該分配到表: " + tableName);
                return tableName;
            }
        }
        throw new IllegalArgumentException("未找到對(duì)應(yīng)的分表，計(jì)算的分表索引: " + tableIndex);
    }


    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<String> rangeShardingValue) {
        // 范圍查詢返回所有的表，確保能查詢到所有的表數(shù)據(jù)
        log.info("范圍查詢，返回所有的表 :{} " , availableTargetNames);
        return availableTargetNames;
    }
}

3，sql

--------------------------------V1 - 單庫(kù)多表進(jìn)行拆分 【水平拆分】----------------------------------------------------
-- 創(chuàng)建數(shù)據(jù)庫(kù) 1個(gè)庫(kù)都，每個(gè)都創(chuàng)建3個(gè)表
CREATE DATABASE db_user;
USE db_user;

-- 創(chuàng)建表
CREATE TABLE t_user_0(
    id        BIGINT PRIMARY KEY,
    uname     VARCHAR(50),
    phone_num VARCHAR(128)
);

CREATE TABLE t_user_1(
    id        BIGINT PRIMARY KEY,
    uname     VARCHAR(50),
    phone_num VARCHAR(128)
);

CREATE TABLE t_user_2(
    id        BIGINT PRIMARY KEY,
    uname     VARCHAR(50),
    phone_num VARCHAR(128)
);


--------------------------------- V2 - 多庫(kù)多表 3個(gè)庫(kù)，每個(gè)庫(kù)3個(gè)表---------------------------------------------------
-- 創(chuàng)建數(shù)據(jù)庫(kù) 10個(gè)庫(kù)都，每個(gè)都創(chuàng)建3個(gè)表，并且根據(jù)雪花ID取模進(jìn)行分庫(kù)，根據(jù)加密后的手機(jī)號(hào)取hash取模
CREATE DATABASE db_user_0;
CREATE DATABASE db_user_1;
CREATE DATABASE db_user_2;
CREATE DATABASE db_user_3;
CREATE DATABASE db_user_4;
CREATE DATABASE db_user_5;
CREATE DATABASE db_user_6;
CREATE DATABASE db_user_7;
CREATE DATABASE db_user_8;
CREATE DATABASE db_user_9;

-- 創(chuàng)建表
CREATE TABLE t_stu_0(
    id        BIGINT PRIMARY KEY,
    uname     VARCHAR(50),
    phone_num VARCHAR(128),
    subject_name VARCHAR(50)
);

CREATE TABLE t_stu_1(
    id        BIGINT PRIMARY KEY,
    uname     VARCHAR(50),
    phone_num VARCHAR(128),
    subject_name VARCHAR(50)
);

CREATE TABLE t_stu_2(
    id        BIGINT PRIMARY KEY,
    uname     VARCHAR(50),
    phone_num VARCHAR(128),
    subject_name VARCHAR(50)
);


--------------------------------- V2 - 多庫(kù)多表 10個(gè)庫(kù)，每個(gè)庫(kù)3個(gè)表---------------------------------------------------
-- 主要測(cè)試相同的根據(jù)手機(jī)號(hào)分庫(kù)分表策略
CREATE TABLE t_tea_0(
    id           BIGINT PRIMARY KEY,
    uname        VARCHAR(50),
    phone_num    VARCHAR(128),
    subject_name VARCHAR(50)
);

CREATE TABLE t_tea_1(
    id           BIGINT PRIMARY KEY,
    uname        VARCHAR(50),
    phone_num    VARCHAR(128),
    subject_name VARCHAR(50)
);

CREATE TABLE t_tea_2(
    id           BIGINT PRIMARY KEY,
    uname        VARCHAR(50),
    phone_num    VARCHAR(128),
    subject_name VARCHAR(50)
);

問(wèn)題

1.為什么要分庫(kù)分表？

分庫(kù)解決的是并發(fā)量大的問(wèn)題。通過(guò)增加數(shù)據(jù)庫(kù)的實(shí)例數(shù)來(lái)提供更多的可用連接，提高系統(tǒng)的并發(fā)性能，提高查詢效率
分表的解決的是數(shù)據(jù)量大的問(wèn)題，存儲(chǔ)和查詢的性能已經(jīng)為瓶頸，此時(shí)做了很多的優(yōu)化，依然沒(méi)有效果的話。

數(shù)據(jù)量大，就分表，并發(fā)高就分庫(kù)

2. 如何選擇 sharing key ？

Sharding key :分表的字段，選擇 sharing key 最重要的參考因素：我們的業(yè)務(wù)是如何訪問(wèn)數(shù)據(jù)的。

假設(shè)app的訂單ID 【orderId】作為 sharding key 行不行？公司的營(yíng)收來(lái)源于用戶，而用戶對(duì)于訂單最高的訪問(wèn)是 app中“我的訂單”頁(yè)面，此時(shí)的查詢條件：用戶ID。但是我們分片的依據(jù)是orderId ，沒(méi)發(fā)查詢。強(qiáng)行查詢的只能是查詢所有的分片，合并查詢結(jié)果，效率低，沒(méi)法分頁(yè)。

如果把userId作為sharing key 行不行？此時(shí)用戶在“我的訂單”頁(yè)面正好可以使用分片鍵，一個(gè)用戶對(duì)應(yīng)的訂單信息都在一個(gè)分片中，

因?yàn)榉制鞘褂肬ser ID，此時(shí)效率最高。直接去對(duì)應(yīng)分片查詢就行了。

可以理解：產(chǎn)品靠什么賺錢(qián)，主要用到查詢的條件是什么

3.有使用order ID 進(jìn)行查詢的場(chǎng)景怎么辦？

網(wǎng)上說(shuō)是下面這個(gè)方法，還未驗(yàn)證
在生成訂單ID的時(shí)候，可以用戶ID的后幾位作為訂單ID的一部分。比如18位的訂單號(hào)，第15-18位是用戶的后4位，此時(shí)按照訂單ID查詢的時(shí)候，可以根據(jù)訂單ID的用戶ID找到分片。

遇到的問(wèn)題：

1，shardingsphere-jdbc 5.5.1 和 spring-boot-starter-parent 2.7.18【spring-boot-starter-parent 3.3.4】寫(xiě)法不一樣

2，shardingsphere-jdbc 5.5.1 如果存在多數(shù)據(jù)源的情況下，mybatis-plus-spring-boot3-starter 3.5.8 如果低于3.5.8會(huì)導(dǎo)致多數(shù)據(jù)源和分庫(kù)分表數(shù)據(jù)源沖突不適配
3，shardingsphere-jdbc 5.5.1 的 snakeyaml 自帶版本是2.0，項(xiàng)目配置多庫(kù)多表不支持，后面需要先排除再引入2.2 多依賴

到此這篇關(guān)于shardingsphere-jdbc5.5.1分庫(kù)分表實(shí)戰(zhàn)的文章就介紹到這了,更多相關(guān)shardingsphere-jdbc 分庫(kù)分表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: