Yaushe kake juyawa zuwa partitioning maimakon sharding don scale MySQL?

Question

IT Interview Practice · Accepted Answer

Dukansu suna raba babban tebur, amma a matakai daban-daban. **Partitioning** yana raba tebur ɗaya a kan files da yawa **a cikin server MySQL ɗaya**. **Sharding** yana raba bayanai a kan **servers da yawa masu 'yancin kai**. Partitioning yana taimakawa tebur da ya yi girma da yawa ga file ɗaya; sharding yana taimakawa aikin da ya yi girma da yawa ga inji ɗaya.

## Partitioning (server ɗaya)

```sql
CREATE TABLE events (
  id BIGINT, created_at DATETIME, payload JSON,
  PRIMARY KEY (id, created_at)          -- partition key must be in every unique key
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027)
);
-- Queries filtering on created_at hit ONE partition (partition pruning);
-- dropping old data is an instant ALTER TABLE ... DROP PARTITION p2024.
```

Kyakkyawa ga time-series da archival mai sauƙi, amma har yanzu server ɗaya ne — babu ƙarin jimlar CPU, RAM, ko write throughput.

## Sharding (servers da yawa)

Zaɓi **shard key** (misali `user_id`) kuma tura kowace row zuwa shard ta hash ko range:

```
shard = hash(user_id) % N
  user 42 -> shard 2 (server B)     user 99 -> shard 0 (server A)
```

- **Fa'idodi** — scaling kusan linear na writes, adanawa, da connections.
- **Rashin dacewa** — JOINs da transactions tsakanin shards suna da wahala ko ba zai yiwu ba; mummunan shard key yana haifar da hotspots; **resharding** lokacin da ka ƙara servers yana da wahala. Zaɓi key mai high-cardinality, mai daidaita rabo kuma ka guji queries da dole su fan out zuwa dukan shards.

## Tsarin ayyuka

Gaji nasarorin masu arha da farko: indexing, query tuning, read replicas, caching, sannan partitioning. Juya zuwa sharding kawai lokacin da primary ɗaya ba zai iya ɗauka **write** volume ba — shine tsalle mafi girma a cikin sarkakiyar ayyuka.

## Me ya sa yake da muhimmanci

Shawarwarin scaling galibi ba za a iya juyawa ba kuma masu tsada ne. Sanin cewa partitioning kayan aikin tsari ne na server-ɗaya yayin da sharding alkawari ne na distributed-systems — tare da shard-key da farashin tsakanin-shard — yana hana ƙungiyoyi sharding da wuri ko fatan partitioning zai warware iyakar write-throughput da ba zai iya ba.

Partitioning (server ɗaya)

Sharding (servers da yawa)

Tsarin ayyuka

Me ya sa yake da muhimmanci

Tambayoyi masu alaƙa