Why and How to Partition MySQL Tables: Sharding, Merge Engine, and Scaling Strategies

Why Partition Tables

When a single table reaches tens of millions of rows, query time increases and joint queries may become untenable. Partitioning reduces database load and shortens query time.

MySQL uses table locks and row locks to ensure data integrity; table locks block all operations on the table, while row locks block only the specific row.

MySQL Proxy: Amoeba

Amoeba enables MySQL clustering. From the Java application perspective, the master‑slave topology is transparent; configuration is handled by Amoeba.

Splitting Large, Frequently Accessed Tables

For tables with predictable massive data (e.g., a company table with up to 50 million rows), pre‑create N tables based on capacity. If each table holds 5 million rows, ten tables are needed. Before inserting, count rows; if under the threshold, insert, otherwise create or use the next table.

Using the MERGE Storage Engine for Partitioning

Retrofitting an existing large table is painful because existing SQL must be rewritten. The MERGE engine allows logical partitioning without code changes.

Database Architecture

Simple MySQL Master‑Slave Replication

Master‑slave replication separates reads from writes, improving read performance.

However, replication introduces performance bottlenecks:

Write scalability is limited

Writes cannot be cached

Replication lag

Increased table lock rates

Large tables reduce cache efficiency

MySQL Vertical Partitioning

When business modules are sufficiently independent, placing each module's data on separate database servers improves fault isolation and load distribution.

Vertical partitioning cannot address inter‑module data (e.g., user tables) or the growth of a single massive table, prompting the need for horizontal sharding.

MySQL Horizontal Sharding (Sharding)

Users are grouped by a rule (e.g., hash of user ID) and stored in separate shards. Adding servers scales the system. The diagram below illustrates the concept.

To locate a user's shard, maintain a mapping table of user IDs to shard IDs; queries first consult this table, then access the appropriate shard.

Original source: http://www.francissoung.com/2015/10/12/Mysql%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E6%96%B9%E6%A1%88/#section