丁奇mysql实战45讲(二)笔记

[2.日志系统：一条SQL更新语句是如何执行的]
MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

先介绍两个模块：redolog和binlog

这里再挂一下mysql的逻辑架构图

一.物理日志redo log(重做日志)

在mysql中,假如每次更新数据都直接写入磁盘,那么整个过程的IO成本非常高,所以mysql改进的的方式是把记录先存到redo log中,假设redo log中的一组4个文件每个文件大小1G,如下图

write pos 是记录当前要写的起始位置，一边写一边后移，checkpoint 是记录当前要写的末尾位置，也是往后推移并且循环的，如果check point要越界了(也就是绕满一圈数据不够装了),那就会把后面旧的数据擦掉。擦除记录前要把记录更新到数据文件。

可以看出,redo log的写入机制有点像循环队列,当记录写满的时候(也就是绕了一圈了),就把旧的那部分记录擦除掉写入新的数据

所以,有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

注:redo log对应的文件名一般是ib_logfile0,这是个二进制文件不能直接打开。且redo log 是物理日志，记录的是“在某个数据页上做了什么修改”,存的是数据块的变动情况(如果直接存sql的话就太离谱了),所以在mysql宕机之后可以还原现场

小结：redo log 用于保证 crash-safe 能力,日志文件大小有限,新的记录会覆盖旧记录,innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

二.逻辑日志binlog（归档日志）

作用:binlog 会记录所有的逻辑操作

binlog和redo log的区别:

①从mysql的逻辑架构图中可知，mysql分为Server层和引擎层,redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

②redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

③redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

现在来看看下面这句更新的sql是如何执行的

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

这里假设是用旧版的mysql有查询缓存(mysql8.0及以上没有查询缓存)

①执行器找引擎取ID=2这一行的数据,查询缓存中有就直接返回给执行器,没有就去从磁盘读入内存,然后再返回

②执行器拿到引擎给的行数据,把c加1后得到新的一行数据,再调用引擎接口写入这行新数据

③引擎将新数据写入到内存中,并把这个更新操作记录写入redo log中,此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

④执行器生成这个操作的 binlog，并把 binlog 写入磁盘

⑤执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

下面是update的流程图,图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的：

正是因为binlog会记录所有的逻辑操作，并且是采用“追加写”的形式,所以保证了数据库可以回到过去的任一状态(只要binlog里面有记录,取决于你的备份记录)

所以如果出现数据误删,可以先找到最近一次的全量备份记录,然后将备份的binlog依次取出来,重放到误删数据前的时刻即可

这里为什么redo log要分prepare和commit两阶段提交呢？原因是只要其中一个log数据正常,另一个不正常,都会导致数据不一致,影响功能

①如果redo log写完,binlog还没写完mysql就异常重启了,及时redolog有恢复现场的能力,但是binlog中没记录,下次用binlog的时候恢复的数据会与原数据不同

②如果binlog 写完了,redo log还没写完然后mysql异常重启了,崩溃以后事务无效,会导致后面用binlog恢复的时候多了一个事务出来,数据还是和实际不一致

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

小结：binlog记录逻辑操作且记录是以追加的形式写入日志文件的,sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

总结：本质上是因为 redo log 负责事务,恢复数据库临时意外现场,binlog负责归档恢复,让数据库回溯到过去的某个时间点