MySQL之日志篇

一、undolog 日志

undo log（回滚日志）：是 InnoDB 存储引擎层生成的日志，实现了事务中的有原子性，主要用于事务回滚和MVCC。

格式

• trx_id：事务id
• roll_pointer：版本指针
• data：不同SQL类型下存储不同的数据
  • insert：只存储主键id
  • update：只存储被修改旧列的值
  • delete：存储所有行数据

两大作用

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了ROLLBACK语句，MySQL 可以利用 undolog 中的历史数据将数据恢复到事务开始之前的状态。
• 实现 MVCC（多版本并发控制）关键字因素之一。MVCC 是通过 ReadView+ undo log 实现的。undo log 为每条记录保持多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 ReadView 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

二、redolog 日志

redo log（重做日志）：是 InnoDB 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复。

格式

以redolog block单位存储。

• header：block no、data length、first record group、checkpoint no
• body：真实数据
• trailer：表示所谓的 checkpoint 的序号， checkpoint 是我们后续内容的重点，现在先不用清楚它的意思，稍安勿躁。

什么时候刷盘？

• 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 文件满了怎么办？

重做日志文件组：ib_logfile0和ib_logfile1

• 当ib_logfile0文件被写满的时候，会切换至ib_logfile1文件
• 当ib_logfile1文件被写满的时候，会切换至ib_logfile0文件
  

我们知道 redo log 是为了防止 Buffer Pool 中脏页丢失而设计的，那么如果随着系统运行，Buffer Pool 的脏页刷新到了磁盘中，那么 redo log 对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出空间记录新的更新操作。
redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前擦除的位置，如下图：

图中的：

• write pos 和 checkpoint 的移动都是顺时针方向；
• write pos ~ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
• check point ~ write pos 之间的部分（图中蓝色）：待落盘的脏数据页记录；
  如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所有针对并发量大的系统，适当位置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复 正常运行，继续执行新的更新操作。
  所以。一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

三、binlog日志

binlog（归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制。

格式

• Statement格式：
  记录了每个执行的SQL语句。
• Row格式：
  记录了每行数据的变化。
• Mixed格式：
  Statement格式和Row格式的混合形式。

主从复制

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
• 回放 Binlog：回放 binlog，并更新从库存储引擎中的数据。

从库是不是越多越好？

不是的。
因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限主库的网络带宽。

MySQL 主从复制还有哪些模型？

• 同步复制：
  MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
• 异步复制（默认模型）：
  MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
• 半同步复制：
  MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种 半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

读写分离

基于主从复制实现。

binlog 什么时候刷盘？

四、relay log 日志

relay log（中继日志）：是MySQL 复制过程中的一种日志，用于在从库服务器中进行回放数据，将主库服务器发送过来的binlog日志数据存储到从库中。

五、两阶段提交

保证了两个日志文件的数据一致性。

两阶段提交有什么问题？

• 磁盘 I/O 次数高
• 锁竞争激烈

如何解决？

• 配置组提交参数（控制事务数量）
  • binlog_group_commit_sync_delay=N
    表示在等待 N 微妙后，直接调用 fsync，将处于文件系统中 page cache 中的 binlog 刷盘，也就是将【binlog 文件】持久化到磁盘。
  • binlog_group_commit_sync_no_delay_count =N
    表示如果队列中的事务数达到 N 个，就忽视binlog_gourp_commit_sync_delay 的设置，直接调用 fsync，将处于文件系统中 page cache 中的 binlog 刷盘。
• 配置binlog刷盘策略参数及redolog刷盘策略参数
  • sync_binlog（binlog刷盘参数）
  • innodb_flush_log_at_trx_commit（redolog刷盘参数）

六、MySQL磁盘I/O很高，有什么优化方法？

• 配置组提交参数（控制事务数量）：5.7版本及之后
  • binlog_group_commit_sync_delay = N，表示在等待 N 微妙后，直接调用 fsync，将处于文件系统中 page cache 中的 binlog 刷盘，也就是将【binlog 文件】持久化到磁盘。
  • binlog_group_commit_sync_no_delay = N，表示如果队列中的事务数达到 N 个，就忽视binlog_gourp_commit_sync_delay 的设置，直接调用 fsync，将处于文件系统中 page cache 中的 binlog 刷盘。
• 配置binlog刷盘策略参数及redolog刷盘策略参数：
  • 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）
  • 将 innodb_flush_log_at_trx_commit 设置为 2。

组提交

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

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

七、update语句执行过程