xingliuhuaMySQL-15 分库分表与分区
目录
MySQL 分库分表与分区
目录
1. 为什么要分库分表
1.1 单机 MySQL 的瓶颈
单机 MySQL 在数据量和并发量达到一定规模后，会遇到以下瓶颈：
性能瓶颈：
单表数据量超过 2000 万行后，B+Tree 高度增加，索引查询变慢
单库 QPS 上限约 10,000~30,000（视机器配置）
大表 DDL（加字段、加索引）耗时长，期间产生大量锁等待
存储瓶颈：
单机磁盘容量有限（TB 级）
单表 .ibd 文件过大，备份、恢复耗时
可用性瓶颈：
单机故障影响全部业务
主从复制延迟随数据量增大而增加
1.2 什么时候考虑分库分表
不要过早分库分表！先考虑以下优化：
1. 加索引、优化慢查询
2. 读写分离（主库写，从库读）
3. 引入缓存层（Redis）
4. 升级硬件（SSD、更大内存）
满足以下条件之一，才考虑分库分表：
单表行数 > 1000 万，且查询性能明显下降
单库 QPS 持续超过 5,000，主从复制延迟 > 1s
单机磁盘使用率 > 70%，预计 1 年内撑满
大表 DDL 超过 30 分钟，影响业务
1.3 拆分的两个维度
垂直拆分（按列/按业务）：解决"表太宽"或"库太杂"的问题
垂直分表：把一张宽表按列拆成多张表（冷热分离）
垂直分库：把不同业务的表拆到不同数据库（微服务化）
水平拆分（按行，Sharding）：解决"数据量太大"的问题
水平分表：同一个库内，把数据按规则拆到多张结构相同的表
水平分库：把数据拆到多个库，每个库可以在不同机器上
2. MySQL 分区表
分区表是 MySQL 原生支持的功能，对应用透明（仍是一张逻辑表），底层按规则将数据分散到不同的物理文件。
2.1 分区类型
RANGE 分区（最常用）
按列值的范围分区，非常适合时间序列数据。
-- 按年份分区的订单表
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT,
order_no VARCHAR(64) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at) -- 分区键必须包含在主键中
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_future VALUES LESS THAN MAXVALUE -- 兜底分区
);
分区裁剪（Partition Pruning）：查询时带上分区键，MySQL 只扫描相关分区。
-- 查询 2024 年的数据，只扫描 p2024 分区
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- partitions: p2024 ← 分区裁剪生效
-- 不带分区键，扫描所有分区（分区表退化为全表扫描）
EXPLAIN SELECT * FROM orders WHERE order_no = 'ORD202401010001';
-- partitions: p2022,p2023,p2024,p2025,p_future ← 裁剪失效！
添加新分区（每年操作一次）：
-- 先把 p_future 改掉，再加新的 p_future
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
删除旧分区（极快，直接删文件）：
-- 删除 2022 年分区（比 DELETE 快几个数量级，不产生 Undo Log）
ALTER TABLE orders DROP PARTITION p2022;
LIST 分区
按列值的枚举列表分区，适合按地区、按类别等固定集合分区。
-- 按地区分区
CREATE TABLE user_profiles (
id BIGINT UNSIGNED AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
region TINYINT NOT NULL COMMENT '1=华北 2=华南 3=华东 4=西部',
nickname VARCHAR(64),
PRIMARY KEY (id, region)
) ENGINE=InnoDB
PARTITION BY LIST (region) (
PARTITION p_north VALUES IN (1),
PARTITION p_south VALUES IN (2),
PARTITION p_east VALUES IN (3),
PARTITION p_west VALUES IN (4)
);
HASH 分区
按列值取模均匀分布，适合没有明显范围规律的场景。
-- 按 user_id 哈希分 8 个区
CREATE TABLE user_behavior (
id BIGINT UNSIGNED AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
action VARCHAR(32) NOT NULL,
ts DATETIME NOT NULL,
PRIMARY KEY (id, user_id)
) ENGINE=InnoDB
PARTITION BY HASH(user_id)
PARTITIONS 8;
KEY 分区
与 HASH 类似，但使用 MySQL 内部的哈希函数，支持多列和字符串类型。
PARTITION BY KEY(user_id, created_at)
PARTITIONS 16;
2.2 分区表的限制
1. 分区键必须包含在主键和所有唯一索引中
2. 每个表最多 8192 个分区（MySQL 8.0+）
3. 外键不能和分区表一起使用
4. 分区表的全文索引不支持
5. 查询不带分区键时，退化为全分区扫描（比单表还慢）
适合分区表的场景：
✅ 历史数据归档（按时间范围，快速删除旧数据）
✅ 数据都在同一台机器上，只是文件物理隔离
✅ 查询条件基本都带分区键
不适合分区表的场景：
❌ 需要跨机器扩展（分区表无法跨机器）
❌ 查询模式多样，分区键不固定
❌ 数据量还没到瓶颈（过度设计）
2.3 查看分区信息
-- 查看各分区数据量
SELECT
PARTITION_NAME,
TABLE_ROWS,
AVG_ROW_LENGTH,
DATA_LENGTH / 1024 / 1024 AS data_mb
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders';
-- 查看分区裁剪是否生效（看 partitions 列）
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01';
3. 垂直拆分
3.1 垂直分表：冷热列分离
一张宽表中，有些列频繁访问（热字段），有些列偶尔访问（冷字段）。宽表会导致：
- 行数据变长，单个数据页存储的行数减少，IO 变多
- 查询时读取了大量无用列（如 TEXT、BLOB 字段）
案例：商品表拆分
-- 拆分前：一张 20+ 列的宽表
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(255), -- 热字段（列表页）
price DECIMAL(10,2), -- 热字段（列表页）
category_id INT, -- 热字段
stock INT, -- 热字段
thumbnail VARCHAR(512), -- 热字段
-- 以下是冷字段（详情页才用）
description TEXT, -- 商品详情，大字段
spec_json JSON, -- 规格参数
images_json JSON, -- 图片列表
seo_title VARCHAR(255),
seo_keywords VARCHAR(255),
created_at DATETIME,
updated_at DATETIME
);
-- 拆分后：主表（高频访问）+ 详情表（低频访问）
CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
category_id INT NOT NULL,
stock INT NOT NULL DEFAULT 0,
thumbnail VARCHAR(512),
created_at DATETIME NOT NULL DEFAULT NOW(),
updated_at DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);
CREATE TABLE product_details (
product_id BIGINT PRIMARY KEY, -- 与 products.id 一一对应
description TEXT,
spec_json JSON,
images_json JSON,
seo_title VARCHAR(255),
seo_keywords VARCHAR(255),
FOREIGN KEY (product_id) REFERENCES products(id)
);
效果：
- 商品列表查询只访问
products（行短，IO 少，缓存命中率高） - 商品详情页联合查询
product_details（只在必要时）
3.2 垂直分库：按业务拆分
将不同业务模块的表分散到不同数据库（通常对应微服务），解决单库连接数不够和业务耦合的问题。
拆分前（大单体数据库）：
shop_db
├── users
├── user_profiles
├── orders
├── order_items
├── products
├── product_details
├── categories
├── carts
├── payments
└── logistics
拆分后（按业务垂直分库）：
user_db ← 用户服务
├── users
└── user_profiles
order_db ← 订单服务
├── orders
└── order_items
product_db ← 商品服务
├── products
├── product_details
└── categories
payment_db ← 支付服务
└── payments
logistics_db ← 物流服务
└── logistics
垂直分库的代价：
- 跨库 JOIN 变为应用层 JOIN（查多次再合并）
- 跨库事务需要分布式事务方案（见上一篇）
- 数据冗余（如订单表冗余用户名、商品名，避免跨库查询）
4. 水平拆分（Sharding）
4.1 水平分表（同库）
数据量大但并发不高时，在同一个数据库内将数据拆到多张结构相同的表。
order_db
├── orders_0
├── orders_1
├── orders_2
└── orders_3
路由规则示例（user_id % 4）：
user_id=100 → 100 % 4 = 0 → orders_0
user_id=101 → 101 % 4 = 1 → orders_1
适用场景：单库 IO 和存储是瓶颈，但 QPS 还好。
4.2 水平分库（跨机器）
数据量大且并发高时，将数据拆到多个数据库，部署在不同机器上。
┌──────────────┐
请求 ──路由层──▶ │ order_db_0 │ machine-1
│ orders_0 │
│ orders_1 │
└──────────────┘
┌──────────────┐
│ order_db_1 │ machine-2
│ orders_2 │
│ orders_3 │
└──────────────┘
┌──────────────┐
│ order_db_2 │ machine-3
│ orders_4 │
│ orders_5 │
└──────────────┘
┌──────────────┐
│ order_db_3 │ machine-4
│ orders_6 │
│ orders_7 │
└──────────────┘
2 库 × 4 表/库 = 8 张表，可承载 8 倍数据量
4.3 分库分表组合
实际生产中通常是先分库，每个库再分表：
分片规则示例（4 库 × 4 表 = 16 个分片）：
shard_id = user_id % 16
db_index = shard_id / 4 → 决定去哪个库
tbl_index = shard_id % 4 → 决定去哪张表
user_id=1000：
shard_id = 1000 % 16 = 8
db_index = 8 / 4 = 2 → order_db_2
tbl_index = 8 % 4 = 0 → orders_0
最终：order_db_2.orders_0
5. 分片键的选择
分片键（Sharding Key）是水平拆分的核心，选择不当会导致数据倾斜或大量跨分片查询。
5.1 选择原则
原则 1：高频查询必须带分片键
最常用的查询条件要包含分片键，避免散列查询（全分片扫描）
原则 2：数据分布均匀
分片键的值要足够离散，避免某个分片数据远多于其他分片（热点）
原则 3：业务关联性强的数据落在同一分片
如：同一用户的所有订单要在同一分片，避免跨分片聚合
原则 4：不要频繁变化
分片键一旦确定，更改代价极大（需要数据迁移）
原则 5：尽量使用整型
整型取模运算比字符串哈希快
5.2 常见分片键方案
按用户 ID 分片（最常见）
适用：ToC 业务，数据天然以用户为单位聚合
订单表按 user_id 分片 → 查询"我的订单"不跨分片
用户行为表按 user_id 分片 → 查询"我的浏览记录"不跨分片
问题：大 V 用户（粉丝千万）产生的数据远多于普通用户 → 热点
解决：对大 V 单独处理（独立分片或特殊路由）
按订单 ID 分片
适用：ToB 业务或需要按订单直接查询
商家查询"今天所有订单"会跨分片（商家名下可能有多个用户）
问题：同一用户的订单散落在不同分片，查"我的订单"需跨分片
权衡：如果按订单 ID 查询是最高频操作，选此方案
按时间分片（不推荐单独用）
问题：写入永远在最新分片（热点写），旧分片几乎没有写入
大多数业务不是按时间查，而是按用户/商家查，必然跨分片
推荐：时间 + 用户 ID 组合分片，或用分区表代替
按地理位置分片
适用：有明显地域属性的业务（外卖、打车）
骑手订单按城市 ID 分片 → 城市维度的统计不跨分片
问题：一线城市数据远多于小城市 → 不均匀
解决：大城市独立分片，小城市合并分片
5.3 案例：电商订单分片键
业务查询频率排序：
1. 用户查询"我的订单列表"（按 user_id） ← 最高频，ToC
2. 商家查询"我店的订单"（按 shop_id） ← 高频，ToB
3. 客服查询某个订单（按 order_id） ← 中频
4. 运营查询某天所有订单（按日期） ← 低频，走数仓
结论：选 user_id 作为分片键（满足最高频查询）
代价及解决方案：
商家查"我店的订单" → 跨分片，走 Elasticsearch 或数仓
按 order_id 查 → order_id 中编码 user_id（见分布式 ID 章节）
运营按日期查 → 走离线数仓或 OLAP 系统
6. 分片路由策略
6.1 Range 路由
按范围划分分片，如按 ID 范围、时间范围。
分片规则：
shard_0: user_id [0, 5,000,000)
shard_1: user_id [5,000,000, 10,000,000)
shard_2: user_id [10,000,000, 15,000,000)
...
优点：
扩容时只需增加新分片，不用迁移历史数据
范围查询性能好（连续数据在同一分片）
缺点：
新注册用户永远在最新分片（写热点）
各分片数据量不均匀（早期用户少，晚期用户多）
6.2 Hash 路由（取模）
shard_id = hash(sharding_key) % shard_count
优点：
数据分布均匀，无热点写
实现简单
缺点：
扩容时需要迁移大量数据（shard_count 变化，路由结果全变）
范围查询必须全分片扫描
6.3 一致性哈希路由
解决取模路由扩容迁移量大的问题。
原理：
将所有分片 ID 映射到一个虚拟环（0 ~ 2^32）上
分片键 hash 后，顺时针找到第一个分片节点
[shard_0]---[shard_1]---[shard_2]---[shard_3]---（环形）
扩容（新增 shard_4）：
只有 shard_4 与 shard_0 之间的数据需要迁移
理论上只迁移 1/N 的数据（N = 新分片数）
虚节点（Virtual Node）：
每个物理节点对应多个虚节点（如 150 个），使数据分布更均匀
避免节点少时分布不均匀的问题
6.4 路由表（Mapping Table）
维护一张"用户 ID → 分片 ID"的映射表，灵活性最高。
CREATE TABLE shard_mapping (
user_id BIGINT UNSIGNED PRIMARY KEY,
shard_id TINYINT UNSIGNED NOT NULL,
INDEX idx_shard (shard_id)
);
优点：
可以按需迁移单个用户，大 V 单独处理
扩容时精准控制迁移范围
缺点：
每次路由都要查这张表（需要缓存，否则成为性能瓶颈）
映射表本身需要高可用
7. 分库分表带来的问题
水平分库分表是最后的手段，因为它会带来一系列新问题，每个问题都需要专门的解决方案。
7.1 分布式 ID
分库分表后，各分片的自增 ID 会冲突，必须使用全局唯一 ID。
方案一：UUID
id := uuid.New().String() // e.g. "550e8400-e29b-41d4-a716-446655440000"
缺点：
36 字节字符串，占用空间大，索引性能差
UUID v4 完全随机，插入时导致 B+Tree 页分裂，写入性能差
不包含时间信息，无法按 ID 排序反映插入顺序
方案二：数据库自增（多主）
使用多个数据库，每个库生成不同步长的自增 ID：
-- 库1：AUTO_INCREMENT 从 1 开始，步长 2（生成奇数 ID）
SET @@auto_increment_offset = 1;
SET @@auto_increment_increment = 2;
-- 库2：AUTO_INCREMENT 从 2 开始，步长 2（生成偶数 ID）
SET @@auto_increment_offset = 2;
SET @@auto_increment_increment = 2;
缺点：
生成 ID 需要访问数据库，性能瓶颈
步长固定，扩展新节点困难
方案三：Snowflake 雪花算法（推荐）
64 bit 结构：
┌──────────────────────────────────────────────────────────────────┐
│ 0 │ 41bit 毫秒时间戳 │ 10bit 机器ID │ 12bit 序列号 │
│ │ (从某个纪元到现在的毫秒) │ (5bit+5bit) │ (每毫秒最多4096) │
└──────────────────────────────────────────────────────────────────┘
特点：
趋势递增（时间戳在高位），写入 B+Tree 性能好
纯内存生成，每秒可生成 400 万+ ID
包含时间信息，可反向解析出生成时间
10bit 机器ID 支持 1024 个节点
问题：
依赖系统时钟，时钟回拨会导致 ID 重复
解决方案：检测到时钟回拨时，等待时钟追上或抛异常
// 简化版 Snowflake（生产建议使用 github.com/bwmarrin/snowflake）
type Snowflake struct {
mu sync.Mutex
epoch int64 // 起始纪元（毫秒时间戳）
nodeID int64 // 机器 ID（0~1023）
sequence int64 // 序列号（0~4095）
lastStamp int64 // 上次生成 ID 的时间戳
}
const (
nodeBits = 10
sequenceBits = 12
maxNodeID = -1 ^ (-1 << nodeBits) // 1023
maxSequence = -1 ^ (-1 << sequenceBits) // 4095
timeShift = nodeBits + sequenceBits // 22
nodeShift = sequenceBits // 12
)
func NewSnowflake(nodeID int64) (*Snowflake, error) {
if nodeID < 0 || nodeID > maxNodeID {
return nil, fmt.Errorf("nodeID must be between 0 and %d", maxNodeID)
}
// 2020-01-01 00:00:00 UTC 作为纪元，延长可用时间
epoch := time.Date(2020, 1, 1, 0, 0, 0, 0, time.UTC).UnixMilli()
return &Snowflake{nodeID: nodeID, epoch: epoch}, nil
}
func (s *Snowflake) NextID() (int64, error) {
s.mu.Lock()
defer s.mu.Unlock()
now := time.Now().UnixMilli() - s.epoch
if now < s.lastStamp {
// 时钟回拨：等待最多 2ms
wait := s.lastStamp - now
if wait > 2 {
return 0, fmt.Errorf("clock moved backwards by %d ms", wait)
}
time.Sleep(time.Duration(wait) * time.Millisecond)
now = time.Now().UnixMilli() - s.epoch
}
if now == s.lastStamp {
s.sequence = (s.sequence + 1) & maxSequence
if s.sequence == 0 {
// 同一毫秒内序列号用完，等下一毫秒
for now <= s.lastStamp {
now = time.Now().UnixMilli() - s.epoch
}
}
} else {
s.sequence = 0
}
s.lastStamp = now
id := (now << timeShift) | (s.nodeID << nodeShift) | s.sequence
return id, nil
}
// 从 Snowflake ID 反解时间（调试用）
func ParseSnowflakeTime(id int64, epoch time.Time) time.Time {
ms := id >> timeShift
return epoch.Add(time.Duration(ms) * time.Millisecond)
}
将分片信息编码进 ID（关键技巧）
// 在 Snowflake ID 中编码 shard_id，实现按 ID 反向路由
// 结构：时间戳 | shard_id | 序列号
// 这样通过 order_id 就能知道它在哪个分片，无需映射表
const (
shardBits = 6 // 支持 64 个分片
seqBits = 10 // 每毫秒每分片 1024 个 ID
shardMaxID = (1 << shardBits) - 1 // 63
)
func NewShardedID(timestamp, shardID, sequence int64) int64 {
return (timestamp << (shardBits + seqBits)) |
(shardID << seqBits) |
sequence
}
func ExtractShardID(id int64) int64 {
return (id >> seqBits) & shardMaxID
}
// 使用示例：
// orderID = NewShardedID(now, userID%64, seq)
// 查询时：shardID = ExtractShardID(orderID)
// 无需查映射表，直接路由到正确分片
7.2 跨分片查询
这是分库分表后最棘手的问题。
场景一：按非分片键查询（散列查询）
问题：
分片键是 user_id，商家要查"我店铺的所有订单"（按 shop_id）
shop_id 不是分片键，无法路由到单一分片
解决方案 1：全分片广播查询（不推荐，性能差）
同时查所有分片，合并结果
适合数据量小、分片数少的场景
解决方案 2：建立二级索引表（推荐）
维护一张 shop_id → order_id 的索引表
先查索引表得到 order_id 列表
再用 order_id 中编码的 shard_id 查对应分片
-- 二级索引表（存在单独的 index_db）
CREATE TABLE shop_order_index (
shop_id BIGINT UNSIGNED NOT NULL,
order_id BIGINT UNSIGNED NOT NULL, -- 编码了 shard_id
created_at DATETIME NOT NULL,
PRIMARY KEY (shop_id, order_id),
INDEX idx_created (shop_id, created_at)
);
// 商家查询订单：二级索引 → 精准路由
func (s *OrderService) ListShopOrders(ctx context.Context, shopID uint64, page, size int) ([]Order, error) {
// 1. 查二级索引表，得到 order_id 列表
var orderIDs []uint64
s.indexDB.WithContext(ctx).
Select("order_id").
Where("shop_id = ?", shopID).
Order("created_at DESC").
Offset((page-1)*size).Limit(size).
Find(&orderIDs)
if len(orderIDs) == 0 {
return nil, nil
}
// 2. 按 shard_id 分组
shardGroups := make(map[int64][]uint64)
for _, id := range orderIDs {
shardID := ExtractShardID(int64(id))
shardGroups[shardID] = append(shardGroups[shardID], id)
}
// 3. 并发查各分片（通常命中 1~2 个分片，因为订单都属于某个用户）
var mu sync.Mutex
var orders []Order
var wg sync.WaitGroup
var firstErr error
for shardID, ids := range shardGroups {
wg.Add(1)
go func(sid int64, oids []uint64) {
defer wg.Done()
db := s.getShardDB(sid)
var shardOrders []Order
err := db.WithContext(ctx).
Where("id IN ?", oids).
Find(&shardOrders).Error
mu.Lock()
defer mu.Unlock()
if err != nil && firstErr == nil {
firstErr = err
}
orders = append(orders, shardOrders...)
}(shardID, ids)
}
wg.Wait()
if firstErr != nil {
return nil, firstErr
}
// 4. 内存排序（不同分片的数据需要重新排序）
sort.Slice(orders, func(i, j int) bool {
return orders[i].CreatedAt.After(orders[j].CreatedAt)
})
return orders, nil
}
解决方案 3：数据双写（冗余存储）
同一份订单数据，写入两套分片：
按 user_id 分片的 orders_by_user → 满足用户查询
按 shop_id 分片的 orders_by_shop → 满足商家查询
代价：存储翻倍，两套数据要保持一致
适合：读远多于写，且两种维度查询都高频
场景二：分页查询
问题：查第 2 页（每页 20 条），SQL 是 LIMIT 20 OFFSET 20
每个分片执行 LIMIT 20 OFFSET 20，各取 20 条（共 n*20 条）
汇总后全局排序，取前 20 条
页码越大，拉取的数据越多（深分页严重）
方案 1：禁止深分页（推荐）
限制最多查前 100 页（2000 条）
提示用户使用搜索缩小范围
方案 2：游标分页（瀑布流场景）
用上次结果的最大/最小 ID 作为游标，不用 OFFSET
每次只查 cursor 之后的数据
方案 3：对于管理后台的深分页
数据同步到 Elasticsearch，由 ES 承担复杂查询
// 游标分页（不跨分片，性能好）
type OrderCursor struct {
LastID uint64 `json:"last_id"`
LastCreatedAt time.Time `json:"last_created_at"`
}
func (s *OrderService) ListUserOrdersByCursor(
ctx context.Context, userID uint64, cursor *OrderCursor, size int,
) ([]Order, *OrderCursor, error) {
db := s.getShardDBByUserID(userID) // 按 user_id 路由，不跨分片
tableName := s.getTableName(userID)
query := db.WithContext(ctx).Table(tableName).
Where("user_id = ?", userID).
Order("created_at DESC, id DESC").
Limit(size + 1) // 多取一条，判断是否有下一页
if cursor != nil {
query = query.Where(
"(created_at < ? OR (created_at = ? AND id < ?))",
cursor.LastCreatedAt, cursor.LastCreatedAt, cursor.LastID,
)
}
var orders []Order
if err := query.Find(&orders).Error; err != nil {
return nil, nil, err
}
var nextCursor *OrderCursor
if len(orders) > size {
last := orders[size-1]
nextCursor = &OrderCursor{
LastID: last.ID,
LastCreatedAt: last.CreatedAt,
}
orders = orders[:size]
}
return orders, nextCursor, nil
}
场景三：聚合统计（COUNT、SUM、AVG）
问题：统计全站今日下单数
SELECT COUNT(*) FROM orders WHERE DATE(created_at) = '2024-01-01'
需要查所有分片，汇总结果
解决方案：
1. 在线统计（数据量小）：并发查所有分片，SUM 各分片的 COUNT
2. 预计算（推荐）：定时任务统计写入汇总表，查汇总表
3. 数仓（推荐）：将数据同步到 ClickHouse / Hive，在数仓做统计
// 并发查所有分片做聚合
func (s *OrderService) CountTodayOrders(ctx context.Context) (int64, error) {
today := time.Now().Format("2006-01-02")
var total int64
var mu sync.Mutex
var wg sync.WaitGroup
var firstErr error
for i := 0; i < s.shardCount; i++ {
wg.Add(1)
go func(shardID int) {
defer wg.Done()
var count int64
db := s.shards[shardID]
err := db.WithContext(ctx).
Table(fmt.Sprintf("orders_%d", shardID)).
Where("DATE(created_at) = ?", today).
Count(&count).Error
mu.Lock()
defer mu.Unlock()
if err != nil && firstErr == nil {
firstErr = err
return
}
total += count
}(i)
}
wg.Wait()
return total, firstErr
}
场景四：跨分片 JOIN
问题：查询订单同时关联用户信息
SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id
orders 在 order_db（分片），users 在 user_db（分片）
数据库层无法执行跨库 JOIN
解决方案：
1. 应用层 JOIN：先查 orders，再按 user_id 批量查 users（IN 查询），内存合并
2. 数据冗余：在 orders 表冗余 user_name、user_phone 等常用字段
3. 全局表（字典表）：数据量小且基本不变的表（如省市区），同步到每个分片
-- 数据冗余：订单表冗余用户和商品信息（下单时快照）
CREATE TABLE orders_0 (
id BIGINT UNSIGNED PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
user_name VARCHAR(64), -- 冗余自 users.name（下单时快照）
user_phone VARCHAR(20), -- 冗余（脱敏后）
product_id BIGINT UNSIGNED NOT NULL,
product_name VARCHAR(255), -- 冗余自 products.name（下单时快照）
product_sku VARCHAR(64), -- 冗余
quantity INT UNSIGNED NOT NULL,
unit_price DECIMAL(10,2) NOT NULL, -- 下单时的价格快照
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(32) NOT NULL,
created_at DATETIME NOT NULL,
-- 冗余字段的好处：查订单不需要跨库查用户/商品
INDEX idx_user_id (user_id)
);
7.3 分布式事务
分库分表后，跨分片写操作无法使用单机事务，需要分布式事务方案（见上一篇）。
实践建议：
1. 尽量设计成同一分片内完成事务
下单时，把订单和订单项都按 user_id 路由到同一分片
2. 无法避免跨分片时，用本地消息表（最终一致性）
商品库存减少（product_db）→ MQ → 订单状态更新（order_db）
3. 金融级强一致才用 TCC，绝大多数业务接受最终一致
7.4 唯一约束失效
问题：
唯一约束只在单表内有效
orders_0 和 orders_1 的 order_no 字段，数据库无法保证跨表唯一
解决方案：
1. 全局唯一 ID 生成器（Snowflake）保证 ID 唯一
2. 业务唯一键（order_no）通过 Redis SETNX 或 唯一索引表 保证
// Redis 分布式锁保证 order_no 全局唯一
func (s *OrderService) generateUniqueOrderNo(ctx context.Context) (string, error) {
for i := 0; i < 3; i++ {
orderNo := fmt.Sprintf("ORD%s%06d", time.Now().Format("20060102150405"), rand.Intn(999999))
ok, err := s.redis.SetNX(ctx, "orderno:"+orderNo, "1", 24*time.Hour).Result()
if err != nil {
return "", err
}
if ok {
return orderNo, nil
}
// 极小概率冲突，重试
}
return "", errors.New("failed to generate unique order no")
}
7.5 运维复杂度
问题：
原来 1 个数据库 → 现在 16 个数据库实例
DDL 变更（加字段）需要在所有分片执行
备份、监控、慢查询分析都要汇总 16 份
解决方案：
DDL：使用 gh-ost 或 pt-online-schema-change，在所有分片顺序执行
监控：统一接入 Prometheus + Grafana，汇总所有分片指标
慢查询：统一接入慢查询分析平台（如 Percona PMM）
备份：各分片独立备份，备份脚本循环执行
8. 数据迁移与平滑扩容
8.1 初次分库分表：存量数据迁移
从单库迁移到分库分表，需要做到不停服、可回滚。
推荐方案：双写迁移（业界标准）
阶段一：准备
1. 建好新的分库分表结构
2. 代码支持"双写模式"（写旧库同时写新库）
3. 代码支持"读旧库/读新库"的开关（配置中心控制）
阶段二：全量迁移
4. 用迁移工具将旧库数据全量同步到新库（分批次，限速）
5. 全量迁移完成后，验证数据一致性（行数、关键字段抽查）
阶段三：双写
6. 灰度上线双写代码（写旧库 + 写新库）
7. 用 binlog 或对比工具持续验证新旧库数据一致性
阶段四：切读
8. 小流量切到新库读（1% → 10% → 50% → 100%）
9. 持续监控错误率和延迟
阶段五：下线旧库
10. 读流量全部切到新库后，停止双写
11. 观察一段时间（1~2周），确认无问题，下线旧库
// 双写模式示例
type OrderRepo struct {
oldDB *gorm.DB
newShards []*gorm.DB
// 配置开关，从配置中心读取
dualWrite bool // 是否双写
readFromNew bool // 是否从新库读
newWriteRatio float64 // 新库写入比例（灰度用）
}
func (r *OrderRepo) Create(ctx context.Context, order *Order) error {
// 写旧库（始终写，保证回滚能力）
if err := r.oldDB.WithContext(ctx).Create(order).Error; err != nil {
return err
}
// 写新库（双写阶段）
if r.dualWrite {
shardDB := r.getShardDB(order.UserID)
if err := shardDB.WithContext(ctx).Create(order).Error; err != nil {
// 新库写失败：记录日志，不影响旧库的结果
// 由数据修复任务补偿
log.Warn("dual write to new shard failed",
zap.Uint64("order_id", order.ID),
zap.Error(err),
)
}
}
return nil
}
func (r *OrderRepo) FindByUserID(ctx context.Context, userID uint64) ([]Order, error) {
if r.readFromNew {
// 读新库
shardDB := r.getShardDB(userID)
var orders []Order
return orders, shardDB.WithContext(ctx).
Where("user_id = ?", userID).Find(&orders).Error
}
// 读旧库
var orders []Order
return orders, r.oldDB.WithContext(ctx).
Where("user_id = ?", userID).Find(&orders).Error
}
8.2 扩容（分片数从 N 增加到 2N）
取模路由的扩容是最常见的挑战。
当前：4 个分片（user_id % 4）
shard_0: user_id ≡ 0 (mod 4)
shard_1: user_id ≡ 1 (mod 4)
shard_2: user_id ≡ 2 (mod 4)
shard_3: user_id ≡ 3 (mod 4)
扩容到 8 个分片（user_id % 8）：
新 shard_0: user_id ≡ 0 (mod 8) ← 原 shard_0 的一半数据
新 shard_4: user_id ≡ 4 (mod 8) ← 原 shard_0 的另一半数据
新 shard_1: user_id ≡ 1 (mod 8) ← 原 shard_1 的一半数据
新 shard_5: user_id ≡ 5 (mod 8) ← 原 shard_1 的另一半数据
...
关键发现：翻倍扩容时，每个旧分片只需迁移"一半"数据到新分片
从 shard_0 迁移 user_id % 8 == 4 的数据到 new_shard_4
其余数据不动
扩容步骤：
1. 新建 shard_4~7（空实例）
2. 从 shard_0 向 shard_4 迁移数据（user_id % 8 == 4 的记录）
从 shard_1 向 shard_5 迁移数据
...（可并行）
3. 双写：同时写 shard_0 和 shard_4（根据 user_id 决定）
4. 数据一致后，将路由切到 8 分片逻辑
5. 删除旧分片中已迁走的数据
// 翻倍扩容路由（过渡期）
func (s *ShardRouter) GetShardDuringExpansion(userID uint64) *gorm.DB {
oldShard := userID % 4
newShard := userID % 8
// 迁移状态：每个旧分片的迁移进度（从配置中心读取）
migrationDone := s.isMigrationDone(oldShard)
if migrationDone {
return s.newShards[newShard] // 迁移完成，路由到新分片
}
// 还在迁移中，根据 user_id 决定
if newShard >= 4 {
// 这部分数据需要从 shard_0 迁到 shard_4
// 查旧分片（迁移未完成时数据还在旧分片）
return s.oldShards[oldShard]
}
return s.oldShards[oldShard]
}
9. 中间件与框架选型
9.1 ShardingSphere（Java 生态主流）
Apache ShardingSphere 提供两种接入方式：
ShardingSphere-JDBC（应用内，无额外进程）
# 配置示例
dataSources:
ds_0: ... # order_db_0
ds_1: ... # order_db_1
rules:
- !SHARDING
tables:
orders:
actualDataNodes: ds_${0..1}.orders_${0..3}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: db_hash
tableStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: tbl_hash
shardingAlgorithms:
db_hash:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
tbl_hash:
type: INLINE
props:
algorithm-expression: orders_${user_id % 4}
ShardingSphere-Proxy（独立代理进程，对应用透明）
应用 → ShardingSphere-Proxy（MySQL 协议） → 各分片
应用无需修改任何代码，像访问单机 MySQL 一样
9.2 Vitess（Go 生态，YouTube 出品）
优势：
Google/YouTube 在生产大规模验证
支持自动化 resharding（扩容）
内置连接池管理
对 Kubernetes 友好
适合：
超大规模（YouTube 级别）
团队有运维 Vitess 的能力
9.3 应用层自实现（Go 小团队推荐）
对于中小规模业务，直接在应用层实现分片路由，无需引入复杂中间件：
// 见第 10 节完整实现
10. Go 实战：手写简单分片路由
以电商订单系统为例，实现一个完整的分库分表路由层。
10.1 配置与初始化
package sharding
import (
"context"
"fmt"
"sync"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
// ShardConfig 分片配置
type ShardConfig struct {
DBCount int // 分库数量
TableCount int // 每库分表数量
DSNs []string // 各库的 DSN（len = DBCount）
}
// ShardManager 分片管理器
type ShardManager struct {
cfg ShardConfig
dbs []*gorm.DB // 各分库的连接
totalShards int // 总分片数 = DBCount * TableCount
}
func NewShardManager(cfg ShardConfig) (*ShardManager, error) {
if len(cfg.DSNs) != cfg.DBCount {
return nil, fmt.Errorf("DSNs count must equal DBCount")
}
dbs := make([]*gorm.DB, cfg.DBCount)
for i, dsn := range cfg.DSNs {
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
Logger: logger.Default.LogMode(logger.Warn),
})
if err != nil {
return nil, fmt.Errorf("open db[%d]: %w", i, err)
}
sqlDB, _ := db.DB()
sqlDB.SetMaxOpenConns(100)
sqlDB.SetMaxIdleConns(10)
dbs[i] = db
}
return &ShardManager{
cfg: cfg,
dbs: dbs,
totalShards: cfg.DBCount * cfg.TableCount,
}, nil
}
// Route 根据 userID 返回对应的 DB 连接和表名
func (m *ShardManager) Route(userID uint64) (*gorm.DB, string) {
shardID := userID % uint64(m.totalShards)
dbIndex := shardID / uint64(m.cfg.TableCount)
tableIndex := shardID % uint64(m.cfg.TableCount)
tableName := fmt.Sprintf("orders_%d", tableIndex)
return m.dbs[dbIndex].Table(tableName), tableName
}
// RouteByOrderID 通过 order_id 路由（order_id 中编码了 shard_id）
func (m *ShardManager) RouteByOrderID(orderID uint64) (*gorm.DB, string) {
shardID := ExtractShardID(int64(orderID)) % int64(m.totalShards)
dbIndex := shardID / int64(m.cfg.TableCount)
tableIndex := shardID % int64(m.cfg.TableCount)
tableName := fmt.Sprintf("orders_%d", tableIndex)
return m.dbs[dbIndex].Table(tableName), tableName
}
// AllShards 遍历所有分片（用于广播查询）
func (m *ShardManager) AllShards() []ShardRef {
refs := make([]ShardRef, 0, m.totalShards)
for dbIdx := 0; dbIdx < m.cfg.DBCount; dbIdx++ {
for tblIdx := 0; tblIdx < m.cfg.TableCount; tblIdx++ {
refs = append(refs, ShardRef{
DB: m.dbs[dbIdx],
TableName: fmt.Sprintf("orders_%d", tblIdx),
ShardID: dbIdx*m.cfg.TableCount + tblIdx,
})
}
}
return refs
}
type ShardRef struct {
DB *gorm.DB
TableName string
ShardID int
}
10.2 数据模型与自动建表
// Order 订单模型（每个分片表结构相同）
type Order struct {
ID uint64 `gorm:"primaryKey"`
OrderNo string `gorm:"uniqueIndex;size:64;not null"`
UserID uint64 `gorm:"not null;index"`
ProductID uint64 `gorm:"not null"`
ProductName string `gorm:"size:255;not null"` // 冗余字段
UnitPrice float64 `gorm:"type:decimal(10,2);not null"`
Quantity uint32 `gorm:"not null"`
Amount float64 `gorm:"type:decimal(10,2);not null"`
Status string `gorm:"type:enum('created','paid','shipped','completed','cancelled');default:created"`
CreatedAt time.Time `gorm:"index"`
UpdatedAt time.Time
}
// AutoMigrate 在所有分片上建表
func (m *ShardManager) AutoMigrate() error {
for _, ref := range m.AllShards() {
if err := ref.DB.Table(ref.TableName).AutoMigrate(&Order{}); err != nil {
return fmt.Errorf("migrate shard %d: %w", ref.ShardID, err)
}
}
return nil
}
10.3 分片 ID 生成器
// IDGenerator 带分片信息的 ID 生成器
type IDGenerator struct {
mu sync.Mutex
nodeID int64
epoch int64
sequence int64
lastMillis int64
totalShards int64
}
func NewIDGenerator(nodeID int64, totalShards int) *IDGenerator {
epoch := time.Date(2024, 1, 1, 0, 0, 0, 0, time.UTC).UnixMilli()
return &IDGenerator{
nodeID: nodeID,
epoch: epoch,
totalShards: int64(totalShards),
}
}
// NextIDForUser 生成包含 shard 信息的 ID
// ID 结构：41bit时间戳 | 8bit shardID | 15bit 序列号
func (g *IDGenerator) NextIDForUser(userID uint64) (uint64, error) {
g.mu.Lock()
defer g.mu.Unlock()
shardID := int64(userID) % g.totalShards
now := time.Now().UnixMilli() - g.epoch
if now < g.lastMillis {
return 0, fmt.Errorf("clock moved backwards")
}
if now == g.lastMillis {
g.sequence = (g.sequence + 1) & 0x7FFF // 15bit
if g.sequence == 0 {
for now <= g.lastMillis {
now = time.Now().UnixMilli() - g.epoch
}
}
} else {
g.sequence = 0
}
g.lastMillis = now
// 41bit时间 | 8bit分片 | 15bit序列
id := (uint64(now) << 23) | (uint64(shardID) << 15) | uint64(g.sequence)
return id, nil
}
func ExtractShardIDFromOrderID(orderID uint64, totalShards int64) int64 {
return int64((orderID >> 15) & 0xFF) % totalShards
}
10.4 订单服务完整实现
type OrderService struct {
shards *ShardManager
idGen *IDGenerator
indexDB *gorm.DB // 二级索引库（单独的数据库，存储 shop->order 映射）
redis *redis.Client
log *zap.Logger
}
// CreateOrderReq 下单请求
type CreateOrderReq struct {
UserID uint64
ProductID uint64
ProductName string
UnitPrice float64
Quantity uint32
}
// CreateOrder 下单（核心流程）
func (s *OrderService) CreateOrder(ctx context.Context, req CreateOrderReq) (*Order, error) {
// 1. 生成全局唯一 ID（包含 shard 信息）
orderID, err := s.idGen.NextIDForUser(req.UserID)
if err != nil {
return nil, fmt.Errorf("generate order id: %w", err)
}
// 2. 生成全局唯一订单号
orderNo, err := s.generateOrderNo(ctx)
if err != nil {
return nil, fmt.Errorf("generate order no: %w", err)
}
order := &Order{
ID: orderID,
OrderNo: orderNo,
UserID: req.UserID,
ProductID: req.ProductID,
ProductName: req.ProductName,
UnitPrice: req.UnitPrice,
Quantity: req.Quantity,
Amount: req.UnitPrice * float64(req.Quantity),
Status: "created",
}
// 3. 路由到对应分片并写入
db, tableName := s.shards.Route(req.UserID)
if err := db.WithContext(ctx).Create(order).Error; err != nil {
return nil, fmt.Errorf("create order in shard (table=%s): %w", tableName, err)
}
s.log.Info("order created",
zap.Uint64("order_id", order.ID),
zap.String("order_no", order.OrderNo),
zap.String("table", tableName),
)
return order, nil
}
// GetOrderByID 按订单 ID 查询（从 ID 中解析分片，精准路由）
func (s *OrderService) GetOrderByID(ctx context.Context, orderID uint64) (*Order, error) {
db, _ := s.shards.RouteByOrderID(orderID)
var order Order
err := db.WithContext(ctx).Where("id = ?", orderID).First(&order).Error
if err != nil {
if errors.Is(err, gorm.ErrRecordNotFound) {
return nil, nil
}
return nil, err
}
return &order, nil
}
// ListUserOrders 查询用户订单列表（游标分页，精准路由，不跨分片）
func (s *OrderService) ListUserOrders(
ctx context.Context, userID uint64, cursor *OrderCursor, size int,
) (orders []Order, nextCursor *OrderCursor, err error) {
db, _ := s.shards.Route(userID)
query := db.WithContext(ctx).
Where("user_id = ?", userID).
Order("created_at DESC, id DESC").
Limit(size + 1)
if cursor != nil {
query = query.Where(
"(created_at < ? OR (created_at = ? AND id < ?))",
cursor.LastCreatedAt, cursor.LastCreatedAt, cursor.LastID,
)
}
if err = query.Find(&orders).Error; err != nil {
return nil, nil, err
}
if len(orders) > size {
last := orders[size-1]
nextCursor = &OrderCursor{LastID: last.ID, LastCreatedAt: last.CreatedAt}
orders = orders[:size]
}
return
}
type OrderCursor struct {
LastID uint64 `json:"last_id"`
LastCreatedAt time.Time `json:"last_created_at"`
}
// UpdateOrderStatus 更新订单状态（按 user_id 路由）
func (s *OrderService) UpdateOrderStatus(
ctx context.Context, orderID uint64, userID uint64,
oldStatus, newStatus string,
) error {
db, tableName := s.shards.Route(userID)
result := db.WithContext(ctx).
Where("id = ? AND user_id = ? AND status = ?", orderID, userID, oldStatus).
Update("status", newStatus)
if result.Error != nil {
return fmt.Errorf("update order status (table=%s): %w", tableName, result.Error)
}
if result.RowsAffected == 0 {
return fmt.Errorf("order not found or status not match: id=%d, expected=%s", orderID, oldStatus)
}
return nil
}
// CountOrdersByStatus 统计指定状态的订单数（广播查询所有分片）
func (s *OrderService) CountOrdersByStatus(ctx context.Context, status string) (int64, error) {
shards := s.shards.AllShards()
results := make(chan int64, len(shards))
errs := make(chan error, len(shards))
for _, ref := range shards {
go func(r ShardRef) {
var count int64
err := r.DB.WithContext(ctx).Table(r.TableName).
Where("status = ?", status).
Count(&count).Error
if err != nil {
errs <- err
return
}
results <- count
}(ref)
}
var total int64
for i := 0; i < len(shards); i++ {
select {
case count := <-results:
total += count
case err := <-errs:
return 0, err
case <-ctx.Done():
return 0, ctx.Err()
}
}
return total, nil
}
// generateOrderNo 生成全局唯一订单号（Redis SETNX 保证唯一性）
func (s *OrderService) generateOrderNo(ctx context.Context) (string, error) {
for i := 0; i < 5; i++ {
no := fmt.Sprintf("ORD%s%08d",
time.Now().Format("20060102"),
rand.Intn(99999999),
)
ok, err := s.redis.SetNX(ctx, "orderno:lock:"+no, "1", 24*time.Hour).Result()
if err != nil {
return "", err
}
if ok {
return no, nil
}
}
return "", errors.New("failed to generate unique order no after 5 retries")
}
10.5 初始化与使用示例
func main() {
// 初始化分片管理器（2库×4表=8分片）
shardMgr, err := NewShardManager(ShardConfig{
DBCount: 2,
TableCount: 4,
DSNs: []string{
"root:pass@tcp(127.0.0.1:3306)/order_db_0?charset=utf8mb4&parseTime=True",
"root:pass@tcp(127.0.0.1:3307)/order_db_1?charset=utf8mb4&parseTime=True",
},
})
if err != nil {
log.Fatal("init shard manager", zap.Error(err))
}
// 建表（生产环境用 DDL 工具，不用 AutoMigrate）
if err := shardMgr.AutoMigrate(); err != nil {
log.Fatal("auto migrate", zap.Error(err))
}
idGen := NewIDGenerator(1, 8) // nodeID=1, 8个分片
svc := &OrderService{
shards: shardMgr,
idGen: idGen,
// ... redis, indexDB
}
ctx := context.Background()
// 下单
order, err := svc.CreateOrder(ctx, CreateOrderReq{
UserID: 1001,
ProductID: 5001,
ProductName: "iPhone 15 Pro",
UnitPrice: 7999.00,
Quantity: 1,
})
if err != nil {
log.Fatal("create order", zap.Error(err))
}
fmt.Printf("订单创建成功: ID=%d, OrderNo=%s\n", order.ID, order.OrderNo)
// 按 ID 查询
found, _ := svc.GetOrderByID(ctx, order.ID)
fmt.Printf("查询订单: %+v\n", found)
// 查询用户订单列表
orders, cursor, _ := svc.ListUserOrders(ctx, 1001, nil, 10)
fmt.Printf("用户订单: %d 条, nextCursor=%v\n", len(orders), cursor)
// 统计所有分片
count, _ := svc.CountOrdersByStatus(ctx, "created")
fmt.Printf("待支付订单数: %d\n", count)
}
小结
分库分表决策树：
数据量/QPS 是否到瓶颈？
→ 没有：先加索引、读写分离、缓存
→ 是单表数据量大：分区表（按时间 RANGE，运维简单）
→ 是并发/QPS 高：垂直分库（按业务拆）
→ 垂直分库后单库仍然撑不住：水平分库分表（Sharding）
水平分片的核心原则：
1. 分片键选择最高频查询条件（通常是 user_id）
2. ID 中编码分片信息（避免映射表查询）
3. 非分片键查询走二级索引表或搜索引擎
4. 跨分片聚合走数仓或离线任务
5. 分片数选 2 的幂次（方便翻倍扩容，迁移量最小）
分库分表后必须解决的问题：
┌──────────────────┬────────────────────────────────┐
│ 问题 │ 解决方案 │
├──────────────────┼────────────────────────────────┤
│ 全局唯一 ID │ Snowflake（ID 中编码 shard_id） │
│ 跨分片查询 │ 二级索引表 + 并发查询合并 │
│ 分页 │ 游标分页 / 禁止深分页 │
│ 聚合统计 │ 预计算汇总表 / 数仓 │
│ 跨分片 JOIN │ 数据冗余 / 应用层 JOIN │
│ 分布式事务 │ 本地消息表（最终一致） │
│ 扩容 │ 翻倍扩容 + 双写迁移 │
│ DDL 变更 │ gh-ost / pt-osc 在各分片执行 │
└──────────────────┴────────────────────────────────┘