select 语句是如何在 MySQL 中执行的？

约定不等于承诺〃 2021-09-07 23:14 756阅读 0赞

MySQL 作为互联网行业使用最多的关系型数据库之一，与其免费、开源的特性是密不可分的。然而，很多小伙伴工作了很多年，只知道使用 MySQL 进行 CRUD 操作，这也导致很多小伙伴工作多年后，想跳槽进入大厂，却在面试的时候屡屡碰壁。

**问个简单的问题：select 语句是如何在 MySQL 中执行的？** 这也是很多面试官喜欢问的问题，如果你连这个简单的问题都不能回答的话，那就要好好规划下自己的职业生涯了。

好了，今天我们就一起来聊聊 select 语句是如何在 MySQL 中执行的。文章的主要内容如下：

![89e2dca28f7f5084977a5c8561b23c6b.png][]

## 频繁使用的 select 语句 ##

为了更好地贯穿全文，这里先来列举一个最简单的 select 查询语句，例如：查询 user 表中 id 为 1001 的用户信息，使用下面的 SQL 语句进行查询。

select * from user where user_id = 1001;

当我们在 MySQL 的命令行中输入上述 SQL 语句时，这条 SQL 语句到底在 MySQL 中是如何执行的呢？接下来，我们就以这条 SQL 语句为例，说说 select 语句是如何在 MySQL 中执行的。

## MySQL 逻辑架构 ##

在介绍 select 语句在 MySQL 中的执行流程之前，我们先来看看 MySQL 的逻辑架构，因为任何 SQL 语句的执行都离不开 MySQL 逻辑架构的支撑。也就是说， SQL 语句在 MySQL 中的执行流程与 MySQL 的逻辑架构是密不可分的。

![145761d3bb525cde51966b8572c60189.png][]

在上图中，我们简单地画了下 MySQL 的逻辑架构图，并且给出了逻辑分层和每层中各部分的功能。从逻辑上，我们可以将 MySQL 粗略地分成三层：Server 层、存储引擎层和系统文件层，而 Server 层中又可以分成网络连接层（连接器）和数据服务层（Server 层）。

Server 层中包含了连接器、查询缓存、分析器、优化器和执行器等 MySQL 的核心组成部分，另外，在 Server 层中还包含了所有的内置函数（比如：日期时间函数、加解密函数、聚合函数、数学函数等），存储引擎、触发器、视图等等。

存储引擎层主要负责和系统文件层进行交互，存储引擎层本身是插件式的架构设计，支持 InnoDB、MyISAM、Archive、Memory 等存储引擎。在 MySQL 5.5.5 及以后的版本中，MySQL 的默认存储引擎是 InnoDB。

系统文件层主要负责存储实际的数据，将数据以文件的形式存储到服务器的磁盘上。

接下来，我们就来说说一条 select 语句在 MySQL 的逻辑架构的每一部分到底是如何执行的。

## 连接器是如何授权的？ ##

首先，我们先来看看在服务器命令行输入连接 MySQL 的命令时，MySQL 的连接器是如何进行验证的。比如，我们在服务器的命令行输入了如下命令。

mysql -ubinghe -p

执行“回车”后，输入 binghe 账户的密码，与 MySQL 进行连接。此时，连接的过程需要完成经典的 TCP 握手操作。之后，连接器就开始认证连接的身份是否合法，最直接的就是验证用户名和密码是否正确。

如果用户名或者密码错误，MySQL 会提示 `Access denied for user`。如果用户名和密码正确，则连接器会到 MySQL 的权限表中查询当前连接拥有的权限。查询到权限之后，只要这个连接没有断开，则这个连接涉及到的权限操作都会依赖此时查询到的权限。

换句话说，一个用户登录 MySQL 并成功连接 MySQL 后，哪怕是管理员对当前用户的权限进行了修改操作，此时只要这个用户没有断开 MySQL 的连接，就不会受到管理修改权限的影响。管理员修改权限后，只有对新建的连接起作用。

如果客户端连接 MySQL 后，长时间没有执行任何操作，则连接器会自动断开与这个客户端的连接。具体多长时间断开是由 MySQL 的参数`wait_timeout`控制的，这个值默认是 8 小时。我们可以根据实际业务需要，自行调整这个参数的值，以使 MySQL 能够满足我们的实际业务场景。

由于客户端与 MySQL 的连接是比较复杂的，这个过程也是比较耗时的，它会涉及 TCP 的握手操作，还会查询当前连接的权限信息等。往往在实际的工作过程中，我们会使用数据库连接池的方式，将数据库的连接缓存起来，这就意味着我们是使用长连接与 MySQL 进行交互的。

但是使用长连接连接 MySQL 也会有一个问题：那就是有时候会发现 MySQL 占用的内存涨得特别快，这是因为 **MySQL 在执行的过程中，使用的临时内存是在连接对象里面进行管理的**。这些占用的资源只有在连接断开的时候，才会被释放。如果连接长时间不释放，就会出现大量的临时内存占用内存空间。如果时间久了，可能会导致占用过多的内存，从而被操作系统“消灭”了，给人的感觉就是 MySQL 意外重启了。

**我们可以使用如下的方案来解决这个问题：**

*  定期或者执行过一个比较占内存的查询操作后，断开连接，以后再重新建立和 MySQL 的连接。
 *  如果使用 MySQL 5.7 或更新的 MySQL 版本，可以通过执行`mysql_reset_connection`重新初始化 MySQL 的资源。重新初始化的过程不会重新连接 MySQL，也不会重新做权限的验证操作。

## 查询缓存的作用是什么？ ##

登录 MySQL 后，客户端就会与 MySQL 建立连接，此时执行 select 语句时，首先会到查询缓存中查询是否执行过当前 select 语句。如果之前执行过相应的 select 语句，则执行过的 select 语句和查询结果会以 key-value 的形式存放在查询缓存中，其中，key 是查询语句，value 是查询的结果数据。

如果在查询缓存中没有找到相应的数据，则会继续执行后续的查询阶段。执行完成后，会将结果缓存到查询缓存中。后续的查询如果命中缓存，则直接返回查询缓存中的数据，性能还是挺高的。

但是，大多数时候我不太建议小伙伴们开启查询缓存，为啥？原因很简单：**查询缓存失效的频率是非常频繁的，只要对一个表进行更新操作，则这张表上所有的查询缓存都会被清空。** 而且在 MySQL 8.0 中，直接删除了查询缓存的功能。

## 分析器对 select 语句做了什么？ ##

分析器主要是对 select 语句进行 **词法分析和语法分析** 操作。

如果 select 语句没有命中缓存，则首先会由分析器对其进行“词法分析”操作，此时，MySQL 会识别 select 语句中的每个字符串代表什么含义。

例如，MySQL 会通过"select"关键字识别出这是一个查询语句，也会把"user"识别为"数据表名 user"，把"id"识别成"字段名 id"。接下来，就要进行“语法分析了”，根据语法规则，判断 select 语句是否满足 MySQL 的语法。如果判断出输入的 SQL 语句不满足语法规则，则 MySQL 会提示相应的错误信息。

## 优化器是如何优化 select 语句的？ ##

对 select 语句进行了词法分析和语法分析后，还要经过优化器的优化处理才能执行。比如，我们的 select 语句中如果使用了多个索引，则优化器会决定使用哪个索引来查询数据；再比如，在 select 语句中，有多表关联的操作，优化器会决定各表的连接顺序，数据表的连接顺序不同，对于执行的效率会大不相同，优化器往往会选择使用查询效率高的连接顺序。

如果 select 语句经过优化器的优化之后，就会进入执行阶段了。

## 执行器如何执行 select 语句？ ##

进入执行阶段的 select 语句，首先，执行器会对当前连接进行权限检查，最直接的方式就是检查当前连接是否对数据表 user 具有查询权限。如果当前连接对数据表 user 没有查询权限，就会返回没有权限的错误。例如，会返回如下错误。

ERROR 1142 (42000): SELECT command denied to user 'binghe'@'localhost' for table 'user'

如果当前连接具有对数据表 user 的查询权限，则会继续执行。首先会进行打开数据表的操作，此时优化器会根据创建表时使用的存储引擎，使用相应存储引擎的接口执行查询操作。这里，我们举一个例子：

假设，我们在 id 字段上没有建立索引，执行器执行的流程大致如下所示。

（1）通过存储引擎读取数据表 user 的第一行数据，判断当前行的 id 值是否等于 1001，如果不等于 1001，则继续读取下一行数据；如果等于 1001，则将当前行放入结果集中。

（2）继续通过存储引擎读取下一行数据，执行与（1）相同的逻辑判断，直到处理完 user 表中的所有数据。

（3）处理完所有的数据后，执行器就会将结果集中的数据返回给客户端。

如果在 id 字段上有索引的话，执行的整体逻辑与 id 字段上没有索引大体一致。

如果开启了慢查询的话，执行 select 语句时，会在慢查询日志中输出一个 rows\_examined 字段，这个字段表示 select 语句在执行的过程中扫描了数据表中的多少行数据。不过在有些场景下，执行器调用一次，存储引擎内部会会扫描多行，这就导致存储引擎扫描的行数与 rows\_examined 字段标识的行数并不完全相同。

[89e2dca28f7f5084977a5c8561b23c6b.png]: /images/20210730/8dca93e73e1c4ded9f21023c07ac958c.png
[145761d3bb525cde51966b8572c60189.png]: /images/20210730/3299554bb48e4d08be2e335881ee3f94.png