Advanced SpringBoot Database Sharding with ShardingSphere

Why Sharding Is Needed

During rapid business iteration, a single‑database single‑table architecture hits a bottleneck when a table exceeds 5‑10 million rows: index depth grows, query speed drops sharply, read/write I/O contention intensifies, and backup/restore becomes slow. Index tuning or SQL optimization alone cannot resolve the performance problem.

Core Bottlenecks

Capacity bottleneck – tables with tens of millions of rows cause index failure and slow queries.

Concurrency bottleneck – a single database has a limited number of connections; under high load the connection pool is exhausted and requests time out.

I/O bottleneck – read/write contention on a single node saturates disk I/O under heavy traffic.

The core goal of sharding is data dispersion, traffic splitting, and pressure sharing .

Splitting Modes

Vertical splitting (database‑level) separates business modules into different databases (e.g., user, order, product, payment) while keeping the same table structure. It solves the problems of too many tables in one database, I/O contention, and tight module coupling. Suitable when module complexity is high but individual tables are not huge.

Horizontal splitting (table‑level) keeps the schema unchanged but distributes rows across multiple tables (e.g., t_order_0‑t_order_3) based on a sharding key. It solves large‑table data volume, slow queries, and index failure, and is ideal for order, log, transaction, or any rapidly growing table.

ShardingSphere Overview

Apache ShardingSphere is a widely used open‑source sharding middleware. Compared with MyCat, Sharding‑JDBC is embedded as a JAR, has zero‑deployment, no proxy overhead, and is the preferred solution for SpringBoot projects.

ShardingSphere Product Forms

Sharding‑JDBC : client‑side component, embedded JAR, highest performance, simple debugging, suited for small‑to‑medium projects.

Sharding‑Proxy : server‑side proxy middleware, independent deployment, supports multiple languages, suitable for large clusters and DBA‑managed environments.

Sharding‑Sidecar : cloud‑native form, community activity has slowed, rarely used in production.

Sharding Algorithm Comparison

MOD – principle: shardingKey % shardCount. Advantages: even data distribution, precise queries. Disadvantage: data mis‑alignment when scaling. Applicable to fixed‑shard workloads that do not require scaling.

Range – principle: split by ID or time range. Advantages: simple scaling, natural ordering. Disadvantage: hot‑spots and uneven distribution. Suitable for time‑series, log, or sequential data.

Custom – principle: business‑defined routing logic. Advantages: highest flexibility for complex rules. Disadvantage: higher development cost. Suitable for complex merchant or user‑dimension sharding.

Environment Preparation & Dependencies

<!-- SpringBoot basic dependency -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!-- MyBatis‑Plus -->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.5.1</version>
</dependency>

<!-- ShardingSphere 5.2.1 stable -->
<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
  <version>5.2.1</version>
</dependency>

<!-- Druid connection pool -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.2.16</version>
</dependency>

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

Database Initialization

CREATE DATABASE IF NOT EXISTS ds0;
CREATE DATABASE IF NOT EXISTS ds1;

CREATE TABLE t_order_0 (order_id BIGINT PRIMARY KEY, user_id BIGINT, order_name VARCHAR(64));
CREATE TABLE t_order_1 (order_id BIGINT PRIMARY KEY, user_id BIGINT, order_name VARCHAR(64));
CREATE TABLE t_order_2 (order_id BIGINT PRIMARY KEY, user_id BIGINT, order_name VARCHAR(64));
CREATE TABLE t_order_3 (order_id BIGINT PRIMARY KEY, user_id BIGINT, order_name VARCHAR(64));

Sharding Routing Rules

Database rule: user_id / 4 % 2 routes to ds0 or ds1. Table rule: user_id % 4 routes to t_order_0‑t_order_3. Example: userId = 5 → 5 % 4 = 1 (t_order_1), 5/4 % 2 = 1 (ds1) → data stored in ds1.t_order_1 .

Sharding Configuration (YAML)

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/ds0?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/ds1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
        username: root
        password: root
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds$->{0..1}.t_order_$->{0..3}
        database-strategy:
          standard:
            sharding-column: user_id
            sharding-algorithm-name: order_db_mod
        table-strategy:
          standard:
            sharding-column: user_id
            sharding-algorithm-name: order_table_mod
        key-generate-strategy:
          column: order_id
          key-generator-name: snowflake
        sharding-algorithms:
          order_db_mod:
            type: MOD
            props:
              sharding-count: 2
          order_table_mod:
            type: MOD
            props:
              sharding-count: 4
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1
        props:
          sql-show: true
          sql-comment-parse-enabled: true

Business Code

Entity

@Data
@TableName("t_order")
public class TOrder {
    // Sharding primary key, generated by Snowflake
    private Long orderId;
    // Sharding key (core field, immutable)
    private Long userId;
    private String orderName;
}

Mapper & Service

public interface OrderMapper extends BaseMapper<TOrder> {}

@Service
public class OrderServiceImpl implements OrderService {
    @Autowired
    private OrderMapper orderMapper;

    @Override
    public void addOrder(TOrder order) {
        // orderId generated automatically
        orderMapper.insert(order);
    }

    @Override
    public TOrder getOrderById(Long orderId) {
        return orderMapper.selectById(orderId);
    }
}

Unit Test

@Test
void testInsertOrder() {
    TOrder order = new TOrder();
    order.setUserId(5L);
    order.setOrderName("ShardingSphere测试订单");
    orderService.addOrder(order);
}

Routing calculation: 5 % 4 = 1 → t_order_1; 5/4 % 2 = 1 → ds1; data stored in ds1.t_order_1 .

Read/Write Splitting Integration

spring:
  shardingsphere:
    rules:
      readwrite-splitting:
        data-sources:
          ds0_group:
            write-data-source-name: ds0
            read-data-source-names: [ds0_slave]
            load-balancer-name: random
          ds1_group:
            write-data-source-name: ds1
            read-data-source-names: [ds1_slave]
            load-balancer-name: random
        load-balancers:
          random:
            type: RANDOM

Core features: default queries go to the slave to boost read throughput; writes, updates, and deletes are forced to the master to guarantee consistency; the @DS("write") annotation can manually force a read on the master.

Custom Sharding Algorithm

public class CustomTableShardingAlg implements StandardShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long userId = shardingValue.getValue();
        // Business rule: userId 0‑100 → t_order_0, others → t_order_1
        if (userId <= 100) {
            return "t_order_0";
        } else {
            return "t_order_1";
        }
    }

    @Override
    public void init() {}
}

sharding-algorithms:
  order_table_alg:
    type: CLASS_BASED
    props:
      strategy: PRECISE
      algorithm-class: com.xxx.alg.CustomTableShardingAlg

Distributed Transaction with Seata

<dependency>
  <groupId>io.seata</groupId>
  <artifactId>seata-spring-boot-starter</artifactId>
  <version>1.6.1</version>
</dependency>

spring:
  shardingsphere:
    transaction:
      default-type: AT

@GlobalTransactional(rollbackFor = Exception.class)
public void crossDbTransTest(){
    // operate ds0
    saveOrder1();
    // operate ds1
    saveOrder2();
    // any exception triggers rollback across both databases
}

Selection guideline: use AT (eventual consistency) for ordinary business; use XA (strong consistency) for financial payment core scenarios.

Practical Takeaways

Sharding is a high‑level core capability for backend engineers and a frequent interview topic. It dramatically improves performance but also adds architecture complexity, operational cost, and development overhead. ShardingSphere becomes the industry standard because it is non‑intrusive, configuration‑driven, compatible with native SQL, and has a rich ecosystem, allowing developers to achieve massive data partitioning, read/write separation, and distributed transaction control without modifying business code.

Optimization priority: first tune indexes, then expand caches, and only resort to sharding when those measures are insufficient. Avoid blind sharding and over‑design.