Principles and Practices of MySQL Database Sharding

Zhou Yanwei, the Database Director at Qunar.com and chair of the China MySQL User Group, manages MySQL, Redis, and HBase platforms, designing architectures, tuning performance, and building automation tools such as the Qunar MySQL development standards, backup platform, and high‑availability designs.

He discusses why MySQL eventually requires sharding (splitting databases or tables) as data volume grows, noting that internal lock granularity limits QPS and that users often wait for vendor optimizations.

Principle 0: Avoid sharding if possible; first try hardware upgrades, read/write separation, and load balancing.

Principle 1: When data size hampers normal operations, such as backup I/O overload or long DDL locks, splitting reduces risk and downtime.

Principle 2: Vertically split tables with poorly designed schemas, isolating large or frequently accessed columns (e.g., user login timestamps or large text fields) to improve performance.

Principle 3: Horizontally split tables that experience unbounded growth (e.g., comments, logs, feeds) by partitioning on user, time, or purpose.

Principle 4: Improve availability by distributing data across multiple instances, preventing a single point of failure.

Principle 5: Reduce business coupling; isolate unrelated services (e.g., ticketing vs. food ordering) to avoid one service’s issues affecting another.

The article concludes with a reminder that sharding decisions are dynamic, often requiring merging later, and invites further discussion.