Design and Implementation of Database Table Sharding for Loan and Repayment Applications Using ShardingSphere and Spring Boot

The author, a senior architect, received a request to split the loan and repayment application tables to improve query performance for tens of millions of records.

Design : A new database instance is created for the split tables, with 50 tables named CashRepayApplySplit0${0..9} and CashRepayApplySplit${10..49} . The sharding key is memberId , using the last two digits modulo 50, padded to two digits. Table structure mirrors the original, adding an index on uptTime .

Historical Data Migration : Full data from CashRepayInfo (the master table) must be synchronized to the split tables. Migration is performed in two steps: first, sync missing historical records from CashRepayInfo to CashRepayApplySplit ; second, sync the remaining data from CashRepayApply to the split tables. A dynamic switch controls the dual‑write behavior during migration.

Backend Query Adaptation : The existing admin system uses joins on a single table, which will no longer work after sharding. The solution keeps recent two‑three years of data in the original table for join queries, while older data is accessed via memberId on the split tables.

Implementation Details : The project uses Spring Boot 3.2.4, MySQL 8, ShardingSphere 5.4.1, and MyBatis‑Plus 3.5.5. Key configuration snippets include:

<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>

application.yml snippet:

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

sharding-config.yaml snippet (core sharding rule):

rules:
  - !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')

Example MyBatis mapper:

@Mapper
public interface CashRepayApplyMapper extends BaseMapper
{
    @Select("select * from cash_repay_apply where member_id = #{memberId}")
    List
testSharding(@Param("memberId") String memberId);
}

Transactional annotation for dual‑write:

@Transactional(propagation = Propagation.REQUIRES_NEW, transactionManager = "transactionManagerSplit")

Batch data‑handle method (processes 500 rows per iteration):

public void dataHandle(Long startId, Long endId) throws AppBizException {
    // ... query CashRepayInfo, build CashRepayApplySplit, insert or update split table ...
    // loop with 500‑row batches, check Redis cache for early termination
}

Pitfalls : Compatibility issues with Spring Boot 3 required adding the JAXB implementation dependency, fixing a missing no‑arg constructor in org.yaml.snakeyaml.representer.Representer , and adjusting the Groovy sharding expression to use Long.parseLong(member_id) % 50 instead of the string mod method.

Conclusion: The article demonstrates a practical end‑to‑end solution for table sharding, covering design, migration, code integration, and operational considerations.