Sharding Concepts — Complete Guide
Sharding Concepts — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of MySQL Tutorial on Toolliyo Academy.
On this page
MySQL Tutorial · Lesson 78 of 100
Sharding Concepts
Basics ✓ → Advanced
Advanced · 2 — Production · ~10 min · MySQL — Advanced MySQL
What is this?
Sharding splits data across multiple MySQL instances by shard key (user_id mod N, tenant_id range). MySQL has no built-in auto-shard — apps or Vitess/ProxySQL route queries.
Why should you care?
Single DataFlow instance caps at terabyte and write QPS — sharding by tenant_id scales SaaS horizontally.
See it live — copy this example
Run in MySQL Workbench or the mysql CLI.
-- App chooses shard by tenant_id (conceptual):
-- shard = tenant_id % 4 → db shard_2
USE dataflow_shard_2;
SELECT COUNT(*) FROM orders WHERE tenant_id = 42;What happened?
- All orders for tenant 42 live on one shard determined by hash/mod.
- Cross-shard JOIN is expensive — design aggregates per shard or use federated query layer.
Practice next
- Paper design: shard key = tenant_id, 4 shards.
- List queries that become cross-shard (global admin search).
- Study Vitess or application router patterns.
- Reshard simulation: change mod 4 to mod 8 migration story.
- Compare tenant-per-database vs mod sharding.
Remember
Shard = horizontal split across servers. Shard key must match access pattern. Cross-shard queries are hard — plan upfront.
DataFlow SaaS scale path
At 2TB, team shards by tenant_id mod 8 — each shard own RDS instance.
Outcome: Write QPS scales linearly with shard count.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
