上篇文章通过介绍一条 SQL 查询语句的执行过程引出了 MySQL 的几个基本处理模块:**Server层**的 连接器分析器优化器执行器以及存储引擎


学了一遍之后的总结:本文通过一条 Update 语句,引出了 MySQL 中两个最重要的日志系统:

  • InnoDB 引擎独有的物理日志 —— redo log
  • MySQL Server 层共有的逻辑日志 —— binlog

重点内容都是围绕着这两块进行展开和深入。


本篇文章介绍一条更新语句执行流程:下面是一条创建表的语句

mysql> create table T(ID int primary key, c int);

下面是一条更新语句,将 ID 为2的行 c的值加1:

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

MySQL 可以恢复到半个月内任意一秒的状态,也跟更新语句和下面要讲的日志分不开关系。

回顾第一章的 SQL 语句基本执行链路

查询语句的流程更新也是要走一遍的:

  • 首先,执行语句前连接数据库 —— 连接器。
  • 之前也提到过,更新一个表会导致跟这个表相关的查询缓存失效,所以这条更新语句会将表 T 上的所有缓存结果清空。
  • 分析器通过词法和语法解析知道这是一条更新语句。优化器决定使用 ID 这个索引,执行器负责具体执行,找到这一行,然后更新。

查询语句和更新语句的不同点更新流程涉及到两个重要的日志模块

  • redo log(重做日志)
  • binlog(归档日志)

这两个模块非常重要,并且在设计上有很多有意思的地方,学习的同时可以将设计思路用到自己的程序中。

重要的日志模块:redo log【引擎层日志】

这里作者举了一个例子:孔乙己中酒店老板记账:

酒店中一般有两种方法来记录赊账:

  • 粉板:用来快速记录赊账信息,优点是速度快,缺点是地方有限,写多了就写不下了。
  • 账本:存储空间大,可记录的条目多,但是每次记账需要找到对应的赊账人,速度慢效率低。

在酒店生活红火人多的时候,掌柜的一定会使用粉板来记录赊账信息,因为查账本更新赊账信息实在太麻烦了,如果账本很大,那么查询效率会非常低,而将赊账记录先写在粉板上,等人少之后再写入账本是一个更合理的选择。

MySQL 中也存在着类似的问题:如果每次更新都需写入磁盘,磁盘需要找到对应的记录,然后执行更新操作,那么大量的更新操作会造成 高昂的 I/O 和查找成本。

为了解决这个问题, MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率:

这就是 MySQL 中经常说到的 WAL技术【我还是第一次听说】,WAL 全称:Write-Ahead Logging,它的**关键点:**在于:先写日志,再写磁盘。 也就是先将要做的操作记录到粉板上,等不忙的时候再写入账本中。

具体来说:当有一条记录需要备更新时,InnoDB 引擎会将记录写到 redo log(粉板)里,并更新内存,这个时候更新操作就算完成了。同时 InnoDB 引擎会在适当时将操作记录更新到磁盘中,这个更新往往在系统比较空闲的时候做。【这个空闲程度是怎么判断的?】

但是还存在一个问题:当粉板写满之后,怎样处理?

  • 这时只能停下手中的活,将粉板中的一部分赊账记录更新到账本中,然后把这些记录从粉板上擦掉,为新的记录腾出空间。

类似的,InnoDBredo log 大小固定的,比如可以配置为一组 4 个文件,每个文件大小 1GB,那么这块"粉板"可以记录 4GB 的操作。其操作是从头开始写,写到末尾又回到开始循环写如下图所示:


image-20200920141132972

wirte pos当前记录的位置一边写一边后移,写到 3号 文件末尾后就回到了 0号文件开头。

checkpoint当前要擦除的位置,也是后移并且循环的,擦除记录前要把记录更新到数据文件

write poscheckpoint 之间的是"粉板"上空闲的区域,可以用来记录更新操作。如果 write pos 追上了 checkpoint 则代表"粉板"满了,这时不再执行新的更新,而是停下来擦掉一些记录,推进 checkpoint

有了 redo logInnoDB 可以保证即使数据库异常重启,之前的提交记录不会丢失,这个能力被称为 crash-safe【因为之前的提交记录在内存和磁盘中】

理解 crash-safe 可以类比上面的酒店,哪怕酒店关门了几天,但是通过账本和粉板上的记录依然可以明确赊账数据。

重要的日志模块: binlog【Server 层日志】

MySQL 从大的层面看就分为两块:Server层引擎层。上面提到的 redo log 是引擎层面的日志,这里的 binlog 则是 Server层的日志。

binlog 的定义: 归档日志,属于 Server 层的日志,其作用是归档。

问题来了,为什么引擎层有日志,Server层还要设计一份日志?

最开始MySQL 并没有 InnoDB引擎,当时自带的是 MyISAM 引擎,但是 MyISAM 没有 crash-safe 能力,binlog 日志只能用于归档InnoDB 是另一个公司以插件形式引入 MySQL 。只依靠 binlog没有 crash-safe 能力的,所以 InnoDB 使用另一套日志系统: redo log 实现了 crash-safe

下面是 redo logbinlog 之间的三点不同:

区别日志层级不同日志类型日志行为不同
redo logInnoDB 特有的日志系统redo log物理日志,记录的是"在某个数据上做了什么修改"redo log 循环写,空间固定会用完
binlogServer层实现的日志,所有引擎都可以使用binlog逻辑日志,记录的是语句的原始逻辑:"给 ID=2 这一行的 c 字段加1"binlog 可以追加写入:当 binlog 文件写到一定大小后会切换到下一个,不会覆盖之前的日志。

上面铺垫了 Server层和引擎层的两种日志,下面介绍 InnoDB 引擎执行这个简单 update 语句时的内部流程:

  1. 执行器引擎获取 ID=2 这一行。 ID 是主键,引擎直接使用树搜索找到这一行。 如果 ID=2 这一行所在的数据页本来就在内存中,直接返回给执行器,否则需要从磁盘读入内存,然后返回。
  2. 执行器拿到引擎给的行数据, 把 c的值加1【具体的更新操作】, 得到新的一行数据,再调用引擎写入这行新数据。
  3. 引擎将这行新数据更新到内存,同时将更新操作记录redo log,此时 redo log 处于 prepare 状态。然后引擎告知执行器更新操作执行完成,随时可以提交事务
  4. 执行器生成这个操作的 binlog,将 binlog 写入磁盘
  5. 执行器调用引擎的提交事务接口,引擎将刚才写入的 redo log 改成**提交(commit)**状态,更新操作完成。

下面是 update 的流程图,浅色代表在 InnoDB 内部执行,深色代表在执行器中执行:

最后三步:写入redlog —— 写入binlog ——提交事务 看上去有点绕,将 redo log写入拆成了两个步骤preparecommit,这就是「两阶段提交」。

两阶段提交

两阶段提交的作用:让两份日志之间的逻辑一致,文章开头说了,MySQL 可以让数据库恢复到版个月内任意一秒的状态,这就和两阶段提交有关。

binlog记录所有的逻辑操作,并且采用追加写的形式,如果 DBA 承诺可以恢复到半个月内任意状态,则说明备份系统保存了最近半个月的所有 binlog,同时系统定期做整库备份。

这里的定期取决于系统的重要性,可以一天一备,也可以一周一备

下面是恢复 MySQL 到任意一秒的操作流程,比如某天下午两点发现中午十二点有一次误删表,需要找回数据:

  • 首先,找到最近的一次全量备份,运气好的话是昨天晚上的备份,从这个备份恢复到临时库。
  • 从备份时间点开始,将备份的 binlog 依次取出,重放到中午误删表之前的那个时刻。

此时临时库就和误删之前的线上库一致了,然后可以将表从临时库中取出,按需恢复到线上。

上面是数据恢复过程,下面是为什么日志需要量阶段提交的论述:

这里作者使用了**反证法redo logbinlog两个独立的逻辑**,如果不使用两阶段提交,那么就有两种情况

  • 先写完 redo log 再写 binlog 假设在 redo log 写完,binlog 还没写完的时候,MySQL 进程发生异常重启了,之前说过 redo log 写完之后,即使系统崩溃,仍然可以将数据恢复,所以恢复后这一行 c 的值是 1。但是 binlog 此时还没有写完,系统就 crash了,这时候 binlog 并没有记录这条语句,因此之后备份日志的时候, binlog 中并没有保存这条更新语句,后面如果使用 binlog 恢复临时库就会丢失语句,恢复出来的 c 的值是0,与原库值不相符。
  • 先写完 binlog 再写 redo log binlog 写完后发生 crashredo log 还没写完,崩溃恢复以后这个**事务无效,所以这一行的 c 的值是0。但是 binlog 已经记录了 「把 c 从0 改到1 」 这条日志,之后如果用 binlog 进行恢复的话就会多出一个日志**,恢复出来的 c 值是1,与原库不同。

综上,如果不使用两阶段提交,数据库的状态就可能与恢复出的数据库状态不一致

并且这个不一致并不仅在恢复数据库这个场景,当数据库需要「扩容」时,常见的做法也是**全量备份加上应用 binlog 实现 备库的搭建**,上面的不一致就会导致主从数据库的不一致

redo logbinlog 都可以用于表示事务的提交状态,两阶段提交让这两个状态的逻辑保持一致 【两次提交,要么全部成功,要么全部失败】

小结

MySQL 中两个最重要的日志:

  • 物理日志 redo log ——> 引擎层:保证了 MySQL crash-safe 的能力。
  • 逻辑日志 binlog ——> Server层

redo log 保证了 crash-safe 能力。 innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘,这个参数作者建议设置为1,这样可以保证 MySQL 异常重启之后数据不丢失

sync_binlog 这个参数设置为 1 的时候,表示每次事务的 binlog 都持久化到磁盘,也建议设置为 1保证 MySQL 异常重启后 binlog 不丢失

两阶段提交的设计保证了 redo logbinlog 之间的状态一致,两阶段提交是跨系统维持数据逻辑一致性时常有的方案,这个方案不仅在数据库中应用,也可以迁移到日常开发的系统设计中。

课后思考

定期全量备份的周期取决于系统的重要性,有的是一天一备份,有的一周一备,什么场景下,一天一备比一周一备更有优势,它影响了这个数据库系统的哪个指标?

个人思考:

越重要的系统备份时间片越小,这样可以恢复的时间点选项越多。如果一周一备,假设每周一备份,而系统在周日崩溃了,这样就只能恢复到上周一的数据,而丢失了本周的数据,如果是一天一备就可以恢复到昨天的数据。

精选留言

关于思考题:

备份是后悔药加救命药,灾难发生的时候备份能救命,出现错误的时候备份能后悔。事情都有两面性,没有绝对的好和坏,只有在合适的情况选用合适的策略,看需求和业务而定。

一天一备的恢复时间段, binlog 更少,恢复起来更快,但是能恢复到的时间点也更短,只局限在昨天。

一周一备则相反,恢复需要的时间长,但是可以回退的时间点更久。

这里留言的作者提了一个兼得的策略设置一个16小时延迟复制的从库,充当后悔药,可以快速回滚恢复。再每两天设置一个全备库和 binlog 作为救命药,最后时刻兼顾

问答:

Q:是否可以认为 redo log 记录的是行在这个页更新之后的状态, binlog 记录的是 sql?

作者回复:

redo log 不是记录数据页更新之后的状态,而是记录这个页做了什么改动

binlog 有两种模式:

  • statment 格式记录的是 sql 语句
  • row 格式记录行的内容,记两条 —— 更新前和更新后的行内容都有

个人总结:

本篇文章介绍了 MySQL两个最重要的日志redo logbinlong

  • redo log
    • 定位:属于特定存储引擎的日志 —— InnoDB,所有如果没有使用 InnoDB 引擎是没有这个日志的。
    • 出现背景:为了提高效率,MySQL 出现了 Write-Ahead Logging 技术,并不是将每次更新操作都实时写入磁盘,而是先写进日志(redo log) 再写入磁盘
    • 作用:保证数据库发生异常重启的情况时,之前提交的记录也不会丢失 —— crash-safe
    • 行为redo log 是一组文件形成一个环状,循环写
    • 性质redolog 是物理日志,记录了"在某个数据页上进行了什么操作",是引擎独有的。
  • binlong
    • 定位:属于 MySQL Server 层的日志,无论使用什么存储引擎,都会有 binlog 的存在,记录了所有的 DDLDML语句,以事件形式记录,并且包含语句执行消耗了的时间。
    • 作用:主要目的是复制恢复
    • 行为:可以追加写入,写完之后切换到下一个文件,而不会像redo log 循环写入那样覆盖之前的文件。
    • 性质逻辑日志,引擎通用。

既然可能存在两份日志系统,就要保证这两个日志系统之间数据的统一,所以MySQL 的日志是两阶段提交的:

  • 先写入 redo log 此时 事务处于 prepare 阶段。
  • 后写入 binlog。
  • 提交事务,此时处于 commit 阶段。

这保证了对于 redologbinlog 的日志提交要么一起成功要么一起失败,不会有一条日志只存在于一个日志系统中,导致了数据的不一致。

binlog 有几个模式,一般使用 row模式,因为遇到时间和从库可能会出现不一致的情况, 但是row模式记录了更新前后的内容,可以进行对比,缺点是会导致日志变大。

两个重要的参数保证了日志的持久化:

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议

最是人间留不住,曾是惊鸿照影来。