Design and Implementation of MySQL Table Sharding with ShardingSphere and Spring Boot

The author, a senior architect, describes how a newly joined team was tasked with splitting the loan and repayment tables, which have tens of millions of rows, to improve query performance and reduce database pressure.

Design Overview – A new sharding database instance is created, using a single‑table sharding strategy based on the memberId column. Fifty tables are generated with names like CashRepayApplySplit0${0..9} and CashRepayApplySplit${10..49} . The table structure mirrors the original, and an index is added to the uptTime column.

Historical Data Synchronization – Existing data in CashRepayInfo must be copied to the new split tables. The process is divided into three phases: (1) developers sync a portion of data, (2) DBAs sync a deterministic range based on uptTime (using operatorDate - 5 Day ), and (3) developers finish the remaining recent data after the DBA window closes.

Backend Query Refactor – After sharding, the original single‑table joins are no longer viable. The solution keeps recent two‑three years of data in the original table for simple joins, while older data is accessed via the sharding key memberId through a new query UI.

Implementation Details

Key dependencies (Maven):

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.19</version>
</dependency>

<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>shardingsphere-jdbc-core</artifactId>
  <version>5.4.1</version>
</dependency>

<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
  <version>3.5.5</version>
</dependency>

Sample application.yml configuration points to a sharding-config.yaml file that defines the data source, actual data nodes, and the inline sharding algorithm:

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding-config.yaml

sharding:
  tables:
    cash_repay_apply:
      actualDataNodes: ds0.cash_repay_apply_0${0..9},ds0.cash_repay_apply_${10..49}
      tableStrategy:
        standard:
          shardingColumn: member_id
          shardingAlgorithmName: memberId-suffix-mode-50
  shardingAlgorithms:
    memberId-suffix-mode-50:
      type: INLINE
      props:
        algorithm-expression: cash_repay_apply_$->{Long.parseLong(member_id) % 50}.padLeft(2,'0')

Transactional handling for the split tables requires a dedicated transaction manager:

@Transactional(propagation = Propagation.REQUIRES_NEW, transactionManager = "transactionManagerSplit")
public void syncData(...) { ... }

The article also documents several pitfalls encountered: incompatibility between Spring Boot 3.2.x and certain ShardingSphere versions, missing no‑arg constructors in Representer , and Groovy expression errors that required casting member_id to Long before applying the modulo operation.

Finally, the author shares the complete demo source, a batch‑processing routine that reads 500 rows at a time, and a method that converts legacy CashRepayInfo records into the new CashRepayApplySplit entities, ensuring idempotent upserts and graceful error handling.