Design and Implementation of Database Table Sharding for a Loan Repayment System

After a year of developing business code, the author received a technical requirement to split the loan and repayment application tables to improve query efficiency for tens of millions of rows. The article begins with a preface describing the motivation and the first non‑business requirement.

Design Plan

Key design points include creating a new database instance for the split tables, using a single‑table sharding strategy based on the memberId column, and defining 50 tables named CashRepayApplySplit0${0..9} and CashRepayApplySplit${10..49}. The split tables replicate the original schema, adding an index on the uptTime column that was missing in the source table.

Historical Data Synchronization

Historical data must be migrated from the original CashRepayInfo table (the full data source) to the new split tables. The migration is performed in two steps: first, synchronize missing early records from CashRepayInfo to CashRepayApplySplit, then sync the remaining data.

Dynamic Switch for Data Write‑through

A dynamic switch is introduced to control whether new business data is written to both the original and split tables, enabling a three‑write strategy (original, info, split) during the transition period.

Transactional Considerations

Because the split tables reside in a different data source, a separate transaction manager is required. Example code shows the use of

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

to ensure consistency.

Implementation Details

The author uses Spring Boot 3.2.4, MySQL 8, ShardingSphere 5.4.1, and MyBatis‑Plus 3.5.5. Maven dependencies and the application.yml and sharding-config.yaml configurations are provided, illustrating how to define data sources, sharding rules, and the inline algorithm expression that maps member_id to the appropriate table suffix.

algorithm-expression: cash_repay_apply_$->{Long.parseLong(member_id) % 50}.padLeft(2,'0')

Mapper interfaces and sample SQL queries demonstrate how ShardingSphere automatically routes queries based on the sharding key.

Pain Points and Solutions

The article lists several pitfalls: compatibility issues between Spring Boot 3 and ShardingSphere, missing no‑arg constructors in Representer, and Groovy expression errors when using the modulo operator. Work‑arounds include adding the jaxb‑impl dependency, providing a custom Representer class, and casting the sharding column to Long in the algorithm expression.

Data Migration Code

A single‑threaded batch process is shown, processing 500 rows at a time, handling null checks, upserting into the split tables, and allowing early termination via a Redis flag.

public void dataHandle(Long startId, Long endId) throws AppBizException { ... }

Utility methods for converting CashRepayInfo records into CashRepayApplySplit entities are also included.

Conclusion

The author reflects on the interview experience, the importance of architectural design beyond code, and emphasizes that mastering sharding and large‑scale data migration is a valuable skill for backend engineers.