🪴 Obsidian Publish

SQL更新语句是如何执行的？

37分钟阅读

Doc: 02.日志系统：一条SQL更新语句是如何执行的？.pdf

binlog

binlog 概念

binlog 是 逻辑日志，用于记录数据库执行的写入操作（查询不记录）信息，并且是顺序写。

binlog 属于 MySQL Server 层记录，和引擎层无关。

逻辑日志，属于 MySQL Server 层记录；binlog 记录语句的原始逻辑。
物理日志，属于 InnoDB 存储引擎产生的；redo log 记录某个数据页上做了什么修改。

当 MySQL 创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。

MySQL 服务重新启动一次，以“.000001”为后缀的文件就会增加一个，并且后缀名按 1 递增。即日志文件的个数与 MySQL 服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是 1GB），也会创建一个新的日志文件。

binlog 作用

在实际应用中，主要用在两个场景：主从复制和数据恢复

  • 主从复制场景：在 Master 主端开启 binlog，将 binlog 发生到各个 Slave 从端，Slave 从端重放 binlog 从而达到主从数据一致
  • 数据恢复场景：通过使用 mysqlbinlog 工具来恢复数据

binlog 记录格式

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

statement 格式

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

row 格式 (默认)

使用 statement 格式，涉及到 update_time=now() 获取系统时间等函数，同步数据时会出现问题。

使用 row 格式，记录的内容不再是简单的 SQL 语句了，还包含操作的具体数据，记录内容如下。

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

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

mixed 格式

row 格式虽好，但是会比较占用空间，恢复与同步时会更消耗 IO 资源，影响执行速度。

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

binlog 记录过程

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

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

系统给 binlog cache 分配了一片内存，每个线程一个。我们可以通过 binlog_cache_size 参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（ Swap ）。

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

binlog 刷盘时机

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

sync_binlog = 0

  • 每次提交事务都只 write，由系统自行判断什么时候执行 fsync。
  • 机器宕机，page cache 里面的 binglog 会丢失。

sync_binlog = 1

  • 每次提交事务进行 write + fsync。

sync_binlog = N

  • 每次提交事务都 write，但累积 N 个事务后才 fsync。
  • 在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。
  • 机器宕机，会丢失最近 N 个事务的 binlog 日志。

在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

binlog 操作

查看与修改配置

查看配置 binlog 参数配置

show variables like '%log_bin%';

查看 binlog 格式

show variables like 'binlog_format';

修改 MySQL 的 my.cnf 或 my.ini 文件可以设置二进制日志的相关参数：

log-bin= # binlog文件位置
binlog_expire_logs_seconds=600
max_binlog_size=100M

命令行(只能)修改 session 级别

SET sql_log_bin=0;

查看当前二进制日志文件列表及大小

SHOW BINARY LOGS;

查看 binlog 日志内容

将行事件以伪 SQL 的形式表现出来

mysqlbinlog -v "/var/lib/mysql/binlog/xxx-bin.000002"

将行事件以伪 SQL 的形式表现出来，其中关闭 binlog 格式的语句。

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"

可查看参数帮助

mysqlbinlog --no-defaults --help

更方面的查询命令

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

  • IN 'log_name' ：指定要查询的 binlog 文件名（不指定就是第一个 binlog 文件）
  • FROM pos ：指定从哪个 pos 起始点开始查起（不指定就是从整个文件首个 pos 点开始算）
  • LIMIT [offset] ：偏移量(不指定就是 0)
  • row_count : 查询总条数（不指定就是所有行）

使用日志恢复数据

mysqlbinlog [option] filename|mysql –uuser -ppass;
  • filename ：是日志文件名。
  • option ：可选项，比较重要的两对 option 参数是—start-date、—stop-date 和 —start-position、—stop-position。
    —start-date 和 —stop-date ：可以指定恢复数据库的起始时间点和结束时间点。
    —start-position 和—stop-position ：可以指定恢复数据的开始位置和结束位置。

使用 mysqlbinlog 命令进行恢复操作时，必须是编号小的先恢复，例如 xxx-bin.000001 必须在 xxx-bin.000002 之前恢复。

删除二进制日志

MySQL 的二进制文件可以配置自动删除，同时 MySQL 也提供了安全的手动删除二进制文件的方法。
PURGE MASTER LOGS 只删除指定部分的二进制日志文件， RESET MASTER 删除所有的二进制日志文件。具体如下：

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

redo log

redo log（重做日志）是 InnoDB 存储引擎独有的，属于物理日志。当 MySQL 实例挂了或宕机了，重启时，InnoDB 存储引擎会使用 redo log 恢复数据。

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新；没有则从磁盘中将对应数据加载到 Buffer Pool 中。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

小贴士：每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成

redo log 作用

  • 前滚操作：具备 crash-safe 能力，提供断电重启时解决事务丢失数据问题。
  • 提高性能：先写 redo log 记录更新。当等到有空闲线程、内存不足、redo log 满了时 刷脏。写 redo log 是顺序写入，刷脏是随机写，节省的是随机写磁盘的 IO 消耗（转成顺序写），所以性能得到提升。此技术称为 WAL 技术：Write-Ahead Logging，它的关键点就是先写日记磁盘，再写数据磁盘。

redo log 刷盘时机

事务修改数据时，如果 buffer pool 中存在，则更新 buffer pool。
事务修改数据时，会将操作写入 redo log buffer。

刷盘，是将 redo log buffer 的数据写入磁盘，即从 redo log buffer 到 os page cache 到 磁盘 的过程。

redo log 一般有三种刷盘的时机：

  • (后台线程)每隔 1 秒，就会把 redo log buffer 中的内容写到文件系统缓存 page cache 中，文件系统自行决定实际调用 fsync 将 page cache 保存到磁盘中。
  • InnoDB 引擎提供 innodb_flush_log_at_trx_commit 参数，可以选择在事务提交时进行刷盘操作。
  • 当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

InnoDB 存储引擎提供的 innodb_flush_log_at_trx_commit 参数，支持三种策略：

  • 0 ：每次事务提交时不进行刷盘操作
  • 1 ：每次事务提交时都将进行刷盘操作（默认值），调用 fsync 对 redo log 进行刷盘
  • 2 ：每次事务提交时都只把 redo log buffer 内容写入 page cache

刷盘的时机和策略使得 redo log 可能在事务未提交时就存储到磁盘上。

Fetching Title#e03a

三种策略对应的故障结果：

  • 0 ：如果 MySQL 挂了或宕机可能会有 1 秒数据的丢失。
  • 1 ：只要事务提交成功，redo log 记录就一定在硬盘里，不会有任何数据丢失。如果事务执行期间 MySQL 挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。
  • 2 ：只要事务提交成功，redo log buffer 中的内容只写入文件系统缓存（ page cache ）。如果仅仅只是 MySQL 挂了不会有任何数据丢失，但是宕机可能会有 1 秒数据的丢失。

通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

redo log 两阶段提交

redo log 与 binlog

  • redo log （重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力。
  • binlog （归档日志）保证了 MySQL 集群架构的数据一致性。
  • 虽然它们都属于持久化的保证，但是侧重点不同。

Before

redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以 redo log 与 binlog 的写入时机不一样。

flowchart LR
 subgraph Z [事务]
 A[开始事务] --> B[更新数据] --> C[提交事务]
 end
 subgraph Y ["Server 层"]
 D[执行器]
 E["写入binlog"]
 end
 subgraph X ["存储引擎层"]
 F[InnoDB]
 G["写入redo log"]
 end
 C --> Y
 Z --> X

数据不一致问题

由于两者写入时机不一样，因此可能出现通过日志获得的结果不一致。

例如，修改 table 中的数据，redo log 写日志正常，binlog 写日志出错。数据恢复时，主数据库数据是修改后的数据，从/备份数据库数据是修改前的数据，数据不一致。

解决方案 - 两阶段提交

InnoDB 存储引擎使用两阶段提交方案。

原理很简单，将 redo log 的写入拆成了两个步骤 prepare 和 commit，这就是两阶段提交。

flowchart LR
 A[开始事务] --> B[更新数据] --> C["写入redo log(Prepare 阶段)"]  --> D[提交事务]
 subgraph D [提交事务]
 E["写入binlog"] --> F["redo log 设置 commit 阶段"]
 end

两阶段提交 - redo log 设置 commit 正常

直接恢复数据。

两阶段提交 - redo log 设置 commit 异常

虽然 redo log 是处于 prepare 阶段，但是能通过事务 id 找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

两阶段提交 - binlog 异常

因为 MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于 prepare 阶段，并且没有对应 binlog 日志，就会回滚该事务。

如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

  1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。
    但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。
    然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

  2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

redo log 容灾恢复

MySQL 的处理过程如下

  • 判断 redo log 是否完整，如果判断是完整 commit 的，直接用 redo log 恢复
  • 如果 redo log 只是预提交 prepare 但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log ，用 redo log 恢复，不完整就回滚事务，丢弃数据。

只有在 redo log 状态为 prepare 时，才会去检查 binlog 是否存在，否则只校验 redo log 是否是 commit 就可以啦。怎么检查 binlog ：一个完整事务 binlog 结尾有固定的格式。

redo log 日志文件组

日志文件组的概念

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

日志文件组的数据结构

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写。

日志文件组的两个属性

  • write pos 是当前记录的位置，一边写一边后移
  • checkpoint 是当前要擦除的位置，也是往后推移

日志文件组的过程

  • redo log 记录到日志文件组时，write pos 位置就会后移更新。
  • MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。
  • 当 write pos 追上 checkpoint 时，表示日志文件组满了，不能添加新纪录，MySQL 必须停下来清理一些数据。

redo log 检查点

检查点 (checkpoint) 是为了定期将 db buffer 的内容刷新到 data file。当遇到内存不足、db buffer 已满等情况时，需要将 db buffer 中的内容/部分内容（特别是脏数据）转储到 data file 中。在转储时，会记录 checkpoint 发生的”时刻“。在故障回复时候，只需要 redo/undo 最近的一次 checkpoint 之后的操作。

  1. 数据库正常关闭时，即 innodb_fast_shutdown=0 时需要执行 sharp checkpoint
  2. redo log 发生切换时或者 redo log 快满的时候进行 fuzzy checkpoint
  3. master thread 每隔 1 秒 或 10 秒 定期进行 fuzzy checkpoint
  4. innodb 保证有足够多的空闲 page，如果发现不足，需要移除 lru list 末尾的 page，如果这些 page 是脏页，那么也需要 fuzzy checkpoint.
  5. innodb buffer pool 中脏页比超过 innodb_max_dirty_pages_pct 时也会触发 fuzzy checkpoint

Checkpoint 发生的时间、条件及脏页的选择等都非常复杂。而 Checkpoint 所做的事情无外乎是将缓冲池中的脏页刷回到磁盘，不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发 Checkpoint。这些本文不会去研究。

redo log 相关补充

redo log block

redo log block 共有 512 字节，头部 12 个字节+身体 496 字节+尾部 4 字节。并行的事务交替的写入 redo log block 中。

redo log 配置

innodb_log_group_home_dir ：指定 redo log 文件组所在的路径，默认值为 ./ ，表示在数据库的数据目录下。MySQL 的默认数据目录( var/lib/mysql)下默认有两个名为 ib_logfile0 和 ib_logfile1 的文件，log buffer 中的日志默认情况下就是刷新到这两个磁盘文件中。此 redo 日志文件位置还可以修改。

innodb_log_files_in_group ：指明 redo log file 的个数，命名方式如：ib_logfile0，iblogfile1… iblogfilen。默认 2 个，最大 100 个。

innodb_flush_log_at_trx_commit：控制 redo log 刷新到磁盘的策略，默认为 1。

innodb_log_file_size ：单个 redo log 文件设置大小，默认值为 48M 。最大值为 512G，注意最大值指的是整个 redo log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值 512G。

redo log LSN

面试官的灵魂一击：你懂 MySQL 事务日志吗？ - 简书

可以简单理解 SLN 就是记录从开始到现在已经产生了多少字节的 redo log 值，可以当作 redo log 写入点。

redo log 组提交

从 MySQL 看到的 TPS 是每秒两万的话，每秒就会写四万次磁盘。但是，我用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的 TPS？

解释这个问题，就要用到组提交（group commit）机制了。

如图所示，是三个并发事务(trx1, trx2, trx3)在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160。

  1. trx1 是第一个到达的，会被选为这组的 leader；
  2. 等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
  3. trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；
  4. 这时候 trx2 和 trx3 就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。

在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。

MySQL 为了让组提交的效果更好，把 redo log 做 fsync 的时间拖到了步骤 1 之后。也就是如下图所示：

500

这么一来，binlog 也可以组提交了。在执行图 5 中第 4 步把 binlog fsync 到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。

不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果你想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。

  1. binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
  2. binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync。

所以，当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 也无效了。

redo log 小结

现在我们来思考一个问题： 只要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？

它们不都是刷盘么？差别在哪里？

实际上，数据页大小是 16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，有必要把完整的数据页刷盘吗？

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

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移
量、更新值，再加上是顺序写，所以刷盘速度很快。

所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

其实内存的数据页在一定时机也会刷盘，我们把这称为页合并，讲 Buffer Pool 的时候会对这块细说

undo log

undo log 概念

undo log 是 逻辑日记 、回滚日记。回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

逻辑格式的日志，在执行 undo 的时候，仅仅是将数据从逻辑地址上恢复至事务之前的状态，而不是从物理页面上操作实现的。

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

undo log 作用

回滚数据
当程序发生异常错误时等，根据执行 undo log 就可以回滚到事务之前的数据状态，保证原子性。

保证 MVCC 视图的一致性
通过 undo log 找到对应的数据版本号，是保证 MVCC 视图的一致性的必要条件。

undo log 类型

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

  • insert undo log
  • update undo log

undo log 存储结构

rollback segment 回滚段

undo log 的存储由 InnoDB 存储引擎实现，数据保存在 InnoDB 的数据文件中。在 InnoDB 存储引擎中，undo log 是采用分段(segment)的方式进行存储的。rollback segment 称为回滚段，每个回滚段中有 1024 个 undo log segment。

在 MySQL5.5 之前，只支持 1 个 rollback segment，也就是只能记录 1024 个 undo 操作。在 MySQL5.5 之后，可以支持 128 个 rollback segment，分别从 resg slot0 - resg slot127，每一个 resg slot，也就是每一个回滚段，内部由 1024 个 undo segment 组成，即总共可以记录 128 * 1024 个 undo 操作。

回滚段与事务

  1. 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务。
  2. 当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段。
  3. 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者在回滚段允许的情况下扩展新的盘区来使用。
  4. 回滚段存在于 undo 表空间中，在数据库中可以存在多个 undo 表空间，但同一时刻只能使用一个 undo 表空间。
  5. 当事务提交时，InnoDB 存储引擎会做以下两件事情：
    将 undo log 放入列表中，以供之后的 purge 操作
    判断 undo log 所在的页是否可以重用，若可以分配给下个事务使用

undo log 存储内容：

undo log 日志里面不仅存放着数据更新前的记录，还记录着 RowID、事务 ID、回滚指针。其中事务 ID 每次递增，回滚指针第一次如果是 insert 语句的话，回滚指针为 NULL，第二次 update 之后的 undo log 的回滚指针就会指向刚刚那一条 undo log 日志，依次类推，就会形成一条 undo log 的回滚链，方便找到该条记录的历史版本。

undo log 生成过程

详细生成过程

事务开始之前，将当前事务版本生成 undo log，undo 也会产生 redo 来保证 undo log 的可靠性。每一次写操作都会生成 undo log，比如 update 操作，会生成一条更新前的记录信息，比如主键 ID，需要更新的字段以及字段对应的旧值。对于 DELETE 操作，在 delete mark 操作时，会获取该条记录最近一次的 undo log 记录，生成的新的 undo log 记录了旧记录的 trx_id（事务编号） 和 old roll_pointer（上一个 undo log 的位置） ，这样回滚时，就可以直接找到删除之前的 undo log 进行回滚操作。

undo log 删除时机

在更新数据之前，MySQL 会提前生成 undo log 日志，当事务提交的时候，并不会立即删除 undo log，因为后面可能需要进行回滚操作，要执行回滚（rollback）操作时，从缓存中读取数据。undo log 日志的删除是通过通过后台 purge 线程进行回收处理的。

  • 针对于 insert undo log
    因为 insert 操作的记录，只对事务本身可见，对其他事务不可见。故该 undo log 可以在事务提交后直接删除，不需要进行 purge 操作。
  • 针对于 update undo log
    该 undo log 可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge 线程进行最后的删除。

undo log 总结

  • undo log 日记内容不是很多，重点是 回滚 和 多版本控制 MVCC 那块。
  • 生成是事务开始后写 redo log 之前生成，当没有事务需要用到 undo log 时就会被删除。
  • 长事务会导致 undo log 过多。因为长事务存在，导致需要保存很多视图快照。
  • 举个例子，如果事务 A 一直存活，那么事务 A 之后产生的事务 B、C… 等等就算提交了，也不会被删除，因为事务 A 需要用到 B、C… 事务去找 A 的版本。所以避免长事务可以减少 undo log 日记量，当然还可以提高性能。

其他文章推荐

其他问题

WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，这磁盘读写次数也没变少呀？

  1. redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
  2. 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。

而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢？

不会。因为这时候 binlog 也还在 binlog cache 里，没发给备库。crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。

如果 sync_binlog = N，binlog_group_commit_sync_no_delay_count = M，binlog_group_commit_sync_delay = 很大值，这种情况 fsync 什么时候发生呀，min(N,M)吗？

达到 N 次以后，可以刷盘了，然后再进入(sync_delay 和 no_delay_count)这个逻辑；sync_delay 如果很大，就达到 no_delay_count 才刷。

如果 sync_binlog = 0，binlog_group_commit_sync_no_delay_count = 10，这种情况下是累计 10 个事务 fsync 一次？

只要 sync_binlog=0,也会有前面的等待逻辑，但是等完后还是不调 fsync。

有时候，sync_binlog = 0 或 =1 效果一样的原因

我说的 sync_binlog=0 或 =1 效果一样，就是看语句实际执行的效果，参数 binlog_group_commit_sync_delay 我设置成了 500000 微秒，在=1 或=0 时，对表进行 Insert，然后都会有 0.5 秒的等待，也就是执行时间都是 0.51 sec，关闭 binlog_group_commit_sync_delay，insert 执行会飞快，所以我认为=1 或=0 都是受组提交参数的影响的。

反向链接