学习日期：2025年10月11日
课时：课时08 - MySQL架构设计与运维
学习方式：苏格拉底式对话学习
学习时长：2小时10分钟

---

📌 学习目标

本课时核心目标：
1. MySQL性能优化方法论（6层模型）
2. 高可用架构设计（主从切换）
3. 主从同步延迟处理（缓存方案）
4. 分库分表实施流程
5. 数据库不停服迁移
6. MySQL监控和故障处理

面试重点：
• 如何系统化地进行MySQL性能优化？
• 如何实现MySQL高可用？
• 如何处理主从延迟问题？
• 分库分表的实施流程是什么？

---

💡 知识点1：MySQL性能优化方法论

我的学习切入点：如何系统化地进行性能优化？

学习这个知识点时，我首先思考的问题是：如果MySQL系统很慢，应该从哪些方面排查问题？

---

我的第一次思考

我首先想到了4个方向：

1. 硬件层面
   • 内存较小可能导致缓冲池不够
   • 磁盘IO性能影响查询速度

2. 配置层面
   • 从库向主库同步时的认证可能耗时
   • 各种参数配置可能不合理

3. SQL层面（重点！）⭐⭐⭐
   • SQL语句问题，这里出问题概率最大
   • 可能写出了type为ALL或index的语句
   • 需要优化JOIN、添加索引、使用覆盖索引

4. 架构层面
   • 如果是单一主库，压力过大肯定会慢
   • 需要读写分离和负载均衡

我遗漏的2个重要方向：

• 数据库层面（表设计问题）
• 业务逻辑层面（应用代码问题，如N+1查询）

---

完整的MySQL性能优化6层模型

学习后，我理解了完整的性能优化方法论：

从外到内，从上到下的6个层面：

第1层：业务逻辑层 ⭐⭐⭐⭐⭐（最容易出问题）
├─ 加缓存（Redis）
├─ 优化查询逻辑（避免N+1查询）
└─ 异步处理（消息队列）

第2层：架构层 ⭐⭐⭐⭐⭐
├─ 主从复制 + 读写分离
├─ 分库分表
└─ 负载均衡

第3层：SQL层 ⭐⭐⭐⭐⭐（最常见）
├─ 慢SQL优化
├─ 索引优化
└─ 查询改写

第4层：表设计层 ⭐⭐⭐⭐
├─ 数据类型优化
├─ 字段拆分
└─ 垂直分表

第5层：配置层 ⭐⭐⭐
├─ 缓冲池大小
├─ 连接数
└─ 日志配置

第6层：硬件层 ⭐⭐
├─ CPU
├─ 内存
└─ 磁盘（SSD）

核心原则：
• 从外到内，从上到下
• 先优化"投入产出比"最高的层面
• 80%的性能问题出在前3层

性能优化6层模型可视化：

+--------------------------------------------------------------------+
| 第1层：业务逻辑层 ★★★★★                                            |
| [加缓存Redis、避免N+1查询]                                         |
|    ↓                                                               |
| 第2层：架构层 ★★★★★                                                |
| [主从复制、读写分离、分库分表]                                     |
|    ↓                                                               |
| 第3层：SQL层 ★★★★★                                                 |
| [慢SQL优化、索引优化]                                              |
|    ↓                                                               |
| 第4层：表设计层 ★★★★                                               |
| [数据类型优化、字段拆分]                                           |
|    ↓                                                               |
| 第5层：配置层 ★★★                                                  |
| [缓冲池、连接数、日志配置]                                         |
|    ↓                                                               |
| 第6层：硬件层 ★★                                                   |
| [CPU、内存、磁盘SSD]                                               |
+--------------------------------------------------------------------+

---

我的重要认知：什么是N+1查询？

学习过程中，我遇到了一个新概念：N+1查询

举例说明（用cpp-chat项目）：

❌ 错误写法（N+1查询）：

// 第1次查询：查所有聊天室（1次查询）
vector<ChatRoom> rooms = db.query("SELECT * FROM chat_rooms");

// 第2-N次查询：循环查每个聊天室的最新消息（N次查询）
for (auto& room : rooms) {
    // 每次循环都查数据库！
    Message lastMsg = db.query(
        "SELECT * FROM messages WHERE room_id = ? ORDER BY time DESC LIMIT 1",
        room.id
    );
    room.lastMessage = lastMsg;
}

// 如果有100个聊天室，就要查询 1 + 100 = 101 次！

✅ 正确写法（一次查询）：

// 用JOIN一次查出所有数据
auto result = db.query(R"(
    SELECT 
        cr.id, cr.name,
        m.content as last_message
    FROM chat_rooms cr
    LEFT JOIN (
        SELECT room_id, content,
               ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY created_at DESC) as rn
        FROM messages
    ) m ON cr.id = m.room_id AND m.rn = 1
)");

// 只查1次数据库！

关键认知：

• N+1查询是业务逻辑层的问题
• 需要在应用代码层面优化
• 不能只盯着数据库，应用代码也可能是瓶颈

---

实际优化顺序：我的理解和修正

继续深入学习时，我思考了一个问题：如果要优化性能，应该按什么顺序来？

我最初的排序：D → F → B → E → C → A

D. 先加Redis缓存
F. 先做分库分表
B. 先检查SQL语句
E. 先优化表设计
C. 先调整MySQL配置
A. 先升级硬件

我的问题：

• 把分库分表（F）放在第2位太早了！
• 分库分表是"终极方案"，复杂度极高
• 适合数据量达到千万级、亿级时
• 过早分库分表是"过度设计"

正确的优化顺序：B → D → E → C → F → A

第1步：B - 检查慢SQL ⭐⭐⭐⭐⭐（必做，0成本）
理由：
• 80%的性能问题都是慢SQL导致的
• 用EXPLAIN分析，看是否走索引
• 成本：0元，效果：立竿见影

第2步：D - 加Redis缓存 ⭐⭐⭐⭐⭐（高优先级）
理由：
• 热点数据缓存，QPS提升10-100倍
• 减少数据库压力
• 成本低（Redis很便宜）

第3步：E - 优化表设计 ⭐⭐⭐⭐（如果表设计有问题）
理由：
• 检查数据类型是否合理
• 检查索引是否合理
• 调整字段顺序

第4步：C - 调整MySQL配置 ⭐⭐⭐（进阶优化）
理由：
• 默认配置通常不是最优的
• 根据服务器硬件调整

第5步：F - 分库分表 ⭐⭐（数据量达到瓶颈时）
理由：
• 只有数据量真正到达瓶颈时才考虑
• 复杂度高，需要慎重

第6步：A - 升级硬件 ⭐（最后的选择）
理由：
• 成本最高
• 软件优化做完再考虑

我的收获：

• 优化要"由外到内"，先软件再硬件
• 先做投入产出比高的优化
• 分库分表不能太早做

---

💡 知识点2：高可用架构设计

我的思考：主库宕机会怎样？

学习高可用架构时，我思考了一个实际问题：如果昨天搭建的主从复制架构中，主库（Master）宕机了，会发生什么？

我的分析：

1. 用户能发送消息吗？（写操作）
   • 不能！❌
   • 主库负责所有写操作
   • 主库宕机 = 所有写操作失败

2. 用户能查看历史消息吗？（读操作）
   • 可以部分查看 ✓
   • 如果消息缓存在Redis里 → 可以查看
   • 如果有从库 → 可以从从库读取
   
3. 系统哪些功能会受影响？
   • 发送消息 ❌
   • 更改名字 ❌
   • 创建新用户 ❌
   • 只要是写操作都不行
   
4. 数据会丢失吗？
   • 有binlog文件和从库
   • 应该不会丢失
   • binlog在从库IO线程读取后，主库可能会删除（看配置）
   • 但从库已经有relay log了

我的分析基本正确，完全理解了主从复制的架构！

---

解决方案：两种高可用方案对比

思考完问题后，我学习了两种解决单点故障的方案：

方案1：从库升级为主库（主从切换）⭐⭐⭐⭐⭐

我最先想到的方案：

当Master宕机时，提升一个Slave为新的Master

原来：
  Master (写)
    ├─ Slave1 (读)
    └─ Slave2 (读)

切换后：
  Slave1 → 新Master (写)
    └─ Slave2 → 仍是Slave (读)

优点：
✅ 不需要额外的备用主库（成本低）
✅ 充分利用现有从库资源
✅ 业界主流方案

缺点：
⚠️ 需要切换时间（几秒到几分钟）
⚠️ 切换过程中写操作不可用

方案2：双主热备（备用主库）⭐⭐⭐

同时运行2个Master，一个主用，一个备用

架构：
  Master1 (主用，写) ←→ Master2 (备用，待命)
    ├─ Slave1 (读)       ├─ Slave3 (读)
    └─ Slave2 (读)       └─ Slave4 (读)

优点：
✅ 切换极快（毫秒级）
✅ 备用主库随时可用

缺点：
⚠️ 成本高（需要2倍的主库资源）⭐⭐⭐
⚠️ 双主同步复杂（可能冲突）
⚠️ 配置和维护复杂

我的认知：

• 我最开始觉得"备用主库很好"
• 但深入学习后发现：成本高！
• 主从切换方案成本更低，是业界主流

---

💡 知识点3：主从同步延迟处理

我的思考：主从延迟会造成什么问题？

学习主从延迟时，我想到了一个实际场景：

用户在cpp-chat发送了一条消息"Hello"

T1: 用户A发送"Hello" → 写入Master
T2: Master同步到Slave（延迟5秒）
T3: 用户A刷新页面，查询历史消息 → 查询Slave
T4: Slave还没同步到"Hello"
T5: 用户A看不到自己刚发的消息！😱

**问题：**用户体验很差！刚发的消息看不到！

---

我的解决方案：缓存+三层降级

我最初的想法：

"双写同时写入缓存，读缓存，缓存出问题降级读主库"

我的问题：

• 降级读主库是不对的！
• 主库压力大（要处理所有写操作）
• 如果大量读请求降级到主库，主库可能扛不住

修正后的方案：

// 读操作：三层降级
vector<Message> getMessage(int room_id) {
    // 第1层：优先读Redis缓存 ⭐⭐⭐⭐⭐
    try {
        auto messages = redis.get("room:" + to_string(room_id) + ":messages");
        if (!messages.empty()) {
            return messages;  // 缓存命中（最快，0.001秒）
        }
    } catch (RedisException& e) {
        log("Redis异常，降级到从库");
    }
    
    // 第2层：缓存未命中，读从库集群 ⭐⭐⭐⭐⭐
    try {
        auto messages = slave_pool.query(...);  // 从库连接池
        
        // 写入缓存（下次命中）
        redis.setex("room:" + to_string(room_id) + ":messages", 60, messages);
        
        return messages;
    } catch (DBException& e) {
        log("从库异常，降级到主库");
    }
    
    // 第3层：从库也挂了，降级读主库（最后手段）⭐⭐
    try {
        auto messages = master.query(...);
        return messages;
    } catch (DBException& e) {
        return {};  // 系统完全不可用
    }
}

// 写操作：双写缓存
bool sendMessage(int room_id, int user_id, string content) {
    // 第1步：写主库（必须成功）
    master.execute(...);
    
    // 第2步：写Redis缓存（失败不影响）
    try {
        redis.lpush("room:" + to_string(room_id) + ":messages", message_json);
    } catch (...) {
        // 缓存写失败不影响
    }
    
    return true;
}

我的收获：

• 读操作降级：Redis → Slave → Master（保护主库）
• 写操作永远：Master（无降级）
• 缓存是解决延迟的最佳方案

三层降级策略可视化：

+-----------------------+
| 用户请求读取消息        |
+-----------------------+
           |
           v
+-----------------------+
|  第1层：Redis缓存      |
|  ╔════════════════╗   |
|  ║ 命中:0.001秒   ║   |
|  ║ ★★★★★        ║   |
|  ╚════════════════╝   |
+-----------------------+
           | 未命中/异常
           v
+-----------------------+
|  第2层：从库集群       |
|  ╔════════════════╗   |
|  ║ 成功:0.01秒    ║   |
|  ║ ★★★★          ║   |
|  ╚════════════════╝   |
+-----------------------+
           | 异常
           v
+-----------------------+
|  第3层：主库          |
|  ╔════════════════╗   |
|  ║ 成功:0.02秒    ║   |
|  ║ ★★             ║   |
|  ╚════════════════╝   |
+-----------------------+
           | 失败
           v
+-----------------------+
|  返回空数据            |
|  ╔════════════════╗   |
|  ║ 系统完全不可用     ║ |
|  ╚════════════════╝   |
+-----------------------+

关键点：

• 优先查Redis（最快）
• Redis失败降级到Slave（保护主库）
• Slave失败才降级到Master（最后手段）
• 绝不能让大量读请求直接打到主库

---

💡 知识点4-6：快速学习的其他知识点

后面三个知识点我快速学习了概念（了解即可）：

知识点4：分库分表实施流程

5步流程：

第1步：评估是否真的需要
• 单表数据量是否超过500万-1000万？
• 单库QPS是否超过1万？

第2步：选择分片策略
• 垂直分库：按业务模块拆分
• 水平分表：按数据拆分
• 分片键选择：user_id、order_id

第3步：制定迁移方案
• 双写方案：新旧库同时写
• 数据迁移：用工具分批迁移

第4步：改造应用代码
• 修改SQL路由逻辑
• 使用分库分表中间件

第5步：测试和回滚方案
• 灰度发布
• 准备回滚方案

知识点5：数据库不停服迁移

双写方案：

旧库 → 双写（旧库+新库）→ 新库

流程：
1. 应用同时写旧库和新库
2. 用工具迁移历史数据
3. 逐步把读流量切到新库（灰度）
4. 停止写旧库
5. 下线旧库

知识点6：MySQL监控和故障处理

监控指标：

• 性能指标：QPS/TPS、慢查询数量
• 复制指标：Seconds_Behind_Master
• 资源指标：CPU、内存、磁盘IO
• 错误日志：error log、slow query log
• 可用性：主从是否可连接

故障处理流程：

1. 告警（监控系统发现异常）
2. 定位（查日志、看监控）
3. 应急处理（主从切换、限流、降级）
4. 根因分析（找出真正原因）
5. 永久修复（修改代码、优化配置）
6. 复盘总结（写文档、分享经验）

---

📊 我的学习总结

今天的核心收获

1. MySQL性能优化方法论 ⭐⭐⭐⭐⭐

• 6层优化模型：从外到内，从上到下
• 优化顺序：B→D→E→C→F→A
• 关键认知：80%问题出在SQL层，先软件再硬件

2. 高可用架构设计 ⭐⭐⭐⭐⭐

• 主从切换方案是业界主流（成本低）
• 双主热备成本高（需要2倍资源）
• 单点故障会导致所有写操作失败

3. 主从延迟处理 ⭐⭐⭐⭐⭐

• 用缓存解决延迟问题
• 读操作降级：Redis → Slave → Master
• 写操作：只写Master，同时写缓存

4. 分库分表和不停服迁移 ⭐⭐⭐

• 分库分表不能太早做
• 数据量达到千万级再考虑
• 不停服迁移用双写方案

---

我的疑惑和待解决的问题

疑惑1：一些工具和命令

• mysqldumpslow命令的具体用法
• 窗口函数ROW_NUMBER()的语法
• 这些现在不需要背，用时查即可

疑惑2：工具掌握程度

• MHA、Orchestrator、ProxySQL等工具
• 现在只需要知道概念和作用
• 不需要会配置和操作
• 工作后再深入学习

---

面试准备要点

必须会答的问题：

1. MySQL性能优化方法有哪些？

   • 答：6层模型，从业务层到硬件层
   • 重点：SQL优化、加缓存、读写分离

2. 如何实现MySQL高可用？

   • 答：主从复制+主从切换
   • 可以用MHA等工具自动切换

3. 如何处理主从延迟？

   • 答：用缓存解决，Redis缓存最新数据
   • 读操作降级：Redis → Slave → Master

4. 分库分表的实施流程？

   • 答：5步流程，重点是评估、选择策略、迁移

---

学习完成时间：2025年10月11日 20:30
学习效果：优秀
下一步：手写核心笔记（5-10分钟）