MySQL 在绝大多数业务系统中扮演事实源的角色。Redis 和 ES 都是派生存储——数据可以从 MySQL 重建，反过来不行。围绕 MySQL 的架构模式，核心问题始终是三个：怎么扛住读写压力、怎么保证数据不丢、怎么随业务增长水平扩展。

下面整理十个生产环境中反复出现的 MySQL 架构模式。和 ES 架构模式 与 Redis 用例全解 形成三件套：Redis 管热路径和派生状态，ES 管可搜索的派生索引，MySQL 管事实。

模式速查

模式 解决的问题 核心机制 典型场景
主从复制 + 读写分离 读压力分摊 binlog 复制 + 代理层路由 读多写少的业务
半同步复制 + 增强半同步 主从切换时数据不丢 after-sync / after-commit 金融、订单
双主（互为主从） 机房级故障切换 双向复制 + 自增步长错开 同城双活
级联复制 大规模从库扩展 从库的从库 读流量极大的分析场景
分库分表 单库容量和写入瓶颈 水平分片 + 路由中间件 亿级数据量
影子表与 Online DDL 大表结构变更不锁表 pt-osc / gh-ost / instant DDL 线上 Schema 演进
归档与冷热分离 历史数据生命周期管理 分区表 / 归档表 + 定期迁移 订单流水、日志
数据同步管道 MySQL 到异构存储的实时同步 binlog → MQ → ES/Redis/数仓 搜索、缓存、分析
分布式事务 跨库事务一致性 XA / TCC / Saga / 本地消息表 拆库后的跨库写入
代理层与连接池 连接管理与透明路由 ProxySQL / MyCat / 连接池 微服务连接治理

模式一：主从复制 + 读写分离

这是 MySQL 架构的起点。单机 MySQL 扛不住读压力时，第一步几乎总是加从库。

问题

业务读写比通常在 10:1 到 100:1 之间。单机 MySQL 的连接数、CPU、磁盘 I/O 在读流量上涨时最先到瓶颈。写入量不大，但读请求把主库的资源占满了。

设计

主库处理所有写入，从库通过 binlog 复制数据，读请求路由到从库。

主从复制与读写分离

binlog 格式与事件结构

binlog 记录的是按顺序写入的事件，不是一份完整的 SQL 历史。MySQL 8.4 默认使用 ROW，而且 binlog_format 已被标记为 deprecated；官方建议新的复制拓扑只使用行格式。STATEMENT 和 MIXED 仍需要理解，因为旧集群、历史日志和迁移过程里经常会遇到。

三种格式的差异在于 DML 怎样落进 binlog。DDL 是例外：即使配置为 ROW，CREATE TABLE、ALTER TABLE、DROP TABLE 仍按语句记录。

格式 记录内容 优势 风险
STATEMENT 产生变更的 SQL 语句 一条语句修改大量行时日志较小 依赖执行环境和语句确定性，复制端可能得到不同结果
ROW 表映射与行级变更镜像 复制端直接应用结果，确定性强 批量更新、宽表和大字段可能显著放大日志
MIXED 默认按语句记录，遇到不安全语句切换为行事件 兼顾部分日志体积与正确性 同一文件混合两种表示，排障和下游解析更复杂

同一条 UPDATE 在三种格式下会走不同的记录路径：

同一条 UPDATE 在三种 binlog 格式下的记录路径

STATEMENT 记录执行意图

语句格式把原始 DML 及其执行上下文写成 Query_event，复制端重新执行。一次 UPDATE 改一百万行，日志里仍可能只有一条语句，因此空间和网络开销较小。

问题在于“重新执行”要求复制端得到相同结果。依赖非确定函数、执行顺序、系统变量、触发器或存储函数的语句可能被判定为 unsafe。MIXED 会在一批已知条件下改用行格式；STATEMENT 则可能给出 warning，或者因为存储引擎能力而直接失败。不能把 MIXED 理解成对任意业务逻辑都能自动证明安全。

ROW 记录执行结果

行格式不要求复制端重新计算原 SQL。服务端先用 Table_map_event 把一个 table_id 映射到数据库、表和列结构，再用 Write_rows_event、Update_rows_event 或 Delete_rows_event 记录行变化。

  • INSERT 需要 after image，也就是写入后的列值。
  • DELETE 需要 before image，用来定位被删除的行。
  • UPDATE 同时需要 before image 和 after image：前者定位旧行，后者给出新值。

mysqlbinlog --verbose 展示的 ### UPDATE、### WHERE 和 ### SET 是解码后的可读表示，不等于服务器收到的原 SQL。列名通常会变成 @1、@2；原语句的谓词、注释和写法也无法从行事件完整还原。

MIXED 在两种表示之间切换

MIXED 通常先按语句处理，遇到 MySQL 判定为不安全的情况再切成行事件，例如涉及 UUID()、部分系统变量、用户函数或特定存储程序的语句。最终采用哪种表示，要看具体语句、存储引擎和版本规则，不能只看全局变量的值。

这也是排查旧集群时容易误判的地方：binlog_format=MIXED 只说明选择策略，不能证明某个事务实际写成了 Query_event。结论必须来自对应日志文件里的事件。

一个 ROW 事务包含哪些事件

下面是启用 GTID 的 InnoDB 行格式事务的典型事件组。Format_description_event 描述当前 binlog 文件的事件布局，不会在每个事务里重复；DDL、无 GTID、非事务表和压缩事务会出现不同序列。

ROW 事务的典型 binlog 事件链

每个 event 都有公共头、事件专属数据和可选校验尾。MySQL 当前事件公共头为 19 字节，包含时间戳、事件类型、生成事件的 server_id、事件长度、下一个事件位置和 flags。事件类型决定后面的 post-header 与 body 怎样解释；开启 CRC32 时，尾部还带校验值。

事件 解决的问题 读日志时关注什么
Format_description_event 告诉读取方当前文件使用的 binlog 版本和事件头布局 解析器是否支持该版本与校验算法
GTID_log_event 标识事务在全局事务集合中的身份 故障切换、去重和断点续传位置
Query_event 记录语句；在 ROW 事务中也可承载 BEGIN，DDL 仍使用它 这是 DML、事务边界还是 DDL
Table_map_event 把短期有效的 table_id 绑定到具体表结构 后续 rows event 引用的是哪个表
Write/Update/Delete_rows_event 记录实际行变化 before/after image 包含哪些列
Xid_event 标记事务型存储引擎提交 CDC 不应在它之前把半个事务当成完成

binlog_row_image 决定记录多少列

ROW 只决定按行记录，binlog_row_image 继续决定每个行镜像包含多少列。

配置 before image after image 主要影响
FULL 记录全部列 记录全部列 默认值；日志最大，但回放、排障和 CDC 兼容性最好
MINIMAL 记录定位行所需的列 记录 SQL 指定或自动生成的列；UPDATE 通常是更新涉及的列 节省磁盘与网络；下游若要重建完整行，需要额外状态或回查
NOBLOB 除不需要的 BLOB/TEXT 外记录其他列 除未变化的 BLOB/TEXT 外记录其他列 宽表大字段的折中方案；CDC 支持要按连接器版本验证

binlog_row_metadata 是另一层配置。默认 MINIMAL 只携带解码必需的部分元数据；设为 FULL 时会增加列名、主键信息、ENUM/SET 值等扩展元数据。复制端已有表结构，通常不依赖完整元数据；通用 CDC 解析器把事件转成自描述消息时，更可能受益于它。

行镜像缩小之后，主键和非空唯一索引更加重要。复制端应用 UPDATE 或 DELETE 时要用 before image 找到目标行；有合适索引就能直接定位，否则可能退化为更昂贵的搜索。MINIMAL 省下的日志空间不能抵消无主键表在复制端的查找成本。

用 mysqlbinlog 验证真实记录

先看运行时配置，不要只读配置文件：

1
SELECT @@global.log_bin, @@global.binlog_format, @@global.binlog_row_image, @@global.binlog_row_metadata;

再对代表性的 INSERT、UPDATE、DELETE 生成日志，用一条命令解码：

1
mysqlbinlog --base64-output=DECODE-ROWS -vv /var/lib/mysql/binlog.000123

-v 会把行事件显示成注释形式的伪 SQL；-vv 还会补充列类型等元数据。检查时至少要看到 Table_map、对应的 rows event、before/after 列和事务结束事件。若只看到变量配置，没有读实际 event，仍不能证明业务事务采用了预期格式。

DECODE-ROWS 适合阅读和排障，不适合直接生成恢复脚本。它会压掉可执行的 base64 BINLOG 语句；需要重放时应保留 mysqlbinlog 默认的 AUTO 输出，并在隔离环境先验证起止位置和事务边界。

格式会改变复制、恢复和 CDC 的行为

场景 选择与边界
新建 MySQL 复制拓扑 使用 ROW。MySQL 8.4 已弃用 binlog_format，非 ROW 格式也处于未来可能移除的路径上
大批量更新 ROW 可能产生大量行事件；先估算日志增长、网络带宽、从库应用速度和保留空间，不要为了省日志改回 STATEMENT
基于时间点恢复 物理/逻辑基线备份与后续 binlog 缺一不可；重放使用可执行输出，停止点要落在确认过的事务边界
CDC 到 Kafka、ES 或数仓 优先 ROW + FULL 获得完整 before/after；若采用 MINIMAL 或 NOBLOB，先验证连接器版本、下游补全策略和 DDL 处理
审计 binlog 能证明数据变化及顺序，但 ROW 通常不保留可完整还原的原 SQL 和操作者语义，不能直接替代审计日志

生产环境采用 ROW 时，仍要一起设计 binlog_row_image、主键、日志保留、刷盘策略、复制延迟和 CDC 位点。格式只决定“事件怎样表达”，并不单独保证日志已经持久化、从库已经应用，或下游已经消费。

对新系统，结论不是在三种格式里平均权衡，而是以 ROW 为基线，再按日志体积和下游契约决定行镜像与元数据配置。

读写分离的实现层

方式 代表 优势 代价
代理层 ProxySQL、MyCat、ShardingSphere-Proxy 对应用透明 多一跳延迟，代理本身需要高可用
客户端 SDK ShardingSphere-JDBC、TDDL 无额外网络跳转 侵入应用代码，升级需要全量发布
框架层注解 Spring @Transactional(readOnly=true) + 动态数据源 轻量 只适合简单场景

主从延迟的处理

主从复制是异步的，从库的数据可能落后主库几十毫秒到几秒。写后立刻读自己刚写的数据时，可能从从库读到旧值。

策略 做法 适用场景
强制走主 关键业务的"写后读"路由到主库 订单创建后立刻查订单
等待 GTID 读从库前等待从库追上指定 GTID 精确控制一致性点
会话粘性 同一会话写后 N 秒内的读走主库 通用场景
业务容忍 接受延迟，页面提示"数据可能有延迟" 报表、列表页 
1
2
3
-- 等待从库追上指定 GTID（MySQL 5.7+）
SELECT WAIT_FOR_EXECUTED_GTID_SET('master-uuid:1-100', 1);
-- 返回 0 表示已追上，返回 1 表示超时

取舍

主从复制解决读扩展，不解决写扩展。写入量到瓶颈时，需要分库分表（模式五）。主从延迟是这个模式的固有代价——所有依赖从库读的业务都要考虑"读到旧数据"的场景。

模式二：半同步复制 + 增强半同步

问题

异步复制下，主库 crash 时 binlog 可能还没传到从库。切主后丢失已提交的事务。金融、订单等场景无法接受数据丢失。

设计

半同步复制要求主库在提交事务时，至少等一个从库确认收到了 binlog，才返回提交成功。

半同步复制时序

两种模式的区别：

模式 等待时机 数据安全 性能
after-commit（传统半同步） 主库先 commit 再等 ACK 极端情况下其他会话能读到未被从库确认的事务 高
after-sync（增强半同步，5.7+） 主库等 ACK 后再 commit 未被从库确认的事务对所有会话不可见 略低 
1
2
3
4
5
# my.cnf — 主库
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_timeout = 1000  # ms，超时后降级为异步

取舍

半同步增加了每次提交的网络往返。如果从库全部挂掉或网络超时，会退化为异步复制（由 rpl_semi_sync_master_timeout 控制）。生产环境通常设 1–3 秒超时，在数据安全和可用性之间做平衡。

模式三：双主（互为主从）

问题

单主架构下，主库故障需要手动或自动切主。切主过程中有一段时间不可写。同城双机房部署时，希望两个机房都能写入，故障切换尽量快。

设计

两个 MySQL 实例互为主从，双向复制 binlog。正常情况下只在一个主上写入（Active-Passive），故障时切换到另一个主。

双主互为主从架构

避免主键冲突的关键配置：

1
2
3
4
5
6
7
8
9
# Master 1
auto_increment_increment = 2
auto_increment_offset = 1
# 生成的自增 ID: 1, 3, 5, 7, ...

# Master 2
auto_increment_increment = 2
auto_increment_offset = 2
# 生成的自增 ID: 2, 4, 6, 8, ...

故障切换由高可用组件负责：

方案 切换速度 复杂度
MHA (Master High Availability) 10–30 秒 中
Orchestrator 10–30 秒 中
MySQL InnoDB Cluster + MySQL Router 秒级 高（需要 Group Replication）
Keepalived + VIP 秒级 低（但缺乏数据一致性检查）

取舍

双主模式下严禁两边同时写同一行数据——双向复制无法处理写冲突。Active-Passive 模式是安全的；Active-Active 双写只在数据可以按业务维度严格分区时才可行（如按地域分流：机房 A 只写北方用户，机房 B 只写南方用户）。

模式四：级联复制

问题

从库数量多时（10+），所有从库都直连主库拉 binlog，主库的网络带宽和 binlog dump 线程成为瓶颈。

设计

让部分从库作为"中继从库"，其他从库从中继从库拉 binlog，形成树状拓扑。

级联复制树状拓扑

关键配置：

1
2
# 中继从库 — 把自己收到的 binlog 也写入自己的 binlog
log_slave_updates = ON

取舍

级联复制增加了复制链路的层级，延迟随层级增加。中继从库故障时，挂在它下面的所有从库都断开。适合从库数量非常多、且这些从库对延迟要求不严格的场景（如离线分析从库、报表从库）。

模式五：分库分表

单库的容量和写入 TPS 到达上限后，垂直拆分（按业务拆库）和水平拆分（按分片键拆表）是唯一的扩展路径。

问题

单表行数超过千万到亿级后，索引 B+ 树层数增加，查询变慢。单库的磁盘、连接数、写入 TPS 接近物理上限。

设计

水平分片的核心是一个路由公式，和 ES 的 shard routing 本质相同：

1
shard = hash(sharding_key) % shard_count

分库分表哈希路由

分片键的选择

分片键是整个分库分表方案中最关键的决策，一旦选定很难更改。

考量 好的分片键 差的分片键
查询覆盖率 绝大多数查询都带这个字段 很多查询不带这个字段，需要广播所有分片
数据均匀 高基数、均匀分布 低基数、严重倾斜
事务边界 同一事务的数据落在同一个分片 事务需要跨分片协调
业务稳定 值不可变（如 user_id） 值会变化（如 status）

典型分片键选择：

业务 推荐分片键 理由
订单系统 user_id 同一用户的订单在同一分片，支持"我的订单"查询
交易流水 user_id 同上
商品系统 product_id 商品维度的查询最多
物流系统 order_id 按订单聚合的查询最多

分片后的查询限制

分片后，不带分片键的查询需要广播到所有分片再合并结果，代价很高。

查询类型 带分片键 不带分片键
等值查询 路由到单个分片，快 广播所有分片，慢
范围查询 如果分片键在范围条件里，可以缩小分片范围 广播所有分片
JOIN 同一分片内的 JOIN 正常 跨分片 JOIN 需要中间件支持或应用层处理
聚合 单分片聚合 各分片聚合后再合并，精度可能有损
ORDER BY + LIMIT 单分片正常 各分片排序后合并，数据量倍增

全局唯一 ID

分片后不能依赖单库的自增 ID。常见方案：

方案 优势 代价
Snowflake 趋势递增、无中心化依赖 依赖时钟同步
号段服务 简单、中心化分配 号段服务是单点
UUID 完全去中心化 无序、索引效率差、占空间
数据库号段表 利用已有基础设施 号段用完要扩容

路由中间件

中间件 形态 特点
ShardingSphere-JDBC 客户端 SDK 无额外部署，侵入 pom
ShardingSphere-Proxy 独立代理 对应用透明，多一跳
MyCat 独立代理 社区活跃度下降
TDDL 客户端 SDK 阿里内部，部分开源
Vitess 独立代理 YouTube 出品，Cloud Native

取舍

分库分表是重型方案。分片键一旦选定很难更改，跨分片查询的限制贯穿整个系统生命周期。在单库优化（索引优化、读写分离、缓存）还有空间时，不要过早分库分表。

模式提炼：固定分母哈希 + 路由中间件

1
sharding_key → hash % N → 物理库表 → 中间件透明路由
系统 路由方式 分母可变 重分布
MySQL 分库分表 hash % N 困难（需要 resharding） 双写/binlog 追平
ES shard routing hash % N（N 固定） 否（需 reindex） 手动 reindex
Kafka partition hash % N 可增不可减 手动 reassign
Redis Cluster CRC16 % 16384 slot slot 可迁移 自动 resharding

模式六：影子表与 Online DDL

问题

大表（千万行以上）执行 ALTER TABLE 加列、改列类型、加索引时，传统方式会锁表，阻塞所有写入。线上业务无法接受长时间锁表。

设计

三种主流方案：

Online DDL 三种方案

方案 原理 优势 限制
pt-online-schema-change 影子表 + 触发器 成熟稳定 触发器有性能开销，不支持外键
gh-ost 影子表 + binlog 解析 无触发器，可暂停 需要 binlog 为 ROW 格式
MySQL 8.0 Instant DDL 只改元数据 毫秒级，无数据拷贝 只支持加列（加到最后）等有限操作
Online DDL (INPLACE) InnoDB 内部重建 无需外部工具 大表仍可能耗时很久

gh-ost 示例：

1
2
3
4
5
6
7
8
9
10
gh-ost \
  --host=master-host \
  --database=mydb \
  --table=orders \
  --alter="ADD COLUMN extra_info VARCHAR(256) DEFAULT NULL" \
  --allow-on-master \
  --chunk-size=1000 \
  --max-load='Threads_running=25' \
  --critical-load='Threads_running=50' \
  --execute

--max-load 和 --critical-load 控制负载阈值——超过阈值时自动限速或暂停，避免影响线上业务。

Instant DDL 示例（MySQL 8.0+）：

1
2
ALTER TABLE orders ADD COLUMN extra_info VARCHAR(256) DEFAULT NULL, ALGORITHM=INSTANT;
-- 毫秒完成，无数据拷贝

取舍

能用 Instant DDL 的操作优先用 Instant DDL。不支持 Instant DDL 的变更（改列类型、加索引、删列等），用 gh-ost 或 pt-osc。核心原则：线上大表的 DDL 不能依赖"锁表等它跑完"。

模式七：归档与冷热分离

问题

订单、流水、日志等数据有天然的时间属性。90% 的查询集中在最近 3–6 个月的数据上，但几年前的数据不能删（合规要求或纠纷追溯）。冷数据和热数据放在同一张表里，表越来越大，索引越来越深，写入和查询都变慢。

设计

两种主要方案：分区表和归档表。

方案一：RANGE 分区表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE orders (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2),
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
  PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
  PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
  PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

-- 每月初添加新分区
ALTER TABLE orders ADD PARTITION (
  PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01'))
);

-- 淘汰 3 年前的数据
ALTER TABLE orders DROP PARTITION p202301;

DROP PARTITION 是 DDL 操作，瞬间回收空间，不走逐行 DELETE。和 ES 的"删除整个索引"是同一个思路。

方案二：归档表 + 定期迁移

1
2
3
4
5
6
7
8
9
10
11
12
-- 归档表（结构与主表一致，存储引擎可以用 ARCHIVE 或压缩 InnoDB）
CREATE TABLE orders_archive LIKE orders;

-- 定期迁移脚本（每月执行）
INSERT INTO orders_archive
  SELECT * FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)
  LIMIT 10000;

DELETE FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)
  LIMIT 10000;

-- 分批执行，避免长事务

冷热数据分离与归档

取舍

方案 优势 代价
分区表 DROP PARTITION 快，查询自动分区裁剪 分区键必须包含在主键和唯一索引中
归档表 灵活，可以用不同存储引擎 需要维护迁移脚本，迁移期间有性能影响

分区表适合查询几乎总是带时间范围过滤的场景。归档表适合冷热数据需要放在不同物理实例上的场景。

模式提炼：不可变分区 + 定期淘汰

1
热数据在主表/主分区 → 冷数据迁移到归档 → 超期数据 DROP PARTITION / DELETE
系统 物理分区 淘汰方式 空间回收
MySQL 分区表 PARTITION BY RANGE DROP PARTITION 瞬间
MySQL 归档表 独立表/库 DELETE + OPTIMIZE 需要 OPTIMIZE
ES 时间分区 独立索引 DELETE index 瞬间
Kafka Log segment Log retention 自动

模式八：数据同步管道

问题

MySQL 是事实源，但搜索需要 ES、缓存需要 Redis、分析需要数仓。应用层双写（写 MySQL 的同时写 ES/Redis）不可靠——MySQL 提交成功但 ES 写入失败时，数据不一致。

设计

通过 binlog 订阅实现 CDC（Change Data Capture），把 MySQL 的变更事件可靠地投递到下游。

数据同步管道 CDC

Canal 配置示例：

1
2
3
4
5
6
# canal.properties
canal.instance.master.address = master-host:3306
canal.instance.dbUsername = canal
canal.instance.dbPassword = canal_password
canal.instance.filter.regex = mydb\\.orders,mydb\\.users
canal.mq.topic = mysql-binlog

Debezium 配置示例（Kafka Connect）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
  "name": "mysql-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "master-host",
    "database.port": "3306",
    "database.user": "debezium",
    "database.password": "password",
    "database.server.id": "1001",
    "database.include.list": "mydb",
    "table.include.list": "mydb.orders,mydb.users",
    "topic.prefix": "mysql",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "schema-changes"
  }
}

Canal vs Debezium

维度 Canal Debezium
生态 阿里开源，国内使用广泛 Red Hat 开源，Kafka Connect 生态
部署 独立进程 Kafka Connect 插件
下游 需要自己写消费者 天然对接 Kafka
DDL 支持 支持 支持，且有 schema registry
全量 + 增量 需要额外处理全量 snapshot 模式支持全量初始化

取舍

binlog 同步管道是"MySQL → 异构存储"的标准方案。比应用层双写可靠——binlog 是 MySQL 已提交事务的忠实记录，不存在"MySQL 成功但下游失败"的不一致窗口（下游失败可以重试消费）。代价是多了 Canal/Debezium 和 MQ 的运维成本，以及 binlog 解析到下游写入之间的延迟（通常在秒级）。

模式九：分布式事务

问题

分库分表后，一个业务操作可能涉及多个库。下单扣库存：订单库写入订单，库存库扣减库存。两个库之间没有本地事务可以保证原子性。

设计

四种主流方案，从强到弱：

方案 一致性 性能 复杂度 适用场景
XA 两阶段提交 强一致 低（锁持有时间长） 低（数据库原生支持） 短事务、低并发跨库写入
TCC 最终一致 高 高（需要写 Try/Confirm/Cancel） 高并发、资源预占
Saga 最终一致 中 中 长事务、步骤多
本地消息表 最终一致 高 低 异步场景

XA 两阶段提交：

XA 两阶段提交

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 事务管理器侧的伪代码
XA START 'order-tx-001';
INSERT INTO order_db.orders VALUES (...);
XA END 'order-tx-001';
XA PREPARE 'order-tx-001';

XA START 'stock-tx-001';
UPDATE stock_db.inventory SET stock = stock - 1 WHERE product_id = 100;
XA END 'stock-tx-001';
XA PREPARE 'stock-tx-001';

-- 两个都 PREPARE 成功后
XA COMMIT 'order-tx-001';
XA COMMIT 'stock-tx-001';

本地消息表（最常用的轻量方案）：

1
2
3
4
5
6
7
8
-- 和业务写在同一个本地事务里
BEGIN;
INSERT INTO orders (id, user_id, amount) VALUES (1001, 123, 99.00);
INSERT INTO outbox (id, topic, payload, status) VALUES (uuid(), 'order-created', '{"orderId":1001}', 'PENDING');
COMMIT;

-- 异步 worker 轮询 outbox 表，发送消息到 MQ
-- 下游消费者接收消息后扣减库存

本地消息表 Outbox 模式

本地消息表的核心优势：业务写入和消息写入在同一个本地事务里，不存在"业务成功但消息丢失"的问题。消息发送失败可以重试，下游消费者需要做幂等。

取舍

生产环境中，本地消息表（Outbox 模式）是最常用的方案——简单、可靠、不需要额外的事务协调器。XA 适合短事务、低并发的场景。TCC 适合高并发的资源预占（如秒杀扣库存）。Saga 适合步骤多、每步耗时长的业务流程。

模式十：代理层与连接池

问题

微服务架构下，几十个服务实例同时连接 MySQL。每个服务实例维护自己的连接池，总连接数 = 服务实例数 × 每实例连接池大小。MySQL 的 max_connections 到上限后，新连接被拒绝。

设计

在应用和 MySQL 之间加一层连接代理，做连接复用和路由。

代理层连接复用

应用侧总共 200 个前端连接，ProxySQL 复用后只需要 50 + 200 = 250 个后端连接，而不是让 MySQL 直接承受 200 个连接。

ProxySQL 关键配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 后端 MySQL 服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (10, 'master-host', 3306, 50),   -- 写组
       (20, 'slave1-host', 3306, 100),  -- 读组
       (20, 'slave2-host', 3306, 100);  -- 读组

-- 路由规则：SELECT 走读组，其他走写组
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (1, '^SELECT.*FOR UPDATE$', 10),  -- SELECT FOR UPDATE 走主库
       (2, '^SELECT', 20),                -- 普通 SELECT 走从库
       (3, '.*', 10);                     -- 其他走主库

-- 连接复用
UPDATE mysql_servers SET max_connections = 50 WHERE hostgroup_id = 10;
UPDATE mysql_servers SET max_connections = 100 WHERE hostgroup_id = 20;

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

连接池参数调优

无论是否有代理层，应用侧的连接池配置直接影响 MySQL 的连接压力。

参数 HikariCP 默认 生产建议 理由
maximumPoolSize 10 CPU 核数 × 2 + 磁盘数 连接太多反而因为上下文切换变慢
minimumIdle 同 maximumPoolSize 同 maximumPoolSize 避免连接创建的延迟
connectionTimeout 30s 3–5s 快速失败比排队等待好
maxLifetime 30min 25min 比 MySQL wait_timeout 短 5 分钟
idleTimeout 10min 10min 回收闲置连接

HikariCP 作者给出的经验公式：

1
连接数 ≈ CPU 核数 × 2 + 有效磁盘数

一台 4 核、1 块 SSD 的机器，最优连接数大约是 4 × 2 + 1 = 9。超过这个数，连接之间的上下文切换开销超过并行带来的收益。

取舍

代理层增加了一跳延迟和运维复杂度。服务实例少（< 10 个）、总连接数远低于 MySQL 上限时，直连更简单。服务实例多（几十到上百个）、连接总数接近 MySQL 上限时，代理层的连接复用能显著降低 MySQL 的压力。

MySQL 架构选型边界

需求 MySQL 合适的条件 替代方案
OLTP 事务处理 核心能力 PostgreSQL、TiDB
强一致 ACID 核心能力 PostgreSQL
读扩展 主从复制 + 读写分离 Redis 缓存、ES 搜索
写扩展 分库分表 TiDB、CockroachDB（NewSQL）
全文搜索 FULLTEXT 索引有限支持 Elasticsearch
时序数据 可以用分区表，但不是最优 InfluxDB、TDengine
OLAP 分析 不适合 ClickHouse、StarRocks
JSON 文档 5.7+ 支持 JSON 类型 MongoDB
海量小对象存储 不适合 S3、MinIO
图关系查询 不适合 Neo4j、TigerGraph

生产设计原则

MySQL 是事实源，其他都是派生

Redis 的缓存可以丢、ES 的索引可以重建、数仓的宽表可以重新跑。MySQL 的数据丢了，一切都没了。这个定位决定了 MySQL 的架构设计必须把数据安全放在第一位：半同步复制、备份策略、binlog 保留策略都不是可选配置。

分片键决定了系统的查询边界

和 ES 的 shard routing 一样，分片键一旦选定，不带分片键的查询就要广播所有分片。分片键选错了，整个系统的查询效率都受影响。在选分片键之前，先把所有高频查询列出来，确保 80% 的查询都带分片键。

索引不是加得越多越好

每个索引都是一棵 B+ 树，写入时需要维护。索引太多，写入变慢、磁盘空间增加、DDL 变更时间变长。只为高频查询建索引，定期清理不再使用的索引。

DDL 是高危操作

线上大表的 DDL 不能 ALTER TABLE 锁着跑。必须用 gh-ost、pt-osc 或 Instant DDL。DDL 发布前必须在从库上验证耗时和锁影响。

binlog 不只是复制用的

binlog 是 MySQL 数据变更的忠实记录。除了主从复制，还用于：CDC 同步到 ES/Redis/数仓、数据恢复（基于 binlog 的增量恢复）、审计追踪。binlog 的保留窗口（MySQL 8.4 使用 binlog_expire_logs_seconds）要根据恢复需求设定，而不是沿用默认值。

面试速查表

场景关键词 模式 核心配置 必讲边界
读多写少、读扩展 主从 + 读写分离 binlog 复制 + 代理路由 主从延迟、写后读一致性
数据不能丢 半同步复制 after-sync + timeout 退化为异步的条件
机房级故障切换 双主 互为主从 + 自增步长 禁止双写同一行
从库很多 级联复制 log_slave_updates 延迟增加
亿级数据、写瓶颈 分库分表 hash 路由 + 中间件 分片键选择、跨分片查询
大表 DDL 不锁表 Online DDL gh-ost / pt-osc / Instant 回退方案
历史数据管理 归档 + 冷热分离 分区表 / 归档表 DROP PARTITION vs DELETE
MySQL 到 ES/Redis 数据同步管道 Canal / Debezium + MQ 延迟、消费者幂等
跨库写入 分布式事务 XA / TCC / Outbox 本地消息表最常用
连接数爆炸 代理层 ProxySQL + 连接复用 一跳延迟

与 ES、Redis 架构模式的对照

三篇文章（Redis 用例全解、ES 架构模式、MySQL 架构模式）覆盖了数据架构中最常用的三个组件。它们的角色定位不同，但很多架构思路是共通的。

维度 MySQL ES Redis
角色 事实源 派生搜索索引 派生热路径
数据分布 分库分表（hash % N） shard routing（hash % N） hash slot（CRC16 % 16384）
生命周期管理 分区表 + DROP PARTITION ILM + 时间分区 + DELETE index TTL 自动过期
复制 binlog 主从复制 shard 副本同步 主从异步复制
一致性 ACID 事务 近实时（refresh_interval） 最终一致
读扩展 加从库 加 replica 加 replica
写扩展 分库分表 加 shard（创建时固定） 加 hash slot
冷热分离 分区表 + 归档表 ILM hot/warm/cold 无内置
Schema 变更 Online DDL (gh-ost) Reindex + Alias 切换 无 schema
同步到下游 binlog → Canal/Debezium 不适用（ES 是下游） 不适用（Redis 是下游）

三者在架构中的典型协作关系：

MySQL ES Redis 协作架构

MySQL 保存事实，binlog 驱动变更同步到 ES 和 Redis。应用层的写操作只打到 MySQL，读操作根据场景路由到三个存储。这个分工是大多数互联网系统数据架构的基本形态。

参考资料