How to Implement High‑Performance Database Sharding with ShardingSphere and SpringBoot

Introduction

After a year of writing business code, the author received a request to split the loan and repayment application tables to improve query performance.

Design Overview

Sharding Database Basics

New instance, independent datasource.

No need for a separate database, only tables (50 tables).

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

Sharding column memberId, suffix two‑digit, pad with 0.

Ensure uptTime column has an index.

Historical Data Synchronization

The migration moves existing data from CashRepayInfo to the new split tables. Because the total row count exceeds two hundred million, the DBA handles an initial bulk sync up to a cutoff (e.g., uptTime < now - 5 days), after which developers sync the remaining recent rows.

Backend Query Refactoring

Old single‑table joins are replaced by a hybrid approach: recent two‑three years of data stay in the original CashRepayApply table for normal joins, while older data are queried via memberId from the split tables.

Dynamic Switch and Three‑Write Strategy

A runtime switch controls whether new writes are duplicated to the split tables. The three‑write flow is: write to CashRepayApply, then to CashRepayInfo, and finally to CashRepayApplySplit.

Scheduled Task for Consistency

A daily task scans today’s data in both the main and split tables, compares fields, and raises alerts on mismatches to ensure data integrity.

Implementation Details

Technology Stack

SpringBoot 3.2.4, JDK 19, MySQL 8, ShardingSphere 5.4.1, MyBatis‑Plus 3.5.5.

Maven Dependencies

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

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

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_${Long.parseLong(member_id) % 50}.padLeft(2,'0')
    props:
      sql-show: true

Mapper Example

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

Transactional Annotation for Split Table

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

Data Synchronization Service

public void dataHandle(Long startId, Long endId) throws AppBizException {
    // batch query from CashRepayInfo, loop, insert/update CashRepayApplySplit,
    // handle termination flag from Redis, and continue until all rows are processed.
}

Utility to Build Split 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;
    }
    // ... map fields from CashRepayInfo to split entity ...
    return split;
}

Conclusion

The author reflects on the learning experience of designing and implementing a sharding solution for billions of rows, emphasizing the importance of architectural planning, cross‑team coordination, and careful handling of both historical and live data.