MySQL日志

在MySQL架构这篇文章中，提到了执行一条SQL查询语句的运行过程。当执行一条SQL更新语句时，其运行过程如下：

  • 客户端首先通过连接器建立连接，连接器自动判断用户身份
  • 解析器通过词法分析识别出关键字update、表名等，构建出语法树，然后做语法分析，判断该语句是否符合MySQL语法
  • 预处理器判断表和字段是否存在
  • 优化器确定执行计划，决定要走哪些索引
  • 执行器负责具体执行，找到目标行，然后做更新

与执行查询语句不同的是，更新语句的流程会涉及到回滚日志undo log、重做日志redo log、归档日志binlog。

  • undo log：InnoDB存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC
  • redo log：InnoDB存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复
  • binlog：Server层生成的日志，主要用于数据备份和主从复制

undo log详解

在执行一条增删改语句时，虽然没有输入begin开启事务和commit提交事务，但MySQL会隐式地开启事务来执行这条语句，执行完就自动提交事务，这样就可以保证用户能够及时地看到相应的结果。

因此，执行update语句时，也是会隐式地使用事务的。那么，事务在提交之前如果发生了崩溃，想要回滚到事务之前的数据则需要依靠undo log来实现，其保证了事务执行的原子性。

在事务没提交之前，MySQL会先将更新前的数据记录到undo log日志文件里，当事务回滚时，可以利用undo log来进行回滚。

undolog

每当InnoDB对一条记录进行增删改时，都要把回滚时需要的信息记录到undo log中，例如：

  • 新增一条记录时，记下这条记录的主键值，回滚时只需要将该主键值对应的记录删除即可
  • 删除一条记录时，记下这条记录的所有内容，回滚时将这些内容组成的记录插入到表中
  • 更新一条记录时，记下被更新列的旧值，回滚时将该列更新为旧值

一条记录的每一次更新操作产生的undo log格式都有一个roll_pointer指针和一个trx_id事务ID：

  • 通过roll_pointer指针可以将这些undo log串成一个链表（版本链）
  • 通过trx_id可知该记录是被哪个事务修改的

roll_pointer

除此之外，undo log还有一个作用，即通过Read View + undo log实现MVCC。

对于「读提交」RC和「可重复读」RR两种隔离级别的事务，它们的快照读（普通select语句）是通过Read View + undo log来实现的，区别在于创建Read View的时机不同：

  • RC级别是在每个select都会生成一个新的Read View，那么，在事务期间多次读取同一条数据，前后读到的数据可能会不一致（因为期间另外一个事务修改该数据并提交了）
  • RR级别是在启动事务时生成一个Read View，整个事务期间都用这个Read View，这样就保证了在事务执行期间读到的数据都是事务开始前的值，不会产生不一致

这两个隔离级别的实现是通过「事务的Read View里的字段」和「记录中的两个隐藏列trx_id和 roll_pointer」的比对，如果不满足可见性，就会顺着undo log版本链里找到满足可见性的记录，从而控制并发事务访问同一个记录，这就叫MVCC（多版本并发控制）

综上，undo log起着两个作用：

  • 实现事务的回滚，保证事务的原子性。
  • 实现MVCC的必要组件。

PS：undo log的刷盘（持久化到硬盘）和数据页是一样的，都需要通过redo log来保证。

Buffer Pool中有undo页，对undo页的修改也都会记录到redo log中。redo log每秒刷盘，提交事务也会刷盘。

Buffer Pool详解

InnoDB存储引擎中有Buffer Pool，用来提高数据库的读写性能。

  • 当读取数据时，若数据存在于Buffer Pool中，客户端就会直接读取Buffer Pool中的数据，否则再去磁盘中读取
  • 当修改数据时，若数据存在于Buffer Pool中，则直接修改Buffer Pool中该数据所在的页，然后将其设置为脏页（内存数据与磁盘数据不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，而是后续由后台线程完成写入

redo log详解

尽管Buffer Pool可以提高数据库的读写效率，但其是基于内存的，可靠性无法保证，断电重启时还未落盘的脏页数据就会丢失。

为了防止这种情况，当有一条记录需要更新时，InnoDB会先更新内存，同时标记为脏页，然后将本次对该页的修改以redo log的形式保存下来，此时算作更新完成。

后续，InnoDB会在恰当时机，用后台线程将脏页刷盘，即预写日志（Write-Ahead Logging，WAL）。

WAL是指MySQL的写操作并不是立即写入磁盘，而是先写日志，再在合适的时间刷盘。整个过程如下：

redolog

那么，在事务提交时，只要先将redo log持久化到磁盘即可，这样即使系统崩溃，脏页尚未持久化，但redo log已经持久化，因此重启MySQL时根据redo log即可将数据恢复到最新状态。

redo log和undo log有什么区别？

两者都是InnoDB层的日志，区别在于：

  • redo log记录此次事务完成后的数据状态，记录的是更新后的值
  • undo log记录此次事务开始前的数据状态，记录的是更新前的值

事务提交前发生崩溃，重启后通过undo log回滚；事务提交后发生崩溃，重启后通过redo log恢复。

redo&undo

redo log要刷盘，数据本身也要刷盘，为何多此一举？

redo log的写入是追加方式，所以磁盘是顺序写，而写入数据要先找到写入位置，所以是随机写。

顺序写比随机写的效率高很多，因此写入redo log的开销很小。

redo log何时写入磁盘？

redo log不会直接写入磁盘（防止产生大量I/O），而是写入到自己的缓存redo log buffer，后续再持久化到磁盘。

redobuffer

那么，何时刷盘呢？

主要有下面几个时机：

  • MySQL正常关闭时
  • 当redo log buffer中记录的写入量大于redo log buffer内存空间的一半时，会触发落盘
  • InnoDB的后台线程每隔1秒，将redo log buffer持久化到磁盘
  • 每次事务提交时都将缓存在redo log buffer里的redo log直接持久化到磁盘（由参数innodb_flush_log_at_trx_commit控制）

redo log文件写满了怎么办？

默认情况下，InnoDB有一个重做日志文件组redo log Group，该组由两个redo log文件组成，分别是ib_logfile0和ib_logfile1。

两个redo log file的大小是固定且一致的，以循环写的方式来工作，相当于构成了一个环形日志。

redo log是为了防止Buffer Pool中的脏页丢失而设计的，那么如果随着系统运行，Buffer Pool的脏页刷新到了磁盘中，那么redo log对应的记录也就没用了，这时候就擦除这些旧记录，以腾出空间记录新的更新操作。

因此，InnoDB用write pos表示redo log当前记录写到的位置，用checkpoint表示当前要擦除的位置。

checkpoint

如果write pos追上了checkpoint，就意味着redo log文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

binlog详解

MySQL在完成一条更新操作后，Server层会生成一条binlog，等事务提交的时候，会将该事务执行过程中产生的所有binlog统一写入binlog文件。

binlog文件包含所有数据库表结构变更和表数据修改的记录，但不会记录查询类的操作，例如select和show操作。

redo log和binlog有什么区别？

  1. 适用对象不同
  • binlog是Server层实现的日志，所有存储引擎都可以用
  • redo log是InnoDB实现的日志
  1. 文件格式不同
  • binlog有3种格式类型，分别是STATEMENT（默认）、ROW、MIXED
  • redo log是物理日志，格式一般为「对XXX表空间中的YYY数据页ZZZ偏移量的地方做了AAA更新」
  1. 写入方式不同
  • binlog是追加写，写满了就创建一个新文件继续写，不会覆盖以前的日志
  • redo log是循环写，文件大小固定，写满就从头覆盖继续写
  1. 用途不同
  • binlog用于备份恢复、主从复制
  • redo log用于掉电等故障恢复

删库了如何恢复？

用binlog而不是redo log，因为binlog保存的是全量日志。

如何通过binlog实现主从复制？

binlog会把MySQL上的所有变化以二进制形式记录并保存在磁盘上。复制的过程就是将binlog中的数据从主库传输到从库上。

这个过程一般是异步完成的。

binlog

整个主从复制过程可归纳为三个阶段：

  • 写入binlog：主库写binlog日志，提交事务，并更新本地存储数据
  • 同步binlog：主库将binlog复制到所有从库，各从库将binlog写到中继日志relay log
  • 回放binlog：从库回放binlog，并更新自身存储引擎中的数据

binlog何时刷盘？

事务执行过程中，先把日志写到binlog cache（Server层的cache）中，事务提交的时候，再把binlog cache写入到binlog文件中。

binlogCache

两阶段提交详解

事务提交后，redo log和binlog都需要持久化到磁盘，但可能会出现半成功的状态，导致两份日志之间的逻辑不一致。

例如，假设id=1这行记录对应的name为Hisashi，执行update user SET name = 'Mitsui' WHERE id = 1;；如果在持久化环节出现了半成功状态，则可能有两种情况：

  • redo log写入，MySQL宕机，binlog未写入。MySQL重启之后，通过redo log将Buffer Pool中对应的name恢复到新值Mitsui，但由于binlog中没有记录这条更新语句，复制给从库后，从库中对应的name值仍然为Hisashi，与主库不一致
  • binlog写入，MySQL宕机，redo log未写入。MySQL重启之后，由于redo log没写，因此这个事务无效，name值仍为Hisashi，但binlog中记录了这条更新语句，因此从库中对应的name值会变为Mitsui，与主库不一致

由此可见，因为redo log影响主库，binlog影响从库，两者必须在逻辑上保持同步才能保证主从数据一致。

MySQL为了避免上述问题，使用了「两阶段提交」的方案。其本质上就是一个分布式事务一致性协议，保证多个逻辑操作要么全部成功，要么全部失败，不会出现半成功的状态。

两阶段提交方案会把单个事务的提交拆分为两个阶段：「准备Prepare」和「提交Commit」，每个阶段都由协调者Coordinator和参与者Participant共同完成。

两阶段提交的具体过程

如前文所述，为了维护redo log和binlog的逻辑一致性，MySQL使用了内部XA事务，其中binlog作为协调者，存储引擎作为参与者。

当客户端执行commit语句或者MySQL自动提交时，MySQL内部会开启一个XA事务，分两阶段来完成XA事务的提交：

2pc

由图可知，所谓的两阶段就是将redo log的写入分为了两个步骤，中间穿插写入binlog。

  • prepare阶段：将内部XA事务的ID写入到redo log，同时将redo log对应的事务状态设置为prepare，然后将redo log持久化到磁盘
  • commit阶段：将内部XA事务的ID写入到binlog，然后将binlog持久化到磁盘，接着调用InnoDB的提交事务接口，把redo log的状态设置为commit，该状态不需要持久化到磁盘，写到page cache就可以了。因为只要binlog写磁盘成功，即使redo log的状态仍是prepare也没关系，事务执行一样会被视作成功。

异常重启会怎样？

图中的时刻A和时刻B，MySQL都有可能发生崩溃。

crash

无论是时刻A还是时刻B系统崩溃，redo log都处于prepare状态。

MySQL重启后，会按顺序扫描redo log文件，碰到处于prepare状态的redo log，就携带XA事务ID去binlog查看是否存在此ID：

  • 若binlog中不存在该XID，说明redo log已经完成刷盘，但binlog尚未刷盘，则回滚事务。对应时刻A崩溃恢复的情况
  • 若binlog中存在该XID，说明redo log和binlog都已完成刷盘，则提交事务。对应时刻B崩溃恢复的情况

因此，两阶段提交是以binlog刷盘成功为事务提交成功的标识。

两阶段提交有什么问题？

性能很差，主要体现在两个方面：

  • 磁盘I/O次数多：由于redo log和binlog都需要刷盘，在某些刷盘策略下，I/O次数会很多
  • 锁竞争激烈：两阶段提交能在「单事务」环境下保证两个日志的逻辑一致，但「多事务」环境下需要锁来保证提交的原子性，从而确保两个日志的提交顺序一致

binlog组提交

MySQl引入了binlog组提交机制，当有多个事务提交时，会将多个binlog刷盘操作合并成一个，从而减少磁盘I/O次数。

引入binlog组提交机制后，prepare阶段不变，commit阶段会被拆分为三个部分：

  • flush阶段：多个事务按照进入的顺序将binlog从cache写入文件（不刷盘）
  • sync阶段：对binlog文件做fsync操作（多个事务的binlog合并一次刷盘）
  • commit阶段：各个事务按顺序做InnoDB的commit操作

三个阶段都各有自己的队列，且每个阶段都用锁来保证事务写入的顺序，第一个进入队列的事务会成为leader，leader领导所在队列的所有事务，整队的操作完成后通知队内其他事务操作已经结束。

这样的话，锁只需要对每个队列进行保护，不需要锁住提交事务的整个过程，粒度变小了，多个阶段可以并发执行。

redo log组提交

MySQL 5.7及以后的版本引入了redo log的组提交机制。

在prepare阶段不再让事务各自执行redo log刷盘操作，而是推迟到组提交的flush阶段。

这个优化是将redo log的刷盘延迟到了flush阶段之中，sync阶段之前。通过延迟写redo log的方式，为redo log做了一次组写入。

优化MySQL磁盘高I/O的方法有哪些？

  • 设置组提交的两个参数：binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数，延迟binlog刷盘的时机，从而减少binlog的刷盘次数。这个方法是基于「额外的故意等待」来实现的，因此可能会增加语句的响应时间，但即使MySQL进程中途挂了，也没有丢失数据的风险，因为binlog早被写入到page cache了，只要系统没有宕机，缓存在page cache里的binlog就会被持久化到磁盘。
  • 将sync_binlog设置为大于 1 的值（常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢 N 个事务的 binlog 日志。
  • 将innodb_flush_log_at_trx_commit设置为 2。表示每次事务提交时，都只是将缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门用来缓存文件数据的，所以写入「redo log文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据。

总结update完整执行过程

假设一条update语句为update user SET name = 'Mitsui' WHERE id = 1;，其完整的执行过程如下：

  • 执行器调用存储引擎的接口，通过主键索引在B+树中获取id=1这一行记录
    • 若该行记录所在的数据页已经在Buffer Pool中，则返回记录给执行器
    • 否则，将对应的数据页从磁盘读入到Buffer Pool中，并返回记录给执行器
  • 执行器查看更新前的记录和更新后的记录是否一致
    • 若一致，则不进行后续的更新操作
    • 否则，将更新前的记录和更新后的记录作为参数传给InnoDB，InnoDB会执行更新记录的操作
  • 事务开启，InnoDB更新记录前，首先记录对应的undo log，将旧值保存
  • InnoDB开始更新记录，先更新内存，同时标记为脏页，然后将记录写入redo log，完成更新
  • 开始记录该语句对应的binlog，并保存到binlog cache；等事务提交时将事务期间所有的binlog写入到磁盘
  • 提交事务
    • prepare阶段：将redo log对应的事务状态设置为prepare，然后将redo log刷盘
    • commit阶段：将binlog刷盘，接着调用InnoDB的提交事务接口，将redo log状态设置为commit
  • 完整流程结束