【MySQL数据库原理三】MySQL日志系统-阿里云开发者社区

上一篇Blog详细学习了MySQL的架构模式和一条语句的执行流程，本篇Blog来详细聊聊MySQL的日志系统，以及它是如何在MySQL的事务上发挥至关重要的作用，本篇文章学习自《极客时间45MySQL45讲》

更新语句执行流程

DML数据操作语句（更新、删除、插入）这些在执行的时候肯定要记录日志，MySQL 自带的日志模块 binlog（归档日志） ，所有的存储引擎都可以使用，常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），假如我们要更新ID为2的这条数据当前值自增1，其中ID为主键，加了索引：

use User
go
update T set c=c+1 where ID=2;

我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

连接器：连接数据库User，并通过输入账号密码通过连接认证， 查询缓存不执行，因为在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空
分析器: 进行语法分析，提取关键字：use 、go、update 、set 、where ，判断关键字是否满足MySQL的语法， 预处理器：进一步获取UserInfo表名、列名：name、age，判断这些元素是否都存在，如果都存在则验证权限，如果权限存在继续向下
优化器：定位到要更新的数据，查询tml这一条数据，然后把age改为18，生成一个执行计划
执行器: 调用handler查询相关接口写入这一行数据

执行器先找存储引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
执行器拿到引擎给的行数据，把这个值设置为18，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务
执行器收到通知后记录 binlog，并把 binlog 写入磁盘
执行器调用引擎接口，提交 redo log 为提交状态。

图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

以上就是在更新数据时的一些操作，实际上只比查询多后半段。

两类日志

上边在执行器阶段涉及两个日志，先来区分下这两个日志的概念。

RedoLog日志

为了降低与磁盘的交互，我们在ES里使用了分段提交，Redis使用了1s的AOF策略，同样在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 使用WAL技术提升更新效率

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，这样做的优势就是顺序写IO比直接的磁盘IO快多了

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log【确切的说是redo log buffer】里面，并更新内存，这个时候更新就算完成了。

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

RedoLog的事务阶段

RedoLog的事务可以分为以下四个阶段，从创建记录到落盘再到更新

创建阶段：事务创建一条日志，并添加到
日志刷盘：日志写入到磁盘上的日志文件；
数据刷盘：日志对应的脏页数据写入到磁盘上的数据文件；
写CKP：日志被当作Checkpoint写入日志文件；

针对日志刷盘阶段和数据刷盘阶段分别有不同的刷盘策略

RedoLog的刷新策略【日志刷盘阶段】

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。

当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

以上策略与Redis采用策略类似，但是数据库常用最为耗IO但不丢数据的策略

因为有了循环的redolog文件，随机IO变为顺序IO，增加了查找效率

RedoLog的刷新策略【数据刷盘阶段】

在数据落盘阶段，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，有以下一些时间点

后台线程定期会刷脏页
清理LRU链表时会顺带刷脏页
redoLog写满会强制刷
数据库关闭时会将所有脏页刷回磁盘
脏页数量过多（默认占缓冲池75%）时，会强制刷

以上就是数据落盘时的一些刷新策略

redo log 一般设置多大？

redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了。一般直接将 redo log 设置为 4 个文件、每个文件 1GB

redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的

正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。

如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。
在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态

可以理解为日志记录的就是一个指针，指向数据脏页

BinLog日志

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

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

redo log有点像Redis的RDB备份模式，而binlog则有点儿像AOF模式。

两阶段提交

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。怎样让数据库恢复到半个月内任意一秒的状态？binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

以上是备份的整体逻辑，即利用磁盘数据+bingLog来备份。

非两阶段提交问题

为什么日志需要“两阶段提交”。由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于 redo log 还没写，崩溃恢复以后这个事务无效。本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

所以要有两阶段提交的一个预备状态，等待大家都准备好了再操作

两阶段提交策略

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。采用这种方式再验证一下：

写redo log 预提交过程中，机器挂了，则回滚事务【时刻A】
写完redo log 预提交，并且写binglog过程中，机器挂了，则判断【时刻B】

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交【说明写完binlog了】
如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务

这样就解决了数据一致性的问题。需要注意的是“commit 语句”执行的时候，会包含“commit 步骤”，以上提到的commit 只是commit 步骤。

MySQL 怎么知道 binlog 是完整的?

一个事务的 binlog 是有完整格式的：statement 格式的 binlog，最后会有 COMMIT；row 格式的 binlog，最后会有一个 XID event。另外，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。所以，MySQL 还是有办法验证事务 binlog 的完整性的

redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务

这样就能知道这条事务binlog到什么阶段了

故障恢复时为什么binlog完整了就提交redo log

在时刻 B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性

为什么不直接逐次提交而设置prepare

对于 InnoDB 引擎来说，如果 redo log 提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果 redo log 直接提交，然后 binlog 写入的时候失败，InnoDB 又回滚不了，数据和 binlog 日志又不一致了

为什么不能只用binlog

把提交流程改成这样：“数据更新到内存” -> “写 binlog” -> “提交事务”，是不是也可以提供崩溃恢复的能力？其实是不可以的

只用 binlog 支持崩溃恢复这样的流程下，binlog 还是不能支持崩溃恢复的。binlog 没有能力恢复“数据页”。如果在图中标的位置，也就是 binlog2 写完了，但是整个事务还没有 commit 的时候，MySQL 发生了 crash需要恢复，恢复的过程就是将未刷盘的内存中的数据脏页恢复到内存中。

binlog1是update c+1;binlog2是update c+1;现在在binlog2写完没提交的时候发生crash,这时对数据的更新可能还停留在内存中，并未刷盘，crash后内存数据丢失。由于binlog2事务未完成，系统会应用binlog2【日志】恢复数据，即此时c+1;
对于binlog1来说，已经完成了事务，系统不会再应用binlog1来恢复数据，所以数据c不会再+1. 这时数据c只加了一次，与未crash前c加了两次不同即binlog没有能力恢复数据页

在图中这个位置崩溃的话，事务1可能只是binlog写进磁盘日志并且提交了，但是事务1更新的记录并没有刷盘，也就是丢失了。但是恢复的时候我们只用binlog来恢复，这时候事务1显示是commit的，所以不会应用binlog，导致这块数据就丢失了。

因为binlog是顺序写，一堆commit，不能确定哪些commit日志指向的数据脏页被刷到了磁盘中，所以在故障恢复的时候有可能将脏页未刷盘但日志已提bei交的数据丢失
redo log就不一样了，因为redo log是循环写的，有checkpoint，通过checkpoint的移动位置可以确定哪些事务指向的数据脏页确实被刷盘了，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了

如果真想使用binlog来恢复的话，那么就要在每个commit之前，将更改的内存记录刷盘。刷盘之后再将这个事务改为commit状态。这样崩溃恢复就可以在事务级去做了，而不用在数据页级去做了，但是这样显然违背WAL技术的初衷

你如果要说，那我优化一下 binlog 的内容，让它来记录数据页的更改可以吗？但，这其实就是又做了一个 redo log 出来。而基于历史原因，既然已经有了现成的redo log，那么就没必要再整一个能crash-safe的binlog出来了。

为什么不能只用redo log

如果只从崩溃恢复的角度来讲是可以的。你可以把 binlog 关掉，这样就没有两阶段提交了，但系统依然是 crash-safe 的。但是一般binlog 都是开着的。因为 binlog 有着 redo log 无法替代的功能。

一个是归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。
一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。总之，由于现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog

通用的Server层离不开Binlog

【MySQL数据库原理三】MySQL日志系统

更新语句执行流程