事务日志

数据库的事务日志主要分三类，分别是**Binlog**、**Redo Log**、**Undo Log**。

## Binlog

### 含义

1. Binlog 记录了对 MySQL 数据库**执行更改**的所有操作，但是**不包括 `SELECT` 和 `SHOW` 这类操作**，但是**若操作本身并没有导致数据库发生变化**，那么**该操作可能也会写入 Binlog**，如`update t set a = 1 where a = 2;`，但是表`t` 中并不存在`a = 2` 的巨鹿，此时并不会对数据库进行更改，但是这个操作也会写入 Binlog。
2. Binlog 主要有以下几种作用：
   1. **恢复：** 某些数据的恢复需要 Binlog，例如，在一个数据库**全备文件恢复后**，用户可以**通过 Binlog 进行 point-in-time 的恢复**（将数据恢复到一个给定的时间点，通常是在进行全量备份之后进行）。
   2. **复制：** 其原理与恢复类似，**通过复制和执行 Binlog 使一台远程的 MySQL 数据库**（一般称为 slave 或 standby）**与一台 MySQL 数据库**（一般称为 master 或 primary）**进行实时同步**。
   3. **审计：** 用户可以**通过 Binlog 中的信息进行审计**，**判断是否有对数据库进行注入的攻击**。

### 常用参数

####  max_binlog_size

1. `max_binlog_size` 指定了**单个 binlog 文件的最大值**，如果**超过该值**，**将产生一个新的 binlog**，**后缀名加 1**，**并记录到 `.index` 文件**。
2. 从 MySQL 5.0 开始的**默认值为**1073741824，代表**1G**，在之前版本中`max_binlog_size` 默认大小为 1.1G。

#### binlog_cache_size

1. 当**使用事务的表存储引擎**（如 InnoDB 存储引擎）时，所有**未提交的 binlog 会被记录到一个缓存中**，等到改该**事务提交时直接将缓冲中的 binlog 写入 binlog 文件**，而该**缓冲的大小由 `binlog_cache_size` 决定**，**默认大小为 32K**。
2. `binlog_cache_size`**是基于会话的**，也就是说，**当一个线程开始一个事务时**，MySQL**会自动分配一个大小为 `binlog_cache_size` 的缓存**，因此，该值的设置需要相当小心，**不能设置过大**，**当一个事务的记录大于设定的 `binlog_cache_size` 时**，MySQL**会把缓冲中的日志写入一个临时文件**中，因此该值**又不能设得太小**。

#### sync_binlog

1. 在默认情况下，binlog 并不是在每次写的时候同步到磁盘，因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入 binlog 文件中，这会给恢复和复制带来问题。
2. **参数 `sync_binlog = [N]` 表示每写缓冲多少次就同步到磁盘**，**默认值为 0**，**如果 N 设为 1**，**表示采用同步写磁盘的方式来写 binlog**，**这时写操作不使用缓冲来写 binlog**。
3. 但是，**即使将 `sync_binlog` 设为 1**，还是会有一种情况导致问题的发生，当使用 InnoDB 存储引擎时，**在一个事务发出 `COMMIT` 动作之前**，由于`sync_binlog` 为 1，因此**会将 binlog 立即写入磁盘**，**如果这时已经写入了 binlog**，**但是提交还没有发生**，**并且此时发生了宕机**，那么在 MySQL 数据库**下次启动时**，由于**`COMMIT` 操作并没有发生**，这个**事务会被回滚**掉，但是**二进制日志已经记录了该事务信息**，**不能被回滚**，**这个问题可以通过将参数 `innodb_support_xa` 设为 1 来解决**，虽然`innodb_support_xa` 与`XA` 事务有关，但他同时也**确保了 `binlog` 和 InnoDB 存储引擎数据文件的同步**。

#### binlog-do-db 和 binlog-ignore-db

1. `binlog-do-db` 和`binlog-ignore-db` 表示**需要写入或忽略写入哪些库的日志**，**默认为空**，**表示需要同步所有库的日志到 `binlog`**。

#### log-slave-update

1. **如果当前数据库是复制中的 `slave` 角色**，**则他不会将从 `master` 取得并执行的 binlog 写入到自己的 binlog 文件中去**，**如果需要写入**，**要设置 `log-slave-update`**。
2. **如果需要搭建 `master => slave => slave` 架构的复制**，则**必须设置该参数**。

#### binlog_format

1. `binlog_format` 参数十分重要，他**影响了记录 binlog 的格式**。

2. 在**MySQL 5.1 版本之前**，没有这个参数，**所有 binlog 的格式都是基于 SQL 语句级别的**，但是**如果在主服务器运行 `rand`、`uuid` 等函数**，又**或者使用触发器等操作**，这些都可能**会导致主从服务器上表中数据的不一致**。

3. MySQL 5.1 开始引入了 `binlog_format` 参数，该参数可设的值有 `STATEMENT`、`ROW`、`MIXED`，每个参数的具体含义如下：

1. `STATEMENT` 格式和之前的 MySQL 版本一样，**binlog 记录的是日志的逻辑 SQL 语句**。
   2. 在`ROW` 格式下，binlog 记录的不再是简单的 SQL 语句了，而是**记录表的行更改情况**。
   3. 在`MIXED` 格式下，MySQL**默认采用 `STATEMENT` 格式进行二进制日志文件的记录**，但是**在一些情况下会使用 `ROW` 格式**，例如：
      1. **表的存储引擎为 `NDB`**，**这时对表的 `DML` 操作都会以 `ROW` 格式记录**。
      2. **使用了 `UUID()`、`USER()`、`CURRENT_USER()`、`FOUND_ROWS()`、`ROW_COUNT()` 等不确定函数**。
      3. **使用了 `INSERT DELAY` 语句**。
      4. **使用了用户定义函**数。
      5. **使用了临时表**。

4. `binlog_format` 是**动态参数**，因此**可以在数据库运行环境下更改**，例如：

```sql
   SET @session.binlog.format = 'ROW';
   ```

5. 在**通常情况下**，我们**将参数 `binlog_format` 设置为 `ROW`**，这样**可以为数据库的恢复和复制带来更好的可靠性**，但是**会带来 binlog 文件大小的增加**，而且由于**复制是采用传输 binlog 的方式实现的**，因此**复制的网络开销也会有所增加**。

## Redo Log

### Redo Log 与 Binlog 的区别

1. **Binlog 是在存储引擎的上层产生的**，**不管是什么存储引擎**，**对数据库的修改都会产生 Binlog**，而**Redo Log 是 InnoDB 层产生的**，**只记录该存储引擎中表的修改**，并且**Binlog 先于 Redo Log 被记录**。
2. **Binlog 记录操作的方法是逻辑性的语句**，即便他是基于 ROW 格式的记录方式，其**本质**也还**是逻辑的 SQL 设置**，而**Redo Log 是物理格式的日志**，它**记录的是数据库中每个页的修改**。
3. **Binlog 只在每次事务提交的时候一次性写入缓存中的日志文件**，而**Redo Log 在数据准备修改前要先写入缓存中的 Redo Log 中**，**然后才对缓存中的数据执行修改操作**，而且保证**在发出事务提交指令时**，**先向缓存中的 Redo Log 写入日志**，**写入完成后才执行提交动作**。
4. 因为 Binlog 只在提交的时候一次性写入，所以**Binlog 中的记录方式和提交顺序有关**，且**一次提交对应一次记录**，而**Redo Log 中记录的是物理页的修改**，Redo Log 文件中**同一事务可能多次记录**，**最后一个提交的事务记录会覆盖所有未提交的事务记录**，例如事务 $T_1$，可能在 Redo Log 中记录了 $T_{11}$、$T_{12}$、$T_{13}$、$T_{1*}$ 共 4 个操作，其中 $T_{1*}$ 表示最后提交时的日志记录，所以对应的数据页最终状态是 $T_{1*}$ 对应的操作结果，而且**Redo Log 是并发写入的**，**不同事务之间的不同版本的记录会穿插写入到 Redo Log 文件中**，例如可能 Redo Log 的记录方式为 $T_{11}$、$T_{12}$、$T_{21}$、$T_{22}$、$T_{2*}$、$T_{13}$、$T_{1*}$。
5. **Redo Log 记录的是物理页的情况**，它**具有幂等性**，因此**记录日志的方式极其简练**，幂等性的意思是多次操作前后状态是一样的，例如新插入一行又删除该行，前后状态没有变化，而**Binlog 记录的是所有影响数据的操作**，**记录的内容较多**，例如插入一行记录一次，删除该行又记录一次。

### 含义

1. **Redo Log 包括两部分**，一部分是**内存中的日志缓冲**（Redo Log Buffer），这部分日志是**易失**的，另一部分是磁盘上的**重做日志文件**（Redo Log File），这部分日志是**持久**的。

2. **InnoDB 通过 Force Log at Commit 机制实现事务的持久性**，即**在事务提交的时候**，**必须先将该事务的所有事务日志写入到磁盘上的 Redo Log File 和 Undo Log File 中进行持久化**。

3. **为了确保每次日志都能写入到日志文件中**，在**每次将 Log Buffer 中的日志写入到日志文件的过程中都会调用一次操作系统的 `fsync` 操作**，即 **`fsync()` 系统调用**，因为**MySQL**是**工作在用户空间**的，**其 Log Buffer 处于用户空间的内存中**，**要写入到磁盘上的 Log File 中**，**中间还要经过操作系统内核空间的 OS Buffer**，**调用 `fsync()` 就是将 OS Buffer 中的日志刷到磁盘上的 Log File 中**。

![](https://notebook.ricear.com/media/202106/2021-06-27_163015.png)

> 1. 在此处需要注意的一点是，**一般所说的 Log File 并不是磁盘上的物理日志文件**，**而是操作系统缓存中的 Log File**，但这本身不太容易理解，既然都称为 file 了，应该已经属于物理文件了，所以**在本文后续内容中都以 OS Buffer 或 File System Buffer 来表示官方所说的 file**，然后**Log File 则表示磁盘上的物理日志文件**，**即 Log File on Disk**。
   > 2. **之所以要经过一层 OS Buffer**，**是因为打开日志文件的时候**，**没有使用 `O_DIRECT` 标志位**，**该标志位意味着绕过操作系统层的 OS Buffer**，**IO 直写到底层存储设备**，**不使用该标志位意味着将日志进行缓冲**，**缓冲到了一定容量**，**或者显式 `fsync()` 才会将缓冲中的刷到存储设备**，**使用该标志位意味着每次都要发起系统调用**，比如写`abcde`，**不使用 `O_DIRECT` 将只发起一次系统调用**，使用`O_DIRECT` 将发起 5 次系统调用。

4. MySQL 支持用户自定义在 Commit 时如何将 Buffer 中的日志刷到 Log File 中，这种控制通过变量 `innodb_flush_log_at_trx_commit` 的值来决定，该变量有三种值，分别是 0、1、2，默认是 1，具体如下：

1. 当设置为**0**的时候，**事务提交时不会将 Log Buffer 中的日志写入到 OS Buffer**，**而是每秒写入 OS Buffer 并调用 `fsync()` 写入到 Log File on Disk 中**，也就是说设置为 0 时是**每秒刷新写入到磁盘中**的，当**系统崩溃**，**会丢失 1 秒钟的数据**。

2. 当设置为**1**的时候，**事务每次提交都会将 Log Buffer 中的日志写入 OS Buffer**，**并调用 `fsync()` 刷到 Log File on Disk 中**，这种方式及时**系统崩溃**也**不会丢失任何数据**，但是因为**每次都写入磁盘**，**IO 性能较差**。

3. 当设置为**2**的时候，**每次提交都仅写入到 OS Buffer**，**然后是每秒调用 `fsync()` 将 OS Buffer 中的日志写入到 Log File on Disk**。

![](https://notebook.ricear.com/media/202106/2021-06-27_165359.png)

5. 在**主从复制结构**中，要**保证事务的持久性和一致性**，需要对日志相关变量设置如下：

1. 如果**启用了 Binlog**，则**设置 `sync_binlog = 1`**，即**每提交一次事务**，**同步写到磁盘中**。
   2. 总是**设置 `innodb_flush_log_at_trx_commit = 1`**，即**每提交一次事务都写到磁盘中**。
      上面两项变量的设置保证了**每次提交事务都写入二进制日志和事务日志**，**并在提交的时候将他们刷新到磁盘中**。

### 测试刷日志的时间对数据修改性能的影响

选择刷日志的时间会严重影响数据修改时的性能，特别是刷到磁盘的过程，下面测试 `innodb_flush_log_at_trx_commit` 分别设置为 0、1、2 时的差距：

1. 首先创建表和相应的存储过程：

```sql
   #创建测试表
   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 ;
   ```

2. 当前环境下，`innodb_flush_log_at_trx_commit` 的值为 1，即每次提交都刷日志到磁盘，测试此时插入 10W 条记录的时间：

```shell
   mysql> call proc(10000);
   Query OK, 0 rows affected (1 min 4.79 sec)
   ```

用时 1 分 1.79 秒。

3. 再测试值为 2 的时候，即每次提交都刷新到 OS Buffer，但每秒才刷入到磁盘中：

```shell
   mysql> set @@global.innodb_flush_log_at_trx_commit=2;
   mysql> truncate test_flush_log;
   
   mysql> call proc(10000);
   Query OK, 0 rows affected (29.84 sec)
   ```

结果插入时间大减，只需 20.84 秒。

4. 最后测试值为 0 的时候，即每秒才刷到 OS Buffer 和磁盘：

```shell
   mysql> set @@global.innodb_flush_log_at_trx_commit=0;
   mysql> truncate test_flush_log;
   
   mysql> call proc(10000);
   Query OK, 0 rows affected (31.81 sec)
   ```

结果用时 31.81 秒。

5. 最后可以发现，其实**值为 2 和 0 的时候**，他们的**差距并不大**，**但 2 却比 0 要安全的多**，**他们都是每秒从 OS Buffer 刷到磁盘**，他们之间的**差距体现在 Log Buffer 刷到 OS Buffer 上**，**因为将 Log Buffer 中的日志刷新到 OS Buffer 只是内存数据的转移**，**并没有太大的开销**，**所以每次提交和每秒刷入差距并不大**，**但值为 1 的性能却差很多**，**尽管设置为 0 和 2 可以大幅度提升插入性能**，**但是在故障的时候可能会丢失 1 秒钟的数据**，**这 1 秒钟很可能有大量的数据**，从上面的测试结果看，1W 条记录只消耗了 30 多秒，1 秒钟大约有 300-400 条数据，尽管上述插入数据的简单，但却说明了丢失数据的大量性，**更好的插入数据的做法是将值设置为 1**，**然后修改存储过程**，**将每次循环都提交修改为只提交一次**，**这样既能保证数据的一致性**，**也能提升性能**，修改如下：

```sql
   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);
       start transaction;
       while s<=i DO
           insert into test_flush_log values(null,c);
           set s=s+1;
       end while;
       commit;
   end$$
   delimiter ;
   ```

6. 测试值为 1 的情况：

```shell
   mysql> set @@global.innodb_flush_log_at_trx_commit=1;
   mysql> truncate test_flush_log;
   
   mysql> call proc(10000);
   Query OK, 0 rows affected (1.06 sec)
   ```

### Log Block

1. InnoDB 存储引擎中，Redo Log 是**以块为单位进行存储**的，**每个块占 512 字节**，这称为**Redo Log Block**，所以不管是 Log Buffer、OS Buffer，还是 Redo Log File on Disk 中，都是这样以 512 字节的块存储的。

2. **每个 Redo Log Block 由 3 部分组成**，分别是**日志块头**、**日志主体**和**日志块尾**，其中**日志块头占用 12 字节**，**日志主体占用 492 字节**，**日志块尾占用 8 字节**。
   ![](https://notebook.ricear.com/media/202106/2021-06-28_094123.png)

3. 因为 Redo Log 记录的是数据页的变化，当**一个数据页产生的变化需要使用超过 492 字节的 Redo Log 记录**，那么就**会使用多个 Redo Log Block 来记录该数据页的变化**。

4. 日志块头包含 4 部分，具体如下：

1. `log_block_hdr_no`：**占用 4 字节**，表示**该日志块在 Redo Log Buffer 中的位置 ID**。

2. `log_block_hdr_data_len`：**占用 2 字节**，表示**该日志块中已记录的日志大小**，**写满该日志块时为 `0x200`**，**表示 512 字节**。

3. `log_block_first_rec_group`：**占用 2 字节**，表示**该日志块中第一个日志的开始偏移位置**。

4. `log_block_checkpoint_no`：**占用 4 字节**，表示**写入检查点信息的位置**。

> 关于日志块头的第三部分 `log_block_first_rec_group`：
      >
      > 1. 因为有时候一个数据页产生的日志量超出了一个日志块，这时需要用多个日志块来记录该页的相关日志。
      > 2. 例如某一数据页产生了 552 字节的日志量，那么需要占用两个日志块，第一个日志块占用 492 字节，第二个日志块需要占用 60 个字节，那么对于第二个日志块来说，他的第一个日志的开始位置就是 73 字节（60+12），如果该部分的值和`log_block_hdr_data_len` 相等，则说明该日志块中没有新开始的日志块，即表示该日志块用来延续前一个日志块。

5. 日志主体主要由 4 部分组成：

1. `redo_log_type`：占用 1 字节，表示 Redo Log 的日志类型。

2. `space`：采用压缩的方式后，占用空间可能小于 4 字节，表示表空间的 ID。

3. `page_no`：同样是压缩过的，表示页的偏移量。

4. `redo_log_body`：表示每个 Redo Log 的数据部分，恢复时会调用相应的函数进行解析，不同类型的 SQL 语句写入 Redo Log 的内容是不一样的，例如下面的`insert` 和`delete` 的记录方式：![](https://notebook.ricear.com/media/202106/2021-06-28_113659.png)

> 1. 因为 InnoDB 存储引擎数据的单元是页，所以 Redo Log 也是基于页的格式来记录的。
      > 2. 默认情况下，InnoDB 的页大小是 16KB（由`innodb_page_size` 变量控制），一个页内可以存放非常多的 Log Block（每个 512 字节），而 Log Block 中记录的又是数据页的变化。

6. **日志尾只有一个部分 `log_block_trl_no`**，该值**和日志块头的 `log_block_hdr_no` 相等**。

7. 上面所说的是一个日志块的内容，**在 Redo Log Buffer 或者 Redo File on Disk 中**，**由很多日志块组成**，如下图所示：

![](https://notebook.ricear.com/media/202106/2021-06-28_100022.png)

### Log Group 和 Redo Log File

1. Log Group 表示的是 Redo Log Group，**一个组内由多个大小完全相同的 Redo Log File 组成**，**组内 Redo Log File 的数量由变量 `innodb_log_files_group` 决定**，**默认值为 2**，**即两个 Redo Log File**。

2. 这个组是一个**逻辑的概念**，并**没有真正的文件来表示这是一个组**，但是**可以通过变量 `innodb_log_group_home_dir` 来定义组的目录**，**Redo Log File 都放在这个目录下**，**默认是在 `datadir` 下**。

```shell
   mysql> show global variables like 'innodb_log%';
   +-----------------------------+------------------+
   | Variable_name               | Value            |
   +-----------------------------+------------------+
   | innodb_log_buffer_size      | 8388608          |
   | innodb_log_checksums        | ON               |
   | innodb_log_compressed_pages | ON               |
   | innodb_log_file_size        | 5242880          |
   | innodb_log_files_in_group   | 2                |
   | innodb_log_group_home_dir   | /www/server/data |
   | innodb_log_write_ahead_size | 8192             |
   +-----------------------------+------------------+
   
   mysql> system ls -l /www/server/data/ib*
   -rw-r----- 1 mysql mysql      381 6 月  28 10:38 /www/server/data/ib_buffer_pool
   -rw-r----- 1 mysql mysql 77594624 6 月  28 10:38 /www/server/data/ibdata1
   -rw-r----- 1 mysql mysql  5242880 6 月  28 10:38 /www/server/data/ib_logfile0
   -rw-r----- 1 mysql mysql  5242880 6 月  27 17:28 /www/server/data/ib_logfile1
   -rw-r----- 1 mysql mysql 12582912 6 月  28 10:38 /www/server/data/ibtmp1
   ```

可以看到在默认的数据目录下，有两个 `ib_logfile` 开头的文件，他们就是 Log Group 中的 Redo Log File，而且他们的大小完全一致，且等于变量 `innodb_log_file_size` 定义的值，第一个文件 **`ibdata1` 是在没有开启 `innodb_file_per_table` 时的共享表空间文件**，**对应于开启 `innodb_file_per_table` 时的 `.ibd` 文件**。

3. 在 InnoDB 将**Log Buffer 中的 Redo Log Block 刷到这些 Log File 中时**，会**以追加写入的方式循环轮询写入**，即**先在第一个 Log File**（即 `ib_logfile0`）**的尾部追加写入**，直到**满了之后再向第二个 Log File**（即 `ib_logfile1`）**写**，当**第二个 Log File 满了之后会清空一部分第一个 Log File**，**然后继续写入**，由于是将 Log Buffer 中的日志刷到 Log File，所以**在 Log File 中记录日志也是 Log Block 的方式**。

4. 在**每组的第一个 Redo Log File 中**，**前 2KB 记录 4 个特定的部分**，**从 2KB 之后才开始记录 Log Block**，**除了第一个 Redo Log File 中会记录**，**Log Group 中的其它 Log File 不会记录这 2KB**，**但是却会腾出这 2KB 的空间**，如下图所示：

![](https://notebook.ricear.com/media/202106/2021-06-28_111705.png)

5. **Redo Log File 的大小对 InnoDB 的性能影响非常大**，**设置的太大**，**恢复的时候就会时间越长**，**设置的太小**，**就会导致在写 Redo Log 的时候循环切换 Redo Log File**。

### 日志刷盘规则

Log Buffer 中未刷到磁盘的日志称为脏日志，刷日志到磁盘主要有以下几种规则：

1. **发出 Commit 动作**，Commit 发出后是否刷日志由变量`innodb_flush_log_at_trx_commit` 控制，具体可参考[2.2 含义](#2-2-含义)。
2. **每秒刷一次**，这个刷日志的频率由变量`innodb_flush_log_at_timeout` 值决定，默认是 1 秒，需要注意的是，这个**刷日志的频率和 Commit 动作无关**。
3. **Log Buffer 已经使用的内存超过一半**。
4. **发生 [Checkpoint](https://notebook.ricear.com/project-37/doc-743)**。

### LSN

#### 含义

1. LSN 称为**日志的逻辑序列号**（Log Sequence Number），在 InnoDB 存储引擎中，LSN **占用 8 个字节**，LSN 的值会**随着日志的写入而逐渐增大**。

2. 根据 LSN，我们可以获取到几个有用的信息：

1. **数据页的版本信息**。
   2. **写入的日志总量**（通过 LSN 开始号码和结束号码可以计算出写入的日志总量）。
   3. **检查点的位置**。

3. LSN 不仅存在于重做日志中，还存在于每个页中：

1. 在每个页的头部，有一个值`FIL_PAGE_LSN`，记录了该页的 LSN。
   2. 在页中，LSN 表示该页最后刷新时 LSN 的大小。
   3. 因为重做日志记录的是每个页的日志，因此页中的 LSN 用来判断页是否需要进行恢复操作：
      1. 例如，页 P1 的 LSN 为 10000，而数据库启动时，InnoDB 检测到写入重做日志中的 LSN 为 13000，并且该事务已经提交，那么数据库需要进行恢复操作，将重做日志应用到 P1 页中。
      2. 同样的，对于重做日志中 LSN 小于 P1 页的 LSN，那么不需要进行重做，因为 P1 页中的 LSN 表示页已经被刷新到该位置。

4. Redo Log 的 LSN 信息可以通过 `show engine innodb status \G;` 来查看，具体如下：

```shell
   mysql> show engine innodb status \G;
   
   ---
   LOG
   ---
   Log sequence number 200876324
   Log flushed up to   200876324
   Pages flushed up to 200876324
   Last checkpoint at  200876315
   0 pending log flushes, 0 pending chkp writes
   10 log i/o's done, 0.00 log i/o's/second
   ```

其中：

1. `Log sequence number`：表示**当前的 Redo Log In Buffer 中的 LSN**。
   2. `Log flushed up to`：表示**刷到 Redo Log File On Disk 中的 LSN**。
   3. `Pages flushed up to`：表示**已经刷到磁盘数据页上的 LSN**。
   4. `Last checkpoint at`：表示**上一次检查点所在位置的 LSN**。

#### 修改机制

##### 简单说明

InnoDB 从执行修改语句开始：

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`。

##### 详细说明

详细说明如下图：

![](https://notebook.ricear.com/media/202106/2021-06-29_105504.png)

1. 假设最初时（12:00:00）**所有的日志和数据页都完成了刷盘**，也**记录好了检查点的 LSN**，这时**他们的 LSN 是完全一致的**。

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

```txt
   log sequence number(110) > log flushed up to(100) = page flushed up to = last checkpoint at
   ```

3. 之后又**执行了一个 `delete` 操作**，LSN 增长到 150，等到 12:00:01 时，**触发 Redo Log 刷盘的规则**（其中有一个规则是 `innodb_flush_log_at_timeout` 控制的默认日志刷盘频率为 1 秒），这时**Redo Log On Disk 中的 LSN 会更新到和 Redo Log Buffer 的 LSN 一样**，都等于 150，这是 `show engine innodb status`，即图 ② 中的位置，结果会是：

```txt
   log sequence number(150) = log flushed up to(150) > page flushed up to(100) = last checkpoint at
   ```

4. 之后又**执行了一个 `update` 操作**，缓存中的 LSN 将增长到 300，即图 ③ 的位置。

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

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

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

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

7. 随着时间的推移到了 12:00:02，即图中位置 ⑥，此时又**触发了日志刷盘规则**，但此时**Buffer 中的日志 LSN 和磁盘中的日志 LSN 是一致的**，所以**不执行日志刷盘**，几次是所有的 LSN 都是相等的。

8. 随后**执行了一个 `insert` 操**作，假设 Buffer 中的 LSN 增长到了 800，即图中位置 ⑦，此时各种 LSN 的大小和位置 ① 时一样。

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

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

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

## Undo Log

### 含义

1. 重做日志记录了事务的行为，可以很好地通过其对页进行“重做操作”，但是事务有时还需要进行回滚操作，这时就需要 Undo，因此在**对数据库进行修改时**，InnoDB 存储引擎**不但会产生 Redo Log**，**还会产生一定量的 Undo Log**，这样**如果用户执行的事务或语句由于某种原因失败了**，**或者用户用一条 `ROLLBACK` 请求回滚**，就**可以利用这些 Undo 信息将数据回滚到修改之前的样子**。
2. **Redo Log 存放在重做日志文件中**，**Undo Log 存放在**数据库内部一个特殊段（Segment）中，这个段称为**Undo 段**（Undo Segment），**位于共享表空间内**。
3. **Undo Log 是逻辑日志**，**只是将数据库逻辑地恢复到原来的样子**，**所有修改都被逻辑地取消了**，**但是数据结构和页本身在回滚之后可能大不相同**：
   1. 这是因为在多用户并发系统中，可能会有数十、数百、甚至数千个并发事务，**数据库的主要任务就是协调对数据记录的并发访问**，比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改，因此**不能将一个页回滚到事务开始的样子**，因为这样**会影响到其他事务正在进行的工作**。
   2. 假如用户执行了一个`INSERT 10W` 条记录的事务，这个事务会导致分配一个新的段，即表空间会增大，在**用户执行 `ROLLBACK` 时**，**会将插入的事务进行回滚**，**但是表空间的大小并不会因此而收缩**，因此，当**InnoDB 存储引擎回滚**时，它实际上**做的是与之前相反的工作**：
      1. 对于**每个 `INSERT`**，InnoDB 存储引擎会**完成一个 `DELETE`**。
      2. 对于**每个 `DELETE`**，InnoDB 存储引擎会**执行一个 `INSERT`**。
      3. 对于**每个 `UPDATE`**，InnoDB 存储引擎会**执行一个相反的 `UPDATE`**，**将修改前的行放回去**。
4. 除了回滚操作，Undo Log 的另一个作用是**MVCC**，即在**InnoDB 存储引擎中 MVCC 的实现是通过 Undo Log 来完成**，**当用户读取一行记录时**，**若该记录已经被其他事务占用**，**当前事务可以通过 Undo Log 读取之前的行版本信息**，**以此实现非锁定读取**。
5. **Undo Log 也会产生 Redo Log**，也就是 Undo Log 的产生会伴随着 Redo Log 的产生，这是**因为 Undo Log 也需要持久性的保护**。

### 存储管理

1. InnoDB 存储引擎对 Undo Log 的管理同样采用段的方式，InnoDB 存储引擎有**回滚段**（Rollback Segment），**每个回滚段中记录了 1024 个 Undo Log Segment**，然后**在每个 Undo Log Segment 中进行 Undo 页的申请**。
2. 在**InnoDB 1.1 版本之前**，**只有一个 Rollback Segment**，因此**支持同时在线的事务限制为 1024**，**从 InnoDB 1.1 版本开始**，InnoDB**支持最大 128 个 Rollback Segment**，因此**支持同时在线的事务限制提高到了 128*1024**。
3. 可以通过如下参数对 Rollback Segment 做进一步的设置：
   1. `innodb_undo_directory`：用于**设置 Rollback Segment 文件所在的路径**，**默认值为“.”**，**表示当前 InnoDB 存储引擎的目录**。
   2. `innodb_undo_logs`：用来**设置 Rollback Segment 的个数**，**默认值为 128**，在 InnoDB 1.2 版本中，该参数用来替换之前版本的参数`innodb_rollback_segments`。
   3. `innodb_undo_tablespaces`：用来**设置构成 Rollback Segment 文件的数量**，这样**Rollback Segment 可以较为平均地分布在多个文件中**，设置该参数后，会在路径`innodb_undo_directory` 看到`undo` 为前缀的文件，该文件就代表 Rollback Segment 文件。
4. 事务**在 Undo Log Segment 分配页写入 Undo Log 的这个过程同样需要写入重做日志**，当**事务提交时**，Innodb 存储引擎**会做以下两件事情**：
   1. **将 Undo Log 放入链表中**，**以供之后的 `purge` 操作**：
      1. **事务提交之后并不能马上删除 Undo Log 以及 Undo Log 所在的页**，这是因为**可能还有其他事务需要通过 Undo Log 来得到行记录之前的版本**，因此**事务提交时将 Undo Log 放入一个链表中**，**是否可以最终删除 Undo Log 和 Undo Log 所在的页由 `purge` 线程来判断**。
   2. **判断 Undo Log 所在的页是否可以重用**，**若可以**，则**分配给下个事务使用**：
      1. 若**为每一个事务分配一个单独的 Undo 页会非常浪费存储空间**，**特别是对于 OLTP 的应用类型**，因为**在事务提交时**，可能并**不能马上释放页**，因此，在 InnoDB 存储引擎的设计中对 Undo 页可以进行重用：
         1. 当**事务提交时**，首先**将 Undo Log 放入链表中**，然后**判断 Undo 页的使用空间是否小于**$\frac34$，**若是**，则**表示该 Undo 页可以被重用**，之后**新的 Undo Log 记录在当前 Undo Log 的后面**。
         2. 由于**存放 Undo Log 的列表是以记录进行组织的**，而**Undo 页可能存放着不同事务的 Undo Log**，因此`purge`**操作需要涉及磁盘的离散读取操作**，是一个**比较缓慢**的过程。

### 格式

在 InnoDB 存储引擎中，Undo Log 分为两种，分别是**Insert Undo Log**、**Update Undo Log**。

#### Insert Undo Log

1. Insert Undo Log 是指在**Insert 操作中产生的 Undo Log**，因为**Insert 操作的记录**，**只对事务本身可见**，**对其他事务不可见**（这是事务隔离性的要求），因此**Insert Undo Log 可以在事务提交后直接删除**。

2. Insert Undo Log 的格式如下图所示，详细信息如下：

1. `next`：2 个字节，**表示下一个 Undo Log 的位置**。

2. `type_cmpl`：1 字节，**表示 Undo 的类型**，**对于 Insert Undo Log**，**该值总是 11**。

3. `undo_no`：**表示事务的 ID**，采用压缩存储。

4. `table_id`：**表示 Undo Log 所对应的表对象**，采用压缩存储。

5. `lenN`、`colN`：**表示主键的列和值**，在**进行 `ROLLBACK` 操作时**，根据这些值**可以定位到具体的记录**，**然后进行删除即可**。

6. `start`：2 字节，**表示 Undo Log 的开始位置**。

![](https://notebook.ricear.com/media/202106/2021-06-29_164810.png)

#### Update Undo Log

1. Update Undo Log 记录的是**对 `delete` 和 `update` 操作所产生的 Undo Log**，该 Undo Log**可能需要提供 MVCC 机制**，因此**不能在事务提交时就删除**，**提交时放入 Undo Log 链表**，**等待 `purge` 线程进行最后的删除**。

2. Update Undo Log 的结构如下图所示，详细信息如下（Update Undo Log 相对于 Insert Undo Log，记录的内容更多，所需占用的空间也更大，其中`next`、`start`、`undo_no`、`table_id` 与 Insert Undo Log 相同，这里不再叙述。）：

1. `type_cmpl`：**表示 Undo 的类型**，由于 Update Undo Log 本身还有其他类型，因此其可能的值如下：

1. **12**：`TRX_UNDO_UPD_EXIST_REC`，**表示更新 `non-delete-mark` 的记录**。
      2. **13**：`TRX_UNDO_UPD_DEL_REC`，**表示将 `delete` 的记录标记为 `not delete`**。
      3. **14**：`TRX_UNDO_DEL_MARK_REC`，**表示将记录标记为 `delete`**。

2. `update_vector`：**表示 `update` 操作导致发生改变的列**，**每个修改的列信息都要记录在 Undo Log 中**，**对于不同的 Undo Log 类型**，**可能还需要记录对索引列所做的修改**。

![](https://notebook.ricear.com/media/202106/2021-06-29_170402.png)

### delete/update 操作的内部机制

#### delete

1. `delete`**操作并不直接删除记录**，**而是将记录标记为已删除**，也就是**将记录的 `delete flag` 设置为 1**，而**记录最终的删除是在 `purge` 操作中完成的**。

#### update

`update` 分为两种情况：

1. 如果`update`**的是非主键**，在 Update Undo Log 中**直接反向记录是如何 `update` 的**即可。
2. 如果`update`**的是主键**，则主要分两个步骤进行，首先**将原主键记录标记为已删除**，然后**插入一条新的纪录**。

### purge

#### 含义

1. 假如存在如下的表：

```sql
   CREATE TABLE t(
     a INT,
     b VARCHAR(32),
     PRIMARY KEY(a),
     KEY(b)
   )ENGINE=InnoDB;
   ```

2. `delete` 和 `update` 操作可能并不直接删除原有的数据：

1. 例如，对表 `t` 执行如下的 SQL 语句：

```sql
      DELETE FROM t WHERE a = 1;
      ```

表 `t` 上列 `a` 有聚集索引，列 `b` 上有辅助索引。

2. 对于上述的 `delete` 操作，仅是将主键列等于 1 的记录 `delete flag` 设置为 1，记录并没有被删除，即记录还是存在于 B+ 树中。

3. 其次，对辅助索引上 `a` 等于 1，`b` 等于 1 的记录同样没有做任何处理，甚至没有产生 Undo Log，而真正删除这行记录的操作其实被延时了，最终在 `purge` 操作中完成。

3. `purge`**用于最终完成 `delete` 和 `update` 操作**：

1. 这样设计是因为**InnoDB 存储引擎支持 MVCC**，所以**记录不能在事务提交时立即进行处理**，这时**其他事务可能正在引用这行**，故 InnoDB 存储引擎**需要保存记录之前的版本**，而**是否可以删除该条记录通过 `purge` 来进行判断**，**若该行记录已不被任何其他事务引用**，那么**就可以进行真正的 `delete` 操作**。
   2. 可见，`purge` 操作是清理之前的`delete` 和`update` 操作，将上述操作最终完成，而实际执行的操作为`delete` 操作，清理之前行记录的版本。

4. 为了节省存储空间，InnoDB 存储引擎的 Undo Log 设计是这样的：

1. **一个页上允许多个事务的 Undo Log 存在**，虽然这**不代表事务在全局过程中提交的顺序**，但是**后面的事务产生的 Undo Log 总在最后**。

2. 此外，InnoDB 存储引擎还有一个**`history` 列表**，他**根据事务提交的顺序**，**将 Undo Log 进行链接**，如下面的一种情况：

1. 如下图所示，**History List 表示按照事务提交的顺序将 Undo Log 进行组织**，在 InnoDB 存储引擎的设计中，**先提交的事务总在尾端**，

2. Undo Page 存放了 Undo Log，由于可以重用，因此**一个 Undo Page 中可能存放了多个不同事务的 Undo Log**，`trx5` 的灰色阴影表示该 Undo Log 还被其他事务引用。

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

4. InnoDB 存储引擎这种先从 History List 中找 Undo Log，然后再从 Undo Page 中找 Undo Log 的设计模式是为了**避免大量的随机读取操作**，从而**提高 `purge` 的效率**。

![](https://notebook.ricear.com/media/202106/2021-06-30_100828.png)

#### 参数

##### innodb_purge_batch_size

1. 全局动态参数`innodb_purge_batch_size` 用来**设置每次 `purge` 操作需要清理的 Undo Page 数量**：
2. 在**InnoDB 1.2 之前**，该参数的**默认值为 20**，从**1.2 版本开始**，该参数的**默认值为 300**。
3. 通常来说，该参数**设置的越大**，**每次回收的 Undo Page 也就越多**，这样**可供重用的 Undo Page 就越多**，**减少了磁盘存储空间与分配的开销**。
4. 不过，该参数**设置的越大**，则**每次需要处理更多的 Undo Page**，从而**导致 CPU 和磁盘 IO 过于集中于对 Undo Log 的处理**，使**性能下降**。
5. 因此，该参数的调整**需要长期观察数据库的运行状态**，**一般情况下不需要调整该参数**。

##### innodb_max_purge_lag

1. 当**InnoDB 存储引擎的压力非常大时**，并**不能高效地进行 `purge` 操作**，那么**History List 的长度会变得越来越长**。

2. 全局动态参数 `innodb_max_purge_lag` 用来**控制 History List 的长度**，若**长度大于该参数**时，其**会延缓 DML 的操作**，该参数的**默认值为 0**，表示**不对 History List 做任何限制**，当**大于 0 时**，就**会延缓 DML 的操作**，其延缓的算法为：

$$
   delay = ((length(history\_list) - innodb\_max\_purge\_lag)*10-5
   $$

**$delay$ 的单位是毫秒**，此外，需要特别注意的是，$delay$ 的**对象是行**，而不是一个 DML 操作，例如当一个 `update` 操作需要更新 5 行数据时，每行数据的操作都会被 $delay$，故总的延时时间为 $5*delay$，而 $delay$ 的统计会在每一次 `purge` 操作完成后重新计算。

3. InnoDB 1.2 版本引入了新的全局动态参数 `innodb_max_purge_lag_delay`，其用来**控制 $delay$ 的最大毫秒数**，也就是当上述**计算得到的 $delay$ 值大于该参数时**，**将 $delay$ 设置为 `innodb_max_purge_lag_delay`**，**避免由于 `purge` 操作缓慢导致其他 SQL 线程出现无限制的等待**。

## 参考文献

1. 《MySQL 技术内幕（InnoDB 存储引擎）第 2 版》
2. [详细分析 MySQL 事务日志(redo log 和 undo log)](https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html)。