Master Spring Boot ShardingSphere: Step-by-Step MySQL Sharding Guide

Introduction

After learning the theory of MySQL sharding and the core concepts of ShardingSphere, we now implement Spring Boot + ShardingSphere sharding with actual code.

The article is divided into three parts: building the project architecture, configuring sharding, and discussing key points.

Build Project Architecture

Import Maven Dependencies

Two ways to configure ShardingSphere‑JDBC: Java code or YAML. Add the core dependency:

<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>5.4.0</version>
</dependency>

Or use the Spring Boot starter:

<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core-spring-boot-starter -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>
<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <version>1.33</version>
</dependency>

Note: If you encounter The following method did not exist: org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.setCodePointLimit(I)V , downgrade the ShardingSphere version or adjust the SnakeYAML version.

Create YAML Configuration File

# JDBC logical database name (default: logic_db)

databaseName: logic_db
mode:
  # other mode settings …

dataSources:
  # define data sources …

rules:
  - !FOO_XXX
    ...
  - !BAR_XXX
    ...

props:
  key_1: value_1
  key_2: value_2

Spring Boot Configuration Using ShardingSphere JDBC Driver

# DataSource driver
spring.datasource.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver
# Specify YAML file
spring.datasource.url=jdbc:shardingsphere:classpath:sharding.yaml

Database Preparation

Create two databases db_test_01 and db_test_02, and in each create the same tables: user_info, production, order, order_item_00, order_item_01.

# Create databases
CREATE DATABASE `db_test_01` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE `db_test_02` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# Create tables (example for user_info)
create table `user_info` (
    `user_id` bigint not null comment '用户id',
    `user_name` varchar(255) comment '用户姓名',
    `user_sex` varchar(255) comment '用户性别',
    `user_age` int(8) not null comment '用户年龄',
    primary key (`user_id`) using btree
) engine=InnoDB default charset=utf8mb4;

# Similar DDL for production, order, order_item_00, order_item_01 …

Create Database Users

create user 'sharding'@'%' identified by 'sharding123!@#';
granted all privileges on db_test_01.* to 'sharding'@'%' with grant option;
granted all privileges on db_test_02.* to 'sharding'@'%' with grant option;
flush privileges;

Generate Code with MyBatis‑Plus

service
  ├── UserInfoService.java
  ├── OrderService.java
  ├── OrderItemService.java
  └── ProductionService.java
impl
  ├── UserInfoServiceImpl.java
  ├── OrderServiceImpl.java
  ├── OrderItemServiceImpl.java
  └── ProductionServiceImpl.java
daо
  ├── UserInfoMapper.java
  ├── OrderMapper.java
  ├── OrderItemMapper.java
  └── ProductionMapper.java
mapper
  ├── UserInfoMapper.xml
  ├── OrderMapper.xml
  ├── OrderItemMapper.xml
  └── ProductionMapper.xml
entity
  ├── UserInfo.java
  ├── Order.java
  ├── OrderItem.java
  └── Production.java

Sharding Configuration

Configure Multiple Data Sources

spring:
  shardingsphere:
    mode:
      type: Standalone
      repository:
        type: JDBC
    datasource:
      names: ds0,ds1
      ds0:
        url: jdbc:mysql://localhost:3306/db_test_01?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true&tinyInt1isBit=false&allowMultiQueries=true
        username: sharding
        password: sharding123!@#
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
      ds1:
        url: jdbc:mysql://localhost:3306/db_test_02?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true&tinyInt1isBit=false&allowMultiQueries=true
        username: sharding
        password: sharding123!@#
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource

Sharding Rules

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          order_item:
            actual-data-nodes: ds0.order_item_00
        props:
          sql-show: true

Test Controller

@RestController
@RequestMapping("/admin-service")
public class AdminTestController {
    @Resource
    private OrderItemService orderItemService;

    @PostMapping("/test")
    public void test() {
        OrderItem orderItem = new OrderItem();
        orderItem.setOrderInfoId(1L);
        orderItem.setOrderId(1L);
        orderItem.setProductionName("商品1");
        orderItem.setProductionPrice(9);
        orderItemService.save(orderItem);
    }
}

Running the test shows both the logical SQL and the actual SQL executed on each shard (UNION ALL).

Sharding Database Rule (by ID parity)

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          order_item:
            actual-data-nodes: ds$->{0..1}.order_item_00
            database-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: db-inline-mod
        sharding-algorithms:
          db-inline-mod:
            type: INLINE
            props:
              algorithm-expression: ds$->{id % 2}

Insert a batch of records and verify that even IDs go to db_test_01 and odd IDs to db_test_02.

Sharding Table Rule (hash on production_name)

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          order_item:
            actual-data-nodes: ds$->{0..1}.order_item_0$->{0..1}
            database-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: db-inline
            table-strategy:
              standard:
                sharding-column: production_name
                sharding-algorithm-name: tb-key-hash
        sharding-algorithms:
          db-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{id % 2}
          tb-key-hash:
            type: HASH_MOD
            props:
              sharding-count: 2

Distributed Sequence (Snowflake)

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          order_item:
            key-generate-strategy:
              column: id
              keyGeneratorName: global-id
        key-generators:
          global-id:
            type: SNOWFLAKE
            props:
              worker-id: 1

After enabling Snowflake, the id column is no longer simple auto‑increment, but remains sortable by production_name.

Binding Tables

Define base_order and order_item as binding tables so that they share the same sharding strategy (order_id → database).

spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          base_order:
            actual-data-nodes: ds_$->{0..1}.base_order
            key-generate-strategy:
              column: id
              keyGeneratorName: snowflake
            database-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: db-mod
          order_item:
            actual-data-nodes: ds_$->{0..1}.order_item
            key-generate-strategy:
              column: id
              keyGeneratorName: snowflake
            database-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: db-mod
        binding-tables:
          - base_order,order_item
        sharding-algorithms:
          db-mod:
            type: MOD
            props:
              sharding-count: 2
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1

Key Points

Choosing the Sharding Key

Using production_name as the sharding key works for inserts, but queries that filter by order_item.id trigger a UNION ALL across all tables because the sharding key is not involved. Selecting an appropriate sharding key is crucial.

Logic SQL: select ... from order_item where id = ? and is_del = 0
Actual SQL: ds1 ::: select ... from order_item_00 where id = ? and is_del = 0 UNION ALL select ... from order_item_01 where id = ? and is_del = 0

Distributed ID Generation

Self‑increment IDs cause collisions in distributed environments. Using Snowflake provides globally unique, ordered IDs without manual step‑size configuration.

Binding Tables

Tables with a parent‑child relationship (e.g., order and order_item) must share the same sharding strategy to keep related rows in the same database.

Conclusion

We have completed a practical Spring Boot + ShardingSphere MySQL sharding implementation, covering project setup, sharding configuration, distributed ID generation, binding tables, and testing. The same principles can be extended to other sharding strategies such as date‑based partitioning by writing custom sharding algorithms.