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

Preface

The author, a senior architect, received a request to split the loan and repayment application tables to improve query efficiency. The loan table holds tens of millions of rows, while the repayment table exceeds two hundred million rows.

Design Plan

Key design points were discussed with leadership before coding:

Sharding Database Basic Info

Use a new instance with an independent data source.

No separate database, only sharding tables (50 tables).

Database name: tcbiz_ins_cash_mas_split

Table name pattern: CashRepayApplySplit0${0..9} , CashRepayApplySplit${10..49}

Sharding column: memberId (last two digits, modulo 50, padded to two digits).

Table structure identical to the original, but add an index on uptTime which was missing.

Historical Data Synchronization

Existing tables:

CashRepayInfo – full data source.

CashRepayApply – current main business table (double‑write with CashRepayInfo ).

Synchronization steps:

Copy missing early data from CashRepayInfo to CashRepayApplySplit .

Copy the remaining data from CashRepayApply to the sharded tables.

Dynamic Switch for Data Write‑through

A runtime switch controls whether new business data is written to the sharded tables, enabling a gradual migration.

Backend Query Refactor

The existing admin/operations backend joins the single table directly. After sharding, the plan is to keep only two‑three years of recent data in CashRepayApply for normal joins, while older data must be queried via memberId against the sharded tables.

External Department Notification

After full migration, other teams (risk control, big‑data) must be informed to switch their reports to the new sharded tables.

DBA Synchronization Process

Because the data volume is huge, DBA will handle bulk migration based on a start id . The process includes:

Relying on developers to sync the earliest missing records.

DBA syncs up to a safe cutoff (e.g., uptTime < now - 5 days ) to avoid interfering with live writes.

After DBA finishes, developers handle the remaining tail data before closing the repayment service for the day.

Sharding Strategy & Code Implementation

The demo uses Spring Boot 3.2.4, MySQL 8, ShardingSphere 5.4.1, and MyBatis‑Plus 3.5.5.

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.19</version>
</dependency>
... (other dependencies) ...

Key configuration files:

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

dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/tcbiz_ins?... 
    username: root
    password: your_password
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')

Mapper example:

@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")

Pitfalls Encountered

Spring Boot 3.2.4 required adding jaxb-impl dependency.

Missing no‑arg constructor in Representer caused NoSuchMethodError ; fixed by adding a custom class.

Groovy inline algorithm expression needed Long.parseLong(member_id) instead of direct modulo.

Data Synchronization Code (Developer Side)

public void dataHandle(Long startId, Long endId) throws AppBizException {
  // query batch of 500 rows from CashRepayInfo
  // for each record, check existence in sharded table and insert/update accordingly
  // support early termination via Redis flag
}

private CashRepayApplySplit buildCashRepayApplySplit(CashRepayInfo history) {
  // map fields from CashRepayInfo (or CashRepayApply) to the split entity
}

Conclusion

The article walks through the entire lifecycle of a sharding project: requirement analysis, schema design, historical data migration, runtime switches, code integration, and troubleshooting. It demonstrates that with proper planning and tooling, even billion‑row tables can be safely partitioned.