mysql在高并发环境下的读写分离与负载均衡-mysql教程-PHP中文网

读写分离必须用proxysql等sql感知代理而非纯应用层或tcp代理；mysql原生不支持自动分离，可靠方案仅proxysql、mysql router或shardingsphere-jdbc三类。

mysql在高并发环境下的读写分离与负载均衡

读写分离必须用中间件还是 Proxy？

MySQL 原生不支持自动读写分离，SELECT 和 INSERT/UPDATE/DELETE 都走同一连接，靠应用层或代理层识别 SQL 类型并路由。硬编码判断 SELECT 开头容易出错（比如注释里含 SELECT、预编译参数干扰），所以生产环境基本不用纯应用层判断。

真正可靠的方案只有两类：

MySQL Router（官方轻量代理，支持读写分离 + 自动故障转移，但不支持权重、读库负载均衡）
ProxySQL（推荐），能按规则匹配 SQL 类型、用户、schema，支持读写分离 + 多读库加权轮询 + 连接池 + 查询缓存 + 实时统计
自研中间件（如 ShardingSphere-JDBC）适合 Java 生态，但需承担维护成本和 SQL 兼容风险

别用 HAProxy 或 nginx 做 MySQL 读写分离——它们只做 TCP 层转发，无法解析 SQL，只能靠端口区分（比如 3307 转写库、3308 转读库），运维和扩缩容极不灵活。

主从延迟导致读不到最新数据怎么办？

这是读写分离最常被低估的问题：SELECT 发到从库后，可能查不到刚 INSERT 的记录，尤其在秒杀、订单创建等强一致性场景下会直接引发业务异常。

解决思路不是“避免延迟”，而是“控制读请求的路由策略”：

对关键事务后的读操作，强制走主库（例如：下单成功后立刻查订单详情，用 /*+ FORCE_MASTER */ 注释或设置 transaction_isolation = 'READ-COMMITTED' + 应用层标记）
用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 的语句，ProxySQL 可配置自动发往主库
监控 Seconds_Behind_Master，当延迟 > 1s 时，临时将该从库从读节点池摘除（ProxySQL 支持 mysql_servers 表动态更新）
避免在从库执行 LOAD DATA INFILE 或大事务回放，这类操作会显著拉长 relay log 回放时间

ProxySQL 配置读写分离的关键参数有哪些？

以下是最小可行配置中不可跳过的几项，漏掉任意一个都可能导致读写错路或连接打满：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES
(1,1,'^SELECT',2,1),        -- 所有 SELECT 走 hostgroup 2（读组）
(2,1,'^INSERT|^UPDATE|^DELETE|^REPLACE|^SET',1,1); -- 写操作走 hostgroup 1（主库）
<p>INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'production');</p><p>-- 必须 reload，否则规则不生效
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;</p><p>-- 检查是否生效
SELECT * FROM stats_mysql_query_rules;</p>

注意：match_digest 是正则匹配摘要（非原始 SQL），比 match_pattern 性能高很多；writer_hostgroup 必须唯一，reader_hostgroup 可包含多个从库；如果主库也加入 reader_hostgroup，会导致部分读请求打到主库，破坏负载均衡意图。

从库数量增加后查询响应反而变慢？

常见错觉：加从库 = 读能力线性提升。实际中经常出现加到第 4 台从库后，平均响应时间上升——根本原因不是数据库瓶颈，而是连接调度与网络开销反超收益。

排查和优化点：

检查 ProxySQL 的 mysql_servers 表中每台从库的 weight 是否设为相同值（默认都是 1），否则低配从库会被压垮
确认客户端连接池最大连接数 × 并发请求数 ≤ 单台从库的 max_connections，否则大量 Too many connections 错误会触发重试风暴
开启 mysql-monitor 模块，它默认每 60 秒执行 SELECT @@read_only 检测从库状态；若从库响应慢，Monitor 会频繁失败并反复尝试，拖累整个 ProxySQL 线程池
用 SHOW PROCESSLIST 在从库上观察是否有大量 Sleep 连接未释放，这说明应用没正确关闭连接，连接池配置不合理