MySQL查询的执行流程

喜欢ヅ旅行 2024-04-06 13:58 41阅读 0赞

#### 文章目录 ####

*  MySQL分层
 *   *  1. Server层
     *  2. 存储引擎层
 *  查询的执行流程
 *   *  1. 建立连接
     *   *  MySQL通信
     *  2. 发送SQL
     *  3. 查询缓存
     *  4. 解析器
     *  5. 预处理器
     *  6. 查询优化器
     *  7. 查询执行引擎

--------------------

## MySQL分层 ##

![在这里插入图片描述][bdf5de7b2a184c91bf0e7269aca003ba.png]

MySQL 主要可以分为Server层和存储引擎层。

### 1. Server层 ###

Server层包括：连接器、查询缓存、分析器、优化器、执行器等

所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数、通用日志模块-binlog日志模块等。

### 2. 存储引擎层 ###

负责数据的存储和提取。支持InnoDB、MyISAM、Memory等多个存储引擎。

MySQL5.5.5版本开始，默认存储引擎为InnoDB。

## 查询的执行流程 ##

![在这里插入图片描述][f3ff037007e24494b8332e42026d266b.jpeg]

1.  建立连接：客户端与服务端通过连接器建立连接
2.  发送SQL：客户端发送查询SQL服务端
3.  查询缓存：服务器检查查询缓存。如果命中缓存，则直接返回
4.  解析器：解析SQL，得到解析树
    
    校验SQL是否符合语法规则。如关键字是否正确，关键字的顺序是否正确。
5.  预处理器：进一步校验，得到新的解析树
    
    校验表明、字段名等是否正确
6.  查询优化器：根据解析树生成执行计划
7.  查询执行引擎：通过执行引擎的API接口查询数据
8.  返回结果：返回结果数据，并缓存数据到查询缓存

### 1. 建立连接 ###

客户端与服务端通过连接器建立连接。

连接器主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作。

*  当用户密码不正确，会返回错误："Access denied for user
 *  当用户密码通过验证，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据。
    
    因此，只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

**`连接是可以复用的，因此一个查询SQL的执行过程可能直接获取一个已有连接，跳过建立连接这一步骤。`**

#### MySQL通信 ####

MySQL客户端和服务端之间的通信是半双工的。

MySQ通信的特点：

*  **数据可以双向传输**
    
    客户端可以服务器发送数据，服务端也可以向客户端发送数据
 *  **数据不可同时传输**
    
    客户端和服务端不能同时发送或同时接受数据，只能一个在发送数据，另一个在接受数据
 *  **客户端只会发送一个数据包给服务端，不会拆成多个数据包发送**
    
    可以通过MySQL配置参数max\_allowed\_packet（默认值：4M）设置数据包最大长度，注意SQL语句的长度问题
 *  **服务端返回给客户端可以有多个数据包**
    
    但是需要注意：客户端必须完整接收，不能接到一半停掉连接或用连接去做其他事

### 2. 发送SQL ###

当客户端将SQL发送到服务端后，连接器会检查该语句是否有权限，若是没有权限就直接返回错误信息，有权限才会继续进行下一步。比如：验证用户是否具有某个数据库的权限。

### 3. 查询缓存 ###

查询内存中是否缓存了该SQL的结果数据，如果有，直接返回给客户端。如果没有，则进行下一步。

**需要注意：在MySQL8.0中查询缓存已经被删除了，MySQL也觉得查询缓存比较鸡肋。**

*  查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
 *  查询缓存缓存SQL结果占用内存，但查询缓存的命中率一般会非常低，不划算。
 *  查找查询缓存和将结果存入到查询缓存都需要额外的性能消耗

**一般，只有到缓存的命中率比较高，且缓存涉及的表不会频繁更新的系统才适合使用查询缓存。**

MySQL会以key-value的方式将SQL和查询结果缓存到内存中。其中key为SQL查询语句的hash，value为SQl的查询结果数据。

**不会写入缓存的SQL：**

*  当查询语句中有一些不确定数据时，不会缓存。
    
    如：NOW()、CURRENT\_TIME()
 *  当查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表，一般都不会缓存。

### 4. 解析器 ###

解析SQL，得到解析树。

*  词法分析：提炼关键性字。如关键字，表名，字段名，查询条件等
 *  校验SQL语句**是否符合MySQL得语法规则**。如关键字是否正确，关键字的顺序是否正确。
    
    如果SQL语句不对，就会返回You have an error in your SQL syntax的错误提醒，一般语法错误会提示第一个出现错误的位置。

**语法分析和词法分析**

*  词法分析：提炼关键性字。
 *  语法分析：验证SQL语句是否正确。

个人理解：解析器有词法分析和语法分析，预处理器只有语法分析（不确定理解是否正确）。

### 5. 预处理器 ###

进一步校验，得到新的解析树。

*  校验表名、字段名等是否正确。
    
    如果表名、字段名等有错误，会有错误提醒
    
    表名错误：
    
        1146 - Table 'demo.article1' doesn't exist
    
    字段名错误：
    
        1054 - Unknown column 'id1' in 'field list'

### 6. 查询优化器 ###

根据解析树生成执行计划。

查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。

*  生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存。
 *  当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。

**mysql的优化类型**

*  关联表（join）的顺序可能会变
 *  outer join可能会变成内连接
 *  优化条件表达式， 例如 5=5 AND a>5被简化成a>5
 *  优化MAX\\MIN， 如果是MAX(索引），那么直接拿B+树的第一条或者最后一条即可。
 *  当发现某个查询或者表达式的结果是可以提前计算出来的时候，就会优化成常数
 *  索引覆盖，如果只要返回索引列，就不会走到最底层去。
 *  子查询优化
 *  提前终止查询（例如LIMIT）
 *  等值传播： join中可能把左表的where 拿给右表一起用
 *  IN(1,2,3,4,5,6)这个条件， 并不是简单遍历判断， 会先排序，然后用二分去判断是否存在。

### 7. 查询执行引擎 ###

优化器确定具体的执行计划后，会交给执行引擎执行操作。通过执行引擎的API接口查询数据。返回结果。

*  开始执行的时候，要先判断一下你对这个表有没有执行查询的权限。如果没有，就会返回没有权限的错误；如果有权限，就打开表继续执行。
 *  打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口：
    
    1.  调用引擎接口取这个表的第一行，判断是否满足条件，如果不是则跳过，如果是则将这行存在结果集中
    2.  调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行
    3.  执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

具体查询过程可参考文章：[MySQL关键字的执行顺序分析][MySQL]

至此，一个查询语句就执行完成了。

参考文章：  
[https://blog.csdn.net/qq\_34272760/article/details/124329767][https_blog.csdn.net_qq_34272760_article_details_124329767]

[bdf5de7b2a184c91bf0e7269aca003ba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/8122cc93be694751a13f41e49045d201.png
[f3ff037007e24494b8332e42026d266b.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3c0c6d9dd7064507be55e45b73dd7800.jpeg
[MySQL]: https://blog.csdn.net/JokerLJG/article/details/126509214
[https_blog.csdn.net_qq_34272760_article_details_124329767]: https://blog.csdn.net/qq_34272760/article/details/124329767