Apache ShardingSphere JDBC YAML 配置全解指南
📌 核心配置结构概览
dataSources:
<data_source_name>:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://localhost:3306/db0
username: root
password: root
rules:
- !SHARDING
- !READWRITE_SPLITTING
- !ENCRYPT
props:
sql-show: true
sql-simple: false
executor-size: 20
max-connections-size-per-query: 50
🔍 数据源配置详解
1. 基础数据源配置
dataSources:
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeout: 30000
idleTimeout: 600000
maxLifetime: 1800000
maximumPoolSize: 50
ds_1:
dataSourceClassName: org.apache.commons.dbcp2.BasicDataSource
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1
username: root
password: root
maxTotal: 50
maxIdle: 10
minIdle: 5
2. 数据源参数优化建议
参数 | 默认值 | 生产建议 | 说明 |
---|
connectionTimeout | 30s | 5-10s | 连接超时时间 |
idleTimeout | 10m | 5-10m | 空闲连接超时 |
maxLifetime | 30m | 20-30m | 连接最大生命周期 |
maximumPoolSize | 50 | CPU核数*2 | 最大连接数 |
minIdle | 0 | 5-10 | 最小空闲连接数 |
⚙️ 分片规则配置精要
1. 标准分片配置
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..15}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: table_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
table_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 16}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
2. 高级分片算法类型
算法类型 | 适用场景 | 配置示例 |
---|
INLINE | 简单分片表达式 | ds_${order_id % 64} |
INTERVAL | 按时间范围分片 | datetime-interval: 30 |
HASH_MOD | 哈希取模分片 | sharding-count: 16 |
CLASS_BASED | 自定义分片逻辑 | strategy: com.example.CustomShard |
COSID_MOD | 高性能分布式序列分片 | mod: 32 |
🔄 读写分离配置模板
1. 基础读写分离
rules:
- !READWRITE_SPLITTING
dataSources:
pr_ds:
type: Static
props:
write-data-source-name: ds_primary
read-data-source-names: ds_replica0, ds_replica1
loadBalancerName: round_robin
loadBalancers:
round_robin:
type: ROUND_ROBIN
random:
type: RANDOM
weight:
type: WEIGHT
props:
ds_replica0: 2
ds_replica1: 1
2. 动态数据源发现
rules:
- !READWRITE_SPLITTING
dataSources:
dynamic_ds:
type: Dynamic
props:
auto-aware-data-source-name: governance_ds
write-data-source-query-enabled: true
🔐 数据加密配置
1. 字段级加密配置
rules:
- !ENCRYPT
tables:
t_user:
columns:
phone:
cipherColumn: phone_cipher
assistedQueryColumn: phone_assisted
encryptorName: aes_encryptor
id_card:
cipherColumn: id_card_cipher
encryptorName: sm4_encryptor
encryptors:
aes_encryptor:
type: AES
props:
aes-key-value: 123456abc
sm4_encryptor:
type: SM4
props:
sm4-key-value: 123456abcdef1234
2. 查询列类型选择
算法类型 | 特点 | 适用场景 |
---|
CHAR_DIGEST_LIKE | MD5/SHA1哈希 | 模糊查询 |
MD5 | 标准MD5哈希 | 等值查询 |
SM3 | 国密SM3哈希 | 国内合规场景 |
CUSTOM | 自定义算法 | 特殊业务需求 |
⚡ 全局属性配置
1. 核心性能参数
props:
sql-show: true
sql-simple: false
executor-size: 20
max-connections-size-per-query: 50
check-table-metadata-enabled: false
query-with-cipher-column: true
proxy-frontend-flush-threshold: 128
2. 生产环境推荐配置
props:
sql-show: false
executor-size: $ {CPU核数} * 2
max-connections-size-per-query: 100
kernel-executor-size: 100
proxy-backend-query-fetch-size: -1
proxy-frontend-executor-size: 128
proxy-opentracing-enabled: true
🧩 多规则组合配置
分片+读写分离+加密
rules:
- !SHARDING
- !READWRITE_SPLITTING
- !ENCRYPT
- !MASK
- !SHADOW
🚨 配置最佳实践
1. 环境变量注入
dataSources:
ds_0:
url: ${DATASOURCE_URL:jdbc:mysql://localhost:3306/default}
username: ${DATASOURCE_USER:root}
password: ${DATASOURCE_PASSWORD:root}
2. 配置分模块管理
config/
├── datasources.yaml
├── sharding.yaml
├── rw-splitting.yaml
└── encrypt.yaml
启动时合并配置:
bin/start.sh --config config/datasources.yaml \
--config config/sharding.yaml \
--config config/rw-splitting.yaml
3. 配置版本控制
version: v2.3
changelog:
- version: v2.2
date: 2023-06-01
comment: 增加新分片表
🔧 调试与验证技巧
1. 配置校验命令
bin/validate-config.sh config.yaml
EXPLAIN SELECT * FROM t_order WHERE user_id=123;
2. 运行时配置查询
SHOW SHARDING TABLE RULES;
SHOW READWRITE_SPLITTING RULES;
SHOW ENCRYPT RULES;
3. 动态配置变更
REGISTER STORAGE UNIT ds_2 (
URL="jdbc:mysql://127.0.0.1:3306/ds_2",
USER="root",
PASSWORD="root"
);
REFRESH METADATA;
💎 总结:YAML配置核心要点
- 模块化配置:数据源、规则、属性分离
- 算法复用:命名算法多处引用
- 环境适配:通过变量区分环境
- 规则组合:灵活叠加多种数据治理能力
- 动态治理:结合DistSQL实现配置热更新
最佳实践路径:
开发环境 → 使用完整SQL日志调试配置
测试环境 → 验证分片路由和算法正确性
生产环境 → 关闭敏感日志,优化连接参数