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 并不是完全相同的。
