ShardingSphere 分庫分表原理與Spring Boot集成實(shí)踐方案

更新時(shí)間：2026年02月02日 11:34:18 作者：小沈同學(xué)呀

本文探討了ShardingSphere分庫分表原理及其Spring Boot集成方案,詳細(xì)闡述了SQL解析、分片路由、SQL改寫、結(jié)果歸并和事務(wù)管理等關(guān)鍵技術(shù)原理,感興趣的朋友跟隨小編一起看看吧

前言

隨著業(yè)務(wù)規(guī)模的增長，單一數(shù)據(jù)庫往往無法滿足高性能、高并發(fā)的需求。ShardingSphere 作為 Apache 基金會(huì)頂級(jí)項(xiàng)目，提供了完整的分布式數(shù)據(jù)庫解決方案，其中分庫分表功能是最核心的能力之一。本文將深入探討 ShardingSphere 的分庫分表原理，并提供 Spring Boot 集成實(shí)踐方案。

理論基礎(chǔ)

1. 分庫分表概念

垂直分庫：按照業(yè)務(wù)模塊將數(shù)據(jù)分散到不同的數(shù)據(jù)庫實(shí)例
水平分表：將單表數(shù)據(jù)按照某種規(guī)則分散到多個(gè)物理表中

2. ShardingSphere 架構(gòu)組成

Sharding-JDBC：輕量級(jí) Java 框架，以 jar 包形式提供服務(wù)
Sharding-Proxy：數(shù)據(jù)庫代理，提供透明化的數(shù)據(jù)庫訪問
Sharding-Sidecar：云原生數(shù)據(jù)庫代理（開發(fā)中）

3. 核心組件

// 數(shù)據(jù)源配置
DataSource dataSource = new ShardingSphereDataSource();
// 分片規(guī)則配置
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
// 分片策略
StandardShardingStrategyConfiguration strategyConfig = new StandardShardingStrategyConfiguration();

4. 原理分析

SQL 解析
Sharding-JDBC 會(huì)對傳入的 SQL 語句進(jìn)行解析，識(shí)別出其中的分片鍵（Sharding Key）以及相關(guān)的表名、字段等信息。這是實(shí)現(xiàn)分片路由的基礎(chǔ)。

  public SQLStatement parse(String sql, boolean useCache);

分片路由
根據(jù)解析出的分片鍵和配置的分片規(guī)則，Sharding-JDBC 會(huì)計(jì)算出 SQL 應(yīng)該路由到哪些實(shí)際的數(shù)據(jù)源和表。這個(gè)過程涉及到分片算法的應(yīng)用，比如取模、范圍分片等。

  public RouteContext route(SQLStatement sqlStatement, ShardingRule shardingRule);

SQL 改寫
在確定了目標(biāo)數(shù)據(jù)源和表之后，Sharding-JDBC 會(huì)將原始 SQL 改寫為目標(biāo)數(shù)據(jù)庫可以執(zhí)行的 SQL。例如，將邏輯表名替換為實(shí)際的物理表名。

  public SQLRewriteResult rewrite(RouteContext routeContext);

結(jié)果歸并
當(dāng)查詢涉及多個(gè)數(shù)據(jù)源或表時(shí)，Sharding-JDBC 會(huì)將各個(gè)數(shù)據(jù)源返回的結(jié)果進(jìn)行歸并，最終返回給應(yīng)用層一個(gè)統(tǒng)一的結(jié)果集。

  public MergedResult merge(List<QueryResult> queryResults, SQLStatement sqlStatement);

事務(wù)管理
Sharding-JDBC 還支持分布式事務(wù)管理，確保在多個(gè)數(shù)據(jù)源之間的操作具有一致性。

  public void begin();
  public void commit();
  public void rollback();

Spring Boot 集成方案

1. Maven 依賴配置

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
    <exclusions>
        <exclusion>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <!-- springboot 2.x 使用 ShardingSphere 推薦的版本 -->
    <version>1.33</version> 
</dependency>

2. 配置文件設(shè)置

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/cce-demo
        username: root
        password: 12345678
        type: com.zaxxer.hikari.HikariDataSource
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/cce-demo-temp
        username: root
        password: 12345678
        type: com.zaxxer.hikari.HikariDataSource
    rules:
      sharding:
        tables:
          mp_user:
            actual-data-nodes: ds${0..1}.mp_user_${0..3}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: mp-user-inline
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline
        sharding-algorithms:
          mp-user-inline:
            type: INLINE
            props:
              algorithm-expression: mp_user_${user_id % 4}
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds${user_id % 2}

3. 測試用例

/**
 * MpUserTest
 * 所有操作都必須包含分表鍵，不然無法路由
 * @author senfel
 * @version 1.0
 * @date 2026/1/30 11:42
 */
@SpringBootTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class MpUserTest {
    @Resource
    private MpUserMapper mpUserMapper;
    private static final String testOpenId = "test_openid_" + System.currentTimeMillis();
    private static final Long testUserIdNumber = generateNumericUserId();
    /**
     * 生成數(shù)字格式的用戶ID
     * @author senfel
     * @date 2026/1/30 16:59
     * @return java.lang.Long
     */
    private static Long generateNumericUserId() {
        // 使用時(shí)間戳和隨機(jī)數(shù)生成數(shù)字ID
        long timestamp = System.currentTimeMillis();
        long random = (long) (Math.random() * 1000000L);
        return timestamp + random;
    }
    /**
     * test
     * @author senfel
     * @date 2026/1/30 16:59
     * @return void
     */
    @Test
    @Order(1)
    public void test() {
        //插入
        MpUser user = MpUser.builder()
                .openid(testOpenId)
                .deleted(false)
                .userId(testUserIdNumber)
                .build();
        int result = mpUserMapper.insert(user);
        System.err.println("userId: " + user.getUserId());
        assertTrue(result > 0, "插入用戶應(yīng)該成功");
        //查詢
        LambdaQueryWrapper<MpUser> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(MpUser::getUserId, user.getUserId());
        List<MpUser> userList = mpUserMapper.selectList(queryWrapper);
        assertNotNull(userList, "根據(jù)userId查詢結(jié)果不應(yīng)該為null");
        //修改
        LambdaUpdateWrapper<MpUser> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(MpUser::getUserId, user.getUserId())
                .set(MpUser::getUserId, testUserIdNumber);
        result = mpUserMapper.update(null, updateWrapper);
        assertTrue(result > 0, "根據(jù)userId更新用戶應(yīng)該成功");
        //刪除
        result = mpUserMapper.delete(updateWrapper);
        assertTrue(result > 0, "根據(jù)userId刪除用戶應(yīng)該成功");
    }
}

4. 測試效果

userId: 1769764291467
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2d91f007] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@3dd09249] will not be managed by Spring
==>  Preparing: SELECT id,openid,deleted,user_id FROM mp_user WHERE (user_id = ?) 
==> Parameters: 1769764291467(Long)
<==    Columns: id, openid, deleted, user_id
<==        Row: 8388609, test_openid_1769763436874, 0, 1769764291467
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2d91f007]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@12888eb5] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@205339e0] will not be managed by Spring
==>  Preparing: UPDATE mp_user SET user_id=? WHERE (user_id = ?) 
==> Parameters: 1769764291467(Long), 1769764291467(Long)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@12888eb5]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76f3f810] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@7d7efdf5] will not be managed by Spring
==>  Preparing: DELETE FROM mp_user WHERE (user_id = ?) 
==> Parameters: 1769764291467(Long)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76f3f810]

實(shí)際應(yīng)用場景

1. 電商訂單系統(tǒng)

@Entity
@Table(name = "order")
public class Order {
    @Id
    private Long orderId;
    private Long userId;
    private BigDecimal amount;
    private LocalDateTime createTime;
    // getter/setter...
}
// 查詢示例
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByUserId(Long userId);
    @Query("SELECT o FROM Order o WHERE o.userId = :userId AND o.createTime BETWEEN :startTime AND :endTime")
    List<Order> findOrdersByUserIdAndTimeRange(@Param("userId") Long userId, 
                                              @Param("startTime") LocalDateTime startTime, 
                                              @Param("endTime") LocalDateTime endTime);
}

2. 日志分表策略

# 按月份分表配置
spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          system_log:
            actual-data-nodes: ds0.system_log_${202301..202312}
            table-strategy:
              standard:
                sharding-column: create_time
                sharding-algorithm-name: log-month-sharding

性能優(yōu)化建議

1. 連接池配置

spring:
  shardingsphere:
    props:
      sql-show: true
      max-connections-size-per-query: 10
      acceptor-size: 16

2. 查詢優(yōu)化

合理設(shè)計(jì)分片鍵，避免跨分片查詢
使用綁定表減少笛卡爾積
合理設(shè)置分片數(shù)量，避免過多分片影響性能

總結(jié)

ShardingSphere 提供了完善的分庫分表解決方案，通過合理的配置和使用，可以有效解決單體數(shù)據(jù)庫的性能瓶頸問題。在實(shí)際應(yīng)用中需要注意：
1.分片鍵選擇：選擇合適的分片鍵是成功的關(guān)鍵
2.數(shù)據(jù)遷移：制定完善的數(shù)據(jù)遷移方案
3.監(jiān)控告警：建立完善的監(jiān)控體系
4.版本升級(jí)：關(guān)注新版本特性，及時(shí)升級(jí)
通過本文的介紹和實(shí)踐方案，我們可以快速掌握 ShardingSphere 的核心功能，并在 Spring Boot 項(xiàng)目中成功集成分庫分表能力。

到此這篇關(guān)于ShardingSphere 分庫分表原理與Spring Boot集成實(shí)踐方案的文章就介紹到這了,更多相關(guān)springboot shardingsphere 分庫分表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: