某天下午,运营在后台跑了一次全量导出,几秒钟后整个网站开始转圈。你登录数据库一看,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:1 | 1-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 五种应对策略
- 写后读主(最常用):在同一个会话(或同一用户)完成写操作后的一个时间窗口内(比如 3 秒),强制所有读走主库。实现方式可以在应用层用一个带过期时间的标记,也可以用 ProxySQL 的
mysql_query_rules配合client_addr或者查询规则命中后的持久化标记。 - 按业务分级:把读请求分成"强一致读"和"弱一致读"两类。订单详情、账户余额、库存查询走主库;商品列表、评论、排行榜、历史记录走从库。这是最简单也最可控的做法,代价是开发要在调用时显式指定。
- 延迟阈值自动下线:监控各从库的
Seconds_Behind_Master,超过阈值(比如 5 秒)就把它从读组摘掉,等追平再放回。ProxySQL 可以配合自定义脚本实现,也可以自己写一个定时任务更新mysql_servers.status='OFFLINE_SOFT'。 - 半同步复制:开启
rpl_semi_sync_master_enabled,保证事务至少到达一个从库才返回,把延迟窗口压到毫秒级。注意它会降低写入 TPS,且超时(默认 10000ms)后会退化为异步。 - 缓存兜底:写操作完成后同时更新缓存,读优先查缓存。这样即便读到延迟的从库,用户看到的也是正确数据。注意缓存与数据库的一致性要另外设计(比如延迟双删)。
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 线程抢不到资源。加从库或者优化慢查询。
五、上线读写分离的实施步骤
- 先用
SHOW GLOBAL STATUS LIKE 'Com_select'和Com_insert/Com_update/Com_delete统计一周的读写比,确认读占比是否值得做(建议读写比 > 10:1)。 - 开启慢查询日志(
slow_query_log=ON,long_query_time=1),用pt-query-digest分析 Top 20 慢 SQL,先做索引和 SQL 优化。这一步没做完就上读写分离,等于把问题复制三份。 - 检查表结构:确认所有 InnoDB 表有主键、无外键(外键会影响并行回放)、字符集和排序规则主从一致。
- 搭建至少两个从库(一个从库没有冗余意义,挂了就全回到主库)。开启 GTID,配置
read_only与super_read_only。 - 部署 ProxySQL 或选定应用层方案,先只把非核心读接口(如列表页、搜索建议)切到从库,观察 1-2 天。
- 在应用侧实现"写后读主"策略:对刚写入的数据,3-5 秒内的读强制走主库。
- 配置延迟监控告警:从库延迟超过 10 秒告警,超过 30 秒自动摘除出读组。
- 逐步扩大读流量比例,同时观察主库负载下降幅度和从库延迟曲线,两者一起看。
- 做一次从库故障演练:停掉一个从库的 mysqld,确认中间件能自动摘除且业务无感。
- 文档化路由规则,尤其是"哪些查询必须走主库"的清单,交给所有开发同学。这份清单是长期维护资产。
六、避坑清单
- 事务内的读必须走主库。如果一个事务里先
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 优化,再考虑读写分离,最后才考虑分库分表。顺序反了,成本会翻倍。
企业QQ咨询




