Case Study: Splitting a Massive MySQL Financial Transaction Table Using Sharding-JDBC

Two years ago the author inherited a financial system whose core transaction table had exceeded 50 million rows and was growing by 600 k rows per month, causing severe MySQL performance degradation and operational risks.

The team identified critical issues: frequent interface timeouts, slow inserts, massive table size triggering DBA alerts, and locking problems during schema changes.

The primary goal was to split the large table into multiple sub‑tables, each holding roughly 10 million rows, while optimizing query conditions to eliminate slow queries and maintain API availability.

Key challenges included the table's central role across 26 business scenarios requiring changes to dozens of mapper methods, ensuring data migration stability, coordinating multi‑team cooperation, and handling downstream system adaptations.

To implement sharding, the team selected sharding-jdbc as the middleware, leveraging its multi‑strategy support and low intrusion. They also evaluated Elasticsearch for query acceleration but ultimately abandoned it due to mismatched requirements.

Sharding key chosen: transaction time, because it appears in most queries, distributes data evenly, and is non‑nullable.

Sharding strategy: horizontal partitioning by month, keeping each shard around 600‑700 k rows.

Technical difficulties addressed:

Multi‑data‑source transaction management was solved with custom annotations and AOP‑based transaction control.

Cross‑shard pagination was handled by a custom algorithm that converts a global offset/pageSize into per‑shard offsets and page sizes, using multi‑threaded counts and cumulative offsets.

Data migration was planned in two parts: cold data (older than three months) migrated incrementally via custom code, and hot data (last three months) migrated by the DBA after a brief write‑stop window before go‑live.

The rollout was divided into three phases: (1) create shards, migrate data, enable dual‑write, and route all queries to shards; (2) stop writes to the old table and switch external interfaces to the new shards; (3) decommission the original large table.

In conclusion, the project highlighted the need for thorough middleware evaluation, careful thread‑pool sizing, comprehensive scenario mapping, robust migration and rollback plans, and the importance of both technical and soft skills for backend engineers.