1. MySQL基本架构
1.1. MySQL架构
MySQL 可以分为 Server 层和存储引擎层两部分。
1. Server 层（上层）
作用：处理所有的通用功能，与存储引擎无关。
包含模块：
- 连接器（Connection）：负责建立连接、用户认证和权限验证。
- 查询缓存（8.0已废弃）：历史上用来缓存查询结果，现在建议通过应用层缓存替代。
- 分析器（Parser）：进行语法和词法分析，把 SQL 转换成解析树（Parse Tree）。
- 优化器（Optimizer）：根据多种执行策略选择最优的执行计划。
- 执行器（Executor）：按照优化器生成的执行计划逐步执行 SQL。
涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
2. 存储引擎层（下层）
作用：负责数据的存储和提取。
常用引擎：
- InnoDB（默认）：支持事务、行级锁和崩溃恢复。
- MyISAM：支持表级锁，不支持事务，已较少使用。
- Memory
不同的存储引擎共用一个Server 层，也就是从连接器到执行器的部分。
对比项 |
InnoDB |
MyISAM |
事务支持 |
✅ 支持事务（ACID） |
❌ 不支持事务 |
锁机制 |
行级锁（高并发性能好） |
表级锁（并发性能差） |
崩溃恢复能力 |
✅ 支持崩溃恢复 |
❌ 恢复能力差，易损坏 |
外键支持 |
✅ 支持外键 |
❌ 不支持外键 |
全文索引 |
5.6+ 开始支持 |
✅ 支持 |
数据存储方式 |
聚集索引（数据和索引在一起） |
非聚集索引（数据和索引分开） |
空间使用 |
相对较大 |
相对较小 |
读取性能 |
读写均衡性能较好 |
读性能较好，写性能差 |
表损坏概率 |
低 |
高 |
默认引擎 |
是（MySQL 5.5+ 默认） |
否 |
支持 MVCC |
✅ 支持多版本并发控制（MVCC） |
❌ 不支持 |
适合场景 |
高并发事务处理，数据一致性要求高 |
以读为主，事务和一致性要求不高 |
MySQL 常见存储引擎对比：
存储引擎 |
是否支持事务 |
是否支持外键 |
是否支持全文索引 |
索引类型 |
特点和用途 |
InnoDB |
✅ 支持 |
✅ 支持 |
✅（5.6+） |
B+树、聚簇索引 |
默认引擎，支持事务、崩溃恢复、高并发。适用于大多数业务。 |
MyISAM |
❌ 不支持 |
❌ 不支持 |
✅ |
B+树 |
存储快，读写分离场景优，但不支持事务，容易数据损坏。 |
Memory |
❌ 不支持 |
❌ 不支持 |
❌ |
哈希表为主 |
数据存储在内存中，读写极快，重启即丢失。适用于临时计算。 |
CSV |
❌ 不支持 |
❌ 不支持 |
❌ |
无索引 |
每张表一个 CSV 文件，适合导入导出。几乎不用。 |
Archive |
❌ 不支持 |
❌ 不支持 |
❌ |
无索引 |
仅支持插入和查询，压缩率高，适合归档历史数据。 |
Federated |
❌ 不支持 |
❌ 不支持 |
❌ |
无索引 |
表连接远程 MySQL 实例，不存本地数据。 |
NDB (Cluster) |
✅ 支持 |
✅ 支持 |
❌ |
B+树 |
用于 MySQL Cluster 分布式系统，适合高可用集群部署。 |
1.2. SQL查询流程
执行流程详解：
1. 连接器（Connection）
- 检查用户账号和密码，校验权限。
- 通过权限后建立连接进入后续流程。
解决MySQL中长连接内存占用太大的问题：
- 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
- 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
2. 分析器（Parser）
- 进行语法分析，识别 SQL 中的关键字、字段名、表名等。
- 输出解析树（Parse Tree）。
3. 优化器（Optimizer）
- 判断是否存在索引，决定是否走全表扫描或使用索引。
- 确定表连接顺序、选择最优执行路径。
4. 执行器（Executor）
- 调用存储引擎接口访问表数据。
- 根据优化器的决策执行操作并返回结果。
在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。
在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。