MySQL日志系统：一条SQL“更新语句”是如何执行的（redo log、binlog）

1. MySQL中一条“更新语句”的执行流程：

1.1 导语：MySQL中的“update更新”操作需要关注哪些问题：

在MySQL中执行更新操作时，必然会涉及到读、写 存储在磁盘中的行数据。如果每一次的更新操作都要写进磁盘，由于磁盘的IO操作效率是很低的，这会导致更新数据的成本很高。因此，出于写数据库性能的考虑，MySQL对更新语句采用了 “先写日志，再写磁盘” 的做法（即所谓的“WAL=Write-Ahead Logging”技术）。

具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到 redo log 里面，然后将更新后的行数据本身只更新到内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候。

这种分段写入的做法又引入了一个新问题：当存储在日志中的更新数据在还未来得及同步到磁盘时，如果此时系统发生异常宕机，如何防止数据丢失。最开始的MySQL中是不支持“crash-safe”的，MySQL Server层只支持 binlog 功能（追加写），后来的InnoDB存储引擎实现了另一套日志系统redo log，用来支持crash-safe功能，相当于先将更新数据的一小部分关键信息写入到磁盘的redo log中，而更新的行数据本身先缓存在内存中，这样减少了每次写磁盘的数据量，又保证了数据在系统宕机时不丢失。

单从数据更新和crash-safe的角度看，仅有redo log就可以实现了，还要同步写binlog 完全是为了支持MySQL Server层历史遗留的binlog；binlog更适用于做“定期归档”，而在集群模式中，binlog还有另外一个重要的作用就是支持主从同步。

1.2 举例说明一条update更新语句的执行流程图：

假设有如下的update更新操作：

mysql> create table T (id INT PIRMARY KEY, c INT);
//创建表T，包含列：ID, c

mysql> update T set c = c+1 where id = 2;
//执行update更新操作，将id=2的行数据中的c字段加1

这条update更新语句在MySQL中的执行过程可简化如下：

执行过程：

① 执行器先调用存储引擎的接口 “取ID=2这一行”，如果ID=2这一行所在的数据页在内存中，则存储引擎直接将其返回给执行器；否则，需要存储引擎先去磁盘中读入内存，然后再返回；
② 执行器拿到存储引擎返回的这行数据后，对其进行更新操作，将c的值加1，得到新的数据，再调用存储引擎的接口，写入这行数据；
③ 存储引擎收到执行器写入的这行数据的新结果后，先将这条更新记录保存在 内存 中，并将这条更新记录写入 redo log，更新redo log的状态为 prepare。随后向执行器返回结果；
④ 执行器知道存储引擎已经将这条更新记录成功写入到redo log后，开始将这条更新记录写入到 binlog；
⑤ redo log调用 fsync 写入磁盘；
⑥ binlog 调用 fsync 写入磁盘；
⑦ 在执行器写入binlog成功后，存储引擎将redo log的状态更新为 commit，更新完成。

1.3 两阶段提交：

“两阶段提交”指的就是 redo log 的 “prepare + commit” 两阶段状态，目的是防止在binlog与redo log之间意外宕机，导致两份log中的内容不一致，保证 binlog与redo log全部写完成后再提交。

1.4 为什么要有两种log？

从历史说起，MySQL最开始使用的存储引擎是MyISAM，binlog是server层的，所有存储引擎都可以使用，MyISAM不支持crash-safe，binlog只能用于归档。后期InnoDB作为第三方插件引入，自行实现了redo log用以支持crash-safe。

1.5 为什么redo log具有crash-safe的能力，而binlog没有？

这是由二者的特点所决定的。redo log是一个固定大小的、“循环写”的日志文件，记录的是物理日志；binlog是一个无限大小、“追加写”的日志文件，记录的是逻辑日志。

redo log和binlog一个很大的区别在于，一个是循环写，一个是追加写。也就是说redo log只会记录未刷盘的日志，已经刷入磁盘的数据都会从redo log这个有限大小的日志文件里删除；binlog是追加日志，保存的是全量的日志。

当数据crash后，想要恢复 未刷盘但已经写入 redo log和binlog的数据到内存时，binlog是无法恢复的。虽然binlog拥有全量的日志，但没有一个标志让InnoDB判断哪些数据已经刷盘，哪些数据还没有。

例如，假设binlog记录的两条日志：

update T set c = c + 1 where ID = 2;
update T set c = c + 1 where ID = 2;

如果在第一条更新语句已刷盘，而第二条更新语句未刷盘时，发生数据库crash，在重启后，数据库并不能判断这两条语句哪条已经写入盘了，哪条没有写入磁盘，也就无法根据binlog去恢复数据库状态。

但redo log不一样，只要写入磁盘的数据，都会从redo log中抹掉，数据库重启后，直接把redo log中的数据都恢复到内存就可以了。这就是为什么redo log具有crash-safe的能力，而binlog不具备的原因。

2. redo log 与 binlog：

2.1 binlog 与 redo log有三个不同点：

① redo log是InnoDB存储引擎所特有的，binlog是MySQL的Server层实现的，所有存储引擎都可以使用；
② redo log是物理日志，记录的是“在某个数据页上做了什么修改”（修改结果是什么），binlog是逻辑日志，记录的是这个语句的原始逻辑（操作语句是什么）；
③ redo log是循环写，空间满时会发生写覆盖；binlog是追加写，不会覆盖。

注：
虽然redo log 是物理日志，但它并不是直接存储修改后的行数据本身（如果是这样实现的话那就跟直接将数据写入磁盘没有区别了，也就起不到提高写数据效率的目的了），实际上redo log 中存储的内容格式是这样的： “xx表空间中yy数据页zz偏移量做了ww更新”。 相当于只记录更新语句的简要信息，减少了写磁盘的数据量。

2.2 关于redo log和binlog的参数配置：

关于redo log的参数：

innodb_flush_log_at_trx_commit

将这个参数设置成 1 时，表示每次事务的redo log都直接持久化到磁盘（事务commit提交时自动触发redo log持久化到磁盘），建议将其设置成1，这样可以保证MySQL异常重启之后数据不丢失。

关于binlog的参数：

sync_binlog

将这个参数设置成 1 时，表示每次事务的binlog都持久化到磁盘（同样是在事务commit提交时自动持久化到磁盘），同样建议将其设置为1，这样可以保证MySQL异常重启之后binlog不丢失。

3. flush操作：

前面提到InnoDB在执行update更新操作时采用 “先写日志，再写磁盘” 的策略：更新后的行数据本身先缓存在内存中，只将缩略的关键信息写入redo log磁盘。但缓存在内存中的数据最终总是要写入磁盘的，术语叫 “flush”，这个flush操作需要何时执行？

当内存数据页 跟 磁盘数据页不一致的时候，称这个内存页为 “脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为 “干净页”。flush操作也就是“刷脏页”。

3.1 触发数据库执行 flush操作的四种情况：

（1）当InnoDB的redo log写满时：
此时系统会停止所有更新操作，将环形的redo log中的“读指针”向前推，对应的所有脏页此时都会flush到磁盘上；

（2）当系统的内存不足时：
当需要新的内存页，而内存不够用时，就需要淘汰一些内存页（一般是空出最长时间没有被访问的内存页），此时如果淘汰的是脏页，就需要先将脏页写到磁盘；

（3）当MySQL任认为系统“空闲”时：
MySQL会在运行期间“见缝插针”的找机会刷一点脏页，以避免当读写业务繁忙时过快的占满系统内存或redo log的空间；

（4）当MySQL 正常关闭 时：
此时MySQL会把内存中的所有脏页都flush到磁盘上，这样MySQL下次启动时就可以直接从磁盘上读数据，启动速度更快（相比于既从磁盘读数据，又从磁盘读redo log日志）。

3.2 不同的flush时机对MySQL性能的影响：

在上述的四种触发flush操作的情况中，第三种和第四种情况都不会对系统性能造成影响，需要关注的是前两种情况。

第一种情况redo log写满了，此时移动redo log的读指针涉及到的脏页都需要flush写入磁盘，此时所有的更新操作都会被阻塞，从监控上看到的更新数会跌为0，此时整个系统都无法接受更新，这种情况是InnoDB必须要尽量避免的。

第二种情况内存不够用了，需要先将脏页写到磁盘。InnoDB使用 “缓冲池”（buffer pool） 管理内存，缓冲池中的内存有三种状态：还未使用的、已使用且是干净页、已使用且是脏页。InnoDB的策略是尽量使用内存，所以对于一个长时间运行的库来说，未被使用的页很少，刷脏页是很频繁的操作，但必须尽量避免一次刷脏页的过程中要淘汰的脏页个数太多，这样会导致查询的响应时间明显变长。

3.3 InnoDB对刷脏页的控制策略：

MySQL需要合理的设置 innodb_io_capacity 的值（全力刷盘时磁盘能够支持的最大速度，但磁盘不可能一直全力刷盘，还需要预留部分IO能力用于服务用户请求），并且平时需要多关注脏页比例，不要让它经常接近 75%（参数 innodb_max_dirty_pages_pct 表示脏页比例上限，默认值是75%，当脏页比例过大时可能会导致一次刷盘需要淘汰的脏页个数过大，导致MySQL短暂的无法响应正常读写业务）。

从下面的这段伪代码可以看出，当脏页比例超过 innodb_max_dirty_pages_pct 阈值时（75%），F1变量返回100，此时InnoDB的flush将独占磁盘的IO能力，导致在flush刷盘阶段磁盘将无法对外提供服务给正常的用户请求。

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

总结一下InnoDB对“刷脏页”的控制策略：

正常情况下，InnoDB希望以一种“温和”的方式处理flush操作，即控制脏页比例在75%以下，利用空闲时间间歇性刷盘，且刷盘速度是计算后的全力刷盘速度的R%比例，预留一部分磁盘IO能力给其他读写操作，并不会影响到用户的正常读写请求；
一旦redo log写盘过快或InnoDB刷脏页过慢，当redo log接近饱和或系统内存接近耗尽时，R% 刷盘操作所占的磁盘IO能力比例就会提升，甚至100%独占磁盘IO，此时就会导致MySQL无法响应用户的其他读写请求，给用户的感觉就像是MySQL“抖了一下”，要尽量避免这种情况，就要合理的设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75% 。