MySQL分區(qū)分表實現(xiàn)方法示例詳解

更新時間：2025年10月11日 09:25:18 作者：muxin-始終如一

ShardingSphere-JDBC通過在應(yīng)用層進行數(shù)據(jù)分片,可以幫你輕松實現(xiàn)分區(qū)、分表和分庫,下面我用具體的配置和代碼示例來說明如何使用,本文結(jié)合實例代碼給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧

特性	分表	分庫	分區(qū)（按特定規(guī)則如時間）
數(shù)據(jù)分布	同一庫中多表	不同庫中表	同一庫或多庫中按規(guī)則分表
性能影響	減輕單表壓力	減輕單庫壓力（可配合不同服務(wù)器）	常用于按時間歸檔，優(yōu)化查詢和管理
配置要點	指定分表算法	指定分庫算法	通常需要自定義復(fù)合分片算法
適用場景	單庫數(shù)據(jù)量大	數(shù)據(jù)量大且并發(fā)高，需分散IO	數(shù)據(jù)有明顯冷熱特征，需定期歸檔

?? 分庫分表依賴

首先，確保你的 pom.xml 包含以下依賴（以 Spring Boot Starter 為例）：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version> <!-- 請使用最新穩(wěn)定版本 -->
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

?? 核心概念與配置示例

1. 水平分表 (Horizontal Table Sharding)

概念：將一個邏輯表的數(shù)據(jù)，按照某種規(guī)則拆分到同一個數(shù)據(jù)庫中的多個物理表中。

YAML 配置示例：

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order: # 邏輯表名
            actual-data-nodes: ds0.t_order_$->{0..1} # 實際數(shù)據(jù)節(jié)點，ds0庫下t_order_0, t_order_1兩張表
            table-strategy:
              standard:
                sharding-column: order_id # 分片字段
                sharding-algorithm-name: table-inline # 分表算法名稱
        sharding-algorithms:
          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 2} # 分片算法表達式，按order_id取模分到兩個表
    props:
      sql-show: true # 打印SQL，方便調(diào)試

代碼使用：
配置好后，在代碼中操作邏輯表 t_order 即可，ShardingSphere-JDBC 會自動路由到具體的物理表。

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一條order_id為123的訂單，根據(jù) 123 % 2 = 1，會路由到 t_order_1 表
    String sql = "INSERT INTO t_order (order_id, user_id, amount) VALUES (?, ?, ?)";
    jdbcTemplate.update(sql, 123L, 1000L, 200.00);
    // 查詢order_id為123的訂單，同樣會路由到 t_order_1 表
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE order_id = ?", 123L);
}

2. 水平分庫 (Horizontal Database Sharding)

概念：將一個邏輯表的數(shù)據(jù)，按照某種規(guī)則拆分到多個不同的數(shù)據(jù)庫中（每個數(shù)據(jù)庫可以包含該邏輯表的一個或多個物理表）。

YAML 配置示例：

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1 # 定義兩個數(shù)據(jù)源
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order:
            # 實際數(shù)據(jù)節(jié)點，兩個庫，每個庫一張表（也可每個庫多張表）
            actual-data-nodes: ds$->{0..1}.t_order
            database-strategy: # 分庫策略
              standard:
                sharding-column: user_id # 分庫字段
                sharding-algorithm-name: database-inline # 分庫算法名稱
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2} # 按user_id取模分庫
    props:
      sql-show: true

代碼使用：
操作邏輯表 t_order，ShardingSphere-JDBC 根據(jù) user_id 自動路由到對應(yīng)的數(shù)據(jù)庫。

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一條user_id為1001的訂單，根據(jù) 1001 % 2 = 1，會路由到 ds1 庫的 t_order 表
    String sql = "INSERT INTO t_order (order_id, user_id, amount) VALUES (?, ?, ?)";
    jdbcTemplate.update(sql, 456L, 1001L, 300.00);
    // 查詢user_id為1001的訂單，同樣會路由到 ds1 庫
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE user_id = ?", 1001L);
}

3. 分區(qū)（自定義復(fù)合分片策略）

概念：這里的"分區(qū)"可以理解為更復(fù)雜的分片策略，例如按時間范圍（如月份）分表，可能同時結(jié)合分庫。這通常需要自定義分片算法。

場景：訂單表按月度分表，如 t_order_202410, t_order_202411。

YAML 配置示例：

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds0.t_order_$->{202410..202412} # 假設(shè)配置未來幾個月的表
            table-strategy:
              standard:
                sharding-column: create_time # 分片字段為創(chuàng)建時間
                sharding-algorithm-name: table-time-month # 使用自定義的時間按月分表算法
        sharding-algorithms:
          table-time-month:
            type: CLASS_BASED # 使用自定義算法
            props:
              strategy: standard
              algorithmClassName: com.yourpackage.algorithm.TimeMonthShardingAlgorithm # 自定義算法類
    props:
      sql-show: true

自定義分片算法實現(xiàn)：
你需要實現(xiàn) StandardShardingAlgorithm 接口。

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.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
public class TimeMonthShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {
    private final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
        // 處理精確分片，如 =, IN
        String logicTableName = shardingValue.getLogicTableName();
        LocalDateTime createTime = shardingValue.getValue();
        String actualTableSuffix = formatter.format(createTime); // 根據(jù)時間生成表后綴，如202410
        String actualTableName = logicTableName + "_" + actualTableSuffix; // 拼接實際表名，如t_order_202410
        // 檢查計算出的表是否存在配置中
        for (String each : availableTargetNames) {
            if (each.equals(actualTableName)) {
                return actualTableName;
            }
        }
        throw new IllegalArgumentException("No actual table found for: " + actualTableName);
    }
    @Override
    public Properties getProps() {
        return new Properties();
    }
    @Override
    public void init(Properties properties) {
        // 初始化操作，如果需要
    }
    // 注意：ShardingSphere 5.x 及以上版本，可能需要實現(xiàn)其他方法，如 `getType`。請根據(jù)實際版本調(diào)整。
}

代碼使用：
操作邏輯表 t_order，ShardingSphere-JDBC 根據(jù) create_time 自動路由到對應(yīng)月份的表。

@Autowired
private JdbcTemplate jdbcTemplate;
public void demo() {
    // 插入一條創(chuàng)建時間為當前的訂單，會路由到對應(yīng)月份的表，如 t_order_202410
    String sql = "INSERT INTO t_order (order_id, user_id, amount, create_time) VALUES (?, ?, ?, ?)";
    jdbcTemplate.update(sql, 789L, 1002L, 400.00, LocalDateTime.now());
    // 查詢特定時間范圍的訂單，自定義算法中的doSharding方法會被調(diào)用
    List<Map<String, Object>> orders = jdbcTemplate.queryForList("SELECT * FROM t_order WHERE create_time BETWEEN ? AND ?", 
        LocalDateTime.of(2024, 10, 1, 0, 0), 
        LocalDateTime.of(2024, 10, 31, 23, 59));
}

?? 重要注意事項

確保物理表和庫存在：ShardingSphere-JDBC 不會自動創(chuàng)建配置中涉及的物理表和數(shù)據(jù)庫，你需要在數(shù)據(jù)庫中手動創(chuàng)建好。
選擇合適的分片鍵：分片鍵的選擇至關(guān)重要，應(yīng)盡量選擇數(shù)據(jù)分布均勻、業(yè)務(wù)查詢常用的字段。一旦確定，修改分片規(guī)則會非常困難。
避免跨庫/表關(guān)聯(lián)查詢：復(fù)雜的關(guān)聯(lián)查詢（尤其是跨庫的JOIN）在分片環(huán)境中性能很差，甚至不被支持。設(shè)計時應(yīng)盡量減少此類操作，或考慮使用綁定表。
分布式主鍵：在分片環(huán)境中，數(shù)據(jù)庫自增主鍵不再適用，建議使用 ShardingSphere 提供的分布式序列算法（如雪花算法 Snowflake）。
SQL 限制：ShardingSphere-JDBC 對某些復(fù)雜 SQL（如子查詢、函數(shù)的使用）支持有限，使用時需參考官方文檔的支持列表。