第12讲：分库分表与生产实战

目标： 掌握分库分表策略，了解分区表和生产最佳实践

---

开篇：主从架构也救不了的场景

上一讲我们搭好了主从架构，读写分离跑起来了，QPS扛住了。但新的问题来了：

订单表1亿行了。加了索引，查询还是慢。EXPLAIN一看，type=ALL，全表扫描。为什么？因为B+树太高了，单表数据太多，索引树也撑不住了。

更头疼的是，单库连接数经常打满。从库再多，写操作还是全压在主库上，主库的连接池成了瓶颈。

这时候，主从复制解决不了问题了。你需要的是——分库分表。

但分库分表不是银弹，搞不好会带来更多麻烦：跨库事务怎么办？全局ID怎么生成？扩容时数据怎么迁移？

这正是本讲要回答的问题。我们从"该不该分"开始，一步步讲清楚分片策略、核心难题、以及生产环境的最佳实践。

---

一、什么时候需要分库分表？

先问自己一个问题：真的需要分库分表吗？

分库分表不是银弹，会带来一堆麻烦事：跨库事务、跨表查询、全局ID、数据迁移...

分库分表的判断标准：

指标	阈值	说明
单表行数	>1000万	B+树高度增加，查询变慢
单库QPS	>5000	CPU 90%以上，扛不住了
单库连接数	接近max_connections	连接池打满

能不分就不分，分库分表是最后的手段。

先试试这些优化：

• 加索引、优化SQL
• 读写分离，分担读压力
• 冷热数据分离，归档历史数据
• 升级硬件，加内存加SSD

都试过了还是扛不住，再考虑分库分表。

---

二、分片策略：怎么拆？

2.1 垂直分表：按字段拆

把宽表拆成窄表，热数据和冷数据分离。

-- 原表：user（20个字段）
-- 问题：每次查询都要读取大量不需要的字段

-- 拆分后：
-- user_base（高频字段：id, username, password, status）
-- user_detail（低频字段：intro, hobby, address）

适用场景： 表字段太多，部分字段访问频率低。

2.2 水平分表：按行拆

把大表拆成多张结构相同的小表，这是最常用的方式。

哈希分片（推荐）：

-- 按user_id哈希，分成16张表
table_index = user_id % 16
-- user_id=10001 → orders_1
-- user_id=10002 → orders_2

优点： 数据分布均匀 缺点： 扩容麻烦（后面会讲解决方案）

范围分片：

-- 按月份分表
orders_202501: created_at在2025年1月
orders_202502: created_at在2025年2月

优点： 扩容简单，直接加新表 缺点： 数据分布不均，最近的表数据最多

2.3 分片策略选择

场景	推荐策略	原因
按用户查询为主	哈希分片（user_id）	同一用户的数据在同一分片
按时间查询为主	范围分片（时间）	同一时间段的数据在同一分片
需要归档历史数据	范围分片（时间）	直接删除旧分片

---

三、分库分表的核心难题

分库分表后，会遇到一系列问题。这些问题处理不好，比不分还麻烦。

3.1 跨分片查询：数据散了，怎么查？

问题场景：

按user_id分片，但运营要查"某个时间段的所有订单"。

-- 这条SQL需要查所有分片，然后合并结果，性能很差
SELECT * FROM orders WHERE created_at > '2025-01-01' ORDER BY created_at LIMIT 10;

解决方案：

方案	适用场景	实现方式
冗余表	查询维度固定	按时间再建一套分片表，双写
异构索引	多维度查询	用ES/ClickHouse做二级索引
基因法	订单号查询	订单号中嵌入user_id的分片信息

基因法示例：

// 假设分成4个分片，分片键是 user_id % 4
// 用户1001 → 1001 % 4 = 1 → 分片1

// 订单号生成时，把 user_id 嵌入订单号中
// 订单号格式：时间戳(13位) + user_id(4位) + 序列号(4位)
// 例如：1737194107000 1001 0001
//                     ^^^^ 这4位就是user_id

// 查订单时，从订单号中提取user_id，再计算分片
String orderId = "173719410700010010001";
String oderId = orderId.substring(13, 17);  // 提取 "1001"
int userId = Integer.parseInt(userId);       // 得到 1001
int shardIndex = userId % 4;                 // 得到 1，查分片1

核心思路：把 user_id 藏进订单号，查询时提取出来重新计算分片。这样即使将来扩容（4→8个分片），只要重新用 user_id 算一遍，照样能定位。

3.2 跨分片事务：数据在不同库，怎么保证一致性？

问题场景：

下单扣库存，订单表和库存表在不同分片。

// 订单表在分片A，库存表在分片B
orderDao.insert(order);      // 分片A
stockDao.decrease(productId); // 分片B
// 如果分片B失败了，分片A的数据怎么回滚？

解决方案：

方案	原理	优缺点
本地消息表	订单和消息在同一事务写入，定时任务发MQ	实现简单，无框架依赖
Seata AT	自动拦截SQL，生成回滚日志	开箱即用，侵入性低
Seata TCC	手动实现Try/Confirm/Cancel三个接口	性能最好，但开发成本高

本地消息表方案（推荐）：

-- 1. 本地事务：插入订单 + 消息（同一个库，保证原子性）
BEGIN;
INSERT INTO orders (...) VALUES (...);
INSERT INTO local_message (biz_id, status, content) VALUES ('order_001', 0, '扣库存');
COMMIT;

-- 2. 定时任务：扫描消息表，发送MQ
-- 3. 库存服务：消费MQ，扣减库存
-- 4. 回调：更新消息状态为已完成

3.3 全局表：每个分片都要用的数据怎么办？

问题场景：

省市区表、配置表，每个分片都要用，怎么办？

解决方案：

方案	优点	缺点
每个分片复制一份	查询快，无跨库	更新需要同步所有分片
独立库	更新简单	查询需要跨库
缓存	性能最好	需要处理缓存一致性

ShardingSphere广播表配置：

rules:
- !SHARDING
  broadcastTables:  # 广播表，自动同步到所有分片
    - sys_config
    - sys_region

---

四、分片扩容：4个分片不够用了，怎么办？

当初分了4个分片，现在每个分片数据量又超标了，需要扩到8个。

核心问题：老数据按 % 4 分布在4个分片里，新规则改成 % 8，数据怎么迁移过去？

4.1 翻倍扩容原理

翻倍扩容时，每个旧分片的数据只需要拆成两份，迁移逻辑最简单。

数学原理：user_id % 4 = 0 的数据，在 % 8 后要么是0，要么是4。所以分片0的数据只会去分片0或分片4，不会乱跑。

4.2 扩容步骤（不停服）

阶段	做什么	怎么做
1. 准备	新建4个空分片（4-7）	只是建库建表，不影响线上
2. 双写	新数据同时写新旧分片	中间件配置双写规则，或代码层面同时写两份
3. 迁移	把旧数据按新规则搬到新分片	用 Canal 监听 binlog 增量同步
4. 校验	确认新旧数据一致	抽样对比 + 全量 count
5. 切流	路由规则改成 % 8	先灰度10%流量验证，再全量切换
6. 清理	删除旧分片冗余数据	确认无问题后再清

4.3 迁移工具选型

场景	工具	说明
增量迁移	Canal	监听 binlog，实时同步变更
全量迁移	DataX	阿里开源，适合一次性大批量搬数据
一站式	ShardingSphere-Scaling	自动化扩缩容，省心但学习成本高

常用组合：先用 DataX 全量迁移历史数据，再用 Canal 增量同步迁移期间的新数据。

---

五、全局唯一ID：自增ID不能用了

5.1 问题：分表后ID会冲突

单表时用自增ID没问题。但分表后，每张表都有自己的自增序列：

orders_0: 1, 2, 3, 4...
orders_1: 1, 2, 3, 4...  ← 和 orders_0 的ID冲突了！

合并查询时ID重复，业务逻辑全乱了。

5.2 方案对比

方案	原理	优点	缺点
UUID	随机生成128位字符串	简单，不依赖外部服务	太长（36字符），无序，插入性能差
Redis自增	用 Redis 的 INCR 命令	简单，性能高	强依赖Redis，Redis挂了就完蛋
数据库号段	每次从DB批量取一段ID（如1-1000）	减少DB访问次数	实现复杂，需要额外的号段表
雪花算法	时间戳+机器ID+序列号	趋势递增，性能高，不依赖外部服务	依赖机器时钟，时钟回拨会出问题

推荐雪花算法：本地生成，不依赖外部服务，性能最高（单机400万QPS），且趋势递增对B+树索引友好。

5.3 雪花算法原理

结构： 64位 = 符号位(1) + 时间戳(41位) + 数据中心(5位) + 机器(5位) + 序列号(12位)

0 | 00000000000000000000000000000000000000000 | 00000 | 00000 | 000000000000
符号  时间戳（41位，约69年）                    数据中心  机器ID   序列号（4096/ms）

各部分含义：

• 符号位：固定为0，保证ID是正数
• 时间戳：毫秒级，41位可用约69年
• 数据中心+机器ID：共10位，支持1024台机器
• 序列号：12位，同一毫秒内可生成4096个ID

使用方式：

• ShardingSphere 内置支持
• 美团 Leaf
• 百度 UidGenerator

---

六、分区表：不用中间件的"伪分表"

什么是分区表？

分区表是MySQL内置的功能，把一张大表的数据分散存储到多个物理文件，但对应用完全透明，SQL不用改。

CREATE TABLE orders (
  id BIGINT,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
  PARTITION p202501 VALUES LESS THAN (202502),
  PARTITION p202502 VALUES LESS THAN (202503),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

分区表的杀手锏：秒删历史数据

传统DELETE：

DELETE FROM orders WHERE created_at < '2024-01-01';
-- 1亿行数据，删除2小时，还会产生大量binlog

分区DROP：

ALTER TABLE orders DROP PARTITION p202401;
-- 0.5秒搞定，不产生binlog

分区裁剪：只扫描需要的分区

SELECT * FROM orders WHERE created_at >= '2025-05-01';
-- 只扫描p202505分区，其他分区不碰

分区表 vs 分表

特性	分区表	分表
实现方式	MySQL内置	中间件（ShardingSphere等）
SQL改动	无需改动	需要路由
跨分区查询	MySQL自动处理	需要中间件合并
适用场景	按时间归档、删除历史数据	按用户分片、突破单库限制

建议： 如果只是为了删除历史数据方便，用分区表就够了，不需要上分库分表。

---

七、生产最佳实践

表设计规范

规范	正确做法	错误做法
表名	user_order	UserOrder
主键	BIGINT（雪花算法）	UUID
字符集	utf8mb4	utf8
金额	DECIMAL(10,2)	FLOAT
空值	NOT NULL + DEFAULT	允许NULL

索引规范

• 命名：idx_字段名、uk_字段名
• 数量：单表索引<5个
• 顺序：区分度高的在前

SQL规范

规范	正确做法	错误做法
SELECT	只查需要的字段	SELECT *
WHERE	避免函数	YEAR(date)=2025
分页	避免深分页	LIMIT 10000,10

监控指标

-- QPS
SHOW GLOBAL STATUS LIKE 'Questions';

-- 连接数（建议<80%）
SHOW STATUS LIKE 'Threads_connected';

-- 慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

---

八、避坑指南

坑1：分片键选择不当

问题： 按status分片（只有0/1/2），数据分布不均。

正确做法： 按user_id分片（区分度高）。

坑2：分片数量不合理

问题： 分成3张表，扩容时数据迁移量大。

正确做法： 分成2的幂次（4/8/16/32）。

坑3：分片键选了会变化的字段

问题： 用订单状态（status）做分片键，状态从0变1后，数据应该在哪个分片？

正确做法： 分片键选不会变化的字段，如 user_id、order_id。

坑4：跨分片查询性能差

问题： 非分片键查询需要扫描所有分片，性能很差。

正确做法： 用ES做二级索引，或者冗余表。

坑5：扩容时数据不一致

问题： 扩容期间双写，但新旧分片数据不一致。

正确做法： 双写期间做数据校验，确认一致后再切换路由。

---

九、作业

基础题

设计分片策略：订单表1亿行，90%按user_id查询。选择分片键、分片数量，说明理由。

进阶题

创建按月分区的订单表，验证DROP PARTITION删除历史数据的速度。

---

十、专栏总结

写在最后

时至今日，历时两个多月，这个专栏终于完结了。

从第一讲"把MySQL当成Excel来学"开始，到现在的分库分表与生产实战，我们一起走过了12讲的旅程。说实话，写专栏远比我想象中难——不是写完就行，而是要把自己真正理解的东西，用别人能听懂的话讲出来。有些知识点我自以为很熟，真要落笔时才发现还有模糊地带。这个过程倒逼我重新翻文档、查源码、做实验，反而让自己学得更扎实了。

感谢每一位读到这里的你。无论你是从头追到尾，还是挑了几讲来看，都希望这些内容对你有所帮助。

如果这个专栏让你对MySQL有了更深的理解，或者在面试、工作中帮到了你，那就是我最大的收获。

12讲回顾

模块	讲次	主题
入门篇	1-3讲	MySQL基础、环境搭建、增删改查
核心原理篇	4-9讲	窗口函数、事务、索引原理、架构、日志与MVCC
实战优化篇	10讲	锁机制与死锁解决
架构扩展篇	11-12讲	主从复制、分库分表、生产实战

学习建议

• 基础必修：1-5讲（SQL基础、事务）
• 面试重点：6-7讲（索引原理与优化）、9讲（MVCC）
• 进阶选修：11-12讲（架构扩展，按需学习）

---

专栏完结，江湖再见！ 🎉