Design and Implementation of Database Table Sharding for Loan and Repayment Applications

In the preface the author, a senior architect, explains that after a year of business development a new technical requirement emerged to split the loan and repayment application tables to improve query performance for tens of millions of rows.

Design Plan outlines the need for a new database instance, no separate database, 50 sharded tables named CashRepayApplySplit0${0..9} and CashRepayApplySplit${10..49} , using the memberId column with a suffix‑based sharding algorithm (last two digits modulo 50, padded to two digits).

Historical Data Synchronization describes the three‑step process: first copy missing early data from CashRepayInfo to the sharded tables, then sync the remaining data, and finally handle new data generated during the DBA migration using a cutoff based on operatorDate - 5 days and uptTime filters.

DBA Operation explains how the DBA receives a start id and works until a deterministic end id is reached, after which developers finish the remaining data with a nightly batch.

Dynamic Switch introduces a runtime flag that controls whether new business writes are duplicated to the sharded tables, ensuring a smooth cut‑over after all data is migrated.

Demo and Pitfalls provides a complete ShardingSphere demo using Spring Boot 3.2.4, MySQL 8, and MyBatis‑Plus, including Maven dependencies, application.yml , sharding-config.yaml , and mapper interfaces. It lists three major issues encountered: missing JAXB dependency, Representer constructor changes, and Groovy expression errors, and shows the fixes for each.

Code Samples include a transactional method annotated with @Transactional(propagation = Propagation.REQUIRES_NEW, transactionManager = "transactionManagerSplit") , a mapper method for querying by memberId , and a batch data‑handle routine that processes 500 rows at a time, updates the sync status in Redis, and logs errors.

The conclusion reflects on how the sharding project, while seemingly complex, boiled down to reading documentation, configuring the framework, and writing a few API calls, emphasizing the importance of architectural design and cross‑department coordination.