MySQL 三大日志

前言

MySQL 的日志系统是维护数据库正常运行非常重要的模块，主要分为错误日志、查询日志、慢查询日志、事务日志、二进制日志五大类。

本篇文章主要讨论的就是事务日志(redo log + undo log)和二进制日志(bin log)，这三种日志主要负责数据库宕机之后的数据恢复、事务的原子性以及主从同步的实现。接下来本篇文章就会详细讲述这三种日志，并简单概述其余不常用的几种日志

注：这里不会详细介绍主从同步的实现，之后会专门讲述主从的原理及其实现

基本概念

redo log

1、为什么需要重做日志

试想如果每次更新在内存中的数据之后，都立刻将更新之后的数据写入磁盘中保存，那么这在更新频繁的情况下会导致数据更新的效率非常差。

因为很可能只更新几个字段的值，但是每次读写磁盘的基本单位是数据页，所以仅为了几个字段的值就将整个页刷到磁盘中肯定不合理的
而且每次更新的数据可能处于不同的数据页，而数据页在磁盘中的物理顺序可能不是连续的，这就会导致随机IO写入磁盘，性能肯定很差

既然如此，肯定就很容易想到先将内存中数据更新，然后挑选合适的时机将内存中的数据页更新到磁盘中。

这种想法肯定是没有错误的，但是将数据临时保存在内存中显然是存在风险的。因为如果数据库突然宕机，那么内存中没有落盘的数据全都会消失并且没有办法找回，因为你也不知道之前到底做了什么修改。

所以就需要重做日志来记录数据在磁盘中的位置：偏移量、页号等物理信息，也就是此前是如何更新数据库中的数据的。

重做日志的主要目的就是确保数据库宕机之后可以恢复内存中没有落盘的数据（称为 Crash-Safe），其次就是避免频繁向磁盘中刷新脏页造成的性能开销

① 这里存在一个问题，重做日志是如何知道数据在磁盘中对应的位置的？我暂时没有找到答案

② 每次刷新脏页的时候都需要配合重做日志的记录进行更新，但是具体是如何配合的就不清楚了

2、什么是重做日志

在解释为什么需要重做日志的时候已经提到了数据页和日志，这两者最后都需要经过操作系统缓冲区再写入到磁盘中，也就是说肯定需要选择合适的写入磁盘的时机，而向磁盘中写入脏页和写入重做日志基本是没有任何联系的，两者是独立的行为

2.1、重做日志落盘

WAL（Write Ahead-Logging）机制

机制内容
- 重做日志组成部分：redo log = redo log buffer（内存）+ redo log file（磁盘）
- 重做日志落盘过程：
  - 首先会将日志记录写入到内存中的 redo log buffer 空间
  - 然后再选择合适的时间将 redo log buffer 中的内容拷贝到 kernal cache 中
  - 最后调用函数将 kernal cache 中的内容写入到磁盘中
- 注：这里的“先写内存，再写磁盘”就是所谓的 WAL 机制
注意事项
- redo log 是 InnoDB 存储引擎独有的日志模块，其余存储引擎是不具有的
- redo log 空间大小是有限的，无法存储所有的更新操作，但是可以通过三个参数进行控制（通常设置为 4个文件，每个文件 1G）
  - innodb_log_buffer_size：重做日志缓冲区的大小
  - innodb_log_files_in_group：重做日志文件的数量
  - innodb_log_file_size：重做日志文件的大小
- 每次更新数据的时候，都会先更新内存中的数据，然后再更新 redo log buffer 中的记录

落盘时机

主线程控制：主线程中的主循环部分每秒都会将 redo log buffer 中的日志持久化存储到 redo log file 中
数据库关闭：如果数据库是正常关闭，那么也会将内存中的 redo log buffer 持久化存储到 redo log file 中
内存比例：如果 redo log buffer 中存储的内容已经超过给定内存的一半时，也会将 redo log buffer 中的日志持久化到 redo log file

事务决定：

直接使用 innodb_flush_log_at_trx_commit 控制落盘时机

参数值	参数含义	特点
0（延迟写）	事务提交之后，此时仅会将重做日志保存在内存中，不会持久化存储，存在数据丢失的风险实时写，	因为需要等待主线程的到来，所以可能在等待的过程中发生宕机，从而丢失数据，不过仅丢失 1 秒的数据
1（实时写、实时刷）	事务提交字后，此时会将重做日志先拷贝到内核缓冲区，然后持久化到磁盘中	采用同步阻塞的方式写入磁盘，这会导致IO性能较差，但是可以保证数据不会在写入的过程中丢失
2（实时写，延迟刷）	事务提交之后，此时仅会将重做日志拷贝到内核缓冲区，然后由操作系统决定何时将日志持久化到磁盘	采用异步非阻塞的方式写入磁盘，虽然IO性能更好，但是数据可能在写入的过程中丢失

如果存在多个事务并行执行的时候，可能其余事务在提交时顺带将当前事务在 redo log buffer 中的日志持久化到 redo log file 中

事务 A 还没有提交，但是事务 B 已经打算提交了，只要 innodb_flush_log_at_trx_commit = 1，事务 B 就会让 redo log buffer 中的日志持久化存储到 redo log file 中，又由于 redo log buffer 是多个线程共同使用，所以事务 A 和事务 B 的日志都会在此时落盘

注：至于存储引擎是如何协调这个刷新时机的，那个就是存储引擎自己的事了，我并不是很清楚o(╥﹏╥)o

注：这里之后会和两阶段提交出现“矛盾”的现象，不过这也是之后再谈

日志记录过程

write position：记录的是最后一个需要落盘的记录的日志序列号（LSN）
check point：记录的是第一个需要落盘的记录的日志序列号（LSN）
check point -> write position：
- 存储的是日志记录，也就是数据页的修改记录
- 每次将缓冲区中的日志记录对应的脏页落盘的时候，就需要将 check point 向前推进
write position -> check point：
- 空闲部分可以用于存储新的日志记录
- 每存储新的日志记录，write position 都会向前推进直到追上 check point
- 如果 write position 追上 check point，那么此时所有的更新必须暂停，必须将已有的日志记录对应的脏页全部刷新到磁盘中

无论此前数据库是否为正常关闭，数据库启动的时候都会验证数据页的日志序列号和重做日志中记录的日志序列号是否合理：如果数据页的日志序列号小于 check point 保存的日志序列号，那么就意味着 check point 之后对应的脏页没有落盘，就会从 check point 开始恢复；反之，就意味check point 之后的内容对应的数据页已经落盘了，没有必要再去恢复了

2.2、脏页落盘

脏页落盘流程

脏页落盘和重做日志没有任何直接关系！

脏页刷新到磁盘中时，依然会在磁盘中寻道找到对应的数据页然后更新，这个过程和重做日志没有任何关系
- 这里是内存中数据多次更新之后才刷新的脏页而不是每次更新都刷脏页，所以这里的随机IO开销是可以接收
- 这里也说明了每次脏页落盘的时候都是从缓冲池中写入磁盘的，而不是从日志缓冲池中写入磁盘的
在数据库宕机恢复的时候才会启用重做日志，将磁盘中的数据页读入内存中，然后使用重做日志将其变为脏页，最后将脏页落盘
- 也就是说重做日志不能够直接将磁盘中干净页进行数据恢复，而只能够先将内存中的数据变为脏页，然后落盘

脏页落盘时机

重做日志写满后就会暂停所有更新操作，然后将重做日志中对应的脏页刷新到磁盘中，并且清除该部分日志
脏页在内存中的比例超过设置的阈值，主线程就会采用最近最少使用的策略挑选固定数量的脏页刷新到磁盘中
- 主线程每秒都会检查脏页在内存中的比例是否超过阈值并且刷新脏页，每十秒也会向磁盘中刷新固定数量的脏页
- 这里挑选的脏页可能是不连续的，重做日志中对应的部分也是不连续的，那么如何清除这部分脏页对应的日志呢？
  - 这个问题的答案是：不需要清除脏页对应的重做日志中的部分
  - 原因是因为，既然脏页已经被写入磁盘了，那么内存中这个数据页也不会再认为是脏页了，既然是干净页直接跳过就行
数据库的正常关闭会将内存中的脏页全部刷新到磁盘中
主线程无事可做的时候会切换到后台循环/刷新循环向磁盘写入脏页

性能问题

数据库正常关闭和主线程空闲的时候向磁盘写入脏页，显然是不会对数据库的查询、更新等操作造成任何影响的

如果因为重做日志写满而导致的脏页写入磁盘，那么就会造成比较严重的性能问题。因为重做日志无法容纳更多的日志记录，所以此时所有的更新操作必须停止，数据库的写性能就会直接降低为 0

如何避免重做日志频繁写满呢？① 建议将重做日志的大小设置得比较大 ② 避免产生重做日志的速度大于使用重做日志的速度

此外，如果因为脏页内存比例超过阈值而向磁盘中写入脏页，并且写入磁盘的脏页数量过多的话，会导致占用大量的IO资源，从而严重降低查询和更新的性能

如何避免脏页每次刷新到磁盘的数量过多呢？刷新脏页的阈值比例设置到多少合适呢？主要是由以下两个参数控制

innodb_max_dirty_pages_pct：脏页比例上限，默认值为 75%
innodb_io_capacity：每次向磁盘中刷新的脏页数量，默认值为 200
- 这个参数的值建议根据磁盘的 IO 能力设置，尽可能将参数的值设置得和磁盘的 IOPS 值相同
- 如果值设置太大就会出现之前提到的性能问题，也就是 MySQL 会突然出现执行非常缓慢的情况
- 如果值设置太小，内存中脏页比例会频繁超过阈值，从而导致频繁向磁盘写入脏页，并且脏页的积累会间接导致重做日志写满
- 注：固态硬盘通常可以将参数的值设置为 20000

脏页落盘时间中提到主线程每秒都会检查的脏页比例是否超过阈值，如果超过阈值就会全力刷新脏页，如果没有那就不会刷新。此外，还提到无论是否超过阈值，每隔十秒都会刷新固定数量的脏页，这里刷新的数量就不是单纯的 innodb_io_capacity 的值了，需要通过公式计算

主线程会根据脏页比例上限和当前的脏页比例计算得到结果 M，然后在根据 write position 和 check point 的序列号之差计算得到结果 N
- 结果 M 的计算公式：
  - 如果当前脏页比例没有超过脏页比例上限，那么 M = 100 * 当前脏页比例 / 脏页比例上限
  - 如果当前脏页比例超过脏页比例上限，那么 M = 100
- 结果 N 的计算公式太复杂，没有给出
最后将每次向磁盘中刷新脏页的数量 * MAX{M，N}，这就是每隔十秒会向磁盘中刷新的数量

bin log

1、什么是二进制日志

二进制日志(binlog)又可以称为归档日志，和重做日志相同，都是将数据的更新操作记录下来，并且以二进制的形式保存在磁盘中。二进制日志和重做日志看似保存的内容相同，实际存储的内容和应用场景却是大相径庭，接下来主要通过对比两个日志来介绍二进制日志。

1.1、二进制日志格式

二进制日志和重做日志记录日志的形式不同：二进制日志采用的逻辑日志的形式记录，重做日志采用的物理日志的形式记录
- 逻辑日志：简单理解记录的就是人能够直接识别的信息：SQL 语句
- 物理日志：简单理解就是硬件才能够识别的信息：数据页号、页偏移量等等

逻辑日志具体三种格式

STATMENT：基于 SQL 语句的复制（SBR：Statement-Based Replication）
- 含义：每次更新数据的时候都直接将更新数据的 SQL 语句记录在归档日志中
  -- 直接就将这个 SQL 语句翻译成二进制的形式保存下来
  update T set name='新的名字' where id between 2 and 4;
- 缺点：在主从同步的过程中可能因为数据库的版本不同从而导致 SQL 语句无法被识别 => 数据同步失败
- 优点：不需要记录所有被 SQL 语句影响的行记录的变化，节省日志占用的空间开销

ROW：基于行的复制（RBR： Row-Based Replication）

含义：每次更新数据的时候不记录更新的 SQL 语句，而是去记录每行是如何变化的

-- 之前的更新语句显然是会影响三行记录的，所以需要将三行记录的变化都写在日志中
id = 2 -> name = '新的名字';
id = 3 -> name = '新的名字';
id = 4 -> name = '新的名字';

优点：不会因为数据库的版本不同而导致主从同步的过程中回放失败，毕竟记录的不再是 SQL 语句
缺点：如果影响的行数特别多（比如插入大量的数据），那么就需要大量的空间来存储对应的日志文件

MIXED：基于 STATMENT 和 ROW 两种的混合模式（MBR：Mixed-Based Replication）
- 含义：在 SQL 语句可以被识别的情况下就使用 STATMENT 模式，在 SQL 语句无法被识别的情况下就使用 ROW 模式
MySQL 5.7 之前默认归档日志的格式是 STATMENT，此后默认格式变为 ROW
-- 查询归档日志的格式
show variables like 'binlog_format';

1.2、二进制日志应用场景

主从同步：
- 每次 Master 数据库更新数据之后都会将对应的归档日志发送给 Slave 数据库
- 然后 Slave 数据库回放归档日志从而保证和 Master 数据库一致
- 之后每次读取数据都是从 Slave 数据库中读取，每次更新数据都是向 Master 数据库中更新
- 设计主从同步的目的主要是为了实现读写分离，从而减轻 Master 数据库的压力
数据备份：
- 如果出现数据库误操作，那么就可以找到此前某个时间点到当前时刻的全量备份
- 从之前的时间点到当前时刻之间的归档日志全部取出来，然后进行回放，这样就可以回到误操作之前的数据库状态
问题：那么什么时候一天一备份，什么时候一周一备份？
归档日志不具有 Crash-Safe 的能力，重做日志才具有 Crash-Safe 的能力
- 虽然归档日志也记录所有更新操作，但是归档日志无法像重做日志一样可以根据检查点得知哪些对应的脏页已经落盘而哪些没有
- 如果实在需要实现 Crash-Safe，在不改动原有归档日志的情况下，就只能够根据归档日志依次查看记录是否已经落盘

1.3、二进制日志落盘时机

归档日志需要写入到磁盘中，也就是说会经过操作系统中的缓冲区才能够写入到磁盘，所以也会存在相应的落盘时机

归档日志落盘流程
- 存储引擎会为每个线程都分配相应的 binlog cache，然后会将日志写入 binlog cache 中
  - 如果 binlog cache 的大小不足以容纳需要写入的日志时，就会在使用临时文件存储需要记录的日志
  - 还可以使用 binlog_cache_size 控制每个线程的 binlog cache 大小
- 每个线程会在事务提交的时候，将 binlog cache 中的内容全部写入到文件系统的 page cache 中（操作系统内核缓冲区）
- 然后会将 binlog cache 中的内容情况，最后将文件系统的 page cache 中的内容同步到磁盘中去
归档日志落盘时机：主要使用 sync_binlog 参数控制
- sync_binlog = 0：每次都只是将归档日志写入操作系统缓冲区中，由操作系统自行判断何时将归档日志写入磁盘
- sync_binlog = 1：每次事务提交的时候，都会将归档日志写入缓冲区然后再写入磁盘
- sync_binlog = N：每次事务提交的时候，都先将归档日志写入缓冲区，直到有 N 个事务提交之后才会写入磁盘
归档日志和重做日志落盘的不同
- 归档日志使用 sync_binlog 参数控制落盘时机，重做日志使用 innodb_flush_log_at_trx_commit 参数控制落盘时机
- 归档日志是每个线程都具有独立的 binlog cache，重做日志中所有线程使用的都是相同的 redo log buffer

1.4、二进制日志和重做日志的其余不同点

归档日志是属于 Server 层的，所有存储引擎都具有的日志模块；重做日志是属于 Engine 层的，只有 InnoDB 才有的日志模块
归档日志是追加写的，而重做日志是循环写的
- 如果归档日志的内容过多而无法存储在内存中，那么会暂存到磁盘中，而不会覆盖之前的日志
- 如果重做日志的内容超过内存限制，就必须要将部分数据更新到磁盘之后才可以继续存储日志，相当于此前的日志被覆盖

2、为什么需要二进制日志和重做日志两份日志

从刚才对比归档日志和重做日志的过程中其实就可以看出，为什么需要两份日志同时存在：最主要的原因就是两个日志的定位不同，也就是使用场景不同

重做日志的大小是有限的（无论是 redo log buffer 还是 redo log file），没有办法记录所有的更新操作，也就没有办法起到归档的作用，也就是说想要恢复到数据库此前任何一个时间点是不可能的，这个行为只能够依靠归档日志完成，此外主从同步也只能够依靠归档日志完成。

同理，归档日志不支持 Crash-Safe，即使强行支持，恢复的速度也远不如重做日志，所以也就没有办法替代重做日志

所以，这两个日志目前只能够是共存的，都存在双方无法替代的功能

undo log

内容：每次执行更新数据库的操作之后， undo log 就会记录其相反操作，并且保存在共享表空间中
特点：其目的就是在事务失败的时候，可以通过 undo log 中相反的操作回滚到此前数据库的状态，也就是实现了原子性

其余日志

错误日志

查询日志

慢查询日志

两阶段提交

1、什么是两阶段提交

机制详解

在此之前已经详细讲述了两种日志，接下来就来需要提到在数据更新的流程中，两种日志究竟是如何配合的

注：重做日志和归档日志都只会记录更新数据的操作，是不会记录查询数据的操作

执行器调用引擎接口根据索引树查询需要更新的数据
- 如果数据在内存中就直接更新；如果数据不在内存中就从磁盘中读取后再更新
- 数据的更新操作完成之后，执行器就将归档日志写入 binlog cache，引擎将重做日志写入 redo log buffer
- 注：事务执行期间发生的数据库宕机导致的日志消失是不会有任何影响的，更新没有执行，日志也相应消失
执行器完成所有的更新操作之后提交事务，准备开始将记录写入内核缓冲区或者持久化存储
- 因为日志的持久化是在事务提交之后，所以此时事务已经不可能再回滚了
- 不会出现因为日志持久化存储到磁盘后，发生事务回滚导致日志记录的内容错误
引擎根据重做日志 innodb_flush_log_at_trx_commit 参数的设置，选择将重做日志持久化的方式，并标记重做日志为 prepare 状态
- 延迟写：此时仅会将重做日志保存在内存中，不会持久化存储，存在数据丢失的风险
- 实时写，实时刷：此时会将重做日志先拷贝到内核缓冲区，然后持久化到磁盘中，没有数据丢失风险，但是同步 IO 效率低
- 实时写，延迟刷：此时仅会将重做日志拷贝到内核缓冲区，然后由操作系统决定何时将日志持久化到磁盘，也存在丢失数据的风险，但是异步 IO 效率高
执行器也根据归档日志 sync_binlog 参数的设置，选择将归档日志持久化的方式
- sync_binlog = 0：仅会将归档日志写入内核缓冲区，由操作系统决定何时持久化到磁盘，存在丢失数据的风险，但是效率高
- sync_binlog = 1：此时会将归档日志拷贝内核缓冲区，然后直接持久化到磁盘，没有数据丢失风险，但是同步 IO 效率低
- sync_binlog = N：此时仅会将归档日志拷贝到内核缓冲区，然后等待第 N 个事务提交之后才会将其一起持久化到磁盘，也存在丢失数据的风险
引擎在执行器完成归档日志的持久化之后，就会将重做日志标记为 commit 状态
- 如果选择实时写，实时刷，那么在 prepare 阶段就将日志持久化了，commit 阶段只会将日志写入内核缓冲区不会再持久化日志
- 如果选择实时写，延迟刷，那么在 commit 阶段就会重新将内核缓冲区的日志改为 commit 状态，再由操作系统决定何时持久化

在两个重做日志都持久化存储之后，两阶段提交就算完成，这里的两阶段指的就是 prepare 和 commit 两种状态

sync_binlog = 1 & innodb_flush_log_at_trx_commit = 1 称为双 1 配置，这种配置因为存在同步写入磁盘的行为，所以造成的性能开销比较大

注：在这里你可能会产生些疑问，但是请耐心看下去，我会详细解释

2、为什么需要两阶段提交

在了解什么是两阶段提交之后，显然我们需要知道为什么需要这个机制，这个机制到底有什么好处？

先试想下，如果没有两阶段提交会出现什么问题？

情景假设：如果在完成第一个日志的持久化存储之后，在写第二个日志的期间数据库发生宕机时，会出现什么情况？

如果先写重做日志，后写归档日志：
- 重做日志已经持久化到磁盘，但是归档日志却因为数据库宕机没有写入磁盘从而消失，那么在数据库恢复的时候就会出现问题
- 此前提到无论数据库正常还是异常关闭，都会检查重做日志，那么此时重做日志有对应的记录肯定就会执行，恢复此前的数据
- 但是主库的数据确实恢复了，但是采用归档日志进行同步的从库却不可能有相应的数据，因为归档日志里压根就没有记录
- 所以这里就会造成主从数据库不一致的问题
如果先写归档日志，再写重做日志：
- 归档日志已经持久化到磁盘中，但是重做日志因为数据库宕机没有写入磁盘而消失，实际造成的问题是一样的
- 数据库恢复的时候，因为找不到对应的重做日志，显然主库的数据肯定无法恢复
- 但是从库却可以利用归档日志恢复出来此前的数据，因为归档日志里记录了相应的更新操作
- 所以依然还是会造成主从数据库不一致的问题

也就是说两阶段提交机制就是为了避免主从数据库不一致的情况出现，从本质上说就是为了保证重做日志和归档日志的逻辑一致性

那么，两阶段提交到底是如何解决这个问题的呢？

先来详细看看数据库恢复的规则

如果重做日志已经被被标记为 commit 状态，那么就会在恢复的时候直接执行相应的重做日志
如果重做日志已经只被标记为 prepare 状态，那么就会利用日志中的 XID 去找对应的归档日志
- 如果找到拥有完整事务的归档日志，那么重做日志就可以执行
- 如果没有找到拥有完整事务的归档日志，那么就不能够使用这份重做日志恢复数据
如果重做日志拥有的事物本身就不完整的话，自然是不能够执行的

再来看下刚才的情况是否能够正确处理

如果在重做日志的 prepare 阶段发生数据库宕机，那么自然所有的日志文件都没有写入磁盘，没法依靠日志恢复数据了
如果在写入归档日志的阶段发生数据库宕机，显然重做日志只会被标记为 prepare 状态，数据恢复的时候是不会执行的
如果在归档日志写入完成之后发生数据库宕机，虽然重做日志只被标记为 prepare 状态，但是可以找到完整的归档日志，所以可以执行

3、两阶段提交存在的潜在“问题”

通过刚才的讲述，相信你对两阶段提交有很直观的认识了，本质是一个非常简单的机制，但是也许你会发现和你此前了解内容出现了些许冲突，接下来就来讲讲这些冲突

后台线程每秒都会将 redo log buffer 中的内容刷新到磁盘中，那事务执行期间突然回滚怎么办？

这其实是对两阶段提交的定义出现了误解，在事务执行期间将日志持久化到磁盘中，此时是不处于两阶段提交机制的，也就是压根不会有 prepare 或者 commit 的标志，也就是说即使事务回滚或者数据库宕机，恢复数据的时候也会因为没有 prepare 的标志而放弃执行重做日志，所以并没有违反两阶段提交

如果在两阶段中的 prepare 阶段就持久化到磁盘，那数据库突然宕机怎么办？

仔细看看刚才提到的恢复规则，这时候就取决于归档日志是否完全落盘，只要归档日志完全落盘，即使没有 commit 标志也是可以用于数据恢复的，如果归档日志没有完全写入磁盘，那肯定就不可以了

以上就是我在学习两阶段提交和日志落盘时机碰到的问题，确实想了很久

参考资料：

《MySQL 实战 45 讲》

《MySQL 存储引擎：InnoDB 技术内幕》

《必须了解的mysql三大日志-binlog、redo log和undo log》