MySQL 笔记（二）日志系统

上一篇【MySQL 笔记（一）基础架构】中，以一条 SQL 查询语句为例说明了 MySQL 的基础架构。这一篇，则以一条 SQL 更新语句了解下 MySQL 的日志系统。那么，一条 SQL 更新语句是如何执行的呢？

上一篇中有介绍过一条 SQL 查询语句的执行路径，同样的，SQL 更新语句也会走一遍那个执行路径；但是与查询不一样的是，更新流程还涉及两个非常重要的日志文件：redo log（重做日志）和 binlog（归档日志）。

一、redo log

为了提高增删改的效率，存储引擎在执行修改表的数据的操作时，通常只修改其内存拷贝，再把修改行为记录持久化到日志文件中，而不用每次都将修改的数据本身持久化到磁盘。日志写成功之后，更新内存的值，就可以给客户端返回修改成功了，至于在内存中被修改的数据，在系统比较空闲的时候再慢慢刷回到磁盘中，这就是 WAL 技术（Write-Ahead Logging），中文叫预写式日志。



有人可能会有疑问，为什么要这样做呢？这样一次更新不是需要写两次磁盘吗？答案是的，WAL 更新一次数据是需要写两次磁盘的，一次记录日志，一次持久化数据本身。这样做的原因是先写日志再写磁盘能大大提升更新效率，因为写日志文件比直接写数据到磁盘要快得多。我们来比较一下这两者的效率：

• 写文件是磁盘上一小块区域内的顺序 IO；而持久化数据本身是随机 IO 需要在磁盘的多个地方移动磁头。

• 写文件是追加的方式，而持久化数据本身；需要先查找到对应的那条记录，再更新数据，整个过程的 IO 成本、查找成本都很高。

• 对于插入操作、删除操作，对于索引的维护，还可能存在数据移动、页分裂、页合并等。



WAL 技术是在存储引擎层支持的，但并不是所有存储引擎都支持。MySQL 最流行的 InnoDB 引擎通过它实现了 crash-safe 能力，这个能力依赖的就是 redo log 和 unod log 两个日志。redo log 的大小是固定的，所以它是循环写的，从头开始写，写到末尾就又从头开始循环写。

二、binlog

从上一篇的逻辑架构图我们知道，MySQL 大体上是分为 Server 层和存储引擎层两部分的。上面我们提到的 redo log 是 InnoDB 引擎特有的，而 binlog 日志是 Server 层的，所有引擎都可以使用。

那么，我们为什么需要两份日志呢？

• 第一，binlog 日志只能用于归档，仅靠 binlog 无法实现 crash-safe 能力。而 InnoDB 引擎要想实现 crash-safe 能力，就需要 redo log

• 第二，数据库的备份恢复功能是通过归档日志实现的，所以需要 binlog，因为 redo log 是循环写，数据会被覆盖调，binlog 是追加写，不会覆盖日志，而且数据库的主从同步也是依赖 binlog 实现的



下面我们通过一条更新语句说明下执行流程，先插入以下两条数据：

INSERT INTO student(id,name,age) VALUES(1, "张三", 21),(2, "李四", 22);

当执行 UPDATE student SET age=18 WHERE id = 2 语句时，我们来看下它的内部流程：

1. 执行器先找到 id=2 这一行。如果该行所在的数据库本来就在内存中，直接返回给执行器；否则，先从磁盘读入内存，再返回给执行器。

2. 执行器拿到引擎给的数据后，把 age 改为 18，得到新的一行数据，再调用存储引擎的接口写入这行新数据。

3. 存储引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 文件，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5. 执行器调用存储引擎的提交事务接口，存储引擎把刚刚写入的 redo log 改成 commit 状态，更新完成。



如下为这个 update 语句的执行流程图，图中深色表示是在执行器中执行的，浅色表示是在 InnoDB 引擎内部执行的。图片来源于极客时间《MySQL实战45讲》

update 语句执行流程

三、两阶段提交

上图中的最后三步，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是两阶段提交。为什么必需要两阶段提交呢？这是为了让两份日志之间保持逻辑一致。想要解释清楚为什么有些难，我们先看看不用两阶段提交会怎么样好了。

1. 先写 redo log 后写 binlog。如果在刚写完 redo log 但是 binlog 还没写完时，MySQL crash 掉了。由于 redo log 可以保证 crash-safe，所以 MySQL 恢复后，id=2 这一行 age 为更新后的 18，但 binlog 日志记录的还是 age=21，此时如果用 binlog 进行数据恢复或者主从同步等依赖 binlog 实现的功能，就会导致这行的数据不一致了。

2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复之后这个事务无效，导致数据库中这行的数据还是旧值 21，但 binlog 中这行的记录为新值 18，此时如果用 binlog 做数据恢复和主从同步，也会导致这行的数据不一致。



从这里我们可以看到，如果不用两阶段提交，数据库的数据就有可能和用它的日志恢复出来的数据不一致。而使用两阶段提交就不一样了，如果 binlog 没写成功，此时 redo log 处于 prepare 状态，更新无效；如果 binlog 写成功，但是 redo log 还没更新 commit 状态就 crash 了，那么这次更新也无效。

四、小结

binlog 和 redo log 是 MySQL 备份恢复和 crash-safe 能力的依赖，是 MySQL 里面最重要的两个日志。对于这两个日志，有两个参数配置需要注意：

• innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

• sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数也建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。