「MySQL进阶」一条SQL语句怎么执行的？

「MySQL进阶」一条SQL语句怎么执行的？

参考与感谢

【重温msql】2、一条 sql 在 MySQL 中是如何执行的？

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

《mysql专栏45讲》

MySQL 5.6参考手册

一、概述

我们的程序通过连接池向 MySQL 发送了一条sql语句，MySQL 就按照要求给我们返回了正确的结果，有时我们不免好奇，这个过程中究竟发生了什么？它是如何工作的？有什么方法可以加速我们的查询？需要解答这些疑问，首先我们需要对 MySQL 的架构体系有所了解。 下图为 MySQL Server 的体系结构：

MySQL的层次

MySQL Server 在逻辑上一共分为三层：连接层、解析层、存储引擎层。各个层次负责不同的分工：

• 连接层，主要包含连接池，负责与程序的连接、授权认证、缓存等等；
• 解析层，主要包括了 Sql Interface、解析器、优化器以及缓存，主要负责解析用户传递的sql，并对 sql 的检索过程进行优化形成执行方案。
• 存储引擎层，主要包括 MySQL 的各种引擎以及所产生的各种数据、Schema、索引以及日志文件存储，存储引擎层提供了相关的数据查询检索接口与上层进行交互，依据上层生成的执行方案查找、存储数据。

---

二、SQL执行过程

那么 sql 从外部连接器发送给 MySQL Server 后，具体的执行过程是怎么样的呢？

• 1、连接池鉴权，判定当前用户是否有当前操作的权限
• 2、如若为查询操作则看缓存是否有数据，缓存命中则直接返回
• 3、传递给解析器进行sql词法分析
• 4、优化器依据词法分析结果，决定后续处理方式，生成执行计划
• 5、依据上面生成的执行计划，调用存储引擎 api
• 6、如若为删改数据或该表等操作，如若开启 binlog 则在事务最终提交前写入 binlog 日志
• 7、返回相关处理结果

---

三、架构分析

连接器

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

查询缓存

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

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

分析器

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

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

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

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

优化器

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

执行器

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

---

四、语句分析

我们的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岁的学生，然后再查询姓名为“张三”的学生。

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

更新语句

以上就是一条查询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的模式了，MyISAM引擎是没有redo log的，那么我们知道它是不支持事务的，所以并不是说只用一个日志模块不可以，只是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），这个日志模块所有执行引擎都可以共用。
• 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory等。
• 查询语句的执行流程如下：权限校验（如果命中缓存）—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
• 更新语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log(prepare 状态—》binlog—》redo log(commit状态)