Mysql的日志体系

提起 redo log、binlog、undo log，使用 Mysql 的人一般都能说上一些内容，诸如：两阶段提交，事务的隔离性，但可能会对 WAL（Write-Ahead Log 预写日志） 本身有些陌生。

Mysql 中的索引数据（主键索引包含数据）按照 B+ 树存储，当一个更新的 sql 语句需要改动数十万行数据，不妨思考一下该如何实现？

最简单的方案是：首先根据更新语句的索引找到需要更新的条目，更新磁盘上对应的数据，更新完成后 sql 返回执行成功的结果。

那这个方案有什么问题：

• 如果在更新数据到一半时机器突然崩溃，那么数据库的数据将可能呈现不一致的状态（有些数据更新了，有些没有更新）；
• 应用服务等待数据库更新所有数据后返回执行结果，性能会很差。

WAL 一方面可以提高性能，另一方面也提高了数据库的可靠性。

write-ahead log

WAL 预写日志的思想是：在保存修改数据前先记录日志，一方面这样的日志可以循环顺序写，相比多次的随机 IO 会有显著的性能提升（尤其是在机械硬盘上），另一方面请求可以快速返回，因为只需要记录日志并刷新到磁盘即可，数据可以还未真正更新到磁盘。

举个例子

这是一个用户表（id、name、email）的 B 树结构：

在初始时所有的数据都没有修改过，write-ahead log 为空，此时来了一个更新请求：update email = d2@l.e where id = 38，通过索引很快定位到了数据所在的页，对数据进行修改：

同时在 write-ahead log 记录能标识本次修改的信息（下方只是示例，真实情况不是这么记录的），例如：

{
    "op": "update",
    "id": "38",
    "changed": "email -> d2.@le"
}

提高性能：该请求修改了内存中的数据（具体 IO 次数跟涉及到修改的索引数，数据是否本来就在内存中都有关系），并在 write-ahead log 上追加了更新信息，很快就能执行完成并返回。

崩溃恢复：也叫 crash-safe。当数据库在更新内存时崩溃，由于没有刷入磁盘，所以没有问题；当数据库更新完内存，记录了 wal 后崩溃，重启时数据库可以检查 wal，判断数据是否已成功修改，进而执行操作保证数据的一致性。

如果 wal 是一个单纯的只追加的日志文件，那么它会无限膨胀。事实上当脏页从内存刷新到磁盘后，相关的内容已经可以从 wal 中移除了，所以 wal 通常被实现为一个循环写的文件，并有检查点（check point）的概念，写指针到检查点的位置即为可以复用的空间。如果某次更新 wal 剩余空间不够了，则会推动 check point 的前进。

Mysql 中的 update 执行流程

首先，数据库很少有直接操作磁盘的情况，都会先把数据读到内存，接着更新内存中的数据，再根据情况选择写回磁盘，这可以平衡 cpu 与磁盘间的处理速度，所以影响数据库性能的一个因素有缓冲池的大小。

那么更新语句在 Mysql 中的执行流程（权限验证、词法、语法分析、生成执行计划等前置步骤）是什么呢？

1. 将磁盘中的对应的数据读入到内存缓冲区中；
2. 记录旧值对应的 undo-log；
3. 更新内存缓存区中的数据为新值；
4. 写入 redo-log（数据中包括 XID），此时为 prepare 状态；
5. 写 binlog（数据中包括同一个 XID，为了对应）；
6. 事务提交，追加状态为 commit 的 redo-log；

上述的描述中，数据还在内存中，只是被修改了，这时的数据页被称为脏页，它会在后台以某种频率或由于内存需要被其它页面替换而写回到磁盘。

这个流程里有两个很容易产生疑问的问题： 首先看下二者的区别：

• redo-log 是 Innodb 引擎独有的，binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用；
• 二者记录的内容也不相同，redo-log 记录了在某个数据页上做了什么修改，而 binlog 可以通过调整格式记录不同形式的内容（例如记录原始的 sql 语句）；
• redo log 是循环写的，空间固定；binlog 写到一定大小后会切换到下一个，并不会覆盖以前的日志。

所以 redo-log 才是后来者，redo-log 的出现提供了 crash-safe 的能力。

为什么还要有 binlog？

一方面 redo-log 是 Innodb 引擎独有的，而 Mysql 是可以选择其它引擎的；另一方面 redo-log 是近似扇形缓冲区的结构，不保留之前的历史记录，没有归档的功能。此外，Mysql 的主从同步集群也是可以用主库的 binlog 来做同步的。

为什么要采用两阶段提交？

其实分布式事务中也有两阶段提交的思想。因为一次事务既需要写 redo-log，又需要些 binlog，为了保证二者状态的一致性因此采用了两阶段提交，即先写 redo-log 并置为 prepare 状态，再写 binlog，再修改 redo-log 为 commit 状态。

以把 “c 从 0 改为 1” 为例，如果不采用该方案：

• 先写 binlog 再写 redo-log：如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志，假如拿 binlog 做了主从同步、亦或者 canal 订阅 binlog 会导致数据不一致或引发错误逻辑，利用 binlog 恢复数据时也会出问题；
• 先写 redo-log 再写 binlog：如果 redo-log 写成功之后 crash，那么崩溃恢复时 redo-log 可以恢复内存中的数据是 1，但 binlog 中没有这个日志，主从同步或利用 binlog 恢复数据时也会有问题（还是原始值0）。

如果在 redo-log 为 prepare，binlog 成功记录后 crash，重启恢复时 Mysql 检测到数据完整会提交这个事务。

上面多次提到了利用 binlog 恢复数据，binlog 记录的日志格式有三种：

• STATEMENT 格式，记录 sql 语句，但 sql 中可能包含 Now()、Random() 等字符，不安全；
• ROW 格式，记录数据行变更前后的值，最精准，但日志量会很大，更新多少记录就会有多少相关的日志；
• MIXED 格式：混合模式，MySQL 会判断 sql 是否安全选择使用 STATEMENT 还是 ROW 格式，但判断未必完全正确。

从 MySQL 8.0 开始，默认的 binlog 格式已从 STATEMENT 改为 ROW，毕竟大多数情况下，数据的一致性是更加重要的。

undo-log 与事务

update 语句也涉及到了 undo-log，在读已提交的隔离级别下，A 事务修改完成还未提交的时候，B 事务是读不到 A 事务修改的值的，这就利用了 undo-log。

事务具有四个特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

同时数据库有四个隔离级别：读未提交（Read UnCommited）、读已提交（Read Commited）、可重复读（Repeatable Read）和串行化（Serializability）。

广义的不可重复读指的是两次相同查询读取的数据不一样，这个不一样既包括数据值不一样，也包括数据量不一样。在这里，不可重复读仅指数据值不一样，即A事务开始时读取的值，即使B事务修改并且成功提交了，A事务再读还是原始值。幻读则指的是数据量不一样。

注意：【可重复读】的隔离级别在某些条件下是可以解决幻读的，涉及到 Mysql 中的锁机制~

以下图为案例分析四种隔离级别下出现的情况：

• 读未提交：A 事务中的 V1、V2、V3 分别是 2、2、 2，原因是该级别下其他事务只要修改即使不提交也能被当前事务看到；
• 读已提交：A 事务中的 V1、V2、V3 分别是 1、2、 2，原因是在查询得到 V2 时事务 B 已经提交；
• 可重复读：A 事务中的 V1、V2、V3 分别是 1、1、 2，原因是事务 A 在查询 V2 时值虽然已经被事务 B 修改，但根据该级别的规则（规则见下文），还是得到 1；
• 串行化：事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

如何实现事务隔离

可重复读也被叫做快照读，含义是事务开始的一瞬间就像生成了一个快照一样，此后的操作都是基于这个快照，因此得到的结果一致；但是随便想想也知道，如果事务真的是在开始时对数据库内容生成了快照文件，那么磁盘的使用不可计量。

Mysql 是基于 MVCC （Multiversion Concurrency Control，多版本并发控制）和 undo log。

简单来说数据库中的一条记录是有多个版本的，而且每一条记录都有以下隐藏字段

• db_row_id 隐藏的行 ID，用来生成默认聚集索引。如果我们创建数据表的时候没有指定聚集索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。
• db_trx_id 修改记录为当前版本的事务ID，所谓的事务ID是每次进行事务操作时生成的，唯一且递增。
• db_roll_ptr 回滚指针，执行该条记录对应的 undo log 信息。

我们可以认为数据库在进行事务操作的开始生成了一个快照（视图，ReadView），里面有以下几个重要的属性：

• trx_ids，系统当前正在活跃的事务 ID 集合（未提交事务ID集合）。
• low_limit_id，活跃的事务中最大的事务 ID，即下图的高水位。
• up_limit_id，活跃的事务中最小的事务 ID，即下图的低水位。
• creator_trx_id，创建这个 Read View 的事务 ID。

现在当前事务想要进行操作了，这时候它扫描到一条记录，这条记录是否应该展示呢？应该遵循以下的判断规则：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况：
   a. 若 row_trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   b. 若 row_trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

不过虽然判断某记录的当前版本对于当前事务不可见，还是需要回滚指针结合 undo log 判断历史版本是否可见。

那么读已提交和可重复读的区别是什么呢？

读已提交的 ReadView 是每次执行一条 SQL 时就生成，而可重复读是在事务一开始就生成，事务期间不变。

最后整理几个与本篇内容相关的配置项，可通过 show variables like '[配置项]' 查看：

• innodb_buffer_pool_size：设置 innodb buffer pool 的大小，也就是文中提到的缓冲池大小；
• innodb_flush_log_at_trx_commit：配置 redo-log 日志的持久化频率，设置为 1 每次事务的 redo-log 都会落盘；
• sync_binlog：配置 binlog 日志的持久化频率，设置为 1 每次事务的 binlog 都会落盘；

后两个配置项都推荐设置为 1，这可以提高数据库的数据一致性，虽然说是每次事务都会落盘，但会采用组提交的机制，多个事务的 redo-log 一起刷盘，以减少磁盘 IO 操作，提高吞吐量。

其实与缓冲池相关的配置有很多，可以通过show variables like 'innodb_buffer_pool%'查看，分享一个看到过的调优案例：Mysql 优化之 innodb_buffer_pool_size 篇 。