一条SQL语句在MySQL中执行过程全解析

转自:https://blog.csdn.net/weter_drop/article/details/93386581

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

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

一 MySQL 基础架构分析

1.1 MySQL 基本架构概览

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

先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图，在 1.2 节中会详细介绍到这些组件的作用。

• 连接器： 身份认证和权限相关(登录 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 层基本组件介绍

1) 连接器

连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

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

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

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

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

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

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

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

3) 分析器

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

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

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

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

4) 优化器

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

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

5) 执行器

当选择了执行方案后，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

————————————————
版权声明：本文为CSDN博主「ratelFu」的原创文章，遵循CC 4.0 by-sa版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weter_drop/article/details/93386581

 

MySQL中一条SQL语句的执行过程

查询语句的执行顺序：

１.客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配（max_connections，8小时超时）

２.建立连接后客户端发送一条语句，mysql收到该语句后，通过命令分发器判断其是否是一条select语句，如果是，在开启查询缓存的情况下，先在查询缓存中查找该SQL是否完全匹配，如果完全匹配，验证当前用户是否具备查询权限，如果权限验证通过，直接返回结果集给客户端，该查询也就完成了。如果不匹配继续向下执行。（注意：此步并不做词法及语法分析，也就是用不到分析器，这块原来我也很疑惑，如果不做分析mysql怎么知道我要查什么？解释如下：{MySQL将缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了以下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能影响返回结果的信息。  当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。} 其实说白了大概就是拿着你的SQL和原始缓存的SQL比对）

3.如果在查询缓存中未匹配成功，则将语句交给分析器作语法分析，MySQL需要知道到底要查哪些东西，如果语法不对，就会返回语法错误中断查询。

4.分析器的工作完成后，将语句传递给预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义等

5.语句解析完成后，MySQL就知道要查什么了，之后会将语句传递给优化器进行优化（通过索引选择最快的查找方式），并生成执行计划。

6.之后交给执行器去具体执行该语句，在执行之前，会先检查该用户是否具有查询权限，如果有，继续执行该语句。执行器开始执行后，会逐渐将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。（缓存到查询缓存受到几个参数的影响 1.query_cache_type 是否打开查询缓存,默认为OFF  2.query_cache_size:查询缓存使用的总内存空间,默认值为1M   3.query_cache_limit 对于大于该值的结果集不会被缓存，默认值1M，在8.0版本后该参数被移除了）（如果该SQL执行过程中超过了慢查询阀值，该SQL会被记录到慢查询日志中） 

#######################################################

一条更新语句的执行顺序：

１.客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配（max_connections，8小时超时）

 2.建立连接后客户端发送一条语句，mysql收到该语句后，通过命令分发器判断其是否是一条更新语句，如果是，则直接发送给分析器做语法分析。

 3.分析器阶段，MySQL需要知道到底要查哪些东西，如果语法不对，就会返回语法错误中断查询

 4.分析器的工作完成后，将语句传递给预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义等

 5.语句解析完成后，MySQL就知道要查什么了，之后会将语句传递给优化器进行优化（通过索引选择最快的查找方式），并生成执行计划。

 6.执行器根据生成的执行计划去open table，此时会先去查看该表上是否有元数据（MDL）排他锁（如果有元数据共享锁则无影响），如果有元数据排他锁，则事物被阻塞，进入等待状态（时间由lock_wait_timeout决定，默认是一年。。。。），等元数据锁被释放，继续执行。如果无元数据锁或者是有元数据共享锁，则该事务在表上加元数据共享锁（因为元数据共享读锁之间是不冲突的，如果表上有元数据共享锁，我们执行alter table这样的DDL语句时，会进入等待状态，因为DDL语句需要在表上加元数据排他锁）

 7.进入引擎层（默认innodb），去innodb_buffer_pool里面的data dictionary得到表得相关信息

 8.根据表信息去innodb_buffer_pool里面的lock info查看是否有相关的锁信息，如果有则等待（因为要加排它锁），如果没有则加排它锁，更新lock info。

 9.取读取相关数据页到innodb_buffer_pool中（如果数据页本身就在缓存中，则不用从硬盘读取）

10.将页中的原始数据（快照）保存到undo log buffer中（undo log buffer会以相关参数定义的规则进行刷盘操作写入到undo tablespace中）

11.在innodb_buffer_pool中将相关页面更新，该页变成脏页（脏页会以相关参数定义的规则进行刷盘操作写入所属表空间中）

12.页面修改完成后，会把修改后的物理页面保存到redo log buffer中，（redo log buffer会以相关参数定义的规则进行刷盘操作写入到redo tablespace中）

13.如果开启binlog，则更新数据的逻辑语句也会记录在binlog_cache中（binlog会以相关参数定义的规则进行刷盘操作写入到binlog file 中）

14.如果该表上有二级索引并且本次操作会影响到二级索引，则会把相关的二级索引修改写入到innodb_buffer_pool中的change buffer里（change buffer 会以相关参数定义的规则进行刷盘操作写入所属表空间中）

15.前期的准备工作到此已经做完了，之后便是事务的commit或者rollback操作。一般情况下执行的是commit操作

16.执行commit操作后（mysql默认开启自动提交，如果手动开始事务begin，则需要显示提交commit），由于要保证redolog与binlog的一致性，redolog采用2阶段提交方式。

17.将undo log buffer及redo log buffer刷盘（innodb_flush_log_at_trx_commit=1），并将该事务的redolog标记为prepare状态。

18.将binlog_cache数据刷盘（sync_binlog=1）

19.如果开启了主从结构，此时会将binlog_cache中的信息通过io线程发送给从机，如果开启了半同步复制则需要等待从机落盘（relay log）并反馈。如果是异步复制则无需等待(默认是异步复制)

20.待binlog落盘完成，再将redolog中该事务信息标记为commit，释放相关锁资源。此时一个更新事务的操作已经完成，返回给客户端成功更新提示。

21.标记undolog中该事务修改页的原始快照信息为delete，当无其他事务引用该原始数据时(MVCC)，再将其删除

22.如果此时触发了脏页刷盘操作，会先将脏页写入到double write buffer中（防止写入过程中出现断页，因为mysql页面默认为16K，linux操作系统最大为4K，如果写了8K时系统挂了，这个数据页将不完整，标记为损坏）然后再写到期所在表空间的相应位置。
————————————————
版权声明：本文为CSDN博主「一梦如是YFL」的原创文章，遵循CC 4.0 by-sa版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/finalkof1983/article/details/84450896