大白话MySQL执行SQL的流程

女爷i 2023-09-27 09:03 86阅读 0赞

# MySQL执行SQL的流程 #

## 一、流程概述 ##

> `注`：查询缓存：Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，如果进入到解析器阶段。需要说明的是，因为查询混窜往往效率不高，所以在MySQL8.0之后就抛弃了这个功能

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

如上图所示一个sql执行的流程大致如下

*  1.客户端通过 `半双工协议` 将查询发送到服务器端
 *  2.首先经过 `缓存` 如果缓存中有相关查询的结果，直接放回，否则进入3
 *  3.将sql交给 `解析器` ，构建语法树，这一步主要检测是否出现语法错误。
 *  4.语法树交给 `预处理器` 进一步解析，做预编译操作。主要是将要查询的表的列提取出来看看是否会有字段不存在这样的错误
 *  5.通过 `查询优化器` 将 `预处理器` 的结果计算出若干个计划，然后选择最优的计划
 *  6.排队之后 `查询引擎`执行计划通过API调用 `存储引擎` ，最终调用到数据库拿到数据。
 *  7.返回数据时生成新的缓存，将数据发送给客户端

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

## 二、MYSQL客户端/服务端通信协议 ##

`半双工` ：任何一个时刻都只能是一方发送一方接受。

**优点**：不需要切分数据。因为只要服务器端在发客户端就得接着

**缺点**：不方便做流量控制。

mysql有对max\_allow\_packet的设置，如果一个包的大小超过了这个设置，那么就会出现异常。

[https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html][https_dev.mysql.com_doc_refman_8.0_en_packet-too-large.html]

> 这里我有一个疑问，如果有一个极大的文件需要存储到数据库呢？

由于是`半双工`的协议，那么就要接收全部服务器端发送的包。如果像之前说的打开结果集读取若干行之后断开链接这个是不可取的。

**方向**： 实际上是服务器向客户端推送数据，所以客户端无法中断这个推送，也就是说上述打开结果集但是只选择几行，实际上还是耗费了内存的。

所以我门在设计查询的时候一定一定要做到简单高效。

### 2.2、线程状态 ###

*  1、sleep: 等待客户端发送请求
 *  2、query: 执行查询计划，或者将内容发送给客户端
 *  3、analyzing and statistics 线程正在收集统计信息
 *  4、copying to tmp table: 向临时表复制（group by, 排序，union吗，如果后面有on disk 那么说明mysql正在将一个内存临时表放在磁盘上。）
 *  5、sorting result：排序
 *  6、sending data: 发送数据，在数据量多的时候会特别耗时间。

### 2.3、查询缓存 ###

> 根据查询SQL的hash来找的缓存，如果命中了缓存，在返回之前会检查一下数据库的权限，如果权限正确就直接返回，将不会解析sql生成执行计划等。

#### 2.3.1、查询优化处理 ####

##### 2.3.1.1、语法解析器 #####

*  将sql语句解析，生成一个解析树，会校验语法验证和解析查询。会抛出语法错误

> MySQL怎么看懂和理解这些SQL?比如现在有个这样的SQL，我们人脑处理一下，只要懂SQL语法的人，立马就明白什么意思了。但是MySQL是一个数据库管理系统，他是没法直接理解这些SQL语句的！此时就有一个关键组件出场了：查询解析器。

查询解析器（Parser）是负责对SQL语句进行解析的，比如上面那个sql语句进行一下拆解，可以拆解为以下几部分：

*  1、要从『users』表里查询数据
 *  2、查询『id』字段等于1的那行数据
 *  3、对查询出来的那行数据要提取里面『id,name,age』三个字段

所谓的SQL解析，就是按照既定的sql语法，对我们按照sql语法规则编写的sql语句进行解析，然后理解这个sql语句要干什么事情，如下图所示：

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

##### 2.3.1.2、预处理器 #####

*  进一步验证解析树的合法性，比方说表上的字段不存在等等。

##### 2.3.1.3、查询优化器 #####

主要针对之前执行的语句，找出多种执行计划，在众多执行计划里面找出一个最优的执行计划  
优化器有自己的评估逻辑，主要就是计算大致的IO次数，需要注意的是即便是使用了缓存他依旧会计算其中的IO耗时。

这就导致了：

*  如果统计信息不准确会影响他的预估。（innodb的mvcc会影响行数的统计）
 *  像上面说的，执行计划的成本不一定是实际上的查询成本，即便是使用了缓存，也会计算IO时间。
 *  mysql的最优可能和你的不一样，简而言之，他选出来的不一定是最短的。
 *  mysql不考虑并发
 *  不考虑成本
 *  无法估算到所有的执行计划。

> 当通过解析器理解了SQL要干什么之后，并不是马上去执行，而是要通过查询优化器（Optimizer）来选择一个最优的查询路径。就以上面那个sql查询为例，看下所谓最优查询路径是什么。

要完成这个事，我们有以下几个查询路径：

*  查询路径1：直接定位到『users』表中的『ID』字段等于1的那一行数据，然后取那行数据的『id,name,age』三个字段值就行了
 *  查询路径2：先把『users』表中每一行数据的『id,name,age』三个字段的值都查出来，然后从这批数据里过了出『id』字段等于1的那行数据
 *  上面就是一个最简单的SQL语句的两种实现路径，要完成这个sql语句的目标，两个路径都可以做到，但哪一种更好呢？显然感觉上第一种查询路径更好一些。

所以查询优化器就是针对你编写的几十行、几百行甚至上千行的复杂sql语句生成查询路径树，然后从里面选择一条最优的查询路径出来。相当于他告诉sql执行器，按照一个什么样的步骤和顺序，去执行哪些操作，然后一步一步把SQL语句就完成了。

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

### 2.4、优化方式 ###

> **静态优化**：和查询上下文无关，比方说简单的代数替换。  
> **动态优化**：和查询上下文有关，比方说利用索引来确定最值。

**如下几种：**

*  1、重新定义关联表的顺序，关联并不是总是按照关联的顺序来执行的，比方说之前的order by的字段只有在是主表字段的时候才会 使用索引，而主表是优化器决定的，可能你写一个sql，有可能第一个并不是主表。
 *  2、外链接转化成内联接：在有些条件下，外联接经过过滤查询之后就等价是内联接。
 *  3、等价变换：对查询条件做一些简单的几何变换。
 *  4、优化count(),max(),min(): count(): myisam。max()/min() 利用索引的有序性
 *  5、预估并转化成常数表达式：min()对索引列执行的时候，可以转化成一个常数。，再就是类似于等值传播的优化。

mysql> explain select film.film_id, film_actor.actor_id from film inner join film_actor using(film_id) where film_id = 1;

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

*  6、覆盖索引扫描
 *  7、子查询优化：将自查询优化成效率更高的方式
 *  8、提前终止。（比方说，自增id的查询你在条件里面写个负数），比方说找没有演员的表.

mysql> explain select film.film_id from film inner join film_actor using(film_id) where film_actor.film_id is null;

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

*  9、等值传播：将一个列的过滤条件传播到另一个列

mysql> explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

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

*  10、in的优化，in不是直接使用等值查询，而是使用二分查找来优化查询效率。

### 2.5、统计信息 ###

*  存储引擎在查询时起到的第一个作用，提供统计信息给查询引擎，查询引擎能通过这些统计信息来去做决定使用什么查询计划。

### 2.6、关联查询 ###

**执行逻辑**：找出最外部的关联表的数据，然后使用这个数据便利去和内部查询的关联对比，如果成功就返回一条，如果内层处理完之后就找外层拿下一条接着处理。  
![在这里插入图片描述][ab4e542dcd12456fb19c2c1b46a29bf1.png]

### 2.7、查看执行计划 ###

mysql> explain extended select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

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

### 2.8、关联查询优化器 ###

决定关联表的顺序。通常多表关联的时候可以有不同的执行顺序来获取相同的结果，关联查询优化器通过评估不同的顺序时的成本来获取一个最快的。

以下两个例子可以展示关联优化器的作用：

![在这里插入图片描述][441585aa336a4cc2a24ccbf9bbb0844d.png]![在这里插入图片描述][64b83c168b6f4b468a0f19bfd25ab854.png]![在这里插入图片描述][f79cdad4bd474cfa8b08644dfdc1ae84.png]

> 使用优化器的时候只需扫描部分行就能完成工作。

不过关联多的时  
候会出现关联的组合种类太多了，mysql会使用贪心算法来解决这一问题。

### 2.9、排序优化 ###

> 尽量使用索引来排序，file\_sortings 性能差。

算法：

*  1、先排序要排序的字段，再根据字段去查数据（两次IO）
 *  2、查所有列，针对排序列排序，返回结果。

> 一些细节： 如果orderby的字段来自关联的第一个表，那么在第一个表查出来的时候就会排序，其他的都会搞到一个临时表里面再排序。  
> 如果排序加上limit，那么会先排序之后再计算limit，会先扫描大量的列再返回较少的列。

### 2.10、查询引擎 ###

> 一些共有的特性都是在服务端做

查询引擎拿到执行计划，完成整个查询。整个过程就是调用存储引擎的API完成的。

### 2.11、存储引擎 ###

> 数据库就是一个编程语言写出来的系统而已，然后启动之后也是个进程，执行池里面的各种代码，也就是我们上面所说的那些东西。所以对数据库而言，我们的数据要不然是放在内存里，要不然是放在磁盘文件里，没什么特殊的地方！

所以我们来思考一下，假设我们的数据有的存放在内存里，有的存放在磁盘文件里如下图所示。  
![在这里插入图片描述][e3d74ffb206d4322a2b348a645dfb11a.png]

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

那么问题来了，我们执行的时候是先更新内存数据？还是磁盘数据？我们如果先更新磁盘数据，是先查询哪个磁盘文件，再更新哪个磁盘文件？

这个时候就需要存储引擎了。存储引擎其实就是执行sql语句的，它会按照一定的步骤去查询内存缓存数据，更新磁盘数据，查询磁盘数据等等，执行一系列数据的操作，如下图所示：  
![在这里插入图片描述][e129f2d8420c47cb8410dc16b212eb8b.png]

MySQL的架构设计中，SQL接口、SQL解析器、查询优化器都是用的一套组件。但是存储引擎像插件一样，支持各种各样的存储引擎的，比如我们常见的InnoDB、MylSam、Momery等。我们可以自由选择哪种存储引擎来负责具体的SQL执行。不过现在大家一般都使用InnoDB作为MySQL的存储引擎。

### 2.12、 执行器 ###

> 那么看完存储引擎之后，我们知道存储引擎可以帮助我们去访问内存以及磁盘上的数据，那么是谁来调用存储引擎的接口呢？其实我们现在还漏了一个执行器的概念，这个执行器会根据优化器选择的执行方案，去调用存储引擎的接口按照一定的顺序和步骤，就把SQL语句的逻辑给执行了。

举个例子，比如执行器可能会先调用存储引擎的一个接口，去获取“usrs”表中的第一行数据，然后判断一下这个数据的"id”字段的值是否等于我们期望的一个值，如果不是的话，那就继续调用存储引擎的接口，去获取“users”表的下一行数据。  
就是基于上述的思路，执行器就会去根据我们的优化器生成的一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL语句的执行计划，大致就是不停的更新或者提取一些数据出来。如下图所示：

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

### 3.12、返回结果到客户端 ###

> 最后还是走TCP发送结果到客户端。

查询完毕之后会将查询发送到客户端，如果整个查询是可以被缓存的，那么就会缓存下来。

这里返回数据是逐步返回的，生成第一条结果的时候就能返回了。

**优点**: 服务器端无需存储太多的结果。也就不会因为要返回太多结果而消耗太多内存。

## 三、执行SQL的问题 ##

### 1.3.1、 问题1：MySQL谁去处理网络请求？ ###

> msyql服务器谁负责从这个连接中去监听这个网络请求？谁负责从网络连接里把数据读出来？

*  其实大家都知道，网络连接必须得分配一个线程去处理，由一个线程来监听和读取请求数据，比如从网络连接中读取和解析出来一条发过去的sql语句，如下图所示：

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

### 1.3.2、 问题2：MySQL如何执行sql语句？ ###

> 接着我们思考一下，当mysql内部的工作线程从一个网络连接中读取出来一个sql语句之后，此时会如何执行这个sql语句呢？  
> 如果要执行这个sql，去完成底层的数据增删改查，其实是一项极其复杂的任务了。Mysql内部首先提供一个组件，就是SQL接口（`SQL Interface`），他是一套执行sql语句的接口，专门用于执行我们发送给mysql的那些增删改查的sql语句。

因此mysql工作线程接收到SQL语句后，就会转交给SQL接口去执行，如下图：

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

[c65b27ad0193495dad1b36db6fc53284.png]: https://img-blog.csdnimg.cn/c65b27ad0193495dad1b36db6fc53284.png
[2dd2da4e539b4f088a7eab36fc1f6d71.png]: https://img-blog.csdnimg.cn/2dd2da4e539b4f088a7eab36fc1f6d71.png
[https_dev.mysql.com_doc_refman_8.0_en_packet-too-large.html]: https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html
[aa65788057bc452ebc7027192fecf922.png]: https://img-blog.csdnimg.cn/aa65788057bc452ebc7027192fecf922.png
[fb619dbb933b4e19a76a5e1e948ab84c.png]: https://img-blog.csdnimg.cn/fb619dbb933b4e19a76a5e1e948ab84c.png
[7cfaf268e17c494581123229d4199d80.png]: https://img-blog.csdnimg.cn/7cfaf268e17c494581123229d4199d80.png
[b0fce8f8f0104247a332df800efe72d8.png]: https://img-blog.csdnimg.cn/b0fce8f8f0104247a332df800efe72d8.png
[5c974c0bbbf34c60b03abfc53275f981.png]: https://img-blog.csdnimg.cn/5c974c0bbbf34c60b03abfc53275f981.png
[ab4e542dcd12456fb19c2c1b46a29bf1.png]: https://img-blog.csdnimg.cn/ab4e542dcd12456fb19c2c1b46a29bf1.png
[23702f319b714f48b8dad490bb47478e.png]: https://img-blog.csdnimg.cn/23702f319b714f48b8dad490bb47478e.png
[441585aa336a4cc2a24ccbf9bbb0844d.png]: https://img-blog.csdnimg.cn/441585aa336a4cc2a24ccbf9bbb0844d.png
[64b83c168b6f4b468a0f19bfd25ab854.png]: https://img-blog.csdnimg.cn/64b83c168b6f4b468a0f19bfd25ab854.png
[f79cdad4bd474cfa8b08644dfdc1ae84.png]: https://img-blog.csdnimg.cn/f79cdad4bd474cfa8b08644dfdc1ae84.png
[e3d74ffb206d4322a2b348a645dfb11a.png]: https://img-blog.csdnimg.cn/e3d74ffb206d4322a2b348a645dfb11a.png
[ba0bd6f5fe8c4815ab0a70e60ed4452f.png]: https://img-blog.csdnimg.cn/ba0bd6f5fe8c4815ab0a70e60ed4452f.png
[e129f2d8420c47cb8410dc16b212eb8b.png]: https://img-blog.csdnimg.cn/e129f2d8420c47cb8410dc16b212eb8b.png
[2f8c98cfb64249b995b84eab11aaf436.png]: https://img-blog.csdnimg.cn/2f8c98cfb64249b995b84eab11aaf436.png
[4a407d12544b4d14bdcca282e53a8ec1.png]: https://img-blog.csdnimg.cn/4a407d12544b4d14bdcca282e53a8ec1.png
[9cdc491c6d3b49ad85ea1e47c4d5a227.png]: https://img-blog.csdnimg.cn/9cdc491c6d3b49ad85ea1e47c4d5a227.png