Back to Details

database-sharding

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

database-sharding

数据库分片

Comprehensive database sharding patterns for horizontal scaling with hash, range, and directory-based strategies.
支持哈希、范围、目录三种策略的全量数据库分片模式，用于实现水平扩展。

Quick Start (10 Minutes)

快速入门（10分钟）

Step 1: Choose sharding strategy from templates:
bash
undefined
步骤1：从模板中选择分片策略：
bash
undefined

Hash-based (even distribution)

基于哈希（分布均匀）

cat templates/hash-router.ts
cat templates/hash-router.ts

Range-based (time-series data)

基于范围（适用于时序数据）

cat templates/range-router.ts
cat templates/range-router.ts

Directory-based (multi-tenancy)

基于目录（适用于多租户场景）

cat templates/directory-router.ts

**Step 2**: Select shard key criteria:
- ✅ **High cardinality** (millions of unique values)
- ✅ **Even distribution** (no single value > 5%)
- ✅ **Immutable** (never changes)
- ✅ **Query alignment** (in 80%+ of WHERE clauses)

**Step 3**: Implement router:
```typescript
import { HashRouter } from './hash-router';

const router = new HashRouter([
  { id: 'shard_0', connection: { host: 'db0.example.com' } },
  { id: 'shard_1', connection: { host: 'db1.example.com' } },
  { id: 'shard_2', connection: { host: 'db2.example.com' } },
  { id: 'shard_3', connection: { host: 'db3.example.com' } },
]);

// Query single shard
const user = await router.query('user_123', 'SELECT * FROM users WHERE id = $1', ['user_123']);
cat templates/directory-router.ts

**步骤2**：遵循分片键选择标准：
- ✅ **高基数**（数百万个唯一值）
- ✅ **分布均匀**（无单个值占比超过5%）
- ✅ **不可变更**（永远不会修改）
- ✅ **查询对齐**（出现在80%以上的WHERE子句中）

**步骤3**：实现路由逻辑：
```typescript
import { HashRouter } from './hash-router';

const router = new HashRouter([
  { id: 'shard_0', connection: { host: 'db0.example.com' } },
  { id: 'shard_1', connection: { host: 'db1.example.com' } },
  { id: 'shard_2', connection: { host: 'db2.example.com' } },
  { id: 'shard_3', connection: { host: 'db3.example.com' } },
]);

// 查询单个分片
const user = await router.query('user_123', 'SELECT * FROM users WHERE id = $1', ['user_123']);

Critical Rules

核心规则

✓ Always Do

✓ 必须遵守

RuleReason
Include shard key in queriesAvoid scanning all shards (100x slower)
Monitor shard distributionDetect hotspots before they cause outages
Plan for rebalancing upfrontCannot easily add shards later
Choose immutable shard keyChanging key = data migration nightmare
Test distribution with production dataSynthetic data hides real hotspots
Denormalize for data localityKeep related data on same shard
规则原因
查询中携带分片键避免全分片扫描（速度慢100倍）
监控分片分布情况在热点引发故障前提前发现问题
提前规划重平衡方案后续新增分片难度极高
选择不可变更的分片键修改分片键会引发数据迁移灾难
用生产数据测试分布情况模拟数据会掩盖真实热点问题
通过非规范化保证数据局部性将关联数据存储在同一个分片

✗ Never Do

✗ 禁止操作

Anti-PatternWhy It's Bad
Sequential ID with range shardingLatest shard gets all writes (hotspot)
Timestamp as shard keyRecent shard overwhelmed
Cross-shard transactions without 2PCData corruption, inconsistency
Simple modulo without consistent hashingCannot add shards without full re-shard
Nullable shard keySpecial NULL handling creates hotspots
No shard routing layerHardcoded shards = cannot rebalance
反模式危害
范围分片搭配顺序ID最新分片承接所有写入请求（热点）
用时间戳作为分片键近期数据所在分片会被压垮
不使用2PC执行跨分片事务会导致数据损坏、不一致
不使用一致性哈希的简单取模新增分片时需要全量重新分片
分片键允许为空NULL值的特殊处理会引发热点
没有分片路由层硬编码分片规则导致无法重平衡

Top 7 Critical Errors

7个最常见的严重错误

Error 1: Wrong Shard Key Choice (Hotspots)

错误1：分片键选择错误（热点问题）

Symptom: One shard receives 80%+ of traffic Fix:
typescript
// ❌ Bad: Low cardinality (status field)
shard_key = order.status; // 90% are 'pending' → shard_0 overloaded

// ✅ Good: High cardinality (user_id)
shard_key = order.user_id; // Millions of users, even distribution
症状：单个分片承接80%以上的流量 修复方案：
typescript
// ❌ 错误：低基数（状态字段）
shard_key = order.status; // 90%的记录都是'pending' → shard_0过载

// ✅ 正确：高基数（user_id）
shard_key = order.user_id; // 数百万用户，分布均匀

Error 2: Missing Shard Key in Queries

错误2：查询中未携带分片键

Symptom: Queries scan ALL shards (extremely slow) Fix:
typescript
// ❌ Bad: No shard key
SELECT * FROM orders WHERE status = 'shipped'; // Scans all 100 shards!

// ✅ Good: Include shard key
SELECT * FROM orders WHERE user_id = ? AND status = 'shipped'; // Targets 1 shard
症状：查询扫描全部分片（速度极慢） 修复方案：
typescript
// ❌ 错误：无分片键
SELECT * FROM orders WHERE status = 'shipped'; // 扫描全部100个分片!

// ✅ 正确：携带分片键
SELECT * FROM orders WHERE user_id = ? AND status = 'shipped'; // 仅访问1个分片

Error 3: Sequential IDs with Range Sharding

错误3：范围分片搭配顺序ID

Symptom: Latest shard gets all writes Fix:
typescript
// ❌ Bad: Range sharding with auto-increment
// Shard 0: 1-1M, Shard 1: 1M-2M, Shard 2: 2M+ → All new writes to Shard 2!

// ✅ Good: Hash-based sharding
const shardId = hash(id) % shardCount; // Even distribution
症状：最新分片承接所有写入请求 修复方案：
typescript
// ❌ 错误：自增ID搭配范围分片
// 分片0: 1-1M, 分片1: 1M-2M, 分片2: 2M+ → 所有新写入都进入分片2!

// ✅ 正确：基于哈希的分片
const shardId = hash(id) % shardCount; // 分布均匀

Error 4: No Rebalancing Strategy

错误4：没有重平衡策略

Symptom: Stuck with initial shard count, cannot scale Fix:
typescript
// ❌ Bad: Simple modulo
const shardId = hash(key) % shardCount; // Adding 5th shard breaks ALL keys

// ✅ Good: Consistent hashing
const ring = new ConsistentHashRing(shards);
const shardId = ring.getNode(key); // Only ~25% of keys move when adding shard
症状：只能使用初始分片数量，无法扩展 修复方案：
typescript
// ❌ 错误：简单取模
const shardId = hash(key) % shardCount; // 新增第5个分片会导致所有键映射失效

// ✅ 正确：一致性哈希
const ring = new ConsistentHashRing(shards);
const shardId = ring.getNode(key); // 新增分片时仅约25%的键需要迁移

Error 5: Cross-Shard Transactions

错误5：跨分片事务问题

Symptom: Data inconsistency, partial writes Fix:
typescript
// ❌ Bad: Cross-shard transaction (will corrupt)
BEGIN;
UPDATE shard_1.accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE shard_2.accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; // If shard_2 fails, shard_1 already committed!

// ✅ Good: Two-Phase Commit or Saga pattern
const txn = new TwoPhaseCommitTransaction();
txn.addOperation(shard_1, 'UPDATE accounts SET balance = balance - 100 WHERE id = ?', ['A']);
txn.addOperation(shard_2, 'UPDATE accounts SET balance = balance + 100 WHERE id = ?', ['B']);
await txn.execute(); // Atomic across shards
症状：数据不一致、部分写入成功 修复方案：
typescript
// ❌ 错误：跨分片事务（会导致数据损坏）
BEGIN;
UPDATE shard_1.accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE shard_2.accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; // 如果分片2执行失败，分片1已经提交!

// ✅ 正确：两阶段提交或Saga模式
const txn = new TwoPhaseCommitTransaction();
txn.addOperation(shard_1, 'UPDATE accounts SET balance = balance - 100 WHERE id = ?', ['A']);
txn.addOperation(shard_2, 'UPDATE accounts SET balance = balance + 100 WHERE id = ?', ['B']);
await txn.execute(); // 跨分片原子操作

Error 6: Mutable Shard Key

错误6：分片键可修改

Symptom: Records move shards, causing duplicates Fix:
typescript
// ❌ Bad: Shard by country (user relocates)
shard_key = user.country; // User moves US → CA, now in different shard!

// ✅ Good: Shard by immutable user_id
shard_key = user.id; // Never changes
症状：记录在分片间迁移，导致重复数据 修复方案：
typescript
// ❌ 错误：按国家分片（用户可能搬迁）
shard_key = user.country; // 用户从美国搬到加拿大，现在属于不同分片!

// ✅ 正确：用不可变更的user_id分片
shard_key = user.id; // 永远不会修改

Error 7: No Monitoring

错误7：缺乏监控

Symptom: Silent hotspots, sudden performance degradation Fix:
typescript
// ✅ Required metrics
- Per-shard record counts (should be within 20%)
- Query distribution (no shard > 40% of queries)
- Storage per shard (alert at 80%)
- Latency p99 per shard
Load 
references/error-catalog.md
for all 10 errors with detailed fixes.
症状：静默热点、性能突然下降 修复方案：
typescript
// ✅ 必须监控的指标
- 每个分片的记录数（差值应在20%以内）
- 查询分布（无分片承接超过40%的查询）
- 每个分片的存储用量（使用率达80%时告警）
- 每个分片的p99延迟
加载 
references/error-catalog.md
查看全部10个错误的详细修复方案。

Sharding Strategies

分片策略

StrategyBest ForProsCons
HashUser data, even load criticalNo hotspots, predictableRange queries scatter
RangeTime-series, logs, append-onlyRange queries efficient, archivalRecent shard hotspot
DirectoryMulti-tenancy, complex routingFlexible, easy rebalancingLookup overhead, SPOF
Load 
references/sharding-strategies.md
for detailed comparisons with production examples (Instagram, Discord, Salesforce).
策略适用场景优势劣势
哈希用户数据、对负载均衡要求高的场景无热点、可预测范围查询需要分散到多个分片
范围时序数据、日志、仅追加写入的场景范围查询效率高、易于归档近期分片易出现热点
目录多租户、复杂路由场景灵活、易于重平衡存在查询开销、单点故障风险
加载 
references/sharding-strategies.md
查看包含Instagram、Discord、Salesforce等生产案例的详细对比。

Shard Key Selection Criteria

分片键选择标准

CriterionImportanceCheck Method
High cardinalityCritical
COUNT(DISTINCT shard_key)
> shard_count × 100
Even distributionCriticalNo value > 5% of total
ImmutableCriticalValue never changes
Query alignmentHigh80%+ queries include it
Data localityMediumRelated records together
Decision Tree:
  • User-focused app → 
    user_id
  • Multi-tenant SaaS → 
    tenant_id
  • Time-series/logs → 
    timestamp
    (range sharding)
  • Product catalog → 
    product_id
Load 
references/shard-key-selection.md
for comprehensive decision trees and testing strategies.
标准重要性校验方法
高基数核心
COUNT(DISTINCT shard_key)
> 分片数量 × 100
分布均匀核心无单个值占总量的5%以上
不可变更核心值永远不会修改
查询对齐高80%以上的查询会携带该字段
数据局部性中关联记录可以放在一起
决策树：
  • 面向用户的应用 → 
    user_id
  • 多租户SaaS → 
    tenant_id
  • 时序/日志数据 → 
    timestamp
    （范围分片）
  • 商品目录 → 
    product_id
加载 
references/shard-key-selection.md
查看完整决策树和测试策略。

Configuration Summary

配置汇总

Hash-Based Router

哈希路由

typescript
import { HashRouter } from './templates/hash-router';

const router = new HashRouter([
  { id: 'shard_0', connection: { /* PostgreSQL config */ } },
  { id: 'shard_1', connection: { /* PostgreSQL config */ } },
]);

// Automatically routes to correct shard
const user = await router.query('user_123', 'SELECT * FROM users WHERE id = $1', ['user_123']);
typescript
import { HashRouter } from './templates/hash-router';

const router = new HashRouter([
  { id: 'shard_0', connection: { /* PostgreSQL config */ } },
  { id: 'shard_1', connection: { /* PostgreSQL config */ } },
]);

// 自动路由到正确的分片
const user = await router.query('user_123', 'SELECT * FROM users WHERE id = $1', ['user_123']);

Range-Based Router

范围路由

typescript
import { RangeRouter } from './templates/range-router';

const router = new RangeRouter(shardConfigs, [
  { start: Date.parse('2024-01-01'), end: Date.parse('2024-04-01'), shardId: 'shard_q1' },
  { start: Date.parse('2024-04-01'), end: Date.parse('2024-07-01'), shardId: 'shard_q2' },
  { start: Date.parse('2024-07-01'), end: Infinity, shardId: 'shard_q3' },
]);

// Range queries target specific shards
const janEvents = await router.queryRange(
  Date.parse('2024-01-01'),
  Date.parse('2024-02-01'),
  'SELECT * FROM events WHERE created_at BETWEEN $1 AND $2'
);
typescript
import { RangeRouter } from './templates/range-router';

const router = new RangeRouter(shardConfigs, [
  { start: Date.parse('2024-01-01'), end: Date.parse('2024-04-01'), shardId: 'shard_q1' },
  { start: Date.parse('2024-04-01'), end: Date.parse('2024-07-01'), shardId: 'shard_q2' },
  { start: Date.parse('2024-07-01'), end: Infinity, shardId: 'shard_q3' },
]);

// 范围查询仅访问指定分片
const janEvents = await router.queryRange(
  Date.parse('2024-01-01'),
  Date.parse('2024-02-01'),
  'SELECT * FROM events WHERE created_at BETWEEN $1 AND $2'
);

Directory-Based Router

目录路由

typescript
import { DirectoryRouter } from './templates/directory-router';

const router = new DirectoryRouter(directoryDBConfig, shardConfigs);

// Assign tenant to specific shard
await router.assignShard('tenant_acme', 'shard_enterprise');

// Route automatically
const users = await router.query('tenant_acme', 'SELECT * FROM users');
typescript
import { DirectoryRouter } from './templates/directory-router';

const router = new DirectoryRouter(directoryDBConfig, shardConfigs);

// 将租户分配到指定分片
await router.assignShard('tenant_acme', 'shard_enterprise');

// 自动路由
const users = await router.query('tenant_acme', 'SELECT * FROM users');

When to Load References

何时需要查阅参考文档

Choosing Strategy

选择分片策略时

Load 
references/sharding-strategies.md
when:
  • Deciding between hash, range, directory
  • Need production examples (Instagram, Discord)
  • Planning hybrid approaches
加载 
references/sharding-strategies.md
，当你需要：
  • 在哈希、范围、目录策略中做选择
  • 参考生产案例（Instagram、Discord）
  • 规划混合分片方案

Selecting Shard Key

选择分片键时

Load 
references/shard-key-selection.md
when:
  • Choosing shard key for new project
  • Evaluating existing shard key
  • Testing distribution with production data
加载 
references/shard-key-selection.md
，当你需要：
  • 为新项目选择分片键
  • 评估现有分片键的合理性
  • 用生产数据测试分片分布

Implementation

实现阶段

Load 
references/implementation-patterns.md
when:
  • Building shard router from scratch
  • Implementing consistent hashing
  • Need transaction handling (2PC, Saga)
  • Setting up monitoring/metrics
加载 
references/implementation-patterns.md
，当你需要：
  • 从零搭建分片路由
  • 实现一致性哈希
  • 处理事务（2PC、Saga）
  • 搭建监控/指标体系

Cross-Shard Operations

跨分片操作场景

Load 
references/cross-shard-queries.md
when:
  • Need to aggregate across shards (COUNT, SUM, AVG)
  • Implementing cross-shard joins
  • Building pagination across shards
  • Optimizing scatter-gather patterns
加载 
references/cross-shard-queries.md
，当你需要：
  • 跨分片聚合（COUNT、SUM、AVG）
  • 实现跨分片关联
  • 实现跨分片分页
  • 优化 scatter-gather 模式

Rebalancing

重平衡场景

Load 
references/rebalancing-guide.md
when:
  • Adding new shards
  • Migrating data between shards
  • Planning zero-downtime migrations
  • Balancing uneven load
加载 
references/rebalancing-guide.md
，当你需要：
  • 新增分片
  • 在分片间迁移数据
  • 规划零停机迁移
  • 平衡不均衡的负载

Error Prevention

错误预防场景

Load 
references/error-catalog.md
when:
  • Troubleshooting performance issues
  • Reviewing shard architecture
  • All 10 documented errors with fixes
加载 
references/error-catalog.md
，当你需要：
  • 排查性能问题
  • 评审分片架构
  • 查看全部10个已记录的错误及修复方案

Complete Setup Checklist

完整部署检查清单

Before Sharding:
  • Tested shard key distribution with production data
  • Shard key in 80%+ of queries
  • Monitoring infrastructure ready
  • Rebalancing strategy planned
Router Implementation:
  • Shard routing layer (not hardcoded shards)
  • Connection pooling per shard
  • Error handling and retries
  • Metrics collection (queries/shard, latency)
Shard Configuration:
  • 4-8 shards initially (room to grow)
  • Consistent hashing or virtual shards
  • Replicas per shard (HA)
  • Backup strategy per shard
Application Changes:
  • All queries include shard key
  • Cross-shard joins eliminated (denormalized)
  • Transaction boundaries respected
  • Connection pooling configured
分片前：
  • 已用生产数据测试分片键分布
  • 80%以上的查询携带分片键
  • 监控基础设施已就绪
  • 已规划重平衡策略
路由实现：
  • 实现分片路由层（不硬编码分片）
  • 每个分片配置连接池
  • 实现错误处理和重试逻辑
  • 实现指标采集（每个分片的查询量、延迟）
分片配置：
  • 初始配置4-8个分片（预留扩展空间）
  • 使用一致性哈希或虚拟分片
  • 每个分片配置副本（高可用）
  • 每个分片配置备份策略
应用改造：
  • 所有查询携带分片键
  • 消除跨分片关联（非规范化）
  • 遵守事务边界
  • 配置连接池

Production Example

生产环境示例

Before (Single database overwhelmed):
typescript
// Single PostgreSQL instance
const db = new Pool({ host: 'db.example.com' });

// All 10M users on one server
const users = await db.query('SELECT * FROM users WHERE status = $1', ['active']);
// Query time: 5000ms (slow!)
// DB CPU: 95%
// Disk: 500GB, growing
After (Sharded across 8 servers):
typescript
// Hash-based sharding with 8 shards
const router = new HashRouter([
  { id: 'shard_0', connection: { host: 'db0.example.com' } },
  { id: 'shard_1', connection: { host: 'db1.example.com' } },
  // ... 6 more shards
]);

// Query single user (targets 1 shard)
const user = await router.query('user_123', 'SELECT * FROM users WHERE id = $1', ['user_123']);
// Query time: 10ms (500x faster!)

// Query all shards (scatter-gather)
const allActive = await router.queryAll('SELECT * FROM users WHERE status = $1', ['active']);
// Query time: 800ms (parallelized across 8 shards, 6x faster than single)

// Result: Each shard handles ~1.25M users
// DB CPU per shard: 20%
// Disk per shard: 65GB
// Can scale to 16 shards easily (consistent hashing)
改造前（单库过载）：
typescript
// 单PostgreSQL实例
const db = new Pool({ host: 'db.example.com' });

// 全部1000万用户都在同一台服务器
const users = await db.query('SELECT * FROM users WHERE status = $1', ['active']);
// 查询耗时: 5000ms（非常慢!）
// 数据库CPU使用率: 95%
// 磁盘用量: 500GB，持续增长
改造后（8台服务器分片部署）：
typescript
// 8分片的哈希分片方案
const router = new HashRouter([
  { id: 'shard_0', connection: { host: 'db0.example.com' } },
  { id: 'shard_1', connection: { host: 'db1.example.com' } },
  // ... 其余6个分片
]);

// 查询单个用户（仅访问1个分片）
const user = await router.query('user_123', 'SELECT * FROM users WHERE id = $1', ['user_123']);
// 查询耗时: 10ms（快了500倍!）

// 查询全部分片（scatter-gather模式）
const allActive = await router.queryAll('SELECT * FROM users WHERE status = $1', ['active']);
// 查询耗时: 800ms（8个分片并行执行，比单库快6倍）

// 效果: 每个分片承载约125万用户
// 每个分片的CPU使用率: 20%
// 每个分片的磁盘用量: 65GB
// 可以轻松扩展到16个分片（基于一致性哈希）

Known Issues Prevention

已知问题预防

All 10 documented errors prevented:
  1. ✅ Wrong shard key (hotspots) → Test distribution first
  2. ✅ Missing shard key in queries → Code review, linting
  3. ✅ Cross-shard transactions → Use 2PC or Saga pattern
  4. ✅ Sequential ID hotspots → Use hash-based sharding
  5. ✅ No rebalancing strategy → Consistent hashing from day 1
  6. ✅ Timestamp sharding hotspots → Hybrid hash+range approach
  7. ✅ Mutable shard key → Choose immutable keys (user_id)
  8. ✅ No routing layer → Abstract with router from start
  9. ✅ No monitoring → Track per-shard metrics
  10. ✅ Weak hash function → Use MD5, MurmurHash3, xxHash
See: 
references/error-catalog.md
for detailed fixes
全部10个已记录的错误都已覆盖预防方案：
  1. ✅ 错误的分片键（热点）→ 提前测试分布情况
  2. ✅ 查询未携带分片键 → 代码评审、lint检查
  3. ✅ 跨分片事务 → 使用2PC或Saga模式
  4. ✅ 顺序ID热点 → 使用哈希分片
  5. ✅ 无重平衡策略 → 从第一天就使用一致性哈希
  6. ✅ 时间戳分片热点 → 哈希+范围的混合方案
  7. ✅ 可修改的分片键 → 选择不可变更的键（user_id）
  8. ✅ 无路由层 → 从一开始就用路由层做抽象
  9. ✅ 无监控 → 追踪每个分片的指标
  10. ✅ 弱哈希函数 → 使用MD5、MurmurHash3、xxHash
参考：
references/error-catalog.md
查看详细修复方案

Resources

资源

Templates:
  • templates/hash-router.ts
    - Hash-based sharding
  • templates/range-router.ts
    - Range-based sharding
  • templates/directory-router.ts
    - Directory-based sharding
  • templates/cross-shard-aggregation.ts
    - Aggregation patterns
References:
  • references/sharding-strategies.md
    - Strategy comparison
  • references/shard-key-selection.md
    - Key selection guide
  • references/implementation-patterns.md
    - Router implementations
  • references/cross-shard-queries.md
    - Query patterns
  • references/rebalancing-guide.md
    - Migration strategies
  • references/error-catalog.md
    - All 10 errors documented
Production Examples:
  • Instagram: Range sharding for media
  • Discord: Hash sharding for messages
  • Salesforce: Directory sharding for orgs
Production-tested | 10 errors prevented | MIT License
模板：
  • templates/hash-router.ts
    - 哈希分片实现
  • templates/range-router.ts
    - 范围分片实现
  • templates/directory-router.ts
    - 目录分片实现
  • templates/cross-shard-aggregation.ts
    - 跨分片聚合模式
参考文档：
  • references/sharding-strategies.md
    - 分片策略对比
  • references/shard-key-selection.md
    - 分片键选择指南
  • references/implementation-patterns.md
    - 路由实现方案
  • references/cross-shard-queries.md
    - 查询模式
  • references/rebalancing-guide.md
    - 迁移策略
  • references/error-catalog.md
    - 全部10个错误的记录
生产案例：
  • Instagram: 媒体数据的范围分片
  • Discord: 消息数据的哈希分片
  • Salesforce: 租户的目录分片
经过生产环境验证 | 覆盖10个常见错误的预防方案 | 采用MIT许可证