A Detailed Explanation of Database Sharding and Partitioning Strategies

A Detailed Explanation of Database Sharding and Partitioning Strategies

Problem Description
Database sharding and partitioning are core solutions for massive data storage and handling high-concurrency access pressure. When a single table's data volume exceeds tens of millions of records or database server performance reaches its bottleneck, it is necessary to disperse data across multiple databases or tables through sharding and partitioning. This topic will systematically explain the common strategies, applicable scenarios, and implementation details of database sharding and partitioning.

I. Why Sharding and Partitioning are Needed?

  1. Performance Bottleneck
    • Excessive data volume in a single table leads to degraded query performance (increased depth of the B+ tree).
    • High-concurrency write operations cause intense lock contention (e.g., InnoDB row locks escalating to table locks).
  2. Resource Limitations
    • Single-server disk capacity cannot accommodate continuously growing data.
    • The number of database connections is limited by hardware and cannot support a large number of concurrent requests.

II. Core Strategies for Sharding and Partitioning
1. Vertical Partitioning

  • Scenario: A table contains many infrequently used or large space-consuming fields (e.g., TEXT type).
  • Method: Split large fields into extension tables, keeping frequently accessed fields in the main table.
  • Example:
    Original user table: user (id, name, age, avatar, personal_info)
    After partitioning:
    user_base (id, name, age)
    user_ext (id, avatar, personal_info)
  • Advantages: Avoids I/O waste when reading large fields.
  • Disadvantages: Requires JOIN queries to retrieve complete data.

2. Horizontal Partitioning (Sharding)

  • Scenario: The data volume of a single table is too large, but the field structure does not need adjustment.
  • Method: Disperse data into multiple tables with the same structure according to specific rules.
  • Common Sharding Rules:
    • Range-based Sharding: Partition by time range (e.g., monthly tables) or ID range.
      Example: order_202301, order_202302
    • Hash-based Sharding: Use modulo operation on the shard key (e.g., user_id % 64).
      Example: user_00 ~ user_63
    • Consistent Hashing: Avoids massive data migration during scaling operations.

3. Database Sharding Strategies

  • Vertical Database Sharding: Split by business modules (e.g., order database, user database).
  • Horizontal Database Sharding: Distribute data from the same business to multiple database instances.

III. Principles for Choosing a Shard Key

  1. High-frequency Query Conditions: For example, if order queries are typically based on user_id, then user_id should be the shard key.
  2. Data Uniformity: Avoid data skew (e.g., sharding by gender may lead to uneven distribution).
  3. Cross-shard Queries: Try to ensure business queries target a single shard to minimize distributed transactions.

IV. Challenges and Solutions Brought by Sharding and Partitioning

  1. Globally Unique ID Generation

    • Snowflake Algorithm: Combines timestamp, machine ID, and sequence number to generate distributed IDs.
    • Database Segment Mode: Pre-allocate ID ranges to reduce database write pressure.

  2. Cross-shard Queries

    • Solution 1: Business-layer aggregation (query each shard separately and merge results).
    • Solution 2: Use middleware (e.g., ShardingSphere) for automatic routing.

  3. Distributed Transactions

    • Flexible Transactions: Use eventual consistency solutions (e.g., TCC, SAGA).
    • Strong Consistency: Use the two-phase commit (2PC) protocol based on XA.

V. Practical Case: E-commerce Order Table Sharding Design

  1. Requirements Analysis
    • Daily order volume exceeds 100,000; support queries by user ID and order time is required.
  2. Sharding Scheme
    • Number of databases: 4 physical databases (to reduce single-server disk pressure).
    • Number of tables per database: 16 tables (64 tables in total).
    • Shard key: order_id (generated by Snowflake algorithm).
    • Routing rule: order_id % 64 determines the specific table location.
  3. Query Optimization
    • User order queries: Maintain a mapping table between user ID and order ID synchronously.
    • Time-range queries: Use middleware to query all shards in parallel and aggregate results.

VI. Summary
The essence of sharding and partitioning is to trade space for time. Through reasonable sharding strategies and architectural design, system scalability can be effectively improved. Practical implementation requires selecting a sharding scheme based on business characteristics and addressing engineering issues such as data migration and monitoring alerts.