How We Split a 500M+ MySQL Table: Lessons in Sharding, Migration, and Performance

Preface

The author took over a financial system two years ago and discovered a 50 million‑row table storing transaction flows, growing by 6 million rows each month and projected to exceed 100 million rows within six months.

System Status Before Splitting

Frequent time‑outs on flow‑related APIs, some interfaces unusable.

Slow inserts causing daily flow creation delays.

Table occupied excessive space, triggering DBA alarms.

Any ALTER operation caused high latency and long table locks.

Goals of Table Splitting

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

Optimize query conditions for each interface to eliminate slow queries.

Difficulty Analysis

The table is the foundation of the financial system; many downstream systems depend on it, so any mistake can cause major issues.

26 business scenarios require changes to 32 mapper methods and many more code locations.

Massive data volume demands a stable migration process.

High‑availability requirement during rollout, needing comprehensive release, rollback, and degradation plans.

Schema changes affect downstream systems, requiring coordinated development, testing, and deployment.

Overall Process

The project was divided into research, design, implementation, testing, and rollout phases, with detailed diagrams (omitted for brevity).

Details

Sharding Middleware Research

We chose Sharding‑JDBC as the sharding plugin. Its advantages:

Supports multiple sharding strategies and automatically resolves = or IN conditions.

Lightweight Maven dependency with minimal intrusion.

We considered using Elasticsearch for query acceleration, but the provided ES service did not fit our scenario, so we abandoned it and used per‑table query threads instead.

Sharding Key Selection

Horizontal sharding based on the “transaction time” field satisfies three principles: it appears in most queries, distributes data evenly (≈600‑700 k rows per month), and is non‑nullable.

Technical Challenges

Multi‑Data‑Source Transaction

Sharding‑JDBC requires an independent data source, leading to multi‑data‑source transaction issues, solved by custom annotations and AOP‑based transaction management (specific code omitted).

Cross‑Table Pagination

Because each shard returns a different number of rows, the original LIMIT cannot be used. We designed a method that first queries each shard for row counts, then converts the global offset/pageSize into per‑shard offset/pageSize, as illustrated in the diagram.

Data Migration Plan

Two approaches were evaluated: DBA‑driven migration and custom code migration. The final hybrid plan:

Cold data (older than three months) migrated by code in small batches (“ant‑style” moves).

Hot data (last three months) migrated by DBA after a brief write‑stop window (~2 hours).

The final DBA migration is controlled to limit per‑run data volume, avoiding instance‑level latency spikes.

Deployment Workflow

Phase 1: Create shards, migrate data, enable dual‑write, route all queries to shards (validation).

Phase 2: Stop writes to the old table, switch business services to the new API (validation).

Phase 3: Decommission the original large table.

Summary

Further research on sharding middleware is needed; Sharding‑JDBC’s features were under‑utilized and its independent data source added transaction complexity.

Thread‑pool sizing must be carefully analyzed to avoid exhausting CPU cores.

All scenarios should be enumerated and covered at the class and method level before refactoring.

Data migration requires robust plans for consistency and fallback.

Comprehensive rollback and degradation strategies are essential for stable releases.

Side Note

Effective communication skills are as important as technical ability for backend engineers, who must coordinate with multiple teams and manage both business and technical aspects of a project.