核心内容摘要
cvc5性能调优秘籍：从命令行参数到理论插件的全方位优化
文章目录前言理论基础
分库分表概念
ShardingSphere 架构组成
核心组件
原理分析Spring Boot 集成方案
Maven 依赖配置
配置文件设置
测试用例
测试效果实际应用场景
电商订单系统
日志分表策略性能优化建议
连接池配置
查询优化
总结前言随着业务规模的增长单一数据库往往无法满足高性能、高并发的需求。
ShardingSphere 作为 Apache 基金会顶级项目提供了完整的分布式数据库解决方案其中分库分表功能是最核心的能力之一。
本文将深入探讨 ShardingSphere 的分库分表原理并提供 Spring Boot 集成实践方案。
理论基础
分库分表概念垂直分库按照业务模块将数据分散到不同的数据库实例水平分表将单表数据按照某种规则分散到多个物理表中
ShardingSphere 架构组成Sharding-JDBC轻量级 Java 框架以 jar 包形式提供服务Sharding-Proxy数据库代理提供透明化的数据库访问Sharding-Sidecar云原生数据库代理开发中
核心组件// 数据源配置 DataSource dataSource new ShardingSphereDataSource(); // 分片规则配置 ShardingRuleConfiguration shardingRuleConfig new ShardingRuleConfiguration(); // 分片策略 StandardShardingStrategyConfiguration strategyConfig new StandardShardingStrategyConfiguration();
原理分析SQL 解析Sharding-JDBC 会对传入的 SQL 语句进行解析识别出其中的分片键Sharding Key以及相关的表名、字段等信息。
这是实现分片路由的基础。
public SQLStatement parse(String sql, boolean useCache);分片路由根据解析出的分片键和配置的分片规则Sharding-JDBC 会计算出 SQL 应该路由到哪些实际的数据源和表。
这个过程涉及到分片算法的应用比如取模、范围分片等。
public RouteContext route(SQLStatement sqlStatement, ShardingRule shardingRule);SQL 改写在确定了目标数据源和表之后Sharding-JDBC 会将原始 SQL 改写为目标数据库可以执行的 SQL。
例如将逻辑表名替换为实际的物理表名。
public SQLRewriteResult rewrite(RouteContext routeContext);结果归并当查询涉及多个数据源或表时Sharding-JDBC 会将各个数据源返回的结果进行归并最终返回给应用层一个统一的结果集。
public MergedResult merge(ListQueryResult queryResults, SQLStatement sqlStatement);事务管理Sharding-JDBC 还支持分布式事务管理确保在多个数据源之间的操作具有一致性。
public void begin(); public void commit(); public void rollback();Spring Boot 集成方案
Maven 依赖配置dependency groupIdorg.apache.shardingsphere/groupId artifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactId version
5.
1/version exclusions exclusion groupIdorg.yaml/groupId artifactIdsnakeyaml/artifactId /exclusion /exclusions /dependency dependency groupIdorg.yaml/groupId artifactIdsnakeyaml/artifactId !-- springboot
x 使用 ShardingSphere 推荐的版本 -- version
33/version /dependency
配置文件设置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${
.1}.mp_user_${
.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}
测试用例/** * MpUserTest * 所有操作都必须包含分表键不然无法路由 * author senfel * version
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(
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, 插入用户应该成功); //查询 LambdaQueryWrapperMpUser queryWrapper new LambdaQueryWrapper(); queryWrapper.eq(MpUser::getUserId, user.getUserId()); ListMpUser userList mpUserMapper.selectList(queryWrapper); assertNotNull(userList, 根据userId查询结果不应该为null); //修改 LambdaUpdateWrapperMpUser 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删除用户应该成功); } }
测试效果userId: 1769764291467 Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession2d91f007] was not registered for synchronization because synchronization is not active JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection3dd09249] 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.DefaultSqlSession2d91f007] Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession12888eb5] was not registered for synchronization because synchronization is not active JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection205339e0] 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.DefaultSqlSession12888eb5] Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession76f3f810] was not registered for synchronization because synchronization is not active JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection7d7efdf5] 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.DefaultSqlSession76f3f810]实际应用场景
电商订单系统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 JpaRepositoryOrder, Long { ListOrder findByUserId(Long userId); Query(SELECT o FROM Order o WHERE o.userId :userId AND o.createTime BETWEEN :startTime AND :endTime) ListOrder findOrdersByUserIdAndTimeRange(Param(userId) Long userId, Param(startTime) LocalDateTime startTime, Param(endTime) LocalDateTime endTime); }
日志分表策略# 按月份分表配置 spring: shardingsphere: rules: sharding: tables: system_log: actual-data-nodes: ds
system_log_${
.202312} table-strategy: standard: sharding-column: create_time sharding-algorithm-name: log-month-sharding性能优化建议
连接池配置spring: shardingsphere: props: sql-show: true max-connections-size-per-query: 10 acceptor-size:
查询优化合理设计分片键避免跨分片查询使用绑定表减少笛卡尔积合理设置分片数量避免过多分片影响性能
总结ShardingSphere 提供了完善的分库分表解决方案通过合理的配置和使用可以有效解决单体数据库的性能瓶颈问题。
在实际应用中需要注意