MySQL 经典架构模式：从主从复制到分库分表

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 复制数据，读请求路由到从库。

flowchart LR
    App["应用"] -->|写| Proxy["代理层<br/>ProxySQL / ShardingSphere"]
    App -->|读| Proxy
    Proxy -->|INSERT/UPDATE/DELETE| Master[("主库")]
    Proxy -->|SELECT| Slave1[("从库 1")]
    Proxy -->|SELECT| Slave2[("从库 2")]

    Master -->|binlog| Slave1
    Master -->|binlog| Slave2

binlog 复制的三种格式：

格式	记录内容	优势	风险
STATEMENT	SQL 语句本身	日志小	NOW()、RAND() 等不确定函数导致主从不一致
ROW	每行变更的前后值	确定性强	日志量大
MIXED	自动选择	折中	少数场景仍有不一致风险

生产环境推荐 ROW 格式——牺牲一些日志空间换取主从数据的确定性一致。

读写分离的实现层

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

主从延迟的处理

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

策略	做法	适用场景
强制走主	关键业务的"写后读"路由到主库	订单创建后立刻查订单
等待 GTID	读从库前等待从库追上指定 GTID	精确控制一致性点
会话粘性	同一会话写后 N 秒内的读走主库	通用场景
业务容忍	接受延迟，页面提示"数据可能有延迟"	报表、列表页

-- 等待从库追上指定 GTID（MySQL 5.7+）
SELECT WAIT_FOR_EXECUTED_GTID_SET('master-uuid:1-100', 1);
-- 返回 0 表示已追上，返回 1 表示超时

取舍

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

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

问题

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

设计

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

sequenceDiagram
    participant Client
    participant Master
    participant Slave

    Client->>Master: COMMIT
    Master->>Master: 写 binlog
    Master->>Slave: 发送 binlog
    Slave-->>Master: ACK（已收到）
    Master-->>Client: COMMIT OK
    Note over Slave: 从库异步回放 relay log

两种模式的区别：

模式	等待时机	数据安全	性能
after-commit（传统半同步）	主库先 commit 再等 ACK	极端情况下其他会话能读到未被从库确认的事务	高
after-sync（增强半同步，5.7+）	主库等 ACK 后再 commit	未被从库确认的事务对所有会话不可见	略低

# 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），故障时切换到另一个主。

flowchart LR
    subgraph 机房A
        M1[("Master 1<br/>Active 写入")]
    end
    subgraph 机房B
        M2[("Master 2<br/>Standby")]
    end

    M1 -->|binlog| M2
    M2 -->|binlog| M1

    App["应用"] -->|写入| VIP["VIP / DNS"]
    VIP --> M1
    VIP -.->|故障切换| M2

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

# 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，形成树状拓扑。

flowchart TD
    Master[("主库")] -->|binlog| Relay1[("中继从库 1")]
    Master -->|binlog| Relay2[("中继从库 2")]

    Relay1 -->|binlog| S1[("从库 1-1")]
    Relay1 -->|binlog| S2[("从库 1-2")]
    Relay1 -->|binlog| S3[("从库 1-3")]

    Relay2 -->|binlog| S4[("从库 2-1")]
    Relay2 -->|binlog| S5[("从库 2-2")]
    Relay2 -->|binlog| S6[("从库 2-3")]

关键配置：

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

取舍

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

模式五：分库分表

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

问题

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

设计

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

shard = hash(sharding_key) % shard_count

flowchart LR
    App["应用"] --> Middleware["分片中间件<br/>ShardingSphere / TDDL"]

    Middleware -->|user_id % 4 = 0| DB0[("db_0.t_order_0")]
    Middleware -->|user_id % 4 = 1| DB0B[("db_0.t_order_1")]
    Middleware -->|user_id % 4 = 2| DB1[("db_1.t_order_0")]
    Middleware -->|user_id % 4 = 3| DB1B[("db_1.t_order_1")]

分片键的选择

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

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

取舍

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

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

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 加列、改列类型、加索引时，传统方式会锁表，阻塞所有写入。线上业务无法接受长时间锁表。

设计

三种主流方案：

flowchart TB
    subgraph "方案 A：pt-online-schema-change"
        A1["创建影子表（新结构）"] --> A2["触发器捕获增量"]
        A2 --> A3["分批拷贝历史数据"]
        A3 --> A4["原子 RENAME 替换"]
    end

    subgraph "方案 B：gh-ost"
        B1["创建影子表"] --> B2["binlog 捕获增量"]
        B2 --> B3["分批拷贝历史数据"]
        B3 --> B4["cut-over 替换"]
    end

    subgraph "方案 C：MySQL 8.0 Instant DDL"
        C1["ALTER TABLE ... ALGORITHM=INSTANT"]
        C1 --> C2["只修改元数据，毫秒完成"]
    end

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

gh-ost 示例：

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+）：

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 分区表

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 的"删除整个索引"是同一个思路。

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

-- 归档表（结构与主表一致，存储引擎可以用 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;

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

flowchart LR
    subgraph 热库 SSD
        Hot["orders<br/>近 6 个月"]
    end

    subgraph 冷库 HDD
        Cold["orders_archive<br/>6 个月以前"]
    end

    Hot -->|定期迁移| Cold
    Cold -.->|偶尔查询| App["应用"]

取舍

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

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

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

热数据在主表/主分区 → 冷数据迁移到归档 → 超期数据 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 的变更事件可靠地投递到下游。

flowchart LR
    App["应用"] -->|写入| MySQL[("MySQL")]
    MySQL -->|binlog| Canal["Canal / Debezium"]
    Canal --> MQ["Kafka / RocketMQ"]
    MQ --> ES["ES 消费者"]
    MQ --> Redis["Redis 消费者"]
    MQ --> DW["数仓消费者"]

    ES --> ESCluster[("ES 集群")]
    Redis --> RedisCluster[("Redis 集群")]
    DW --> Hive[("Hive / ClickHouse")]

Canal 配置示例：

# 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）：

{
  "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 两阶段提交：

sequenceDiagram
    participant TM as 事务管理器
    participant DB1 as 订单库
    participant DB2 as 库存库

    TM->>DB1: XA START / INSERT 订单
    TM->>DB2: XA START / UPDATE 库存
    TM->>DB1: XA PREPARE
    TM->>DB2: XA PREPARE
    Note over DB1,DB2: 两个库都回复 OK
    TM->>DB1: XA COMMIT
    TM->>DB2: XA COMMIT

-- 事务管理器侧的伪代码
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';

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

-- 和业务写在同一个本地事务里
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
-- 下游消费者接收消息后扣减库存

flowchart LR
    App["应用"] -->|本地事务| DB1[("订单库<br/>orders + outbox")]
    Worker["Outbox Worker"] -->|轮询| DB1
    Worker -->|发送| MQ["MQ"]
    MQ --> Consumer["库存消费者"]
    Consumer -->|扣减| DB2[("库存库")]

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

取舍

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

模式十：代理层与连接池

问题

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

设计

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

flowchart LR
    S1["服务实例 1<br/>20 连接"] --> Proxy["ProxySQL<br/>连接复用"]
    S2["服务实例 2<br/>20 连接"] --> Proxy
    S3["服务实例 3<br/>20 连接"] --> Proxy
    S4["..."] --> Proxy

    Proxy -->|后端 50 连接| Master[("主库")]
    Proxy -->|后端 100 连接| Slave1[("从库 1")]
    Proxy -->|后端 100 连接| Slave2[("从库 2")]

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

ProxySQL 关键配置：

-- 后端 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 作者给出的经验公式：

连接数 ≈ 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 的保留天数（expire_logs_days）要根据恢复需求设定，而不是默认值。

面试速查表

场景关键词	模式	核心配置	必讲边界
读多写少、读扩展	主从 + 读写分离	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 是下游）

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

flowchart TD
    App["应用"] -->|CRUD| MySQL[("MySQL<br/>事实源")]
    App -->|搜索| ES[("ES<br/>搜索索引")]
    App -->|缓存/热路径| Redis[("Redis<br/>缓存")]

    MySQL -->|binlog CDC| Canal["Canal / Debezium"]
    Canal --> MQ["MQ"]
    MQ --> ESSync["ES 同步"]
    MQ --> RedisSync["Redis 同步"]

    ESSync --> ES
    RedisSync --> Redis

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

参考资料

• MySQL 官方文档：Replication
• MySQL 官方文档：Semisynchronous Replication
• MySQL 官方文档：XA Transactions
• MySQL 官方文档：Partitioning
• MySQL 官方文档：Online DDL
• gh-ost: GitHub’s Online Schema Migration
• pt-online-schema-change
• Canal: Alibaba MySQL binlog 增量订阅
• Debezium: CDC Platform
• ProxySQL Documentation
• ShardingSphere
• HikariCP: About Pool Sizing