ShardingSphere 分库分表技术详解与 Spring Boot 实践

前言

随着业务规模的增长，单一数据库往往无法满足高性能、高并发的需求。ShardingSphere 作为 Apache 基金会顶级项目，提供了完整的分布式数据库解决方案，其中分库分表功能是最核心的能力之一。本文将深入探讨 ShardingSphere 的分库分表原理，并提供 Spring Boot 集成实践方案。

理论基础

1. 分库分表概念

垂直分库：按照业务模块将数据分散到不同的数据库实例
水平分表：将单表数据按照某种规则分散到多个物理表中

2. ShardingSphere 架构组成

• Sharding-JDBC：轻量级 Java 框架，以 jar 包形式提供服务
• Sharding-Proxy：数据库代理，提供透明化的数据库访问
• Sharding-Sidecar：云原生数据库代理（开发中）

3. 核心组件

// 数据源配置 DataSource dataSource = new ShardingSphereDataSource(); // 分片规则配置 ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); // 分片策略 StandardShardingStrategyConfiguration strategyConfig = new StandardShardingStrategyConfiguration();

4. 原理分析

1. SQL 解析
   Sharding-JDBC 会对传入的 SQL 语句进行解析，识别出其中的分片键（Sharding Key）以及相关的表名、字段等信息。这是实现分片路由的基础。

public SQLStatement parse(String sql, boolean useCache);

2. 分片路由
   根据解析出的分片键和配置的分片规则，Sharding-JDBC 会计算出 SQL 应该路由到哪些实际的数据源和表。这个过程涉及到分片算法的应用，比如取模、范围分片等。

public RouteContext route(SQLStatement sqlStatement, ShardingRule shardingRule);

3. SQL 改写
   在确定了目标数据源和表之后，Sharding-JDBC 会将原始 SQL 改写为目标数据库可以执行的 SQL。例如，将逻辑表名替换为实际的物理表名。

public SQLRewriteResult rewrite(RouteContext routeContext);

4. 结果归并
   当查询涉及多个数据源或表时，Sharding-JDBC 会将各个数据源返回的结果进行归并，最终返回给应用层一个统一的结果集。

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

5. 事务管理
   Sharding-JDBC 还支持分布式事务管理，确保在多个数据源之间的操作具有一致性。

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. 配置文件设置

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(); /** * 生成数字格式的用户ID * @author senfel * @date 2026/1/30 16:59 * @return java.lang.Long */ private static Long generateNumericUserId() { // 使用时间戳和随机数生成数字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, "插入用户应该成功"); //查询 LambdaQueryWrapper<MpUser> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(MpUser::getUserId, user.getUserId()); List<MpUser> userList = mpUserMapper.selectList(queryWrapper); assertNotNull(userList, "根据userId查询结果不应该为null"); //修改 LambdaUpdateWrapper<MpUser> updateWrapper = new LambdaUpdateWrapper<>(); updateWrapper.eq(MpUser::getUserId, user.getUserId()) .set(MpUser::getUserId, testUserIdNumber); result = mpUserMapper.update(null, updateWrapper); assertTrue(result > 0, "根据userId更新用户应该成功"); //删除 result = mpUserMapper.delete(updateWrapper); assertTrue(result > 0, "根据userId删除用户应该成功"); } }

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]

实际应用场景

1. 电商订单系统

@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

性能优化建议

1. 连接池配置

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

2. 查询优化

• 合理设计分片键，避免跨分片查询
• 使用绑定表减少笛卡尔积
• 合理设置分片数量，避免过多分片影响性能

总结

ShardingSphere 提供了完善的分库分表解决方案，通过合理的配置和使用，可以有效解决单体数据库的性能瓶颈问题。在实际应用中需要注意：
1.分片键选择：选择合适的分片键是成功的关键
2.数据迁移：制定完善的数据迁移方案
3.监控告警：建立完善的监控体系
4.版本升级：关注新版本特性，及时升级
通过本文的介绍和实践方案，我们可以快速掌握 ShardingSphere 的核心功能，并在 Spring Boot 项目中成功集成分库分表能力。