Scaling MySQL for High Concurrency: Caching, Read/Write Splitting, Archiving, and Sharding Strategies

Relational database transaction features guarantee data consistency, but a single MySQL instance cannot handle millions of users and tens of thousands of QPS.

Solution 1: Cache (MySQL + Redis) – Redis is an in‑memory key‑value store with simple data structures that provides high performance by serving most read requests before they reach MySQL. Because Redis stores data in volatile memory, it sacrifices durability, which makes it ideal as a front‑cache but also introduces cache‑related problems such as penetration, avalanche, hot keys, low hit rate, and data inconsistency.

Solution 2: Read‑Write Splitting – Deploy multiple MySQL instances: a master for writes and one or more slaves for reads. The master asynchronously replicates changes to the slaves, creating a small replication lag that can cause temporary inconsistency (e.g., an order still appears as “pending payment” on a slave). Implementation can be as simple as configuring master/slave data sources and routing read/write SQL via DAO changes or Spring’s AbstractRoutingDataSource .

Solution 3: Data Archiving – Move stale historical data (e.g., old orders) to separate archive tables to reduce the size of the primary tables. This improves query performance and storage efficiency. Deleting rows in InnoDB does not immediately free disk space; running OPTIMIZE TABLE rebuilds the table, releases space, and tightens indexes, but it locks the table during execution.

Solution 4: Sharding (Database/Table Partitioning) – Split large tables into many identical shards using a sharding key (hash modulo, range, or lookup table). Sharding reduces the amount of data each query scans and distributes load across multiple database instances, addressing both data‑size and high‑concurrency challenges. Common tools include Sharding‑Sphere (formerly Sharding‑JDBC), TDDL (Taobao Distributed Data Layer), and Mycat.

Warnings: Read‑write splitting may cause data inconsistency due to replication delay; sharding introduces complexity and may require distributed transaction solutions, increasing operational cost.

References and further reading links are provided throughout the article.