Understanding MySQL Sharding: Vertical/Horizontal Table and Database Partitioning Strategies

1. Table Partitioning (Sharding)

Table partitioning splits a single table into multiple tables (vertical) or multiple tables with identical structure (horizontal) to reduce storage pressure and improve query performance for large datasets.

Vertical Partitioning

Also called "column‑based splitting", it separates hot or large columns into an extension table while keeping a primary key for joining.

Each table has a different schema.

Data is stored separately; a common key (usually primary or foreign key) links the tables.

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

Typical scenarios include:

Hot fields with high update frequency (e.g., user balance) that cause row‑level lock contention.

Very large text/blob columns that increase I/O when using SELECT * .

Business domains with clear separation or redundant columns, allowing independent evolution.

Horizontal Partitioning

Also known as "row‑based splitting", it divides rows based on a column value (e.g., phone number prefix) into separate tables with identical schema.

All tables share the same structure.

Data sets are disjoint; each table holds a distinct subset of rows.

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

Use when a single table's size or growth rate degrades query performance and CPU load, and the partition key can be derived easily for routing queries.

2. Database Partitioning (Sharding)

Database sharding splits an entire database instance into multiple independent instances, unlike read‑write splitting or master‑slave replication which duplicate the same schema.

Vertical Database Sharding

Separate distinct business modules or shared services (e.g., authentication, dictionary data) into their own databases, allowing independent scaling and maintenance.

Each database contains different tables.

No data overlap between databases.

Modules become more isolated and modular.

Horizontal Database Sharding

Distribute rows of a single logical database across multiple physical databases based on a sharding key. Although rarely recommended due to operational complexity, it can be considered when overall concurrency and storage pressure exceed what vertical sharding can handle.

All databases share the same schema.

Data sets are disjoint; the union forms the complete dataset.

Useful for extreme scale scenarios where a single instance cannot sustain load.

Conclusion

Before choosing sharding, evaluate simpler optimizations such as caching, read‑write separation, and SQL tuning, as they often solve performance issues with lower cost. Remember that modifying tables or databases can introduce hidden legacy problems, so propose sharding only after thorough analysis.

动表就是动根本，你永远不知道这张表后面会连带多少历史遗留问题