Commit 之后发生了什么事情

面试的时候我总喜欢提一个问题：

commit 之后，缓冲池中的脏块经历了什么？

这是一个很宽泛的问题，但是能够考验出一个 DBA 对于数据库的基本原理的理解。本文从几个知识点开始，一步一步深入的探讨这个问题的答案。

1. 缓冲池

计算机体系内有一个很有意思的现象，就是 CPU 运算速度越来越快，而磁盘的 IO 速度却很多年都在原地踏步，确切的说是机械磁盘速度依旧很慢。CPU 发出一个指令后，如果没有别的技术手段，那么只能等待磁盘慢慢吞吞的将数据返回，交给 CPU 处理，这是很浪费 CPU 时间的。

为了解决这个问题，除了中断这种好办法外，人们还添加了一个叫做内存的易失性存储，将一部分数据缓存在内存中，提高 IO 速度。

因为大部分数据被读取之后，再次被读取的可能性很大，因此利用内存可以有效地提升数据处理的效率。这种思想也被广泛借鉴到数据库系统的设计中。在任何一种关系型数据库中，总会强调缓冲池的重要性，而大部分的性能问题，也都是因为缓冲池设置不合理导致的（太小导致反复磁盘 IO，太大导致 SWAP）。

InnoDB 的缓冲池由参数 innodb_buffer_pool_size 控制，这个值在独占服务器上一般设置为物理内存的 60%-80%，在现代版本的 MySQL 中，缓冲池还支持多实例来提高并发能力，这是由参数 innodb_buffer_pool_instances 控制的，如果缓冲池的大小设置为 1G 以上才会开启。

如前文所述，很多时候缓冲出来的数据会被多次访问。考虑这样一种场景，数据库日常维护升级需要关闭，再次启动的时候，缓冲池中并没有缓冲块，因此在系统启动后的一段时间内，数据访问都会去请求磁盘，严重影响效率。如果存在一种机制，能将缓冲池导出，在重启的时候预加载到内存中，那么就能很好的解决刚才设想的问题。

这种机制 MySQL 同样有提供，可以将 innodb_buffer_pool_load_at_startup 参数设置为 ON，至于导出，有两种方式，手动的方式是在关闭的时候首先执行这样的命令：

SET innodb_buffer_pool_dump_now=ON;

自动的方式则是将配置文件中的 innodb_buffer_pool_dump_at_shutdown 项目设置为 ON。

缓冲池中的数据块虽然理论上会多次被访问，但是内存的容量是远远小于磁盘的容量的，大部分情况下也不可能将所有的数据块都缓存在缓冲池中。数据块缓冲到缓冲池中，或者数据块从缓冲池中被逐出的情况是经常发生的，对于熟悉计算机体系的人来说，看到这里应该会自动的联想到缓冲池管理数据块逐出的算法应该是 LRU。

所谓 LRU 即最近最少使用，即维护一个链表，将最近没有使用的数据块逐渐向队尾移动，直到被清理出去。但是 MySQL 对这个算法进行了改造，每次写缓冲新的块时，总是将这个块写入链表的 3/8 处，我们称这个点为 midpoint，只有当这个块被再次访问的时候，才会将这个块移动到队头位置。

如上图，midpoint 之前的部分可以成为 young list，之后的部分可以称之为 old list，将数据块添加到队头的操作我们成为 made young。根据这种数据结构，我们可以推断出：

如果发现 made young 操作很频繁，那么要检查缓冲池是否太小。

下面这段代码是 made young 的过程：

block = UT_LIST_GET_LAST(buf_pool->LRU);
while (block != NULL) {    ut_a(block->in_LRU_list);
    mutex_enter(&block->mutex);    freed = buf_LRU_free_block(block);    mutex_exit(&block->mutex);
    if (freed) {        break;    }

我们还可以通过 show engine innodb status 来观察：

里还给出了 made young 的速率。

内存是易失性存储，这种存储的问题是一旦断电后重启，所有的数据都会丢失。作为保存数据的数据库，自然是不允许数据丢失的，或者严谨点表述：

一旦提交的数据，是不允许丢失的。

比如说这样一个语句：

update Student set score=100 where name = 'zhiquan';commit;

这样一个语句首先要把 Student 表中，name='zhiquan'这条记录的数据块读取到内存中，然后对这条数据进行更改，更改之后的内存中的数据块称为脏块。在 commit 命令之后，脏块必须写入磁盘，这个过程叫做持久化。只有持久化之后的数据，才不会丢失。

这样就引出了我之前提出的问题，commit 之后，内存中的数据块经历了什么？从现在表述来看，应该是经历了持久化的过程。这个持久化的过程，还是值得说道一下的。

2. REDO 日志

现在看来，脏块在 commit 命令之后应该是写磁盘文件了，这是毋庸置疑的。不过事情并没有这么简单，我们来举一个简单的例子，我现在是一个财务人员，每天都需要记录当天的所有财务流水，记录在小本本上，晚上的时候，我会将小本本上今天的流水核算并合并到总账里。

那么我为什么不直接写总账呢，原因也很简单，总账算起来比较复杂，直接记总账会阻塞工作时间的正常流水。

MySQL 的 InnoDB 引擎也有一个类似于小本本一样的东西，称之为 Redo 日志，中文名叫做重做日志。所谓重做，意思是崩溃恢复的时候，能够重新做日志里记录的事情。

当我提交了 commit 的时候，会有一个线程将重做这个脏块写入重做日志中，这个过程是立刻马上进行的。这里需要引入几个参数，下面一一说明：

• innodb_flush_log_at_trx_commit

• innodb_flush_method

innodb_flush_log_at_trx_commit 控制了写入的时机，一般设置为 1，下面是几种选择的意义：

• 0：每秒将 redo buffer 中的数据写回到磁盘；

• 1：每次提交都将 redo buffer 中的数据写回到磁盘，并调用 fsync 函数，将其刷入磁盘中；

• 2：每次提交都将 redo buffer 中的数据写回磁盘，每秒调用 fsync 函数，将其刷入磁盘。

这样说比较迷惑，其实每次刷磁盘也有两个步骤，即写到 OS cache 和刷盘。只有调用了 fsync 才能保证数据块真的刷入到磁盘中了。这样说来，设置为 0 是最不稳定的，如果出现故障，至少 1s 内的事务都会丢失，设置为 1 是最安全的，设置为 2 的话，如果仅仅是 mysqld 进程退出，还是可以保证数据安全的，但是宕机的话就会丢失至少 1s 数据。

上图说明了这一过程的原理。

innodb_flush_method 则表示文件的打开方式，一般都会推荐设置成 O_DIRECT，我们可以看看 O_DIRECT 的官方解释：

InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.

InnoDB 使用 O_DIRECT 方式打开 data 文件，并使用 fsync()函数将数据库输入数据文件和日志文件中。

现在我们知道了，当 commit 命令发出后，脏块首先被写入了 redo log 中，而写 redo log 也是一个复杂的过程。

我刚才没有讲到的一点是，还有一个线程，会慢慢的将脏块写入数据文件。这个过程不是立刻马上进行的，可能每隔 1s 进行一次，也可能每隔 10s 进行一次。基于这种机制，数据文件是落后于 Redo 日志的。MySQL 给数据文件和 Redo 日志中最新的数据定义了一个整型的序号，我们称为 LSN。

刚才提到了重做是崩溃后重新做，那么崩溃恢复的时候，数据文件中的数据是没有问题的，但是数据文件中并没有全部的已提交的事务，因此，需要恢复 Redo 日志中 LSN-数据文件中 LSN 差值部分的数据，这部分数据量其实很小，可以很快进行恢复。

redo 日志是磁盘上的一组文件，这部分文件不是无限的，在文件写满之后，系统会覆盖最老的部分。其实 Redo 日志文件可以认为是一个环，写入操作永远在进行中。建议设置 Redo 日志大小为 2G 左右，不能太小，否则会频繁的回收空间，或者切换日志，导致系统负载升高，性能下降。

这种先写 Redo 后写数据文件的方式，成为 WAL 即预写日志。这种方式在 MongoDB 的 WiredTiger 引擎上也在应用。

3. 2PC

了解了 WAL 方式之后，我们就知道了一个数据块在 commit 之后大致的经历。需要注意的是，Redo 日志是 InnoDB 提供的特性，并不是 MySQL 共有的，在 MySQL Server 层面上，还有一个日志叫做 binlog，一般用于复制。

binlog 记录的是实际的数据变更。那么，commit 之后，一定也会写 binlog 的，这个过程和写 Redo 有什么关联呢？

1. 执行 SQL 并 commit；

2. Redo 中该事物被标记为 commit prepare 状态；

3. 写 binlog；

4. Redo 中该事物被标记为 commit 状态。

这四个步骤之后，事务才能真的提交。也正是因为这种机制的设计，才保证了从库接收到的事件一定是真正持久化的。如果步骤 3 失败，则整个事务会回滚。

这种机制就称为两阶段提交，即 2PC。

这部分看完，我们知道，一个脏块在 commit 之后，还会要写入 binlog 中，而且一旦 binlog 写入失败，该事务还是会回滚。

之前有一个参数 innodb_flush_log_at_trx_commit 控制了 Redo 的刷盘时机，那么对应的 binlog 也有一个参数来控制刷盘时机，即 sync_binlog 参数，其值的设定解释如下：

• 0：MySQL 不去主动刷盘，而是将这个过程交给 OS；

• 1：每次事务提交都会刷盘，这是最安全的方式，一般都要求这个参数设置为 1；

• N：即 N 次组提交后才刷盘。

刷盘还是 fsync()函数来做。

这里引用官方的推荐：

For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:

sync_binlog=1.

innodb_flush_log_at_trx_commit=1.

4. Undo

有一次我进行了一次测试，写了大量 update 语句，这之后我发现我的 ibdata1 文件增长了好几倍，而且悲剧的是这个空间是回缩不了的。

是什么撑大了我的共享表空间文件呢？答案是 undo。在没有独立 undo 空间的版本里，undo 空间是在共享表空间文件上的，每次执行 update 这种操作的时候，都会生成一个反操作，称为 read view，保存在 undo 里。

因此在反复大量执行 update 之后，undo 空间就会膨胀。

为什么要有 undo 呢？因为数据库的世界除了 commit 还有 rollback。rollback 可以在回滚事务，只要是没有提交的事务，总能回滚。而回滚的过程，就是一个逆操作的过程，例如我执行了：

-- 原来的score是59update Student set score = 100 where name = 'zhiquan';

其逆操作就是：

let score 59

当然回滚段里不会保存 sql 语句，这里用一个伪代码表示逆操作。更改了几次就有几次逆操作，因此频繁更改也就带来了大量的逆操作，也就导致 undo 空间膨胀。

Undo 中保存的 read  view 不仅仅能进行回滚，还能实现 MVCC 即多版本并发控制。一个会话读取的是会话开始时刻的一个 read view，这种方式叫做快照读。

上图中，事务 A 在结束之前，读取到的值一定都是 1，这是因为发生了快照读的原因，事务 A 读取到的永远是一个快照。因此图中的 V1 和 V2 值都是 1。

read view 的存在，实现了 MVCC，MVCC 则实现了可重复读。其实 read view 还有一个作用。我们知道要更新一行，首先要获取行上的 X 锁，而 X 锁与任何锁都是互斥的，因此理论上一个事务获得 X 锁之后，任何别的事务都不能读取该行的数据，因为读取也要获得一个 S 锁。但是实际上这种情况却没有发生过，我们可以像上图一样并发的读取数据，这就是 read view 的另一个意义，即提高数据库并发能力。

讲到这里，我们知道了，一个脏块在 commit 之后，首先进入了 redo 日志， 在此同时也会将变更逻辑写入 binlog 中，同时，undo 中也会新增一个 read view，commit 过程结束后，会有一个线程将脏块写到 data file 中去。

但是真的数据块只有 redo 和 data file 两个走向，这一点必须明确。