ShardingSphere-Proxy案例

本文目录导读：

1. 基础分片案例
2. 读写分离+分片案例
3. 分片+加密+脱敏案例
4. 分布式事务案例
5. 性能优化配置案例
6. 运维监控案例
7. 实际部署案例

我将为您详细介绍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实现分布式数据库解决方案，实际使用时建议根据业务需求进行适当调整和优化。