MySQL逻辑架构

原创已于 2025-08-13 23:23:42 修改 · 1.1k 阅读

29 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

数据库

于 2025-08-13 23:14:28 首次发布

逻辑架构

一、MySQL 逻辑架构（C/S 架构核心分层）

连接层

服务层

引擎层

Connectors（连接层）
- 负责不同语言与 MySQL 的交互，基于 TCP 协议实现自定义应用层协议，支持 Native C API、JDBC、ODBC、PHP 等 SDK。
- 核心功能：建立 TCP 连接、身份认证（用户名密码校验）、权限获取（与连接关联，后续权限判断基于此）、线程池管理（复用线程减少创建 / 销毁开销）。
服务层
- SQL Interface：接收 SQL 命令（DML、DDL、存储过程等），返回执行结果。
- Parser（解析器）：对 SQL 进行词法分析（识别关键字、表名、列名）和语法分析（生成语法树，校验 SQL 合法性），若语法错误返回ERROR 1064。
- Optimizer（查询优化器）：生成最优执行计划，决定表连接顺序、是否使用索引等，基于 “选取 - 投影 - 连接” 策略优化（如先过滤再投影）。
引擎层（存储引擎

小结

二、SQL 执行流程

下图针对MySQL5.7

MySQL 5.7 及之前（含查询缓存）
- 流程：客户端发送SQL → 查询缓存（命中则返回结果）→ 解析器（生成语法树）→ 优化器（生成执行计划）→ 执行器（调用存储引擎API执行，权限校验）→ 返回结果（若开启缓存则存入缓存）。
- 查询缓存缺陷：仅相同 SQL（字符完全一致）命中，表修改即失效，更新频繁场景命中率极低，MySQL 8.0 移除。

建议在静态表使用缓存

静态表的定义

静态表是指那些极少更新的表，例如系统配置表、字典表等。这类表的数据相对稳定，查询操作频繁且结果变化不大，适合使用查询缓存来优化性能。

可以通过以下命令监控查询缓存的命中率

SHOW STATUS LIKE '%Qcache%';

该命令会返回与查询缓存相关的状态信息，例如缓存命中次数、未命中次数等，从而帮助评估查询缓存的效果

MySQL 8.0 查询缓存功能在MySQL 8.0中已被移除，因此运行上述命令会返回空结果。
MySQL 5.7
```
SHOW GLOBAL VARIABLES LIKE "%query_cache_type%";
```
如果返回值为OFF，表示查询缓存已关闭。
对于需要缓存的查询语句，可以显式指定SQL_CACHE，例如：
```
SELECT SQL_CACHE * FROM test WHERE ID = 5;
```
query_cache_type参数
- 0：关闭查询缓存（OFF）。
- 1：开启查询缓存（ON），默认情况下所有查询都会被缓存。
- 2（DEMAND）：按需使用查询缓存。只有在SQL语句中显式使用SQL_CACHE关键词时，查询结果才会被缓存。
配置示例
```
query_cache_type=2
```
这样设置后，默认情况下SQL语句不会使用查询缓存，只有通过SQL_CACHE显式指定的查询才会被缓存。
MySQL 8.0（无查询缓存）

关键节点

解析器：确保 SQL 语法正确，生成可执行的语法树。
优化器：选择最优执行路径（如索引选择、表连接顺序），直接影响执行效率。
执行器：校验用户权限，调用存储引擎 API 读写数据（如无索引则全表扫描，有索引则按索引查找）。

2.1.2 解析器

作用：对SQL语句进行语法分析和语义分析，确定SQL语句的正确性和含义。
主要步骤：
- 词法分析：识别SQL语句中的各个组成部分，如关键字、表名、列名等。例如，将“select”识别为查询语句的关键字，将“T”识别为表名，将“ID”识别为列名。
- 语法分析：根据语法规则判断SQL语句是否正确。如果语句有误，会报错提示。例如，将“from”错写为“rom”会导致语法错误。
结果：生成语法树，为后续的优化和执行提供基础。

2.1.3 优化器

作用：确定SQL语句的最佳执行路径，提高查询效率。
主要任务：
- 选择执行方案：对于同一条查询语句，可能存在多种执行方式。优化器需要比较这些方式的效率，选择最优的执行计划。例如，对于两个表的join操作，可以先从一个表中筛选出符合条件的记录，再关联到另一个表进行进一步筛选，优化器需要决定先操作哪个表。
- 索引选择：优化器会根据表的索引情况，选择是否使用索引以及使用哪种索引，以加快查询速度。
优化阶段：
- 逻辑查询优化：通过改变SQL语句的内容，使其更高效。例如，对条件表达式进行等价谓词重写、条件简化，对视图和子查询进行优化，消除外连接和嵌套连接等。这些优化基于关系代数，目的是为物理查询优化提供更多的候选执行计划。
- 物理查询优化：基于关系代数的物理计算，计算各种执行路径的代价，选择代价最小的作为执行计划。在这个阶段，优化器会考虑如何高效地使用索引，提升单表和多表连接操作的查询效率。
后续流程：优化器确定执行方案后，SQL语句进入执行器阶段。
2.1.4执行器

机读执行顺序（右半部分）

是 数据库实际执行 SQL 的底层顺序，遵循 “先准备数据 → 再处理结果” 的逻辑，和手写顺序不同，目的是让数据库高效计算：

FROM <left_table> + 3. <join_type> JOIN <right_table> + 2. ON <join_condition> ：先加载表数据、处理关联（先确定参与查询的表，做 JOIN 关联，得到 “原始数据集”）
ON <join_condition> ：用关联条件过滤关联后的行（和 FROM 阶段一起，先筛选关联后的有效行）
WHERE <where_condition> ：过滤原始数据行（在分组前，先筛掉不需要的行，减少后续计算量）
GROUP BY <group_by_list> ：按字段分组（把数据分成若干组）
HAVING <having_condition> ：过滤分组结果（只保留符合条件的组）
SELECT + DISTINCT <select_list> ：确定最终要返回的字段、去重（分组后才确定选哪些字段，因为分组可能影响字段可用性，比如非分组字段要用聚合函数）
ORDER BY <order_by_condition> ：对结果排序（排序是最后一步处理结果的操作）
LIMIT <limit_number> ：限制结果行数（最后截断结果，返回给用户）

3. 数据库缓冲池（Buffer Pool）

3.1 缓冲池 vs 查询缓存

缓冲池（Buffer Pool）：
- 作用：缓存数据页和索引页，减少磁盘 I/O 操作，提升数据库性能。
- 内容：存储数据页、索引页、插入缓冲、锁信息、自适应哈希、数据字典等。
- 工作原理：
  1. 当需要访问某个页时，先检查是否在缓冲池中。
  2. 如果在缓冲池中，直接读取。
  3. 如果不在缓冲池中，从磁盘读取并加载到缓冲池。
  4. 缓冲池采用“位置 * 频次”原则，优先缓存热点数据。
  5. 支持预读机制，根据局部性原理提前加载可能用到的数据。
- 脏页（Dirty Page）：缓冲池中被修改过的页，与磁盘上的数据页不一致。
- Checkpoint 机制：定期将脏页写回到磁盘，避免每次更新都进行磁盘 I/O。
查询缓存（Query Cache）：
- 作用：缓存查询结果，减少查询执行时间。
- 内容：缓存查询语句和对应的查询结果。
- 工作原理：
  1. 如果相同的查询语句再次执行，直接返回缓存结果。
  2. 缓存命中条件苛刻，数据表变化时缓存失效，命中率低。
  3. MySQL 8.0 已废弃查询缓存，因为其性能提升有限且维护成本高。

3.2 缓冲池如何读取数据

读取流程：
1. 检查缓冲池中是否存在需要的页。
2. 如果存在，直接从缓冲池读取。
3. 如果不存在，从磁盘读取并加载到缓冲池。
更新操作：
1. 更新数据时，先修改缓冲池中的页。
2. 不会立即写回磁盘，而是通过 Checkpoint 机制定期刷新。
3. 当缓冲池空间不足时，会将不常用的脏页写回磁盘并释放空间。

补充说明

缓冲池的大小：可以通过配置参数 innodb_buffer_pool_size 来调整，通常建议设置为可用内存的 70%-80%。
预读机制：
- 线性预读：当按顺序读取数据时，预读后续的页。
- 随机预读：当访问某个页时，预读其周围的页。
脏页刷新策略：
- 后台刷新：定期将脏页写回磁盘。
- 前台刷新：当缓冲池空间不足时，强制刷新脏页。
- Checkpoint 机制：确保脏页在一定条件下写回磁盘，避免数据丢失。

总结

缓冲池是 InnoDB 存储引擎的核心组件，通过缓存数据页和索引页，减少磁盘 I/O，提升性能。
查询缓存虽然可以减少查询时间，但由于命中率低且维护成本高，已被 MySQL 8.0 废弃。
缓冲池的高效管理（如预读机制和 Checkpoint 机制）是提升数据库性能的关键。

面试高频问题

MySQL 架构分层及各层作用？
- 连接层（认证、线程管理）、服务层（SQL 解析、优化、缓存）、引擎层（数据存储）、存储层（文件存储）。
SQL 执行流程中优化器的作用？
生成最优执行计划，如选择索引、确定表连接顺序，直接影响执行效率。
查询缓存为什么被移除？
命中条件苛刻（需完全相同 SQL），表修改即失效，更新频繁场景命中率低，维护成本高。
Buffer Pool 的作用及配置建议？
- 减少磁盘 I/O，缓存热数据；建议innodb_buffer_pool_size设为内存 50%-70%，多实例拆分减少锁竞争。
缓冲池中的脏页如何处理？
通过checkpoint机制异步刷回磁盘，避免频繁磁盘写入，平衡性能与数据安全性。
Redo Log 和 Undo Log 的作用？
Redo Log：记录数据修改操作，确保崩溃后可恢复未刷盘的脏页数据；Undo Log：记录修改前数据，支持事务回滚。