MySQL教程 / 第 230 节

第23章：读写分离

读写分离是提高MySQL并发能力的重要手段

23.1 读写分离概述

23.1.1 什么是读写分离？

定义：

写操作（INSERT、UPDATE、DELETE）→ 主库
读操作（SELECT）→ 从库
分散数据库压力，提高并发能力

架构图：

        应用程序
           ↓
      读写分离中间件
       ↙        ↘
    写操作      读操作
      ↓          ↓
    主库    →   从库1
            →   从库2
            →   从库3

23.1.2 为什么需要读写分离？

问题场景：

❌ 读操作占比80%-90%
❌ 单台数据库压力大
❌ 查询慢，影响业务

读写分离的优势：

✅ 分散读压力（多个从库）
✅ 提高并发能力
✅ 提高查询性能
✅ 提高可用性（主库故障，从库可读）

适用场景：

✅ 读多写少（读写比 > 3:1）
✅ 高并发查询
✅ 报表查询
✅ 数据分析

23.2 读写分离实现方式

23.2.1 应用层实现

原理：

应用程序判断SQL类型
写操作连接主库
读操作连接从库

示例代码（Java）：

public class DataSourceRouter {
    private DataSource masterDataSource;  // 主库
    private List<DataSource> slaveDataSources;  // 从库列表
    
    public DataSource getDataSource(String sql) {
        if (isWriteOperation(sql)) {
            return masterDataSource;  // 写操作用主库
        } else {
            return getSlaveDataSource();  // 读操作用从库
        }
    }
    
    private boolean isWriteOperation(String sql) {
        String upperSql = sql.trim().toUpperCase();
        return upperSql.startsWith("INSERT") ||
               upperSql.startsWith("UPDATE") ||
               upperSql.startsWith("DELETE");
    }
    
    private DataSource getSlaveDataSource() {
        // 负载均衡：随机选择一个从库
        int index = new Random().nextInt(slaveDataSources.size());
        return slaveDataSources.get(index);
    }
}

优点：

✅ 灵活，可自定义规则
✅ 性能好（无中间件）
✅ 可以精确控制

缺点：

❌ 代码侵入性强
❌ 维护成本高
❌ 需要处理主从延迟

23.2.2 中间件实现

常用中间件：

MySQL Router：MySQL官方
ProxySQL：功能强大（推荐）⭐⭐⭐⭐⭐
MaxScale：MariaDB开发
MyCat：国产，功能丰富
Atlas：360开源

中间件架构：

应用程序
   ↓
中间件（ProxySQL）
   ↓
主库 + 从库

优点：

✅ 对应用透明
✅ 无代码侵入
✅ 统一管理
✅ 支持负载均衡

缺点：

❌ 增加一层网络开销
❌ 中间件本身可能成为瓶颈
❌ 需要维护中间件

23.3 ProxySQL实战 ⭐⭐⭐⭐⭐

23.3.1 ProxySQL安装

# CentOS安装
cat <<EOF > /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF

yum install proxysql

# Ubuntu安装
wget https://github.com/sysown/proxysql/releases/download/v2.4.4/proxysql_2.4.4-ubuntu20_amd64.deb
dpkg -i proxysql_2.4.4-ubuntu20_amd64.deb

# 启动ProxySQL
systemctl start proxysql
systemctl enable proxysql

# 查看状态
systemctl status proxysql

# 连接ProxySQL管理端口
mysql -u admin -padmin -h 127.0.0.1 -P6032

23.3.2 ProxySQL配置

-- 连接ProxySQL管理端口
mysql -u admin -padmin -h 127.0.0.1 -P6032

-- 1. 添加MySQL服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (1, '192.168.1.10', 3306);  -- 主库，hostgroup_id=1

INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (2, '192.168.1.11', 3306);  -- 从库1，hostgroup_id=2

INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (2, '192.168.1.12', 3306);  -- 从库2，hostgroup_id=2

-- 加载到运行时
LOAD MYSQL SERVERS TO RUNTIME;

-- 保存到磁盘
SAVE MYSQL SERVERS TO DISK;

-- 查看服务器列表
SELECT * FROM mysql_servers;

-- 2. 配置监控账号
INSERT INTO mysql_users(username, password, default_hostgroup) 
VALUES ('monitor', 'monitor_password', 1);

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

-- 3. 配置应用账号
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent) 
VALUES ('app_user', 'app_password', 1, 1);
-- default_hostgroup=1：默认使用主库
-- transaction_persistent=1：事务内的所有语句都路由到同一个hostgroup

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

-- 4. 配置读写分离规则
-- 写操作路由到hostgroup 1（主库）
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 1, 1);

INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT.*LOCK IN SHARE MODE$', 1, 1);

-- 读操作路由到hostgroup 2（从库）
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (10, 1, '^SELECT', 2, 1);

-- 写操作路由到hostgroup 1（主库）
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (20, 1, '^INSERT', 1, 1);

INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (21, 1, '^UPDATE', 1, 1);

INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (22, 1, '^DELETE', 1, 1);

-- 加载规则
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- 查看规则
SELECT rule_id, active, match_pattern, destination_hostgroup 
FROM mysql_query_rules 
ORDER BY rule_id;

-- 5. 配置健康检查
UPDATE global_variables 
SET variable_value='monitor' 
WHERE variable_name='mysql-monitor_username';

UPDATE global_variables 
SET variable_value='monitor_password' 
WHERE variable_name='mysql-monitor_password';

UPDATE global_variables 
SET variable_value='2000' 
WHERE variable_name='mysql-monitor_connect_interval';

UPDATE global_variables 
SET variable_value='200000' 
WHERE variable_name='mysql-monitor_ping_interval';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

23.3.3 ProxySQL使用

# 应用程序连接ProxySQL（端口6033）
mysql -u app_user -papp_password -h 127.0.0.1 -P6033

# 测试读写分离
-- 读操作（应该路由到从库）
SELECT * FROM users WHERE id = 1;

-- 写操作（应该路由到主库）
INSERT INTO users(name, age) VALUES ('张三', 25);
UPDATE users SET age = 26 WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- 查看查询统计
-- 连接管理端口
mysql -u admin -padmin -h 127.0.0.1 -P6032

-- 查看查询统计
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;

-- 查看连接统计
SELECT * FROM stats_mysql_connection_pool;

-- 查看命令统计
SELECT * FROM stats_mysql_commands_counters;

23.4 主从延迟问题 ⭐⭐⭐⭐⭐

23.4.1 什么是主从延迟？

定义：

主库执行的事务，从库还没有执行
从库的数据落后于主库

查看主从延迟：

-- 在从库上执行
SHOW SLAVE STATUS\G

-- 关注字段：
-- Seconds_Behind_Master：延迟秒数
--   0：无延迟
--   NULL：复制未运行
--   >0：有延迟

23.4.2 主从延迟的原因

1. 主库写入压力大

主库TPS：10000
从库TPS：5000
→ 从库跟不上主库

2. 从库硬件性能差

主库：SSD
从库：HDD
→ 从库I/O慢

3. 大事务

-- 主库执行大事务
UPDATE users SET status = 1;  -- 更新100万行
-- 从库需要很长时间才能执行完

4. 从库负载高

从库同时承担：
- 复制
- 查询
→ 资源竞争

5. 网络延迟

主库和从库在不同机房
→ binlog传输慢

23.4.3 主从延迟的影响

问题场景：

-- 1. 用户注册
INSERT INTO users(name, email) VALUES ('张三', 'test@example.com');
-- 主库执行成功

-- 2. 立即查询（读从库）
SELECT * FROM users WHERE email = 'test@example.com';
-- 从库还没有同步，查询不到
-- 用户看到"注册失败"

影响：

❌ 数据不一致
❌ 用户体验差
❌ 业务逻辑错误

23.4.4 解决主从延迟

方案1：强制读主库

// 写操作后，立即读主库
public void register(User user) {
    // 1. 写入主库
    masterDao.insert(user);

    // 2. 读主库（不读从库）
    User savedUser = masterDao.selectByEmail(user.getEmail());
}

方案2：延迟读取

// 写操作后，延迟一段时间再读
public void register(User user) {
    // 1. 写入主库
    masterDao.insert(user);

    // 2. 等待100ms
    Thread.sleep(100);

    // 3. 读从库
    User savedUser = slaveDao.selectByEmail(user.getEmail());
}

方案3：缓存

// 写操作后，写入缓存
public void register(User user) {
    // 1. 写入主库
    masterDao.insert(user);

    // 2. 写入缓存
    cache.put("user:" + user.getEmail(), user);

    // 3. 读缓存
    User savedUser = cache.get("user:" + user.getEmail());
}

方案4：半同步复制

-- 主库等待至少一个从库确认后才返回
-- 详见第22章：主从复制

方案5：并行复制

-- 从库使用多线程并行复制
-- MySQL 5.7+支持
[mysqld]
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4

方案6：读写都用主库

// 对于重要业务，读写都用主库
public void transfer(int fromId, int toId, int amount) {
    // 1. 读主库
    Account from = masterDao.selectById(fromId);
    Account to = masterDao.selectById(toId);

    // 2. 写主库
    masterDao.update(from);
    masterDao.update(to);
}

23.5 负载均衡策略

23.5.1 轮询（Round Robin）

public class RoundRobinLoadBalancer {
    private List<DataSource> slaves;
    private AtomicInteger index = new AtomicInteger(0);

    public DataSource getDataSource() {
        int i = index.getAndIncrement() % slaves.size();
        return slaves.get(i);
    }
}

特点：

✅ 简单
✅ 均匀分配
❌ 不考虑服务器性能

23.5.2 随机（Random）

public class RandomLoadBalancer {
    private List<DataSource> slaves;
    private Random random = new Random();

    public DataSource getDataSource() {
        int i = random.nextInt(slaves.size());
        return slaves.get(i);
    }
}

特点：

✅ 简单
✅ 分布均匀（长期）
❌ 短期可能不均匀

23.5.3 加权轮询（Weighted Round Robin）

public class WeightedRoundRobinLoadBalancer {
    private List<Server> servers;  // Server包含DataSource和weight

    public DataSource getDataSource() {
        // 根据权重选择
        // 权重高的服务器被选中的概率大
    }
}

// 配置示例
Server slave1 = new Server(dataSource1, 3);  // 权重3
Server slave2 = new Server(dataSource2, 1);  // 权重1
// slave1被选中的概率是slave2的3倍

特点：

✅ 考虑服务器性能
✅ 灵活
❌ 配置复杂

23.5.4 最少连接（Least Connections）

public class LeastConnectionsLoadBalancer {
    private List<DataSource> slaves;

    public DataSource getDataSource() {
        // 选择当前连接数最少的服务器
        DataSource selected = null;
        int minConnections = Integer.MAX_VALUE;

        for (DataSource ds : slaves) {
            int connections = getActiveConnections(ds);
            if (connections < minConnections) {
                minConnections = connections;
                selected = ds;
            }
        }

        return selected;
    }
}

特点：

✅ 动态负载均衡
✅ 适合长连接
❌ 需要监控连接数

23.6 读写分离最佳实践

23.6.1 设计原则

1. 读写分离对应用透明
   - 使用中间件
   - 或使用框架（如MyBatis）

2. 处理主从延迟
   - 重要业务读主库
   - 或使用缓存

3. 监控主从延迟
   - 延迟过大时告警
   - 延迟过大时切换到主库

4. 从库只读
   - 设置read_only=1
   - 防止误写入

5. 负载均衡
   - 多个从库分散压力
   - 根据性能设置权重

6. 故障切换
   - 从库故障时自动摘除
   - 从库恢复后自动加入

23.6.2 常见错误

-- ❌ 错误1：所有查询都读从库
-- 问题：主从延迟导致数据不一致
-- 解决：重要查询读主库

-- ❌ 错误2：从库可写
-- 问题：从库数据与主库不一致
-- 解决：设置read_only=1

-- ❌ 错误3：不监控主从延迟
-- 问题：延迟过大时仍然读从库
-- 解决：监控Seconds_Behind_Master

-- ❌ 错误4：事务内读从库
-- 问题：事务内的读写可能不一致
-- 解决：事务内都读主库

-- ❌ 错误5：不处理从库故障
-- 问题：从库故障时查询失败
-- 解决：自动摘除故障从库

23.6.3 监控指标

-- 1. 主从延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master

-- 2. 从库负载
SHOW PROCESSLIST;
-- 查看正在执行的查询数量

-- 3. 查询分布
-- 统计主库和从库的查询数量
-- 理想比例：主库20%，从库80%

-- 4. 从库状态
SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes

-- 5. 复制错误
SHOW SLAVE STATUS\G
-- Last_Error

23.7 本章总结

本章学习内容：

✅ 读写分离概述
✅ 读写分离实现方式（应用层、中间件）
✅ ProxySQL实战（安装、配置、使用）⭐⭐⭐⭐⭐
✅ 主从延迟问题（原因、影响、解决方案）⭐⭐⭐⭐⭐
✅ 负载均衡策略
✅ 读写分离最佳实践

重点掌握：

读写分离：写主库，读从库
ProxySQL配置读写分离规则
主从延迟的原因和解决方案
重要业务读主库
从库设置read_only=1
监控主从延迟

读写分离架构：

应用 → 中间件 → 主库（写）
              → 从库1（读）
              → 从库2（读）

主从延迟解决方案：

强制读主库
延迟读取
使用缓存
半同步复制
并行复制

负载均衡策略：

轮询：简单均匀
随机：简单
加权轮询：考虑性能
最少连接：动态负载

面试重点：

读写分离的原理
如何解决主从延迟
ProxySQL如何配置
负载均衡策略有哪些
读写分离的注意事项

下一章预告： 高可用方案

练习题

什么是读写分离？
读写分离有哪些实现方式？
如何使用ProxySQL实现读写分离？
什么是主从延迟？
主从延迟有哪些原因？
如何解决主从延迟问题？
负载均衡策略有哪些？
为什么从库要设置read_only=1？
如何监控主从延迟？
设计一个读写分离方案

继续学习： 第24章：高可用方案