MySQL查询语句的执行过程有哪些

共计 5460 个字符，预计需要花费 14 分钟才能阅读完成。

本篇文章为大家展示了 MySQL 查询语句的执行过程有哪些，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

查询请求的执行流程

众所周知在 MySQL 数据库应用中查询请求是使用最多的，假设我们输入代码段 1   中的 SQL，通过客户端请求 MySQL 服务器，会得到一个包含 user 的结果集。但是，其中 MySQL 的处理过程我们并不了解，那么下面就让我们一起看看在查询请求前后 MySQL 服务端发生了些什么吧。

Select * from user where userId=1

代码段 1

图 1 MySQL 查询请求处理流程

如图 1 所示，整张图由三部分组成，从上到下分别是客户端(紫色)、MySQL Server 层(绿色)、MySQL 存储引擎层(黄色)。

客户端不言而喻，主要负责与 MySQL Server 层建立连接，发送查询请求以及接受响应的结果集。

MySQL  Server 层，主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了 MySQL 的大部分主要功能，例如平时使用最多的存储过程、触发器、视图都在这一层中。  还有一个通用的日志模块 bin log。l MySQL   存储引擎层，主要负责数据的存储和提取。其支持多个存储引擎，例如：InnoDB、MyISAM 等。常用的有 InnoDB，它从 MySQL  5.5.5 版本开始成为了 MySQL 的默认存储引擎，重要的是 InnoDB 引擎包含了自带的日志模块 redo  log，这个在后面讲述更新语句的时候会着重提到。

上面介绍了 MySQL 的组件结构，那么这里将其处理 SQL 语句的流程简单梳理一遍，之后再对每个组件逐一进行介绍。如图 2 所示，在图 1   的基础上加上了流程处理的编号，顺着编号来看看 MySQL 的各各组件是如何处理 SQL 查询请求的。

1. 连接器：当客户端登陆 MySQL 的时候，对身份认证和权限判断。

2. 查询缓存: 执行查询语句的时候，会先查询缓存(MySQL 8.0 版本后移除)。

3. 分析器: 假设在没有命中查询缓存的情况下，SQL 请求就会来到分析器。分析器负责明确 SQL 要完成的功能，以及检查 SQL 的语法是否正确。

4. 优化器：为 SQL 提供优化执行的方案。

5. 执行器: 将语句分发到对应的存储引擎执行，并返回数据。

MySQL 组件定义

图 2 SQL 请求执行流程

上面通过一张大图将 SQL 执行流程进行了梳理，这里将对应的组件进行详细介绍。

连接器

客户端需要通过连接器访问 MySQL  Server，连接器主要负责身份认证和权限鉴别的工作。也就是负责用户登录数据库的相关认证操作，例如：校验账户密码，权限等。在用户名密码合法的前提下，会在权限表中查询用户对应的权限，并且将该权限分配给用户。在连接完成以后可以通过图 3 看到连接状态，可以通过命令行“show  processlist”生成图 3 的查询结果。其中“Command”列返回的内容中，“Sleep”表示 MySQL 相同中对应一个空闲连接。而“Query”表示正在查询的连接。

图 3 连接状态

上面提到了连接状态，这里将 5 种连接状态整理为如下表格，方便大家参考。

Command 含义 sleep 线程正在等待客户端发数据 query 连接线程正在执行查询 locked 线程正在等待表锁的释放 sorting result 线程正在对结果进行排序 sending data

向请求端返回数据  

MySQL 将连接器中的连接分为长连接和短连接。

长连接是指连接成功后，客户端请求一直使用是同一个连接。

短连接是指每次执行完 SQL 请求的操作之后会断开连接，如果再有 SQL 请求会重新建立连接。

由于短连接会反复创建连接消耗相同资源，因此多数情况下会选择长连接。但是为了保持长连接，会占用系统内存，而这些被占用的内存知道连接断开以后才会释放。这里提出了两个解决方案：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

定期断开长连接，每隔一段时间或者执行一个占用内存的大查询以后断开连接，从而释放内存，当查询的时候再重新创建连接。

MySQL 5.7 或者更高的版本，通过执行 mysql_reset_connection   来重新初始化连接。此过程不会重新建立连接，但是会释放占用的内存，将连接恢复到刚刚创立连接的状态。

查询缓存

在建立与数据库的连接以后就可以执行 SQL 语句来，不过在执行之前会先查询缓存，其目的是查看是否之前执行过该语句，并且将执行结果按照 key-value 的形式缓存在内存中了。

Key 是查询的 SQL 语句，Value 是查询的结果。如果缓存 Key   被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，执行完 SQL 仍旧会把结果缓存起来，方便下一次调用。

MySQL   查询不建议使用缓存，因为会出现这样的场景：如果针对某张表进行更新，针对这张表的查询缓存就会被清空。如果张表不断地被使用(更新、查询)，那么查询缓存会频繁地失效，获取查询缓存也失去了意义。不过可以运用在一些修改不频繁的数据表。

例如：系统配置、或者修改不频繁的表。缓存的淘汰策略是先进先出，适用于查询远大于修改的情况下，  否则建议使用 Redis 或者其他做缓存工具。因此大多数情况下不推荐使用查询缓存。MySQL 8.0   版本后删除了查询缓存的功能，官方认为该功能应用场景较少，所以将其删除。

如果你不需要在 MySQL 中使用查询缓存，也可以将参数 query_cache_type 设置成  DEMAND，那么默认情况下的执行 SQL 语句时就不会使用查询缓存了。如果打开了缓存可以通过“show status like   Qcache%”命令查看缓存的情况。

如图 4 所示，其中几个使用较多的状态值如下：

Qcache_inserts 是否有新的数据添加，每有一条数据添加 Value 会加一。

Qcache_hits 查询语句是否命中缓存，每有一条语句命中 Value 会加一。

Qcache_free_memory 缓存空闲大小。

如图 4 缓存状态

分析器

如果查询缓存没有命中，那么 SQL 请求会进入分析器，分析器是用来分辨 SQL 语句的执行目的，其执行过程大致分为两步：

第一步，词法分析(Lexical scanner)

主要负责从 SQL 语句中提取关键字，比如：查询的表，字段名，查询条件等等。

第二步，语法规则(Grammar rule module)

主要判断 SQL 语句是否合乎 MySQL 的语法。

其实说白了词法分析 (Lexical scanner) 就是将整个 SQL 语句拆分成一个个单词，而语法规则(Grammar rule  module) 则根据 MySQL 定义的语法规则生成对应的数据结构，并存储在对象结构当中。其结果供优化器生成执行计划，再调用存储引擎接口执行。来看下面这个例子，假设有这样一个 SQL 语句“select  username from userinfo”。

先通过词法分析，从左到右逐个字符进行解析，获得如表 1 的四个单词。

关键字非关键字关键字非关键字 selectusernamefromuserinfo

表 1 语法分析关键字

然后再通过语法规则解析，判断输入的 SQL   语句是否满足 MySQL 语法，并且生成图 5 的语法树。由 SQL 语句生成的四个单词中，识别出两个关键字，分别是 select   和 from。根据 MySQL 的语法 Select 和 from 之间对应的是 fields   字段，下面应该挂接 username; 在 from 后面跟随的是 Tables 字段，其下挂接的是 userinfo。

图 5 语法规则生成语法树

优化器

优化器的作用是对 SQL 进行优化，生成最有的执行方案。如图 6 所示，前面提到的 SQL 解析器通过语法分析和语法规则生成了 SQL 语法树。这个语法树作为优化器的输入，而优化器 (黄色的部分) 包含了逻辑变换和代价优化两部分的内容。在优化完成以后会生成 SQL 执行计划作为整个优化过程的输出，交给执行器在存储引擎上执行。

图 6 优化器所处的位置

如上图所示，这节的重点在优化器中的逻辑变换和代价优化上。

逻辑变换

逻辑变换也就是在关系代数基础上进行变换，其目的是为了化简，同时保证 SQL 变化前后的结果一致，也就是逻辑变化并不会带来结果集的变化。其主要包括以下几个方面：

否定消除：针对表达式“和取”或“析取”前面出现“否定”的情况，应将关系条件进行拆分，从而将外层的“NOT”消除。

等值常量传递：利用了等值关系的传递特性，为了能够尽早执行“下推”运算。“下推”的基本策略是，始终将过滤表达式尽可能移至靠近数据源的位置。

常量表达式计算：对于能立刻计算出结果的表达式，直接计算结果，同时将结果与其他条件尽量提前进行化简。

这样讲概念或许有些抽象，通过图 7 来看看逻辑变化如何在 SQL 中执行的吧。

图 7 逻辑变换

如图 7 所示，从上往下共有 4 个步骤：

1.   针对存在的 SQL 语句，首先通过“否定消除”，去掉条件判断中的“NOT”。语句由原来的“or”转换成“and”，并且大于小于符号进行变号。蓝色部分为修改前的 SQL，红色是修改以后的 SQL。

2. 等值传递，这一步很好理解分别降”t2.a=9”和”t2.b=5”分别替换掉 SQL 中对应的值。

3. 接下来就是常量表达式计算，将“5+7”计算得到“12”。

4. 最后是常量表达式计算后的化简，将”9 =10”化简为”true”带入到最终的 SQL 表达式中完成优化。

代价优化

代价优化是用来确定每个表，根据条件是否应用索引，应用哪个索引和确定多表连接的顺序等问题。为了完成代价优化，需要找到一个代价最小的方案。

因此，优化器是通过基于代价的计算方法来决定如何执行查询的(Cost-based Optimization)。

简化的过程如下：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

赋值操作代价：针对每个数据库操作 (创建表、返回数据集) 设置对应的代价，这个代价值一般设置为 1、0.2 之类的值，没有具体的含义就是对操作的代价定义。

计算操作数量：将 SQL 语句中涉及到的操作进行逻辑，并且做计算。说白了就是看这次 SQL 请求需要做哪些具体的数据库操作。

求和操作代价：既然知道 SQL 由哪些数据库操作组成，同时知道每个操作对应的代价，求和以后就是知道整体 SQL 执行的代价。

选择代价计划：如果说没给 SQL 执行的操作都是一个计划，那么这些操作的不同组合就会对应不同的计划，这里需要选择整体执行代价最低的操作计划，作为这次执行 SQL 语句的代价计划，从而达到总代价最低。

这里将配置操作的代价分为 MySQL 服务层和 MySQL 引擎层，MySQL 服务层主要是定义 CPU 的代价，而 MySQL 引擎层主要定义 IO 代价。MySQL  5.7 引入了两个系统表 mysql.server_cost 和 mysql.engine_cost 来分别配置这两个层的代价。

如下：

MySQL 服务层代价保存在表 server_cost 中，其具体内容如下：

row_evaluate_cost (default 0.2) 计算符合条件的行的代价，行数越多，此项代价越大

memory_temptable_create_cost (default 2.0) 内存临时表的创建代价

memory_temptable_row_cost (default 0.2) 内存临时表的行代价

key_compare_cost (default 0.1) 键比较的代价，例如排序

disk_temptable_create_cost (default 40.0) 内部 myisam 或 innodb 临时表的创建代价

disk_temptable_row_cost (default 1.0) 内部 myisam 或 innodb 临时表的行代价

由上可以看出创建临时表的代价是很高的，尤其是内部的 myisam 或 innodb 临时表。

MySQL 引擎层代价保存在表 engine_cost 中，其具体内容如下：

io_block_read_cost (default 1.0) 从磁盘读数据的代价，对 innodb 来说，表示从磁盘读一个 page 的代价

memory_block_read_cost (default 1.0) 从内存读数据的代价，对 innodb 来说，表示从 buffer  pool 读一个 page 的代价

目前 io_block_read_cost 和 memory_block_read_cost 默认值均为 1，实际生产中建议酌情调大 memory_block_read_cost，特别是对普通硬盘的场景。

MySQL 会根据 SQL 查询生成的查询计划中对应的操作从上面两张代价表中查找对应的代价值，并且进行累加形成最终执行 SQL 计划的代价。再将多种可能的执行计划进行比较，选取最小代价的计划执行。

执行器

当分析器生成查询计划，并且经过优化器以后，就到了执行器。执行器会选择执行计划开始执行，但在执行之前会校验请求用户是否拥有查询的权限，如果没有权限，就会返回错误信息，否则将会去调用 MySQL 引擎层的接口，执行对应的 SQL 语句并且返回结果。

例如 SQL：“SELECT * FROM userinfo WHERE username = Tom“

假设“username“字段没有设置索引，就会调用存储引擎从第一条开始查，如果碰到了用户名字是”Tom“，  就将结果集返回，没有查找到就查看下一行，重复上一步的操作，直到读完整个表或者找到对应的记录。

需要注意 SQL 语句的执行顺序并不是按照书写顺序来的，顺序的定义会在分析器中做好，一般是按照如下顺序：

上述内容就是 MySQL 查询语句的执行过程有哪些，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。