【重点】MySQL执行一条sql语句的流程

【重点】MySQL执行一条sql语句的流程

在这里插入图片描述

mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。

这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中

redo log实际上记录数据页的变更，而这种变更记录是没必要全部保存

MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

  • 连接器

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

  • 查询缓存

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

    在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句
    就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。

  • 分析器

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

    第一步，词法分析，一条SQL语句有多个字符串组成，首先要提取关键字，比如select，提出查询的表，提出字段名，提出查询条件等。

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

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

  • 优化器

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

  • 执行器

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

重点：一条sql语句是如何执行的？其实sql语句可以分两种情况进行分析，一是查询语句，二是更新语句。

（一）查询语句

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

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

  • 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在MySQL 8.0版本以前，会先查询缓存，以这条SQL语句为key在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

  • 通过分析器进行词法分析，提取SQL语句的关键元素，比如提取上面这个语句是查询select，提取需要查询的表名为tb_student,需要查询所有的列，查询条件是这个表的id=‘1’。然后判断这个SQL语句是否有语法错误，比如关键词是否正确等，如果检查没问题就执行下一步。

  • 接下来就是优化器进行确定执行方案，上面的SQL语句，可以有两种执行方案：

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

  • 那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

  • 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

（二）更新语句（增加，更新，删除）

先走查询的流程，更新则需要记录日志（此时有日志模块binlog(归档日志)，InnoDB自带了一个日志模块redo log(重做日志)）。

更新时，写入数据时引擎把数据保存在内存中，并记录redo log，此时redo log进入准备状态，告诉执行器可以随时提交。执行器收到通知后记录binlog，调用引擎，提交redo log提交状态，则提交后更新完成。

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 为提交状态。
  • 更新完成。

update语句更新流程
在这里插入图片描述

一些问题

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

答案：这就是之前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 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致

那么问题来了，有没有一个极端的情况呢？假设redo log 处于预提交状态，binlog也已经写完了，这个时候发生了异常重启会怎么样呢？

这个就要依赖于MySQL的处理机制了，MySQL的处理过程如下：

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

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

其中》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》

重要的日志模块：redo log

WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉
板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，
将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件
的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就
又回到开头循环写
在这里插入图片描述
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件
开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录
更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如
果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下
来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢
失，这个能力称为crash-safe。

重要的日志模块：binlog

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为binlog（归档日志）。
为什么会有两份日志呢？
MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

这两种日志有以下三点不同。

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都
    可以使用。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日
    志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指
    binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

参考：一条sql执行流程

posted @ 2021-05-16 22:30  your_棒棒糖  阅读(249)  评论(0)  编辑  收藏  举报