MySQL中的redo log和undo log

MySQL中的redo log和undo log

MySQL日志系统中最重要的日志是 重做日志redo log 和 归档日志bin log ，后者为MySQL Server前者是层层日志InnoDB存储引擎层的日志。

1 重做日志redo log

1.1 什么是redo log

redo log保证事务的持久性，即ACID中的D。

持久性：指一旦提交事务，它对数据库中数据的变化是永久性的，即使数据库出现故障，也不应对其产生任何影响。

redo log物理重做日志和逻辑重做日志有两种类型。 在InnoDB中redo log在大多数情况下，它是记录数据页面物理变化(实际数据值)的物理日志。

1.2 redo log的功能

redo log数据库崩溃时的数据恢复是主要功能。

1.3 redo log的组成

redo log可分为以下两部分

• 存储在内存中的重做日志缓冲区

• 存储在磁盘上的重做日志文件

编辑

添加图片注释不得超过 140 字（可选）

1.4 记录redo log的时机

• 在 数据修改完成后，脏页刷入磁盘前 写入重做日志缓冲区。也就是说，先修改，然后再写入。脏页:内存中与磁盘不一致的数据(不坏！

• 在下列情况下，redo log磁盘上的重做日志文件由重做日志缓冲区写入。redo log buffer的日志占据redo log buffer总容量的一半是 ，将redo log写入磁盘。提交事务时 ，他的redo log刷入磁盘，以确保数据永远不会丢失（最常见的情况）。请注意，此时内存中的脏页可能还没有全部写入磁盘。定期刷新后台线程 ，每隔一秒就会有一个后台线程redo log写入磁盘。MySQL关闭时 ，redo log都被写入磁盘。第一种情况和第四种情况执行redo log第二种情况和第三种情况的根据参数执行 innodb_flush_log_at_trx_commit 下面将详细描述设定值。

• 创建索引还需要记录redo log。

1.5 一个重做整个过程的例子

编辑

添加图片注释不得超过 140 字（可选）

以更新事务为例。

1. 将原始数据读入内存，修改数据的内存副本。

2. 生成redo log并写入重做日志缓冲区，redo log修改后的新值存储在中间。

3. 提交事务时，将重做日志缓冲区的内容刷新到重做日志文件。

4. 然后将内存中的脏页刷回磁盘。

1.6 保证持久性

1.6.1 Force Log at Commit机制

Force Log at Commit该机制实现了事务的持久性。 在内存中操作时，将日志写入重做日志缓冲区。但在提交事务之前，所有日志都必须写在磁盘上。

为了确保每个日志都被写入重做日志文件，必须使用一个fsync系统调用，确保OS buffer中间日志完全写在磁盘上log file。

fsync系统调用：您需要在入参位置将其传递给他fd，然后系统调用这个fd指向文件起作用。fsync它将确保直到磁盘操作结束才返回，因此当您的程序使用此函数并成功返回时，这意味着数据必须安全下降。fsync适用于数据库。

编辑

添加图片注释不得超过 140 字（可选）

1.6.2 innodb_flush_log_at_trx_commit参数

InnoDB提供参数 innodb_flush_log_at_trx_commit 将控制日志刷新到磁盘的策略。

• 当 innodb_flush_log_at_trx_commit 值为1时(默认)。 每次每次提交都必须提交log buffer写入中日志os buffer并调用fsync()写入磁盘。这样，即使系统崩溃，也不会丢失任何数据，但每次提交都会写入磁盘，IO性能较差。

• 当 innodb_flush_log_at_trx_commit 值为0时。 不会提交事务log buffer写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。这实际上相当于在内存中维护用户设计的缓冲区，减少和os buffer数据传输之间，性能更好。每秒写入磁盘，系统崩溃会丢失1s的数据。

• 当 innodb_flush_log_at_trx_commit 值为2时。 每次提交都只写入os buffer，然后每秒调用fsync()将os buffer写入中日志log file on disk中。虽然我们每秒都在调用fsync()将os buffer写入中日志log file on disk但平时即使不调用fsync，数据也将逐渐独立进入磁盘。因此，当系统崩溃时，与第二种情况相比，数据丢失较少。但与此同时，每次提交都被写入os buffer，所以性能会比第二种情况差，但还是比第一种好。

• 不管是什么情况

编辑

添加图片注释不得超过 140 字（可选）

1.6.3 性能测试小

几个选项之间的性能差距很大，下面是一个简单的测试。

#创建测试表 drop table if exists test_flush_log; create table test_flush_log(id int,name char(50))engine=innodb; #在测试表中记录插入指定行数的存储过程 drop procedure if exists proc; delimiter $$ create procedure proc(i int) begin declare s int default 1; declare c char(50) default repeat('a',50); while s<=i do start transaction; insert into test_flush_log values(null,c); commit; set s=s 1; end while; end$$ delimiter ;

下面插入10万条记录。

Ⅰ 当innodb_flush_log_at_trx_commit值为1时

test> call proc(100000) [2021-07-25 13:22:02] completed in 27 s 350 ms

需要长达27.35s。

Ⅱ 当innodb_flush_log_at_trx_commit值为2时

test> set @@global.innodb_flush_log_at_trx_commit=2; test> truncate test_flush_log; test> call proc(100000) [2021-07-25 13:27:33] completed in 5 s 774 ms

只需5.774s，性能大大提高。

Ⅲ 当innodb_flush_log_at_trx_commit值为0时

test> set @@global.innodb_flush_log_at_trx_commit=0; test> truncate test_flush_log; test> call proc(100000) [2021-07-25 13:30:34] completed in 3 s 537 ms

只需3.537s，性能更高。

显然，innodb_flush_log_at_trx_commit当值为1时，性能非常差，改为0和2后的性能有了很大的提升，其中0更快，但与2相比没有太大的提升。

虽然改为0和2可以大大提高性能，但会严重影响安全。 通过修改存储过程，可以将事务的创建和提交放在循环之外，统一提交，减少IO频率。

drop procedure if exists proc; delimiter $$ create procedure proc(i int) begin declare s int default 1; declare c char(50) default repeat('a',50); star transaction; while s<=i DO insert into test_flush_log values(null,c); set s=s+1; end while; commit; end$$ delimiter ;

1.6.4 迷你事务mini-transaction

mini-trasaction是InnoDB处理小型事务时使用的一种机制，它可以确保 并发事务操作和数据库异常发生时，数据页中的数据一致性。

迷你事务必须遵循下面三个协议：

• FIX规则。写时必须使用独占锁，读时必须使用共享锁。反正就是要锁住。

• 预写日志。预写日志即WAL，Write-Ahead Log。持久化数据之前，必须先持久化内存中的日志。每个页面都有一个LSN（日志序列号）。在将数据写入磁盘前，要先将内存中序列号小于LSN的日志写入磁盘。WAL提供三种持久化模式最严格的是full-sync，fsync保证在返回之前将记录刷新到磁盘，最大化了数据的安全性。

​

编辑

添加图片注释，不超过 140 字（可选）

• 第二个级别是write-only，保证记录写入操作系统。这允许数据在进程级别的崩溃后幸存。

​

编辑

添加图片注释，不超过 140 字（可选）

• 最不严格的是no-sync，将记录保存在内存缓冲区中，不保证立即写入文件系统。

​

编辑

添加图片注释，不超过 140 字（可选）

• 强制日志再提交。 即Force-log-at-commit，它要求提交事务时必须把所有迷你事务日志刷新到磁盘。

1.7 写redo log的过程

​

编辑

添加图片注释，不超过 140 字（可选）

如上图，展示了redo log是如何被写入log buffer的。 每个mini-trasaction对应于每个DML操作 ，例如更新语句等。

• 每个数据修改后被写入迷你事务私有缓冲区。

• 当更新语句完成，redo log从迷你事务私有缓冲区被写入内存中的公共日志缓冲区。

• 提交外部事务时，会将重做日志缓冲区刷入重做日志文件。

1.8 日志块 log block

redo log以块为单位进行存储，每个块大小为512字节。无论是在内存重做日志缓冲区、操作系统缓冲区还是重做日志文件中，都是以这样的512字节大小地块进行存储的。

​

编辑

添加图片注释，不超过 140 字（可选）

每个日志块头由以下四个部分组成

• log_block_hdr_no：(4字节)该日志块在redo log buffer中的位置ID。

• log_block_hdr_data_len：(2字节)该log block中已记录的log大小。写满该log block时为0x200，表示512字节。

• log_block_first_rec_group：(2字节)该log block中第一个log的开始偏移位置。

• lock_block_checkpoint_no：(4字节)写入检查点信息的位置。

1.9 log group

log group代表redo log的分组，由多个大小相同的redo log file组成。由一个参数 innodb_log_files_group 决定，默认为2。

[外链图片转存失败,源站可能有防盗 img-qAyaSeL3543740G:61311akw89MySQL[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h01w68EG-1627284031849)(G:\markdown\MySQL\image-20210726131134489.png)].png)]

这个group是逻辑上的概念，但可以通过变量 innodb_log_group_home_dir 来定义组的目录，redo log file都放在这个目录下，默认是在datadir下。

​

编辑

添加图片注释，不超过 140 字（可选）

2 撤销日志undo log

2.1 关于undo log

• undo log存在的意义是确保数据库事务的原子性。原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

• redo log记录了事务的行为，可以很好地保证一致性，对数据进行“重做”操作。但事务有时还需要进行“回滚”操作，这时就需要undo log。当我们对记录做了变更操作的时候就需要产生undo log，其中记录的是老版本的数据，当旧事务需要读取数据时，可以顺着undo链找到满足其可见性的记录。

• undo log通常以逻辑日志的形式存在。我们可以认为当delete一条记录时，undo log会产生一条对应的insert记录，反之亦然。当update一条记录时，会产生一条相反的update记录。

• undo log采用段segment的方式来记录，每个undo操作在记录的时候占用一个undo log segment。

• undo log也会产生redo log，因为undo log也要实现持久性保护。

2.2 undo log segment

为了保证事务并发操作时，写各自的undo log时不发生冲突，nnodb用段的方式管理undo log。 rollback segment称为回滚段，每个回滚段中有1024个undo log segment。 MySQL5.5以后的版本支持128个rollback segment，就可以存储128*1024个操作，还可以通过 innodb_undo_logs 参数定义盯梢个rollback segment。

​

编辑

添加图片注释，不超过 140 字（可选）

2.3 purge

在聚集索引列的操作中，MySQL是这样设计的。对一条delete语句

delete from t where a = 1

假如a有聚集索引（主键），那么不会进行真正的删除，而是在主键列等于1的记录处设置delete flag为1，即把记录保存在B+树中。同理，对于update操作，不是直接更新记录，而是把旧记录标识给删除，再创建一条新记录。

那么，旧版本记录什么时候真正的删除呢？

InnoDB使用undo日志进行旧版本的删除操作，这个操作称为purge操作。InnoDB开辟了purge线程进行purge操作，并且可以控制purge线程的数量，每个purge线程每10s 进行一次purge操作。

InnoDB的undo log设计

一个页上允许多个事务的undo log存在，undo log的存储顺序是随时的。 InnoDB维护了一个history链表，按照事务提交的顺序将undo log进行连接。

​

编辑

添加图片注释，不超过 140 字（可选）

在执行purge过程中，InnoDB存储引擎首先从history list中找到第一个需要被清理的记录，这里为trx1，清理之后InnoDB存储引擎会在trx1所在的Undo page中继续寻找是否存在可以被清理的记录，这里会找到事务trx3，接着找到trx5，但是发现trx5被其他事务所引用而不能清理，故再去history list中去查找，发现最尾端的记录时trx2，接着找到trx2所在的Undo page，依次把trx6、trx4清理，由于Undo page2中所有的记录都被清理了，因此该Undo page可以进行重用。

InnoDB存储引擎这种先从history list中找undo log，然后再从Undo page中找undo log的设计模式是为了避免大量随机读操作，从而提高purge的效率。

3 InnoDB的恢复操作

3.1 数据页刷盘的规则和checkpoint

内存中(buffer pool)未刷到磁盘的数据称为脏数据(dirty data)。由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。

在InnoDB中，checkpoint是数据刷盘的唯一规则。checkpoint触发后，会将内存中的脏数据刷到磁盘。

innodb存储引擎中checkpoint分为两种：

• sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。

• fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：

• master thread checkpoint。由master线程控制， 每秒或每10秒 刷入一定比例的脏页到磁盘。

• flush_lru_list checkpoint。从MySQL5.6开始可通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。

• async/sync flush checkpoint。同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘

• dirty page too much checkpoint。脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例有变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。

由于刷脏页需要一定的时间来完成，所以记录检查点的位置是在每次刷盘结束之后才在redo log中标记的。

3.2 LSN

3.2.1 LSN概念

LSN称为日志的逻辑序列号，在InnoDB中占用8个字节

我们可以通过LSN了解到下面这些信息：

• 数据页的版本信息。

• 写入的日志总量。

• 检查点的位置。

在下面两个位置存在LSN：

fil_page_lsn

显然，如果页中的LSN值小于redo log中的LSN值，说明数据出现了丢失。

通过 show engine innodb status 可以查看当前InnoDB的运行信息，其中有一栏log中有关于lsn的记录。

​

编辑

添加图片注释，不超过 140 字（可选）

• log sequence number记录了当前的redo log(in buffer)中的LSN。

• log flushed up to是刷到磁盘重做日志文件中的LSN。

• pages flushed up to是已经刷到磁盘数据页上的LSN。

• last checkpoint at是上一次检查点所在位置的LSN。

3.2.2 LSN处理流程

(1).首先修改内存中的数据页，并在数据页中记录LSN，暂且称之为data_in_buffer_lsn；

(2).并且在修改数据页的同时(几乎是同时)向redo log in buffer中写入redo log，并记录下对应的LSN，暂且称之为redo_log_in_buffer_lsn；

(3).写完buffer中的日志后，当触发了日志刷盘的几种规则时，会向redo log file on disk刷入重做日志，并在该文件中记下对应的LSN，暂且称之为redo_log_on_disk_lsn；

(4).数据页不可能永远只停留在内存中，在某些情况下，会触发checkpoint来将内存中的脏页(数据脏页和日志脏页)刷到磁盘，所以会在本次checkpoint脏页刷盘结束时，在redo log中记录checkpoint的LSN位置，暂且称之为checkpoint_lsn。

(5).要记录checkpoint所在位置很快，只需简单的设置一个标志即可，但是刷数据页并不一定很快，例如这一次checkpoint要刷入的数据页非常多。也就是说要刷入所有的数据页需要一定的时间来完成，中途刷入的每个数据页都会记下当前页所在的LSN，暂且称之为data_page_on_disk_lsn。

​

编辑

添加图片注释，不超过 140 字（可选）

上图中，从上到下的横线分别代表：时间轴、buffer中数据页中记录的LSN(data_in_buffer_lsn)、磁盘中数据页中记录的LSN(data_page_on_disk_lsn)、buffer中重做日志记录的LSN(redo_log_in_buffer_lsn)、磁盘中重做日志文件中记录的LSN(redo_log_on_disk_lsn)以及检查点记录的LSN(checkpoint_lsn)。

假设在最初时(12:0:00)所有的日志页和数据页都完成了刷盘，也记录好了检查点的LSN，这时它们的LSN都是完全一致的。

假设此时开启了一个事务，并立刻执行了一个update操作，执行完成后，buffer中的数据页和redo log都记录好了更新后的LSN值，假设为110。这时候如果执行 show engine innodb status 查看各LSN的值，即图中①处的位置状态，结果会是：

log sequence number(110) > log flushed up to(100) = pages flushed up to = last checkpoint at

之后又执行了一个delete语句，LSN增长到150。等到12:00:01时，触发redo log刷盘的规则(其中有一个规则是 innodb_flush_log_at_timeout 控制的默认日志刷盘频率为1秒)，这时redo log file on disk中的LSN会更新到和redo log in buffer的LSN一样，所以都等于150，这时 show engine innodb status ，即图中②的位置，结果将会是：

log sequence number(150) = log flushed up to > pages flushed up to(100) = last checkpoint at

再之后，执行了一个update语句，缓存中的LSN将增长到300，即图中③的位置。

假设随后检查点出现，即图中④的位置，正如前面所说，检查点会触发数据页和日志页刷盘，但需要一定的时间来完成，所以在数据页刷盘还未完成时，检查点的LSN还是上一次检查点的LSN，但此时磁盘上数据页和日志页的LSN已经增长了，即：

log sequence number > log flushed up to 和 pages flushed up to > last checkpoint at

但是log flushed up to和pages flushed up to的大小无法确定，因为日志刷盘可能快于数据刷盘，也可能等于，还可能是慢于。但是checkpoint机制有保护数据刷盘速度是慢于日志刷盘的：当数据刷盘速度超过日志刷盘时，将会暂时停止数据刷盘，等待日志刷盘进度超过数据刷盘。

等到数据页和日志页刷盘完毕，即到了位置⑤的时候，所有的LSN都等于300。

随着时间的推移到了12:00:02，即图中位置⑥，又触发了日志刷盘的规则，但此时buffer中的日志LSN和磁盘中的日志LSN是一致的，所以不执行日志刷盘，即此时 show engine innodb status 时各种lsn都相等。

随后执行了一个insert语句，假设buffer中的LSN增长到了800，即图中位置⑦。此时各种LSN的大小和位置①时一样。

随后执行了提交动作，即位置⑧。默认情况下，提交动作会触发日志刷盘，但不会触发数据刷盘，所以 show engine innodb status 的结果是：

log sequence number = log flushed up to > pages flushed up to = last checkpoint at

最后随着时间的推移，检查点再次出现，即图中位置⑨。但是这次检查点不会触发日志刷盘，因为日志的LSN在检查点出现之前已经同步了。假设这次数据刷盘速度极快，快到一瞬间内完成而无法捕捉到状态的变化，这时 show engine innodb status 的结果将是各种LSN相等。

3.3 InnoDB的恢复行为

启动InnoDB时，一定会进行恢复操作，无论上次是因为什么原因退出。

checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时仅需要恢复从checkpoint开始的日志部分。例如，当数据库在上一次checkpoint的LSN为10000时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从检查点开始恢复。

还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机，数据页中记录的LSN就会大于日志页中的LSN，在重启的恢复过程中会检查到这一情况，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

另外， 事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。 而二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多。例如，某记录中id初始值为2，通过update将值设置为了3，后来又设置成了2，在事务日志中记录的将是无变化的页，根本无需恢复；而二进制会记录下两次update操作，恢复时也将执行这两次update操作，速度比事务日志恢复更慢。