SQL语句在MySQL中是如何执行的(一)

参考：

https://mp.weixin.qq.com/s?__biz=MzU3NDkwMjAyOQ==&mid=2247483894&idx=1&sn=43642fac89173db293d2a3739decb4ef&chksm=fd2a1b94ca5d92822ac4a8b97c4cc1323255f366a2f0114de7ee9ba1faa58ba2a85195dd126b&scene=178&cur_album_id=1552870102100983810#rd

https://juejin.cn/post/6844903811983622157

https://www.cnblogs.com/jasonbourne3/p/12530069.html

https://www.cnblogs.com/gusluo/p/11281687.html

https://www.cnblogs.com/gusluo/p/11250863.html

https://www.dazhuanlan.com/2019/10/23/5db04095c5885/

SQL语句在MySQL中是如何执行的

mysql> select * from T where ID=10;

开门见山，当我们输入一条 SQL 语句的时候，MySQL 内部究竟执行了什么？直接上架构图，我们才能对其有一个概念，而不要陷入细节之中。

架构

架构概况

大体上来说，MySQL 可以分为 Server 层与存储引擎两个部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器。
存储引擎负责数据的存储和读取，其架构模式是插件式的，支持支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL5.5.5 版本开始成为了默认存储引擎。

所以不难看出，主要差别在于 Server 层，也就是连接器到执行器部分。接下来我们来说明上文的 SQL 语句到底在内部经历了什么。

连接器

第一步，客户端会先连接到数据库，这个时候就是连接器来接待。它负责跟客户端建立连接、获取权限、维持和管理连接。

如果用户名密码正确，连接器就到权限表查询你所拥有的的权限之后这个连接里面的权限判断，都依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，被修改了权限，也不会影响已经存在的连接的权限。修改完成后，只有再重新建立的连接才会使用到新的权限设置。

建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

另外说到这里就顺便提一下，客户端连接mysql服务器时，如果连接一直处于空闲状态，那么到了一定的时候就会断开连接，多长时间是由 wait_timeout 控制的，其默认是8个小时。如果超过8个小时，你执行操作数据库时就回提示 “Lost connection to MySQL server during query”，这时只有重新连接数据库方能进行操作。

说到连接器，咱们得说一下长连接和短链接。长连接就是如果客户端一直都有请求操作数据库，那么就会一直使用这个连接进行操作。短链接就是每次执行完很少的数据库操作就断开连接了，如果再有请求就必须重新连接。

所以这里建议减少数据库的连接操作，尽量使用长连接。但是长时间使用长连接会导致一个问题，那就是mysql的占用的内存会越来越大，甚至到最后可能会出现OOM情况，导致mysql异常重启，那么这就尴尬了。

针对上面的情况有两种解决办法：

（1）、定期断开长连接，或者断开一些查询占用内存比较大的操作的连接，释放资源。

（2）、如果是5.7及以上版本，可以使用 mysql_reset_connection 来重置连接，但是需要注意以下几点

活跃事务会被回滚，自动提交模式也会被重置；
释放所有表锁；
关闭&删除所有临时表；
会话变量(选项)被重置成和全局变量一致；
用户级变量丢失；
PREPARE语句会被释放（其相应的HANDLER也会被关闭）；
LAST_INSERT_ID值重置为0；
利用GET_LOCK获取的锁会被释放。

以上是mysql官微给出的解释，所以重置连接的时候以上因素，以免对数据库中的数据产生影响。

查询缓存

连接建立好了以后，就可以执行 select 语句了，执行逻辑进入第二步：查询缓存。

MySQL 拿到一个查询语句，会先查询缓存，先校验这个语句是否执行过，以 key-value 的形式存在内存里， Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了，往往带来的效果并不好，对于不经常更新的数据来说，使用缓存还是可以的，Mysql 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器

如果没有命中缓存，那么就进入分析器，主要就是分析 SQL 语句是拿来干嘛，也就是解析该语句生成语法树，会分为两步：

第一步：词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
第二步：语法分析，主要就是判断你输入的 SQL 是否正确，是否符合 MySQL 的语法。，主要就是判断你输入的 SQL 是否正确，是否符合 MySQL 的语法。

优化器

经过了分析器分析，MySQL 知道你要干啥了，在开始执行之前，还要先经过优化器的处理。

优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

比如这条语句，既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种的执行逻辑结果是一样的，但是执行效率会有不同，而优化器就是决定使用哪种方案。

执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。这里的权限其实就是第一步客户端连接到连接器然后去查询出来的权限信息。

InnoDB 引擎更新数据的操作其实还会涉及到两个日志模块的操作，以后会专门分析。主要就是 binlog 以及 redolog 的操作。

InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态，更新完成。

总结

Mysql 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用。
引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
查询语句的执行流程如下：权限校验（如果命中缓存）---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎。
更新语句执行流程如下：分析器----》权限校验----》执行器---》引擎---redo log(prepare 状态---》binlog---》redo log(commit 状态) 。

参考

《MySQL 专栏 45 讲》

sql执行过程分析

本篇文章会分析一个 sql 语句在 MySQL 中的执行流程，包括 sql 的查询在 MySQL 内部会怎么流转，sql 语句的更新是怎么完成的。

在分析之前我会先带着你看看 MySQL 的基础架构，知道了 MySQL 由那些组件组成以及这些组件的作用是什么，可以帮助我们理解和解决这些问题。

MySQL 基础架构分析

1.1 MySQL 基本架构概览

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图

连接器：身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器：按照 MySQL 认为最优的方案去执行。
执行器: 执行语句，然后从存储引擎返回数据。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

1.2 Server 层基本组件介绍

连接器

连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存(MySQL 8.0 版本后移除)

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

二语句分析

2.1 查询语句

说了以上这么多，那么究竟一条 sql 语句是如何执行的呢？其实我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from tb_student A where A.age='18' and A.name=' 张三 ';

结合上面的说明，我们分析下这个语句的执行流程：

先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id='1'。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。

b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

2.2 更新语句

以上就是一条查询 sql 的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql 语句如下：

update tb_student A set A.age='19' where A.name=' 张三 ';

我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块式 binlog（归档日志），所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

先查询到张三这一条数据，如果有缓存，也是会用到缓存。
然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
更新完成。

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

判断 redo log 是否完整，如果判断是完整的，就立即提交。
如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

三总结

•MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。 •引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。 •SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎 •对于更新等语句执行流程如下：分析器----》权限校验----》执行器---》引擎---redo log prepare---》binlog---》redo log commit

作者：thekingisalwayslucky
链接：https://juejin.cn/post/6844903811983622157
来源：掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

update语句执行过程

　　以下面的一条sql为例（ID为主键）

update T set b=b+1 where ID = 2

客户端通过连接器与mysql建立连接　
删除要update表的缓存
分析器解析sql并判断是否含有语句错误
优化器确定查询索引
执行器调用Innodb存储引擎接口获取ID=2的数据行。
Innodb存储引擎扫描主键索引查找到ID=2的行返回给server层。
执行器将B+1 调用存储引擎接口写入该行数据。
Innodb存储引擎将数据保存在内存中(WAL)。
Innodb存储引擎写redo日志，prepare状态，通知Server层。
server层提交Binlog后通知Innodb存储引擎。
Innodb存储引擎将redo日志commit。

　　至此一条update语句就执行完成了。

　　redo日志：Innodb存储引擎特有的机制，可以用来应对异常恢复，Crash-safe，redo可以保证mysql异常重启时，将未提交的事务回滚，已提交的事务安全落库。

　　二阶段提交：redo（perpare）--->binlog-->redo(commit) 保证了mysql在异常重启的时候，数据的一致性，在任意环节出错，都可以保证redo日志和binlog的一致性。

　　WAL：第8个步骤，innodb没有直接将数据落盘，而是存在内存中，并记录日志，这里用到的技术就是WAL(Write-Ahead Logging)。数据在持久化硬盘前，如果mysql异常重启，innodb可以根据redo日志将未持久化的数据恢复。redo日志是有大小限制的，循环写，当redo快要写满时，将redo日志头部的记录清理，擦除记录前要把内存记录更新到数据文件。

　　binLog: binlog 记录的是逻辑日志，是mysql的归档日志，支持所有引擎使用。与redo不同的是，binlog是不限制大小，文件追加写。

这里将老师的图贴上（对应的5--11的步骤）

一条更新sql的执行过程

update scores set  score=c+10 where id=1

上面一条sql是将id为1的分数加上10。

那么它的执行流程是怎样的呢？借用上篇文章的图，如下：

我这边就再简单的说一下这个流程，首先客户端连接mysql服务器，连接后执行sql语句，执行sql的过程需要经过分析器得出它是需要做update操作，再接着经过优化器它决定使用id这个索引，然后经过执行器通过索引找到这一行，最后进行更新操作。

以上就是整个更新操作得整个流程。说到这你肯定以为说完了，不过很遗憾的告诉你，这才刚刚开始呢。

因为更新操作和查询操作不一样，更新操作涉及到两个非常重要的日志模块。redo log (重做日志) 和 bin log（归档日志）。这个两个才是今天要说的重点。

首先咱们得知道这两个日志是什么？然后再得知道它们是干什么的？

redo log 是 InnoDB 引擎特有，它是属于物理日志，主要用于记录 “某个数据页上做了什么修改” ，而且它的记录空间是固定的并且是会用完的。

bin log 是属于 server 层持有的，主要是再执行器中记录日志，所以mysql所有的引擎都可以使用它。bin log 是属于逻辑日志，它有 statement 和 row 两种模式，statement记录的是执行的sql语句，row记录的是更新行的内容，所以是记录两条，一条是更新前的内容，另外一条是更新后的内容。默认模式是 row 模式。另外 bin log 是会追加写入日志，当日志文件写到一定大小的时候，就会切换到下一个继续写入日志，并且不会覆盖之前的日志文件。

以上就是这两种日志的概念以及作用，那么现在我们说说它们的记录流程。咱们先看下面一张图，黄填充色的为执行器的操作，蓝填充色为InnoDB引擎的操作

图有点长，不过应该很容易看懂。那么现在就来一步一步的分析。

1、首先执行器会找引擎取id=1这条数据；

2、因为id是主键，所以使用树来找到一行数据。不过引擎先去内存中查找是否有这一页数据；

3、如果有则直接返回数据给执行器；如果没有就会去磁盘把数据读入到内存中，然后返回数据给执行器。

4、执行器就会执行C+10操作；

5、执行器生成新的一行数据；

6、再调用 InnoDB 引擎的写入接口，把数据更新到内存中；

7、InnoDB 引擎写入 redo log 日志，标记状态为 prepare，并且告诉执行器已经更新数据完成，可以随时提交事务；

8、执行器把此操作写入 bin log ，并且把 bin log 写入磁盘；

9、最后执行器调用引擎的提交事务接口，引擎把 redo log 的状态改 commit ，至此整个更新操作完成。

看到这里也许你会冒出几个问题？

1、redo log 空间是固定，那它会不会用完呢？

首先不用担心 redo log 会用完空间，因为它是循环利用的。例如 redo log 日志配置为一组4个文件，每个文件分别为1G。它写的流程如下图：

上图中有两块填充色，黄色和红色，黄色标记着 check point，这表示这当前擦除掉 redo log 的位置，红色标记着 write pos ，这表示着当前记录 redo log 的位置。当 redo log 写满了之后，就会停下来，不再写入数据，会执行擦除 redo log 操作，当然在擦除这些日志之前，都会把数据写入到磁盘中，把数据进行持久化。这样才能保住数据的准确性。

2、为什么要用这两种日志呢？因为在没有 InnoDB 引擎的时候是没有 redo log 日志的。

因为 InnoDB 引擎的 redo log 可以保证即使数据库突然宕机了或者异常重启了，之前提交的数据是不会丢失的。这个能力咱们称之为 crash-safe。

3、当mysql服务器在执行过程中突然间宕机了，数据会不会丢失？

答案是不会。为什么这么肯定呢？我们可以从第二张图就可以看出来。比如有以下几种情况：

1、写入 redo log 之前宕机了，那么原始数据是不会发送改变的，因为还没有进行事务的提交。

2、如果写入 redo log 之后，写入 bin log之前宕机，那么原始数据还是不会变，因为数据库重启后，因为两种日志的记录没有同步，所以不会有新数据生成。

3、在 redo log 生成commit之前宕机了，数据库重启后数据会变成更新后的数据，因为这个时候 redo log 和 bin log 都有了记录，所以数据库重启后会自己进行commit，所以这时候的数据就是更新后的数据了。

我们使用 redo log 主要是需要保证 crash-safe 能力，innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证MySQL异常重启之后数据不丢失。

sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

为什么mysql更新的效率很高？

WAL机制：即更新内存中数据页，同时写redolog+binglog然后返回。后台慢慢将数据页刷到磁盘中。（随机写改为顺序写）
mysql的chagen buffer。