本文目录导读：
我将为您详细介绍ShardingSphere-Proxy的实战案例，从基础配置到复杂场景，帮助您快速上手。
基础分片案例
分库分表配置示例
# config-sharding.yaml
schemaName: sharding_db
dataSources:
ds0:
url: jdbc:mysql://localhost:3306/ds0?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds1:
url: jdbc:mysql://localhost:3306/ds1?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds${0..1}.t_order${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds${0..1}.t_order_item${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_item_inline
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds${user_id % 2}
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order${order_id % 2}
t_order_item_inline:
type: INLINE
props:
algorithm-expression: t_order_item${order_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 1
连接使用测试
-- 创建表
CREATE TABLE t_order (
order_id BIGINT PRIMARY KEY,
user_id INT NOT NULL,
order_amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20)
);
CREATE TABLE t_order_item (
item_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_name VARCHAR(100),
quantity INT,
price DECIMAL(10,2)
);
-- 插入数据
INSERT INTO t_order (user_id, order_amount, order_date, status)
VALUES (1001, 199.00, '2024-01-15', 'PAID');
-- 查询数据
SELECT * FROM t_order WHERE user_id = 1001;
读写分离+分片案例
配置示例
# config-readwrite-splitting.yaml
schemaName: sharding_readwrite_db
dataSources:
write_ds:
url: jdbc:mysql://192.168.1.100:3306/sharding_db
username: root
password: root
read_ds_0:
url: jdbc:mysql://192.168.1.101:3306/sharding_db
username: root
password: root
read_ds_1:
url: jdbc:mysql://192.168.1.102:3306/sharding_db
username: root
password: root
rules:
- !READWRITE_SPLITTING
dataSources:
readwrite_ds:
writeDataSourceName: write_ds
readDataSourceNames:
- read_ds_0
- read_ds_1
loadBalancerName: round_robin
loadBalancers:
round_robin:
type: ROUND_ROBIN
props:
default: true
- !SHARDING
tables:
t_user:
actualDataNodes: readwrite_ds.t_user${0..1}
tableStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: user_inline
shardingAlgorithms:
user_inline:
type: INLINE
props:
algorithm-expression: t_user${user_id % 2}
实际应用场景
-- 写入操作走主库 INSERT INTO t_user (user_id, username, email) VALUES (1, '张三', 'zhangsan@example.com'); -- 读取操作负载均衡到从库 SELECT * FROM t_user WHERE user_id = 1; -- 事务内操作保持一致 START TRANSACTION; UPDATE t_user SET email = 'new@example.com' WHERE user_id = 1; COMMIT;
分片+加密+脱敏案例
配置示例
# config-encrypt.yaml
schemaName: encrypt_db
dataSources:
encrypt_ds:
url: jdbc:mysql://localhost:3306/encrypt_db
username: root
password: root
rules:
- !ENCRYPT
encryptors:
aes_encryptor:
type: AES
props:
aes-key-value: 1234567890abcdef
md5_encryptor:
type: MD5
tables:
t_customer:
columns:
id_card:
cipherColumn: id_card_cipher
encryptorName: aes_encryptor
phone:
cipherColumn: phone_cipher
encryptorName: aes_encryptor
password:
cipherColumn: password_cipher
encryptorName: md5_encryptor
name:
plainColumn: name
cipherColumn: name_cipher
encryptorName: aes_encryptor
- !SHARDING
tables:
t_customer:
actualDataNodes: encrypt_ds.t_customer${0..1}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: customer_inline
shardingAlgorithms:
customer_inline:
type: INLINE
props:
algorithm-expression: t_customer${id % 2}
数据操作示例
-- 插入加密数据
INSERT INTO t_customer (name, id_card, phone, password)
VALUES ('测试用户', '110101199001011234', '13800138000', 'password123');
-- 查询时自动解密
SELECT * FROM t_customer WHERE id = 1;
-- 条件查询加密字段
SELECT * FROM t_customer WHERE id_card = '110101199001011234';
分布式事务案例
XA事务配置
# config-xa.yaml
schemaName: xa_db
dataSources:
xa_ds_0:
url: jdbc:mysql://localhost:3306/xa_ds0
username: root
password: root
xa_ds_1:
url: jdbc:mysql://localhost:3306/xa_ds1
username: root
password: root
rules:
- !SHARDING
tables:
t_account:
actualDataNodes: xa_ds${0..1}.t_account${0..1}
tableStrategy:
standard:
shardingColumn: account_id
shardingAlgorithmName: account_inline
keyGenerateStrategy:
column: account_id
keyGeneratorName: snowflake
shardingAlgorithms:
account_inline:
type: INLINE
props:
algorithm-expression: t_account${account_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
database_inline:
type: INLINE
props:
algorithm-expression: xa_ds${user_id % 2}
props:
sql-show: true
executor-size: 20
max-connections-size-per-query: 1
测试分布式事务
-- 开启XA事务 SET XA = ON; -- 跨库更新操作 UPDATE t_account SET balance = balance - 100 WHERE account_id = 1001; UPDATE t_account SET balance = balance + 100 WHERE account_id = 2002; -- 提交事务 XA COMMIT; -- 如果出错可以回滚 XA ROLLBACK;
性能优化配置案例
连接池和性能配置
# config-performance.yaml props: sql-show: true # 打印SQL日志 sql-simple: false # 简化SQL表示 executor-size: 20 # 线程池大小 max-connections-size-per-query: 1 # 查询最大连接数 check-table-metadata-enabled: false # 检查表结构一致性 query-with-cipher-column: true # 控制加密字段查询 show-process-list-enabled: false # 显示进程列表 proxy-frontend-flush-threshold: 128 # 服务端刷新阈值 proxy-backend-query-fetch-size: 100 # 查询获取大小 proxy-frontend-executor-size: 20 # 前端执行线程数 proxy-backend-executor-size: 20 # 后端执行线程数 proxy-max-connections-idle-timeout: 60 # 最大连接空闲时间 proxy-connection-timeout-milliseconds: 30000 # 连接超时 proxy-check-table-metadata-enabled: false # 或使用命令动态设置 SET VARIABLE sql_show = true; SET VARIABLE executor_size = 30;
缓存优化配置
# config-cache.yaml
props:
# Redis缓存配置
cache-enabled: true
cache-type: REDIS
cache-redis-host: localhost
cache-redis-port: 6379
cache-redis-password:
cache-redis-database: 0
cache-resources:
- dataSourceName: ds0
cacheSize: 1000
ttl: 300
运维监控案例
开启监控指标
# config-monitor.yaml props: metrics-enabled: true metrics-type: PROMETHEUS # 通过curl访问监控指标 curl http://localhost:3307/metrics # 常用监控指标 sharding_proxy_sql_total sharding_proxy_request_total sharding_proxy_execute_time_millis sharding_proxy_connection_total sharding_proxy_route_result_total
日志配置
# config-log.yaml props: sql-show: true sql-simple: true log-level: DEBUG # 日志输出示例 2024-01-15 10:30:00.123 INFO ShardingSphere-SQL - Actual SQL: ds0 ::: SELECT * FROM t_order WHERE user_id = 1001 2024-01-15 10:30:00.126 INFO ShardingSphere-SQL - Actual SQL: ds1 ::: SELECT * FROM t_order_item WHERE order_id IN (1, 2)
实际部署案例
Docker部署
# docker-compose.yml
version: '3.8'
services:
shardingsphere-proxy:
image: apache/shardingsphere-proxy:5.4.0
container_name: shardingsphere-proxy
ports:
- "3307:3307"
volumes:
- ./conf:/opt/shardingsphere-proxy/conf
- ./logs:/opt/shardingsphere-proxy/logs
environment:
- JVM_OPTS=-Xmx2g -Xms2g -XX:+UseG1GC
networks:
- sharding-net
mysql-master:
image: mysql:8.0
environment:
- MYSQL_ROOT_PASSWORD=root
ports:
- "3306:3306"
networks:
- sharding-net
networks:
sharding-net:
driver: bridge
客户端连接测试
// Java客户端连接示例
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://localhost:3307/sharding_db?serverTimezone=UTC";
String username = "root";
String password = "root";
try (Connection conn = DriverManager.getConnection(url, username, password)) {
// 执行分片查询
String sql = "SELECT order_id, user_id, amount FROM t_order WHERE user_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, 1001);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println("OrderId: " + rs.getLong("order_id"));
}
}
}
}
// 使用MyBatis连接
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setURL(url);
dataSource.setUser(username);
dataSource.setPassword(password);
SqlSessionFactory factory = new SqlSessionFactoryBuilder()
.build(getConfiguration(dataSource));
// 创建DAO层使用...
}
通过这些案例,您可以快速搭建和使用ShardingSphere-Proxy实现分布式数据库解决方案，实际使用时建议根据业务需求进行适当调整和优化。
