14.8.4 读写分离与扩展性策略

在高可用场景中，读写分离与扩展性策略的核心目标是提升读性能、平滑写压力并确保主从切换与规则一致性。ProxySQL通过主机组、查询规则与复制拓扑联动，实现对读流量的精细控制和对写流量的稳定保障。

原理草图（读写分离与延迟摘除）：

读写分离策略设计要点：
- 主机组规划：主库与从库分别归属写组与读组，避免跨组写入。
- 规则优先级：写规则优先级高于读规则，确保事务与写请求不被错误分流。
- 会话一致性：启用事务黏滞（transaction persistent）与会话黏滞，防止读到延迟数据。
- 读写比例控制：通过权重与最大连接数限制读组分流比例，避免单点过载。
- 延迟感知：结合复制延迟检测策略，对延迟过高的从库进行摘除或降权。

扩展性策略：
- 水平扩展：新增只读从库加入读组，使用权重进行流量平衡。
- 纵向扩展：为写组配置更高性能主库，并在高峰期临时提升写组连接上限。
- 分区与分库：通过规则按库/表/用户分流，降低单库写压力，增强扩展能力。
- 弹性扩缩容：结合配置管理与模板化规则，在扩容时快速同步主机组、监控阈值与用户权限。

安装与初始化示例（含命令解释）：

# 安装 ProxySQL（以 Ubuntu 为例）
sudo apt-get update
sudo apt-get install -y proxysql

# 启动服务并确认监听端口
sudo systemctl enable --now proxysql
ss -lntp | grep -E '6032|6033'   # 6032 管理端口，6033 业务端口

读写分离配置示例（完整可执行）：

-- 连接管理端
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- 1) 配置后端 MySQL 节点
-- hostgroup 10: 写组，hostgroup 20: 读组
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections)
VALUES
(10,'10.0.0.10',3306,100,200),
(20,'10.0.0.11',3306,100,200),
(20,'10.0.0.12',3306,80,200);

-- 2) 配置账号
INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent)
VALUES ('appuser','AppPass',10,1);

-- 3) 读写分离规则：写/事务强制写组，读走读组
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(10,1,'^SELECT',20,1),
(20,1,'^SELECT.*FOR UPDATE',10,1),
(30,1,'^INSERT|^UPDATE|^DELETE|^REPLACE|^ALTER|^CREATE|^DROP|^TRUNCATE',10,1),
(40,1,'^START TRANSACTION|^BEGIN|^COMMIT|^ROLLBACK',10,1);

-- 4) 启用复制延迟感知（在 2 秒以上则摘除读组）
UPDATE global_variables SET variable_value='2000'
WHERE variable_name='mysql-monitor_slave_lag_when_null';

-- 5) 载入到运行态并写入磁盘
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

关键参数解释：
- hostgroup_id：分组路由依据，写组/读组必须清晰区分。
- weight：读组内流量分配权重，权重越高分流越多。
- transaction_persistent=1：事务会话强制路由至写组，避免读到延迟数据。
- match_digest：基于 SQL 正则匹配规则，注意优先级从 rule_id 小到大。

验证与观测示例：

-- 查看后端状态与权重
SELECT hostgroup_id,hostname,port,status,weight,max_connections
FROM runtime_mysql_servers;

-- 查看读写规则命中统计
SELECT rule_id,match_digest,apply,hit_count
FROM stats_mysql_query_rules
ORDER BY hit_count DESC;

-- 查看当前连接分布
SELECT hostgroup,conn_used,conn_free,Queries
FROM stats_mysql_connection_pool;

读写分离扩展示例（新增从库并降权）：

-- 新增从库 10.0.0.13，初始权重 50
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections)
VALUES (20,'10.0.0.13',3306,50,200);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- 降低延迟高的从库权重
UPDATE mysql_servers SET weight=20
WHERE hostgroup_id=20 AND hostname='10.0.0.12';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

排错与常见问题：
1) 读请求落到写组
- 检查规则优先级与正则匹配：

SELECT rule_id,match_digest,destination_hostgroup
FROM runtime_mysql_query_rules ORDER BY rule_id;

确认规则命中：

SELECT rule_id,hit_count FROM stats_mysql_query_rules;

2) 读组无可用节点
- 查看从库状态与延迟：

SELECT hostgroup_id,hostname,status,latency_us
FROM runtime_mysql_servers WHERE hostgroup_id=20;

临时将读流量回切到写组：

UPDATE mysql_query_rules SET destination_hostgroup=10
WHERE rule_id=10;
LOAD MYSQL QUERY RULES TO RUNTIME;

3) 事务读到旧数据
- 确认事务黏滞已开启：

SELECT username,transaction_persistent
FROM runtime_mysql_users WHERE username='appuser';

实践练习：
- 练习1：新增一台只读从库，设置权重为 30，并观察读流量分配变化。
- 练习2：模拟从库延迟（在从库上人工 sleep），观察 ProxySQL 是否摘除该节点。
- 练习3：编写两条 SQL（SELECT 与 UPDATE），用 stats_mysql_query_rules 验证命中次数增长。

风险与优化建议：
- 避免强一致场景下读写分离，必要时使用强制路由或读写一致性参数。
- 定期校验规则与路由命中率，防止规则漂移导致流量异常。
- 在扩展过程中保持规则与配置一致性，确保主从切换不影响业务路由。