Apache ShardingSphere 快速集成使用配置教程

-- 创建数据库
CREATE DATABASE shardingdb0;
CREATE DATABASE shardingdb1;

-- 在 shardingdb0 中创建分表
USE shardingdb0;
CREATE TABLE t_order_0 (
    order_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    PRIMARY KEY (order_id)
);
CREATE TABLE t_order_1 (
    order_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    PRIMARY KEY (order_id)
);

-- 在 shardingdb1 中创建相同的分表
USE shardingdb1;
CREATE TABLE t_order_0 (
    order_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    PRIMARY KEY (order_id)
);
CREATE TABLE t_order_1 (
    order_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    PRIMARY KEY (order_id)
);

<!-- MySQL 驱动 -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- ShardingSphere JDBC 核心依赖 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc</artifactId>
    <version>5.5.3</version>
</dependency>

<!-- 连接池 HikariCP -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

<!-- MyBatis Plus（可选，本教程示例使用） -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
    <version>3.5.12</version>
</dependency>

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    # 指向类路径下的分片配置文件
    url: jdbc:shardingsphere:classpath:sharding.yaml

@Configuration
public class DataSourceConfig {

    @Bean(name = "shardingDataSource")
    public DataSource shardingDataSource() throws SQLException, IOException {
        try (InputStream inputStream = getClass().getClassLoader()
                .getResourceAsStream("sharding.yaml")) {
            if (inputStream == null) {
                throw new IllegalStateException("Cannot find sharding.yaml in classpath");
            }
            byte[] yamlBytes = inputStream.readAllBytes();
            return YamlShardingSphereDataSourceFactory.createDataSource(yamlBytes);
        }
    }
}

# ==================== 数据源配置 ====================
dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/shardingdb0?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: your_password
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/shardingdb1?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: your_password

# ==================== 分片规则 ====================
rules:
  - !SHARDING
    tables:
      t_order:                              # 逻辑表名
        actualDataNodes: ds_${0..1}.t_order_${0..1}  # 实际数据节点：2库 x 2表
        databaseStrategy:                   # 分库策略
          standard:                         # 单分片键标准分片
            shardingColumn: user_id         # 分片列
            shardingAlgorithmName: db_inline  # 分片算法名称
        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}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: db_inline
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: t_order_inline
        keyGenerateStrategy:
          column: item_id
          keyGeneratorName: snowflake

    bindingTables:                          # 绑定表声明
      - t_order,t_order_item

    # 分片算法定义
    shardingAlgorithms:
      db_inline:                            # 分库算法：user_id 取模
        type: INLINE
        props:
          algorithm-expression: ds_${user_id % 2}
          allow-range-query-with-inline-sharding: true  # 允许范围查询
      t_order_inline:                       # 分表算法：order_id 取模
        type: INLINE
        props:
          algorithm-expression: t_order_${order_id % 2}

    # 主键生成器定义
    keyGenerators:
      snowflake:                            # 雪花算法（Long 类型）
        type: SNOWFLAKE

# ==================== 属性配置 ====================
props:
  sql-show: true                            # 打印改写后的真实 SQL，便于调试
  check-table-metadata-enabled: false       # 启动时不检查分片元数据一致性

@Data
@TableName("t_order")
public class Order {
    @TableId(type = IdType.ASSIGN_ID)  // 使用 ShardingSphere 生成主键
    private Long orderId;
    private Long userId;
    private BigDecimal amount;
    private String status;
}

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}

@SpringBootTest
public class ShardingTest {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    void testInsert() {
        for (long i = 1; i <= 10; i++) {
            Order order = new Order();
            order.setUserId(i);           // 分库键
            order.setAmount(new BigDecimal("100.00"));
            order.setStatus("INIT");
            orderMapper.insert(order);    // order_id 由雪花算法自动生成
        }
    }

    @Test
    void testQuery() {
        // 精确查询：根据分片键路由到单库单表
        LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(Order::getUserId, 1L);
        List<Order> orders = orderMapper.selectList(wrapper);
        orders.forEach(System.out::println);
    }

    @Test
    void testQueryAll() {
        // 无分片键查询：全路由，归并结果
        List<Order> orders = orderMapper.selectList(null);
        orders.forEach(System.out::println);
    }
}

[ShardingSphere-SQL] Actual SQL: ds_0 ::: INSERT INTO t_order_0 (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)
[ShardingSphere-SQL] Actual SQL: ds_1 ::: INSERT INTO t_order_1 (order_id, user_id, amount, status) VALUES (?, ?, ?, ?)

https://shardingsphere.apache.org/document/5.5.3/cn/downloads/

shardingsphere-proxy-bin/
├── LICENSE
├── NOTICE
├── README.txt
├── bin/          # 启动停止脚本
├── conf/         # 配置文件目录
├── lib/          # JAR 包
└── licenses/

mkdir ext-lib
# 下载 MySQL 驱动到 ext-lib 目录
wget -O ext-lib/mysql-connector-j-8.0.33.jar \
  https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar

mode:
  type: Standalone                    # 单机模式（生产环境用 Cluster）
  repository:
    type: JDBC                        # 元数据持久化方式
    props:
      provider: H2
      jdbc_url: jdbc:h2:mem:config;DB_CLOSE_DELAY=-1;MODE=MySQL

rules:
  - !AUTHORITY                        # 认证配置
    users:
      - root@%:root                   # 用户名:密码，% 表示任意主机
      - sharding@:sharding
    provider:
      type: ALL_PERMITTED             # 权限策略：全部允许（生产环境应细化）

  - !TRANSACTION                      # 事务配置
    defaultType: XA                   # 默认事务类型：XA / BASE
    providerType: Atomikos            # XA 事务管理器

  - !SQL_PARSER                       # SQL 解析器缓存配置
    sqlStatementCache:
      initialCapacity: 2000
      maximumSize: 65535
    parseTreeCache:
      initialCapacity: 128
      maximumSize: 1024

props:
  max-connections-size-per-query: 1   # 单次查询最大连接数
  kernel-executor-size: 16            # 内核执行线程数
  proxy-frontend-flush-threshold: 128 # 前端刷新阈值
  sql-show: false                     # 是否打印真实 SQL
  proxy-frontend-max-connections: 0   # 最大前端连接数（0=不限制）
  proxy-backend-executor-suitable: OLAP  # 后端执行器类型：OLAP / OLTP

schemaName: sharding_db               # 逻辑库名称（客户端看到的数据库名）

dataSources:                          # 物理数据源
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/shardingdb0?serverTimezone=UTC&useSSL=false
    username: root
    password: your_password
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/shardingdb1?serverTimezone=UTC&useSSL=false
    username: root
    password: your_password
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_${0..1}.t_order_${0..1}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: database_inline
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: t_order_inline
        keyGenerateStrategy:
          column: order_id
          keyGeneratorName: snowflake

    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}

    keyGenerators:
      snowflake:
        type: SNOWFLAKE

# Linux / macOS 启动
sh bin/start.sh

# 指定端口启动（默认 3307）
sh bin/start.sh 3308

# Windows 启动
bin/start.bat

# 查看启动日志
tail -100f logs/stdout.log

[INFO ] 2026-xx-xx xx:xx:xx.xxx [main] o.a.s.p.frontend.ShardingSphereProxy
  - ShardingSphere-Proxy Standalone mode started successfully

mysql -h 127.0.0.1 -u root -proot -P 3307

mysql> SHOW DATABASES;
+-------------+
| schema_name |
+-------------+
| sharding_db |
+-------------+

mysql> USE sharding_db;

-- 插入数据，ShardingSphere 自动路由到对应分表
mysql> INSERT INTO t_order (user_id, amount, status) VALUES
       (1, 100.00, 'INIT'),
       (2, 200.00, 'INIT'),
       (3, 300.00, 'INIT');

-- 查询时透明聚合所有分表
mysql> SELECT * FROM t_order WHERE user_id = 1;
+--------------------+---------+--------+--------+
| order_id           | user_id | amount | status |
+--------------------+---------+--------+--------+
| 786812345678901234 |       1 | 100.00 | INIT   |
+--------------------+---------+--------+--------+

docker run -d \
  --name shardingsphere-proxy \
  -p 3307:3307 \
  -v /path/to/conf:/opt/shardingsphere-proxy/conf \
  -v /path/to/ext-lib:/opt/shardingsphere-proxy/ext-lib \
  apache/shardingsphere-proxy:5.5.3

rules:
  - !READWRITE_SPLITTING
    dataSourceGroups:
      readwrite_ds:                      # 读写分离逻辑数据源名称
        writeDataSourceName: write_ds    # 写库（主库）数据源名
        readDataSourceNames:             # 读库（从库）数据源名列表
          - read_ds_0
          - read_ds_1
        transactionalReadQueryStrategy: PRIMARY  # 事务内读请求路由策略
        loadBalancerName: random         # 负载均衡算法名称

    loadBalancers:
      random:                            # 随机负载均衡
        type: RANDOM
      round_robin:                       # 轮询负载均衡
        type: ROUND_ROBIN
      weight:                            # 权重负载均衡
        type: WEIGHT
        props:
          read_ds_0: 2                   # 权重值
          read_ds_1: 1

dataSources:
  # 主库
  write_ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/master_0
    # ... 其他连接参数
  write_ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/master_1
  # 从库
  read_ds_0_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://127.0.0.1:3307/slave_0_0
  read_ds_0_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://127.0.0.1:3308/slave_0_1
  read_ds_1_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://127.0.0.1:3307/slave_1_0
  read_ds_1_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://127.0.0.1:3308/slave_1_1

rules:
  - !READWRITE_SPLITTING
    dataSourceGroups:
      ds_0:                              # 逻辑数据源 ds_0 = 1主2从
        writeDataSourceName: write_ds_0
        readDataSourceNames: [read_ds_0_0, read_ds_0_1]
        loadBalancerName: round_robin
      ds_1:                              # 逻辑数据源 ds_1 = 1主2从
        writeDataSourceName: write_ds_1
        readDataSourceNames: [read_ds_1_0, read_ds_1_1]
        loadBalancerName: round_robin
    loadBalancers:
      round_robin:
        type: ROUND_ROBIN

  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_${0..1}.t_order_${0..1}  # ds_0/ds_1 是读写分离逻辑数据源
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: db_inline
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: t_order_inline
    shardingAlgorithms:
      db_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${user_id % 2}
      t_order_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_${order_id % 2}

rules:
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos              # XA 事务管理器：Atomikos / Narayana

<!-- XA 事务管理器 Atomikos -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-transaction-xa-atomikos</artifactId>
    <version>5.5.3</version>
</dependency>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-transaction-base-seata-at</artifactId>
    <version>5.5.3</version>
</dependency>

rules:
  - !TRANSACTION
    defaultType: BASE
    providerType: Seata

@Service
public class OrderService {

    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private OrderItemMapper orderItemMapper;

    // 使用默认事务类型（配置文件中指定的）
    @Transactional
    public void createOrder(Order order, OrderItem item) {
        orderMapper.insert(order);       // 可能路由到 ds_0
        orderItemMapper.insert(item);    // 可能路由到 ds_1
        // 跨库操作，由分布式事务保证一致性
    }

    // 显式指定 XA 事务
    @ShardingSphereTransactionType(TransactionType.XA)
    @Transactional
    public void createOrderWithXA(Order order, OrderItem item) {
        orderMapper.insert(order);
        orderItemMapper.insert(item);
    }

    // 显式指定 BASE 事务
    @ShardingSphereTransactionType(TransactionType.BASE)
    @Transactional
    public void createOrderWithBASE(Order order, OrderItem item) {
        orderMapper.insert(order);
        orderItemMapper.insert(item);
    }
}

rules:
  - !ENCRYPT
    tables:
      t_user:                            # 加密表
        columns:
          password:                      # 加密列（逻辑列名）
            cipher:
              name: password             # 密文列名（物理存储列）
              encryptorName: aes_encryptor  # 加密算法
          phone:
            cipher:
              name: phone_cipher
              encryptorName: aes_encryptor
            assistedQuery:               # 辅助查询列（用于等值查询）
              name: phone_assisted
              encryptorName: md5_assisted

    encryptors:                          # 加密算法定义
      aes_encryptor:
        type: AES
        props:
          aes-key-value: 123456abc       # AES 密钥
          digest-algorithm-name: SHA-1   # 密钥摘要算法
      md5_assisted:                      # 辅助查询加密算法（不可逆）
        type: MD5

// 写入时，ShardingSphere 自动将 password 加密后存储
userMapper.insert(user);  // user.password = "明文密码"

// 查询时，ShardingSphere 自动解密返回明文
User user = userMapper.selectById(1L);  // user.password = "明文密码"

// 等值查询时，ShardingSphere 自动加密查询条件
wrapper.eq(User::getPhone, "13800138000");
// 实际执行：WHERE phone_cipher = AES('13800138000')
//         AND phone_assisted = MD5('13800138000')

rules:
  - !MASK
    tables:
      t_user:
        columns:
          email:
            maskAlgorithm: mask_before_at   # 邮箱脱敏
          phone:
            maskAlgorithm: keep_first_last  # 手机号脱敏
          id_card:
            maskAlgorithm: mask_middle      # 身份证脱敏

    maskAlgorithms:
      mask_before_at:                       # @ 前部分脱敏
        type: MASK_BEFORE_SPECIAL_CHARS
        props:
          special-chars: '@'
          replace-char: '*'
      keep_first_last:                      # 保留前3后4
        type: KEEP_FIRST_N_LAST_M
        props:
          first-n: 3
          last-m: 4
          replace-char: '*'
      mask_middle:                          # 中间脱敏
        type: MASK_FROM_MIDDLE_TO_HEAD
        props:
          replace-char: '*'
          target-chars: '8'

rules:
  - !BROADCAST
    tables:
      - t_dict          # 字典表
      - t_config        # 配置表

rules:
  - !SINGLE
    tables:
      - ds_0.t_address        # 指定单表
      # - ds_1.*              # 加载 ds_1 所有单表
      # - "*.*"              # 加载所有单表（谨慎使用）

mode:
  type: Cluster                          # 集群模式
  repository:
    type: ZooKeeper                      # 注册中心类型：ZooKeeper / ETCD
    props:
      namespace: governance_ds           # 命名空间（隔离不同集群）
      server-lists: localhost:2181       # ZooKeeper 地址（多个逗号分隔）
      retryIntervalMilliseconds: 500     # 重试间隔
      timeToLiveSeconds: 60              # 节点存活时间
      maxRetries: 3                      # 最大重试次数
      operationTimeoutMilliseconds: 500  # 操作超时时间

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_proxy
      server-lists: zk1:2181,zk2:2181,zk3:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500

-- 查看所有数据源
SHOW SCHEMA RESOURCES FROM sharding_db;

-- 动态添加数据源
ADD RESOURCE ds_2 (
    HOST="127.0.0.1", PORT=3306, DB="shardingdb2",
    USER="root", PASSWORD="your_password"
);

-- 创建分片规则
CREATE SHARDING TABLE RULE t_order (
    DATANODES("ds_${0..2}.t_order_${0..1}"),
    DATABASE_STRATEGY(TYPE="standard", SHARDING_COLUMN=user_id, SHARDING_ALGORITHM(TYPE(NAME=inline, PROPERTIES("algorithm-expression"="ds_${user_id % 3}")))),
    TABLE_STRATEGY(TYPE="standard", SHARDING_COLUMN=order_id, SHARDING_ALGORITHM(TYPE(NAME=inline, PROPERTIES("algorithm-expression"="t_order_${order_id % 2}"))))
);

-- 查看分片规则
SHOW SHARDING TABLE RULES;

-- 查看真实 SQL 路由
PREVIEW SELECT * FROM t_order WHERE user_id = 1;

url: jdbc:shardingsphere:classpath:sharding.yaml

# 正确：取模运算
algorithm-expression: ds_${user_id % 2}

# 正确：字符串哈希取模
algorithm-expression: ds_${Math.abs(id.hashCode() % 2)}

# 错误：MOD 算法不支持表达式，应使用 INLINE
# shardingAlgorithms:
#   xxx:
#     type: MOD                    # MOD 只接受 sharding-count 参数
#     props:
#       algorithm-expression: ...  # 这行会报错

rules:
  - !AUTHORITY
    users:
      - root@%:root          # @% 表示允许任意 IP 连接
    provider:
      type: ALL_PERMITTED    # 生产环境应使用 SCHEMA_PRIVILEGES_PERMITTED 细化权限