MYSQL执行一条SELECT语句的具体流程

妖狐艹你老母 2023-10-13 10:38 31阅读 0赞

昨天CSDN突然抽风 我一个ctrl+z把整篇文章给撤掉了还不能复原 直接心态崩了不想写了 不过这部分果然还是很重要,还是写出来吧

## 流程图 ##

## ![3c14f6e2a220435c911c5296f503b2cf.png][] ##

这里面总共有两层结构Server层 储存引擎

*  **Server 层负责建立连接、分析和执行 SQL**。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
 *  **存储引擎层负责数据的存储和提取**。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

> ## 第一步：连接器 ##
> 
> 就是用户和MYSQL相连接的过程,会发生三次握手和四次挥手,因为连接是基于TCP协议进行传输的
> 
>     # -h 指定 MySQL 服务得 IP 地址，如果是连接本地的 MySQL服务，可以不用这个参数；
>     # -u 指定用户名，管理员角色名为 root；
>     # -p 指定密码，如果命令行中不填写密码（为了密码安全，建议不要在命令行写密码），就需要在交互对话里面输入密码
>     mysql -h$ip -u$user -p
> 
> 如果 MySQL 服务正常运行，完成 TCP 连接的建立后，连接器就要开始验证你的用户名和密码，如果用户名或密码不对，就收到一个"Access denied for user"的错误，然后客户端程序结束执行。
> 
> 如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断。
> 
> 所以，如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
> 
> 可以通过
> 
>     SHOW PROCESSLIST;
> 
> 查看有多少用户连接了MYSQL
> 
> ![f1a5ffff3e4848d387cf74200c7f937f.png][]
> 
> 比如上图的显示结果，共有两个用户名为 root 的用户连接了 MySQL 服务，其中 id 为 6 的用户的 Command 列的状态为 `Sleep` ，这意味着该用户连接完 MySQL 服务就没有再执行过任何命令，也就是说这是一个空闲的连接，并且空闲的时长是 736 秒（ Time 列）。
> 
> ### 空闲连接会一直占用着吗？ ###
> 
> 当然不是了，MySQL 定义了空闲连接的最大空闲时长，由 `wait_timeout` 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。
> 
>     mysql> show variables like 'wait_timeout';
>     +---------------+-------+
>     | Variable_name | Value |
>     +---------------+-------+
>     | wait_timeout  | 28800 |
>     +---------------+-------+
>     1 row in set (0.00 sec)
> 
> 当然，我们自己也可以手动断开空闲的连接，使用的是 kill connection + id 的命令。
> 
> ### 长链接和短连接的区别 ###
> 
>     短连接
>     连接 mysql 服务（TCP 三次握手）
>     执行sql
>     断开 mysql 服务（TCP 四次挥手）
>     
>     // 长连接
>     连接 mysql 服务（TCP 三次握手）
>     执行sql
>     执行sql
>     执行sql
>     ....
>     断开 mysql 服务（TCP 四次挥手）
> 
> 所以就是短连接只有一条SQL语句 长连接有多条SQL语句
> 
> 同样一个任务我们使用长连接要比使用短连接节省了多次连接和断开MYSQL的过程
> 
> 但是长连接就一点缺点没有吗?非也
> 
> MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。
> 
> 那我们如何解决这种问题吗
> 
> 断开连接不就完事了嘛
> 
> 1.定期手动断开连接
> 
> 2.**客户端主动重置连接**。MySQL 5.7 版本实现了 `mysql_reset_connection()` 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql\_reset\_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
> 
> 每次学XX底层原理的时候都感觉,底层运行在替我负重前行

> ## 第二步:查询缓存 ##
> 
> 连接工作结束之后,客户端就可以向MYSQL发送SQL语句了 它首先会解析语句的第一个字段 查看这是一条什么语句 假如说发送的是一个SELECT语句 那么它就会去去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。
> 
> 如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。
> 
> 这么一看 这太牛了 这个查询缓存 这不就像dp数组一样可以大量优化运行了吗
> 
> 其实不然 因为每次对这张表更新数据的时候 这张表的缓存就会被全部清除....啊 也就是说 如果是更新后的数据查不到 只能是未更新的查询过一次的数据
> 
> ?那我查它干啥 我直接把上一次的查询结果拿出来用多好
> 
> 很好 MYSQL也是这么想的 所以MYSQL8.0直接把这个东西给删了

> ## 第三步:解析SQL ##
> 
> 第一件事:词法分析,MYSQL会分析你这个SQL语句中的关键子 然后形成SQL语法树 这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。 (给MYSQL来一把长难句分析)
> 
> 第二件事:句法分析 就是看有没有**语法错误**
> 
> 这里加粗了语法错误 字段,表,数据不存在那不是语法错误哦
> 
> 就像JVM中的元数据验证 字节码验证一样

## 第四步：执行 SQL ##

![397fbf4c1d924718b6899267b920f449.png][]

对应图中的四块

*  prepare 阶段，也就是预处理阶段；
 *  optimize 阶段，也就是优化阶段；
 *  execute 阶段，也就是执行阶段；

> ### 预处理阶段 ###
> 
> 那肯定在预处理器中,于SELECT而言 预处理就做俩事
> 
> 1.查看字段,表等数据是否存在
> 
> 2.把\* 替换成全部列

> ### 优化阶段 ###
> 
> 对应的当然是优化器
> 
> 我们知道MYSQL执行语句的时候贼麻烦 又要判断这个索引又判断那个长度的 就是这一步 要确定一个执行的具体方案
> 
> 我们可以在查询语句最前面加个 `explain` 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引 若果这个key参数是null的话 就说明没有使用索引 也就是说 使用的是效率最差的全表查询
> 
> 具体来讲 优化器会选择什么方案呢?
> 
>     SELECT id FROM product WHERE id > 1  AND name LIKE 'i%';
> 
> 这条查询语句的结果既可以使用主键索引，也可以使用普通索引，但是执行的效率会不同。这时，就需要优化器来决定使用哪个索引了。
> 
> 这里使用的是覆盖索引,也就是说 同时使用这两个索引 这两个**索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。(**查询主键索引的 B+ 树的成本会比查询二级索引的 B+ 的成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引。**)**
> 
> 就是个啥流程呢 首先先在二级索引(name)中 找到匹配的数据 然后二级索引存的数据就是主键id 然后记录id>1的数据 所以就不用再去主键索引了 直接就出结果了

> ## 执行阶段 ##
> 
> 执行阶段 那肯定是执行器
> 
> 经历过优化器了 我们已经得到了执行方案 那现在就是正式的执行SQL的时机了
> 
> 执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。
> 
> 拢共有三种执行方式:
> 
>  *  主键索引查询
>  *  全表扫描
>  *  索引下推
> 
> ### 主键索引查询 ###
> 
> 从语文的角度分析 他肯定是通过主键索引查询的对吧(?)
> 
> 我们看这行代码
> 
>     select * from product where id = 1;
> 
> 首先id为主键 肯定为1 再有这个筛选条件是等值 所以一定只会查询一次
> 
> 执行器第一次查询 调用read\_first\_record 函数指针指向的函数
> 
> 这个函数指针被指向为 InnoDB 引擎索引查询的接口(优化器参数为const 有这个参数 储存引擎才能选择对应的执行方式)，把条件 `id = 1` 交给存储引擎，**让存储引擎定位符合条件的第一条记录**。然后呢储存引擎通过主键索引(b+树)找到id为1的数据
> 
> 判断1:如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
> 
> 然后如果存在的话 判断2
> 
> 查看是否符合筛选条件 如果符合发送给客户端，如果不符合则跳过该记录。
> 
> 执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以这次会调用 read\_record 函数指针指向的函数(区别于上面的read\_first\_record)，因为优化器选择的访问类型为 const(前面提到的这个参数再次出现)，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了
> 
> ### 全表扫描 ###
> 
>     select * from product where name = 'iphone';
> 
> 第一步不变 还是调用 read\_first\_record 函数指针指向的函数 但是这次因为是全表扫描(优化器参数为all)
> 
> 第二步 从第一条记录开始读取,看它是否满足WHERE条件(name = iPhone) 如果不满足跳过,满足把这条记录发给客户端
> 
> 执行器查询的过程是一个 while 循环，所以还会再查一次，会调用 read\_record 函数指针指向的函数，因为优化器选择的访问类型为 all，read\_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层） 返回了读取完毕的信息； 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。
> 
> ### 索引下推 ###
> 
> 它是MySQL 5.6 推出的查询优化策略
> 
>     select * from t_user  where age > 20 and reward = 100000
> 
> 假如说我们创建了 对于age和reward的索引
> 
> 联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 **age 字段能用到联合索引，但是 reward 字段则无法利用到索引(硬记就行 后面可能会更新索引失效的部分)**
> 
> 如果不使用索引下推
> 
> 第一步还是: server层调用储存引擎 找到满足age>20的第一条记录
> 
> 第二步:存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层
> 
> 第三步:
> 
> server再判断reward是否等于100000 如果符合 返回给用户 不符合忽略
> 
> 第四步:接着拿下一条记录(不需要再次定位记录了 记录之间由链表连接) 存储引擎在二级索引定位到记录后，获取主键值，然后回表操作拿到全部记录数据 判断是否符合
> 
> 然后三四步循环 直到age>20的记录查询完毕
> 
> 没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，接着 Server 再判断该记录的 reward 是否等于 100000
> 
> 那么有索引下推的时候呢?
> 
> Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
> 
> 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
> 
> Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立(为什么只有reward条件可以用储存引擎判断呢? 因为它虽然没用上联合索引 但是索引的数据还是有的 在联合索引中对着id找reward还是很方便的(一对一对应的))，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。 如此往复，直到存储引擎把表中的所有记录读完。
> 
> 就相当于把判断reward是否等于100000这件事外包给储存引擎了
> 
> 索引下推能够减少**二级索引**在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了

[3c14f6e2a220435c911c5296f503b2cf.png]: https://img-blog.csdnimg.cn/3c14f6e2a220435c911c5296f503b2cf.png
[f1a5ffff3e4848d387cf74200c7f937f.png]: https://img-blog.csdnimg.cn/f1a5ffff3e4848d387cf74200c7f937f.png
[397fbf4c1d924718b6899267b920f449.png]: https://img-blog.csdnimg.cn/397fbf4c1d924718b6899267b920f449.png