大白话 mysql 之详细分析 mysql 事务日志

redo log 与 binlog

虽然可能大部分文章都有介绍过，但为了文章的完整性，我们还是从 redo log 和 binlog 的区别聊起。

位置不同

首先就是两个日志所处的位置不同了，mysql 的整体架构可分为 server 层和存储引擎层，mysql 采用插拔式的存储引擎，常见的存储引擎有 myisam、innodb、memory 等，在创建表时指定要使用的存储引擎 (create table .... engine=innodb)。

binlog 是存在于 server 层的日志，也就是无论使用哪种存储引擎，都能使用 binlog 记录执行语句。而 redolog 是 innodb 存储引擎特有的。

大小不同

binlog 分多个日志文件记录，单个文件的大小通过 max_binlog_size 设置，采用追加的方式写入，当 binlog 大小超过 max_binlog_size 设置的大小会创建新的日志文件，然后切换到新文件继续写入。此外，可通过 expire_logs_days 设置 binlog 日志保留的天数。

redolog 的大小是固定的，在 mysql 中可以通过修改配置参数 innodb_log_files_in_group 和 innodb_log_file_size 配置日志文件数量和每个日志文件大小，采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。

记录内容不同

binlog 记录操作的方法是逻辑性的语句。有 statement 和 row 两种记录格式， statement 格式记 sql 语句；row 格式会记录更新前和更新后行的内容.

而 redolog 记录的是数据库中每个页的修改。比如 “在某个数据页上做了什么修改”

二阶段更新流程

了解了两种日志的区别后，我们再来通过一条更新语句的执行流程来看看这两个日志分别如何写入的。语句内容为 update t set a = a + 1 where id = 1

1. 执行器通过 innodb 引擎获取 id = 1 的数据，如果数据本身在内存中，会直接返回给执行器；否则，先从磁盘中读入内存，再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中。然后将对内存数据页的更新内容记录在 redolog buffer 中，此时，buffer 中的这条语句状态为 prepare。然后告知执行器执行完成了，随时可以提交事务。
4. server 层提交事务时，会先将这个操作的日志写入 binlog buffer 中，再调用引擎的事务提交接口，引擎会将刚写入的 redolog 记录状态修改为 commit。更新完成。

可以发现，一次更新后，不仅数据存在内存中，redolog 和 binlog 也是先写到内存中，之后再根据设定的落盘机制进行日志落盘。

日志落盘

binlog 落盘策略

mysql 通过 sync_binlog 参数来控制 binlog buffer 的日志落盘策略。

sync_binlog = 0, 表示 mysql 不控制 binlog 的刷新，使用文件系统的缓存刷新策略，这时性能最好，同时风险也是最大的，一旦系统 crash，binlog buffer 中的日志数据都将丢失。

sync_binlog = 1 表示每次提交事务都会将 buffer 中的日志数据同步刷到磁盘中，最安全但由于刷盘频率较高，性能也是最差的。

sync_binlog > 1 表示 binlog buffer 每写入 sync_binlog 次事务后，再刷日志数据到磁盘中。

redolog 落盘策略

在讲 redolog 持久化之前，我们先了解下 write 和 fsync 两个系统调用，操作系统中，内存被划分为用户空间和内核空间，用户空间存放着应用程序的缓存数据，redolog buffer 就存在于用户空间中，要把用户空间的数据持久化到磁盘中，需要先调用 write 系统调用，把数据先写入内核空间，之后再调用 fsync 系统调用，将内核空间的数据写入到磁盘中。

mysql 通过 innodb_flush_log_at_trx_commit 参数控制 redo log buffer 写入磁盘的时机。

innodb_flush_log_at_trx_commit = 0 表示事务提交时，日志继续保存在 redolog buffer 中，根据 innodb_flush_log_at_timeout 设置的间隔调用 write 和 fsync 将日志持久化到磁盘中，innodb_flush_log_at_timeout 默认为 1，也就是日志每秒写入到磁盘中。批量写入，io 性能较好，但数据丢失风险较大。

innodb_flush_log_at_trx_commit = 1 表示事务提交时，都将调用 write 和 fsync 将日志写入磁盘。这种方式不会丢失任何数据，但 io 性能较差。

innodb_flush_log_at_trx_commit = 2 表示事务提交时，都会调用 write 将日志写入到内核缓存中，之后每秒调用 fsync 将日志写入磁盘。这种也比较安全，即使 mysql 程序奔溃了，os buffer 中的日志也不会丢失。当然，如果操作系统也奔溃了，这部分日志也就不见了。

Q&A

❝Q: 处于 prepare 状态的 redolog 会被刷新到磁盘中吗？ A: 会的，例如同一时刻，有 a 和 b 两个事务，a 处于 prepare，b 进行 commit 触发日志刷盘，这时会把 a 的 redo 日志也刷到磁盘中。 ❞

❝Q: binlog 是否是多余的，可以使用 redolog 代替 binlog 吗？ A: 首先，就支持事务方面，binlog 确实用处是不大的，在奔溃恢复的时候需要通过 binlog 确定事务是否该提交也只是避免 binlog 被应用到备库上了，如果主库直接回滚会导致主备数据不一致。 但 binlog 的” 归档 “功能是 redolog 不具备的。redolog 大小固定，采用循环写，较早的日志会被覆盖，无法持久保存，而 binlog 是不限制大小的，日志追加写入。只要有保留 binlog 日志，可以恢复数据库任何时刻的状态。 ❞

❝Q: binlog 和 redolog 的几种落盘策略，也是频繁写磁盘，与直接数据写磁盘有什么区别吗？ A: 日志文件是存储在连续的若干个数据页中的，所以在写日志到磁盘时只需要进行一次寻址，属于顺序读写；而写数据时，一次事务可能需要改动的数据可能涉及好几个离散的数据页，写磁盘时需要进行多次「寻道 -> 旋转」的寻址过程，属于随机读写，速度比顺序读写差了好几个数量级。 ❞

数据落盘

为了避免频繁写入磁盘导致的性能瓶颈，数据页先在内存中修改，在内存中发生过修改的页称为脏页 (因为此时页中的数据与磁盘的不一致，是” 脏 “的), 改动的数据页需要找时间同步到磁盘中，这个过程称为” 刷脏页”。

LSN

在 innodb 中，每对一个数据页的修改，都会生成一个 8 字节的序列号 lsn 来标记版本，lsn 的值全局单调递增，随着日志的写入而逐渐增大，lsn 存在于数据页和 redo log 中。 在整个更新过程中，有几个 lsn 比较值得关注：

1. 修改内存数据页中的数据时，会更新内存数据页中的 LSN，暂称为 data_in_buffer_lsn。
2. 向 redolog buffer 写入日志时，会记录下对应的 LSN，暂称为 redo_log_in_buffer_lsn。
3. 当触发到 redolog 的几种刷盘策略时，会将 redolog buffer 中的日志刷入磁盘中，并在该文件记下对应的 LSN，暂称为 redo_log_on_disk_lsn。
4. 数据从内存中刷到磁盘时，会在磁盘上对应的数据页记录下当前的 LSN，暂称为 data_on_disk_lsn。
5. innodb 会在适当的时候将 redolog 上记录的对应数据页的改动同步到磁盘中，同步进度也是通过 lsn 标示，称为 checkpoint_lsn。（后文会详细介绍）

可以通过 show engine innodb status 查看各 lsn 的值。

❝lsn 可以理解为数据库从创建以来产生的 redo 日志量，这个值越大，说明数据库的更新越多，也可以理解为更新的时刻。此外，每个数据页上也有一个 lsn，表示最后被修改时的 lsn，值越大表示越晚被修改。比如，数据页 A 的 lsn 为 100，数据页 B 的 lsn 为 200，checkpoint lsn 为 150，系统 lsn 为 300，表示当前系统已经更新到 300，小于 150 的数据页已经被刷到磁盘上，因此数据页 A 的最新数据一定在磁盘上，而数据页 B 则不一定，有可能还在内存中。 ❞

下面我们来讨论下 innodb 中发生刷脏页的几种时机。

数据落盘时机

定时刷新

innodb 的主线程会定时将一定比例的脏页刷新到磁盘中，这个过程是异步的，不会影响到查询 / 更新等其他操作。

系统内存不够用

innodb 会维护一个内存数据页的 lru 列表，并通过一个单独的 page clear 线程来保证一定的空闲数据页，当空闲页不足时，会将 lru 尾部的内存页淘汰掉，如果淘汰的页中有脏页，会先将脏页数据刷新到磁盘中。

脏页比例过高

innodb 中，有个 innodb_max_dirty_pages_pct 参数，用于控制脏页在内存中的占比，当脏页比例超过设置的比例后，会刷新一部分脏页到磁盘中。

mysql> show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 90.000000 |
+----------------------------+-----------+

数据库正常关闭

参数 innodb_fast_shutdown 控制着数据库关闭时的落盘策略，当设置为 1 时，会将所有的日志脏页和数据脏页都刷新到磁盘中；设置为 2 时，仅保证日志落盘。

redo log checkpoint 刷盘

再回顾下更新的流程，更新操作记录到 redolog，数据更新到内存中，整个更新操作就算结束了。 如果数据库异常关闭了，下次启动时，我们需要根据 redolog 将相应的数据页的数据改动恢复回来。

但 redolog 大小是固定的，采用循环写的模式，写到结尾时，会回到开头循环写日志。 所以，随着更新操作次数的积累，redolog 上的记录会被覆盖掉，有些改动也就丢失了。

那不限制 redolog 的大小可以吗？ 可以试想下，redolog 达到 1TG，数据库数据量有 10TG，异常重启时，为了恢复数据页的改动。我们需要读取 1T 的日志进行恢复。如果全部的数据页都发生了修改，我们还需要将 10TG 的数据全部载入到内存中。 所以，不限制 redolog 大小后，会出现另外两个问题：

1. 恢复速度较慢；
2. 内存无法缓存数据库所有的数据。

redolog 采用 checkpoint 策略，会定期将 redolog 上的数据修改逐渐刷新到磁盘中，同步进度用 lsn 标示，称为 checkpoint_lsn。redolog 根据 checkpoint_lsn 可以划分为两部分，小于 checkpoint_lsn 的日志对应的数据页改动已经刷新到磁盘中，这部分日志可被覆盖重新写入；大于 checkpoint_lsn 部分日志对应改动还未同步到磁盘。

redolog checkpoint 刷盘分为异步刷盘和同步刷盘。

checkpoint_age = redo_lsn - checkpoint_lsn
async_water_mark = 75% * total_redo_log_file_size
sync_water_mark = 90% * total_redo_log_file_size

checkpoint_age < async_water_mark, 表示当前脏页数据较少，不会触发 redolog checkpoint 刷盘。

async_water_mark < checkpoint_age < sync_water_mark, 会异步将一定量的脏页刷新到磁盘中，使得满足 checkpoint_age < async_water_mark。异步刷新不会影响其他更新操作。

checkpoint_age > sync_water_mark, 当 redolog 容量设置的较小，同时进行大量的更新操作，导致剩余可使用的日志较少，会触发同步刷新，将脏页刷新到磁盘中，直到满足 checkpoint_age < async_water_mark，同步刷新会阻塞用户的更新操作。

Q&A

❝Q: 除了 redolog checkpoint，其他几种情况刷脏页会推动 checkpoint_lsn 吗？ A: 不会。缓冲池会维护一个管理脏页的 flush_list, 一个数据页因修改了数据成为脏页后，会添加到 flush_list 中，脏页在刷新到磁盘中后，会从 flush_list 中去掉。 flush_list 按数据页的最早修改 lsn (oldest_modifcation) 从小到大排序。比如一个干净页变为脏页后，data_in_buffer_lsn=100，在 flush_list 的位置为 1，当数据页再次发生改动时，data_in_buffer_lsn 变为 120，但在 flush_list 的位置不变。 进行 redo checkpoint 时，选择的日志只需要与 flush_list 上最老的页 (拥有 flsuh_list 上最小的 lsn) 进行比较即可：

1. page_noflush_list != page_noredo, 表示该脏页数据已被同步到磁盘中，推进 checkpoint_lsn。
2. page_noflush_list == page_noredo, 将该脏页刷新到磁盘中，推进 checkpoint_lsn。

❞

❝Q: checkpoint 信息存在哪？如何存储？ A: checkpoint 信息存储在第一个 redo 日志文件的文件头中。储存采用双份存储，轮流读写的方式。 在第一个 redo 日志文件的文件头中有两个地方用于存储 checkpoint 信息，记录时来回读取这两个 checkpoint 域。假设只有一个 checkpoint 域，当更新 checkpoint 一半时，服务器也挂了，会导致整个 checkpoint 域不可用。这样数据库将无法做崩溃恢复，从而无法启动。如果有两个 checkpoint 域，那么即使一个写坏了，还可以用另外一个尝试恢复，虽然有可能这个时候日志已经被覆盖，但是至少提高了恢复成功的概率。两个 checkpoint 域轮流写，也能减少磁盘扇区故障带来的影响。 ❞

奔溃恢复

用户修改数据并成功提交了事务，此时数据改动在内存中还未落盘，如果这个时候数据库挂了，重启后，需要从日志中将成功提交的事务数据改动恢复后重新写入磁盘，保证数据不丢失，同时还要回滚没有提交的事务。奔溃恢复中，除了需要 redolog 和 binlog 日志，还离不开 undo 日志的支持。

undo log

进行更新操作时，都会产生 undo 日志：当 delete 一条记录时，会记录一条对应的 insert 日志。当 update 一条记录时，会记录一条对应相反的 update 日志。当 insert 一条记录时，会记录一条 delete 日志。

需要回滚事务时，只需要执行对应的 undo 操作，就可以将数据恢复。此外，通过 undo 日志，可以保证事务的隔离性。假设隔离级别设为读提交，当未提交的事务 A 修改了 id=1 对应的行数据，此时事务 B 想要读取 id=1 的数据，可以先拿着最新版本的数据，顺着 undo 日志找到满足其可见性的记录。

undo 日志与普通的数据页一样，对于 undo 页的修改，需要先写 redo 日志。也可能会由于 lru 的规则被淘汰出内存，之后再从磁盘中读取。

奔溃恢复流程

整个奔溃恢复流程可以分为 redo前滚和 undo回滚两部分。

redo 前滚

对于 checkpoint_lsn 之前的日志，对应改动已经落盘，不需要关心。首先初始化一个 hash_table，扫描 checkpoint_lsn 之后的日志，将同一个数据页的日志分发到 hash_table 的相同位置，并按日志的 lsn 从小到大排序。扫描后，遍历整个哈希表，依次应用每个数据页的日志。应用完后，内存中数据页的状态就恢复到了奔溃之前。

undo 回滚

接着，初始化 undo 日志，按操作类型分为 undo_insert_list 和 undo_update_list，遍历两个链表，根据日志中记录的事务的状态重建事务状态，TRX_ACTIVE 表示需要回滚，TRX_STATE_PREPARED 表示可能需要回滚。然后将事务加入到 trx_list 链表中，之后，遍历 trx_list，按照事务的不同状态回滚或提交。对于 TRX_ACTIVE 状态的事务，利用 undo 日志直接回滚；对于 TRX_STATE_PREPARED 状态的事务，根据 server 层的 binlog 来决定是否回滚，如果 binlog 已经写了并且日志是完整的，则提交该事务，否则就回滚。

Q&A

❝Q: undo 日志什么时候会删除？ A: undo 按操作类型可分为 update/delete/insert， insert 操作在事务提交前只对当前事务可见，产生的 Undo 日志可以在事务提交后直接删除。而 update/delete 操作，其他事务可能需要老版本数据，需要保留到 undo 操作对应的事务 id 比数据库当前所有的事务快照都小（此时数据库所有事务对此次改动均可见），才可以删除。 ❞