Senior LevelSQL & MySQL
How do you select an optimal Shard Key to prevent hotspotting in distributed database sharding?
❓ Interview Prompt: Explain Shard Key Selection rules, Hash-based vs Range-based Sharding, Scatter-Gather query penalties, and multi-tenant isolation.
Selecting a poor Shard Key causes uneven data distribution (Hotspotting) and forces queries to execute expensive Scatter-Gather operations across all shards:
Selection Guidelines:
1
High Cardinality: Choose keys with millions of unique values (e.g. 'user_id' or 'tenant_id').
2
Equal Query Distribution: Ensure reads/writes hit all shards evenly.
3
Include Shard Key in Queries: Co-locate queries to a single target shard ('WHERE user_id = 10'), avoiding cross-shard scatter-gather queries!
Code Implementation Example
sql
-- Query including Shard Key (user_id) executes on 1 target shard only (Fast!)
SELECT * FROM orders WHERE user_id = 402 AND order_date >= '2026-01-01';
-- Query omitting Shard Key triggers expensive Scatter-Gather across ALL shards!
-- SELECT * FROM orders WHERE order_date = '2026-01-15';💡Executing targeted single-shard queries by including the Shard Key.
🎯 Interview Key Takeaway
Select high-cardinality shard keys and include the shard key in all frequent query filters to avoid scatter-gather overhead.
#SQL#Sharding#Shard Key#Distributed DB#Senior
Back to all SQL & MySQL Questions→