执行 - Mysql语句执行流程
执行流程图

MySQL 的架构可以大致划分为四个层次:连接层、服务层、存储引擎层和文件系统层。
- 连接层:负责对来自客户端的连接进行权限验证,并将连接信息存入连接池中,方便后续的连接复用。
- 服务层:主要负责 SQL 语句的解析与优化,还包括查询缓存和 MySQL 内置函数的实现。
- 存储引擎层:提供多种可插拔的存储引擎,允许我们通过不同的引擎进行数据的存取操作。存储引擎使得 MySQL 能够直接与硬盘上的数据和日志进行交互,用户可以根据需求选择合适的引擎。从 MySQL 5.5 版本开始, InnoDB 成为了 MySQL 的默认存储引擎。
- 文件系统层:这一层主要包括日志文件、数据文件及与 MySQL 相关的其他程序。在这四个层次中,服务层和存储引擎层构成了架构的核心。服务层负责处理 MySQL 的核心逻辑,而存储引擎层则直接负责数据的存取操作。
也可以将其简单的分成两层:Server 层和存储引擎层,如图

- Server 层:负责建立连接、分析和执行SQL。主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了MySQL的大部分主要功能。
- 存储引擎层:负责数据的存储和提取。
连接器
客户端需要通过连接器访问MySQL Server,连接器主要负责身份认证和权限鉴别的工作。也就是负责用户登录数据库的相关认证操作,例如:校验账户密码,权限等。在用户名密码合法的前提下,会在权限表中查询用户对应的权限,并且将该权限分配给用户。
如何查看有多少连接?
执行 show processlist 命令进行查看
其中”Command”列返回的内容中,“Sleep”表示MySQL相同中对应一个空闲连接。而“Query”表示正在查询的连接。
连接状态:
Command | 含义 |
---|---|
sleep | 线程正在等待客户端发数据 |
query | 连接线程正在执行查询 |
locked | 线程正在等待表锁的释放 |
sorting result | 线程正在对结果进行排序 |
sending data | 向请求端返回数据 |
空闲连接是否一直存在
从上图可以看出有许多空闲连接,MySQL 定义了空闲连接的最大空闲时长,由 wait_timeout 参数控制的,默认值是 8 小时(28880秒),如果空闲连接超过了这个时间,连接器就会自动将它断开。
手动断开空闲的连接,使用的是 kill connection + id 的命令
最大连接数

长连接和短连接
- 长连接是指连接成功后,客户端请求一直使用是同一个连接。
- 短连接是指每次执行完SQL请求的操作之后会断开连接,如果再有SQL请求会重新建立连接。
由于短连接会反复创建连接消耗相同资源,因此多数情况下会选择长连接。但是为了保持长连接,会占用系统内存,而这些被占用的内存知道连接断开以后才会释放。这里提出了两个解决方案:
- 定期断开长连接,每隔一段时间或者执行一个占用内存的大查询以后断开连接,从而释放内存,当查询的时候再重新创建连接。
- 客户端主动重置连接。MySQL 5.7 或者更高的版本,通过执行 mysql_reset_connection 来重新初始化连接。此过程不会重新建立连接,但是会释放占用的内存,将连接恢复到刚刚创立连接的状态。
查询缓存
在建立与数据库的连接以后就可以执行SQL语句了
如果 SQL 是查询语句(select 语句),MySQL 就会先去查询缓存( Query Cache )里查找缓存数据,看看之前有没有执行过这一条命令,并且将执行结果按照key-value的形式缓存在内存中了。
Key 是查询的SQL语句,Value 是查询的结果。如果缓存 Key 被命中,就会直接返回给客户端,如果没有命中,就会执行后续的操作,执行完SQL仍旧会把结果缓存起来,方便下一次调用。
Mysql的机制是只要一个表有更新操作,那么这个表的查询缓存就会被清空。如果张表不断地被使用(更新、查询),那么查询缓存会频繁地失效,获取查询缓存也失去了意义。不过可以运用在一些修改不频繁的数据表。例如:系统配置、或者修改不频繁的表。
缓存的淘汰策略是先进先出,适用于查询远大于修改的情况下, 否则建议使用Redis或者其他做缓存工具。因此大多数情况下不推荐使用查询缓存。MySQL 8.0 版本后删除了查询缓存的功能,官方认为该功能应用场景较少,所以将其删除。
这里说的查询缓存是 server 层的,与Innodb 存储引擎中的 buffer pool的缓存无关。也就是 MySQL 8.0 版本移除的是 server 层的查询缓存,
解析 SQL
在正式执行 SQL 查询语句之前, MySQL 会先对 SQL 语句做解析,这个工作交由「解析器」来完成。
解析器会做两件事情
- 词法分析。MySQL 会根据你输入的字符串识别出关键字出来,构建出 SQL 语法树,这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
- 语法分析。根据词法分析的结果,语法解析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。
- 语义分析:语义分析主要是检查 SQL 语句中的每个对象是否符合数据库的实际情况。如表名、字段名是否存在,用户是否对相关表和列拥有执行权限,数据类型是否匹配等。

如果输入的 SQL 语句语法不对,就会在解析器这个阶段报错。比如把 from 写成了 form,这时 MySQL 解析器就会给报错.
执行SQL语句
每条SELECT 查询语句流程可以分为三个阶段:
- prepare 阶段,也就是预处理阶段;
- optimize 阶段,也就是优化阶段;
- execute 阶段,也就是执行阶段;
预处理器
预处理器的作用:
- 检查 SQL 查询语句中的表或者字段是否存在;
- 将 select * 中的 * 符号,扩展为表上的所有列;
优化器
优化器的主要任务是对 SQL 查询进行优化,生成一个最优的执行计划,从而提高查询性能。优化器的工作基于查询的解析树和元数据,它会尝试在不同的查询执行策略中选择效率最高的一个。
在查询优化器中,分为逻辑查询优化和物理查询优化两个大块
逻辑优化会进行一些逻辑层面的优化,主要目的是通过调整 SQL 语句的结构来提高查询效率。包括:
- 消除冗余的子查询:将某些子查询转换为连接或合并查询。
- 重写查询:比如将 OR 条件转换为 UNION 操作。
- 查询合并:将多个查询合并成一个查询。
- 移除不必要的操作:例如消除不需要的 ORDER BY 或 DISTINCT。
物理查询优化是根据数据库的具体执行引擎、索引、统计信息等做出的决策。这个阶段会根据优化器评估的成本模型选择合适的执行计划。具体的优化措施包括:
- 选择合适的连接方式:比如选择 Nested Loop Join、Hash Join 或 Sort Merge Join。
- 选择索引:通过选择合适的索引来加速数据访问。
- 选择合适的排序方式:通过使用索引扫描或临时表来避免全表扫描。
优化器会使用**基于成本的模型(Cost-Based Optimization)**来评估每种查询执行计划的成本,选择成本最低的执行计划。其核心是通过计算不同执行计划的资源消耗(如 CPU 时间、I/O 操作等),并选出最优的执行策略。
优化器的目标是通过多种优化策略来降低查询的执行成本,生成一个尽可能高效的执行计划。它在逻辑层面和物理层面对 SQL 查询进行优化,以减少查询执行所需的资源。
执行器
当解析器生成查询计划,并且经过优化器以后,就到了执行器。
在执行之前,执行器会首先检查用户是否有权限执行相应的操作。如果没有权限,则返回错误信息。
执行器的主要工作包括:
- 表扫描:根据查询条件决定是否使用索引、是否全表扫描。
- 连接操作:根据优化器选择的连接方式(如嵌套循环连接、哈希连接等)执行表之间的数据合并。
- 排序和聚合:执行查询中的 ORDER BY、GROUP BY 等操作。
- 数据返回:查询结果被返回给用户,修改操作则会提交事务。
对于涉及数据修改的 SQL(如 INSERT、UPDATE、DELETE 等),执行器还需要管理事务的提交和回滚操作,确保数据的一致性和持久性。这些操作会与 MySQL 的日志系统(Undo Log、Redo Log、Binlog) 密切交互,确保事务的 ACID 属性。
执行器根据优化器生成的执行计划实际执行 SQL 查询,完成数据操作,返回查询结果或更新数据库状态。它是查询执行的最后环节,直接与 MySQL 的存储引擎进行交互。
查询语句执行流程
查询语句的执行流程如下:权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。
举个例子,查询语句如下:
select * from user where id > 1 and name = 'seven';
- 首先,通过连接器,客户端与MySQL服务器建立连接,并完成身份认证和权限验证过程。在此过程中,客户端需要提供用户名和密码以证明其合法性,服务器则会对这些信息进行核对。
- 检查是否开启缓存。MySQL 8.0之前,Query Cache 确实会缓存完全相同的查询结果,以便重复执行相同查询时直接返回缓存数据。然而,MySQL 8.0及以后版本已经完全弃用Query Cache,因此在MySQL 8.0及更高版本中这一步骤不在适用。
- 1MySQL的解析器会对查询语句进行解析,检查语法是否正确,并将查询语句转换为内部数据结构。预处理器则会根据MySQL的规则进一步检查解析树是否合法,如检查数据表或数据列是否存在等。
- 优化器会根据查询语句的结构、表的统计信息等因素,生成多个可能的执行计划,并通过成本估算器选出最优的执行计划。两种执行方案,先查 id > 1 还是 name = 'seven',优化器根据自己的优化算法选择执行效率最好的方案;这一步旨在提高查询效率,降低资源消耗。
- 执行器按照优化器选择的执行计划,调用存储引擎的API来执行查询。存储引擎负责实际的数据存储和检索,根据执行器的请求,读取或写入数据。
- 存储引擎负责实际的数据存储和检索工作,根据执行器的请求,读取或写入数据。
- 如果开启了Query Cache且查询结果能够命中缓存,查询结果会从缓存中直接返回。而如果没有开启Query Cache或缓存没有命中,MySQL会直接返回查询结果。
更新语句执行过程
更新语句执行流程如下:分析器、权限校验、执行器、引擎、redo log(prepare状态)、binlog、redo log(commit状态)
举个例子,更新语句如下:
update user set name = 'seven' where id = 1;
具体的执行流程如下图:

- 找存储引擎取到 id = 1 这一行记录。
- 根据主键索引树找到这一行,如果 id = 1 这一行所在的数据页本来就在内存池(Buffer Pool)中,就直接返回给执行器;否则,需要先从磁盘读入内存池,然后再返回。
- 记录Undo Log日志,对数据进行备份,便于回滚。
- 拿到存储引擎返回的行记录,把 name 字段设置为 “seven”,得到一行新的记录,然后再调用存储引擎的接口写入这行新记录。
- 将这行新数据更新到内存中,同时将这个更新操作记录到 Redo Log 里面,为 Redo Log 中的事务打上 prepare 标识。然后告知执行器执行完成了,随时可以提交事务。
- 生成这个操作的 Binlog,并把 Binlog 写入磁盘。
- 提交事务。
- 把刚刚写入的 Redo Log 状态改成提交(commit)状态,更新完成。
关于以上日志的介绍,可以看这篇文章
以上只是一个简单的case,方便我们能够简单的熟悉流程。接下来,我们对update过程中的全流程进行梳理,具体的流程如下图:

- 首先客户端发送一条 SQL 语句到 Server 层的 SQL interface。
- SQL interface 接到该请求后,先对该条语句进行解析,验证权限是否匹配,也就是在我们上文中讲到的执行器中在执行。
- 验证通过以后,分析器会对该语句分析,是否语法有错误等。
- 接下来是优化器生成相应的执行计划,选择最优的执行计划,然后是执行器根据执行计划执行这条语句。
- 执行器从Buffer Pool中获取数据页的数据,如果数据页没有,需要从磁盘中进行加载。
- 开启事务,修改数据之前先记录Undo Log,写入Buffer Pool的Undo Page。
- 开始更新数据页中的记录,被修改的数据页称为脏页,修改会被记录到内存中的 Redo Log Buffer中,再刷盘到磁盘的Redo Log文件,此时事务是 perpare阶段。
- 这个时候更新就完成了,当时脏页不会立即写入磁盘,而是由后台线程完成,这里会用double write来保证脏页刷盘的可靠性。
- 通知Server层,可以正式提交数据了, 执行器记录Binlog cache,事务提交时才会将该事务中的Binlog刷新到磁盘中。
- 这个时候Update语句完成了Buffer Pool中数据页的修改、Undo Log、Redo Log缓存记录,以及记录Binlog cache缓存。
- commit阶段,这个阶段是将Redo Log中事务状态标记为commit。
- 此时Binlog和Redo Log都已经写入磁盘,如果触发了刷新脏页的操作,先把脏页copy到double write buffer里,double write buffer 的内存数据刷到磁盘中的共享表空间 ibdata,再刷到数据磁盘上数据文件 ibd。
以上就是修改语句的全部流程,为什么记录完redo log,不直接提交,而是先进入prepare状态?
这里涉及到两阶段提交问题。假设先写redo log直接提交,然后写binlog,写完redo log后,机器挂了,binlog日志没有被写入,那么机器重启后,这台机器会通过redo log恢复数据,但是这个时候binlog并没有记录该数据,后续进行机器备份的时候,就会丢失这一条数据,同时主从同步也会丢失这一条数据。
总结
- 连接器:建立连接,管理连接、校验用户身份;
- 查询缓存:查询语句如果命中查询缓存则直接返回,否则继续往下执行。MySQL 8.0 已删除该模块;
- 解析 SQL,通过解析器对 SQL 查询语句进行词法分析、语法分析,然后构建语法树,方便后续模块读取表名、字段、语句类型;
- 执行 SQL:执行 SQL 共有三个阶段:
- 预处理阶段:检查表或字段是否存在;将 select * 中的 * 符号扩展为表上的所有列。
- 优化阶段:基于查询成本的考虑, 选择查询成本最小的执行计划(选择使用哪个索引);
- 执行阶段:根据执行计划执行 SQL 查询语句,从存储引擎读取记录,返回给客户端;
