MySQL读写分离有什么优势?适用场景、部署架构与常见问题

某天下午,运营在后台跑了一次全量导出,几秒钟后整个网站开始转圈。你登录数据库一看,CPU 打满,几百条 SELECT 排着队等待,而真正核心的下单接口因为抢不到连接全部超时。这种"一个慢查询拖垮全站"的场景,几乎是每个从小规模走向中等规模的 MySQL 部署都会遇到的一课。读写分离就是这类问题最经典的解法:让主库专心处理写,把读流量分摊到多个从库上。但它绝不是万能药——用错了场景,它会带来一套全新的麻烦。

一、读写分离到底解决了什么问题

要理解读写分离的价值,先看单库为什么会撑不住。MySQL 的连接是有限资源(max_connections 默认 151),每个连接对应一个线程,几百个并发查询会让线程调度开销陡增。更重要的是,读和写会互相争抢同一批资源:InnoDB 的 buffer pool、行锁、redo log 写入、以及最容易被忽视的——磁盘 IOPS。

把读流量剥离出去之后,收益体现在四个层面:

  • 提升读吞吐:读请求天然可以水平扩展,加从库就等于加读容量。一个主库扛 3000 QPS 读,加两个从库理论上能撑到 9000 QPS。
  • 隔离读写干扰:报表查询、运营导出、全表扫描这些重型读操作不会再去抢占主库的 buffer pool,也不会因为长事务阻塞写入。
  • 提高可用性冗余:每个从库都是一份完整的数据副本,主库故障时它是切换的候选者,同时也分担了备份压力(可以把备份跑在从库上)。
  • 支持异构读场景:可以给不同用途的从库配不同的硬件和参数。比如给 BI 报表从库配更大的内存和更宽松的 max_execution_time,给实时接口从库配更快的 SSD。

1.1 但它解决不了的问题

必须清醒地认识到读写分离的边界。它不能提升写入能力——所有写仍然集中在单点主库,写入瓶颈要靠分库分表或者换架构解决。它不能解决单表数据量过大的问题——从库一样要扫描同样多的行,慢查询在从库上照样慢。它还会引入数据延迟,这是下面要重点讨论的。如果你们的业务是"写多读少"(比如物联网数据采集、日志上报),读写分离几乎没有收益,反而增加了运维复杂度。

二、哪些场景适合,哪些不适合

判断要不要上读写分离,最有价值的两个指标是读写比和延迟容忍度。

业务类型典型读写比延迟容忍度是否适合读写分离
新闻/博客/内容站50:1 以上秒级可接受非常适合,收益最大
电商商品详情页20:1秒级可接受适合,注意下单链路走主库
SaaS 后台管理10:11-2 秒可接受适合
社交动态流5:1用户刚发的内容必须立刻可见部分适合,需"写后读主"策略
订单/交易核心1:1几乎零容忍不适合,应全走主库
支付/账务写为主零容忍不适合
实时监控采集写为主—无收益

一句经验之谈:读写比低于 10:1 的业务,上读写分离的收益通常抵不上它带来的复杂度。先用慢查询日志和索引优化把单库性能榨干,再考虑架构层面的拆分。很多团队跳过这一步直接上读写分离,结果发现慢查询在从库上依然是慢查询,只是从拖垮一个库变成拖垮三个库。

三、三种主流部署架构对比

3.1 应用层(代码/框架)路由

这是最轻量也最灵活的方式。在应用代码里配置两个数据源(masterDataSource 和 slaveDataSource),通过注解或者方法名前缀决定走哪个库。Java 生态里 ShardingSphere-JDBC、Spring 的 AbstractRoutingDataSource 都是典型实现。

# ShardingSphere-JDBC 读写分离配置片段(YAML)
rules:
  - !READWRITE_SPLITTING
    dataSources:
      rw_ds:
        writeDataSourceName: master_ds
        readDataSourceNames:
          - slave_ds_0
          - slave_ds_1
        loadBalancerName: round_robin
    loadBalancers:
      round_robin:
        type: ROUND_ROBIN

优点是不引入额外中间件,没有额外网络跳数,性能好,故障点少。缺点是路由逻辑侵入应用,多语言技术栈要各自实现一遍;从库上下线需要改配置并重启应用;最重要的是,它无法感知主从延迟,配置里写死"这个查询走从库",延迟大的时候也会照走不误。

3.2 中间件代理(ProxySQL / MySQL Router / ShardingSphere-Proxy)

这是生产环境最常见的方案。应用只连接中间件一个地址,由中间件解析 SQL、判断读写、转发到后端实例。以 ProxySQL 为例:

# 连接 ProxySQL 管理端口(默认 6032)
mysql -u admin -padmin -h 127.0.0.1 -P 6032

# 注册后端 MySQL 实例
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)
VALUES (10, '10.0.0.11', 3306, 1, 1000);   -- hostgroup 10 = 写组
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections)
VALUES (20, '10.0.0.12', 3306, 1, 1000),   -- hostgroup 20 = 读组
       (20, '10.0.0.13', 3306, 1, 1000);

# 配置用户:default_hostgroup 决定默认走写组
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent)
VALUES ('appuser', 'AppPass#2024', 10, 1);

# 核心规则:把 SELECT 分流到读组
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*', 20, 1);

# 例外:SELECT ... FOR UPDATE / 在事务中的 SELECT 必须走主库
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT.*FOR UPDATE$', 10, 1);

LOAD MYSQL SERVERS TO RUNTIME;  SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;    SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

ProxySQL 相比应用层路由,多了几个关键能力:一是连接池复用,后端 MySQL 的实际连接数可以远小于应用连接数,这对高并发很有价值;二是自动摘除故障节点,通过 mysql-monitor_enabled 定期探测,从库挂了自动移出读组;三是可以做延迟感知,配合 mysql-monitor_slave_lag_when_null 和自定义脚本,把延迟超过阈值的从库自动下线;四是支持查询规则热更新,改路由不用重启应用。

# 查看后端健康状态与延迟监控
SELECT hostgroup_id, hostname, port, status, Queries, Latency_us
FROM stats_mysql_connection_pool;

SELECT hostname, port, last_check_ms, repl_lag_ms
FROM mysql_server_replication_lag_log ORDER BY time_started DESC LIMIT 10;

# 查看规则命中统计,判断分流是否符合预期
SELECT rule_id, hits, match_digest, destination_hostgroup
FROM stats_mysql_query_rules;

3.3 DNS / VIP 层分流

把读域名(如 mysql-read.internal)解析到多个从库 VIP,靠 DNS 轮询或 LVS 做负载均衡。这种方式对应用完全透明,几乎零改造,但缺点明显:DNS 有缓存,故障切换时收敛慢;无法区分 SQL 类型,做不到"事务内走主库"这类精细控制;负载均衡粒度粗,容易因为某个慢查询把某台从库打满。通常只作为辅助手段,或者用在纯只读的报表类场景。

方案改造成本延迟感知故障自动摘除连接池复用适用规模
应用层路由中(改代码)需自己实现需自己实现依赖应用池小型、单语言栈
ProxySQL低(改连接串)支持支持支持中大型,推荐
MySQL Router低与 InnoDB Cluster 联动自动支持InnoDB Cluster 场景
DNS/VIP 轮询极低不支持弱不支持只读报表场景

四、最常见的坑:主从延迟引发的"读不到刚写的数据"

这是读写分离上线后第一必然遇到的问题。用户提交订单后跳转到订单详情页,页面提示"订单不存在";用户改了昵称刷新后还是旧名字。原因是写入在主库完成,读请求被路由到了还没追上 binlog 的从库。

4.1 五种应对策略

  1. 写后读主(最常用):在同一个会话(或同一用户)完成写操作后的一个时间窗口内(比如 3 秒),强制所有读走主库。实现方式可以在应用层用一个带过期时间的标记,也可以用 ProxySQL 的 mysql_query_rules 配合 client_addr 或者查询规则命中后的持久化标记。
  2. 按业务分级:把读请求分成"强一致读"和"弱一致读"两类。订单详情、账户余额、库存查询走主库;商品列表、评论、排行榜、历史记录走从库。这是最简单也最可控的做法,代价是开发要在调用时显式指定。
  3. 延迟阈值自动下线:监控各从库的 Seconds_Behind_Master,超过阈值(比如 5 秒)就把它从读组摘掉,等追平再放回。ProxySQL 可以配合自定义脚本实现,也可以自己写一个定时任务更新 mysql_servers.status='OFFLINE_SOFT'。
  4. 半同步复制:开启 rpl_semi_sync_master_enabled,保证事务至少到达一个从库才返回,把延迟窗口压到毫秒级。注意它会降低写入 TPS,且超时(默认 10000ms)后会退化为异步。
  5. 缓存兜底:写操作完成后同时更新缓存,读优先查缓存。这样即便读到延迟的从库,用户看到的也是正确数据。注意缓存与数据库的一致性要另外设计(比如延迟双删)。

4.2 从根源上降低延迟

与其在应用层打补丁,不如把延迟本身压下去。延迟的主要来源和对应手段:

  • 大事务:一次更新 100 万行,主库 10 秒完成,从库单线程回放可能要几分钟。拆分为每批 5000 行。可以设一个监控规则:SHOW REPLICA STATUS 里 Seconds_Behind_Master 突然跳到几百,去 information_schema.INNODB_TRX 里找正在跑的长事务。
  • 从库单线程回放:MySQL 5.6 之前 SQL 线程是单线程的,这是延迟的经典瓶颈。开启多线程复制:slave_parallel_workers = 8(8.0.22+ 为 replica_parallel_workers),slave_parallel_type = LOGICAL_CLOCK,并配 slave_preserve_commit_order = ON 保证提交顺序。注意 LOGICAL_CLOCK 依赖主库的 binlog_group_commit_sync_delay 或足够的并发度,如果主库写入本身很稀疏,多线程也快不起来。
  • 从库硬件偏弱:很多团队给从库配更差的磁盘,回放速度追不上写入。从库磁盘 IOPS 不应低于主库。
  • 缺少主键:ROW 格式下,从库回放 UPDATE/DELETE 时如果表没有主键,要做全表扫描定位行,回放耗时可能相差百倍。强制所有 InnoDB 表必须有主键,这条规则怎么强调都不过分。
  • 从库承担过多读压力:读流量超过从库承载能力,SQL 线程抢不到资源。加从库或者优化慢查询。

五、上线读写分离的实施步骤

  1. 先用 SHOW GLOBAL STATUS LIKE 'Com_select' 和 Com_insert/Com_update/Com_delete 统计一周的读写比,确认读占比是否值得做(建议读写比 > 10:1)。
  2. 开启慢查询日志(slow_query_log=ON,long_query_time=1),用 pt-query-digest 分析 Top 20 慢 SQL,先做索引和 SQL 优化。这一步没做完就上读写分离,等于把问题复制三份。
  3. 检查表结构:确认所有 InnoDB 表有主键、无外键(外键会影响并行回放)、字符集和排序规则主从一致。
  4. 搭建至少两个从库(一个从库没有冗余意义,挂了就全回到主库)。开启 GTID,配置 read_only 与 super_read_only。
  5. 部署 ProxySQL 或选定应用层方案,先只把非核心读接口(如列表页、搜索建议)切到从库,观察 1-2 天。
  6. 在应用侧实现"写后读主"策略:对刚写入的数据,3-5 秒内的读强制走主库。
  7. 配置延迟监控告警:从库延迟超过 10 秒告警,超过 30 秒自动摘除出读组。
  8. 逐步扩大读流量比例,同时观察主库负载下降幅度和从库延迟曲线,两者一起看。
  9. 做一次从库故障演练:停掉一个从库的 mysqld,确认中间件能自动摘除且业务无感。
  10. 文档化路由规则,尤其是"哪些查询必须走主库"的清单,交给所有开发同学。这份清单是长期维护资产。

六、避坑清单

  • 事务内的读必须走主库。如果一个事务里先 UPDATE 再 SELECT,而这个 SELECT 被路由到从库,会读到旧数据导致逻辑错误。ProxySQL 里要设 transaction_persistent = 1,让同一事务的所有语句落到同一个 hostgroup。
  • SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 必须走主库。它们是要加锁的,在从库上加锁毫无意义且从库本身是 read_only 的。务必在查询规则里单独匹配。
  • 存储过程、临时表、LAST_INSERT_ID() 都要走主库。临时表是会话级的,语句被分发到不同节点必然出错;LAST_INSERT_ID() 依赖同一连接,从库上取到的值毫无意义。
  • 不要在主库做备份,也不要在从库跑完备份就以为万事大吉。备份在从库跑是对的,但要确认备份期间从库延迟不会失控(xtrabackup 会加全局锁的短暂阶段)。
  • 注意连接池的连接数放大。N 个应用实例 × 每个实例 50 连接 = 50N 个连接到中间件,中间件再到后端。要在 ProxySQL 里配 mysql-max_connections 和后端 max_connections 的合理比例,避免把 MySQL 打爆。
  • 多个从库之间要做负载均衡权重。硬件配置不同要设不同 weight,否则弱机器先被打满。
  • 监控要从两个视角看延迟。既看 Seconds_Behind_Master,也看 Retrieved_Gtid_Set 与 Executed_Gtid_Set 的事务数差。前者在大事务场景下严重失真。
  • 别忘了一致性校验。定期用 pt-table-checksum 校验主从数据差异,读写分离让不一致更容易被用户感知到。

小结

MySQL 读写分离的优势非常明确:提升读吞吐、隔离读写干扰、提供冗余副本、支持异构读场景。但它有严格的适用前提——读多写少(读写比建议 10:1 以上)且业务能容忍秒级延迟。部署架构上,中小规模可以用应用层路由快速落地,中大规模强烈建议用 ProxySQL 这类中间件,它带来的连接池复用、故障自动摘除、延迟感知和规则热更新能力,价值远超那一点性能开销。而上线后真正考验人的,是对"写后读主""事务内走主库""延迟阈值摘除"这些细节的处理——读写分离的技术本身不难,难的是把它的副作用一个个堵住。记住一条优先级:先做索引和 SQL 优化,再考虑读写分离,最后才考虑分库分表。顺序反了,成本会翻倍。