Mastering MySQL Sharding: Principles, Architecture, and Real‑World Implementation

MySQL scaling bottlenecks

Single‑instance MySQL quickly hits performance limits when data volume grows, queries slow, write pressure rises, indexes bloat, and scaling becomes difficult.

Sharding principle

Sharding (分库分表) horizontally partitions data. “分库” distributes data across multiple database instances; “分表” splits a logical table into many physical tables, possibly across different instances.

Vertical vs horizontal sharding

Vertical sharding separates data by business domain (e.g., user, order, product databases). It creates clear business boundaries and low coupling, but cross‑database joins become complex.

Horizontal sharding partitions rows of a single logical table according to a rule (user‑id modulo, time range, region code, etc.). It spreads data volume and load, but access paths become more complex and transaction/query capabilities are limited.

Practical implementation – e‑commerce order system

Order table can reach billions of rows and must support high‑concurrency order placement, lookup, payment callbacks, and status transitions. The following five‑step process is recommended.

Step 1 – Assess sharding necessity

Verify that index tuning, read/write separation, caching, or archiving cannot solve the problem; consider sharding only when a single database and table approach the performance ceiling.

Step 2 – Define sharding rule

Select a stable, high‑frequency shard key such as user_id, tenant_id, or a business field embedded in the order number. Changing the rule after deployment incurs high cost.

Step 3 – Design table schema

All shard tables must share an identical structure. Limit the number of columns and indexes per table to avoid index bloat.

Step 4 – Introduce middleware

Use a sharding middleware (e.g., ShardingSphere) to perform SQL routing and rewriting, reducing application intrusion. Be aware of the middleware’s supported SQL features and performance overhead.

Step 5 – Migrate data

Data migration is typically the most complex phase. A common approach:

Full migration of historical data.

Dual‑write or binlog‑based incremental synchronization.

Cut‑over verification.

Gradual decommissioning of the old database.

During migration, perform rigorous validation, maintain rollback plans, and use gray‑release strategies.