一条sql语句是怎样更新执行的?

430 阅读6分钟

update T set c=c+1 where ID=2; 这条sql在mysql当中是怎样执行的呢?

redo log

如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到 对应的那条记录,然后再更新,整个过程IO成本、查找成本都很高。mysql设计了redo log来处理这个问题。
当有一条记录需要更新的时候,InnoDB引擎就会先把记录写到redo log里面,并更新内存,这个时候更新就算完成了。同时,InnoDB引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做。
InnoDB的redo log是固定大小的,比如可以配置为一组4个文件,每个文件的大小是1GB,总共就可以记录4GB的操作。从头开始写,写到末尾就又回到开头循环写,如下面这个图所示。

image.png write pos是当前记录的位置,一边写一边后移,写到第3号文件末尾后就回到0号文件开头。 checkpoint是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。
write pos和checkpoint之间的还空着的部分,可以用来记录新的操作。如果write pos 追上checkpoint,这时候不能再执行新的更新,得停下来先擦掉一些记录,把 checkpoint推进一下。
有了redo log,InnoDB就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个 能力称为crash-safe。

binlog

MySQL整体来看,其实就有两块:一块是Server层,它主要做的是MySQL功能 层面的事情;还有一块是引擎层,负责存储相关的具体事宜。上面我们聊到的粉板redo log是 InnoDB引擎特有的日志,而Server层也有自己的日志,称为binlog(归档日志)。

为什么会有两份日志呢?

因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM,但是MyISAM没有 crash-safe的能力,binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL 的,既然只依靠binlog是没有crash-safe能力的,所以InnoDB使用另外一套日志系统— — 也就是 redo log来实现crash-safe能力。

为啥 Binlog 没有 crash-safe 功能? redo log 和 binlog 有一个很大的区别就是,一个是循环写,一个是追加写。也就是说 redo log 只会记录未刷盘的日志,已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志,保存的是全量日志。

当数据库 crash 后,想要恢复未刷盘但已经写入 redo log 和 binlog 的数据,binlog 是无法恢复的。虽然 binlog 拥有全量的日志,但没有一个标志让 innodb 判断哪些数据已经刷盘,哪些数据还没有

举个例子, binlog 记录了两条日志:

  1. 给 ID = 2 这一行的 c 字段加1
  2. 给 ID = 2 这一行的 c 字段加2

在记录1刷盘后,记录2未刷盘时,数据库 crash。重启后,只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘,哪条没有写入磁盘,不管是两条都恢复至内存,还是都不恢复,对 ID=2 这行数据来说,都不对

但redo log 不一样,只要刷入磁盘的数据,都会从 redo log 中抹掉,数据库重启后,直接把 redo log 中的数据都恢复至内存就可以了。这就是为什么 redo log 具有 crash-safe 的能力,而 binlog 不具备

保证 crash-safe 为啥要用两个日记,不能用一个日记吗(Redo log 或 Binglog)? 我们知道只有 binlog 日志,没有 redo log 是不能做到故障恢复的。那么针对只有 redo log日志,没有 binlog 日志,这也是不行的,因为 redo log 是 innodb 持有的,且日志上的记录落盘后会被抹掉。因此需要 binlog 和 redo log 两者同时记录,才能保证当数据库发生宕机重启时,数据不会丢失。

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

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

update的执行逻辑如下图 image.png 将redo log的写入拆成了两个步骤:prepare和 commit,这就是"两阶段提交" 为什么要用两阶段提交 仍然用前面的update语句来做例子。假设当前ID=2的行,字段c的值是0,再假设执行update语 句过程中在写完第一个日志后,第二个日志还没有写完期间发生了crash,会出现什么情况呢?

  1. 先写先 redo log 后写后 binlog。假设在redo log写完,binlog还没有写完的时候,MySQL进程异 常重启。由于我们前面说过的,redo log写完之后,系统即使崩溃,仍然能够把数据恢复回 来,所以恢复后这一行c的值是1。 但是由于binlog没写完就crash了,这时候binlog里面就没有记录这个语句。因此,之后备份 日志的时候,存起来的binlog里面就没有这条语句。 然后你会发现,如果需要用这个binlog来恢复临时库的话,由于这个语句的binlog丢失,这 个临时库就会少了这一次更新,恢复出来的这一行c的值就是0,与原库的值不同。
  2. 先写先 binlog b 后写后 redo log r 。如果在binlog写完之后crash,由于redo log还没写,崩溃恢复以后这个事务无效,所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日 志。所以,在之后用binlog来恢复的时候就多了一个事务出来,恢复出来的这一行c的值就是 1,与原库的值不同。