上篇文章通过介绍一条 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 引擎会在适当时将操作记录更新到磁盘中，这个更新往往在系统比较空闲的时候做。【这个空闲程度是怎么判断的？】

但是还存在一个问题：当粉板写满之后，怎样处理？

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

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

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

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

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

有了 redo log， InnoDB 可以保证即使数据库异常重启，之前的提交记录不会丢失，这个能力被称为 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 log 和 binlog 之间的三点不同：

区别	日志层级不同	日志类型	日志行为不同
redo log	InnoDB 特有的日志系统	redo log 是物理日志，记录的是"在某个数据上做了什么修改"	redo log 循环写，空间固定会用完
binlog	Server层实现的日志，所有引擎都可以使用	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 的写入拆成了两个步骤：prepare 和 commit，这就是「两阶段提交」。

两阶段提交

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

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

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

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

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

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

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

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

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

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

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

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

小结

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 log 和 binlog 之间的状态一致，两阶段提交是跨系统维持数据逻辑一致性时常有的方案，这个方案不仅在数据库中应用，也可以迁移到日常开发的系统设计中。

课后思考

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

个人思考：

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

精选留言

关于思考题：

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

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

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

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

问答：

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

作者回复：

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

binlog 有两种模式：

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

个人总结：

本篇文章介绍了 MySQL 中两个最重要的日志： redo log 和 binlong。

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

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

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

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

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

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

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Q.E.D.