前阵子帮某电商平台优化性能，他们的主库 QPS 长期维持在 8000+，其中 70% 是查询操作（查订单、商品详情），导致主库 CPU 使用率经常飙到 90% 以上，偶尔还会因为锁等待超时丢单。后来用 ProxySQL 做了读写分离，把读请求全部分流到 3 个从库，主库 QPS 直接降到 2400，CPU 使用率稳定在 30%，再也没出现过超时问题。
读写分离是 MySQL 性能优化的 “分水岭” 方案 —— 当单库读写压力过大时，通过 “主库写、从库读” 的方式分散负载，能让系统支撑的并发量提升 3-5 倍。但很多人只知道 “读走从库”，却不懂 “怎么判断 SQL 该走主库还是从库”“从库延迟了怎么办”“中间件怎么选”，结果要么分离不彻底，要么读从库时拿到旧数据。
这篇是 MySQL 专栏的性能分流实战篇，核心目标是帮你从 “知道读写分离” 进阶到 “能在生产环境落地 ProxySQL 方案”。全文用电商订单系统的真实场景贯穿，包含 “ProxySQL 搭建全步骤”“读写规则配置”“从库延迟从 3 秒降到 0.1 秒的优化”，每步都附可复现的命令和验证方法，新手跟着做也能快速上手。
一、先搞懂：为什么要做读写分离？（用 “超市收银” 类比）
单库 MySQL 就像 “超市只有一个收银台”—— 不管是买东西（写操作，如下单）还是查价格（读操作，如查库存），都得排队。当人多了（并发高了），收银台就会堵死。
读写分离就像 “超市分设‘买单通道’和‘咨询台’”：
买单通道（主库）：只处理 “付钱”（写操作：INSERT/UPDATE/DELETE），专注高效；咨询台（从库）：处理 “查价格、问库存”（读操作：SELECT），多个咨询台分流压力。
1. 读写分离能解决的 3 个核心问题
主库读压力过大：电商大促时，“查商品详情” 的请求是 “下单” 的 10 倍以上，全走主库会导致主库响应变慢；主库连接数不足：MySQL 默认最大连接数 151，大量读请求占满连接，写请求会因 “连接超时” 失败；异地访问延迟：北京主库，广州用户查数据，直接访问主库延迟 50ms，访问广州从库延迟 10ms。
2. 3 种常见读写分离方案对比（企业级选型指南）
| 方案类型 | 实现方式 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|---|
| 应用层分离 | 代码中判断 SQL 类型，写连主库，读连从库 | 简单直接，无需额外组件 | 代码侵入性强，新增从库需改代码 | 小型项目，开发资源紧张 |
| 中间件分离 | 用 ProxySQL/MyCat 做中间层，自动路由 | 无代码侵入，支持动态扩缩容 | 需维护中间件，有一定学习成本 | 中大型项目，核心业务 |
| 驱动层分离 | 用 MySQL Connector/J 的读写分离功能 | 轻量，对应用透明 | 规则配置灵活度低，不支持复杂路由 | Java 项目，简单读写分离需求 |
企业级结论：90% 的中大型项目会选ProxySQL—— 它是 MySQL 官方推荐的中间件，支持自动识别读写 SQL、故障检测、从库权重分配，稳定性经过阿里、腾讯等大厂验证。
二、ProxySQL 核心原理：它是怎么 “分辨” 读写请求的？
ProxySQL 就像 “超市入口的引导员”，会先看你是 “要买单” 还是 “要咨询”，再引导到对应通道。它的核心工作流程分 3 步：
1. 连接池管理：复用连接，减少开销
应用不直接连数据库，而是连 ProxySQL 的 3306 端口；ProxySQL 会和后端主从库建立 “长连接” 并维护连接池，应用请求来时直接复用连接（避免频繁创建连接的开销）；比如应用需要 1000 个连接，ProxySQL 只需和主库保持 200 个长连接，大幅减少数据库的连接压力。
2. 查询路由：判断 SQL 该走主库还是从库
ProxySQL 通过 “规则匹配” 决定 SQL 的走向，核心规则如下：
写操作强制走主库：只要 SQL 包含
INSERT
UPDATE
DELETE
ALTER
SELECT ... FOR UPDATE
SELECT
SELECT
3. 健康检查：自动剔除故障节点
ProxySQL 会定期（默认每 200ms）给主从库发 “心跳检测”（如
SELECT 1
三、实战环境：基于 MGR 集群搭建读写分离
我们基于上一篇的 3 节点 MGR 集群（1 主 2 从），再部署 ProxySQL 中间层，架构如下：
ProxySQL：192.168.1.200（CentOS 7），负责路由请求；主库（MGR PRIMARY）：192.168.1.100，处理写请求；从库 1（MGR SECONDARY）：192.168.1.101，处理读请求；从库 2（MGR SECONDARY）：192.168.1.102，处理读请求。
1. 前置准备（避坑第一步）
所有节点网络互通：ProxySQL 能 ping 通主从库（192.168.1.100/101/102），主从库也能 ping 通 ProxySQL；主从库已开启读写分离基础：主库可写，从库
read_only=ON
四、实战步骤：部署 ProxySQL 并配置读写分离
1. 步骤 1：安装 ProxySQL（192.168.1.200）
ProxySQL 有官方 yum 源，安装简单：
bash
# 1. 添加ProxySQL yum源
cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql_repo]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/7
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
# 2. 安装ProxySQL
yum install -y proxysql
# 3. 启动ProxySQL并设为开机自启
systemctl start proxysql
systemctl enable proxysql
# 4. 验证是否启动成功（默认管理端口6032，客户端端口3306）
netstat -tulpn | grep proxysql
# 输出应包含：
# tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN ...
# tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN ...2. 步骤 2：登录 ProxySQL 管理界面
ProxySQL 有两个端口：
3306：客户端连接端口（应用用这个）；6032：管理端口（DBA 配置用这个，默认账号 admin，密码 admin）。
bash
# 登录管理界面（端口6032）
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt 'ProxySQLAdmin> '3. 步骤 3：配置后端主从库节点
ProxySQL 通过
mysql_servers
（1）添加主库节点（192.168.1.100）
sql
-- ProxySQL管理界面执行：添加主库，设置主机组ID=1（主库专用组）
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (1, '192.168.1.100', 3306, 100);
hostgroup_id=1
weight=100
（2）添加从库节点（192.168.1.101 和 192.168.1.102）
sql
-- 添加从库1到主机组ID=2（从库专用组），权重100
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (2, '192.168.1.101', 3306, 100);
-- 添加从库2到主机组ID=2，权重200（权重高的从库分配更多请求）
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (2, '192.168.1.102', 3306, 200);
hostgroup_id=2
weight=200
（3）加载配置到运行时并保存
ProxySQL 的配置分 “内存”“磁盘”“运行时”，需要手动同步：
sql
-- 加载内存配置到运行时
LOAD MYSQL SERVERS TO RUNTIME;
-- 保存配置到磁盘（重启后不丢失）
SAVE MYSQL SERVERS TO DISK;
-- 查看已添加的节点
SELECT hostgroup_id, hostname, port, weight, status FROM mysql_servers;结果：3 个节点的
status
ONLINE
4. 步骤 4：配置数据库访问账号（ProxySQL 连接主从库用）
ProxySQL 需要一个数据库账号，用于连接后端主从库（建议和业务账号分开）：
（1）先在主库创建 ProxySQL 专用账号（主库执行）
sql
-- 主库（192.168.1.100）执行：创建账号proxysql，允许ProxySQL IP连接
CREATE USER 'proxysql'@'192.168.1.200' IDENTIFIED WITH mysql_native_password BY 'Proxy@123';
-- 授予必要权限（查询、复制权限）
GRANT SELECT, REPLICATION CLIENT ON *.* TO 'proxysql'@'192.168.1.200';
-- 刷新权限（从库会同步这个账号，无需单独创建）
FLUSH PRIVILEGES;（2）在 ProxySQL 中添加这个账号
sql
-- ProxySQL管理界面执行：添加账号到mysql_users表
INSERT INTO mysql_users (username, password, default_hostgroup, active)
VALUES ('proxysql', 'Proxy@123', 1, 1);
-- default_hostgroup=1：默认走主库组（后续规则会覆盖这个默认）
-- 加载并保存配置
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
-- 查看账号
SELECT username, default_hostgroup, active FROM mysql_users;5. 步骤 5：配置读写分离规则（核心）
通过
mysql_query_rules
（1）规则 1：写操作（INSERT/UPDATE/DELETE）走主库（hostgroup=1）
sql
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^INSERT|^UPDATE|^DELETE|^ALTER|^DROP|^CREATE', 1, 1);
-- match_digest：正则匹配SQL开头，包含写操作关键字
-- destination_hostgroup=1：路由到主库组
-- apply=1：匹配后不再检查其他规则（2）规则 2：加锁读（SELECT … FOR UPDATE）走主库
sql
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT.*FOR UPDATE', 1, 1);
-- 加锁读需要最新数据，必须走主库（3）规则 3：普通读（SELECT）走从库（hostgroup=2）
sql
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (3, 1, '^SELECT', 2, 1);
-- 普通查询走从库组（4）规则 4：特殊表（如秒杀库存表）强制走主库
sql
INSERT INTO mysql_query_rules (rule_id, active, match_digest, match_pattern, destination_hostgroup, apply)
VALUES (4, 1, '^SELECT', 'FROM seckill_stock', 1, 1);
-- match_pattern：匹配包含“FROM seckill_stock”的SELECT语句
-- 优先级高于规则3（rule_id=4 < 3，ProxySQL按rule_id升序匹配）（5）加载并保存规则
sql
-- 加载规则到运行时
LOAD MYSQL QUERY RULES TO RUNTIME;
-- 保存到磁盘
SAVE MYSQL QUERY RULES TO DISK;
-- 查看所有规则
SELECT rule_id, match_digest, destination_hostgroup FROM mysql_query_rules ORDER BY rule_id;6. 步骤 6：配置健康检查（自动剔除故障节点）
ProxySQL 默认会检查节点健康，我们可以优化检查参数：
sql
-- 设置心跳检测SQL（默认是SELECT 1，可自定义）
UPDATE global_variables SET variable_value='SELECT 1' WHERE variable_name='mysql-monitor_ping_interval';
-- 检测间隔：200ms一次
UPDATE global_variables SET variable_value='200' WHERE variable_name='mysql-monitor_ping_interval';
-- 连续3次失败则标记为离线
UPDATE global_variables SET variable_value='3' WHERE variable_name='mysql-monitor_ping_max_failures';
-- 加载并保存配置
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;五、测试验证：读写分离是否生效？
我们用客户端连接 ProxySQL，执行读写操作，验证是否路由到正确的库。
1. 连接 ProxySQL 的客户端端口（3306）
bash
# 用主库的业务账号连接ProxySQL（注意：不是ProxySQL的管理账号）
mysql -u root -p -h 192.168.1.200 -P 3306 --prompt 'ProxySQLClient> '2. 测试写操作（应路由到主库 192.168.1.100）
sql
-- 执行INSERT（写操作）
INSERT INTO mgr_test.pay_order (order_no, amount) VALUES ('P20251110001', 399.00);验证：查看主库和从库的数据
主库（192.168.1.100）能查到这条记录；从库（192.168.1.101/102）也能查到（MGR 同步），但写入路由是主库。
用 ProxySQL 日志确认路由
bash
# 在ProxySQL服务器查看日志（记录SQL路由情况）
tail -f /var/lib/proxysql/proxysql.log日志中会有类似记录：
INSERT ... routed to hostgroup 1
3. 测试普通读操作（应路由到从库 101 或 102）
sql
-- 执行SELECT（普通读）
SELECT * FROM mgr_test.pay_order;验证：查看 ProxySQL 的查询统计
sql
-- 在ProxySQL管理界面执行：查看各节点处理的查询数
SELECT hostgroup, srv_host, queries FROM stats_mysql_connection_pool;结果：从库 101 和 102 的
queries
4. 测试特殊表读操作（应路由到主库）
sql
-- 先创建秒杀库存表（主库执行）
CREATE TABLE seckill_stock (id INT PRIMARY KEY, goods_id INT, stock INT);
INSERT INTO seckill_stock VALUES (1, 1001, 50);
-- 通过ProxySQL查询该表
SELECT * FROM seckill_stock;验证：查询统计显示路由到主库
stats_mysql_connection_pool
queries
六、企业级问题解决：从库延迟导致 “读旧数据” 怎么办？
读写分离最大的坑是 “从库延迟”—— 主库写入数据后，从库还没同步，此时读从库会拿到旧数据（比如用户刚下单，查订单列表显示 “未下单”）。
1. 先量化延迟：怎么看从库延迟了多久？
用 ProxySQL 的监控表查看从库延迟：
sql
-- ProxySQL管理界面执行：查看各节点延迟（单位：秒）
SELECT srv_host, Replication_lag FROM stats_mysql_monitor_replication_lag;正常情况：延迟≈0 秒；异常情况：大事务后延迟可能达 3-10 秒（比如批量插入 10 万条数据）。
2. 解决方案 1：延迟阈值过滤（超过阈值的从库不分配请求）
配置 ProxySQL，只给延迟 < 1 秒的从库分配请求：
sql
-- 设置从库最大允许延迟1秒
UPDATE mysql_servers SET max_replication_lag=1 WHERE hostgroup_id=2;
-- 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;当从库延迟≥1 秒，ProxySQL 会自动将其从可用列表中剔除；所有从库都延迟时，会 “降级” 到主库读（避免返回旧数据）。
3. 解决方案 2：关键业务强制读主库（如订单查询）
通过规则配置，让 “订单查询” 走主库：
sql
-- 添加规则：SELECT * FROM pay_order 走主库（rule_id=0，优先级最高）
INSERT INTO mysql_query_rules (rule_id, active, match_digest, match_pattern, destination_hostgroup, apply)
VALUES (0, 1, '^SELECT', 'FROM pay_order', 1, 1);
-- 加载规则
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;4. 解决方案 3：优化从库同步速度（根本解决）
从库开启并行复制（见 MGR 章节），提升同步效率；主库避免大事务（如拆分 “批量插入 10 万条” 为 100 次 “插入 1000 条”）；主库 binlog 刷盘优化：
sync_binlog=1
sync_binlog=100
实战效果：某电商通过这 3 个方案，从库延迟从平均 3 秒降到 0.1 秒，用户 “下单后查不到订单” 的投诉降为 0。
七、ProxySQL 进阶优化：企业级部署的 5 个关键配置
1. 连接池优化（减少连接开销）
sql
-- 设置每个后端节点的最大连接数（根据数据库能承受的连接数调整）
UPDATE global_variables SET variable_value='200' WHERE variable_name='mysql-max_connections';
-- 连接池超时时间（空闲连接保留300秒）
UPDATE global_variables SET variable_value='300000' WHERE variable_name='mysql-connect_timeout_server';
-- 加载配置
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;2. 缓存高频读请求（减轻从库压力）
ProxySQL 支持缓存查询结果，适合 “商品详情” 等不常变的高频查询：
sql
-- 开启查询缓存
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-query_cache_enabled';
-- 添加缓存规则：缓存商品详情查询（10分钟过期）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, cache_ttl, apply)
VALUES (5, 1, 'SELECT.*FROM goods WHERE id=', 600000, 1);
-- cache_ttl=600000毫秒=10分钟
-- 加载规则
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;3. 读写分离与 MGR 自动切换联动
当 MGR 主库宕机，新主库产生后，ProxySQL 能自动识别新主库：
sql
-- 开启MGR监控（自动发现新主库）
UPDATE global_variables SET variable_value='1' WHERE variable_name='mysql-monitor_groupreplication_healthcheck_enabled';
-- 主库组ID=1，从库组ID=2，自动同步MGR角色变化
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type)
VALUES (1, 2, 'groupreplication');
-- 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;4. 监控 ProxySQL 性能（用 stats 库）
定期查询以下表，监控中间件健康：
stats_mysql_query_digest
stats_mysql_connection_pool
stats_proxysql_servers
5. 高可用：ProxySQL 集群部署（避免单点故障）
单个 ProxySQL 是单点，可部署 2 个 ProxySQL 节点，用 Keepalived 做 VIP（虚拟 IP）：
应用连接 VIP（如 192.168.1.201）；Keepalived 监控 ProxySQL 状态，故障时自动切换 VIP 到备用节点。
八、读写分离的 “落地 Checklist”
在生产环境落地读写分离前，务必检查以下事项：
从库延迟：用
max_replication_lag
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
