MySQL 基础知识（十）之 MySQL 架构

1 MySQL 架构说明

下图是 MySQL 5.7 及其之前版本的逻辑架构示意图

MySQL 架构大致可分为以下三层：

• 连接层：负责跟客户端建立连接、获取权限、维持和管理连接
• 核心业务层：包括查询缓存、解析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等
• 存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎

2 连接层

MySQL 连接层是 MySQL 数据库架构中的一个重要组成部分，它主要负责处理客户端与数据库服务器之间的连接和通信。

主要作用为：

• 连接管理：连接层负责监听客户端的连接请求，当客户端请求连接时，连接层会创建一个新的连接或者复用已有的空闲连接，以供客户端使用。在客户端使用完连接后，连接层会关闭连接以释放资源。为了提高性能和响应速度，连接层通常会实现一个线程池，线程池可以减少创建和销毁线程的开销，因为线程可以被重复使用
• 通信协议处理：连接层处理客户端与数据库服务器之间的通信协议。它负责解析和处理客户端发送的请求，并将请求转换为数据库服务器能够理解的格式。同时，它也将数据库服务器的响应转换为客户端能够理解的格式，并返回给客户端
• 安全性管理：连接层负责处理数据库的安全性。它验证客户端的身份和权限，确保只有经过授权的客户端能够访问数据库。连接层还可以对客户端发送的数据进行加密和解密，以保护数据库中的敏感信息
• 异常处理：连接层还负责处理数据库连接过程中的异常情况。它可以检测到连接超时、网络中断等异常情况，并采取相应的措施，如重新连接或返回错误信息给客户端

查看连接状态

通过 show processlist 或 show full processlist 命令可以查看连接状态

• Id：线程 id
• User：启动这个线程的用户
• Host：发送请求的客户端的 IP 和端口号
• db：当前命令在哪个库执行
• Command：该线程正在执行的操作命令 - Create DB：正在创建库操作- Drop DB：正在删除库操作- Execute：正在执行一个 Prepared Statement- Close Stmt：正在关闭一个 Prepared Statement- Query：正在执行一个语句- Sleep：正在等待客户端发送语句- Quit：正在退出- Shutdown：正在关闭服务器
• Time：表示该线程处于当前状态的时间，单位是秒
• State：线程状态 - Updating：正在搜索匹配记录，进行修改- Sleeping：正在等待客户端发送新请求- Starting：正在执行请求处理- Checking table：正在检查数据表- Closing table : 正在将表中数据刷新到磁盘中- Locked：被其他查询锁住了记录- Sending Data：正在处理 Select 查询，同时将结果发送给客户端
• Info：一般记录线程执行的语句，默认显示前100个字符。想查看完整的使用 show full processlist;

# 查看连接状态
show processlist;

3 核心业务层

3.1 查询缓存

    当 MySQL 接收到一个查询请求时，它首先会检查查询缓存中是否已经存在与该查询请求相同的查询结果。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。其中 key 是查询的语句，value 是查询的结果。如果存在，MySQL 会直接返回缓存中的结果，而不需要再执行查询语句。如果查询缓存中没有该查询结果，MySQL 会执行查询语句并将结果存储在查询缓存中，以便下次相同的查询可以直接从缓存中获取结果。我们一般不使用查询缓存，因为一旦查询涉及的表发生改变，该缓存会失效，会被查询缓存清除。从 MySQL 8.0 版本开始，MySQL 不再支持查询缓存。

3.2 解析器

    MySQL 首先会进行**词法分析** (Lexical scanner)，将 SQL 语句分成一个个的**词法单元 (Token) **。其中 Token 中包含 Keyword（也称 symbol）和非 Keyword。之后根据**语法分析** (Grammar rule module) 生成一棵对应的“**解析树**”。最后进行**语义分析**，检查“解析树”中的元素是否在数据库中有对应的实体，并验证操作的合法性。例如会检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。

3.3 优化器

    优化器会根据“解析树”生成最优的执行计划。这些执行计划可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

MySQL 能够处理的优化类型：

• 重新定义关联表的顺序
• 将外连接转化为内连接
• 使用等价变换规则简化并规范 SQL 语句，如（5 = 5 AND a > 5) 会被改写成 a > 5
• 预估并转化为常数表达式，当 MySQL 检测到一个表达式可以转化为常数时，会一直把该表达式作为常数进行优化处理，如在索引列执行 COUNT()、MIN() 和 MAX()
• 子查询优化，减少数据访问次数
• 提前终止查询，当已经满足查询需求时，MySQL 能够立刻终止查询，如使用 LIMIT 子句或条件不成立时，立刻终止后续查询
• 等值传播，如果两个列的值通过等式关联，MySQL 能够把其中一个列的 WHERE 条件传递到另一个列上

select g1.id,g1.name
from goods g1
inner join goods g2 using(id)
where g1.id > 5;

# where g1.id > 5 等价于 where g1.id > 5 and g2.id > 5
select g1.id,g1.name
from goods g1
inner join goods g2 using(id)
where g1.id > 5 and g2.id > 5;

• 列表 IN() 的比较，如 IN(2,1,3)，MySQL 会 IN() 列表中的数据进行排序，排序后为 IN(1,2,3)，然后可以通过二分查找的方式确定列表中的值是否满足条件

3.4 执行器

    MySQL 执行器是 MySQL 服务器中的一个组件，它负责执行优化器生成的执行计划，并返回查询结果。

MySQL 执行器的主要职责包括：

• 执行查询计划：根据优化器生成的执行计划，执行器会执行相应的操作，如读取数据、连接表、执行排序、聚合等
• 处理数据：执行器会从存储引擎中读取数据，并对数据进行处理，如过滤、排序、分组等
• 返回结果：执行器会将处理后的数据返回给客户端，通常以行的形式返回
• 管理事务：执行器会负责管理事务的提交和回滚，确保数据的一致性和完整性
• 处理错误：如果在执行过程中发生错误，执行器会捕获错误并将其返回给客户端

执行一条 SQL 语句的过程

4 存储引擎层

    MySQL 存储引擎是 MySQL 数据库的核心组件之一，它负责管理数据的存储和访问。MySQL 支持多种存储引擎，每个存储引擎都有自己的特点和适用场景。

常见的存储引擎包括：

• InnoDB：InnoDB 是 MySQL 的默认存储引擎，它支持事务、热备份（可以在不停机的情况下进行数据备份）、行级锁、外键约束等特性，适用于大多数应用场景
• MyISAM：MyISAM 是 MySQL 的早期存储引擎，它支持快速的读取和插入操作，但不支持事务和外键约束
• Memory：Memory 存储引擎将数据存储在内存中，适用于需要快速访问数据的场景，但数据在服务器重启后会丢失
• CSV：CSV 存储引擎将数据存储为 CSV 文件，适用于需要导入和导出数据的场景
• Archive：Archive 存储引擎用于存储归档数据，支持快速的插入和压缩操作，但不支持索引和查询

存储引擎层的主要职责包括：

• 管理数据存储：存储引擎负责将数据存储在磁盘上，并提供高效的数据访问方式
• 支持索引：存储引擎负责管理索引的创建、维护和查询
• 处理事务：存储引擎支持事务的原子性、一致性、隔离性和持久性
• 提供数据恢复：存储引擎负责在服务器崩溃或意外关闭后，恢复数据的完整性
• 优化查询性能：存储引擎会根据查询条件和索引，选择最优的查询计划来提高查询性能

5 参考文档

《高性能MySQL》

深入浅出MySQL - 架构与执行 (https://www.cnblogs.com/novwind/p/17489997.html)

MySQL架构原理(详解) (https://cloud.tencent.com/developer/article/1981543)

MySQL架构详解 (https://mdnice.com/writing/43f52aeaee9644b1bc1373ff85f89972)