Design and Implementation of Table Sharding for Loan and Repayment Applications Using ShardingSphere and SpringBoot

Introduction

After a year of business development, the author received a request to split the loan and repayment application tables to improve query performance for tens of millions of rows. This marks the first non‑business requirement since joining the company.

Design Overview

The design includes creating a new database instance, using 50 split tables named CashRepayApplySplit0${0..9} and CashRepayApplySplit${10..49} , with the sharding column memberId whose last two digits (mod 50) determine the target table. Table structures mirror the original, adding an index on uptTime where missing.

Historical Data Synchronization

Existing data resides in CashRepayInfo (full data) and CashRepayApply (current business table). The migration steps are:

Synchronize missing historical rows from CashRepayInfo to the split tables.

Synchronize the remaining rows of CashRepayApply to the split tables.

Backend Query Refactoring

The original admin system joins the single table directly. After sharding, the main table will retain only two‑to‑three years of data and continue to be queried by memberId . Older data must be accessed through the split tables, requiring a new query page with memberId as a mandatory parameter.

External Department Coordination

Once full data is migrated, the legacy CashRepayInfo table will be phased out. Other teams (risk control, big data) must be notified to switch their reports to the new split tables.

DBA Synchronization Process

Developers handle the initial data sync, then provide DBA with a start ID. DBA synchronizes up to operatorDate - 5 days to avoid missing newly inserted rows. Remaining recent rows are later synced by developers after business hours.

Three‑Write Synchronization

During the transition, three tables ( CashRepayInfo , CashRepayApply , CashRepayApplySplit ) are kept in sync using a dynamic switch to enable or disable double‑write to the split tables.

Scheduled Task Scanning

A daily task compares data between the main and split tables, raising alerts if discrepancies are found, and ensures that failures in writing to the split tables do not affect the primary business flow.

Demo Implementation

The demo uses SpringBoot 3.2.4, JDK 19, MySQL 8, ShardingSphere 5.4.1, and MyBatis‑Plus 3.5.5. Maven dependencies include MySQL connector, ShardingSphere JDBC core, MyBatis‑Plus starter, and JAXB implementation.

application.yml

spring:
  application:
    name: sharding-jdbc-demo
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding-config.yaml
  profiles:
    active: default
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

sharding-config.yaml

dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/tcbiz_ins?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    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_${String.valueOf(Long.parseLong(member_id.substring(member_id.length()-2))%50).padLeft(2,'0')
    props:
      sql-show: true

Mapper Example

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

Pitfalls Encountered

Missing JAXB dependency for SpringBoot 3.

No‑arg constructor missing in org.yaml.snakeyaml.representer.Representer , solved by adding a custom class.

Groovy inline sharding expression error; fixed by casting member_id to Long before modulo.

Data Synchronization Code (Developer‑Side)

/**
 * Synchronize data example
 */
public void dataHandle(Long startId, Long endId) throws AppBizException {
    log.info("CashRepayApplyServiceImpl#dataHandle start startId-{},endId-{}", startId, endId);
    if (endId <= 0L) {
        endId = null;
    } else if (startId > endId) {
        throw new AppBizException(ErrorCodeEnum.PARAM_CHECK_ERROR.getCode(), "起始id不能大于结束id");
    }
    QueryCashRepayInfoCondition condition = new QueryCashRepayInfoCondition();
    condition.setGteId(startId);
    condition.setLteId(endId);
    condition.setOrders("id+"); // id ascending
    List
infoList = cashRepayInfoDao.query(condition, 0, -1);
    long lastId;
    while (CollectionUtil.isNotEmpty(infoList)) {
        lastId = infoList.get(infoList.size() - 1).getId() + 1;
        infoList.forEach(history -> {
            try {
                if (StringUtil.isBlank(history.getMemberId()) || StringUtil.isBlank(history.getRepayNo())) {
                    log.error("CashRepayApplyServiceImpl#dataHandle error memberId or repayNo is null id-{}", history.getId());
                    return;
                }
                QueryCashRepayApplySplitCond splitCond = new QueryCashRepayApplySplitCond();
                splitCond.setMemberId(history.getMemberId());
                splitCond.setRepayApplyNo(history.getRepayNo());
                CashRepayApplySplit exist = cashRepayApplySplitDao.getUnique(splitCond);
                CashRepayApplySplit splitData = buildCashRepayApplySplit(history);
                if (exist == null) {
                    cashRepayApplySplitDao.add(splitData);
                } else {
                    splitData.setId(exist.getId());
                    cashRepayApplySplitDao.update(splitData);
                }
            } catch (Exception e) {
                log.error("CashRepayApplyServiceImpl#dataHandle error id-{},repayNo-{}", history.getId(), history.getRepayNo());
                throw new RuntimeException(e);
            }
        });
        log.info("dataHandle finish startId-{},endId-{}", condition.getGteId(), endId);
        String redisCache = RedisCacheUtils.getRedisCache(CashApplyRedisKeyConsts.TERMINATE_SPLIT_SYNC_DATA);
        if (StringUtil.isNotEmpty(redisCache)) {
            log.info("CashRepayApplyServiceImpl#dataHandle need terminate loop startId-{}", condition.getGteId());
            break;
        }
        condition.setGteId(lastId);
        infoList = cashRepayInfoDao.query(condition, 0, -1);
    }
}

private CashRepayApplySplit buildCashRepayApplySplit(CashRepayInfo history) {
    CashRepayApplySplit split = new CashRepayApplySplit();
    CashRepayApply apply = cashRepayApplyDao.get(history.getRepayNo());
    if (apply != null) {
        BeanUtils.copyProperties(apply, split, "id");
        return split;
    }
    // ... omitted: map fields from CashRepayInfo to split entity ...
    return split;
}

Conclusion

The author reflects that while sharding may seem daunting, the real challenge lies in designing a solution that coordinates multiple teams, preserves existing functionality, and handles massive data safely. After completing the migration, the team now has practical experience with billion‑level data sharding.