likes
comments
collection
share

MySQL一条更新sql的执行流程，以及日志的两阶段提交，以及崩溃恢复流程和组提交

作者站长头像
站长
· 阅读数 12

详细介绍了MySQL中一条更新sql的执行流程，以及日志的两阶段提交分布式事务，以及崩溃恢复流程，以及多事务组提交策略。

上一篇文章中我们介绍了Mysql的日志系统：重做日志（redo log）、归档日志（binlog）、回滚日志（undo log ）。实际上这些日志与MySQL的更新sql的执行流程有关，并且还涉及到了日志的分布式事务两阶段提交，下面详细介绍。

1 一条更新sql的执行流程

现有一个更新的sql语句为：

update T set A=1 where ID=1;

假设Mysql开启了binlog日志，并且使用的InnoDB引擎，那么更新操作的执行流程如下：

  1. 前面的步骤同查询sql类似，连接数据库，让查询缓存失效，经过分析器的词法和语法分析，再经过优化器的优化，比如选择走id索引，最后走到执行器。
  2. 执行器首先向执行引擎查找满足ID=1的数据，首先在引擎内部的内存缓冲池中查找，如果有，就直接使用缓存的数据，如果没有，就从磁盘中先读取满足条件的数据到缓存池中，然后返回给执行器。
  3. 执行器获取到数据之后，将数据的A字段设置为1，随后调用执行引擎的接口写入新数据。
  4. 执行引擎将这行新数据先在buffer pool中的内存数据页中进行更新，同时将这个更新操作记录到redo log buffer（日志缓存）里面，并且fsync()刷到redo log file日志文件中，生成事务ID（XID）信息，此时redo log处于prepare状态。然后告诉执行器，执行完成了，随时可以提交。（undo log 日志也是在此时写入）。
  5. 执行器收到通知后，将逻辑操作记录到binlog cache中，并且fsync()刷到binlog file日志文件中（成功后会有Commit标志或者XID标志），然后调用引擎的提交事务接口。
  6. 执行引擎把刚刚写入的redo log改成提交（commit）状态，这一步只是在redo log里标记commit，不会立即刷盘，因为日志在prepare状态时已被刷到日志文件中，同时将对应的undo log放入到一个删除列表中，未来通过purge来删除，不会立即删除。更新完成。

可以看到，更新sql和查询sql的步骤相比，出了之前的步骤都是相同的之外，还涉及到一系列日志文件的写入。但是细心的我们能够发现：

  1. 仅仅是更新了内存中的数据，并没有写入磁盘中，实际上数据并不会每次都落盘。
  2. 这里不仅仅是简单的先写xx，后写xx，而是对于redo log写了两次，redo log有两个状态。实际上redo log和binlog本身就是两个独立的系统，要想保持数据一致性，就必须使用分布式事务的解决方案来处理，这里其实就是很典型的分布式事务场景。将redo log的写分成了两步，其实就是使用了一种分布式一致性解决方案——两阶段提交协议（Two-phase Commit，2PC）。它的内部的具体原理，我们下面讲解。

2 日志两阶段提交（2PC）

可以看到redo log的写入拆成了两个步骤：prepare 和commit，这就是"两阶段提交"。另外，需要说明的是，两阶段提交的一系列操作（包括prepare 和commit等步骤）都是在sql语句中的commit执行之后进行的，两阶段成功之后，sql中的commit才算成功。

那么，为什么redo log要分两步写，中间再穿插写binlog呢？

实际上redo log和binlog其实就是很典型的分布式事务场景，因为两者本身就是两个独立的个体和逻辑，要想保持一致，就必须使用分布式事务的解决方案来处理。而将redo log分成了两步，其实就是使用了两阶段提交协议（Two-phase Commit，2PC），区别于常见的不同数据库之间的分布式事务，redo log和binlog之间的分布式事务是一种MySQL内部的分布式事务，由binlog作为事务的协调者。

redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

2.1 为什么要保证一致性

为什么要保证redo log和binlog之间的数据一致呢？实际上主要是为了两点：

  1. 在利用binlog日志在执行数据恢复的时候保证数据的完好性和正确性！
  2. 因为redo log影响主库的数据，binlog影响从库的数据，所以redo log和binlog必须保持一致才能保证主从数据一致。

2.2 为什么要两阶段提交

如果不用两阶段提交会出现什么情况呢？假设是简单的先写redo log，再写binlog或者先写binlog，再写redo log不行吗？

假设上面的update语句中，ID=1的数据的A属性初始值为0，就我们的目的是将该属性改为1：

  1. 先写redo log，后写binlog。假设在redo log写完了，但binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
  2. 先写binlog，后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，表中的数据根本没有改变，所以这一行c的真实值还是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候，恢复出来的临时库中的这一行c值就是1，与原库的值不同。
  3. 同理，对于MySQL主从同步，现在常见的做法也是用全量备份加上应用binlog来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

那么，为什么两阶段提交就能保证两个日志系统的数据一致性呢？如果采用 redo log 两阶段提交的方式，则分为三步：

  1. 预提交redo log（prepare）。
  2. 写完 binglog 。
  3. 真正提交 redo log（commit）。

我们发现，基于两阶段提交时，MySQL仍然可能在预提交到写binlog和写binlog到commit redo log之间崩溃，那么此时MySQL怎么处理呢？

首先我们需要知道redo log 和 binlog 有一个共同的数据字段，叫 XID，可以将redo log 和 binlog 关联起来。

MySQL崩溃恢复的时候，会按顺序扫描 redo log，并且做出判断：

  1. 如果碰到既有 prepare、又有 commit 的 redo log，那么binlog肯定也已经写完了，就直接提交；
  2. 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，判断binlog的完整性。如果binlog已经写完，就自动提交redo log（把这个事务重做），如果binlog还没有写完，就自动回滚该事务（应用undo log做回滚）。

在MySQL重启之后通过以上的判断，就保证了redo log和binlog的数据一致性的问题。

那么，MySQL 怎么知道 binlog 是完整的？ 因为，一个事务的 binlog 是有完整格式的，无论是statement 格式还是row格式的 binlog，写完之后的最后会有 COMMIT标记以及一个XID_EVENT标记。XID是由server层维护的，InnoDB内部使用Xid，就是为了能够在InnoDB事务和server之间做关联。

在 MySQL 5.6.2 版本以后，还引入了binlog-checksum参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。所以，MySQL 是有办法验证事务 binlog 的完整性的。

3 崩溃恢复流程

3.1 LSN的介绍

LSN称为日志的逻辑序列号(log sequence number)，在InnoDB存储引擎中，LSN占用8个字节，它表示事务写入到日志的字节总量。

redo log中就存在着LSN，且LSN的值会随着日志的写入而逐渐增大。 MySQL一条更新sql的执行流程，以及日志的两阶段提交，以及崩溃恢复流程和组提交 上面是我们在此前的文章中介绍过的redo log日志结构，图中write pos中表示 redo log 当前记录的最新数据页更改记录的LSN位置，而checkpoint 表示数据页更改记录刷到盘时对应的redo log 所处的 LSN位置。

LSN不仅存在于redo log中，还存在于数据页中，在每个数据页的头部FILE_HEADER中，有一个FIL_PAGE_LSN字段记录了当前页最终的LSN值是多少，即该数据页最后被修改的日志序列位置。

在数据库启动自检（无论是正常启动还是崩溃重启）时，会将数据页的LSN和redo log中的LSN值比较，如果数据页中的LSN值小于redo log中LSN值，则表示数据可能丢失了一部分，这时候会通过redo log执行数据恢复，否则不会。

可以通过show engine innodb status命令来查看redo log的LSN信息，MySQL 5.5版本的show结果中只有3条记录，没有pages flushed up to。

MySQL一条更新sql的执行流程，以及日志的两阶段提交，以及崩溃恢复流程和组提交

log sequence number 当前的redo log buffer（内存）中的LSN；
log flushed up to 已刷到redo log file 文件中的LSN，即write pos所在位置的LSN；
pages flushed up to 已经刷到磁盘数据页上的最大LSN
last checkpoint at 最后一次刷盘后的checkpoint所在位置的LSN。

以上四个LSN的大小递减：

  1. log flushed up to小于等于log sequence number，因此可能日志可能不是实时刷盘，大概率是1秒钟刷一次盘。
  2. pages flushed up to小于等于pages flushed up to，因为日志刷盘的时候很有可能没有进行数据刷盘（仅仅更新内存中的数据）。
  3. last checkpoint at小于等于pages flushed up to，因为数据刷盘完毕之后才会更新checkpoint所在位置的LSN。

3.2 数据恢复流程

无论是MySQL进程突然奔溃，或者是MySQL正常重启，总是会进行恢复操作。因为 redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 binlog )要快很多。

启动时InnoDB会读取磁盘数据页的LSN，然后判断如果小于redo log中的LSN，那么从checkpoint开始重做，否则不会进行重做。

还有一种可能是数据页刷盘成功，但是在checkpoint推进过程中宕机，将会出现数据页的LSN超过了checkpoint的LSN的情况，此时，超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

在根据redo log从checkpoint位置开始重做数据的时候，会检查redo log中是完整并且处于prepare状态的事务，然后根据XID（事务ID），从binlog中找到对应的事务，如果找不到，则通过undo log回滚；找到并且事务完整则重新commit redo log，完成事务的提交。

根据上面讲的最终落盘策略，注意这里的重做仅仅是将内存数据页中的脏数据更新为正确的数据，没有立即刷新磁盘脏页，重做完毕之后会按照正常的流程提供服务，在满足数据刷盘的要求时，再通过正确的内存数据进行脏页的刷盘。

重做的时候会检查每一条redo log：

  1. 如果碰到既有 prepare、又有 commit 的 redo log，那么binlog肯定也已经写完了，就直接提交；
  2. 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，并判断binlog的完整性。如果binlog已经写完（找到了此XID的binlog日志），就自动提交redo log（把这个事务重做），如果binlog还没有写完，就自动回滚该事务（应用undo log做回滚）。

MySQL数据崩溃恢复的简略流程如下：

MySQL一条更新sql的执行流程，以及日志的两阶段提交，以及崩溃恢复流程和组提交

4 组提交

组提交 (group commit) 是在保证数据一致性的前提下为了优化写日志时的刷磁盘性能能的问题，从最初只支持 InnoDB redo log 组提交，到 5.6 官方版本同时支持 redo log 和 binlog 组提交，大大提高了 MySQL 的事务处理性能。

3.1 多个事务并行的问题

两阶段提交虽然能够保证单事务的redo log和binlog日志的内容一致性，但在多事务的情况下，却不能保证两者的提交顺序一致，比如下面这个例子，假设现在有3个事务同时提交：

T1 (--prepare--binlog---------------------commit)
T2 (-----prepare-----binlog----commit)
T3 (--------prepare-------binlog------commit)

写入顺序为：

redo log prepare的顺序：T1 --》T2 --》T3
binlog的写入顺序：T1 --》 T2 --》T3
redo log commit的顺序：T2 --》 T3 --》T1

由于binlog写入的顺序和redo log提交结束的顺序不一致，导致binlog和redo log所记录的事务提交结束的顺序不一样，当T2、T3提交事务之后，若通过在线物理备份进行数据库恢复来建立复制，搭建Slave时，change master to的日志偏移量记录T3在事务位置之后。那么事务 T1 在备机恢复 MySQL 数据库时，发现 T1 未在存储引擎内提交，那么在恢复时，T1 事务就会被回滚，此时就会导致主备数据不一致。

因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。所以在早期的MySQL版本中，通过使用prepare_commit_mutex锁来保证事务提交的顺序，在一个事务获取到锁时才能进入prepare，一直到commit结束才能释放锁，下个事务才可以继续进行prepare操作。

通过加锁虽然完美地解决了顺序一致性的问题，但在并发量较大的时候，就会导致对锁的争用，性能不佳。除了锁的争用会影响到性能之外，还有一个对性能影响更大的点，就是每个事务提交都会至少进行两次fsync（写磁盘），一次是redo log落盘，另一次是binlog落盘。大家都知道，写磁盘是昂贵的操作，对于普通机械硬盘，每秒的IOPS（磁盘读写次数）大概也就是几百。

在mysql5.6开始提出了binlog组提交的改进，组提交既解决了多了个事务并发提交的问题，也解决了每次提交事务都需要两次fsync刷盘导致的事务提交性能过低的问题。

3.2 组提交

InnoDB存储引擎层本身就支持redo log组提交，但是如果使用prepare_commit_mutex锁，那么组提交就会失效。

为此，MySQL 5.6引入了binlog组提交，即BLGC（Binary Log Group Commit）。binlog组提交的基本思想是：引入队列机制保证InnoDB commit顺序与binlog落盘顺序一致，并将事务分组，组内的binlog刷盘动作交给一个事务进行，实现同时支持 redo log 和 binlog 组提交，即多个并发提交的事务共用一次fsync操作来实现持久化，大大提高了 MySQL 的事务处理性能。

BLGC具体分为两个阶段：

  1. prepare阶段（redo log 组提交prepare），持有prepare_commit_mutex，并且write/fsync redo log到磁盘，设置为prepared状态，完成后就释放prepare_commit_mutex，binlog不作任何操作。InnoDB本身就支持redo log组提交。
  2. commit阶段（binlog组提交），这里拆分成了三个小阶段：
    1. Flush Stage（写入binlog缓存）
      1. 持有Lock_log mutex [leader持有，follower等待]。
      2. 获取队列中的一组binlog(队列中的所有事务)，写入binlog缓存。
      3. 如果在这一步完成后数据库崩溃，由于协调者binlog中不保证有该组事务的记录，所以MySQL可能会在重启后回滚该组事务。
    2. Sync Stage（将binlog落盘）
      1. 释放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待]。
      2. 将一组binlog落盘（fsync动作，假设sync_binlog为1）。
      3. 如果在这一步完成后数据库崩溃，由于协调者binlog中已经有了事务记录，MySQL会在重启后通过Flush 阶段中Redo log刷盘的数据继续进行事务的提交。
    3. Commit Stage（redo log 顺序commit）
      1. 释放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待]。
      2. 遍历队列中的事务，按顺序逐一进行InnoDB commit，Commit阶段不用即时刷盘，prepare阶段中的Redo log刷盘已经足够保证数据库崩溃时的数据安全了。
      3. 释放Lock_commit mutex。

每个Stage 阶段都有自己的队列，队列中的第一个事务（第一个进入队列的事务）称为leader，其他事务称为follower，leader控制着follower的行为，完成后通知队内其他事务操作结束。每个队列各自有mutex锁保护，队列之间是顺序的。leader 同时会带领当前队列的所有 follower 到下一个 stage 去执行，只有flush完成后，才能进入到sync阶段的队列中；sync完成后，才能进入到commit阶段的队列中。但是这三个阶段的作业是可以同时并发执行的，即当一组事务在进行commit阶段时，其他新事务可以进行flush阶段，实现了真正意义上的组提交，大幅度降低磁盘的IOPS消耗。

针对组提交为什么比两阶段提交加锁性能更好：组提交虽然保留了prepare_commit_mutex锁，但是锁的粒度变小了，变成了原来两阶段提交的1/4，所以锁的争用性也会大大降低；另外，组提交是批量刷盘，相比之前的单条记录都要刷盘，能大幅度降低磁盘的IO消耗。

当头部事务执行完毕之后，后续的事务会直接返回，因为它们的log都已被持久化到磁盘中了，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。在并发更新场景下，第一个事务写完redo log buffer以后，接下来这个fsync越晚调用，组员可能越多，节约IOPS的效果就越好。相关参数：

  1. binlog_group_commit_sync_delay=N：在等待N 微秒后，进行binlog刷盘操作。
  2. binlog_group_commit_sync_no_delay_count=N：达到最大事务等待数量，开始binlog刷盘。

这两个条件是或的关系，也就是说只要有一个满足条件就会调用fsync。当binlog_group_commit_sync_delay=0时，binlog_group_commit_sync_no_delay_count参数设置无效，即没有定时的情况下，将不会等待组员。增大这两个参数数的值，将会减少binlog的写盘次数，如果MySQL的性能瓶颈在IO上，则能够提升性能。

另外，sync_binlog是控制刷盘的参数，是在上面两个参数判断之后再判断的值。

参考资料：

  1. 《 MySQL 技术内幕: InnoDB 存储引擎》
  2. 《高性能 MySQL》
  3. 《MySQL实战45讲 | 极客时间 | 丁奇》
  4. MYSQL之 GroupCommit
  5. mysql组提交

如有需要交流，或者文章有误，请直接留言。另外希望点赞、收藏、关注，我将不间断更新各种Java学习博客！