02 | 日志系统：一条SQL更新语句是如何执行的？

更新语句执行流程

比如有sql：mysql> update T set c=c+1 where ID=2;

image.png

1. 连接器，先连接数据库
2. 缓存：清空缓存，清空表T上的所有查询缓存。
3. 分析器：词法与语法解析，得知是一条更新语句
4. 优化器：进行优化，决定使用ID这个索引
5. 执行器：找到数据，进行更新
   至此，更新流程结束，但更新流程涉及到两个非常重要的日志模块，redo log（重做日志）和binlog（归档日志）

详解redo log

• 两个能力：高性能（WAL）与数据一致性（crash-safe）

WAL

是什么

WAL（Write-Ahead Logging）技术，先写日志，再写磁盘（是不是联想到了LSM）

为什么需要WAL

• 在Mysql的更新里有个问题，如果每个更新语句都需要写进磁盘，然后磁盘也要找到对应的记录，然后再更新，整个过程IO成本、查找成本都很高。
• 为了提高更新的效率，Mysql引入了WAL（Write-Ahead Logging）技术，先写日志，再写磁盘（是不是联想到了LSM）。

为什么写redo log会比直接写磁盘数据表快呢

redo log和bin log怎么对应

1. Redolog是顺序写
2. 可以组提交
3. 还有别的一些优化，收益最大是上面两个

怎么做的

• 具体来说，就是当有一个记录需要更新时，InnoDB引擎就会先把记录写入到redo log里并更新内存，这个时候更新就结束了。
• InnoDB引擎会在适当的时候将这个操作记录更新到磁盘里，这个动作往往在系统比较空闲的时候做。（是不是很像LSM的flush）

• 但如果在系统flush到磁盘之前，redo log就被写满了，那么就需要先进行flush操作。（redo log的大小是固定的，比如可以配置为一组四个文件，每个文件1G，那么就可以写入4G的操作。）

  
  image.png
• 如上图所示，write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
• write pos 和 checkpoint 之间的是redo log文件上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示文件满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

crash-safe

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失

binlog

MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。而redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为binlog（归档日志）。

为什么要有binlog

• 能力：数据恢复
  binlog可以追加写入，写满一个文件时会继续写入到一个新的文件，而不会覆盖原有问题，故经常被用来：数据库误删恢复、全量备份、数据库扩容、主从数据同步等

binlog与redo log

为什么会有两个日志？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

binlog和redo log的区别

1. binlog是Mysql的server层实现的，而redo log是InnoDB引擎特有的。
2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”（记录这个页 “做了什么改动”而不是记录数据页“更新后的状态”），binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加一”
3. redo log是循环写的，空间固定的，会被用完。binlog是可以追加写入的。binlog文件写到一定大小后就切换下一个，而不会覆盖之前的数据。

两阶段提交

更新流程

• 先看下执行器和InnoDB引擎在执行此update语句时的内部流程

1. 查询：执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 计算：执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 更新：引擎将这行新数据更新到内存中，同时将这个更新操作记录到** redo log** 里面，此时redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 提交事务：执行器生成这个操作的 binlog，并把** binlog** 写入磁盘，随后执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

下图为此 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

image.png

其中，最后两步将redo log的写入分为了两步，prepare和commit，这就是“两阶段提交”
注意：

• redo log和bin log是通过事务ID对应的
• 这个过程中日志文件操作了三次（redolog两次 binlog 1次）不过在并发更新的时候会合并写

为什么需要两阶段提交？

• 为了保证redo log与binlog的一致性。

没有两阶段提交的场景

假如没有两阶段提交，在写入第一个日志后系统crash掉了，会出现什么情况呢？
还是针对这个语句：update T set c=c+1 where ID=2; 假设ID=2的行的c字段此时为0。

• 场景一：先写redo log再写binlog：
  1. 写redo log
  2. crash
  3. 系统重启，使用redo log恢复数据，得到结果一（c=1）
  4. bin log缺失此更新语句
  5. 数据库被删
  6. 使用bin log恢复数据，得到结果二（c=0）
     结果：结果一与结果二的数据不一致。
• 场景二：先写binlog，后写redo log：
  1. 写binlog
  2. crash
  3. 系统重启，使用redo log恢复数据，由于redo log没有此语句，得 到结果一（c=0）
  4. 数据库被删
  5. 使用binlog恢复数据，得到结果二（c=1）
     结果：结果一与结果二数据不一致。

需要注意的是，binlog不仅仅会用来数据恢复，在很多场景中都会用到（数据库扩容、主从数据同步、数据全量备份），所以binlog与redo log的数据一致性是要保证的。

有两阶段提交的场景

假如有两阶段提交，在写入过程中crash了，会出现什么情况呢？
还是针对这个语句：update T set c=c+1 where ID=2; 假设ID=2的行的c字段此时为0。

• 场景一：写完redo log写为prepare后crash

  1. 写入redo log（prepare）
  2. crash
  3. 重启服务：使用redo log恢复数据，由于没有commit（没有binlog），回滚。得到结果一（c=0）
  4. 数据库被删
  5. 使用binlog恢复数据，由于没有此语句的binlog，得到结果（c=0）
     结论：结果一与结果二一致

• 场景二：写完binlog后crash

  1. 写入redo log （prepare）
  2. commit
  3. 写binlog
  4. crash
  5. 重启服务：使用redo log恢复数据，由于已经commit（有binlog），更改redo log为commit状态，得到结果一（c=1）
  6. 数据库被删
  7. 使用binlog恢复数据，由于有此语句的binlog，得到结果二（c=1）
     结论：结果一与结果二一致

• 场景三：commit redo log后crash

  1. 写入redo log （prepare状态）
  2. commit
  3. 写binlog
  4. 改redo log为commit状态
  5. crash
  6. 重启服务：使用redo log恢复数据，由于已经commit（有binlog且redo log的记录为commit状态，这里需要确认是否需要两个条件都满足？），得到结果一（c=1）
  7. 数据库被删
  8. 使用binlog恢复数据，由于有此语句的binlog，得到结果二（c=1）
     结论：结果一与结果二一致

小结

• innodb_flush_log_at_trx_commit: redo log持久化
  redo log 用于保证 crash-safe 能力innodb_flush_log_at_trx_commit 这个参数设置成1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。
• sync_binlog: binlog 持久化
  sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数也建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

问题

问题1：缓存问题

image.png

上一章中讲到，查询时时会有缓存的，这个缓存在MYSQL 8.0即将被废弃。那这里的查询缓存和上图【磁盘中读入内存】的内存是一个东西吗？

• 猜测：应该不是同一个东西，上一章说的查询缓存，应该是MYSQL的server的东西，上图的内存是引擎层的东西。
• 答：

问题2： redo log写满但其中有未提交的数据

如果在非常极端的情况下，redo log被写满，而redo log涉及的事务均未提交，此时又有新的事务进来时，就要擦除redo log，这就意味着被修改的的脏页此时要被迫被flush到磁盘了，因为用来保证事务持久性的redo log就要消失了。但如若真的执行了这样的操作，数据就在被commit之前被持久化到磁盘中了。当真的遇到这样的恶劣情况时，mysql会如何处理呢，会直接报错吗？还是有什么应对的方法和策略呢？

• 答：这些数据在内存中是无效其他事务读不到的（读到了也放弃），同样的，即使写进磁盘，也没关系，再次读到内存以后，还是原来的逻辑