一条更新SQL的内部执行

学习MySQL实战45讲,非常推荐学

还是老图:上文复习 在执行查询语句的时候,会执行连接器(总要连上才能搞事情),然后去查询缓存(MySQL8+删除了),有数据返回,没数据进行分析器-优化器-执行器-执行引擎流程并且其特点是如果该表上有更新,都会把缓存结果清空

MySQL整体来看可以分为两块:一块是Server层,它主要做的是MySQL功能层面的事情;还有一块是引擎层,负责存储相关的具体事宜;

 update T set c=c+1 where ID=2;

流程:

  1. 连接器:连接数据库
  2. 分析器:分析词法和语法解析知道这是更新语句
  3. 优化器:决定使用ID进行索引
  4. 执行器:具体执行,找到改行然后更新

更新对查询来说,涉及到了两个重要的日志模块,redo log(重做日志)InnoDB特有的,binlog(归档日志)Server中存在的,只要持续学习数据库方面,这两个是绕不过的。

redo log(重做日志)

在InnoDB中redo log的大小是固定的,可以配置为一组4个文件,每个文件的大小是1GB,那么这块redo log总共就可以记录4GB的操作(可以自己设置)。

MySQL中的问题:

如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程IO成本、查找成本都很高

解决问题:

WAL技术:Write-Ahead Logging

  1. 写日志
  2. 写磁盘

执行流程:

为什么redo log具有crash-safe的能力

为什么 redo log 具有 crash-safe 的能力,是 binlog 无法替代的?redo log的被动刷盘机制明确定义: write:刷盘 fsync:持久化到磁盘 write(刷盘)指的是MySQL从buffer pool中将内容写到系统的page cache中,并没有持久化到系统磁盘上。这个速度其实是很快的。 fsync指的是从系统的cache中将数据持久化到系统磁盘上。这个速度可以认为比较慢,而且也是IOPS升高的真正原因。

redo log是物理日志,记录的是“在某个数据页上做了什么修改”,内部结构是基于页的,记录了这个页的字段值变化,只要crash后读取redo log进行重放就可以恢复数据。(因为redo log是循环写的,如果满了InnoDB就会执行真正写盘)。 好处是不用每一次操作都实时把数据写盘,就算crash后也可以通过redo log重放恢复,所以能够实现快速响应SQL语句。

binlog(归档日志)

MySQL刚开始是使用的自带引擎MyISAM,但其没有crash-safe,binlog日志只能用于归档,5.5版本之后引入InnoDB,通过redo log来实现crash-safe能力。

redo log与binlog的区别

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

update内部执行流程:

流程图:图中浅色框表示是在 InnoDB 内部执行的,深色框表示是在执行器中执行的

其中redo log分为两个阶段提交:这是为了让两份日志之间的逻辑一致

  1. redo log prepare阶段
  2. commit阶段

好处:保证redo log和binlog都能同时失败或者成功。

论证两阶段提交的必要性:

如果不采用两阶段提交,那么redo log和binlog是单独的逻辑,由此引出下面两种提交方式:

  1. 先写redo log,后写binlog
  2. 先写binlog,后写redo log

还是以update为例:

update T set c=c+1 where ID=2;

先写redo log,后写binlog

在redo log写完,binlog没写完的时候,MySQL 进程异常重启,因为其实先写入内存的,所以MySQL崩溃后仍然能把数据恢复

根据备份恢复后c的值为1.但由于binlog没有写完就崩溃了,这时候binlog就没有记录这条语句的操作,使用binlog恢复的时候就会少一次更新,c的值为0,这就与原库的值不同了。

先写binlog,后写redo log与上面逻辑相同都会造成恢复的数据与原库值不同;

但是当按照上述流程图来说,在进行两阶段提交的时候,即A、B时刻出现crash,是怎么处理的。 流程如下: B时刻发生crash后对应的是2(a)的情况,崩溃恢复过程中事务会被提交。 由此引出的很多问题,感兴趣的朋友可以看下MySQL实战45讲答疑篇第一章备份的流程:

  1. 找到最近的一次全量备份,从这个备份恢复到临时库;
  2. 从备份的时间点开始,将备份的 binlog 依次取出来,重放到中午误删表之前的那个时刻。

备份恢复的数据的场景:

  1. 误操作后需要恢复数据
  2. 要扩容的时候,也需要再多搭建一些备库来增加系统的读能力的时候,用全量备份加上应用binlog 来实现的。如果这个“不一致”就会导致你的线上出现主从数据库不一致的情况
作者:|xbhog|,原文链接: https://www.cnblogs.com/xbhog/p/16295603.html

文章推荐

记一次 Oracle 下的 SQL 优化过程

这么分析大文件日志,以后就不用加班卷了!

Docker入门与实战-Docker镜像的使用

AutoGPT:有手就会的安装教程

最新版本 Stable Diffusion 开源 AI 绘画工具之图生图进阶篇

keepalived的简单使用

pytorch resnet50 模型转成 onnx 的代码样例,batch_size 是...

Docker原理

【记事本】一个SQL语句转化为Laravel migration 文件

使用containerd搭建MinIO集群服务

es的查询、排序查询、分页查询、布尔查询、查询结果过滤、高...

栈溢出基础