01 | 日志系统：一条SQL更新语句是如何执行的？

MySQL 可以恢复到半个月内任意一秒的状态，惊叹的同时，你是不是心中也会不免会好奇，这是怎样做到的呢？

我们还是从一个表的一条更新语句说起，下面是这个表的创建语句，这个表有一个主键 ID 和一个整型字段 c： mysql> create table T(ID int primary key, c int); 如果要将 ID=2 这一行的值加 1，SQL 语句就会这么写： mysql> update T set c=c+1 where ID=2;

前面我有跟你介绍过 SQL 语句基本的执行链路。首先，可以确定的说，查询语句的那一套流程，更新语句也是同样会走一遍。

1.你执行语句前要先连接数据库，这是连接器的工作。

2.前面我们说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。

3.接下来，分析器会通过词法和语法解析知道这是一条更新语句。

4.优化器决定要使用 ID 这个索引。

5.然后，执行器负责具体执行，找到这一行，然后更新。

SQL更新与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。

1. rodo log 日志模块(崩溃安全日志)

MySQL 里有个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。

IO成本就是寻址时间和上线文切换所需要的时间，最主要是用户态和内核态的上下文切换。我们知道用户态是无法直接访问磁盘等硬件上的数据的，只能通过操作系统去调内核态的接口，用内核态的线程去访问。 这里的上下文切换指的是同进程的线程上下文切换，所谓上下文就是线程运行需要的环境信息。 首先，用户态线程需要一些中间计算结果保存CPU寄存器，保存CPU指令的地址到程序计数器（执行顺序保证），还要保存栈的信息等一些线程私有的信息。 然后切换到内核态的线程执行，就需要把线程的私有信息从寄存器，程序计数器里读出来，然后执行读磁盘上的数据。读完后返回，又要把线程的信息写进寄存器和程序计数器。 切换到用户态后，用户态线程又要读之前保存的线程执行的环境信息出来，恢复执行。这个过程主要是消耗时间资源。 --来自《Linux性能优化实战》里的知识 SQL执行前优化器对SQL进行优化，这个过程还需要占用CPU资源

redo log 里用的技术就是MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存(buffer_pool)，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

innoDB引擎先把记录写到redo log 中，redo log 在哪，他也是在磁盘上，这也是一个写磁盘的过程，但是与更新过程不一样的是，更新过程是在磁盘上随机IO，费时。 而写redo log 是在磁盘上顺序IO。效率要高。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件（默认是两个，ib_logfile0以及ib_logfile1），每个文件的大小是 1GB。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos 和 checkpoint 之间的是还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

这里刷数据页保障redo log有空余空间也不仅仅是满了才进行同步到磁盘，一般以下情况都会刷

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

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，数据会先保存到日志里，这个能力称为 crash-safe(崩溃安全)。

2.binlog 日志模块

MySQL 整体来看，其实就有两块：

• 一块是 Server 层，它主要做的是 MySQL 功能层面的事情；
• 还有一块是引擎层，负责存储相关的具体事宜。

上面的redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

这两种日志有以下三点不同。

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

我想你肯定会问，为什么会有两份日志呢？ 因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

有了两种日志的逻辑基础，我们再来看执行器和InnoDB引擎在执行这个简单的update语句时的内部流程。

1.执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2.执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3.引擎将这行新数据更新到内存中，然后将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。

4.执行器生成这个操作的binlog，并把binlog写入磁盘。

5.执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。（最后commit阶段redo log会写入binlog的文件名和位置信息来保证binlog和redo log的一致性，commit这一步实际是会写入XID到redo log中的，XID可以匹配binlog中对应的内容），关于两者的关联 可以看下真实binlog日志（row格式）

### UPDATE `mysql`.`ha_health_check`
### WHERE
###   @1=1582266682672 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='m' /* STRING(3) meta=65027 nullable=0 is_null=0 */
### SET
###   @1=1582266698557 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='m' /* STRING(3) meta=65027 nullable=0 is_null=0 */
# at 790
#200221 14:31:38 server id 3141372998  end_log_pos 821 CRC32 0xf63b387d         Xid = 1289289427
COMMIT/*!*/;
# at 821
#200221 14:31:44 server id 3141372998  end_log_pos 886 CRC32 0xc01e5aea         GTID    last_committed=2   sequence_number=3

update执行过程 浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

最后三步看上去有点“绕”，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

两阶段提交

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致从而保障遇到崩溃或者需要恢复数据时候可以保障数据库一致。要说明这个问题，我们得从文章开头的那个问题说起：怎样让数据库恢复到半个月内任意一秒的状态？

前面我们说过了，binlog会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

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

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

好了，说完了数据恢复过程，我们回来说说，为什么日志需要“两阶段提交”。这里不妨用反证法来进行解释

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

• 1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
• 1. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

你可能会说，这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？

其实不是的，不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致

redolog和binlog具有关联行，在恢复数据时，redolog用于恢复主机故障时的未更新的物理数据，binlog用于备份操作。每个阶段的log操作都是记录在磁盘的，在恢复数据时，redolog 状态为commit则说明binlog也成功，直接恢复数据；如果redolog是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。

小结

今天，我介绍了 MySQL 里面最重要的两个日志，即物理日志 redo log 和逻辑日志 binlog。

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

innodb_flush_log_at_trx_commit={0|1|2} # 指定何时将事务日志刷到磁盘，默认为1。 0表示每秒将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。 1表示每事务提交都将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。 2表示每事务提交都将"log buffer"同步到"os buffer"但每秒才从"os buffer"刷到磁盘日志文件中。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

额外扩展

1. Redo log不是记录数据页“更新之后的状态”，而是记录这个页 “做了什么改动”，是一种物理上的变化。

Redo log可以理解为，因为其记录的是对物理页的修改，一个物理页16kb，那redo log里记录的可以这么理解：16kb，换算出byte，就是16 * 1024,这么多个字节，假设文件开始为第0个字节，简称为offset 0； redo log，可能会这么记录，offset x开始，写入长度为n的字节数组，要写入的字节数组为byteArray[n]。 Binlog是记录逻辑上的变化。 有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前的记录和更新后的记录。 关于binlog模式的选择以及配置方法可以看https://www.cnblogs.com/barrywxx/p/11544473.html

2.为啥感觉binlog还不能去掉呢？

一个原因是，redolog只有InnoDB有，别的引擎没有。 另一个原因是，redolog是循环写的，数据会被清理，不持久保存，binlog的“归档”这个功能，redolog是不具备的。 其实我们现在知道可能发生的情况了，我感觉也是可以好好设计搞成一个日志也能保证安全以及一些同步需要的。

3.MySQL如何判断binlog完整性？

如果binlog是statement模式的，最后面会出现一个commit的标识，如下:

use `test`/*!*/;
SET TIMESTAMP=1590418900/*!*/;
insert into test values (1,'zhangsan')
/*!*/;
# at 490
#200525 23:01:40 server id 2025725  end_log_pos 521 
COMMIT/*!*/;

如果binlog是row模式的，最后面会出现一个xid的event事件，如下：

### UPDATE `mysql`.`ha_health_check`
### WHERE
###   @1=1582266682672 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='m' /* STRING(3) meta=65027 nullable=0 is_null=0 */
### SET
###   @1=1582266698557 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='m' /* STRING(3) meta=65027 nullable=0 is_null=0 */
# at 790
#200221 14:31:38 server id 3141372998  end_log_pos 821 CRC32 0xf63b387d         Xid = 1289289427
COMMIT/*!*/;
# at 821
#200221 14:31:44 server id 3141372998  end_log_pos 886 CRC32 0xc01e5aea         GTID    last_committed=2   sequence_number=3

除此之外，MySQL5.6中还引入了binlog checksum的参数，用来确认binlog的正确性，一般情况下，这个参数在主从上的设置应该保持一致，要么都为none，要么都为CRC32

4.redo log和binlog是如何关联起来的？

redo log对于用户是不可见的，如果你强制用vim打开redo log，你会看到一堆乱码。在binlog中，我们可以看到binlog的xid值，这个值就是用来关联redo log和binlog的。

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

5. 为什么prepare、binlog写入之后事务需要提交？不能同时抛弃么？

其实对于主库来讲，redo log和binlog要么同时存在，要么同时回滚，都不影响redo log和binlog的一致性。之所以在redo log prepare阶段完成、binlog写入后让事务提交，本质上还是为了保证主库和从库的一致性。因为binlog一旦写入，会通过dump thread同步给从库，从库会应用这个binlog，那么如果主库上crash之后，将写入的binlog回滚了，就有可能造成主库和从库的数据不一致现象

6. 只用binlog或者redo log不能支持崩溃恢复么？

如果只有binlog，那么MySQL的执行逻辑将变成：

数据更新到内存---写binlog---提交事务.

这种情况下，如果写完binlog之后MySQL发生了crash，那么内存中的数据页是无法修复的，由于MySQL采用的是WAL技术，也就是先写内存日志再写磁盘，而binlog是没有能力恢复损坏的内存数据页的。

如果只有redo log，那么因为redo log是循环写的，也就没有办法保留很长的周期，失去了binlog归档变更操作的功能。再者主从复制的结构可能会更脆弱，高可用架构也就更谈不上了。

7. 数据落盘是从redo log落盘的还是从buffer pool？

其实数据落盘和redo log是没有关系的，redo log本身不记录数据页的完整数据，它只记录数据也的物理变更。

数据的落盘其实是将buffer pool中的脏页刷新到磁盘的过程。所谓的脏页，就是buffer pool中被修改的和磁盘上不一致的数据页。

在MySQL崩溃回复的过程中，如果发现某个数据页可能在崩溃回复的过程中，丢失了更新，就会将这个数据页加载到内存，也就是buffer pool，让redo log更新内存中的内容。更新完成之后，这个数据页就变为脏页，可以刷新回磁盘了。

8 .redo log buffer和redo log的写入顺序？

假设有这么一个事务：

begin；

insert xxx 1

insert xxx 2

commit;

在commit之前，需要保存这两个insert产生的redo log，但是又不能直接写入到redo log文件里面，此时这些redo log就先保存在redo log buffer里面，当我们执行commit的时候，才会把redo log写入到iblogfile里面。

所以写入顺序上来讲，redo log buffer先写入，而redo log文件后写入。