SQL进阶之分库分表：应对海量数据的拆分策略

可视化演示

动态演示SQL进阶之分库分表的核心机制，通过输入/处理/存储/输出四种模式展示关键概念与交互流程。

拆分背景与动机

老王的后厨一天能稳定接 500 单，这是他的“单库单表”上限。订单涨到 800 单那天，厨师累到颠勺的手都在抖，出餐慢、差评多、食材堆积——后厨从“够用”变成了“瓶颈”。

数据库的世界一模一样。一张表的数据量从百万涨到千万、亿级时，磁盘容量的天花板、CPU 和 I/O 的争抢、连接池的耗尽，会让每条 SQL 像高峰期的外卖骑手一样寸步难行。判断要不要拆，不是拍脑袋，而是先算账：一天新增多少行、每行占多大空间、要在线保留多久。

$$\displaystyle \text{数据总量} = \text{日增行数} \times \text{平均行大小} \times \text{保留天数}$$

1. 日增行数：每天新增写入的数据行数，比如订单表每天新增 50 万行

$$\text{日增行数} = 5 \times 10^5$$

2. 平均行大小：单条记录占用的字节数，等于各字段长度累加再加上索引开销

$$\text{平均行大小} = \sum_{j=1}^{k} \text{字段长度}_j + \text{索引开销}$$

3. 保留天数：业务要求在线提供查询的天数，比如交易流水要保留 730 天

$$\text{保留天数} = 730$$

4. 数据总量：三者相乘得到总存储需求，拿它和单机磁盘、单表查询性能对比

$$\text{数据总量} = \text{日增行数} \times \text{平均行大小} \times \text{保留天数}$$

除了存储账，还要看性能账：慢查询变多、索引优化后仍不达标、备份时间长得无法接受，这三个信号亮灯时，才轮到“拆分”登场。记住一个原则：先升级硬件、优化索引、加缓存、做主从分离，都试过了还不行，再动刀拆库。

老王最后决定：先加了一台猛火灶（升配），撑过旺季；同时请来数据分析员，把半年后的订单量估算出来。数据说话，才知道后厨是“升级”能救，还是必须“连锁化”——这就是先量化、再决策的工程思维。

垂直拆分策略

老王没急着开分店，而是先改造厨房：凉菜、热菜、烧烤、甜品各设一个独立档口，各管一摊、互不争抢灶台。这就是“垂直拆分”——按菜系（业务）分工，让每个档口只专注自己的一类活。

数据库的垂直拆分也分两层。第一层是垂直分库：把用户、商品、订单等不同业务域的表，拆到不同的数据库实例里，各自独立部署、独立扩容。第二层是垂直分表：一张列特别多的“宽表”，按字段的冷热程度拆成两张窄表——高频查询的核心字段放一张，很少查询的大文本字段放另一张。

$$\displaystyle \text{宽表} = \text{核心字段} \cup \text{低频大字段}$$

1. 宽表：原本一张表塞下全部字段，既有高频的小字段，也有很少读的大文本

2. 核心字段：高频查询和更新的列，如订单号、金额、状态

$$\text{核心字段} = \{ \text{order\_id}, \text{amount}, \text{status} \}$$

3. 低频大字段：很少参与查询的大字段，如商品详情、订单备注

$$\text{低频大字段} = \{ \text{detail}, \text{remark} \}$$

4. 并集：拆开后两张表各自存储，通过主键 JOIN 又能拼回完整信息

$$\text{宽表} = \text{核心字段} \cup \text{低频大字段}$$

垂直拆分的好处立竿见影：每个库的表少了、锁竞争小了、职责单一了，某个业务过热也不会拖垮其他业务。但老王很快发现一个新麻烦：一份婚宴订单要同时找热菜档和甜品档要菜，跨档口协调开始变得复杂。对应到数据库，就是跨库 JOIN 和跨库事务的难题——垂直拆分把“大而全”变成了“小而专”，代价是协作成本。

水平拆分策略

档口改造救不了订单量翻倍的事实。老王一咬牙：开分店！四家分店卖一模一样的菜谱，按顾客的居住城区分配订单。每家分店的压力瞬间只有原来的四分之一。这就是“水平拆分”——表结构完全相同，按行把数据拆到多个库或表中。

水平拆分有三种常见的“派单规则”。第一种是范围分片：按主键区间切分，ID 1 到 1000 进店 1，1001 到 2000 进店 2，简单直观但容易产生“尾部热点”（最新数据总挤在最后一个分片）。第二种是哈希取模，也是用得最广的一种：

$$\displaystyle \text{shard\_id} = \text{hash}(\text{shard\_key}) \bmod N$$

1. shard_key：分片键，决定这一行走哪个门，比如“买家 ID”或“订单号”

2. hash()：把任意类型的分片键映射成一个尽量均匀的大整数

$$\text{hash}(\text{user\_id}) \rightarrow [0, 2^{32})$$

3. mod N：对大整数取模，结果只会落在 0 到 N-1 之间

$$\text{shard\_id} \in \{0, 1, \dots, N-1\}$$

4. N：分片数量，取模结果决定了这条记录最终进入哪个分片

第三种是一致性哈希：把节点和数据都映射到一个哈希环上，数据顺时针找最近的节点。它的价值在扩容时体现——后面讲“实践与演进”时会细说。哈希取模均匀、简单，但有一个致命伤：一旦分片数 N 变化，几乎所有数据的归宿都要重算。老王现在有四家店，将来开到八家时，老顾客的“常去门店”全得换——这就是取模分片的扩容之痛。

分片键选择

老王给四家分店分配订单，靠的是顾客“居住城区”。但如果按“点菜数量”分——点得多的去一店、点得少的去二店——大胃王们会全挤进一店，其他店空转。派单规则错了，开再多分店也白搭。数据库里，这个派单规则就是“分片键”。

选分片键有三条铁律。第一，高频查询维度：绝大多数查询的 WHERE 条件都要能带上它，比如“我的订单”按买家 ID 查，那就用买家 ID 做分片键；第二，分布均匀：字段的取值基数要大、不倾斜，避免某个分片变成热点；第三，关联收敛：经常一起 JOIN 的表，或者需要保证事务一致性的数据，尽量用同一个分片键，让它们落在同一分片。可以用一个公式量化“均匀”这件事：

$$\displaystyle \text{均匀度} = 1 - \frac{\sum_{i=1}^{N} |c_i - \bar{c}|}{N \times \bar{c}}$$

1. c_i：第 i 个分片里实际存储的行数

2. 平均值：所有分片行数的算术平均，是均匀的基准线

$$\bar{c} = \frac{1}{N}\sum_{i=1}^{N} c_i$$

3. |c_i - \bar{c}|：每个分片偏离均值的程度，偏离越大说明越不均衡

4. 分母 N×\bar{c}：理论最大总偏差，用来把结果归一化到 0 到 1 之间

5. 均匀度：越接近 1 说明数据铺得越平均，越接近 0 说明热点严重

选分片键还有一个隐含约束：它几乎不可更改。数据一旦按某个键散落四处，换键等于全量重排。所以上线前要拿历史数据跑一遍均匀度验证，就像老王先在几个小区试点，看看按城区划分后每家店的工作量是不是真的接近，再正式全面铺开。

路由与全局主键

四家分店开起来后，老王发现麻烦了：每家店都从 1 号开始自己给小票编号，结果两家店都发出过“第 100 号”小票，顾客拿着小票来投诉对不上账。解决办法是买一台总台取号机统一发号，再按城区把单派给各分店。数据库世界的对应物，就是“全局主键”和“路由中间件”。

路由很好理解：一条 SQL 进来，中间件（如 ShardingSphere、MyCat）根据分片键算出 shard_id，把 SQL 改写成“去第几个分片执行”，再下发到对应库。但全局主键不能用数据库自增了——每个分片各自递增，必然撞号。业界最常用的是雪花算法（Snowflake），它生成的 64 位 ID 长这样：

$$\displaystyle \text{id} = (\text{timestamp} - \text{epoch}) \times 2^{22} + \text{worker\_id} \times 2^{12} + \text{sequence}$$

1. timestamp - epoch：相对固定起始时间的时间戳毫秒值，保证 ID 随时间单调递增

2. 左移 22 位：时间戳乘以 2 的 22 次方，给机器号和序列号腾出位空间

$$(\text{timestamp} - \text{epoch}) \times 2^{22}$$

3. worker_id × 2^{12}：机器编号左移 12 位，让不同分片上的生成器互不冲突

4. + sequence：同一毫秒内的自增序号，12 位最多支持 4096 个

$$\text{sequence} \in [0, 4095]$$

有了总台取号机，小票号全网唯一；有了雪花算法，订单号跨库唯一且大致递增。更妙的是，订单号本身就能当分片键：解析出时间戳，还能顺便做按时间段的查询。老王体验到了“总台统一管理”的甜头，但他也明白，总台再聪明，也挡不住“顾客想同时问四家店有没有某道菜”——这就是跨库查询的难题。

跨库查询处理

总有刁钻的顾客打电话到总台：“你们哪家分店还有招牌烤鸭？”总台只能一家一家打电话问，再把四家的回答汇总、排序、去重。这就是跨库查询的“广播查询 + 结果归并”。数据库层面，原本一条 SQL 就能完成的 JOIN、子查询、排序，现在变成多分片并发查询加应用层拼装。

分库分表后有几条“新规矩”：第一，禁止跨库 JOIN，两个分片里的表没法在一条 SQL 里关联，要在应用层先查 A 库再查 B 库组装；第二，非分片键查询要广播到所有分片，中间件把结果 UNION 后归并；第三，深分页是最大的坑。用户要看第 100 页的订单，你不能只让每个分片取第 100 页，而是每个分片都要把前 100 页的数据全取出来再归并。候选行数的膨胀是惊人的：

$$\displaystyle \text{候选行数} = N \times (m \times \text{page\_size})$$

1. N：参与查询的分片数量，分片越多乘数越大

2. m：目标页码，页码越深，乘数越夸张

3. page_size：每页返回的行数

$$m \times \text{page\_size} = \text{全局偏移量}$$

4. 乘积：每个分片都要取足够的行回内存排序，再丢弃大部分

$$\text{候选行数} = N \times (m \times \text{page\_size})$$

举个例子：4 个分片、每页 20 条、查第 100 页，候选行数是 4 × 100 × 20 = 8000 行，而用户真正要的只有 20 条。页数越深，浪费越大，接口延迟直线上升。实际工程里，要么禁止深分页（最多翻到第 N 页），要么改成“游标翻页”（基于上次查询的最后一条 ID 往下拉），要么把深分页的需求改造成离线报表。老王给总台立了规矩：跨店查询可以，但只许问“每家前 20 条”；要全量对账，走夜里统一盘点。查询规范，从拆分第一天就要立好。

分布式事务

老王的绝活是承办婚宴，一桌婚宴要四家分店各出两道菜。如果热菜店菜都下锅了，甜品店却突然打烊，整场宴席就砸了。总台的方案是：先挨家确认“能不能接单”（准备阶段），全部确认后统一下令“开火”（提交阶段）；只要有一家说不行，全部取消（回滚）。这就是分布式事务里的两阶段提交（2PC）。

两阶段提交把一次跨库写操作拆成两个阶段：第一阶段协调者让所有参与者执行 Prepare，各自锁住资源并上报“准备好了”；第二阶段协调者根据反馈，要么给所有参与者发 Commit，要么发 Rollback。它保证了“要么全成功，要么全失败”：

$$\displaystyle \text{事务成功} = \bigwedge_{i=1}^{N} \text{本地事务}_i$$

1. 本地事务_i：第 i 个分片或服务内部独立完成的事务

2. 逻辑与(∧)：所有本地事务必须全部成功，整体才算成功

$$\text{结果}_i \in \{0, 1\}$$

3. N：参与跨库事务的分片数量

4. 事务成功：N 个参与者全部成功才提交；任一失败则整体回滚或补偿

2PC 看起来很完美，但互联网高并发场景很少直接用：Prepare 阶段锁资源，协调者一旦宕机，参与者会长时间阻塞；而且分片越多，延迟越高。所以现代架构更常用“柔性事务”——放弃强一致，追求最终一致。比如本地消息表：先写业务表和消息表（同一个本地事务），再异步把消息发给其他分片；又比如 TCC（Try-Confirm-Cancel）：先冻结资源，再确认扣减，失败则补偿退回。老王后来也想通了：婚宴这种强一致场景一年没几场，外卖才是大头。外卖他改成“先接单、后台对账”——先告诉顾客“已下单”，系统异步核对，偶尔差一单也能补。生意经和数据库事务，本质都是取舍。

实践与演进

老王要把账本从一家总店迁到四家分店，还不能停业一天。他安排了一个稳妥的流程：新订单老账本和新账本同时记（双写）；把历史台账分批抄到新账本（回填）；每天对账，发现差异就修正；对账无误后，把查询流量逐渐切到新账本（灰度切换）；最后老账本归档退役。这就是生产环境分库分表的经典迁移四步曲。

迁移过程中还有一个扩容问题。假设老王要从四家店扩到八家店，哈希取模的规则一变，几乎所有老顾客的分店归属都要重算，等于全量搬家。而一致性哈希只在哈希环上新增一个节点，它只接管环上的一小段弧，因此只有一小部分数据需要迁移：

$$\displaystyle \text{迁移比例} \approx \frac{1}{N+1}$$

1. 迁移比例：扩容时路由结果发生变化、需要搬迁的数据占全量的比例

2. N：扩容前的分片数量，N 越大迁移比例越小

3. ≈ 1/(N+1)：一致性哈希新增节点只接管环上一段弧，该段内的数据需要迁移

$$\text{迁移比例} \approx \frac{1}{N+1}$$

4. 对比取模：哈希取模扩容时几乎所有 key 的取模结果都变，迁移接近全量

这就是一致性哈希在“平滑演进”上的核心价值：把扩容的代价从“伤筋动骨”降到“局部微调”。当然它也有短板——节点少时分布可能不均，需要引入“虚拟节点”来打散。最后是容量规划：别等数据库报警才动手，按业务增速预估未来 6 到 12 个月的数据量，提前设计分片数，给“分店地图”留好扩展位。老王的连锁店从四家开到八家，每家只交接一小批老客户，营业一天没停。分库分表的最高境界，就是让扩容像连锁店加招牌一样，平静、有序、不打扰生意。

AI 应用场景

Python 演示

import hashlib  class Shard:    """模拟一个分片(相当于一张分表或一个分库)"""     def __init__(self, name):        self.name = name        self.rows_map = {}     def insert(self, row_id, row):        self.rows_map[row_id] = row     def query(self, row_id):        return self.rows_map.get(row_id)  class ShardRouter:    """根据分片键把数据路由到对应分片, 并支持跨分片归并"""     def __init__(self, shard_count):        self.shard_count = shard_count        self.shards_list = [Shard("shard_%d" % i) for i in range(shard_count)]     def shard_id(self, key):        digest = hashlib.md5(str(key).encode("utf-8")).hexdigest()        return int(digest, 16) % self.shard_count     def insert(self, row_id, row):        s = self.shard_id(row_id)        self.shards_list[s].insert(row_id, row)     def query_by_id(self, row_id):        s = self.shard_id(row_id)        return self.shards_list[s].query(row_id)     def query_all(self, predicate):        total = 0        for shard in self.shards_list:            for row_id, row in shard.rows_map.items():                if predicate(row):                    total += 1        return total  def main():    router = ShardRouter(4)    rows = [        {"id": 1001, "city": "北京", "amount": 88},        {"id": 1002, "city": "上海", "amount": 120},        {"id": 1003, "city": "深圳", "amount": 66},        {"id": 1004, "city": "北京", "amount": 210},        {"id": 1005, "city": "杭州", "amount": 45},    ]    for row in rows:        router.insert(row["id"], row)     print("按主键查询 1003:", router.query_by_id(1003))    print("跨分片统计 amount>100 的单数:", router.query_all(lambda r: r["amount"] > 100))    for i, shard in enumerate(router.shards_list):        print("%s 行数: %d" % (shard.name, len(shard.rows_map)))  if __name__ == "__main__":    main()

1. class Shard:模拟一个分片，相当于老王的一家分店，内部用字典存数据。
2. self.rows_map = {}用字典模拟分片内的行存储，主键(row_id)是字典的 key。
3. def shard_id(self, key):路由算法核心：对分片键做 MD5 哈希再取模，算出该去哪个分片。
4. return int(digest, 16) % self.shard_count把哈希十六进制串转成整数并取模，得到 0 到 N-1 的分片号。
5. self.shards_list[s].insert(row_id, row)按计算出的分片号，把行写入对应分片。
6. def query_all(self, predicate):跨分片查询：遍历所有分片再汇总，对应文中“广播查询 + 归并”。

常见误区

面试题