Mastering MySQL Sharding: When and How to Use Database Partitioning

Remember, if someone asks you the most effective way to optimize a database, you might say SQL tuning, distributed clusters, or sharding—just do it! But jumping straight to sharding isn’t always appropriate; you need to understand what sharding is, when to apply it, and the different methods available.

First, understand what database and table partitioning (sharding) are, focusing on MySQL.

Sharding (分库): Splitting a single database instance into multiple instances, distributing data across them.

Table partitioning (分表): Splitting a single table into multiple tables, distributing rows among them.

For large‑scale internet projects, daily data growth can reach tens of millions, making a single MySQL server unrealistic.

As data volume and QPS increase, a single‑node database hits storage and concurrency limits. Sharding adopts a “divide‑and‑conquer” strategy: sharding databases reduces storage pressure and improves scalability, while sharding tables alleviates query bottlenecks caused by oversized tables—issues common to all relational databases.

We will explore common sharding strategies and scenarios, including vertical and horizontal table sharding, as well as vertical and horizontal database sharding.

1. Table Partitioning

1.1 Vertical Table Partitioning

Vertical partitioning (also called column‑based splitting) separates a table into a main table and one or more extension tables based on column activity, length, or usage.

Features:

Each table has a different schema.

Data stored in each table is distinct.

A common key (usually primary or foreign) links the tables.

The union of all tables represents the original table’s full data.

Scenarios:

Hot columns that update frequently (e.g., a balance field) are moved to a separate table to avoid heavy row locks.

Large columns such as TEXT that consume significant storage are isolated to reduce I/O.

Clear business separation or redundant fields justify splitting for future extensibility.

2. Horizontal Table Partitioning

Horizontal partitioning (also called row‑based splitting) divides a table by rows, typically using a column’s value range or hash.

Example: a phone number table can be split by the first two or three digits (e.g., 131, 132, 133 → phone_131, phone_132, phone_133). Queries determine the target table by extracting the prefix.

Features:

All tables share the same schema.

Each table holds a distinct subset of rows with no overlap.

The union of all tables equals the original table’s full data.

Scenarios: When a single table’s size or growth rate degrades query performance and increases CPU load, horizontal partitioning should be applied early.

3. Database Partitioning

Note that traditional database clustering (master‑slave replication) differs from sharding. Clustering replicates a single database to multiple nodes for read/write separation, while sharding splits the master database into multiple independent databases.

3.1 Vertical Database Partitioning

Vertical sharding separates databases based on business modules or shared services (e.g., authentication, single sign‑on). Each resulting database contains distinct tables and operates independently.

Features:

Each database contains different tables.

Data across databases does not overlap.

Databases are relatively independent, enabling modularization.

Scenarios: When distinct business modules can be isolated (e.g., dictionaries, configuration data) or when a dedicated server is desired for a specific workload.

4. Horizontal Database Partitioning

Horizontal sharding distributes rows across multiple databases based on a key value. Although it can alleviate storage pressure, it introduces complexity for backend development and is generally not recommended unless necessary.

Features:

All databases share the same schema.

Each database holds a unique subset of rows with no overlap.

The union of all databases represents the full dataset.

Scenarios: When system concurrency spikes, CPU and memory become bottlenecks, and table‑level partitioning is insufficient or lacks clear business boundaries for vertical sharding.

Conclusion

Before choosing a sharding strategy, consider alternatives such as caching, read/write separation, and SQL optimization, which are often cheaper and more direct solutions. Remember that altering tables changes the fundamental data structure and can introduce legacy issues; use sharding judiciously in large projects.