Mysql日志系统

需要查看更多的数据库相关的知识？点击这里

MySQL日志系统

日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志( binlog(归档日志) )和事务日志(包括redo log(重做日志) 和 undo log )，本文接下来会详细介绍这三种日志

一. redo log（重做日志/物理日志）

我们都知道，事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态 。

那么 mysql是如何保证一致性的呢？

最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

1. 因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
2. 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此 mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

为了理解这个日志，可以举一个酒店掌柜的例子。说是有一个酒店掌柜，他有一个粉板专门记录客人赊账的情况，如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。
如果有人要赊账或者还账的话，掌柜一般有两种做法：

1. 一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；
2. 另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。
同样，在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。redo log 就相当于是粉板，而 bin log 相当于是账本。
而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

redolog 写入机制

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。

事务在执行过程中，生成的redo log会写到redo log buffer，其里面的内容不需要在每次生成时都持久化到磁盘（若事务执行期间MySQL发生异常重启，那这部分日志就丢了。但由于事务并没有提交，所以这时日志丢了也不会有损失），但是依然会有存在一个没有提交的事务的redo log，可能是已经持久化到磁盘的状况。

redolog三种存储状态

1、存在redo log buffer中，物理上是在MySQL进程内存中，就是图中的红色部分；
2、写到磁盘(write)，但是没有持久化（fsync)，物理上是在文件系统的page cache里面，也就是图中的黄色部分；
3、持久化到磁盘，对应的是hard disk，也就是图中的绿色部分。

日志写到redo log buffer是很快的，wirte到page cache也差不多，但是持久化到磁盘的速度就慢多了。innodb_flush_log_at_trx_commit值可用于设置redolog写入策略。InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。

• 设置为0的时候(延迟写)，表示每次事务提交时都只是把redo log留在redo log buffer中;
• 设置为1的时候(实时写，实时刷)，表示每次事务提交时都将redo log直接持久化到磁盘；
• 设置为2的时候(实时写，延迟刷)，表示每次事务提交时都只是把redo log写到page cache。
  

redo log记录形式

redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

二. bin log（归档日志/逻辑日志）

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

• 逻辑日志：可以简单理解为记录的就是sql语句
• 物理日志：mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更

binlog 是通过追加的方式进行写入的，可以通过max_binlog_size 参数设置每个 binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog使用场景

1. 主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致。
2. 数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

binlog刷盘时机

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录binlog ，此时记录还在内存中，那么 binlog是什么时候刷到磁盘中的呢？
mysql 通过 sync_binlog 参数控制 binlog 的刷盘时机，取值范围是 0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次 commit 的时候都要将 binlog 写入磁盘；
• N：每N个事务，才会将 binlog 写入磁盘。

从上面可以看出， sync_binlog 最安全的是设置是 1 ，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

binlog写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中，并清空cache。每个线程有自己binlog cache，但是共用同一份binlog文件。

• 图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
• 图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。

write 和fsync的时机，是由参数sync_binlog控制的：

• sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
• sync_binlog=1的时候，表示每次提交事务都会执行fsync；
• sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。
但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

binlog 日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。

statement

每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候其sql进程会解析成和原来master端执行的相同sql再执行。

优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为他只需要记录在master上所执行的语句的细节以及执行语句的上下文信息。

缺点：在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，如以下场景可能会有数据不一致的情况。

sleep()函数在有些版本中就不能被正确复制
在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。
执行delete from t where a > '666' and create_time<'2022-03-01' limit 1，数据选择了a索引和选择create_time索引，最后limit 1出来的数据一般是不一样的。所以就会存在这种情况：在binlog = statement格式时，主库在执行这条SQL时，使用的是索引a，而从库在执行这条SQL时，使用了索引create_time。最后主从数据不一致了。

row

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改，只记录要修改的数据，只有value，不会有sql多表关联的情况。

由于row是基于每一行来记录的变化，所以不会出现statement那种可能导致数据不一致的问题。

优点：在row模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和出现无法被正确复制问题。

缺点：在row模式下，所有的执行的语句在记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，会占用大量的空间（比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。同理，alter table也一样。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度）。

mixed

前两种格式的混合，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。另外如果MySQL判断使用statement可能会有数据不一致的情况时，就用row格式，

三、undo log

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上， 原子性 底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。

同时， undo log 也是 MVCC(多版本并发控制)实现的关键。

四、redolog 与 binlog日志的对比

	redo log 物理日志	binlog 逻辑日志
文件大小	redo log 的大小是固定的。	binlog 可通过配置参数max_binlog_size 设置每个 binlog 文件的大小。
实现方式	redo log 是 InnoDB 引擎层实现的，并不是所有引擎都有。	binlog是 Server 层实现的，所有引擎都可以使用 binlog 日志。
记录方式	redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。	binlog 通过追加的方式记录，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上，不会覆盖以前的记录。
适用场景	redo log 适用于奔溃恢复(crash-safe)	binlog适用于主从复制和数据恢复

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。
• 只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。
• 通常主库是用 redo log 来恢复，从库是用 binlog 来同步的。

为什么Mysql有两份日志呢？

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

五、Innodb存储引擎配合日志系统的执行过程

假设我们要执行以下一条简单的语句，我们来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

 update users set name = 'xx' where id = 10;

流程总结：

1. 首先 MySQL 执行器根据执行计划调用存储引擎的API查询数据
2. 存储引擎先从缓存池 buffer pool 中查询数据，如果没有就会去磁盘中查询，如果查询到了就将其放到缓存池中
3. 在数据加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo log 日志文件中（不需要到磁盘读原来的值，只是做一个与当前语句相反的命令）
4. innodb 会在 Buffer Pool 的 Change Buffer 中执行更新操作

   在事务提交后，后台会有线程定期刷新数据到磁盘，也就是刷页

   • 访问这个数据页触发merge
   • 后台线程会定期merge
   • 在数据库正常关闭（shutdown）的过程中会执行merge

   在正常的情况下, MySQL写数据page时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是从doublewrite buffer写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。(Slave上可以关闭双写缓冲区)

5. 更新后的数据会记录在 redo log buffer 中(prepare)
6. 提交事务在提交的同时会做以下三件事
   1. 将 redo log buffer 中的数据刷入到redo log文件中（由innodb_flush_log_at_trx_commit策略决定刷盘时机）
   2. 将本次操作记录写入到 bin log 文件中（由sync_binlog策略决定刷盘时机）
   3. 将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记
7. 使用一个后台线程，它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了

其中redolog 的写入拆成了两个步骤：prepare和commit，这就是"两阶段提交"，其目的是为了让两份日志之间的逻辑一致。

为什么需要两阶段提交？

我们用反证法来进行解释如下：
由于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，与原库的值不同。
2. 先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

那么按照前面说的两阶段提交就能解决问题吗？
我们来看如下三种情况：

1. 阶段一提交之后崩溃了，即写入 redo log，处于 prepare 状态 的时候崩溃了，此时：由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。
2. 假设写完 binlog 之后崩溃了，此时：redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时候，首先检查 binlog 中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。
3. 假设 redolog 处于 commit 状态的时候崩溃了，那么重启后的处理方案同情况二。

由此可见，两阶段提交能够确保数据的一致性。

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

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

一天一备与一周一备的对比

在一天一备的模式里，最坏情况下需要应用一天的binlog。比如，你每天0点做一次全量备份，而要恢复出一个到昨天晚上23点的备份。
一周一备最坏情况就要应用一周的binlog了。其中涉及更长的RTO（恢复目标时间）
频繁全量备份需要消耗更多存储空间，所以这个RTO是成本换来的，需要根据业务重要性来评估使用哪种全量备份形式

写入流程总结

• Innodb层：事务执行过程中，将生成的redolog写到redolog buffer中；事务提交后，由具体的策略决定什么时候将buffer的内容持久化到磁盘（大小固定，循环写入的环结构），策略也可设置提交后只存储到文件系统的page cache中（实时写，延迟刷）
• Server层：事务执行过程中，把日志记录到binlog cache（每个线程私有）；事务提交后，将cache中的内容存储到文件系统的page cache，并清空上面的cache，由具体策略决定什么时候刷盘到binlog文件（每个线程公有，满了再新建文件）。
• 流程配合：事务执行过程中，写redolog，设置其状态为prepare，再写binlog；随后事务提交，将redolog的状态改成commit（两阶段提交）。

六、其他

为什么 binlog cache 是每个线程自己维护的，而 redolog buffer 是全局共用的？

1、binlog是不能“被打断的”。一个事务的binlog必须连续写，因此要整个事务完成后，再一起写到文件里。而redo log并没有这个要求，中间有生成的日志可以写到redo log buffer中。redo log buffer中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。
2、binlog存储是以statement或者row格式存储的，而redo log是以page页格式存储的。page格式，天生就是共有的，而row格式，只跟当前事务相关。

redo log 与 change bufer的区别：

从上面的文章中我们可以知道，WAL 技术的核心机制，是尽量减少随机读写
而change buffer也是用于减少随机读写，将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
redolog是日志，而change buffer是缓存

什么是change buffer？

内容参考链接

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer是可以持久化的数据，它是buffer pool中的一块区域。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge

触发merge的时机如下：

• 访问这个数据页触发merge
• 后台线程会定期merge
• 在数据库正常关闭（shutdown）的过程中会执行merge

merge流程如下：

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从change buffer里找出这个数据页的change buffer 记录(可能有多个），依次应用，得到新版数据页；
3. 写redo log。这个redo log包含了数据的变更和change buffer的变更。

change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

什么条件下可以使用change buffer

只有普通索引可以使用change buffer（对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了）

change buffer的使用场景

change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

redo log一般设置多大？

如果是现在常见的几个TB的磁盘的话，可以直接将redo log设置为4个文件、每个文件1GB

InnoDB双写缓冲技术

InnoDB使用了一种叫做doublewrite的特殊文件flush技术，在把pages写到data files之前，InnoDB先把它们写到一个叫doublewrite buffer的连续区域内（位于磁盘空间中），在写doublewrite buffer完成后，InnoDB才会把pages写到data files的适当的位置。如果在写page的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复。

partial page write问题
InnoDB的page size一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以page为单位进行操作的。操作系统写文件是以4KB作为单位的，那么每写一个InnoDB的page到磁盘上，操作系统需要写4个块。而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K时，发生了系统断电或系统崩溃，只有一部分写是成功的，这种情况下就是partial page write（部分页写入）问题。这时page数据出现不一样的情形，从而形成一个"断裂"的page，使数据产生混乱。这个时候InnoDB对这种块错误是无能为力的.
有人会认为系统恢复后，MySQL可以根据redo log进行恢复，而MySQL在恢复的过程中是检查page的checksum，checksum就是page的最后事务号，发生partial page write问题时，page已经损坏，找不到该page中的事务号，就无法恢复。
因此Mysql设计了双写缓冲区来解决此问题

doublewrite buffer是InnoDB在tablespace上的128个页（2个区）大小是2MB。为了解决 partial page write问题，当MySQL将脏数据flush到data file的时候, 先使用memcopy将脏数据复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分2次，每次写入1MB到共享表空间，然后马上调用fsync函数，同步到磁盘上，避免缓冲带来的问题，在这个过程中，doublewrite是顺序写，开销并不大，在完成doublewrite写入后，再将double write buffer写入各表空间文件，这时是离散写入。

所以在正常的情况下, MySQL写数据page时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是从doublewrite buffer写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。

doublewrite的缺点是什么？

位于共享表空间上的doublewrite buffer实际上也是一个文件，写共享表空间会导致系统有更多的fsync操作, 而硬盘的fsync性能因素会降低MySQL的整体性能，但是并不会降低到原来的50%。这主要是因为：

doublewrite是在一个连续的存储空间, 所以硬盘在写数据的时候是顺序写，而不是随机写，这样性能更高。

将数据从doublewrite buffer写到真正的segment中的时候，系统会自动合并连接空间刷新的方式，每次可以刷新多个pages。

是否一定需要doublewrite

在一些情况下可以关闭doublewrite以获取更高的性能。比如在slave上可以关闭，因为即使出现了partial page write问题，数据还是可以从中继日志中恢复。设置InnoDB_doublewrite=0即可关闭doublewrite buffer。

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

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

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

什么是“双1"配置？

将sync_binlog和innodb_flush_log_at_trx_commit都设置成 1，即为“双1”配置。其意味着，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。
在什么时候会把线上生产库设置成“非双1”？

• 业务高峰期。一般如果有预知的高峰期，DBA会有预案，把主库设置成“非双1”。
• 备库延迟，为了让备库尽快赶上主库。
• 用备份恢复主库的副本，应用binlog的过程，这个跟上一种场景类似。
• 批量导入数据的时候。

一般情况下，把生产库改成“非双1”配置，是设置innodb_flush_logs_at_trx_commit=2、sync_binlog=1000。

---

注：文章属于学习笔记，主要来源于：极客时间-日志系统：一条SQL更新语句是如何执行的？

部分信息参考来源：

https://zhuanlan.zhihu.com/p/156134875

https://www.cnblogs.com/sunshineliulu/p/10905483.html

https://juejin.cn/post/6860252224930070536

扩展阅读：

SQL语句的执行过程