How We Split a 50‑Million‑Row MySQL Table: Strategies and Lessons Learned

Preface

Two years ago I took over the development and maintenance of the company's financial system. Early on we discovered a table with over 50 million rows that stores transaction flows and is used by many downstream systems.

The table continues to grow by more than 600,000 rows each month, meaning it would exceed 100 million rows within six months.

System Status Before Splitting

Frequent timeouts on interfaces that query the flow table, some interfaces becoming unusable.

Slow daily inserts due to heavy write load.

The single table occupies excessive space, triggering DBA monitoring alerts.

Any ALTER operation causes high replication lag and long table locks.

Splitting Goals

Divide the large flow table into multiple shards, keeping each shard around 10 million rows (a size MySQL handles comfortably).

Optimize query conditions for each interface to maintain availability and eliminate slow queries.

Difficulty Analysis

The flow table is foundational to the finance system; many features and downstream systems depend on it, requiring meticulous development, testing, and deployment processes.

There are 26 usage scenarios, requiring changes to 32 mapper methods and numerous other code locations.

The massive data volume demands a stable migration process.

High user volume and critical functionality mean the rollout must minimize downtime and ensure system availability, requiring comprehensive rollback and degradation strategies.

Splitting the table changes interfaces, which in turn forces other systems to adapt; coordinating multi‑team cooperation is a major challenge.

Overall Process

Sharding Middleware Research

We selected sharding‑jdbc as the sharding plugin. Its advantages include support for multiple sharding strategies with automatic detection of = or IN conditions, and a lightweight Maven dependency with minimal intrusion.

We also considered using Elasticsearch to accelerate queries, but after two discussions with the ES team we found the provided service unsuitable for our scenario and abandoned the plan, opting instead for parallel query threads per shard.

Splitting Criteria

Horizontal sharding was chosen because vertical sharding or fixed‑table splitting cannot prevent unlimited growth for immutable financial flow data. Horizontal sharding keeps each shard’s size constant, allowing periodic archiving.

Criteria for selecting a sharding key:

Prefer fields that appear most frequently in query conditions to reduce code changes.

Ensure the field distributes data evenly to keep each shard around 10 million rows.

The field must be mandatory (no nulls).

Analyzing our data and business needs, “transaction time” emerged as the ideal sharding key: it is always present, partitions data by month into roughly 600‑700 k rows per shard, and appears in about 70 % of queries.

Technical Challenges

Multi‑datasource transaction issue – sharding‑jdbc requires an independent data source, leading to transaction problems across multiple data sources. We solved this with a custom annotation and aspect that opens a transaction, rolling back or committing based on the call stack.

Pagination across shards – Global LIMIT/OFFSET no longer works after splitting. We designed a method that converts a global pagination request (e.g., offset = 8, pageSize = 20) into per‑shard pagination parameters. The conversion process involves counting matching rows per shard, mapping the global offset to shard‑specific offsets, and adjusting page sizes accordingly.

Data Migration Plan

We evaluated two migration approaches: DBA‑driven migration and custom code migration. Combining both, we handle cold data (older than three months) with incremental code‑driven migration, and hot data (within three months) with a brief write‑stop and DBA bulk migration before release. This balances time cost, data consistency, and impact on the live system.

Overall Rollout Process

Stage 1: Create shard tables, migrate existing data, enable dual‑write (old and new tables), and route all queries to the shards for validation.

Stage 2: Stop writes to the old table, switch business logic to use the new shard‑based service interfaces.

Stage 3: Decommission the original large table.

Summary

Further research on sharding middleware is needed; sharding‑jdbc’s features were under‑utilized due to our specific sharding key.

Thread pool sizing must be carefully analyzed to avoid exhausting system threads.

When refactoring an existing project, enumerate all scenarios and map them to code locations to ensure full coverage.

Design robust data migration and inconsistency handling strategies, considering time cost and impact.

Prepare comprehensive rollback and degradation plans for complex releases to guarantee stability.

Additional Thoughts

Effective communication is a crucial soft skill for backend engineers, complementing technical expertise. Coordinating with other teams, managing expectations, and presenting technical changes as mutually beneficial can significantly influence project success.