【MySQL】基础架构分析

考察频率	难度
40%	⭐⭐⭐⭐

这道题在面试时的出现频率其实并不高，最起码对于笔者来说是没有遇到过。那为什么还是选择把这个问题作为 MySQL 八股文系列的第一个呢？其实原因也挺简单的，还是老规矩，先通过一个问题把整个知识框架来一个概览。

完整地理解 MySQL 的基础架构有助于后面理解很多问题，这些在后面的学习过程中你就会有所感受。闲话少说，直接开始正文吧。

基本架构示意图

先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图，在下面中会详细介绍到这些组件的作用。

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。

大体来说，MySQL 可以分为 Server层和存储引擎两部分。

Server层 包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、 Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎。不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。

Server层

连接器

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;

查询缓存

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

MySQL 8.0 之前可以正常的使用查询缓存的功能，可通过 “SHOW VARIABLES LIKE '%query_cache_type%'” 命令查询数据库是否开启了查询缓存的功能。

它的结果值有以下三项：

OFF，关闭了查询缓存功能；
ON，开启了查询缓存功能；
DEMAND，在 sql 语句中指定 sql_cache 关键字才会有查询缓存，也就是说必须使用 sql_cache 才可以把该 select 语句的查询结果缓存起来，比如“select SQL_CACHE * from T where ID=10；”语句。

开启和关闭查询缓存可以通过修改 MySQL 的配置文件 my.cnf 进行修改，它的配置项如下：

query_cache_type = ON

注意：配置被更改之后需要重启 MySQL 服务才能生效。建议设置为按需缓存（DEMAND）模式，因为查询缓存的功能并不是那么好用。

分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器

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

MySQL 能够处理的优化类型：

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

执行器

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

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

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

存储引擎

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

常见的存储引擎包括：

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

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

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

一条 sql 语句是如何执行的？

综合上面对 MySQL 基础架构的分析，我们来看一个比较高频的面试题——一条 sql 语句是如何执行的？

客户端通过连接器与MySQL服务器建立连接、获取权限、维持和管理连接；
查询缓存，如果开启查询缓存，则先去缓存哈希表查找数据，如果命中缓存，则直接返回数据给客户端；如果没有命中缓存，则继续执行下面逻辑；
解析器通过词法分析和语法分析验证SQL是否合法，并生成相应“语法树”；并通过预处理器进一步检查”语法树“是否合法；
接着，优化器将语法树转化成执行计划。执行计划决定了执行器会选择存储引擎的哪个方法去获取数据。MySQL使用基于成本的优化器，它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
执行器负责根据这个执行计划调用存储引擎的API接口来完成整个查询工作。
MySQL将结果集增量、逐步返回给客户端，如果开启了查询缓存，MySQL在这个阶段会将结果放到查询缓存中。