第 26 章 复制、高可用与分库分表

单机数据库的容灾、读扩展、单表容量三个天花板，分别对应复制、高可用与分库分表。本章只讲它们的丢数据窗口与失效边界。

学习目标

读完本章，你应当能够：

1. 推导异步、半同步（AFTER_SYNC / AFTER_COMMIT）与组复制各自的丢数据窗口，判断给定崩溃时刻会不会丢"已提交"数据；
2. 画出主库 dump 线程、从库 IO（receiver）线程与 SQL（applier）线程的分工，解释 relay log 为什么必须存在，以及 GTID 相对位点加偏移量的三项优势；
3. 对"主从延迟很大"按五个原因逐项排查，并用 WRITESET 说明并行复制为什么能做到行级粒度；
4. 指出 Seconds_Behind_Master 的失真场景，改用 pt-heartbeat、GTID 差集等口径；
5. 判断一次网络分区会不会造成脑裂，给出预防手段与切换后的一致性校验流程；
6. 用行宽与 buffer pool 算例判断是否需要分表，选路由规则，并为跨库 JOIN、跨库分页、分布式事务各给方案与代价。

前置知识检查

26.1 三种复制模式：丢数据窗口由"何时返回客户端"决定

复制的选型不是比谁更快，而是回答一个问题：主库返回成功那一刻，这条已提交的事务最多可能在几台机器上存在？ 三个模式的差别全部落在"ACK 与 commit 的先后"上。

半同步的两种时机是高频追问点，务必能画出时序：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

AFTER_COMMIT（旧默认，5.7.2 之前）

  客户端 ── COMMIT ──▶ 主库：写 binlog → 引擎提交（其他会话此刻已能读到新值）
                        │ dump 线程发送事件
                        │ 等从库 ACK（最多 rpl_semi_sync_master_timeout，默认 10000 ms）
  客户端 ◀── 成功 ──────┘
  崩溃点在"引擎已提交"与"收到 ACK"之间：
    本地已提交、已被其他会话读取，但故障切换到没收到该事务的从库
    ⟹ 已提交数据凭空消失。这是最坏的一类不一致（读到过的东西后来又没了）

AFTER_SYNC（5.7.2 起默认）

  客户端 ── COMMIT ──▶ 主库：写 binlog → 发事件给从库
                        │ 等 ACK
                        │ 收到 ACK 后才提交引擎、才返回客户端
  崩溃点在等 ACK 期间：
    本地从未提交 ⟹ 不存在"已提交又丢失"，客户端只看到失败
    但已 ACK 的从库上可能已有该事务 ⟹ 切换后它存在，客户端重试即产生重复写
    ⟹ AFTER_SYNC 把"丢数据"换成了"可能重复"，而重复可以用幂等键解决

MGR 与半同步的本质区别在于谁能单方面决定提交。半同步里主库是唯一决策者，从库只回 ACK；MGR 里提交需要组内多数派认证（certification）通过，认证时会检查事务的写入集合（writeset）是否与并发事务冲突，冲突方直接被回滚。group_replication_single_primary_mode=ON（默认）时只有一个可写成员，冲突基本只发生在切换瞬间；OFF（多主）时必须同时开 group_replication_enforce_update_everywhere_checks=ON，此时间隙锁语义与 SERIALIZABLE 不被支持，冲突写入在 commit 阶段被拒——所以多主并不是"随便写哪个节点"，而是把冲突检测推迟到了提交时。

26.2 线程模型、relay log 与 GTID

复制的执行体是三个线程加一个文件，模型不复杂，但每个组件的存在理由都是面试点。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

       主库（source）                                    从库（replica）
 ┌───────────────────────────┐                  ┌──────────────────────────────────┐
 │ 客户端事务提交             │                  │                                  │
 │        │                  │                  │  ┌────────────────────────────┐  │
 │        ▼                  │                  │  │ IO 线程（receiver）        │  │
 │   binlog（按 event 追加）  │   ① 复制连接      │  │ · 向主库请求"从某位点开始   │  │
 │        │                  │ ◀───────────────▶│  │   的后续 binlog"           │  │
 │        ▼                  │   ② 事件流        │  │ · 收到的 event 顺序写       │  │
 │  dump 线程（binlog dump）  │ ────────────────▶│  │   relay log                │  │
 │  · 持锁读 binlog           │   ③ ACK（半同步） │  │ · 更新已拉取位点（receiver  │  │
 │  · 按 event 顺序发送       │ ◀────────────────│  │   status / slave_master_info）│ │
 │  · 收到 ACK 后推进位点     │                  │  └──────────────┬─────────────┘  │
 └───────────────────────────┘                  │                 ▼                │
  注意：dump 线程不读数据页、    │                │        relay log（本地文件）      │
  不重放事务，它只搬运 event      │                │                 ▼                │
                                               │  ┌────────────────────────────┐  │
                                               │  │ SQL 线程 / worker 池        │  │
                                               │  │ · 读 relay log，按事务重放   │  │
                                               │  │ · 记录已执行位点（applier    │  │
                                               │  │   status / slave_relay_log_ │  │
                                               │  │   info）                    │  │
                                               │  └──────────────┬─────────────┘  │
                                               │                 ▼                │
                                               │        存储引擎（重放后的数据）    │
                                               └──────────────────────────────────┘

relay log 为什么不能省。 直觉方案是"IO 线程收到事件直接交给 SQL 线程重放"，省掉一次落盘。实际不能这么做，四个理由：

1. 拉取与重放必须解耦。 二者速度不同：IO 线程受网络带宽限制（同机房 RTT 约 0.1~0.5 ms），SQL 线程受磁盘与锁限制。解耦后从库可以在主库切换、网络中断时继续重放已拉取的部分，而不是卡在网络读上。
2. 位点必须能崩溃恢复。 relay log 是本地文件，SQL 线程执行到哪个位置可以持久化，重启后从该位置续跑；网络流没有"可回退的本地副本"。
3. 并行重放需要随机访问。 多个 worker 要各自从 relay log 里取不同事务，必须有可随机读、可分段定位的本地文件，网络流只能顺序消费。
4. 主库的 binlog 会过期。 主库受 binlog_expire_logs_seconds 约束会清理旧文件；从库把事件落地后，dump 线程才敢让主库释放更早的 binlog（Relay_Log_Space 与主库清理策略是两件事）。

GTID 相对位点的优势。 位点式复制要记住 binlog file + pos（如 mysql-bin.000042:197023），GTID（global transaction identifier）把标识变成 source_uuid:transaction_id 的集合，从库只需 SOURCE_AUTO_POSITION=1，主库自动算出"我有哪些、你缺哪些"再补发。三项优势：

• 故障切换后自动找位点。 从库指向新主库时不必人工算位点，也就不会算错；位点式切换最常见的故障就是位点算错导致重复执行或跳过事务。
• 幂等。 事务的 GTID 一旦在 gtid_executed 中，重复收到会被跳过，这正是 26.1 里 AFTER_SYNC 造成的"重复投递"能被消化掉的原因。
• 拓扑重建简单。 gtid_purged 表达"这台机器上已经确定不存在的事务"，当从库所需的 binlog 已被主库清理时报错信息明确，而位点式只会得到一个错位的沉默结果。

26.3 主从延迟的五个原因与并行复制

延迟是复制唯一的常态问题。回答"延迟大怎么办"之前，必须先定位是哪一类原因，因为五类的解法互不通用。

并行复制的关键是"哪些事务可以乱序重放"。从库乱序的前提是这些事务在主库上本来就不冲突，而 binlog 里恰好记录了判断依据（last_committed 与 sequence_number）。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

 主库的提交流（binlog_transaction_dependency_tracking 决定依赖信息怎么算）

   会话 A： t1 改 user(id=1)        t3 改 user(id=1)     ← 与 t1 同主键 ⟹ 必须保序
   会话 B： t2 改 user(id=7)        t4 改 order(id=5)    ← 与 A 无交集 ⟹ 可与 t1 并行

   COMMIT_ORDER（默认）：只按主库组提交分组。
     同组提交 ⇒ 无锁冲突 ⇒ 从库可并行。若主库 QPS 低、提交不重叠，
     一组里常常只有 1 个事务 ⟹ 退化成串行。粒度 = "组"。
   WRITESET：为每个事务算出写入行的哈希集合（transaction_write_set_extraction，
     XXHASH64），只有 writeset 有交集才保序 ⟹ 上例 4 个事务里 3 个可在从库并行。
     粒度 = "行"，比按库并行（DATABASE）细几个数量级。
   WRITESET_SESSION：在 WRITESET 上追加"同一会话内必须保序"，
     因为同一连接里 t1 与 t3 可能有业务因果（先插入后更新）⟹ 上例 A 的两个事务永不并行。

 从库 worker 池（replica_parallel_workers = 4；5.7 默认 0，8.0.27 起默认 4）

   worker0: t1 ──▶ t3        worker1: t2
   worker2: t4               worker3: 空闲
   replica_parallel_type = LOGICAL_CLOCK（8.0 默认；5.7 默认 DATABASE 按库并行）
   replica_preserve_commit_order = ON ⇒ 各 worker 仍按主库提交顺序进入 commit，
     否则从库上的可见顺序与主库不同，基于从库的一致性读会读到"未来"的数据

slave_parallel_type=DATABASE 的致命局限要能一句话说清：它按库分 worker，同一个库上的事务必须进同一个 worker。真实业务往往是"一个库、几十张表"，于是并行度恒为 1；这也是它被 LOGICAL_CLOCK 取代的原因。WRITESET 的局限同样要能说：writeset 由主键或唯一键计算，没有主键的表算不出行标识，只能退化为与所有事务冲突（等于串行），所以"每张表都要有显式主键"不是风格建议而是并行复制的前提。

26.4 Seconds_Behind_Master 为什么不准

它的算法简单到可以一句话写出来：当前时间 − SQL 线程正在执行的这个 event 的时间戳（时间戳在 event 头部，由主库写入时打上）。由此得到三类失真：

因此它既不是"从库落后主库多少秒"，也不是"数据多久后可见"。真实可用的替代口径：

26.5 读写分离下的延迟读问题

延迟在业务上的可观测形态主要有三种：写后读（同一会话写完立刻读，读到旧值，破坏 read-your-writes）、单调读（同一会话两次读，第二次反而更旧）、因果读（读到了 A 的评论，却没读到 A 的帖子）。解法按代价排序：

26.6 高可用、故障切换与脑裂

脑裂的成因是"判定与事实不一致"：主从之间网络分区，主库自己仍在正常接受写入，而探测方（管理节点或另一侧集群）判定主库已死并提升从库，于是出现两个可写节点，双写造成的分叉无法用重放修复。预防手段分三类：多数派（MGR/Raft 下少数派无法提交，从机制上不可能双写）、fencing/STONITH（切换前先把旧主隔离掉：关机、断网卡、撤销 VIP、SET GLOBAL super_read_only=1 并回收写入账号）、仲裁与探测策略（引入第三方仲裁节点投票，多次探测并校验 read_only，避免单次网络抖动就触发切换）。云托管方案通常把 fencing 做在 VIP/代理层，这也是"切过去以后为什么必须重建连接"的原因。

切换完成后必须做一致性校验，因为补差逻辑可能漏事件：pt-table-checksum 在主库按块计算每行的校验和并写入结果表，该表随复制流到从库，从库据此比较出差异块；pt-table-sync 依据校验结果生成补齐 SQL（先用 --print 审阅，再 --replicate 执行）。两个工具都会给主库加压力，要用 --chunk-size、--max-load、--pause-file 限速，并且它们只能发现数据差异，不能发现结构差异。

26.7 分库分表的拆分维度与路由

垂直拆分先做，因为它不动数据行：垂直分库按业务域把表拆到不同实例（用户库、订单库、商品库），代价是原来一个库内的 JOIN 变成跨库、单库事务变成分布式事务；垂直分表按列拆，把大字段（详情、JSON、附件元信息）挪到扩展表，目的是让主表行更短——行越短一页放的行越多，树高与随机 IO 次数就越低（B+ 树原理见第 23 章 23.2 节）。

水平拆分才是解决"单表放不下"的手段，但"单表 2000 万行"不是阈值，它是某个假设下的估算结果。这个数字来自三层 B+ 树、行宽约 1 KB 的算例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

 假设：主键 BIGINT 8 B，页 16 KB，页内可用约 16 200 B（扣页头页尾）
 非叶子节点：8 B 主键 + 6 B 页号 ≈ 14 B/条（含页内开销）⟹ 每页约 1170 条指针
 叶子节点（行宽 1 KB）：16 200 / 1024 ≈ 16 行/页
   2 层（根 + 叶）  ：1170 × 16        ≈ 1.9 万行
   3 层（根 + 中 + 叶）：1170² × 16     ≈ 2.2 × 10⁷ ≈ 2000 万行   ← 那个数字的来源
 同样的 3 层，若行宽只有 100 B（16 200 / 100 ≈ 162 行/页）：
   1170² × 162 ≈ 2.2 × 10⁸ ≈ 2 亿行
 行宽差 10 倍，阈值差 10 倍 ⟹ "2000 万"是行宽的结论，不是 MySQL 的限制

 真正决定性能的是热数据能否留在 buffer pool（默认 128 MB，线上常配到几十 GB）：
   页在内存在命 ⟹ 一次访问约 100 ns；页不在内存落盘 ⟹ 一次 SSD 随机读约 100 μs
   两者差 3 个数量级 ⟹ 若热数据长期驻留，几亿行的单表也可以不拆

因此正确的判断顺序是：先看单表数据总量与热数据量、行宽、二级索引数量、buffer pool 容量、慢查询中的随机 IO 占比，再决定拆不拆；只有"热数据装不进内存且 QPS 要求无法靠缓存缓解"时，水平拆分才是收益大于成本的选择。

中间件定位差异：ShardingSphere 分客户端（ShardingSphere-JDBC，JAR 内嵌，SQL 改写、路由、归并在 JVM 内完成，无额外网络跳数，但与语言绑定、升级需发版）与服务端（ShardingSphere-Proxy，独立进程用 MySQL/PostgreSQL 协议对外，多语言友好，代价是多一跳且需独立运维）两种形态，且治理能力（读写分离、数据加密、影子库压测）围绕同一套内核；MyCat 是独立代理进程形态，生态与治理能力弱于前者，适合已有存量系统。选型时要问清"谁来解析 SQL、谁来做归并、跨语言需求有多强"。

26.8 分库分表带来的五个新问题

跨库深分页的"二次查询法"是最值得讲清的一个：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

目标：SELECT * FROM product WHERE category = ? ORDER BY price LIMIT 1000000, 20
分片数 N = 4

第一次查询（每片取全局 offset 的 1/N，多取 20 行）：
  分片 i: SELECT * FROM product WHERE category = ? ORDER BY price LIMIT 250000, 20
  得到 4 组结果 ⟹ 记每片第 250001 行的 price 为 p_i，取 min(p_i) 作为全局边界候选 p_min
  （因为全局第 1000001 行必然不小于各片第 250001 行的最小值）

第二次查询（用 p_min 把"可能进入全局前 1000020 名"的行全部捞回来）：
  分片 i: SELECT * FROM product WHERE category = ? AND price BETWEEN p_min AND p_i
          ORDER BY price
  各片返回行数远小于 250020 ⟹ 在内存中归并排序，取全局第 1000001~1000020 行

代价：多一轮查询与一次全分片扫描；收益：避免了每片取 1000020 行的网络与内存放大
工程上更常用的是"禁止跳页 + 游标"：WHERE price > :last_price ORDER BY price LIMIT 20

26.9 XA 事务与为什么最终一致更受欢迎

MySQL 的 XA 实现就是教科书式两阶段提交：XA START 进入 ACTIVE，XA END 结束语句阶段进入 IDLE，XA PREPARE 完成阶段一并把状态持久化（此后崩溃可恢复），最后 XA COMMIT / XA ROLLBACK 完成阶段二。XA RECOVER 列出所有处于 PREPARE 状态的事务。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 阶段一：各参与者在 PREPARE 后都持有行锁，且都具备崩溃后恢复的能力
XA START 'xid-order-20240501-0001';
UPDATE account SET balance = balance - 100 WHERE id = 1;   -- 扣款
XA END 'xid-order-20240501-0001';
XA PREPARE 'xid-order-20240501-0001';   -- 关键点：锁不释放，但改动对其他会话仍不可见
-- 协调者在所有参与者都 prepare 成功后才决定提交
XA COMMIT 'xid-order-20240501-0001';

-- 协调者宕机后的排查：找出悬空的 PREPARE 事务（长时间不处理会一直占锁）
XA RECOVER;
-- 输出示例：formatID=1, gtrid_length=21, bqual_length=4,
--          data='xid-order-20240501-0001-0001'

三个问题是面试的固定追问点：

1. 同步阻塞。 XA PREPARE 之后行锁一直持到阶段二，其他事务写同一行会阻塞到 innodb_lock_wait_timeout（默认 50 s）；若协调者迟迟不决策，连接与锁会持续堆积。这是"强一致换吞吐"的直接代价。
2. 协调者单点。 阶段一全部成功后协调者崩溃，参与者无法自行决定提交还是回滚，只能保留锁等待；恢复要靠协调者的日志重放或人工/补偿脚本，这段时间业务是挂住的。
3. prepare 后的中间态很难处理。 事务既没提交也没回滚：改动对其他会话不可见（一致性读看不到），但锁被持有；此时做备份、主从切换或 DDL 都可能踩到它——切换走的从库可能根本没有这个 prepare（取决于复制模式），而旧主上它已被持久化，一提交就产生分叉。数据库内部还有一层两阶段提交（redo/binlog 的 prepare/commit，见第 21 章 21.6 节），两层叠在一起让排障更难。

互联网业务更倾向最终一致，原因不是"XA 不好"，而是三笔账：XA 把可用性交给协调者、把吞吐交给锁、把故障处理交给人工；而业务侧通常已有幂等键与补偿能力，用 TCC/Saga/事务消息（本地消息表 + 至少一次投递 + 幂等消费）可以把"长事务"拆成"若干本地短事务 + 一个可重试的补偿流程"，代价变成"短暂不一致窗口"而非"锁等待"。方案对比与各模式的不一致窗口见第 34 章 34.5 节。

26.10 在线扩容与数据迁移

扩容的本质是"让新库追上旧库，再把流量搬过去"，且每一步都要能停下和回退。

1. 影子表/新分片就位：先建新结构，不接流量。
2. 全量 + 增量同步：全量用一致性快照（mysqldump --single-transaction --master-data，或用 MyDumper 类工具）拿一个位点；增量用 binlog 订阅，canal 就是把自己伪装成 MySQL 从库、请求 dump 线程的事件流，解析成行变更后投递到消息队列，下游按行应用。全量位点与增量起点对齐后，新库才可能追平。
3. 双写与校验：写请求同时写新旧两侧（双写不是原子的，必须配幂等键 + 失败重试 + 定期对账），同时按分片/主键区间分批做校验和比对，差异率降到阈值以下才允许切读。
4. 灰度切流：读流量按用户/租户/百分比逐步切到新库（1% → 5% → 50% → 100%），每一步盯错误率与 P99 延迟；写流量不能两头都开，通常按"用户分片路由"让不同用户先迁到不同库，最后再把路由表整体切过去。
5. 收尾与回退：确认追平后再停旧库写入；旧库与全量快照保留一段时间用于回退，回退预案要能覆盖"新库写入如何反向补偿"。

在这个流程里，gh-ost 与 canal 的位置不同：gh-ost 解决"表结构变更如何在线执行"——它把自己也当成一个从库去解析 binlog 得到增量变更（而不是用触发器），在主库上做的是 ALTER 的逐块拷贝，可以按负载阈值自动限速暂停，因此对主从延迟的影响比直接 ALTER 可控；canal 解决"数据如何搬运"——它是增量通道，把 binlog 变成可投递给新分片、搜索引擎、数仓的数据流。二者组合常见于"既要改结构又要换分片"的迁移，而分批校验与灰度切流是它们之上必须有的一层业务保障。

本章小结

自测题

Q1（计算）页 16 KB、页内可用 16 200 B，主键为 BIGINT（8 B），页号指针 6 B。请估算三层 B+ 树在行宽 1 KB 与行宽 120 B 时分别能容纳多少行，并说明"单表 2000 万行就该分表"这句话哪里不严谨。

Q2（判断）主库崩溃，其中一个已提交事务尚未被任何从库收到。分别判断在异步、wait_point=AFTER_COMMIT、wait_point=AFTER_SYNC 三种配置下，这个事务会不会造成"用户读到过、之后又消失"的不一致。

Q3（分析）监控面板上 Seconds_Behind_Master 一直是 0，但业务反馈"下单后立刻查订单查不到”。请给出至少三种可能原因，并说明你会改用哪些指标确认。

Q4（设计）订单表按 user_id 取模分成 4 个库，现在要支持"按创建时间倒序的运营后台列表，允许跳页到第 5 万页”。给出两种方案并比较代价；如果你还需要在这个列表上按商家维度筛选，问题会变成什么？

Q5（设计）支付回调里"写订单库（主库）后立刻在主库上读回校验，再去从库查列表"。上线后偶发列表里看不到刚支付的订单。请给出两种解法并比较代价；若要求 100% 写后读一致且可接受写延迟上升，选哪个？

面试高频问题与陷阱

延伸阅读

• MySQL 官方手册 “Replication” 章：复制线程与 SHOW REPLICA STATUS 字段定义、WAIT_FOR_EXECUTED_GTID_SET() 的语义与超时行为。
• MySQL 官方手册 “Group Replication” 章：认证（certification）过程、group_replication_consistency 各级别的可见性保证、流控与成员驱逐超时。
• MySQL 官方手册 “XA Transactions” 节：XA START/END/PREPARE/COMMIT 状态机与 XA RECOVER 输出格式。
• Percona Toolkit 文档：pt-heartbeat（延迟口径）、pt-table-checksum 与 pt-table-sync（切换后校验与修复）。
• 《高性能 MySQL》中复制、扩展与分片相关章节；gh-ost 与 canal 的项目文档（在线 DDL 执行与 binlog 增量订阅的定位差异）。