超详细解析：在执行一条SQL语句期间发生了什么？

前言

我们学习MySQL时，首先第一个接触到的就是SQL语句了，那么在我们运行一条SQL语句时，在MySQL中究竟发生了什么？MySQL是如何在那么多数据中准确的找出我们要操作的那一条语句并且执行我们需要做的操作的？
为了解开这个疑问，我们就从MySQL的内部“零件”开始看起吧~

MySQL的执行流程

MySQL内部的架构图：

在这里插入图片描述

MySQL的架构总共分为两层：Server层和存储引擎层

Server层负责建立连接，分析和执行SQL。MySQL大多数的核心功能模块都在Server层实现

Server层

存储引擎层负责数据的存储和提取，支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

连接器

使用MySQL的第一步一定是要先和MySQL服务建立连接，之后才能执行SQL语句。使用Windows系统连接MySQL数据库需要使用如下命令：

启动MySQL服务器：

net start mysql

打开数据库：

mysql -u root -p

然后输入用户名密码进行验证，如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断。

如果连接长时间没有数据，连接器就会自动断开，时间由参数wait_timeout控制，默认为八小时

查询缓存

在MySQL8.0以下版本存在查询缓存，当我们查询一条数据库时，会将我们的查询语句以及结果以key-value键值对的形式存储到查询缓存里，如果再次查询该条语句，将会把结果直接从查询缓存里返回。

这时有的小伙伴可能会产生一些疑惑了：这么说来，如果我们对数据库中的一些数据进行了修改，再对该数据进行查询，那我们查询到的数据岂不是在查询缓存之中拿到的之前查询到的值吗？这样还如何能确保我们拿到的数据是最新的呢？别急，查询缓存的运行机制是当有任何一条sql将表中的任一字段进行修改，那么将会把这之前的查询缓存全部清空，再重新存储。

啊这…费劲巴拉的把结果都存储起来了，结果一个更新过来，直接全部缓存清空，之前的努力全部白废掉了…鸡肋！非常的鸡肋！

因此在8.0及以后版本就删除掉了这个功能。。。

词法分析器

如果没有命中缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。

分析器首先会做“词法分析”，我们输入的SQL语句是由多个字符串和空格组成的，MySQL 会根据我们输入的字符串识别出关键字出来。

接下来要做的是语法分析。根据词法分析的结果，语法分析器会根据语法规则，判断我们输入的这个SQL语句是否满足MySQL语法。如果语句不对，就会返回一条“You hava an error in your SQLsyntax”的错误。

如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

在这里插入图片描述

预处理

在预处理阶段，会检查我们SQL语句中所涉及到的表或字段是否存在，并且将select * 中的 * 符号扩展为表上的所有的列

优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引

执行器

通过执行器将最终的sql语句放到存储引擎层。

执行器会根据表的存储引擎，使用这个存储引擎提供的接口；在执行SQL语句之前执行器会先进行权限检查，确保当前用户有足够的权限执行该操作。

那么在引擎层具体会发生什么呢？我们以Innodb为例来解析一下。

引擎层

我们先看一个详细流程图。

在这里插入图片描述

具体流程

数据库的增删改查都是直接操作Buffer Pool，Buffer Pool一般设置为机器内存的60%~70%左右。首先在Buffer Pool中查询是否存在该条数据，如果不存在，则会从磁盘文件（ibd）中将该条数据加载到缓存池中来。
之后就是将要修改的数据的旧值放入到undolog（回滚日志文件）中，这里主要是将历史数据进行一个记录，以便于回滚。
将旧数据进行一个存储后，就可以对缓存中的数据进行修改了，之后再将更新完的数据写入redolog中，redolog将顺序写入磁盘文件中去。
将准备提交的数据写入binlog日志一份，binlog主要用来恢复数据库磁盘里的数据
在redolog的该条数据里写入一个commit标记，写入标记后会向客户端返回事务提交成功。
在系统空闲时，以page为单位随机写入磁盘

如果事务提交成功，buffer Pool里的数据还没来得及写入磁盘，此时系统宕机了，可以用redo日志里的数据恢复磁盘ibd文件里的数据

为什么需要redolog

redolog是Innodb特有的，在上面的流程中可以看出，redolog和binlog做的事情是基本相似的，那已经存在了binlog，为什么innodb又设计了redolog呢？

redolog的组成

redolog由两部分组成，一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。

redolog如何提高性能？

我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题：

一方面，由于Inoodb是以页为单位进行磁盘交互的，而一个事务可能只修改一页数据中的某几个字节，如果每修改一次就将整个数据页刷新到磁盘一遍的话，也太浪费资源了

另一方面，idb数据文件是随机读写，不同的数据表有不一样的ibd文件，修改不同的表的话就要修改不同表的ibd文件，不能实现顺序写文件的效果，耗时非常长。而redo是一个或几个预先分配好磁盘空间的文件，写入永远都是在文件末尾追加，具体来说就是只记录事务对数据页做了哪些修改，相对而言文件更小，也可以很好的解决性能问题。

redo log与binlog区别

redo log 主要用于保证事务的持久性（Durability）。当事务提交时，redo log 会记录事务对数据库的修改操作，以保证即使在数据库崩溃的情况下，这些修改也能够被恢复。

binlog 用于记录数据库的所有更改操作，包括对数据的增删改操作以及对数据库结构的修改；与 redo log 不同，binlog 的写入是按照事务的提交顺序进行的，而不是按照数据修改的顺序。

总结

看了这么多了，那我们来总结一下，在执行一条SQL语句期间发生了什么？

连接器：建立连接，校验用户身份；
查询缓存：直接将SQL语句与查询缓存中的所有键值对进行比对，如果命中缓存则直接返回结果，如果没有则继续向下执行，在查询到结果时存储到查询缓存中一份；MySQL8.0及以后版本废除；
解析SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树；
执行SQL：先经过预处理和优化，再通过执行器调用引擎接口
对数据库进行处理，返回结果。

如有其他见解，欢迎各位大佬留言讨论~~