Database Sharding Strategy and Data Migration Solution

Database Sharding Strategy and Data Migration Solution

Problem Description
In large-scale internet applications, when a single table's data volume reaches tens of millions or the database faces excessive concurrent pressure, database and table sharding are necessary to improve system performance and scalability. Please elaborate on common sharding strategies (such as horizontal table splitting, vertical database splitting, etc.) and design a safe and reliable data migration solution to ensure the availability of the original system and data consistency during the migration process.

Fundamental Concepts of Database Sharding

  1. Problem Background

    • Query performance degradation due to excessive data volume in a single table (e.g., increased B+ tree depth).
    • Database connections or I/O becoming bottlenecks in high-concurrency scenarios.
    • Hardware limitations of a single database instance (e.g., disk space, CPU processing power).

  2. Core Strategy Categories

    • Vertical Database Sharding: Split databases by business modules (e.g., user database, order database) to reduce pressure on a single database.
    • Vertical Table Splitting: Separate cold and hot fields of a wide table (e.g., storing frequently used fields and detailed fields in separate tables).
    • Horizontal Table Splitting: Distribute data from a single table into multiple tables with identical structures based on rules (e.g., splitting by user ID modulo).

Detailed Sharding Strategies
Step 1: Routing Rules for Horizontal Table Splitting

  • Hash Modulo: Table location = user_id % number of tables.
    • Advantages: Even data distribution.
    • Disadvantages: Data migration required during capacity expansion (consistent hashing can alleviate this).
  • Range Sharding: Split data by range (e.g., by time or ID intervals).
    • Advantages: Easy to expand (simply add new tables).
    • Disadvantages: Potential data skew (hotspot data concentration).
  • Geographic Sharding: Split by region or business unit (e.g., North China, South China databases).

Step 2: Combined Practice of Database and Table Sharding

  • First, perform vertical database splitting (separate by business), then horizontal table splitting (to address single-table data volume issues).
  • Example: An e-commerce system is divided into user database, order database, and product database. The order database is further split into 16 tables based on order ID hashing.

Data Migration Solution Design
Objective: Ensure zero downtime during migration, with no data loss and guaranteed consistency.

Step 1: Dual-Write Migration Method (Recommended Solution)

  1. Phase 1: Synchronous Dual-Write

    • The original database (old database) continues to serve. New data is written simultaneously to both the old database and the new sharded databases/tables.
    • Modify business code to send all write operations (insert, delete, update) to both old and new databases.
    • Enable a log verification tool to periodically compare data consistency between old and new databases.

  2. Phase 2: Historical Data Migration

    • Use data synchronization tools (e.g., DataX, Canal) to migrate existing data from the old database to the new database.
    • Record incremental logs (e.g., Binlog) during migration to fill data gaps from the migration period.

  3. Phase 3: Read Traffic Switch

    • Gradually shift read requests to the new database (e.g., start with 10% traffic for trial).
    • Verify the correctness and performance stability of read operations.

  4. Phase 4: Stop Writing to Old Database

    • After confirming the completeness of data in the new database, stop writing to the old database and fully switch to the new database.
    • Retain a backup of the old database for a period to enable rollback if needed.

Step 2: Key Considerations

  • Consistency Assurance: Ensure atomicity during dual-write (rollback on failure) or implement asynchronous retry and compensation mechanisms.
  • Gradual Rollout: Migrate gradually by user groups to reduce risks.
  • Rollback Plan: If the new database encounters issues, immediately switch back to the old database and repair data.

Conclusion
The core of database sharding lies in selecting appropriate sharding keys and routing strategies to avoid cross-shard queries. Data migration requires balancing availability and consistency through steps like dual-write, incremental synchronization, and gradual traffic switching. In practice, database middleware (e.g., ShardingSphere) can be used to simplify operations.