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

Preface

After a year of writing business code, the team received a request to split the loan and repayment application tables to improve query performance. The loan table holds tens of millions of rows, while the repayment table exceeds two hundred million rows, making sharding a viable solution.

Design Plan

Before coding, the architecture needed to be aligned with leadership on several points.

Sharding Database Basic Information

Use a new instance and independent data source for the sharding database.

No separate database is required; only 50 tables are needed: 50 .

Database name: tcbiz_ins_cash_mas_split (chosen by leadership).

Total number of tables: 50 .

Table naming pattern: CashRepayApplySplit0${0..9} , CashRepayApplySplit${10..49} (camel‑case naming).

Sharding column: memberId . Sharding strategy: take the last two digits of memberId % 50 and pad with a leading zero when the remainder is less than 10, because the table suffix is two digits.

Table structure must be identical to the original, including indexes. Notably, the original uptTime column lacks an index and must be added in the sharding tables.

Historical Data Synchronization

After creating 50 tables, both new and existing data must be written according to the sharding strategy. The original tables are CashRepayInfo (full data) and CashRepayApply (current business table). The migration steps are:

Synchronize the missing early data from CashRepayInfo to CashRepayApplySplit .

Synchronize the remaining data from CashRepayApply to CashRepayApplySplit .

Questions & Answers

Why not sync directly from CashRepayInfo to the sharding tables? Because the volume exceeds two hundred million rows, making a full‑code migration impractical; the DBA must handle the bulk load, but the source and target schemas differ, so a hybrid approach is required.

Existing Backend Query Refactoring

The current admin system joins the single table directly. After sharding, the original join cannot be used. The plan is to keep the original table for the most recent two‑three years of data (still joinable) and query older data via memberId against the sharding tables.

Dynamic Switch for Three‑Write Synchronization

A dynamic switch will control whether new business data is written to both the original and sharding tables. The migration will proceed in three phases: DBA bulk load, developer incremental sync, and finally enabling the switch for seamless operation.

Transactional Configuration

Because the sharding database uses a different data source, a dedicated transaction manager is required:

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

ShardingSphere Integration

The demo uses Spring Boot 3.2.4, JDK 19, MySQL 8, ShardingSphere 5.4.1, and MyBatis‑Plus 3.5.5. Maven dependencies include MySQL driver, ShardingSphere JDBC core, MyBatis‑Plus starter, and JAXB implementation for compatibility.

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

<!-- MyBatis‑Plus for Spring Boot 3 -->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
  <version>3.5.5</version>
</dependency>

<dependency>
  <groupId>com.sun.xml.bind</groupId>
  <artifactId>jaxb-impl</artifactId>
  <version>2.3.3</version>
</dependency>

The application.yml points to a sharding-config.yaml file that defines the data source and sharding rules.

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

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

The sharding configuration declares a single data source ds0 and a sharding rule for cash_repay_apply with an inline algorithm that extracts the last two digits of member_id (converted to Long ) and pads with zero when necessary.

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

props:
  sql-show: true

A MyBatis mapper demonstrates a simple sharding query:

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

Running the application with Spring Boot 3.2.4 initially throws a NoSuchMethodError from SnakeYAML; the workaround is to add a custom Representer class with a no‑arg constructor.

public Representer() {
    super(new DumperOptions());
    this.representers.put(null, new RepresentJavaBean());
}

After fixing the dependency issue, the sharding works and routes queries based on member_id .

Data Migration Code

The migration uses a single‑threaded batch that processes 500 rows at a time, checks for existing records in the sharding tables, and inserts or updates accordingly. It also respects a Redis flag that can terminate the loop early.

/**
 * Synchronize data from the original tables to the sharding tables.
 */
public void dataHandle(Long startId, Long endId) throws AppBizException {
    log.info("CashRepayApplyServiceImpl#dataHandle start startId-{},endId-{}", startId, endId);
    if (endId <= 0L) { endId = null; }
    if (startId > endId) { throw new AppBizException(ErrorCodeEnum.PARAM_CHECK_ERROR.getCode(), "Start ID cannot be greater than end ID"); }
    QueryCashRepayInfoCondition condition = new QueryCashRepayInfoCondition();
    condition.setGteId(startId);
    condition.setLteId(endId);
    condition.setOrders("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);
    }
}

/**
 * Convert CashRepayInfo to CashRepayApplySplit entity.
 */
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;
    }
    // ... populate fields from history ...
    return split;
}

The article concludes with reflections on interview preparation, the importance of architectural design, and the practical experience gained from handling billions of rows through sharding.