第 5 章
MySQL 架构全景
MySQL 执行架构与完整链路解析
理解MySQL的内部架构对优化和故障排除至关重要。本指南涵盖从网络连接到结果交付的完整查询执行流程，包括服务器层、查询优化器和存储引擎接口。
1. MySQL架构概述
1.1 分层架构
MySQL使用三层架构：
┌──────────────────────────────────────────────────────────────┐
│ 客户端应用程序 │
│ （mysql CLI、JDBC、PDO、Node.js等） │
└──────────────────────────────────────────────────────────────┘
↓ SQL查询
┌──────────────────────────────────────────────────────────────┐
│ 服务器层（mysqld） │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ 连接管理与身份验证 │ │
│ │ - TCP/Socket连接处理 │ │
│ │ - 用户身份验证和权限检查 │ │
│ │ - 连接池和会话管理 │ │
│ └─────────────────────────────────────────────────────────┘ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ SQL解析器 │ │
│ │ - 词法分析（标记化） │ │
│ │ - 语法分析（解析树构造） │ │
│ │ - 查询验证 │ │
│ └─────────────────────────────────────────────────────────┘ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ 查询优化器 │ │
│ │ - 预处理（schema验证、简化） │ │
│ │ - 成本分析（估算执行计划） │ │
│ │ - 计划选择（选择最优执行策略） │ │
│ └─────────────────────────────────────────────────────────┘ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ 查询执行器 │ │
│ │ - 计划执行（遵循优化策略） │ │
│ │ - 缓存管理和更新 │ │
│ │ - 事务管理 │ │
│ └─────────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────┘
↓ 查询API
┌──────────────────────────────────────────────────────────────┐
│ 存储引擎层 │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ InnoDB │ │ MyISAM │ │ Memory │ │
│ │ │ │ │ │ │ │
│ │ 事务支持 │ │ 读取快速 │ │ 极速 │ │
│ │ ACID │ │ 适合日志 │ │ 临时表 │ │
│ │ │ │ │ │ │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
└──────────────────────────────────────────────────────────────┘
↓
┌──────────────┐
│ 磁盘I/O │
│ 缓冲池 │
│ │
└──────────────┘
1.2 查询执行完整流程
SELECT * FROM users WHERE id = 1;
┌─────────────────────────────────────────┐
│ 1. 连接建立与身份验证 │
│ • TCP连接 │
│ • 用户验证 │
│ • 权限检查 │
└─────────────────────────────────────────┘
↓
┌─────────────────────────────────────────┐
│ 2. SQL解析 │
│ • 词法分析：SELECT * FROM users... │
│ • 语法验证：确保SQL有效 │
│ • 生成解析树 │
└─────────────────────────────────────────┘
↓
┌─────────────────────────────────────────┐
│ 3. 查询优化 │
│ • 统计分析：users表有多少行？ │
│ • 索引评估：有id索引吗？ │
│ • 成本计算：全表扫描 vs 索引查询 │
│ • 选择最优计划：使用id索引 │
└─────────────────────────────────────────┘
↓
┌─────────────────────────────────────────┐
│ 4. 查询执行 │
│ • 使用id索引查找 │
│ • 应用WHERE条件（id=1） │
│ • 检索匹配的行 │
│ • 应用SELECT投影（*） │
└─────────────────────────────────────────┘
↓
┌─────────────────────────────────────────┐
│ 5. 结果返回 │
│ • 构建结果集 │
│ • 序列化为协议格式 │
│ • 发送给客户端 │
│ • 释放资源 │
└─────────────────────────────────────────┘
2. 连接管理
2.1 连接生命周期
1. TCP连接建立
↓
2. TCP/UNIX Socket握手
└─ 客户端：发送协议版本
└─ 服务器：发送身份验证挑战
↓
3. 身份验证
└─ 客户端：发送用户名、密码、数据库
└─ 服务器：针对mysql.user表验证
└─ 结果：身份验证成功/失败
↓
4. 初始化
└─ 加载用户变量（字符集、sql_mode等）
└─ 检查默认数据库的权限
└─ 启动新会话
↓
5. 命令阶段
└─ 接收和处理查询
↓
6. 终止
└─ QUIT命令或连接超时
└─ 清理会话资源
**监控连接状态：**
-- 显示活跃连接
SHOW PROCESSLIST;
-- 扩展进程信息
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;
-- 连接统计
SHOW STATUS LIKE 'Threads%';
-- 连接池配置
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'interactive_timeout';
SHOW VARIABLES LIKE 'wait_timeout';
-- 杀死空闲或长时间运行的连接
KILL CONNECTION ;
KILL QUERY ; -- 杀死查询，不是连接
3. SQL解析器
3.1 解析过程
输入：SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC
↓ 词法分析（标记化）
└─ 分割成标记：[SELECT, *, FROM, users, WHERE, age, >, 18, ...]
↓ 语法分析（解析）
└─ 验证语法规则
└─ 构建抽象语法树（AST）
├─ SELECT子句（列列表、DISTINCT等）
├─ FROM子句（表、JOIN）
├─ WHERE子句（谓词、条件）
├─ GROUP BY子句（分组）
├─ HAVING子句（聚合过滤）
├─ ORDER BY子句（排序）
└─ LIMIT子句（分页）
↓ 语义验证
└─ 验证表/列是否存在
└─ 检查用户权限
└─ 验证函数调用
└─ 检查类型兼容性
↓ 输出：准备好优化的解析树
常见解析器错误：
- 语法错误：SELECT * FORM users（打字错误）
- 未知列：SELECT unknown_col FROM users
- 未知表：SELECT * FROM nonexistent
- 权限被拒绝：错误1143（用户没有SELECT权限）
4. 查询优化器
4.1 优化过程
解析树
↓
预处理
├─ Schema验证
├─ 常数折叠（1+1变成2）
├─ 删除未使用的子句
├─ 简化表达式
└─ 解析表/列引用
↓
逻辑优化
├─ 谓词下推（将WHERE下推到JOIN中）
├─ 子查询转换
│ ├─ 如果可能，将IN子查询转换为JOIN
│ ├─ 将EXISTS转换为联接
│ └─ 展平派生表
├─ 删除冗余条件
├─ 常数表达式消除
└─ OR条件简化
↓
成本分析
├─ 表大小估计
├─ 列选择性分析
├─ 索引可用性检查
├─ 联接顺序评估（对于n个表，~n！个组合）
└─ 存储引擎成本计算
↓
计划选择
├─ 选择最佳联接顺序
├─ 为每个表选择最佳索引
├─ 选择执行策略
└─ 应用优化提示
↓
执行计划
├─ 有序操作
├─ 每个表的访问方法
└─ 成本估计
**查看执行计划：**
-- 基本EXPLAIN
EXPLAIN SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = 1
GROUP BY u.id, u.name;
-- JSON格式（更详细）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1\G
-- 分析实际执行（MySQL 5.7+）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
-- 优化器提示（MySQL 5.7.7+）
SELECT /*+ BKA(t1) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM users;
5. 存储引擎接口
5.1 存储引擎对比
InnoDB（默认，MySQL 5.5+）
├─ 事务：是（ACID兼容）
├─ 锁定：行级
├─ 崩溃恢复：是（通过重做日志）
├─ 内存使用：较高（缓冲池）
├─ I/O模式：友好随机I/O
├─ 最适合：生产、事务型应用
└─ 权衡：对读取比MyISAM略慢
MyISAM（旧版，MySQL 5.5之前）
├─ 事务：否
├─ 锁定：表级
├─ 崩溃恢复：有限
├─ 内存使用：较低
├─ I/O模式：友好顺序扫描
├─ 最适合：读密集、日志文件、数据仓库
└─ 权衡：不适合并发写入
Memory/HEAP
├─ 事务：否
├─ 锁定：表级
├─ 持久性：否（关闭时丢失）
├─ 速度：非常快
├─ 最适合：临时表、缓存
└─ 权衡：限于可用RAM
-- 检查表的存储引擎
SHOW TABLE STATUS FROM mydb;
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb';
-- 转换表引擎
ALTER TABLE users ENGINE = InnoDB;
ALTER TABLE logs ENGINE = MyISAM;
6. 性能瓶颈识别
6.1 常见瓶颈
1. 解析缓慢
症状：即使简单查询也很慢
原因：
├─ 非常长/复杂的查询（>1000行）
├─ 子查询中的深层嵌套
└─ IN子句中的许多字面量
解决方案：
├─ 简化查询结构
├─ 使用JOIN代替子查询
├─ 使用预准备语句（解析一次，执行多次）
└─ 避免有太多值的动态SQL
2. 执行计划错误
症状：使用全表扫描而不是索引
原因：
├─ 索引缺失
├─ 过时的表统计信息
├─ 优化器估算成本不正确
├─ 类型不匹配（字符串vs整数比较）
└─ 非sargable WHERE条件
解决方案：
├─ ANALYZE TABLE更新统计信息
├─ 创建适当的索引
├─ 使用EXPLAIN ANALYZE验证计划
├─ 重写查询以便优化器理解
└─ 如果优化器错误，使用优化器提示
3. 锁竞争
症状：查询等待锁，超时
解决方案：
├─ 缩短事务持续时间
├─ 减少锁等待超时以快速失败
├─ 将更新批量处理
├─ 如果合适，使用较低的隔离级别
└─ 将热数据分离到不同的表
4. 内存压力 / 磁盘I/O饱和 / CPU饱和
主要解决方案：
├─ 增加缓冲池大小（RAM的50-80%）
├─ 使用SSD而不是HDD
├─ 优化索引（减少查询）
└─ 水平扩展（复制、分片）
7. 查询优化工作流
1. 识别慢查询
└─ 检查slow_query_log
└─ 使用SHOW PROCESSLIST
└─ 监控应用程序日志
2. 收集信息
└─ EXPLAIN查询
└─ EXPLAIN ANALYZE（MySQL 5.7+）
└─ 检查表大小
└─ 查看索引
└─ 检查服务器负载
3. 分析执行计划
├─ 全表扫描吗？（type=ALL）
├─ 索引错误？
├─ 联接顺序错误？
├─ 在磁盘上排序吗？（Extra字段）
├─ 检查的行数 >> 返回的行数？
└─ 成本估计是否逼真？
4. 实施修复
├─ 创建索引（最常见）
├─ ANALYZE TABLE（更新统计信息）
├─ 重写查询（联接顺序、子查询消除）
├─ 添加优化器提示
├─ 分区表（如果非常大）
└─ 反规范化（作为最后手段）
5. 验证与记录
├─ 再次运行EXPLAIN
├─ 检查查询时间改进
├─ 在生产中监控
└─ 记录索引和优化内容
8. MySQL 5.7 vs 8.0 架构对比
MySQL 5.7 → MySQL 8.0 Changes:
1. 数据字典
5.7: 文件式（.frm, .ibd文件）
8.0: InnoDB中的事务数据字典
├─ 原子DDL操作
└─ 更快的元数据查询
2. 查询优化器
5.7: 传统连接顺序优化
8.0: 超图优化器（更多连接组合）
3. 执行引擎
5.7: 仅嵌套循环连接
8.0: 哈希连接支持（MySQL 8.0.18+）
4. 索引
5.7: 普通索引
8.0: 函数式索引（索引中的表达式）
5. 统计信息
5.7: 单表统计
8.0: 直方图统计（更好处理倾斜数据）
8.0 需要利用的功能：
- 函数式索引用于复杂谓词
- 窗口函数用于排名/分析
- 公共表表达式（CTE/WITH子句）
- JSON增强
- 超图优化器提示
结论
理解MySQL的架构可以更好地优化：
- SQL解析通常很快；专注于执行计划优化
- 查询优化器基于成本；确保统计信息是最新的
- 使用EXPLAIN理解选择的执行计划
- InnoDB是默认的，也是大多数用例的最佳选择
- 缓冲池至关重要 —— 为您的硬件调整它
- 监控锁竞争和I/O模式
- 使用存储引擎功能（事务、行级锁定）
- 仅在优化器错误时应用优化器提示
- 在优化前后进行性能分析和基准测试