SQL Lab

Appearance

读写分离架构 ​

⭐⭐⭐架构级优化5.7 & 8.0读写分离主从复制复制延迟架构优化

场景痛点 ​

电商订单系统，主库单机扛着 5000 QPS 写入 + 50000 QPS 读取，CPU 长期飙在 80% 以上。用户查"我的订单"这种高频读接口，平时 4ms 返回，高峰期却抖动到 20-50ms，写入也开始排队：

sql
SELECT *
FROM t_order_master
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

这条查询走了 (user_id, created_at) 联合索引，单看 EXPLAIN 完全没问题。但主库同时处理大量 INSERT/UPDATE 和 SELECT，CPU、内存、I/O 被读写两类负载共享，互相挤占。大量读连接占用连接池，写入请求排队甚至被拒绝；扫描型读查询把冷数据页加载进 Buffer Pool，把热点写数据挤出缓存，写入命中率下降。

这就是 "读写未分离" 的典型困境--单主库承载全部流量，读写互相干扰，读吞吐上限就是单机上限，无法通过加机器提升。

真实场景

订单查询、商品列表、用户中心、报表统计--凡是读多写少的业务系统，单主库架构在高并发下都会遇到读写争抢资源的问题。读 QPS 随业务增长线性上升，而主库单机能力有物理上限，迟早成为瓶颈。

问题分析 ​

bad.sql ​

sql
-- bad.sql: 所有读查询都走主库，主库同时承担写入和读取压力
-- 高并发下读查询与写入争抢锁、CPU、Buffer Pool，互相拖慢
-- 生产环境中主库还可能因大量 SELECT 占用连接数导致写入排队
SELECT *
FROM t_order_master
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

本案例用两张同构表模拟主库与从库：t_order_master 模拟主库表，t_order_replica 模拟从库表（结构相同，各 10 万行数据）。bad.sql 将读查询路由到主库表。

EXPLAIN 结果 ​

+----+-------------+----------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table          | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_order_master | NULL       | ref   | idx_user_created  | idx_user_created  | 8       | const|    2 |   100.00 | Backward index scan   |
+----+-------------+----------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+

为什么慢 ​

单看 EXPLAIN，这条查询本身并不慢--它走了索引、rows 很小（预估约 2 行）。真正的代价在架构层面：

  1. 读写争抢主库资源：主库同时处理大量 INSERT/UPDATE 和 SELECT，CPU、内存、I/O 被读写两类负载共享，互相挤占
  2. 锁竞争：写操作持有的行锁/间隙锁会阻塞读查询（或反过来），高并发下等待时间显著增加
  3. 连接数瓶颈：主库 max_connections 有限，大量读连接占用连接数，导致写入请求排队甚至被拒绝
  4. Buffer Pool 污染：大量扫描型读查询把冷数据页加载进 Buffer Pool，把热点写数据挤出缓存，写入变慢
  5. 无法横向扩展：主库单点，读 QPS 上限就是单机上限，无法通过加机器提升读吞吐
未分离（单主库承载全部流量）:
  写入峰值 5000 QPS + 读取峰值 50000 QPS
    -> 主库 CPU 长期 >80%，写入响应时间抖动
    -> 大量读连接占用连接池，写入排队
    -> Buffer Pool 被读查询污染，写入命中率下降
    -> 单机读上限就是系统读上限，无法扩展

实际耗时：约 4 ms（实测 MySQL 8.0.46，10 万行表）。在读写混合高并发场景下，主库因争抢资源，同类查询可能抖动到 20-50 ms。

核心认知

读写分离的本质不是优化单条 SQL 的执行计划，而是架构层面的负载分离--把读流量从主库剥离，让主库专注写入。单次查询的 EXPLAIN 可能完全相同，但高并发下的稳定性和系统整体吞吐能力截然不同。

优化方案 ​

good.sql ​

sql
-- good.sql: 读查询路由到从库，主库专注写入，读写互不干扰
-- 从库横向扩展可线性提升读吞吐，主库写入性能不受读流量影响
-- 注意: 对强一致读（如刚下单立即查询）仍应读主库，避免复制延迟读到旧数据
SELECT *
FROM t_order_replica
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

原理 ​

读写分离的核心思想是将读流量分流到从库，主库专注写入。本案例中 t_order_replica 模拟通过主从复制同步的从库读节点，结构与主库完全相同：

分离前: 单主库
  读写混合 -> 互相干扰 -> 写入抖动 + 读延迟不稳定
  读上限 = 单机上限，无法扩展

分离后: 1主 N从
  主库: 专注写入，响应稳定可预测
  从库: 专注读取，按需横向扩展
  -> 读吞吐随从库数量线性增长
  -> 写入性能不受读流量影响
  1. 读写互不干扰：从库只处理读，没有写入争抢锁、CPU、I/O，读查询响应稳定无抖动
  2. Buffer Pool 专属：从库 Buffer Pool 全部用于缓存读数据，命中率更高
  3. 连接数独立：从库有独立的连接池，读连接再多也不影响主库写入
  4. 横向扩展：读吞吐不够时加从库即可线性提升，主库写入能力不受影响
  5. 故障隔离：从库上的慢查询、大扫描不会拖垮主库的写入链路

复制延迟是核心风险

读写分离引入的最大问题是主从复制延迟。用户下单（写主库）后，binlog 异步复制到从库，这段时间内读从库会读到旧数据。典型延迟为毫秒级（正常）到秒级（大事务/高负载）。

解决策略：

  • 关键路径读主库：下单后立即查询、支付确认等强一致场景，强制读主库
  • 写后短时读主：写入后 N 秒内（如 1-3 秒）相关查询走主库，过了窗口再读从库
  • 半同步复制：主库写入后至少等待一个从库确认收到 binlog 才返回
  • 延迟监控降级：监控 Seconds_Behind_Master，延迟超阈值时读请求自动降级到主库

对比 ​

指标bad (读主库)good (读从库)提升
写读隔离读写混合争抢资源完全隔离写入稳定
读吞吐上限单机随从库线性扩展N 倍
主库连接占用高低（仅写入+强一致读）释放连接
一致性强（读主库）最终一致（有延迟）需权衡
单次查询耗时~4 ms（可能抖动到 50ms）~3 ms（稳定）更稳定
指标优化前 (bad)优化后 (good)
访问类型refref
使用索引idx_user_createdidx_user_created
扫描行数22
附加信息Backward index scan（读写混合争抢资源，高并发下抖动到 50ms）Backward index scan（从库只读，无写入干扰，响应稳定）

🚀 执行计划结构一致，但架构层面读写分离后主库写入稳定、读吞吐可线性扩展

本案例单表数据量小，单次查询差异不明显。读写分离的价值在高并发、大数据量场景下才充分体现：主库写入稳定 + 读吞吐可扩展。

避坑指南 ​

注意事项

  1. 不要盲目读从库：强一致场景（下单后查询、支付确认）必须读主库，否则会因复制延迟读到旧数据。

  2. 监控复制延迟：SHOW SLAVE STATUS 中的 Seconds_Behind_Master 要纳入监控告警，延迟超阈值时自动降级读主库。

  3. 从库也要建索引：从库索引缺失会导致读查询变慢，DDL 要同步到所有从库。

  4. 负载均衡要均匀：多从库时用代理（如 ProxySQL/HAProxy）做读流量分发，避免单台从库过载。

  5. 大事务会拖慢复制：批量 DELETE/UPDATE 拆小批，避免单个大事务阻塞从库回放。

  6. 主从切换要演练：主库故障时提升从库为新主库，需提前演练切换流程和数据校验。

  7. 考虑读写分离中间件：ShardingSphere-Proxy、ProxySQL、MyCat 等可自动路由读写，降低应用层复杂度。

5.7 vs 8.0 差异 ​

特性5.78.0
读写分离方案✅ 有效✅ 有效
并行复制（MTS）✅ 支持（基于库）✅ 增强（基于 WRITESET）
WRITESET 并行复制❌ 不支持✅ 支持
降序索引优化❌ Using filesort✅ Backward index scan
复制延迟相对较高更低（并行回放）

8.0 并行复制优势

执行计划结构在两个版本上一致，读写分离方案都有效。核心价值在于架构层面的读写分离，与版本无关。

8.0 在复制方面有显著增强：支持基于 WRITESET 的并行复制，从库回放 binlog 更快，复制延迟更低。5.7 的并行复制基于库级别（database-level），并行度受限。8.0 的 WRITESET 模式可以跨库并行回放，大幅降低延迟。此外，8.0 的 EXPLAIN 显示 Backward index scan（逆向索引扫描优化），而 5.7 显示 Using filesort（5.7 无降序索引优化）。

本地复现 ​

bash
# 默认在 MySQL 8.0 上运行
./scripts/run-case.sh 41-read-write-splitting

# 在 MySQL 5.7 上运行（对比）
./scripts/run-case.sh 41-read-write-splitting --ver 5.7

# 跳过造数据重跑
./scripts/run-case.sh 41-read-write-splitting --no-seed

MIT Licensed

Copyright © 2026 SQL Lab