综合真题解析
定位：将多个知识点串联成一个完整回答——展示知识的广度和深度
案例1：一条简单的 UPDATE 背后发生了什么？
题干：执行 UPDATE users SET age=30 WHERE id=1;，从连接建立到数据落盘，MySQL 内部经历哪些过程？
回答框架
阶段 1: Server 层
┌────────────────────────────────────────────┐
│ ① 连接器: 检查权限、获取连接 │
│ ② 分析器: 词法分析 → UPDATE users... │
│ 语法分析 → 确定这是 UPDATE 语句 │
│ ③ 优化器: 决定走主键索引（id=1 是主键） │
│ ④ 执行器: 调用 InnoDB 接口 │
└────────────────────────────────────────────┘
↓
阶段 2: InnoDB 层（执行过程）
┌────────────────────────────────────────────┐
│ ⑤ 当前读: 对 id=1 加 X 锁（行锁） │
│ → 如果是 RR 级别，加 Next-Key Lock │
│ ⑥ 读到旧的 age=20 │
│ → 写 Undo Log: age=20（用于回滚+MVCC） │
│ ⑦ 在 Buffer Pool 中修改 age=20 → 30 │
│ → 该页变为脏页 │
│ ⑧ 写 Redo Log Buffer │
│ → "page#42, offset#128, 20→30" │
│ → 状态: prepare │
└────────────────────────────────────────────┘
↓
阶段 3: 提交阶段
┌────────────────────────────────────────────┐
│ ⑨ 写 Binlog │
│ → ROW 格式: "UPDATE id=1, age 20→30" │
│ → 状态: prepared │
│ ⑩ Redo Log Buffer → 刷盘（fsync） │
│ → 状态: commit ✅ │
│ ⑪ Binlog → 刷盘（sync_binlog=1 时） │
│ ⑫ 事务提交完成 ✅ │
│ ⑬ 释放锁 │
└────────────────────────────────────────────┘
↓
阶段 4: 后台异步
┌────────────────────────────────────────────┐
│ ⑭ Page Cleaner 线程: 脏页刷入磁盘 │
│ ⑮ Purge 线程: 清理不再需要的 Undo Log │
└────────────────────────────────────────────┘涉及的原理笔记
- 架构：**MySQL架构总览**
- InnoDB：**InnoDB架构**
- 锁：**行锁与间隙锁**
- MVCC：**MVCC实现原理**
- Redo Log：**Redo Log**
- Binlog：**Binlog**
- 2PC：**两阶段提交与崩溃恢复**
案例2：电商系统的数据库优化方案
题干：一个电商系统，订单表 5000 万行，日均新增 10 万行。高峰时段经常出现接口超时，怎么优化？
回答框架
1. 先诊断：确认瓶颈在哪
sql
-- 看当前正在运行的查询
SHOW PROCESSLIST;
-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';
-- 分析慢查询日志找出最慢的 SQL2. 从轻到重的优化策略
| 优先级 | 方案 | 原理 | 预估效果 |
|---|---|---|---|
| ✅ 1 | 索引优化（覆盖索引、包含排序列） | 减少回表、减少 filesort | 提升 10-100x |
| ✅ 2 | 分页优化（游标分页代替 OFFSET） | 减少扫描行数 | 提升 100x 以上 |
| ✅ 3 | 冷热数据分离 | 近期订单在热表，历史归档 | 减少热表数据量 |
| ✅ 4 | 读写分离（主库写，从库读） | 分散读压力 | 线性扩展读能力 |
| ⚠️ 5 | 缓存（Redis 缓存热点数据） | 减少数据库查询 | 减轻 80%+ 的读压力 |
| ❌ 6 | 分库分表（最后的手段） | 水平拆分 | 扩展能力强但维护成本高 |
3. 针对订单表的具体建议
sql
-- 现有索引优化
-- 订单表经常按 user_id 查 + 按 create_time 排序
-- 优化前索引: idx_user_id, idx_create_time（两个独立索引）
-- 优化后索引: idx_user_id_create_time（联合索引）
-- 覆盖索引示例
-- 如果只需查状态和金额
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);
-- 这样 WHERE user_id=xxx AND status='PAID' 不需要回表4. 架构层面的建议
主库（写）+ 从库 1（读）+ 从库 2（报表）
│
┌──────┴──────┐
│ ProxySQL │ ← 读写分离 + 连接池
└──────┬──────┘
│
应用层案例3：系统变慢了，如何定位问题？
题干：一个运行良好的 MySQL 最近突然变慢，作为 DBA 你要怎么排查？
排查顺序
Step 1: 看全局
┌──────────────────────────────┐
│ SHOW PROCESSLIST │ ← 有没有长时间运行的查询？
│ top / htop │ ← CPU/内存/IO 是否有瓶颈？
│ dmesg | tail -20 │ ← 内核有没有报错（OOM、IO error）？
└──────────────────────────────┘
Step 2: 看 MySQL 内部指标
┌──────────────────────────────┐
│ Buffer Pool 命中率 │ ← <95% → 内存不足
│ 脏页比例 / Checkpoint 频率 │ ← 写入压力大
│ InnoDB 行锁等待 │ ← 锁竞争
│ 复制延迟 (Seconds_Behind) │ ← 从库问题
└──────────────────────────────┘
Step 3: 定位具体慢查询
┌──────────────────────────────┐
│ 慢查询日志 → 找 top N 慢 SQL │
│ EXPLAIN 分析执行计划 │
│ SHOW PROFILE 看各阶段耗时 │
└──────────────────────────────┘
Step 4: 判断原因类型
┌──────────────────────────────┐
│ 突然变慢 → 一般不是 SQL 问题 │
│ → 检查数据量突增、索引失效、 │
│ 系统资源被占用 │
│ │
│ 一直慢 → SQL 需要优化 │
│ → 索引、表结构、查询改写 │
│ │
│ 某时段慢 → 业务行为导致 │
│ → 定时任务、批量操作、报表 │
└──────────────────────────────┘📚 相关链接
- **面试高频100题**
- ← 返回 **设计文档**