How to Achieve MySQL Sub‑Second Scaling for 100M‑10B Rows (Interview Scenario)

Background

When a business grows from millions to hundreds of millions of users, a single MySQL instance cannot handle the load: disk fills, memory runs out, connections exhaust, and replication lag spikes. Horizontal sharding (splitting by uid % N) is required to distribute data and traffic across multiple instances.

Solution 1: Stop‑Service Migration (Traditional)

Scenario

During a traffic surge, the order table reaches 100 M rows, queries become slow, and the single‑shard architecture fails.

Steps

Plan a short maintenance window and stop the service.

Migrate data from the original two shards ( uid % 2) to four new shards ( uid % 4) using a Shell script that extracts data with SELECT * FROM order_info WHERE uid%2=0 and inserts into the appropriate new database.

Update the routing configuration to use uid % 4 and restart the service.

Verify that the migration succeeded and that the application can read/write correctly.

#!/bin/bash
# Example migration script
OLD_DB_0="jdbc:mysql://192.168.1.10:3306/order_db_0?useSSL=false"
... # omitted for brevity
mysql -u$OLD_USER -p$OLD_PWD -h192.168.1.10 -e "SELECT * FROM order_info WHERE uid%2=0" | mysql -u$NEW_USER -p$NEW_PWD -h192.168.1.20 -Dorder_db_0
...

Key point: Data migration alone does not make the system available; the service must be down.

Solution 2: Double‑Write Migration (No Downtime)

Scenario

An online education platform cannot afford any downtime; traffic must remain 24/7.

Process

Configure two virtual IPs (VIP0, VIP1) for the existing shards.

Introduce two new shards (DB2, DB3) and set up bidirectional replication between old and new shards.

Implement double‑write in the application: every new order is written to both the old shard ( uid % 2) and the new shard ( uid % 4).

Run a background task to gradually sync historical data from old to new shards, marking migrated rows with a sync_status flag.

Perform a gray‑scale read‑traffic switch using a configuration flag (e.g., system.maintenance) to route a small percentage of reads to the new shards, then increase to 100 %.

After verification, disable writes to the old shards and decommission them.

@Service
public class OrderService {
    @Value("${system.maintenance:false}")
    private boolean maintenance;
    @GetMapping("/detail")
    public Result<OrderDetail> getDetail(Long orderId) {
        if (maintenance) {
            return Result.fail("系统升级中，预计2小时后恢复");
        }
        return Result.success(orderService.getDetail(orderId));
    }
}

@Transactional
public Long saveOrder(OrderDTO dto) {
    OrderPO po = convert(dto);
    // write to old shard
    DataSourceContextHolder.setDbIndex(dto.getUid() % 2);
    oldMapper.insert(po);
    // write to new shard
    DataSourceContextHolder.setDbIndex(dto.getUid() % 4);
    newMapper.insert(po);
    return po.getId();
}

Risks: Inconsistent writes if one side fails; requires asynchronous compensation (log, retry, alert).

Solution 3: ShardingSphere Middleware Expansion

Scenario

When the order table reaches 1.2 B rows and QPS exceeds 100 k, scaling the hardware is costly and manual sharding is error‑prone.

Approach

Add two new physical databases (order_db_2, order_db_3) with identical schema.

Configure ShardingSphere to manage four shards and enable dual‑write so new writes are automatically routed to both old ( uid % 2) and new ( uid % 4) shards.

Use the ShardingSphere‑Migration tool to move existing rows that belong to the new shards ( uid % 4 = 2 or 3) in batches (e.g., 10 k rows per batch) with MD5 consistency checks.

Validate data consistency (row count, shard key distribution, content checksum) before switching routing rules to uid % 4.

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.3.2</version>
</dependency>

spring:
  shardingsphere:
    datasource:
      names: order_db_0,order_db_1,order_db_2,order_db_3
      order_db_0:
        url: jdbc:mysql://192.168.1.10:3306/order_db_0
        username: root
        password: 123456
      ...
    rules:
      sharding:
        tables:
          order_info:
            actual-data-nodes: order_db_${0..3}.order_info
            database-strategy:
              standard:
                sharding-column: uid
                sharding-algorithm-name: main_db_inline
            duplicate-data-sources:
              strategy: STANDARD
              sharding-algorithm-name: duplicate_db_inline
        sharding-algorithms:
          main_db_inline:
            type: INLINE
            props:
              algorithm-expression: order_db_${uid % 2}
          duplicate_db_inline:
            type: INLINE
            props:
              algorithm-expression: order_db_${uid % 4}
    props:
      duplicate-data-source.enabled: true
      duplicate-data-source.async: true

After migration and verification, the routing rule is changed to uid % 4, achieving seamless scaling without code changes.

Solution 4: Dual‑VIP + Dynamic Routing (Zero‑Second Expansion)

Concept

Decouple application access from physical DB IPs by using virtual IPs (VIP). Each old shard gets a second VIP (dual‑VIP). New shards are introduced, and bidirectional replication keeps data consistent.

Five‑Step Procedure

Initial state: Two shards, each accessed via a single VIP (VIP0, VIP1).

Add second VIPs: Bind an extra IP (VIP00, VIP11) to each old shard, creating “one DB, two entry points”.

Deploy new shards (DB2, DB3) and enable dual‑master sync: Old shard 0 ↔ new shard 2, old shard 1 ↔ new shard 3.

Update routing to uid % 4 : Application now calculates four possible VIPs, but the new VIPs still point to the old shards, so traffic distribution becomes logical only.

Switch VIP bindings: Move VIP00 to DB2 and VIP11 to DB3 (using ifconfig or orchestration scripts). After the switch, traffic for uid%4=2 and 3 reaches the new physical databases.

# Bind new VIP to new shard 2
ssh [email protected] "ifconfig eth0:0 192.168.1.202 netmask 255.255.255.0 up"
# Unbind old VIP00
ssh [email protected] "ifconfig eth0:0 down"

Because the application never changes its connection string, the switch occurs in seconds, with no TCP reset, no transaction loss, and full gray‑scale control (e.g., 1 % → 10 % → 100 %).

Common Pitfalls & Best Practices

Data consistency: Always verify that old and new shards contain identical rows (MD5, record count, shard key range) before cutting over.

Double‑write consistency: Log failures, retry asynchronously, and monitor for missing rows.

Shard key selection: Use high‑cardinality, uniformly accessed columns (e.g., uid, order_id) and avoid low‑cardinality fields like status or province.

Gradual traffic shift: Use configuration‑driven gray‑release (1 % → 10 % → 50 % → 100 %) and watch QPS, latency, and error rates.

Conclusion

MySQL sub‑second scaling for massive tables can be achieved through four progressively sophisticated methods. The stop‑service approach is simple but incurs downtime; double‑write offers zero downtime at the cost of consistency complexity; ShardingSphere provides middleware‑level transparency; and dual‑VIP routing delivers true zero‑second, zero‑impact expansion suitable for high‑traffic “big‑promotion” scenarios.