InnoDB事务系统（二）：事务的实现

事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的 redo log 和 undo log 来完成。

redo log 称为重做日志，用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。

有的 DBA 或许会认为 undo 是 redo 的逆过程，其实不然。redo 和 undo 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改的页操作，而 undo 回滚行记录到某个特定版本。

因此，两者记录的内容不同，redo 通常是物理日志，记录的是页的物理修改操作。undo 是逻辑日志，根据每行记录进行记录。

注：物理日志是指数据页的变更；逻辑日志是指记录的是 SQL 语句；

redo log

---

redo log 的引入

1）硬盘知识回顾

1. 机械硬盘：机械硬盘是将数据写入扇区，一个扇区 512b，那么写入数据都会有一个寻址的过程（就是寻找扇区）机械硬盘写入数据时需要经过：1、定位到磁道；2、等待旋转到对应扇区；3、开始读写。步骤 1 和 2 机械定位，速度慢。

2. 固态硬盘：固态硬盘将数据写入闪存芯片，使用电子定位，所以比机械硬盘快很多。【拓展：对于拷贝数据，是连续的扇区写入数据，一次性申请连续的空间，固态硬盘比机械硬盘快 3 到 5 倍。对于随机读写，固态硬盘比机械硬盘快 100-300 倍，而我们大多数的场景都是随机读写】

2）文件系统

我们一般不会直接与硬盘打交道，都是与文件系统打交道。文件系统将硬盘划分为一个一个的 block，1block = 8 扇区 = 4kb，一个block 是由多个连续的扇区组成，这样就大大减少了扇区寻址的时间，但是这样会存在 block 写不满的情况，这就是磁盘碎片。

1. 文件系统磁盘读写模式

   1. 顺序读写：顺序读写，分配的是连续的 block，所以读写性能高，但是需要满足两个条件：1、文件大小固定；2、预分配磁盘空间。

   2. 随机读写：随机读写，分配的 block 不是连续的，磁头需要频繁切换，读写性能比较低。

2. 文件系统磁盘读写规则：文件系统实际不是直接将数据读写到磁盘中，它会先将数据写入 buffer ，buffer 按照策略将数据刷新到磁盘；读取数据时先读 cache，cache 读不到再去读磁盘。【拓展：如果系统宕机、断电，使用 Linux 提供的 fsync 可以直接将数据刷新到磁盘】

3）为什么要用 redo log

数据库将数据直接写入磁盘，使用的是随机读写模式，这种模式性能比较差，会造成数据库读写瓶颈。所以数据库就想先将数据写入内存（缓存池），然后再定时从内存刷到磁盘，这样就快很多。

问题来了：一旦发生故障，内存中的数据会全部丢失，那么要如何保证数据的一致性呢？此时就有了 redo log。

redo log 的模式叫做 WAL 技术，也就是写前日志（write ahead log）；redo log 在硬盘中是一个大小固定，扇区连续的模块，这样 redo log 就可以实现顺序写入，比随机写入快很多。

问：什么是WAL呢？

WAL即 Write Ahead Log，WAL的核心思想是，每次更新操作都先写入日志，只做了写日志这一个磁盘操作。这个日志叫做redo log（重做日志），也就是《孔乙己》记账的粉板，在更新完内存写完redo log后，就返回给客户端，本次更新成功。而实际更新操作是由后台线程根据redo log异步写入。因此，对Client来说，延迟会减少。同时，顺序写入的可能性很大，如果更新的多条记录位于同一Page，则磁盘IO次数也能大大降低。

所以，WAL机制主要得益于三个方面：

1. 减少更新数据的磁盘IO。

2. redo log 和 binlog都是顺序写， 磁盘的顺序写比随机写速度要快很多。

3. 组提交机制， 可以大幅度降低磁盘的IOPS消耗。

MySQL 的数据在写入内存的时候同时将数据写入到 redo log 中，这样当内存数据丢失的时候，以 redo log 数据为准恢复数据，则可保证数据一致性。redo log 保存的每一条数据都是记录事务对哪些数据页做了哪些修改。【拓展：Innodb 以页为单位进行磁盘交互，一页大小 16 KB，一页中至少保存两条数据，也就是说当你更新某个页中的一条数据中的一个字段时，它都需要整页更新，而且如果事务修改了很多页，页之间的 block 不是连续的，磁盘还要不停的寻址。IO 大不大，速度慢不慢？所以有了 redo log】

redo log 的概念

重做日志用来实现事务的持久性，即事务的 ACID 中的 D。其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），其是持久的。

InnoDB 是事务的存储引擎，其通过 Force Log at Commit 机制实现事务的持久性，即当事务提交（commit）时，必须先将该事务的所有日志写到重做日志进行持久化，待事务的 commit 操作完成才算完成。

这里的日志是指重做日志，在 InnoDB 存储引擎中，由两部分组成，即 redo log 和 undo log。redo log 用来保证事务的持久性，undo log 用来帮助事务回滚及 MVCC 功能。redo log 基本上都是顺序写的，在数据库运行时不需要对 redo log 的文件进行读取操作。而 undo log 是需要进行随机读写的。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB 存储引擎都需要调用一次 fsync 操作（fsync 指异步刷新redo log）。

注意：InnoDB 存储引擎允许用户手工设置非持久性的情况发生，以此来提高数据库的性能。即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再次执行 fsync 操作。但是当数据库发生宕机时，由于部分日志未刷新到磁盘，因此会丢失最后一段时间的事务。

参数 innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略，参数取值及含义如下：

1. innodb_flush_log_at_trx_commit = 0

   1. 日志写入：日志在每次事务提交时都写入redo log buffer，但并不写入磁盘。

   2. 刷新时机：通过Master Thread线程异步刷新到磁盘（通常是每秒）。

   3. 性能：由于减少了磁盘I/O操作，这个设置通常可以提供最好的性能。

   4. 数据持久性风险：断点数据丢失。

2. innodb_flush_log_at_trx_commit = 1

   1. 日志写入：每次事务提交时，事务日志都会写入日志缓冲区，并立即写入重做日志文件（同时Master Thread线程也会每秒异步刷新redo log）。

   2. 刷新时机：每次事务提交都会触发日志刷新到磁盘。

   3. 性能：由于每次事务提交都涉及到磁盘 I/O 操作，当有大量小事务时会降低性能。

   4. 数据持久性：断电数据不丢失。

3. innodb_flush_log_at_trx_commit = 2

   1. 日志写入：日志在每次事务提交时都写入redo log buffer，并调用flush函数，把redo log buffer中的数据刷新到磁盘的缓冲区中，并没有刷新到磁盘，且不等flush操作完成就返回给客户端事务提交成功的响应。

   2. 刷新时机：提交两个事务刷新一次磁盘，同时Master Thread线程也会每秒异步刷新到磁盘。

   3. 性能：这个设置在性能和持久性之间取得了一个折中。它减少了磁盘I/O操作的频率，但仍然在每次事务提交时保持数据的安全。

   4. 数据持久性风险：当 MySQL 数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失；而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件的那部分数据。

问1：flush操作和fsync操作的区别？

答：flush操作可能只是将数据从一种缓存转移到另一种缓存（如从内存缓冲区到内核缓冲区），而fsync操作则确保了数据从内核缓冲区到磁盘的同步。

问2：将innodb_flush_log_at_trx_commit参数分别设置不同的值，然后向数据库中插入50万条记录，其插入速度如何？

答：三种值对应插入数据，如下：

虽然用户可以通过把innodb_flush_log_at_trx_commit设置为0或2来提高事务提交的性能，但这种设置方法丧失了事务的ACID特性。

问3：什么是binlog，binlog和redo log的区别是什么？

答：在MySQL数据库中还有一种二进制日志（binlog），其用来进行POINT-IN-TIME（PIT）的恢复及主从复制（Replication）。

binlog和redo log的区别：

1. 重做日志是在InnoDB引擎层产生，而二进制日志是在MySQL数据库上层产生的。

2. 重做日志是全局的，binlog是线程私有的。

3. 两种日志记录的内容形式不同。二进制日志是一种逻辑日志，其记录的是对应的SQL语句。而重做日志是物理日志，其记录的是对于每个页的修改。

4. 两种日志记录写入磁盘的时间点也不同，如下图所示。二进制日志只在事务提交完成后进行一次写入。而重做日志在事务进行中不断地被写入，即重做日志并不是随着事务提交的顺序进行写入的。

从上图可知，二进制日志仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。

而对于InnoDB存储引擎的重做日志，由于记录的是物理操作日志，因此每个事务对应多个条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，故其在文件中记录的顺序并非是事务开始的顺序。*T1、*T2、*T3表示的是事务提交时的日志。

总结：binlog日志以事务为粒度，而重做日志（redo log）的粒度更细。（PolarDB Serverless实现主要是使用redo log和undo log）

注意：

• 在事务开始时，redo log会被写到内存中的redo log buffer。此时redo log状态为prepare状态，表示事务已经开始，但尚未提交。

• 在事务提交前，redo log的状态会从prepare变为commit。表示在提交事务之前，redo log中的记录已经准备好写入磁盘，但实际写入磁盘的操作会在提交事务后进行‌。

redo log 的结构

在InnoDB存储引擎中，重做日志都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块（block）的方式进行保存的，称之为重做日志块（redo log block），每块的大小为512字节。

若一个页中产生的重做日志数量大于512字节，则需要分割为多个重做日志块进行存储。此外，由于重做日志块的大小和磁盘扇区大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要doublewrite技术。

重做日志块除了日志本身之外，还有日志块头（log block header）及日志块尾（log block tailer）两部分组成。重做日志头一共占用12字节，重做日志尾占用8字节。故每个重做日志块实际可以存储的大小为492字节（512-12-8）。

重做日志块缓存结构：

log block header由4部分组成（redo log buffer是由log block组成的，在内部redo log buffer就好似一个数组）：

名称	占用字节	作用
LOG_BLOCK_HDR_NO	4	标记数组中的位置
LOG_BLOCK_HDR_DATA_LEN	2	标识log block所占用的大小，当log block被写满时，该值为0x200
LOG_BLOCK_FIRST_REC_GROUP	2	表示log block中第一个日志所在的偏移量
LOG_BLOCK_CHECKPOINT_NO	4	表示该log block最后被写入时的检查点第4字节的值

问：如果LOG_BLOCK_FIRST_REC_GROUP的值等于LOG_BLOCK_HDR_DATA_LEN的值，则意味着什么？

答：如果二者相等，则意味着log block不包含新的日志。如果事务T1的重做日志1占762字节，事务T2的重做日志占100字节。由于log block实际只能保存492字节，因此其在log buffer中的情况如下图所示。

从上图可以观察到，由于事务T1的重做日志1占762字节，因此需要占用两个log block。第一个log block中 LOG _BLOCK _FIRST_REC_GROUP的值为12（log block中第一个日志的开始位置）。在第二个log block中，由于包含了之前事务T1的重做日志，事务T2的日志才是log block中第一个日志，因此该log block的LOG_ BLOCK _ FIRST _REC_GROUP为282（270+12）。

log blick tailer组成部分：

名称	占用字节	作用
LOG_BLOCK_TRL_NO	4	其值和LOG_BLOCK_HDR_NO相同

redo log 的格式

由于InnoDB存储引擎的存储管理是基于页的，故其重做日志格式也是基于页的。虽然有着不同的重做日志格式，但是它们有着通用的头部格式，如图所示。

通用的头部格式由以下3部分组成：

1. redo_log_type：重做日志类型。

2. space：表空间的ID。

3. page_no：页的偏移量。

redo log body部分，根据重做日志类型的不同，会有不同的存储内容。例如，对于页上记录的插入和删除操作，分别对应下图所示的格式：

到InnoDB1.2版本时，一共有51种重做日志类型。

redo log 的 LSN

LSN是Log Sequence Number的缩写，其代表的是日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增。LSN表示的含义如下：

1. 重做日志写入的总量。

2. checkpoint的位置。

3. 页的版本。

LSN表示事务写入重做日志的字节总量。

例如当前重做日志的LSN为1000，有一个事务T1写入了100字节的重做日志，那么LSN就变为了1100，若又有事务T2写入了200字节的重做日志，那么LSN就变为了1300。

注意：LSN不仅记录在重做日志中，还存在于每个页中。

在每个页的头部，有一个值FIL_PAGE_LSN，记录了该页的LSN。在页中，LSN表示该页最后刷新时LSN的大小。因为重做日志记录的是每个页的日志，因此页中的LSN用来判断是否需要进行恢复操作。

例如，页P1的LSN为10000，而数据库启动时，InnoDB检测到写入重做日志中的LSN为13000，并且事务已经提交，那么数据库需要进行恢复操作，将重做日志应用到P1页中。同样的，如果重做日志中LSN小于P1页中的LSN，则不要进行重做，因为P1页中的LSN表示页已经被刷新到该位置。

问：用户如何查看LSN的情况？

答：用户可以通过 show engine innodb status 命令查看LSN的情况。示例如下：

参数说明：

1. Log sequence number：表示当前的LSN。随着数据库操作的进行而不断增加。

2. Log flushed up to：表示刷新到重做日志文件的LSN。当redo log buffer中的内容被刷新到磁盘上的redo log文件时，这个值会更新为最新刷到磁盘的LSN。保证了在数据库恢复时，可以从这个位置开始重放redo log。

3. Last checkpoint at：表示最后一次刷新到磁盘的LSN（即页中的LSN）。Checkpoint是一个将缓存池中的脏页刷回磁盘的过程，当Checkpoint发生时，会记录此时的LSN值。

在实际生产环境中，Log sequence number和Log flushed up to的值可能是不同的。因为在一个事务中从日志缓冲刷新到重做日志文件并不只是在事务提交时发生，每秒都会有从日志缓冲刷新到重做日志文件的动作。

在生产环境下重做日志的信息示例：

从图中可知，Log sequence number、Log flushed up to、Last checkpoint at的值都不相同。

redo log 的恢复

InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。

因为从重做日志记录的是物理日志，因此恢复的速度比逻辑日志，如二进制日志（binlog），要快很多。

由于checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中仅需要恢复checkpoint开始的日志部分。

对于下图中的例子，当数据库在checkpoint的LSN为10000时发生宕机，恢复操作仅恢复LSN：10000~13000范围的日志。

问1：redo log具体记录的是什么？

答：redo log记录的是每个页上的变化。假设有如下表结构：

create table t (a int, b int, primary key(1), key(b)); -- 执行插入语句 insert into t select 1, 2;

由于需要对聚集索引页和二级索引页进行操作，其记录的重做日志大致为：

page(2, 3), offset 32, value 1, 2 # 聚集索引 page(2, 4), offset 64, value 2 # 二级索引

可以看到记录的是页的物理修改操作，若插入涉及B+数的split，可能会有更多的页需要记录日志。

问2：数据库中的幂等是指什么？

答：幂等概念：f(f(x))=f(x)

换句话说，幂等操作无论被调用多少次，其结果都与单次调用的结果相同（不会产生重复结果）。

错误认知：只要将二进制日志（binlog）的格式设置为ROW，那么二进制日志也是幂等的。

例如，insert操作在二进制日志中就不是幂等的，重复执行可能会插入多条重复的记录。而上述的insert操作的重做日志是幂等的。

问3：为什么把二进制日志（binlog）的格式设置为ROW，其binlog仍然不是幂等？

答：ROW格式下，binlog记录的是每一行数据在执行SQL操作前后的具体变化。这包括表名、行ID以及发生变化的列值。ROW格式本身并不直接提供幂等性保证，它只是记录了数据的变化。

问4：如果解决非幂等问题？

答：最常见的就是创建唯一索引，防止插入重复数据。

redo log、binlog关于幂等性问题总结：

1. redo log的幂等性，redo log是物理日志，记录的是数据页的物理变化，而非具体的SQL语句。因此，无论多少次写入相同的redo log记录，对数据库的实际影响都是相同的。也就是说redo log的幂等性体现在它只关心数据页的最终状态，而不关心达到这个状态的具体过程。因此，即使redo log中的某些记录被多次写入，只要它们代表的是相同的物理修改，最终的数据页状态就不会改变。

2. binlog的非幂等性，binlog是逻辑日志，记录的是对数据库执行的修改操作，即SQL语句。如果相同的SQL语句被多次执行，那么数据库中的数据可能被多次修改，从而导致非幂等结果。

注：对于binlog而言，即便因为表中存在主键id，导致insert语句在多次插入时产生冲突，从而避免数据重复，但这并不意味着binlog本身是幂等的。因为，此时是由于主键约束保证了数据的唯一性，而不是因为binlog本身是幂等的。

undo log

---

undo log 的概念

undo log的作用：回滚和MVCC并发控制。

redo log存放在重做日志文件中，而undo log存放在数据库内部的一个特殊段（segment）中，这个段称为undo段。

undo段位于共享表空间中。可以通过py_innodb_page_info.py工具查看当前共享表空间中undo的数量。

示例如下：

从上图可知当前的共享表空间ibdata1内有2222个undo页。

误解：undo用于将数据库物理地恢复到执行语句或事务之前的样子!

事实并非如此！

例如，用户执行了一个insert 10w条记录的事务，这个事务会导致分配一个新的段，即表空间增大。用户在回滚时，会将插入的事务进行回滚，但是表空间的大小并不会因此而收缩。因此，当InnoDB存储引擎发生回滚时，它实际上做的是与先前相反的工作。

回滚的实质：对于insert操作，回滚时会执行delete；对于delete操作，回滚时会执行insert；对于update操作，回滚时会执行一个相反的update！

注意：undo log也会产生redo log，也就是undo log的产生会伴随着redo log的产生，因为undo log也需要持久性的保护。

undo log 存储管理

InnoDB存储引擎对undo的管理采用段的方式。

InnoDB存储引擎有rollback segment，每个回滚段记录了1024个undo log segment，而在每个undo log segment段中进行undo页申请。共享表空间偏移量为5的页（0，5）记录了所有rollback segment header所在的页，这个页类型为file_page_type_sys。

在InnoDB 1.1版本之前（不包括InnoDB 1.1），只有一个rollback segment，因此支持同时在线的事务限制为1024。从InnoDB 1.1版本开始，最大支持128个rollback segment，故其支持同时在线的事务限制提高到了128*1024。

注意：

1. 每个undo log segment用于记录一个事务的undo log，在InnoDB 1.1之前，每个rollback segmen记录了1024个undo log segment，这意味着同时只能有1024个事务在线，因为每个事务都需要一个undo log segment来记录其undo日志。

2. InnoDB 1.1版本支持128个rollback segment，但这些rollback segment都存储于共享表空间。

从InnoDB 1.2开始，可通过参数对rollback segment进行控制，参数如下：

1. innodb_undo_directory：用于设置rollback segment文件所在路径，可以存放在共享表空间以外的位置，即可以设置为独立的共享表空间。该参数的默认值为 “.”，表示当前InnoDB存储引擎的目录。

2. innodb_undo_logs：用于设置rollback segment的个数，默认值为128。（InnoDB 1.1版本中用参数innodb_rollback_segment设置，现已被该参数替换）。

3. innodb_undo_tablespaces：用于设置构成rollback segment文件的数量，这样rollback segment可以较为平均的分布在多个文件中。该参数设置后，会在路径innodb_undo_directory中看到undo为前缀的文件，该文件就代表rollback segment文件。下图显示了由3个文件组成的rollback segment。

注意：事务在undo log segment分配页并写入undo log的这个过程同样需要写入重做日志。当事务提交时，InnoDB会做以下两件事：

1. 将undo log放入列表中，以供之后的purge操作。

2. 判断undo log所在的页是否可以重用，若可以，则分配给下个事务使用。

事务提交后并不能马上删除undo log及undo log所在页。这是因为可能还有其它事务需要通过undo log来得到行记录之前的版本。

问：是否可以为每一个事物分配一个单独的undo页？

答：不能，这样会非常浪费存储空间，特别是对于OLTP的应用类型。因此，在InnoDB存储引擎的设计中对undo页可以进行重用。具体来说，当事务提交时，首先将undo log放入链表中，然后判断undo页的使用空间是否小于3/4，若是则表示该undo页可以被重用，之后新的undo log记录在当前undo log的后面。

示例：假设某个应用的删除和更新操作的TPS（transaction per second）为1000，为每个事务分配一个undo页，那么一分钟就需要1000*60个页，大约需要的存储空间为1GB。若每秒purge页的数量为20，则这样的涉及对磁盘空间有着相当高的要求。

注意：由于存放undo log的列表是以记录进行组织的，而undo页可能存放着不同事务的undo log，因此purge操作需要涉及磁盘及的离散读取操作，是一个比较缓慢的过程。

可以通过show engine innodb status来查看链表中undo log的数量，如：

History list length就代表了undo log的数量，这里为12。purge操作会减少该值，然而由于undo log所在的页可以被重用，因此即使操作发生，History list length的值也可以不为0。

undo log 的格式

思考：如果多个事务对同一条记录进行修改，且同时有事务在处理同一个表中的其它记录，那么同一条记录的不同版本是连续存放还是间断性存放？

在InnoDB存储引擎中，undo log分为：

insert undo log

指在insert操作中产生的undo log。

因为insert操作的记录，只对事务本身可见，对其他事务不可见，故该undo log可以在事务提交后直接删除。无需进行purge操作。

insert undo log结构示意图：

名词解释：

1. next：记录下一个undo log的位置，通过next的字节可以知道一个undo log所占的空间字节数。

2. type_cmpl：占用一个字节，记录undo的类型，对于insert undo log，该值总是11。

3. undo_no：记录事务的ID。

4. table_id：记录undo log所对应的表对象。

5. ……：记录了所有主键的列和值。

6. * ：表示对存储字段进行了压缩。

7. start：记录的是undo log的开始位置。

在进行rollback操作时，根据这些值可以定位到具体的记录，然后进行删除即可。

update undo log

update undo log记录的是对delete和update操作产生的undo log。

该undo log可能需要提供MVCC机制，因此不能在事务提交是就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

update undo log结构示意图：

名词解释：

1. next、start、undo_no、table_id和insert undo log部分相同。

2. 由于update undo log本身还有分类，type_cmpl取值如下：

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

查看undo log信息

Oracle和Microsoft SQLServer数据库都由内部的数据字典来观察当前undo的信息，InnoDB存储引擎在这方面做得还不够，DBA只能通过原理和经验来进行判断。

InnoSQL对information_schema进行了扩展，添加了两张数据字典表（innodb_trx_rollback_segment、innodb_trx_undo），这样用户可以非常方便和快捷地查看undo的信息。

但MySQL不支持，感兴趣的读者可自行参考：MySQL技术内幕-InnoDB存储引擎。

purge

---

purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理。这时其他事物可能正在引用这行，故InnoDB存储引擎需要保存记录之前的版本。

而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就就可以进行真正的delete操作。可见，purge操作是清理之前的delete和update操作，将上述操作"最终"完成。

InnoDB存储引擎的设计原理：一个页上允许多个事务的undo log存在。虽然这不代表事务在全局中的提交顺序，但后面的事务产生的undo log总在最后。此外，InnoDB存储引擎还有一个history列表，它根据事务的提交顺序，将undo log进行连接。

undo log与history列表的关系：

history list表示按照事务提交的顺序将undo log进行组织。

在InnoDB存储引擎的设计中，先提交的事务总在尾端。undo page存放了undo log，由于可以重用，因此一个undo page中可能存放了多个不同事务的undo log。图中阴影部分trx5表示该undo log正在被其它事务引用。

问1：InnoDB存储引擎是怎么清除undo log的？

答：在执行purge的过程中，InnoDB存储引擎首先会从history list中找到第一个需要被清理的记录，这里为trx1，清理之后InnoDB存储引擎会在trx1的undo log所在页中继续寻找是否存在可以被清理的记录（undo log），这里会找到trx3，接着找到trx5，但是发现trx5正在被其他事务引用而不能清理。故再次去history list中查找，发现这时最尾端的记录为trx2，接着找trx2所在的页，然后依次再把事务trx4、trx6的记录进行清理（undo log）。

由于undo page2中所有的页都被清理，因此该undo page可以被重用。

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

全局动态参数innodb_purge_batch_size用来设置每次purge操作需要清理的undo page数量。

在InnoDB 1.2版本之前，该参数默认值为20。从InnoDB 1.2版本开始，该参数的默认值为300。

问2：innodb_purge_batch_size参数是否设置的越大越好？

答：不是的。通常来说，该参数设置的越大，每次回收的undo page也就越多，这样可供重用的undo page就越多，减少了磁盘存储空间与分配的开销。不过，若该参数设置的太大，则每次需要purge处理的undo page就越多，从而导致CPU和磁盘IO过于集中对于undo page的处理，使性能下降。

注：MySQL官方手册建议，普通用户不需要调整该参数。

问3：当InnoDB存储引擎的压力非常大时，并不能高效地进行purge操作。那么history list的长度会变得越来越长此时怎么办？

答：使用全局动态参数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操作完成后，重新进行计算。

InnoDB 1.2版本引入了参数innodb_max_purge_lag_delay，其用来控制delay的最大毫秒数。当上述计算得到的delay大于该参数时，将delay设置为innodb_max_purge_lag_delay，避免由于purge操作缓慢导致其它SQL线程出现无限制的等待。

group commit

---

若事务为非只读事务，则每次事务提交时都要进行一次fsynu操作，以保证重做日志都写入磁盘。

为了提高fsync的效率，当前数据库都提供了group commit的功能，即一次fsync可以刷新确保多个事务日志被写入文件。对于InnoDB存储引擎来说，事务提交时会进行两个阶段的操作：

1. 修改内存中事务对应的信息，并将日志写入重做日志缓冲。

2. 调用fsync将确保日志都从重做日志缓冲写入磁盘。

步骤2相对较慢，因为存储引擎需要与磁盘打交道。

group commit原理：

当有事务进行步骤2时，其他事务可以进行步骤1，正在提交的事务完成提交后，再次进行步骤2时，可以将多个事务的重做日志通过一次fsync刷新到磁盘，这样就大大减少了磁盘的压力，从而提高数据库的整体性能。

对于写入或更新较为频繁的操作，group commit的效果尤为明显。

然而在InnoDB 1.2版本之前，在开启二进制日志后，InnoDB存储引擎的group commit功能会失效，从而导致性能的下降。并且在线环境多使用replication环境，因此二进制日志的选项基本都为开启状态，因此这个问题尤为显著。

导致这个问题的原因是在开启二进制日志后，为了保证存储引擎层中的事务和二进制日志的一致性，二者之间使用了两阶段事务，其步骤如下：

1. 当事务提交时InnoDB存储引擎进行prepare操作。

2. MySQL数据库上层写入二进制日志。

3. InnoDB存储引擎层将日志写入重做日志文件。

   1. 修改内存中事务对应的信息，并且将日志写入重做日志缓冲。

   2. 调用fsync将确保日志都从重做日志缓冲写入磁盘。

注意：步骤2和步骤3都需要进行一次fsync操作才能保证上下两层数据的一致性，分别由sync_binlog、innodb_flush_log _at_trx_commit参数控制。

上述整个过程流程图如下：

为了保证MySQL数据库上层二进制日志的写入顺序和InnoDB层的事务提交顺序一致，MySQL数据库内部使用了prepare_commit_ mutex这个锁。但是在启用这个锁之后，步骤3中的步骤a不可以在其它事务执行步骤b时进行，从而导致了group commit失效。

问1：为什么需要保证MySQL数据库上层二进制日志的写入顺序和InnoDB层的事务提交顺序一致呢？

答：因为备份及恢复的需要。

例如通过工具xtrabackup或者ibbackup进行备份，并用来建立replication，如图所示：

从上图可以看到若通过在线备份进行数据库恢复来重新建立replication，事务T1的数据会产生丢失。因为在InnoDB存储引擎层会检测事务T3在上下两层都完成了提交，不需要再进行恢复。

因此通过锁prepare_commit_mutex以串行的方式来保证顺序性，然而这会使group commit无法生效，如图所示：

问2：如何解决prepare_commit_mutex锁导致group commit失效问题？

答：自MySQL 5.6开始，该问题就已经被解决，且将其称为Binary Log Group Commit（BLGC）。

MySQL 5.6 BLGC的具体实现如下：

在MySQL数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为leader，其它事务称为follower，leader控制着follower的行为。

BLGC的步骤分为以下三个阶段：

1. Flush阶段，将每个事务的二进制日志写到内存中。

2. Sync阶段，将内存中的二进制日志刷新到磁盘（按队列中事务的顺序），若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的写入，这就是BLGC。

3. Commit阶段，leader根据顺序调用存储引擎层事务的提交，InnoDB存储引擎本就支持group commit，因此修复了原先由于锁prepare_commit_mutex导致group commit失效的问题。

注：该解决方案去除了prepare_commit_mutex锁。

当有一组事务在进行Commit阶段时，其它新事务可以进行Flush阶段，从而使group commit不断生效。

此外，参数binlog_max_flush_queue_time用来控制Flush阶段中的等待时间，即上一组事务完成提交，当前组的事务也不马上进入fsync阶段，而是至少需要等待一段时间。

问3：为什么上一组事务提交后，需要等待一段时间再提交当前组事务？

答：这样可以使得group commit中的事务数量更多，但如果实物数量过多可能会导致事务的响应时间变慢。该参数默认值为0，建议值也为0。除非用户的MySQL数据库系统中有着大量的连接（如100个连接），并且再不断地进行事务的写入或更新操作。