MySQL 查询语句执行过程

梦里梦外; 2023-10-06 13:03 70阅读 0赞

当我们执行一条 `select * from table where ID=1;` 查询语句时，MySQL 内部给我们做了什么呢？

### 逻辑架构简介 ###

首先我们得先了解下 MySQL 中的一些零件：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4_size_16_color_FFFFFF_t_70]

再来介绍一下各层功能：

**连接层：** 最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

**服务层：** 服务层涵盖了 MySQL 的大多数核心功能以及内置函数，以及存储过程、触发器和视图等跨存储引擎的功能，这是这篇文章中的重头戏。

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Connection Pool</td> 
   <td>连接池：提供认证、连接数限制，检查内存等功能</td> 
  </tr> 
  <tr> 
   <td>Management Serveices &amp; Utilities</td> 
   <td>系统管理和控制工具</td> 
  </tr> 
  <tr> 
   <td>SQL Interface</td> 
   <td>SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface</td> 
  </tr> 
  <tr> 
   <td>Parser</td> 
   <td>解析器，SQL 命令传递到解析器的时候会被解析器验证和解析</td> 
  </tr> 
  <tr> 
   <td>Optimizer</td> 
   <td>查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有where 条件时，优化器来决定先投影还是先过滤。</td> 
  </tr> 
  <tr> 
   <td>Cache 和 Buffer</td> 
   <td>查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等</td> 
  </tr> 
 </tbody> 
</table>

**引擎层：** 我们常说的 MyISAM 以及 InnoDB 就在这一层了。

**存储层：** 将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

### 查询语句执行顺序 ###

> 有了上面有个 MySQL 逻辑架构的介绍，相信在看这部分内容的时候会比较方便一些。

**文字版：** SQL 接口→解析器/查询缓存（如果缓存中有，则直接返回结果；如果没有，则进行交给解析器处理，解析器处理完后交由下一步并向查询缓存中添加缓存）→优化器→执行器。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4_size_16_color_FFFFFF_t_70 1]  
在极客时间的《MySQL 实战 45 讲》中，给出的图是下面的，和我的有一些出入（作者可能将连接池和 SQL Interface 一块命名为连接器），欢迎各位读者提出自己见解。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4_size_16_color_FFFFFF_t_70 2]  
本着「站在巨人的肩膀上」的原则，下文将参考《MySQL 实战 45 讲》中的内容，暂时保留个人关于「权限验证」方面的见解。

#### 连接器 ####

当咱们登录 MySQL 时，连接器就会去验证此连接的账号密码，两种情况：

1.  密码正确，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
2.  密码错误，收到一个 “Access denied for user” 的错误提醒，客户端程序结束运行。

> 如果一个用户的密码或权限被修改，需要重新登录。

在数据库中存在两种连接：

1.  长连接：指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
2.  短连接：指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

> 建立连接的过程通常是比较复杂的，所以咱们在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。  
> 但是长连接比较占资源，故需要咱们：
> 
> 1.  定期断开长连接；
> 2.  如果使用的是 MySQL 5.7 或以上版本，咱们可以在每次执行一个比较大的操作之后，为了方便，咱们可以通过执行`mysql_reset_connection` 来重新初始化连接资源，这个过程不需要重新连接和权限验证。

#### 查询缓存 ####

MySQL 拿到查询请求后，回去缓存中查看，查看之前是否执行过此条语句。如果执行过，则语句与对应结果间存在 k-v 对的形式。如果不在缓存，则进行后面的过程。

不建议使用缓存，因为失效频繁（比如刚缓存了一个表，此时更新会将之前缓存清空）。更新数据量大的数据库缓存命中率很低。在 MySQL 8.0 版本中，查询缓存功能都被删除了。

#### 分析器 ####

这里就是将 `select * from table where ID=1;` 肢解的地方了，首先进行此法分析：

1.  select：执行查询操作
2.  table：要操作这个表
3.  where：条件是这个之后的

然后进行语法分析：就是看看语法是否符合 MySQL 语句的语法规范。

#### 优化器 ####

经过了分析器后，MySQL 知道了咱们要做啥，然后它就开始给咱们优化咱们的 SQL 语句：比如多个索引下使用哪个索引、多个 Join 的条件下如何进行。

#### 执行器 ####

优化完后，就该为我们执行 SQL 语句了：

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误：`ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'`；  
如果有权限，则打开对应表执行。打开表时按照表的引擎定义去使用对应引擎的接口（引擎是对表来说的）。以 InnoDB 为例：

1.  调用 InnoDB 引擎接口取这个表的第一行，判断 ID 的值是不是 1 ,如果不是就跳过，如果是那就放在结果集中；
2.  调用引擎接口取「下一行」,重复相同的判断逻辑,直到这个表的最后一行；
3.  执行器将上述遍历过程中所有满足条件的行,组成记录集返回给客户端。

### 关联阅读 ###

MySQL 索引简单入门请阅读：[MySQL 索引][MySQL]

MySQL 查询优化请阅读：[MySQL 查询优化][MySQL 1]

MySQL Explain 的详细使用请阅读：[MySQL Explain 使用，看这一篇就够了][MySQL Explain]

MySQL 慢查询以及 MySQL 存储引擎比较等文章正在不紧不慢地编写中，敬请期待~

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200716013324680.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200716020654870.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200716020804307.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01yQmF5bWF4,size_16,color_FFFFFF,t_70
[MySQL]: https://mindartisan.blog.csdn.net/article/details/106608303
[MySQL 1]: https://mindartisan.blog.csdn.net/article/details/107603173
[MySQL Explain]: https://mindartisan.blog.csdn.net/article/details/107595923