ShardingJdbc配置说明
ShardingJdbc是ShardingSphere生态中的轻量级分库分表解决方案，它通过JDBC层拦截SQL语句，实现对底层数据库的分片访问。下面详细介绍ShardingJdbc的主要配置项和配置方法。
1. 基础依赖配置
首先，需要在Maven项目中添加ShardingJdbc的依赖：
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.1</version>
</dependency>
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.33</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
2. 数据源配置
在application.yml或application.properties中配置数据源：
spring:
shardingsphere:
datasource:
names: db0,db1 # 数据源名称列表
# 第一个数据源配置
db0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
# 第二个数据源配置
db1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
3. 分片规则配置
3.1 表分片配置
spring:
shardingsphere:
rules:
sharding:
tables:
# 表名
orders:
# 实际数据节点，格式：数据源名称.表名
actual-data-nodes: db${0..1}.orders_${0..3}
# 分库策略
database-strategy:
standard:
sharding-column: user_id # 分片列
sharding-algorithm-name: database-inline # 分片算法名称
# 分表策略
table-strategy:
standard:
sharding-column: order_id # 分片列
sharding-algorithm-name: table-inline # 分片算法名称
# 主键生成策略
key-generate-strategy:
column: order_id # 主键列
key-generator-name: snowflake # 主键生成算法
3.2 绑定表配置
当两个表之间存在外键关联关系时，可以配置为绑定表，以优化查询性能：
spring:
shardingsphere:
rules:
sharding:
binding-tables:
- orders,order_items # 绑定表组，多个表用逗号分隔
3.3 广播表配置
广播表是指在所有分片数据源中都存在的表，表结构和数据完全相同：
spring:
shardingsphere:
rules:
sharding:
broadcast-tables:
- t_config # 广播表名
- t_dict # 多个广播表
4. 分片算法配置
ShardingJdbc支持多种分片算法，以下是常见的几种：
4.1 内联分片算法
spring:
shardingsphere:
rules:
sharding:
sharding-algorithms:
# 数据库分片算法
database-inline:
type: INLINE
props:
algorithm-expression: db${user_id % 2}
# 表分片算法
table-inline:
type: INLINE
props:
algorithm-expression: orders_${order_id % 4}
4.2 范围分片算法
spring:
shardingsphere:
rules:
sharding:
sharding-algorithms:
range-sharding:
type: INTERVAL
props:
datetime-pattern: yyyy-MM-dd
datetime-lower: 2020-01-01
datetime-upper: 2025-12-31
sharding-suffix-pattern: \'yyyyMM\'
datetime-interval-unit: MONTHS
datetime-interval-amount: 1
4.3 哈希分片算法
spring:
shardingsphere:
rules:
sharding:
sharding-algorithms:
hash-sharding:
type: HASH_MOD
props:
sharding-count: 4
4.4 时间分片算法
spring:
shardingsphere:
rules:
sharding:
sharding-algorithms:
time-sharding:
type: CLASS_BASED
props:
strategy: STANDARD
algorithmClassName: com.example.TimeShardingAlgorithm # 自定义分片算法类
5. 主键生成策略配置
spring:
shardingsphere:
rules:
sharding:
key-generators:
# 雪花算法
snowflake:
type: SNOWFLAKE
props:
worker-id: 1
# UUID
uuid:
type: UUID
props:
max-length: 36
6. 读写分离配置
spring:
shardingsphere:
rules:
readwrite-splitting:
data-sources:
ds_group_0:
write-data-source-name: db0
read-data-source-names: db0_slave0, db0_slave1
load-balancer-name: random # 负载均衡算法
load-balancers:
random:
type: RANDOM
7. 公共配置项
spring:
shardingsphere:
# SQL显示配置
props:
sql:
show: true # 是否显示SQL
# 执行模式配置
executor:
size: 20 # 执行线程池大小
# 检查项配置
check:
table:
metadata: true # 是否检查表元数据
8. 分布式事务配置
ShardingJdbc支持XA事务和SAGA事务：
spring:
shardingsphere:
rules:
transaction:
default-type: XA # 事务类型，可选XA、BASE
providers:
xa:
type: Atomikos # XA事务管理器
saga:
type: Seata # SAGA事务管理器
9. 自定义分片算法实现
如果内置的分片算法不能满足需求，可以实现自定义分片算法：
public class CustomShardingAlgorithm implements StandardShardingAlgorithm {
@Override
public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
// 实现精确分片逻辑
Long value = shardingValue.getValue();
String tableName = \"orders_\" + (value % 4);
for (String targetName : availableTargetNames) {
if (targetName.endsWith(tableName)) {
return targetName;
}
}
throw new UnsupportedOperationException();
}
@Override
public Collection doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) {
// 实现范围分片逻辑
Collection result = new LinkedHashSet();
Range range = shardingValue.getValueRange();
for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
String tableName = \"orders_\" + (i % 4);
for (String targetName : availableTargetNames) {
if (targetName.endsWith(tableName)) {
result.add(targetName);
}
}
}
return result;
}
@Override
public void init() {
// 初始化方法
}
@Override
public String getType() {
return \"CUSTOM\"; // 算法类型
}
}
10. 配置示例（完整）
spring:
shardingsphere:
datasource:
names: db0,db1,db0_slave0,db0_slave1
# 主库配置
db0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
db1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
# 从库配置
db0_slave0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3307/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
db0_slave1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3308/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
rules:
# 分片规则
sharding:
tables:
orders:
actual-data-nodes: ds_group_${0}.orders_${0..3}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
key-generate-strategy:
column: order_id
key-generator-name: snowflake
order_items:
actual-data-nodes: ds_group_${0}.order_items_${0..3}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
# 绑定表
binding-tables:
- orders,order_items
# 广播表
broadcast-tables:
- t_config
# 分片算法
sharding-algorithms:
database-inline:
type: INLINE
props:
algorithm-expression: db${user_id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: orders_${order_id % 4}
# 主键生成
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 1
# 读写分离
readwrite-splitting:
data-sources:
ds_group_0:
write-data-source-name: db0
read-data-source-names: db0_slave0, db0_slave1
load-balancer-name: random
ds_group_1:
write-data-source-name: db1
read-data-source-names: db1_slave0, db1_slave1
load-balancer-name: random
load-balancers:
random:
type: RANDOM
# 公共配置
props:
sql:
show: true
executor:
size: 20
注意事项
- 版本兼容性：不同版本的ShardingJdbc配置可能有所差异，请根据实际使用的版本参考官方文档
- 分片键选择：选择合适的分片键对性能影响很大，通常选择查询频率高、分布均匀的字段
- SQL兼容性：某些复杂SQL可能不被ShardingJdbc完全支持，需要注意测试
- 性能优化：对于大数据量场景，需要合理配置执行线程池、连接池等参数
- 监控：建议配置日志和监控，以便及时发现和解决问题
通过合理配置ShardingJdbc，可以有效解决单库单表的性能瓶颈，提高系统的扩展性和稳定性。
