Understanding Database Sharding: Partitioning Strategies and When to Use Them

Hello, I am a top architect.

Remember, if someone asks you what the most effective way to optimize a database is, the answer is SQL optimization, distributed clusters, sharding! But jumping straight to sharding may not always be appropriate; you need to understand what sharding is, when to use it, and the different methods. Don't overthink it—just hop on board! The following outlines various sharding techniques.

First, we need to know what sharding (分库) and table partitioning (分表) are. In this article, MySQL is used as the reference.

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

Table partitioning (分表): Splitting a single table into multiple tables.

For large internet projects, daily data growth can reach tens of millions, making a single MySQL server unrealistic. Even the strongest MySQL instance cannot handle the load alone.

As data volume and QPS increase, database pressure grows, and a single‑node database quickly hits storage and concurrency limits, so performance optimization is required. Sharding follows a "divide and conquer" strategy: sharding reduces storage pressure and improves scalability, while table partitioning addresses query bottlenecks caused by oversized tables.

Now we discuss common sharding and partitioning strategies and their scenarios.

1. Table Partitioning

1.1 Vertical Partitioning (垂直分表)

Vertical partitioning (also called "vertical slicing") separates columns based on activity or length, moving less‑used or large columns to an auxiliary table.

Features:

Each table has a different structure.

Data in each table is different.

Tables are linked by a common key (usually primary or foreign key) called the "brother table".

The union of all brother tables represents the full data set.

Scenarios:

Hot columns with high update frequency need to be isolated to avoid InnoDB row‑lock contention (e.g., a balance field).

Large text fields that cause storage pressure.

Clear business separation or redundant fields that warrant splitting for future extensibility.

1.2 Horizontal Partitioning (水平分表)

Horizontal partitioning (also called "horizontal slicing") splits data rows based on a column value, such as the first two or three digits of a phone number.

Example: 131, 132, 133 → phone_131, phone_132, phone_133 . Queries determine the target table by extracting the prefix and dynamically constructing the table name.

Features:

All tables share the same structure.

Data in each table is distinct with no overlap.

The union of all tables equals the full data set.

Scenario: When a single table becomes too large or its growth rate threatens query performance, horizontal partitioning should be applied early.

2. Database Sharding

2.1 Vertical Sharding (垂直分库)

Vertical sharding separates databases based on business modules or shared services (e.g., authentication, configuration). Each database contains different tables.

Features:

Each database has a different set of tables.

Data across databases does not overlap.

Databases are relatively independent, supporting modularization.

Scenario: When distinct business modules can be isolated or when a dedicated server is needed for specific workloads.

2.2 Horizontal Sharding (水平分库)

Horizontal sharding splits a database into multiple databases based on row data, similar to horizontal table partitioning but at the database level. It is rarely recommended because it adds complexity for backend developers.

Features:

All databases share the same schema.

Data in each database is distinct with no overlap.

The union of all databases represents the complete data set.

Scenario: When overall concurrency spikes, CPU and memory pressure become critical, and neither table partitioning nor vertical sharding can adequately address storage saturation.

Conclusion

Before choosing a sharding strategy, consider simpler optimizations such as caching, read/write splitting, and SQL tuning, as they are often cheaper and more direct solutions.

Altering tables changes the foundation; you never know how many legacy issues it may trigger. If you encounter scaling problems in a large project, propose sharding carefully.