Ni lini unatumia partitioning dhidi ya sharding kuongeza (scale) MySQL?

Question

IT Interview Practice · Accepted Answer

Zote zinagawanya jedwali kubwa, lakini katika viwango tofauti. **Partitioning** hugawanya jedwali moja katika files kadhaa **ndani ya server moja ya MySQL**. **Sharding** hugawanya data katika **servers kadhaa huru**. Partitioning husaidia jedwali lililo kubwa mno kwa file moja; sharding husaidia workload iliyo kubwa mno kwa mashine moja.

## Partitioning (server moja)

```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.
```

Nzuri kwa time-series na uhifadhi rahisi wa archival, lakini bado ni server moja — hakuna CPU, RAM, au write throughput ya ziada.

## Sharding (servers nyingi)

Chagua **shard key** (mfano `user_id`) na elekeza kila row kwenye shard kwa hash au range:

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

- **Faida** — scaling karibu ya mstari ya writes, uhifadhi, na connections.
- **Hasara** — cross-shard JOINs na transactions ni ngumu au haziwezekani; shard key mbaya inaunda hotspots; **resharding** unapoongeza servers ni jambo lenye maumivu. Chagua key yenye cardinality kubwa, iliyosambaa sawasawa na epuka queries zinazolazimika kufika (fan out) kwenye shards zote.

## Mpangilio wa hatua

Maliza kwanza ushindi wa bei nafuu: indexing, query tuning, read replicas, caching, kisha partitioning. Tumia sharding tu wakati primary moja haiwezi tena kunyonya kiasi cha **write** — ndiyo hatua kubwa zaidi katika utata wa uendeshaji.

## Kwa nini ni muhimu

Maamuzi ya scaling kwa kiasi kikubwa hayawezi kutenduliwa na ni ghali. Kujua kwamba partitioning ni chombo cha kupanga cha server-moja wakati sharding ni ahadi ya distributed-systems — pamoja na gharama zake za shard-key na cross-shard — huzuia timu zisi-shard mapema mno au kutegemea partitioning kutatua ukomo wa write-throughput ambao haiwezi.

Partitioning (server moja)

Sharding (servers nyingi)

Mpangilio wa hatua

Kwa nini ni muhimu

Maswali yanayohusiana