ProxySQL 读写分离与连接池优化：MySQL 8.0 生产可验证实战指南

本文面向正在使用 MySQL 8.0 的团队，目标是在不改动应用大框架的前提下，通过 ProxySQL 实现稳定的读写分离与连接池复用，同时兼顾事务一致性与复制延迟控制。所有配置与参数均给出可执行示例，便于快速验证与上线落地。

## 适用版本与前提

• 数据库版本：MySQL 8.0.18+（推荐 8.0.30 及以上，支持更完善的性能特性）。
• 中间件版本：ProxySQL 2.4+（推荐 2.6.x/2.7.x，具备更完善的监控与规则能力）。
• 复制拓扑：一主多从（半同步或异步均可；读一致性要求高的场景推荐半同步）。
• 管理接口：使用 ProxySQL Admin 接口进行配置与持久化（`LOAD ... TO RUNTIME` / `SAVE ... TO DISK`）。

## 架构与 Hostgroup 规划

建议使用两个 Hostgroup：

• 写库（writer）：`10`
• 读库（reader）：`20`

读库设置 `read_only=1`，并通过复制延迟阈值确保在延迟过大时自动下线或回退至写库。

## 安装与基础配置

### 1. 配置后端 MySQL 监控用户（用于 ProxySQL 健康检查与复制状态）

-- 在主库创建监控用户（从库可同步）
CREATE USER 'monitor'@'%' IDENTIFIED BY 'StrongPass#2024';
GRANT REPLICATION CLIENT, PROCESS, SHOW DATABASES ON *.* TO 'monitor'@'%';

### 2. 在 ProxySQL Admin 中注册后端实例与监控参数

-- 连接到 ProxySQL Admin（示例：proxysql-admin 或 mysql -h 127.0.0.1 -P6032 -u admin -p）

-- 定义后端服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port, read_only, weight, max_connections, status)
VALUES
  (10, '10.0.0.10', 3306, 0, 100, 2000, 'ONLINE'),  -- writer
  (20, '10.0.0.11', 3306, 1, 100, 2000, 'ONLINE'),  -- reader1
  (20, '10.0.0.12', 3306, 1, 100, 2000, 'ONLINE');  -- reader2

-- 配置复制组映射，便于自动角色识别与切换
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment)
VALUES (10, 20, 'rw split');

-- 监控配置：启用监控并设置监控凭据
SET mysql-monitor_enabled = 1;
SET mysql-monitor_username = 'monitor';
SET mysql-monitor_password = 'StrongPass#2024';

-- 复制延迟阈值（单位：秒）；超过阈值的从库将被标记不健康
SET mysql-default_max_replication_lag = 2;

-- 连接池与线程
SET mysql-threads = 4;                     -- 根据 CPU 核心与并发调整
SET mysql-connect_timeout_server = 2000;   -- 毫秒，后端连接超时
SET mysql-poll_timeout = 2000;             -- 毫秒，I/O 轮询等待

-- 加载到运行态并持久化到磁盘
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

### 3. 注册应用访问账号（转发到后端 MySQL）

-- 应用使用的数据库账号信息（ProxySQL 层持有认证与路由）
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent)
VALUES('appuser', 'AppStrong#2024', 10, 1);  -- 默认指向写库，开启事务粘滞

LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

## 查询规则：读写分离与事务粘滞

目标：

• 非事务的普通 `SELECT` 走读库（`20`）。
• 事务内所有语句（包括 `SELECT`）走写库（`10`）。
• 带锁读（如 `SELECT ... FOR UPDATE`）强制走写库。
• 事务结束后清除粘滞（`COMMIT`/`ROLLBACK`）。

示例规则（请按需调整 `rule_id` 保持唯一）：

-- 事务开始：设置会话粘滞标志，路由写库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, flagOUT)
VALUES (100, 1, '^BEGIN', 10, 1, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, flagOUT)
VALUES (101, 1, '^START\\s+TRANSACTION', 10, 1, 1);

-- 带锁查询或修改语句：强制写库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (110, 1, '^SELECT.*FOR\\s+UPDATE', 10, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (111, 1, '^(INSERT|UPDATE|DELETE|REPLACE)', 10, 1);

-- 非事务的普通 SELECT 走读库（当 flag 未设置时）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, flagIN)
VALUES (120, 1, '^SELECT', 20, 1, 0);

-- 事务内的所有语句统一走写库（flagIN=1）
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, flagIN)
VALUES (130, 1, '.*', 10, 1, 1);

-- 事务结束：清除粘滞
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, flagOUT)
VALUES (140, 1, '^COMMIT', 10, 1, 0);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, flagOUT)
VALUES (141, 1, '^ROLLBACK', 10, 1, 0);

LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

## 复制延迟与健康检查

• ProxySQL 会周期性检查后端的 `read_only` 与复制状态，并基于 `mysql-default_max_replication_lag` 将延迟过大的从库标记为不健康。
• 可通过 `stats_mysql_connection_pool` 与 `stats_mysql_query_rules` 观察当前路由与命中情况。

SELECT hostgroup, srv_host, status, ConnUsed, Queries FROM stats_mysql_connection_pool ORDER BY hostgroup, srv_host;
SELECT rule_id, hits, match_pattern, destination_hostgroup FROM stats_mysql_query_rules ORDER BY rule_id;

## 验证步骤（可复现）

1. 基线验证：

• 在应用或客户端执行 `SELECT @@version, @@hostname;`，观察是否命中读库主机名。
• 执行简单事务：

     START TRANSACTION;
     SELECT @@hostname;
     COMMIT;

期望两次查询均命中写库（事务粘滞生效）。

2. 带锁读验证：

   SELECT id FROM orders WHERE id=1 FOR UPDATE;

期望命中写库，避免在从库上加锁导致不一致。

3. 复制延迟验证：

• 暂停从库复制或人为制造延迟，观察 `stats_mysql_connection_pool` 中读库的 `status`/`ConnUsed` 是否下降。
• 恢复后确认读库重新参与查询分担。

4. 故障回退验证：

• 下线某个读库（`SET ONLINE/OFFLINE_SOFT` 或停止实例），确认查询自动落在剩余读库或回退写库。

## 参数建议与调优要点

• `mysql-threads`：与 CPU 核心数匹配或略低，避免线程过多导致争用。
• `max_connections`（`mysql_servers` 表字段）：根据后端实例容量与连接复用策略设置，常见 1000–5000。
• `mysql-connect_timeout_server` / `mysql-poll_timeout`：在高延迟网络中适度上调，避免误判后端不可用。
• 规则命中顺序：`rule_id` 越小越先匹配；确保事务/锁规则优先于通用 `SELECT` 规则。
• 从库过滤：必要时使用 `weight` 控制负载分担，或临时将延迟高的实例 `OFFLINE_SOFT`。

## 注意事项（一致性与安全）

• 强一致读：读取后立刻读取必须一致的场景，建议在应用层显式走写库（如关键下单、核销场景）。
• 只读事务：MySQL 的 `START TRANSACTION READ ONLY` 可与规则结合优化，但仍需谨慎评估复制延迟。
• 连接池隔离：不同应用/租户建议使用独立账号与默认 Hostgroup，避免规则互相干扰。
• 密码与凭据：生产环境请使用安全的密码管理与 SSL 连接（`use_ssl=1`）。

## 相关文章（同分类热门）

• [MySQL 8.0 复制与高可用：Group Replication 与 InnoDB Cluster 实战指南](./MySQL 8.0 复制与高可用：Group Replication 与 InnoDB Cluster 实战指南.md)
• [MySQL 慢查询诊断与 EXPLAIN ANALYZE 实战指南](./MySQL 慢查询诊断与 EXPLAIN ANALYZE 实战指南.md)
• [MySQL 索引设计与覆盖索引的可验证实践指南](./MySQL 索引设计与覆盖索引的可验证实践指南.md)

## 结语

通过明确的 Hostgroup 规划、事务粘滞与复制延迟控制，ProxySQL 能在生产环境稳定实现 MySQL 的读写分离与连接池优化。按本文的参数与验证步骤逐项落地，可在不侵入应用的前提下获得确定性的性能提升与一致性保障。