MySQL Database Sharding: Vertical vs Horizontal Partitioning
Database Sharding Theory
Background
Internet systems need to handle massive user concurrent requests and data storage.
Challenges
- Exponential data growth
- Long-term preservation of historical data
- High-concurrency reads and writes
- Millisecond-level query latency
Solutions
- NoSQL databases
- NewSQL databases
- Database sharding
Vertical Partitioning
Applicable Scenarios
A single database contains too many tables
Partitioning Principle
Classify tables into different databases by business domain
Vertical Sharding
Split user tables and order tables into different databases
Vertical Table Splitting
Split infrequently used fields from one table into another table
Advantages
- Clear business decoupling
- Strong data maintainability
- Improved query performance
- Optimized cache utilization
Disadvantages
- Primary key redundancy management
- Cross-table join issues
- Single table capacity limitations
- Transaction processing difficulties
Horizontal Partitioning
Applicable Scenarios
Single table data volume is too large (e.g., exceeding 50 million records)
Partitioning Methods
- Range-based: Partition by user ID ranges
- Hash-based: Modulo operation on order ID
- Time-based: Monthly table splitting
Advantages
- Database shares computing pressure
- Solves single database performance bottlenecks
- Controllable application modification cost
- Improved system scalability
Disadvantages
- Complex sharding rule design
- Poor cross-shard query performance
- Distributed transaction challenges
- High scaling and maintenance costs