几年了，作为一个码农终于把MySQL日记看懂了！

一、写作背景

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

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

二、文章指引

文章指导：文章第三节内容切莫跳过，但如果觉得第四、第五、第六和第七节没意思或者已经有了概念，直接进入文章第八节一举攻破拿下。

文章方向：理论、原理篇。

三、必要概念字典介绍

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

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

四、认识二进制日记（Binlog）

4.1 Binlog概念

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

image image

4.2 Binlog 作用

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

• 主从复制场景：在Master主端开启Binlog，将Binlog发生到各个Slave从端，Slave从端重放Binlog从而达到主从数据一致

• 数据恢复场景：通过使用 mysqlbinlog 工具来恢复数据

4.3 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

4.4 Binlog 记录格式

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

image

五、认识事务日记（Undo log）

5.1 Undo log 概念

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

5.2 Undo log 作用

• 回滚数据：当程序发生异常错误时等，根据执行Undo log就可以回滚到事务之前的数据状态，保证原子性，要么成功要么失败。

• MVCC一致性视图：通过Undo log找到对应的数据版本号，是保证MVCC视图的一致性的必要条件。

5.3 Undo log 记录过程及刷盘时机

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

5.4 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）

6.1 Redo log 概念

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

image

6.2 Redo log 作用

• 前滚操作：具备crash-safe能力，提供断电重启时解决事务丢失数据问题。

• 提高性能：先写Redo log记录更新。当等到有空闲线程、内存不足、Redo log满了时 “刷脏”。写Redo log是顺序写入，刷脏是随机写，节省的是随机写磁盘的 IO 消耗（转成顺序写），所以性能得到提升。此技术称为WAL技术：Write-Ahead Logging，它的关键点就是先写日记磁盘，再写数据磁盘。

6.3 Redo log 的两阶段提交

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

6.4 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结尾有固定的格式。

6.5 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

6.6 Redo log 存储方式

image

上图是日记磁盘的Redo log环形设计图（从头写，写到结束又从头开始写~循环）。write pos和check point是两个指针，write pos指针指向当前日记文件写入的位置，check point指针指向当前要擦除的开始位置。图中绿色部分是可以写入Redo log地方，每次写入，write pos指针会顺时针推进，当然基本不会与check point指针重合，因为MySQL有这种机制去实现，每次触发检查点checkpoint，check point会指针向前推进，这个过程就是需要进行刷日记和数据磁盘，记录相应的LSN，引出难点LSN。

6.7 Redo Log 检查点

啥时候会触发检查点checkpoint，网上找了点资料：啥时候数据库会触发检查点checkpoint

image

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

6.8 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

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

1. 内存日记：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。

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

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

1. 检查点：chckpoint lsn；

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

都说Redo log是环形记录，那么怎么记录的？下面结合LSN给出记录过程虚构图，可以对比6.6 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

6.9 Redo log 容灾恢复过程与LSN

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

重启innodb时，Redo log完不完整，采用6.4知识过程。用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

7.1 为啥提到ChangeBuffer

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

7.2 ChangeBuffer概念及作用

下面是我对ChangeBuffer的简单介绍

image

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

7.3 ChangeBuffer与Redo log区别

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

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

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

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

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

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

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

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

对那句话的理解答案：

• ChangeBuffer机制，优化了步骤1——避免了随机读磁盘IO ，将不在内存中的数据页的操作写入ChangeBuffer中，而不是将数据页从磁盘读入内存页中

• Redo log机制， 优化了步骤3——避免了随机写磁盘IO，将随机写磁盘，优化为了顺序写磁盘(写Redo log，确保crash-safe)

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

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

7.5 ChangeBuffer的merge过程

image

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

merge过程做三步

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

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

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

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

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

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

8.1 制造演示数据

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

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

8.2 假设没有日记和ChangeBuffer 示范

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

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

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

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

image

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

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

8.3.1 两阶段提交过程

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

2. 写undo日记。先写缓存，后面根据刷盘参数决定何时刷入磁盘，后面的redo/Binlog都一样。日记刷盘 在每一个日记中基本已经提到，它和设置的参数有关，具体刷盘可以参考上文4.3和6.5章节，下文不会再展开介绍。

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

4. 日记状态改成prepare阶段。

5. 写Binlog日记。

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

image

8.3.2 merge 过程

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

1. 将page2读入内存

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

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

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

image

8.3.3 数据刷盘过程

数据刷盘flush的有四种情况

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

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

3. MySQL 认为系统“空闲”的时候

4. MySQL 正常关闭的情况

数据刷盘也代表着Redo log检查点checkpoint触发，这一步将联系到第六章6.7和6.8中的章节内容，较为复杂。

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

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

2. 刷完后更新检查点checkpoint的值

image

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

九、结尾

整个文章讲了Binlog、Undo log和Redo log，随带一提ChangeBuffer，前面四五六七章是分讲，最后第八章是对整个日记相关联讲解。对此，讲到这里，基本上要把我要讲的已经讲完，内容挺多，有耐心可以慢慢啃，不懂欢迎留言！

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

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

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

作者： 神韵_499
原文链接：https://blog.csdn.net/qq_41055045/article/details/108681970