MySQL的各种日志

Photo by hippopx.com

《MySQL实战45讲》笔记。

1. redo log——只是一块粉板

孔乙己又来酒馆喝酒，兜里没钱手机也没电了，只能向掌柜的赊账。掌柜有一块粉板，当客人要赊账的时候就往上写一笔，等客人少的时候或者粉板写满了就记到账本里去。还好有这块粉板，不然每次客人要赊账，掌柜都要翻看账本，在密密麻麻的账本里找到赊账客人的名字绝对不是一件容易的事，有了粉板，掌柜只要往粉板上记一笔：“孔乙己赊两文”，空闲的时候再更新到账本里去，简单多了。

同样的，MySQL也有一块“粉板”—— redo log。更新的时候，先写到 redo log 和内存里，这次更新就算是结束了。等到合适的时机再写到磁盘里，大大减小了写磁盘的次数。

redo log 是固定大小、“循环写”的，就像粉板一样，顶多也就记个十几二十条，多了就记不下了，这时会把粉板上的帐都写到账本里，再擦掉粉板，从头开始记。假设 redo log 配置了4组文件，每个文件 1G ，一共可记录 4G 的操作，写满了就会擦掉一部分记录。

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。

有了 redo log，InnoDB 就可以保证即使数据库发生了异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

2. binlog

binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如”给 ID=2 这一行的 c 字段加1“。

binlog 是“追加写”的，一个文件写完了会切换到下一个，不会覆盖以前的日志。

为什么有了 redo log 还需要 binlog?

其实 redo log 才是那个新来的仔。MySQL 自带了 binlog 日志用于归档，没有 crash-safe 的能力。InnoDB 引擎以插件的形式引入 MySQL 时，为了能够实现 crash-safe 的能力，引入了 redo log 。

一般我们用 binlog 做主从复制，数据恢复等操作。

binlog 是如何做数据恢复的？

一般我们做数据库备份是一周一备，一天一备，也可能一月一备。

假设今天中午12点，我们发现部分数据被误删了。需要恢复到昨天晚上8点这个时间段。但是数据库是每天凌晨3点的时候备份，离我们最近的一份备份数据已经缺失，只能恢复到昨天凌晨3点。这个时候我们就可以拿出昨天凌晨3点到晚上8点这个时间段的 binlog，重放到数据缺失前的那个时刻。在把这份数据恢复到线上数据库去。

3. 更新操作的执行流程

了解了 redo log 和 binlog 这两个日志的概念，我们再来看看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

执行器先找引擎取 ID=2 这一行。如果数据在内存就直接返回，如果不在内存就先从磁盘读入内存，再返回。
执行器拿到数据，给这行的 c 值加 1。
引擎将这行数据的改动更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成 commit 状态，更新完成。

下图出自《MySQL实战45讲》，浅色框表示是在 InnoDB 内部执行的，深色框表示实在执行器中执行的。

4. redo log 和 binlog 的两阶段提交

为什么需要两阶段提交？

我们先假设没有两阶段提交时，可能会有以下两种情况：

redo log 提交成功了，这时候数据库挂掉导致 binlog 没有成功写入。数据库重启之后通过 redo log 把数据恢复回来，但是 binlog 没有成功写入，导致我们在做主从复制或者数据恢复的时候，数据不一致。
binlog 提交成功了，这时候数据库挂掉导致 redo log 没有成功写入。数据库重启之后，无法恢复崩溃之前提交的那个事务，这部分数据更改在主库缺失。但是 binlog 已经成功写入了，从库反而有了该事务的改动，导致数据不一致。

综上我们知道，redo log 和 binlog 必须同时成功或同时失败，才能保证数据一致性。

两阶段提交是如何保证 redo log 和 binlog 同时成功或同时失败的？

图片来自《MySQL实战45讲》

假设已经有了两阶段提交，分析一下以下两种情况：

假设在上图的时刻A，redo log 处于 prepare 之后，写 binlog 之前，数据库挂掉了。由于此时 binlog 还没有写，redo log 也还没有提交，所以崩溃恢复后，这个事务会回滚。这时候 binlog 还没写，所以也不会传到备库。
假设在上图的时刻B，写 binlog 之后，redo log 还没有 commit 前发生 crash。那么崩溃恢复时，MySQL 会做以下判断：
1. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
2. 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
  a. 如果是，则提交事务；
  b. 否则，回滚事务。

那么 MySQL 是怎么知道 binlog 是否完整的？

一个事务的 binlog 是有完整的格式的：

statement 格式的 binlog，最后会有 COMMIT;
row 格式的 binlog，最后会有一个 XID event。

5. change buffer

什么是 change buffer ?

当需要更新一个数据时，如果数据页在内存里就直接更新了，如果数据页不在内存里，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要读磁盘了。在下次查询需要访问到这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

如果能够将更新操作先记录在 change buffer，减少读磁盘，更新操作变快。而且数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

change buffer 是可以持久化的数据，change buffer 在内存中有拷贝，也会被写入到磁盘中。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。以下情况会触发 merge:

访问数据页
系统有后台线程定期 merge
数据库正常关闭也会触发 merge

为什么普通索引比唯一索引效率高？

查询时：
1. 普通索引查出数据页，数据页读入内存，判断是否有相等的数据，返回数据。
2. 唯一索引查出数据页，数据页读入内存，直接返回数据。
3. 虽然普通索引多了一步判断，但是数据是以页为单位读入内存的，判断大概率是内存操作，消耗很小，可以忽略。
更新时：
1. 普通索引直接更新内存或者缓存到 change buffer 中，结束。
2. 唯一索引更新时需要判断是否有数据冲突，所以无法利用 change buffer，当数据页不在内存时，必须读磁盘写入内存再做判断，效率低于普通索引。

什么情况下不适合使用 change buffer?

如果某个业务更新后马上做查询，即使我们把更新先记录在 change buffer，读取操作也会马上把数据读入内存，而且立即触发 merge 操作。这种情况下，随机访问磁盘的次数没有减少，反而增加了 change buffer 的维护代价。所以对于这种业务，change buffer 反而起到了反作用。

6. change buffer 和 redo log

插入时

插入的数据页刚好在内存中，直接更新内存中的数据页（上图1）。
数据页不在内存中，在 change buffer 里记录下对该数据页的改动（上图2）。
将上述两个动作记入 redo log 中（上图3，4）。

我们可以看到，执行这条语句的成本很低，写了两处内存（内存和change buffer），写了一处磁盘（redo log，两次操作合在一起写磁盘），而且还是顺序写（直接写日志文件）。

同时，图中两个虚线箭头，是后台操作（异步操作，空闲时间就刷的那种），不影响该语句的响应时间。

查询时

数据在内存时，直接读取。
数据不在内存时，从磁盘读入内存，然后应用 change buffer 里的操作日志，在内存生成一个最新的数据。

比较

从上面两个案例我们可以看出：

redo log 主要节省的是随机写磁盘的 IO 消耗（把更新时的随机写磁盘转成顺序写）。
change buffer 主要节省的是随机读磁盘的 IO 消耗（减少更新时读磁盘的次数）。

7. binlog 和 redo log 的持久化

binlog 的写入机制

binlog 的写入逻辑：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开写的，因此不论这个事务多大，也要确保一次性写入。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存的磁盘中。

事务提交时，执行器把 binlog cache 里的完整事务写到 binlog file 和磁盘中，并清空 binlog cache。状态如下图所示：

图中的 write，指的是日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，速度比较快。
图中的 fsync，指的是日志最终持久化到磁盘，速度慢。
write 和 fsync 的时机，由参数 sync_binlog 控制：
1. sync_binlog=0 时，表示每次提交事务都只 write，不 fsync；
2. sync_binlog=1 时，表示每次提交事务都会执行 fsync；
3. sync_binlog=N(N>1) 时，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志（没有持久化到磁盘，主机挂了就丢失了）。

redo log 的写入机制

事务在执行过程中，生成的 redo log 会先写到 redo log buffer 中。
写入到 page cache 的速度也很快，写入到磁盘的速度慢。
innodb_flush_log_at_trx_commit 参数用来控制 redo log 的写入策略：
1. 设为 0 时，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中；
2. 设为 1 时，表示每次事务提交时都将 redo log 直接持久化到磁盘；
3. 设为 2 时，表示每次事务提交时都只是把 redo log 写到 page cache。
InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
事务执行过程中写入 redo log buffer 的记录，也会随着其他事务的提交或者定时写入过程持久化到磁盘中。也就是说有些还未提交的事务的 redo log 也会被持久化。
redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，也会触发持久化操作。

分组提交

为了降低写磁盘的次数，redo log 把 write 和 fsync 拆成两个步骤，当有并发时，事务A写完 page cahce，事务B也写完了 page cache，事务A触发 fsync 的时候，会把两个事务的 redo log 并在一组，一起写磁盘。

并且为了能让更多的事务加入同一个组，InnoDB 让 redo log 和 binlog 的 write 和 fsync 交替执行，分组提交的优化，redo log 和 binlog 都有。

WAL 机制是减少磁盘写，但每次提交事务都要写 redo log 和 binlog，写磁盘的次数好像没有减少？

redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度快；（日志写磁盘都是顺序写的，事务提交后直接把数据写磁盘就是随机访问）；
组提交机制可以大幅降低磁盘的 IOPS 消耗。

MySQL 是如何保证 crash-safe 的。

redo log 是如何保证 crash-safe 的。
写到 redo log buffer 不能保证 crash-safe，写到 fs cache 也不能保证 crash-safe，只有 redo log 写入磁盘之后，数据库异常重启，从磁盘中的 redo log 拿出未执行的日志进行恢复，才算是 crash-safe。
这也说明了多个事务提交之后才写磁盘，还是会有事务丢失。只有每个事务提交后都进行写磁盘才能保证数据完全不丢失。

binlog 为什么无法保证 crash-safe?

如果 binlog 写入成功了，数据还没写入磁盘，数据库异常崩溃，重启后主库没有这部分数据，而通过 binlog 同步的从库却有了这部分配置，导致主从数据不一致。
如果数据写入磁盘，binlog 写入失败了，数据库异常崩溃，重启后主库有这部分数据，而通过 binlog 同步的从库没有这部分数据，导致主从数据不一致。

能否只使用 binlog 或 redo log 单个日志保证 crash-safe?

我的理解是：并不是单个 log 无法保证 crash-safe，而是 binlog 本身无法保证 crash-safe，因为 InnoDB 无法重新设计 binlog，所以引入了 redo log。并且花了很大力气来保证 redo log 和 binlog 的一致性。
如果重新设计 MySQL，可以使用 redo log 实现 binlog 的功能，也可以把 binlog 设计成 crash-safe 的，这样就只需要一种 log 了。