mysql读写分离在高并发下如何配置_mysql架构优化方法

主从延迟大时select读到旧数据的根本原因是未做读写分离路由或弱一致性策略未处理好时序；mysql不自动分流，需在客户端或中间件层显式控制，将关联读强制路由至主库实现强一致性。

主从延迟大时，SELECT 为什么还会读到旧数据？

根本原因不是没走从库，而是应用没做读写分离的路由控制，或者用了弱一致性策略但没处理好时序。MySQL 自身不自动分流 SELECT 和 INSERT/UPDATE，所有流量默认打到主库——除非你在客户端或中间件层显式指定。

常见错误现象：INSERT 后立刻 SELECT，结果查不到刚插入的数据；或者查到了，但字段值还是旧的。这不是 MySQL bug，是主从复制存在天然延迟（尤其在高并发写入、大事务、网络抖动时）。

• 必须在业务代码或代理层（如 ShardingSphere-JDBC、MyCat、ProxySQL）中识别写操作上下文，并将后续关联读强制路由到主库（即“强一致性读”）
• 避免用 sleep(1) 或重试轮询来等从库同步——不可靠且放大延迟感知
• 如果用 read_committed 隔离级别 + 行级锁，仍不能解决跨会话读旧数据问题，因为从库回放是异步的，和事务隔离无关

max_allowed_packet 和 slave_parallel_workers 必须调大

高并发下主从同步卡顿，80% 是这两个参数没调。前者限制单个 binlog Event 大小，后者决定从库并行回放线程数。默认值在千级 QPS 下极易成为瓶颈。

典型表现：Seconds_Behind_Master 持续增长，SHOW SLAVE STATUS 中 Slave_SQL_Running_State 常停在 “Reading event from the relay log”；Relay_Log_Space 不断上涨。

• max_allowed_packet 建议设为 512M（主从两端都要改），否则大 UPDATE 或 LOAD DATA 会直接中断复制
• slave_parallel_workers 设为 CPU 核数 × 2（如 16 核设 32），同时开启 slave_parallel_type = LOGICAL_CLOCK，才能真正利用多核加速回放
• 注意 relay_log_recovery = ON 必须开启，否则从库异常重启后可能丢 relay log，导致同步断裂

ProxySQL 的 mysql_query_rules 怎么写才不误杀写请求？

用 ProxySQL 做读写分离时，规则匹配顺序和正则精度决定成败。很多团队配置完发现 UPDATE 被发到从库，报错 Error 1290 (HY000): The MySQL server is running with the --read-only option。

关键点在于：ProxySQL 默认按 rule_id 升序匹配，第一条命中即执行，不会继续往下找。所以写规则必须优先于泛化读规则。

• 写规则 match_pattern 推荐用 ^INSERT[[:space:]]+INTO|^UPDATE[[:space:]]+.*SET|^delete[[:space:]]+FROM|^REPLACE[[:space:]]+INTO，加 ^ 锚定开头，避免误匹配注释里的关键词
• 务必设置 apply = 1 并把该 rule_id 设为最小（如 1），再配读规则（rule_id=2）匹配 ^SELECT
• 不要依赖 username 或 schemaname 做路由，权限粒度太粗，且无法区分同用户下的读写混杂语句

从库只读开关 read_only=1 为什么有时不起作用？

read_only=1 确实能阻止普通用户写从库，但它对 SUPER 权限用户无效。而 MySQL 主从切换、监控探活、备份工具（如 mydumper）常以 root 或具有 SUPER 权限的账号连接，一不留神就往从库写了脏数据。

更隐蔽的问题是：某些 ORM（如 laravel 的 DB::statement()）或 dba 临时排障脚本，可能绕过读写分离中间件直连从库执行 TRUNCATE 或 DROP，导致主从数据彻底不一致。

• 生产环境必须配 super_read_only=1（MySQL 5.7.8+），它连 SUPER 用户都禁止写，且比 read_only 更严格
• 定期检查从库的 show global status like 'Com_insert%'，非零值说明有写入发生，要溯源
• 备份任务必须明确指定 --no-lock --single-transaction，避免在从库上加全局读锁影响复制回放

实际部署中最容易被忽略的是：主库故障切换后，原从库升主，但应用连接池里还缓存着旧的只读连接，导致新主库被当从库用。这需要配合服务发现（如 consul）或带健康检测的连接池（如 HikariCP 的 connection-test-query）来主动剔除失效节点。