Large Table Splitting in MySQL: Challenges, Solutions, and Lessons Learned

The author, a senior architect, inherited a financial system with a massive 50M+ transaction table that was growing by 600k rows each month, making MySQL maintenance untenable.

System status before sharding: frequent interface timeouts, slow inserts, excessive table size triggering DBA alerts, and lock contention that prevented schema changes.

Goals: split the large table into multiple shards each around 10M rows, optimize query conditions for each interface, and eliminate MySQL slow queries.

Challenges: the table underpinned 26 business scenarios requiring changes to dozens of mapper methods; data migration had to preserve system stability; coordination with downstream systems was required; multi‑datasource transaction handling and pagination across shards added complexity.

Solution: adopt sharding-jdbc for horizontal sharding based on the "transaction time" field, keeping each shard under 10M rows; implement custom annotations and AOP to manage transactions across independent data sources; use multithreaded queries (one thread per shard) to improve performance; devise a pagination conversion algorithm that translates global offset/pageSize into per‑shard parameters; choose a hybrid data migration approach—code‑driven incremental migration for cold data older than three months and DBA‑driven bulk migration for recent hot data.

Technical details: sharding-jdbc supports multiple sharding strategies, is lightweight via Maven, and automatically routes queries based on = or IN conditions; Elasticsearch was evaluated but discarded due to mismatch with business needs; the pagination algorithm is illustrated with an example converting a global offset=8, pageSize=20 into shard‑specific offsets and limits.

Rollout plan: three‑stage phased deployment—(1) dual‑write to new shards while migrating data, (2) stop writes to the old table and redirect external interfaces, (3) decommission the old table.

Summary and recommendations: further investigate sharding middleware, carefully size thread pools, enumerate all affected scenarios when refactoring, prepare robust migration, rollback, and downgrade strategies, and recognize that backend engineers need strong communication skills alongside technical expertise.

MySQL

Sharding

Data Migration

Backend Architecture