在 Spring Boot 中配置 ShardingSphere 实现分库分表与读写分离

随着业务数据量和访问压力的增长，单体数据库架构逐渐面临性能瓶颈与扩展性问题。为了应对这一挑战，常见的解决方案包括读写分离与分库分表：前者用于缓解读写压力不均，后者用于降低单表数据规模，提高整体查询与写入性能。

在实际开发中，如果直接在业务代码中实现路由逻辑，会显著增加系统复杂度。因此，通常会引入 ShardingSphere 这类数据库中间件，在不改动业务代码的前提下完成 SQL 路由与数据拆分。

本文将基于 Spring Boot，逐步实现 ShardingSphere 的读写分离与分库分表配置，并通过示例验证其运行效果。

先决条件

配置好 MySQL 主从，参考 MySQL 主从复制一文中配置 MySQL 主从复制，主机用来写，从机用来读。

数据库中有测试用表:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
create table users
(
    id            bigint auto_increment comment '用户ID'
        primary key,
    email         varchar(255) not null comment '用户邮箱',
    nickname      varchar(50)  not null comment '用户昵称',
    password_hash varchar(255) not null comment '用户密码',
    constraint uk_email
        unique (email)
)
    comment '用户表';

使用 MybatisPlus 3 操作数据库 (可以通过插件例如 MybatisX 一键生成 mapper 和 domain 等)

读写分离

在 Spring Boot 项目中的 application.yaml 中配置数据库驱动和 jdbc url

1
2
3
4
spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding.yaml

在与 application.yaml 同目录下创建 sharding.yaml 文件写入如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
dataSources:
  master:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/db?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: user
    password: pass

  slave:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3307/db?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: user
    password: pass

rules:
  - !SINGLE
    tables:
      - "*.*" # ← 加载所有库的所有表，不分片的表都走这里

  - !READWRITE_SPLITTING
    dataSourceGroups:
      readwrite_ds:
        writeDataSourceName: master
        readDataSourceNames:
          - slave
        transactionalReadQueryStrategy: PRIMARY
        loadBalancerName: random
    loadBalancers:
      random:
        type: RANDOM

props:
  sql-show: true

配置成功后启动 Spring Boot 项目检查无误后进行测试 Sharding jdbc 读写分离是否正常运行，创建 Spring Boot 测试类：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
package com.example.demo;

import com.example.demo.domain.User;
import com.example.demo.mapper.UserMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.infra.hint.HintManager;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author Evorsio
 * @since 2026/4/17
 */
@Slf4j
@SpringBootTest
public class ShardingTests {

    @Autowired
    private UserMapper userMapper;

    /**
     * 测试写操作 → 路由到 master
     */
    @Test
    void testInsert() {
        User user = new User();
        user.setEmail("test@example.com");
        user.setNickname("测试用户");
        user.setPasswordHash("123456");
        userMapper.insert(user);
        log.info("插入成功，id={}", user.getId());
        // 观察日志：Actual SQL: master ::: INSERT ...
    }

    /**
     * 测试读操作 → 路由到 slave
     */
    @Test
    void testSelect() {
        List<User> users = userMapper.selectList(null);
        log.info("查询到 {} 条记录", users.size());
        // 观察日志：Actual SQL: slave ::: SELECT ...
    }

    /**
     * 测试事务内读 → 走 master（避免主从延迟）
     */
    @Test
    @Transactional
    void testTransactionalRead() {
        User user = new User();
        user.setEmail("transaction@example.com");
        user.setNickname("事务用户");
        user.setPasswordHash("123456");
        userMapper.insert(user);

        // 事务内立即读 → 走 master，能读到刚写入的数据
        User result = userMapper.selectById(user.getId());
        log.info("事务内查询结果：{}", result);
        // 观察日志：Actual SQL: master ::: SELECT ...（不走 slave）
    }

    /**
     * 测试 HintManager 强制走 master
     */
    @Test
    void testForceMaster() {
        try (HintManager hintManager = HintManager.getInstance()) {
            hintManager.setWriteRouteOnly();  // 强制走主库
            List<User> users = userMapper.selectList(null);
            log.info("强制主库查询到 {} 条记录", users.size());
            // 观察日志：Actual SQL: master ::: SELECT ...
        }
    }
}

分别测试每个方法，正确输出如下:

testInsert()

1
2
3
2026-04-17T21:32:06.277+12:00  INFO 27064 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( email, nickname, password_hash )  VALUES (  ?, ?, ?  )
2026-04-17T21:32:06.277+12:00  INFO 27064 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master ::: INSERT INTO users  ( email, nickname, password_hash )  VALUES (  ?, ?, ?  ) ::: [test@example.com, 测试用户, 123456]
2026-04-17T21:32:06.330+12:00  INFO 27064 --- [demo] [           main] com.example.demo.ShardingTests           : 插入成功，id=2

testSelect()

1
2
3
2026-04-17T21:34:34.371+12:00  INFO 27216 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,email,nickname,password_hash  FROM users
2026-04-17T21:34:34.371+12:00  INFO 27216 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: slave ::: SELECT  id,email,nickname,password_hash  FROM users
2026-04-17T21:34:34.428+12:00  INFO 27216 --- [demo] [           main] com.example.demo.ShardingTests           : 查询到 2 条记录

testTransactionalRead()

1
2
3
4
5
2026-04-17T21:35:06.995+12:00  INFO 4324 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( email, nickname, password_hash )  VALUES (  ?, ?, ?  )
2026-04-17T21:35:06.995+12:00  INFO 4324 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master ::: INSERT INTO users  ( email, nickname, password_hash )  VALUES (  ?, ?, ?  ) ::: [transaction@example.com, 事务用户, 123456]
2026-04-17T21:35:07.167+12:00  INFO 4324 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: SELECT id,email,nickname,password_hash FROM users WHERE id=?
2026-04-17T21:35:07.168+12:00  INFO 4324 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master ::: SELECT id,email,nickname,password_hash FROM users WHERE id=? ::: [3]
2026-04-17T21:35:07.211+12:00  INFO 4324 --- [demo] [           main] com.example.demo.ShardingTests           : 事务内查询结果：User(id=3, email=transaction@example.com, nickname=事务用户, passwordHash=123456)

testForceMaster()

1
2
3
2026-04-17T21:35:33.391+12:00  INFO 10264 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,email,nickname,password_hash  FROM users
2026-04-17T21:35:33.392+12:00  INFO 10264 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master ::: SELECT  id,email,nickname,password_hash  FROM users
2026-04-17T21:35:33.451+12:00  INFO 10264 --- [demo] [           main] com.example.demo.ShardingTests           : 强制主库查询到 2 条记录

全部测试通过，读写分离配置完成

分库分表

首先配置 mysql 主从复制和测试用数据库

创建三个文件

master-init.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- ===== 建库授权 =====
CREATE DATABASE IF NOT EXISTS db_0;
CREATE DATABASE IF NOT EXISTS db_1;

GRANT ALL PRIVILEGES ON db.* TO 'user'@'%';
GRANT ALL PRIVILEGES ON db_0.* TO 'user'@'%';
GRANT ALL PRIVILEGES ON db_1.* TO 'user'@'%';

-- 创建主从复制专用账号
CREATE USER IF NOT EXISTS 'replicator'@'%' IDENTIFIED BY 'replicator123';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

FLUSH PRIVILEGES;

-- ===== db：非分片表 =====
USE db;

CREATE TABLE roles (
                       id   bigint PRIMARY KEY,
                       type varchar(50) NOT NULL
);

CREATE TABLE user_profile (
                              user_id  bigint PRIMARY KEY,
                              avatar   varchar(255),
                              bio      varchar(500),
                              gender   tinyint DEFAULT 0,
                              birthday date,
                              location varchar(100)
);

CREATE TABLE user_role (
                           user_id bigint NOT NULL,
                           role_id bigint NOT NULL,
                           PRIMARY KEY (user_id, role_id)
);

CREATE TABLE outbox (
                        id         bigint PRIMARY KEY,
                        topic      varchar(100)      NOT NULL,
                        payload    text              NOT NULL,
                        sent       tinyint DEFAULT 0 NOT NULL,
                        created_at datetime          NOT NULL
);

INSERT INTO roles (id, type) VALUES (1, 'admin'), (2, 'user');

-- ===== db_0：users 分片表 =====
USE db_0;
CREATE TABLE users_0 (
                         id            bigint PRIMARY KEY,
                         email         varchar(255) NOT NULL UNIQUE,
                         nickname      varchar(50)  NOT NULL,
                         password_hash varchar(255) NOT NULL
);
CREATE TABLE users_1 LIKE users_0;

-- ===== db_1：users 分片表 =====
USE db_1;
CREATE TABLE users_0 (
                         id            bigint PRIMARY KEY,
                         email         varchar(255) NOT NULL UNIQUE,
                         nickname      varchar(50)  NOT NULL,
                         password_hash varchar(255) NOT NULL
);
CREATE TABLE users_1 LIKE users_0;

slave-init.sh

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
#!/bin/bash

echo "等待 master 就绪..."
until mysql -h mysql-master -P 3306 -u replicator -preplicator123 -e "SELECT 1" &>/dev/null; do
    echo "重试中..."
    sleep 3
done

mysql -uroot -p"$MYSQL_ROOT_PASSWORD" <<EOF
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='mysql-master',
    SOURCE_PORT=3306,
    SOURCE_USER='replicator',
    SOURCE_PASSWORD='replicator123',
    SOURCE_AUTO_POSITION=1,
    GET_SOURCE_PUBLIC_KEY=1;
START REPLICA;
EOF

echo "主从复制配置完成"

docker-compose.yaml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
services:
  mysql-master:
    image: mysql:8.0
    container_name: mysql-master
    environment:
      MYSQL_DATABASE: db
      MYSQL_USER: user
      MYSQL_PASSWORD: pass
      MYSQL_ROOT_PASSWORD: root
    command: >
      --server-id=1
      --log-bin=mysql-bin
      --binlog-format=ROW
      --gtid-mode=ON
      --enforce-gtid-consistency=ON
      --log-slave-updates=ON
    ports:
      - "3306:3306"
    volumes:
      - mysql_master_data:/var/lib/mysql
      - ./master-init.sql:/docker-entrypoint-initdb.d/init.sql # ← 新增

  mysql-slave:
    image: mysql:8.0
    container_name: mysql-slave
    restart: always
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
    command: >
      --server-id=2
      --relay-log=mysql-relay-bin
      --gtid-mode=ON
      --enforce-gtid-consistency=ON
      --log-slave-updates=ON
      --read-only=ON
    volumes:
      - mysql_slave_data:/var/lib/mysql
      - ./slave-init.sh:/docker-entrypoint-initdb.d/init.sh # ← 新增

volumes:
  mysql_master_data:
  mysql_slave_data:

使用 docker compose up -d 启动，并进入 mysql-slave 从机中查看状态 docker exec -it mysql-slave mysql -uroot -proot，

1
show replica status\G;

输出:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
mysql> show replica status\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: mysql-master
                  Source_User: replicator
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000003
          Read_Source_Log_Pos: 197
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 413
        Relay_Source_Log_File: mysql-bin.000003
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 197
              Relay_Log_Space: 3004361
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: 3b2fadb5-3a53-11f1-999c-b2404a28de3c
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: 3b2fadb5-3a53-11f1-999c-b2404a28de3c:1-25
            Executed_Gtid_Set: 3b1bf78c-3a53-11f1-923f-e21ea55938fa:1-5,
3b2fadb5-3a53-11f1-999c-b2404a28de3c:1-25
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 1
            Network_Namespace:
1 row in set (0.00 sec)

ERROR:
No query specified

状态正常，可以进行 sharding sphere 分库分表配置，修改 sharding.yaml,

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
dataSources:
  master: # db 库（非分片表）
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/db?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: user
    password: pass

  master_0: # db_0 库
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/db_0?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: user
    password: pass

  master_1: # db_1 库
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/db_1?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: user
    password: pass

  slave: # slave db 库
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3307/db?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: user
    password: pass

  slave_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3307/db_0?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: user
    password: pass

  slave_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3307/db_1?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: user
    password: pass

rules:
  - !READWRITE_SPLITTING
    dataSourceGroups:
      rw_ds: # 非分片表用这个
        writeDataSourceName: master
        readDataSourceNames:
          - slave
        transactionalReadQueryStrategy: PRIMARY
        loadBalancerName: random
      rw_ds_0:
        writeDataSourceName: master_0
        readDataSourceNames:
          - slave_0
        transactionalReadQueryStrategy: PRIMARY
        loadBalancerName: random
      rw_ds_1:
        writeDataSourceName: master_1
        readDataSourceNames:
          - slave_1
        transactionalReadQueryStrategy: PRIMARY
        loadBalancerName: random
    loadBalancers:
      random:
        type: RANDOM

  - !SHARDING
    tables:
      users:
        actualDataNodes: rw_ds_${0..1}.users_${0..1}
        databaseStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: db_inline
        tableStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: tbl_inline

    shardingAlgorithms:
      db_inline:
        type: INLINE
        props:
          algorithm-expression: rw_ds_${id % 2}
      tbl_inline:
        type: INLINE
        props:
          algorithm-expression: users_${(id.intdiv(2)) % 2}

  - !SINGLE
    tables:
      - "*.*"
    defaultDataSource: rw_ds # 非分片表走 db 库

props:
  sql-show: true

配置完后运行测试，

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    /**
     * 测试批量写操作 → 分库分表
     */
    @Test
    void testBatchInsertSharding() {
        for (int i = 0; i < 20; i++) {
            User user = new User();
            user.setId(i+1L);
            user.setEmail("user_" + i + "@test.com");
            user.setNickname("user" + i);
            user.setPasswordHash("pwd_" + i);

            userMapper.insert(user);
        }
    }

控制台输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
2026-04-18T01:12:41.626+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.626+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [1, user_0@test.com, user0, pwd_0]
2026-04-18T01:12:41.653+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.653+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [2, user_1@test.com, user1, pwd_1]
2026-04-18T01:12:41.666+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.666+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [3, user_2@test.com, user2, pwd_2]
2026-04-18T01:12:41.676+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.676+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [4, user_3@test.com, user3, pwd_3]
2026-04-18T01:12:41.685+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.685+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [5, user_4@test.com, user4, pwd_4]
2026-04-18T01:12:41.692+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.692+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [6, user_5@test.com, user5, pwd_5]
2026-04-18T01:12:41.702+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.702+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [7, user_6@test.com, user6, pwd_6]
2026-04-18T01:12:41.709+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.709+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [8, user_7@test.com, user7, pwd_7]
2026-04-18T01:12:41.716+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.716+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [9, user_8@test.com, user8, pwd_8]
2026-04-18T01:12:41.724+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.724+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [10, user_9@test.com, user9, pwd_9]
2026-04-18T01:12:41.731+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.731+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [11, user_10@test.com, user10, pwd_10]
2026-04-18T01:12:41.739+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.740+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [12, user_11@test.com, user11, pwd_11]
2026-04-18T01:12:41.746+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.746+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [13, user_12@test.com, user12, pwd_12]
2026-04-18T01:12:41.751+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.752+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [14, user_13@test.com, user13, pwd_13]
2026-04-18T01:12:41.759+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.759+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [15, user_14@test.com, user14, pwd_14]
2026-04-18T01:12:41.766+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.766+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [16, user_15@test.com, user15, pwd_15]
2026-04-18T01:12:41.772+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.772+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [17, user_16@test.com, user16, pwd_16]
2026-04-18T01:12:41.777+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.777+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [18, user_17@test.com, user17, pwd_17]
2026-04-18T01:12:41.781+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.781+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_1 ::: INSERT INTO users_1  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [19, user_18@test.com, user18, pwd_18]
2026-04-18T01:12:41.785+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO users  ( id, email, nickname, password_hash )  VALUES (  ?, ?, ?, ?  )
2026-04-18T01:12:41.786+12:00  INFO 16080 --- [demo] [           main] ShardingSphere-SQL                       : Actual SQL: master_0 ::: INSERT INTO users_0  ( id, email, nickname, password_hash )  VALUES (?, ?, ?, ?) ::: [20, user_19@test.com, user19, pwd_19]

20 条测试数据全部插入数据库成功，进入数据库查看

db_0.users_0

id	email	nickname	password
4	user_3@test.com	user3	pwd_3
8	user_7@test.com	user7	pwd_7
12	user_11@test.com	user11	pwd_11
16	user_15@test.com	user15	pwd_15
20	user_19@test.com	user19	pwd_19

db_0.users_1

id	email	nickname	password
2	user_1@test.com	user1	pwd_1
6	user_5@test.com	user5	pwd_5
10	user_9@test.com	user9	pwd_9
14	user_13@test.com	user13	pwd_13
18	user_17@test.com	user17	pwd_17

db_1.users_0

id	email	nickname	password
1	user_0@test.com	user0	pwd_0
5	user_4@test.com	user4	pwd_4
9	user_8@test.com	user8	pwd_8
13	user_12@test.com	user12	pwd_12
17	user_16@test.com	user16	pwd_16

db_1.users_1

id	email	nickname	password
3	user_2@test.com	user2	pwd_2
7	user_6@test.com	user6	pwd_6
11	user_10@test.com	user10	pwd_10
15	user_14@test.com	user14	pwd_14
19	user_18@test.com	user18	pwd_18

如上可见 20 条 user 数据被正确分到了四张表中，分库分表配置成功。

通过上述示例可以看到，数据在插入时已经按照分片规则被自动路由到不同的库和表中，实现了基础的分库分表效果。

但需要注意的是，本文中的分片规则属于人为构造的简化模型，更多用于帮助理解数据路由过程。在真实生产环境中，ShardingSphere 还支持更复杂的分片策略（如哈希分片、范围分片、自定义算法等），同时也可以配合雪花算法等分布式 ID 生成方案，从而避免手动维护 ID 和路由规则的问题。