面试官的灵魂一击：你懂 MySQL 事务日志吗？

写作背景

大家都清楚，日志是 MySQL 数据库的重要组成部分，记录着数据库运行期间各种状态信息。MySQL 日志主要包括「错误日志」、「查询日志」、「慢查询日志」、「二进制日志(binlog)」 和 事务日志(redo log、undo log)几大类。

其中，「二进制日记」和「事务日记」尤为重要，一直被人重视、深入研究；可是事实很残忍，重视或者说大多数人一般都是了解个表面，真正懂得人并不多。真想攻破这两块日记必须下血本，而且还不一定能攻破。但是不要紧，为了让你们省下血本还能顺利攻破这两块日记，我连续研究几周 MySQL日记，最终肝出了这篇文章。

必要概念字典介绍

基础不牢地动山摇，还是常规套路，先把必要知识普及/温习一遍，当后续文章出现疑虑反过来看下这些概念字典，说不定能柳暗花明又一村呢？

写了又写，想了又想，纠结了好久，这部分知识确实有点多，最后还是决定将这些必要概念字典单独分出一个文章，后续打算用截图方式引入各个章节中，建议遇到不懂名词查阅一下字典。

image

认识二进制日记（Binlog）

Binlog 概念

Binlog 是逻辑日记，用于记录数据库执行的写入操作（查询不记录）信息，Server 层记录和引擎层无关，并且是以追加方式进行写入，可以通过参数 max_binlog_size 设置每个 Binlog 文件的大小，文件大小达到设定值时会生成新的文件来保存日记。

image
image

Binlog 作用

在实际应用中，主要用在两个场景：主从复制和数据恢复

  • 主从复制场景：在 Master 主端开启 Binlog，将 Binlog 发生到各个 Slave 从端，Slave 从端重放 Binlog 从而达到主从数据一致
  • 数据恢复场景：通过使用 mysqlbinlog 工具来恢复数据

Binlog 记录过程及刷盘时机

Binlog 大致记录过程是先写 Binlog Buffer，然后通过刷盘时机，控制刷入 OS Buffer，控制 fsync() 进行写入 Binlog File 日记磁盘的过程。

image

对于 Binlog，MySQL 是通过参数 sync_binlog 参数来控制刷盘时机，取值是 0、1 和 N 三种值。0 表示由系统自行判断何时调用 sync() 写入磁盘；1 表示每次事务 commit 都要调用 fsync() 写入磁盘；N 表示每 N 个事务，才会调用 fsync() 写入磁盘。

image

Binlog 记录格式

MySQL 5.7.7 版本之前默认格式是 STATEMENT，版本之后默认是 ROW，可以通过参数 binlog-format 指定。

认识事务日记（Undo log）

Undo log 概念

Undo log是 逻辑日记 、回滚日记。比如一条修改 +3 的逻辑语句，Undo log 会记录对应一条 -3 的逻辑日记，一条插入语句则会记录一条删除语句，这样发生错误时，根据执行 Undo log 就可以回滚到事务之前的数据状态。

Undo log 作用

  • 回滚数据：当程序发生异常错误时等，根据执行 Undo log 就可以回滚到事务之前的数据状态，保证原子性，要么成功要么失败。
  • MVCC 一致性视图：通过 Undo log 找到对应的数据版本号，是保证 MVCC 视图的一致性的必要条件。

Undo log 记录过程及刷盘时机

刷盘过程及时机类似于 Binlog 和 Redo，可以参考 Redo log 刷盘时机章节给出的图片，已经体现出来了。

Undo log 总结

Undo log 日记内容不是很多，重点是回滚和多版本控制 MVCC那块。此外，我记得印象笔记深刻的是长事务会导致日记过多，这个日记就是 Undo log。因为长事务存在，导致需要保存很多视图快照，其实这里就是涉及到Undo log 何时删除和生成的问题，当时纠结好久，其实很简单。生成是事务开始后写 Redo log 之前生成，当没有事务需要用到 Undo log 时就会被删除。举个例子，如果事务 A 一直存活，那么事务 A 之后产生的事务 B、C...等等就算提交了，也不会被删除，因为事务 A 需要用到 B、C... 事务去找 A 的版本。所以避免长事务可以减少Undo log 日记量，当然还可以提高性能。

认识事务日记 （Redo log）

Redo log 概念

Redo log 是重做日记，属于InnoDB引擎的日记。是物理日记，日记记录的内容的是数据页的更改，这个页 “做了什么改动”。如：add xx记录 to Page1，向数据页Page1增加一个记录。

image

Redo log 作用

  • 前滚操作：具备 crash-safe 能力，提供断电重启时解决事务丢失数据问题。
  • 提高性能：先写Redo log记录更新。当等到有空闲线程、内存不足、Redo log满了时刷脏。写 Redo log 是顺序写入，刷脏是随机写，节省的是随机写磁盘的 IO 消耗（转成顺序写），所以性能得到提升。此技术称为WAL技术：Write-Ahead Logging，它的关键点就是先写日记磁盘，再写数据磁盘。

Redo log 两阶段提交

更新内存后引擎层写 Redo log 将状态改成 prepare 为预提交第一阶段，Server 层写 Binlog，将状态改成 commit为提交第二阶段。两阶段提交可以确保 Binlog 和 Redo log 数据一致性。

Redo log 容灾恢复过程

MySQL的处理过程如下

  • 判断 redo log 是否完整，如果判断是完整（commit）的，直接用 Redo log 恢复
  • 如果 redo log 只是预提交 prepare 但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 Redo log，用 Redo log 恢复，不完整就回滚事务，丢弃数据。

只有在 redo log 状态为 prepare 时，才会去检查 binlog 是否存在，否则只校验 redo log 是否是 commit 就可以啦。怎么检查 binlog：一个完整事务 binlog 结尾有固定的格式。

Redo log 刷盘时机

Undo log 的刷盘时机和 Redo log 差不多，但是对于 Undo log 我没找到对应的刷盘参数设计，所以不在提。Redo log 每次先写入 Redo Log Buffer 中，然后通过刷盘时机控制刷入 OS Buffer 时间和刷入日记磁盘的时间。

image

在 Undo Log 中，MySQL 是通过参数innodb_flush_log_at_trx_commit来控制刷盘时机，取值是 0、1 和 2三种值。0 表示事务提交后，每秒写入 OS Buffer 并调用 fsync() 写入日记磁盘中；1 表示每次事务提交会写入OS Buffer 并调用 fsync() 将日记写入日记磁盘中。2 表示事务每次提交写入到 OS Buffer，每秒调用 fsync() 写入日记磁盘。可见参数为 1 是最安全的，同时也是默认值。

image

Redo log 存储方式

image

Redo Log 检查点

啥时候会触发检查点 checkpoint

image

「Checkpoint 发生的时间、条件及脏页的选择等都非常复杂。而 Checkpoint 所做的事情无外乎是将缓冲池中的脏页刷回到磁盘，不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发 Checkpoint。这些本文不会去研究」。

Redo Log LSN

LSN 这个概念，比较复杂。LSN 称为日志的逻辑序列号(log sequence number)，在 innodb 存储引擎中，lsn占用 8 个字节。LSN 的值会随着日志的写入而逐渐增大。「可以简单理解SLN就是记录从开始到现在已经产生了多少字节的Redo log值」。

存储方式两个指针又是通过 LSN 计算得到指向位置，因为 LSN 记录的是文件的大小字节，当超过文件大小时，需要用取模计算出这两个指针位置，取模使得写入就会从头开始写，这样使得两个指针在一个文件中，一直落在循环位置，你追我赶的过程。这就是 Redo log 环形逻辑思想设计实现。

上面提到LSN比较复杂，是因为它有很多个值，输入命令 show engine innodb status; ，可以看到四个的 lsn 记录

image

为了方便识别，我都为它们重新命名，如下所示。名词记不住，后面无法继续深入

  • 内存日记：redo log buffer lsn；磁盘日记：redo log file lsn；

一般关系为：redo log buffer lsn >= redo log file lsn，如果刷盘时机为1，则redo log buffer lsn = redo log file lsn。

  • 内存数据页：data buffer lsn；数据磁盘数据页：data disk lsn；

一般关系为data buffer lsn > data disk lsn，如果已经刷入数据磁盘，则data buffer lsn = data disk lsn。

  • 检查点：chckpoint lsn；

后面提到检查点刷盘，数据刷盘和日记刷盘（如果有日记刷盘：则说明我假设的日记刷盘的时机设置值不为1，为1 是同步的，即始终 redo log buffer lsn = redo log file lsn，不会由检查点触发刷日记磁盘）。

都说 Redo log 是环形记录，那么怎么记录的？下面结合 LSN 给出记录过程虚构图，可以对比 Redo log 存储方式图

❝

相关知识：日记磁盘 + redo log file lsn + checkpoint lsn + 双指针（write pos、check point）

❞

1-8 按时间顺序发生。1 点是假设最初的状态；2、3 点写日记磁盘；4 点是触发了检查点 checkpoint，进行刷盘，「checkpoint lsn=1开始」，刷盘结束并更新「checkpoint lsn=512」。在 5 点、6 点已经刷过了一循环内存、二循环内存，「从头开始写入 log，两个指针指向回到了头部」。第 7 点也是一个触发 checkpoint 的过程。9 点是假设没有更新，最后达到平衡的结果，即内存中数据页和日记都完成了刷盘。

image

整个流程：

在某些情况下，触发 checkpoint，触发数据页和日志页刷盘，此时将内存中的脏数据---数据脏页和日志脏数据"分别刷到数据磁盘和日记磁盘中，而且两者刷盘速度不一样。checkpoint 会保护机制，当数据刷盘速度超过日志刷盘时，将会暂时停止数据刷盘，等待日志刷盘进度超过数据刷盘。

刷盘时，对于数据磁盘，全部都是在内存中，此时每次刷一个数据页到内存更新数据页也更新了「data disk lsn」为 「data buffer lsn」（在更新内存数据页时，会更新data buffer lsn）「。」

对于日记磁盘，除了要记录 checkpoint lsn 的值为检查点 checkpoint的值（必须在结束时直接记录一个值，速度很快），这里是针对日记刷盘时机不是1（1是同步缓存刷日记刷盘）时，并且日记还没刷到日记磁盘需要触发将缓存中日记提前刷到日记磁盘中，此时会将redo buffer log 刷到 redo log file 中也更新了 redo log file lsn为redo log buffer lsn 。

模拟检查点触发前后，整个流程变化，一个数据页和日记，「数据变化及lsn从179-180的变化图（刷盘时机不为1）」

image

Redo log 容灾恢复过程与 LSN

Redo log 容灾恢复过程和 LSN 的知识，再次细化 Redo log 恢复过程

重启 innodb 时，Redo log 完不完整，采用 Redo log 相关知识。用 Redo log 恢复，启动数据库时，InnoDB 会扫描数据磁盘的数据页 data disk lsn 和日志磁盘中的 checkpoint lsn。两者相等则从 checkpoint lsn 点开始恢复，恢复过程是利用 redo log 到 buffer pool，直到 checkpoint lsn 等于 redo log file lsn，则恢复完成。

如果 checkpoint lsn 小于 data disk lsn，说明在检查点触发后还没结束刷盘时数据库宕机了。因为 checkpoint lsn 最新值是在数据刷盘结束后才记录的，检查点之后有一部分数据已经刷入数据磁盘，这个时候数据磁盘已经写入部分的部分恢复将不会重做，直接跳到没有恢复的 lsn 值开始恢复。

了解 ChangeBuffer

为啥提到 ChangeBuffer

为啥本文我会提到 ChangeBuffer 呢，其实很多时候会将 ChangeBuffer 和 Redo log 搞混，两者都是巧用内存，减少磁盘 IO，为了不弄混我觉得有必要专门对这个进行一个讲解。

ChangeBuffer 概念及作用

下面是我对 ChangeBuffer 的简单介绍

image

也就是说对于更新的操作，如果用到了 ChangeBuffer，更新的数据所在的数据页如果不在内存中，将不用去数据磁盘将数据页读到内存，而是将这一次操作记录在 ChangeBuffer 中，「ChangeBuffer 主要节省的则是随机读磁盘的 IO 消耗」，下次读取查询等读取数据页时用上 ChangeBuffer 中的记录即可。其实也是一种巧用内存的思想。

ChangeBuffer 与 Redo log 区别

Redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 ChangeBuffer 主要节省的则是随机读磁盘的 IO 消耗

这句话怎么理解，看下面：

Redo log 与 ChangeBuffer (含磁盘持久化) 这 2 个机制，不同之处在于优化了整个变更流程的不同阶段。

先不考虑 Redo log、ChangeBuffer 机制，简化抽象一个更新 (insert、update、delete) 流程：

  • 从磁盘读取待变更的行所在的数据页，读入内存页中

  • 对内存页中的行，执行变更操作

  • 将变更后的数据页，写入至数据磁盘中

其中，流程中的步骤 1 涉及随机读磁盘 IO；步骤 3 涉及随机写磁盘 IO；刚好对应 ChangeBuffer 和 Redo log。

对那句话的理解答案：

  • ChangeBuffer 机制，优化了步骤 1——避免了随机读磁盘 IO ，将不在内存中的数据页的操作写入ChangeBuffer 中，而不是将数据页从磁盘读入内存页中
  • Redo log 机制， 优化了步骤 3——避免了随机写磁盘 IO，将随机写磁盘，优化为了顺序写磁盘(写 Redo log，确保 crash-safe)

有没有用到 ChangeBuffer 对于 Redo log 的区别

Redo log 机制，为了保证 crash-safe，一直都会用到。有无用到 ChangeBuffer 机制，对于 redo log 这步的区别在于—— 用到了 ChangeBuffer 机制时，在 Redo log 中记录的本次变更，是记录 new change buffer item 相关的信息，而不是直接的记录物理页的变更。在我们 mysql innodb 中， ChangeBuffer 机制不是一直会被应用到，仅当待操作的数据页当前不在内存中，需要先读磁盘加载数据页时，ChangeBuffer 才有用武之地。

ChangeBuffer的 merge 过程

image

除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

merge 过程做三步

  • 从磁盘读入数据页到内存（老版本的数据页）；

  • 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；

  • 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

日记大连贯U-R-B，一举攻破拿下

前面分别讲的是 Binlog、Undo log 和 Redo log，下面将他们都串联起来，在一些流程体现全部日记。

同样，以一些最经典的更新语句例子展开说明。

制造演示数据

测试语句：插入语句+查询语句，a字段是普通索引

1、insert into ta(a,b) values(2,5),(7, 5)
2、select * from t where a in (2, 7)

假设原来的数据如下图，数据页 page1 在内存中，page2 不在。插入的数据 (2，5) 落在 page1，数据 (7,5) 落在page2 中。
image.png

假设没有日记和 ChangeBuffer 示范

先不考虑所有日记及 ChangeBuffer 机制，简化抽象一个更新 insert 流程

  • 从磁盘读取待变更的行所在的数据页，读入内存页中

  • 对内存页中的行，执行变更操作

  • 将变更后的数据页，写入至数据磁盘中

image

考虑所有日记和 ChangeBuffer 示范--现有 Innodb 流程

过程是 两阶段提交-----日记刷盘------数据刷盘（涉及 Redo log lsn 和 ChangeBuffer 的内容）

两阶段提交过程

  • 数据 (2,5) 所在页 page 1 在内存中直接更新内存；数据 (7,5) 所在页 page 2 不在内存中，记录 change buffer（具有唯一性的索引或者没有使用 change buffer 的操作是将磁盘中的数据页读入内存中并做更新）。

  • 写 undo 日记。「先写缓存，后面根据刷盘参数决定何时刷入磁盘，后面的 redo/Binlog 都一样」。日记刷盘 在每一个日记中基本已经提到，它和设置的参数有关，下文不会再展开介绍。

  • 写 redo 日记（先记在内存中的更新，然后记不在内存中的 change buffer 的改变）

  • 日记状态改成 prepare 阶段。

  • 写 Binlog日记。

  • 提交事务，日记状态改成 commit 阶段。

image

merge 过程

紧接着上文，图片可上下参考，假设现在执行查询语句 select * from t where a in (2, 7) ，此次查询索引 a=7 所在的数据页不在内存中，并且上一步更新已经在 change buffer 中有记录，将会触发 merge 过程

  • 将 page 2 读入内存

  • 依次应用 change buffer 中的记录，得到最新版数据页

  • 写入 redo，之前记录的 changebuffer 改动，现在改成数据页的改动

至于 changebuffer 被应用后是删除还是标记，还有 redo 中原有的记录 changebuffer 的改动怎么调整是删除还是修改成数据页的改动这里下面的图是按照自己的想法描述出来，如有误望留言指正。

image

数据刷盘过程

数据刷盘 flush 的有四种情况

  • InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写

  • 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是 脏页，就要先将脏页写到磁盘

  • MySQL 认为系统空闲的时候

  • MySQL 正常关闭的情况

数据刷盘也代表着 Redo log 检查点 checkpoint 触发，较为复杂。

假设数据刷盘 flush 的四种情况发生了一种，那么联系上文的过程将如下

  • 将脏页从内存中刷回到数据磁盘

  • 刷完后更新检查点 checkpoint 的值

image

流程中间某个环节数据库宕机后，恢复具体过程，这些留在心里了，没往上去写，读者可以自行思考，不难。

结尾

整个文章讲了 Binlog、Undo log 和 Redo log，随带一提 ChangeBuffer，对此，讲到这里，基本上要把我要将的已经讲完，内容挺多，有耐心可以慢慢啃，不懂欢迎留言！

思考环节，下面留下两个问题，欢迎大家留言解答

1、为啥 Binlog 没有 crash-safe 功能？

2、保证 crash-safe 为啥要用两个日记，不能用一个日记吗（Redo log 或 Binglog）?

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,053评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,527评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,779评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,685评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,699评论 5赞 366
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,609评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,989评论 3赞 396
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,654评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,890评论 1赞 298
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,634评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,716评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,394评论 4赞 319
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,976评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,950评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,191评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 44,849评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,458评论 2赞 342