MySQL的日志和持久性

Buffer Pool

关系型数据库的特点就是需要频繁对磁盘页进行IO，所以有时候也被叫做基于磁盘的数据库，InnoDB为了改善直接读写磁盘导致的 IO 性能问题，引入了缓冲池

缓冲池是一片内存区域，存储引擎在读取数据时，会先将页读取到缓冲池中。下次读取时，看缓冲池是否命中，如果命中，则直接读取，否则从磁盘中读取。

在修改数据时，如果缓冲池中不存在所需的数据页，则从磁盘读入缓冲池，否则直接对缓冲池中的数据页进行修改。

这样的好处是，如果我们频繁修改某个数据页，我们可以不用每次都去磁盘读写（注意是读和写）该页，而是直接对缓冲池中的内容修改，在某个时机再把数据刷新到磁盘。这样就会使得对磁盘的多次操作变为一次。即便修改的内容在磁盘中相距较远的不同数据页上，我们也可以将对多次对磁盘的 IO 合并为一次随机 IO。这样极大地提高了性能

Buffer Pool结构

InnoDB 会把存储的数据划分为若干个页作为磁盘和内存交互的基本单位，默认大小为 16KB。因此，Buffer Pool 同样需要按页来划分。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，一般是128MB，然后按照默认的16KB的大小划分出一个个的页

Buffer Pool 中的页就叫做缓存页，此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool 除了缓存索引页和数据页，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等。

为了更好的管理这些在 Buffer Pool 中的缓存页

InnoDB 为每一个缓存页都创建了一个控制块，用来记录每一个缓存页的信息，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。

控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页

管理空闲页

Buffer Pool使用空闲链表来管理所有的空闲页，将空闲缓存页的「控制块」作为链表的节点

这是一个双向链表，有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除。

管理脏页

和空闲链表类似

提高缓存命中率

再设置一个链表，使用LRU算法

1. 当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部
2. 当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点

但是传统的LRU算法在这里会出现一些问题，就是因为局部性原理导致如果预读进缓冲池的页并没有访问，那么这个预读就没有任何用，预读进来的页还占用着一些空间。所以LRU算法被innoDB改进了：将LRU链表划分为两部分，前面存放会被真正访问的页，后面存放预读的页。

old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pc 参数来设置，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37

划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。

避免缓冲池污染

如果因为没有设置索引或者模糊查找而进行全盘扫描时，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降。

MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。

在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：

1. 如果后续的访问时间与第一次访问的时间间隔很短，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；
2. 如果后续的访问时间与第一次访问的时间间隔不短，那么该缓存页移动到 young 区域的头部；

这个间隔时间是由 innodb_old_blocks_time 控制的，默认是 1000ms。

也就说，只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题 ，短暂停留的页不会进入young区。

MySQL 针对 young 区域还做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会

日志系统和数据持久性详解

由于数据页不是立刻写入磁盘，而是在缓冲池停留，所以被修改的数据页会与磁盘上的数据产生短暂的不一致，我们称此时缓冲池中的数据页为脏页 ，将该页刷到磁盘的操作称为刷脏页 。这个刷脏页的过程是异步的，不需要等待磁盘的 IO 操作。这些特点极大地提升了 InnoDB的性能，但是也引入了脏页的问题，而日志系统就是为了解决这个问题

MySQL最常见的日志主要是 bin log（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）

redo log（重做日志）

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力

在redo log降临之前，我们面临两难：

1. 如果我们修改了缓冲池中的数据页就立刻刷脏页，会产生大量随机 IO，导致磁盘性能变差（写放大问题）
2. 但如果我们先写缓冲，一段时间后再刷脏页，如果这时宕机，脏页就会失效，就有可能造成数据丢失，无法保证事务的持久性。

什么是写放大问题？

对于一次事务来说，写一行数据，对应页中一个记录。但是要实现事务的持久化，不光是要往磁盘中写数据页，还要写 Undo log页。这就是出现了修改一行，需要持久化多个页到磁盘中，因此性能的损失会比较大，这就是写放大问题。

于是救世主降临，即 WAL（Write-Ahead Logging，日志先行） ，让写日志在事务提交前进行写，这里所谓的日志，就是 redo log，保证数据不丢，就是 redo log 的一个重要功能。在事务提交时，不需要绝对保证修改的页持久化到磁盘中，只需保证日志已经持久化存储到磁盘中即可。如果出现掉电或者宕机的场景，内存的页虽然丢失，但是可以通过磁盘的页进行 Redo 重做，恢复更改的内存页。

在绝大部分情况下，Redo Log 数据比数据页和 Undo log页要小，而且按顺序写入，性能也比写放大后的好。

redo log 不会记录对整个页的修改，而是大概像这种：

记录下对磁盘中某某页某某位置数据的修改结果（这种日志被称为物理日志）

由此可以看出，数据库使用 Redo log对数据的操作，速度上接近内存，持久性接近磁盘。

redo log包含两部分，一个是日志缓冲redo log buffer，另外一个是磁盘日志文件redo log file，大致的运行机制如下图

缓冲池的好处前面已经写过，所以 redo log 弄了个类似作用的 redo log buffer，在写 redo log 时会先写 redo log buffer，然后会在特定时机刷到磁盘

刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

• 设置为 0 的时候，表示每次事务提交时不进行刷盘操作，依靠刷盘线程进行
• 设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
• 设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 OS page cache，依靠刷盘线程进行

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘，也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘

除了后台线程每秒1次的轮询操作，还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘

• 为0时，如果MySQL挂了或宕机可能会有1秒数据的丢失

• 为1时， 只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。

  如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失

• 为2时， 只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。

  如果仅仅只是MySQL挂了不会有任何数据丢失，因为已经写入主机的内存里了，但是宕机可能会有1秒数据的丢失

日志文件

redo log也需要持久化，日志的持久化成本更低。redo log持久化到磁盘的redo logfile，日志文件也承担了innoDB数据恢复的职责

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。

这个文件组将以环形数组的形式的聚簇，从头开始写，写到末尾又回到头循环写，如下图所示

从上图可知，redo logfile是顺序写，对于已经持久化的数据将进行覆盖，redo logfile只会记录未被持久化的数据，这对于数据恢复十分重要。

两个redo logfile的逻辑指针：

1. checkpoint：指示未刷盘的数据，从这里开始，编号小于checkpoint的空间表示已经被持久化，可以被覆盖
2. write position：表示当前数据写入的位置

redo logfile是环形结构，如果checkpoint原地不动的话，随着redo log record的不断插入，write position总会追上checkpoint，如果追上了那就没办法再写redo log了，因为checkpoint之后的redo log表示的数据脏页还没有刷盘，是肯定不可以覆盖的。

解决办法也很简单，催着checkpoint也往前走，去“吃掉还未刷盘的数据”，形成一种write position催着checkpoint前进的局面，checkpoint与write position之间的间隔越小，证明还未刷入磁盘的脏页越少，服务重启之后recovery所需的时间就越少，反之则越大。

为什么不直接把修改后的数据页直接刷盘？而是要记录redo log?

实际上，数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，没有必要将整个数据页落盘。

而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能很差。

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值，再加上是顺序写，所以刷盘速度很快。所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强

bin log（归档日志）

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于Server层。不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志

bin log的作用：主从同步。可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写

记录格式

binlog 日志有三种格式，可以通过binlog_format参数指定。

• statement
• row
• mixed

指定statement，记录的内容是SQL语句原文，比如执行一条update T set update_time=now() where id=1，记录的内容如下

同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。

为了解决这种问题，我们需要指定为row，记录的内容不再是简单的SQL语句了，还包含操作的具体数据，记录内容如下

row格式记录的内容看不到详细信息，要通过mysqlbinlog工具解析出来。

update_time=now()变成了具体的时间update_time=1627112756247，条件后面的@1、@2、@3 都是该行数据第 1 个~3 个字段的原始值（假设这张表只有 3 个字段）。

这样就能保证同步数据的一致性，通常情况下都是指定为row，这样可以为数据库的恢复与同步带来更好的可靠性。

但是这种格式，需要更大的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。

所以就有了一种折中的方案，指定为mixed，记录的内容是前两者的混合。

MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式

写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache

我们可以通过binlog_cache_size参数控制单个线程 bin log cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）

• 上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
• 上图的 fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数sync_binlog控制，默认是0。

• 为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync

• 虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。

  为了安全起见，可以设置为1，表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样

• 最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync

两阶段提交

redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了MySQL集群架构的数据一致性

虽然它们都属于持久化的保证，但是侧重点不同。

我们如何保证两个日志的一致性？

如果先写 binlog 再写 redolog

假设我们要向表中插入一条记录 R，如果是先写 binlog 再写 redolog，那么假设 binlog 写完后崩溃了，此时 redolog 还没写。

那么数据恢复的时候就会出问题：binlog中已经有 R 的记录了，当slave从master同步数据的时候或者我们使用 binlog恢复数据的时候，就会同步到 R 这条记录；但是redolog中没有关于 R 的记录，主机会认为该记录已经被持久化到磁盘，所以崩溃恢复之后，插入 R 记录的这个事务是无效的，但是实际上数据库中没有该行记录，这就造成了数据不一致。

如果先写 redolog 再写 binlog

假设我们要向表中插入一条记录 R，如果是先写 redolog 再写 binlog，那么假设 redolog 写完后崩溃了，此时 binlog 还没写。

那么重启恢复的时候也会出问题：redolog 中已经有 R 的记录了，所以崩溃恢复之后，插入 R 记录的这个事务是有效的，通过该记录将数据恢复到数据库中，master数据库是没有问题的。但是 binlog 中还没有关于R的记录，所以当slave从master同步数据的时候或者我们使用 binlog 恢复数据的时候，就不会同步到 R 这条记录，这就造成了数据不一致。

为了解决两份日志之间的一致性问题，InnoDB存储引擎使用两阶段提交方案

原理很简单，将redo log的写入拆成了两个步骤**prepare和commit**

事务提交时就会被拆分成三个步骤：

1. 写入redo log，进入prepare状态（一阶段提交）
2. 写binlog
3. 修改redo log，状态变为 commit（二阶段提交）

一阶段提交崩溃

即redo log prepare状态的时候崩溃了，此时由于 bin log还没写，所以崩溃恢复的时候，这个事务会回滚，也不会传到slave

写完 bin log崩溃

redo log中的日志是不完整的，处于prepare状态，还没有提交，那么恢复的时候，首先检查bin log中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。

redo log commit崩溃

此时bin log已经写入完成，能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据，所以直接提交事务即可

redo log和bin log有什么区别？

1. 层次不同：bin log是在存储引擎的上层产生的，无论是怎么样的存储引擎，对数据库的修改都会产生二进制日志。而redo log是在存储引擎层产生的，innoDB独占，只记录该存储引擎对表的修改，产生时间晚于bin log
2. 记录内容的不同：MySQL的bin log是逻辑日志，其记录是对应的SQL语句，记录顺序与提交顺序有关。而innoDB存储引擎层面的redo log日志是物理日志，redo log记录的是物理页的修改情况，如空间号、数据页号、磁盘文件偏移量。
3. 记录时机不同：bin log只在每次事务提交的时候一次性写入缓存中的日志文件。而redo log保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作，两者通过二阶段提交来保证一致性。
4. binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。

有 bin log为什么还要redo log？

1. bin log 不知道数据库究竟是在哪一时刻丢失了哪部分数据，只能从备份点开始重放bin log 记录来恢复数据，非常耗时
2. bin log 恢复是需要我们手动执行的，而 redo log 可以在服务器重启后自动恢复数据。
3. WAL + 异步刷脏页有效提升了磁盘的 IO 效率。

有 redo log为什么还要bin log？

1. bin log 是Server层的功能，redo log 是innoDB的功能。redo log 帮助 InnoDB 实现了性能提升、自动恢复。但其他存储引擎是无法使用 redo log 的。
2. 我们也可以关闭 bin log，但大多数情况下我们都会开启，因为开启的好处更多。比如，使用bin log 进行主从复制，以及可以通过 bin log 进行数据库的增量备份和恢复。

总结

MySQL InnoDB 引擎使用redo log和bin log保证事务的持久性，使用 undo log 来保证事务的原子性。

MySQL数据库的数据备份、主备、主主、主从都离不开bin log，需要依靠bin log来同步数据，保证数据一致性

undo log

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。

如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

undo log就是一个记录该条记录的旧版本内容的一条记录链，链表头就是最新的旧记录，链表尾部就是最旧的旧纪录

另外，MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 根据可见性算法来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

MySQL的数据恢复

MySQL 崩溃也是一次关闭，只是比正常关闭急了一些

正常关闭时，MySQL 会做一系列收尾工作，例如：清理日志、合并 change buffer缓冲区等操作。

具体会进行哪些收尾工作，取决于系统变量 innodb_fast_shutdown 的配置。

崩溃直接就是戛然而止，直接不干了，还没来得及进行的那些收尾工作就只能等待下次启动的时候再干了，这就是数据恢复

两次写

MySQL 一旦崩溃，Redo log就要出马了，使用Redo log把还没来得及刷盘的脏页恢复到崩溃之前那一刻的状态。

虽然 Redo 日志能够用来恢复数据页，但这是有前提条件的：数据页必须完好无损的状态。如果数据页刚写了一半，MySQL 就戛然而止，这个数据页就损坏了，面对这种情况，Redo log也没办法了

Redo log要输了吗？那显然是不会的，这就该轮到两次写上场了。

两次写的官方名字是 double write，它包含内存缓冲区和dblwr文件两个部分，InnoDB脏页刷盘前，都会先把脏页写入内存缓冲区，再写入dblwr文件，成功之后才会刷盘，两次写会降低性能，所以选择性地打开

我们会遇到两种情况：

1. 如果脏页写入内存缓冲区和 dblwr文件的程中，MySQL 崩溃了，表空间中对应的数据页还是完整的，下次启动时，不需要用两次写页面修复这个数据页。
2. 如果脏页刷盘时，MySQL 崩溃了，表空间对应的数据页损坏了，下次启动时，使用Redo log恢复数据时，需要用两次写页面修复这个数据页。

总之，double write可以保证dblwr文件和本地数据文件中总有一份干净的内容。