Designing MySQL for Millions of QPS: From Single Server to Distributed Architecture

Problem Origin: Single‑Server MySQL Limits

When order traffic peaks at 300,000 QPS, response time jumps from 50 ms to 2 s, lock contention rises, and the primary CPU is saturated, revealing that the system has outgrown its single‑node design.

Index Mis‑design Causes Back‑Table Lookups

A typical query in the order system looks like:

SELECT order_id, user_id, total_amount, status
FROM orders
WHERE user_id = 10086 AND status = 'PAID'
ORDER BY create_time DESC
LIMIT 20;

Even with a simple index idx_user_status(user_id, status), MySQL must still read the clustered primary key row to fetch total_amount and create_time, causing random I/O and CPU context switches under high concurrency. The Using index condition hint only reduces some back‑table work; it does not replace a covering index.

A better approach is to build an index that matches the query pattern:

ALTER TABLE orders
ADD INDEX idx_user_status_ctime_amount (user_id, status, create_time, order_id, total_amount);

Trade‑offs: larger index size, higher write cost, and the index should be limited to high‑frequency, latency‑sensitive queries.

Frequent Deadlocks in Stock Deduction

BEGIN;
SELECT stock FROM inventory WHERE product_id = 111 FOR UPDATE;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 111;
COMMIT;

The deadlock root causes are:

Missing index on product_id makes the lock span many rows.

Inconsistent lock order when a transaction touches multiple products.

RR isolation creates gap locks that make inserts and updates interfere.

Effective fixes include:

Ensure the hot‑spot table has an index on product_id.

Enforce a fixed lock acquisition order for multi‑item updates.

Use optimistic locking for idempotent operations.

First Evolution: Fix the Single‑Node MySQL

Before scaling out, correct the query model and index design, and resolve transaction‑level hotspots. This eliminates the biggest performance‑killer without adding more nodes.

Second Evolution: Read‑Write Splitting

When the primary cannot handle read traffic, introduce a read‑write split. It offloads list, report, and near‑real‑time queries to replicas, but introduces consistency challenges such as "write‑then‑read" anomalies.

When to Apply Read‑Write Splitting

Master CPU is high and most traffic is read‑only → suitable.

Hotspot writes and deadlocks dominate → not suitable.

Business requires strong read‑after‑write consistency → must keep those reads on master.

Third Evolution: Vertical Sharding

Separate order, user, product, and inventory tables into different databases. Benefits:

Isolation of resource contention between business domains.

Failure or slow queries in one domain no longer affect others.

However, the order table still grows, and hot‑spot contention remains, so vertical sharding alone is insufficient for long‑term growth.

Fourth Evolution: Horizontal Sharding

When a single order table reaches tens of millions of rows, split it by both database and table:

Shard by user_id to keep a user's orders in the same database.

Shard by order_id (generated by Snowflake) to distribute rows across tables.

ShardingSphere‑JDBC configuration example:

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds0-slave0,ds0-slave1,ds1-slave0,ds1-slave1
    rules:
      readwrite-splitting:
        ds0_rw:
          write-data-source-name: ds0
          read-data-source-names: [ds0-slave0, ds0-slave1]
          load-balancer-name: round_robin
        ds1_rw:
          write-data-source-name: ds1
          read-data-source-names: [ds1-slave0, ds1-slave1]
          load-balancer-name: round_robin
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.orders_$->{0..15}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: table-inline
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds${user_id%2}
          table-inline:
            type: INLINE
            props:
              algorithm-expression: orders_${order_id%16}

Consequences:

Routing keys constrain API design – queries must carry user_id to hit the correct shard.

Cross‑shard transactions become expensive; they require either a distributed‑transaction framework (e.g., Seata) or a redesign to async or pre‑allocation models.

Global transactions add rollback complexity, longer latency, and higher observability requirements.

Data Pipeline: Canal + Kafka + Elasticsearch

After the database handles the transactional truth, downstream services consume binlog changes via Canal, push them to Kafka, and update search indexes, caches, or data warehouses.

@KafkaListener(topics = "order_binlog", groupId = "order-sync")
public void onMessage(String message) {
    CanalMessage canalMessage = JSON.parseObject(message, CanalMessage.class);
    if (canalMessage.isDdl()) return;
    for (CanalEntry entry : canalMessage.getEntries()) {
        if (entry.getEntryType() != EntryType.ROWDATA) continue;
        RowChange rowChange = RowChange.parseFrom(entry.getStoreValue());
        for (RowData rowData : rowChange.getRowDatasList()) {
            if (rowChange.getEventType() == EventType.INSERT) {
                esClient.index(buildEsDoc(rowData.getAfterColumnsList()));
                redis.set("order:" + orderId, JSON.toJSONString(order), 3600);
            }
        }
    }
}

Key challenges addressed:

Idempotency : Kafka retries, consumer restarts, and ES retries may cause duplicate processing.

Ordering : Updates for the same order must be applied in sequence.

Latency : Downstream views are near‑real‑time, not transaction‑level immediate.

Production Checklist

Create covering indexes that match core query patterns.

Verify hot‑spot updates hit the correct indexes.

Define a whitelist of operations that must read from the master.

Limit deep pagination and cross‑shard sorting.

Design binlog consumers for idempotency, ordering, and bounded latency.

Practice failure scenarios: large‑table DDL, replication lag, connection‑pool exhaustion, deadlock reproduction.