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

一、吹牛逼

🏁：MySQL 可以恢复到半个月内任意一秒的状态。PS：看到这里根据以往的记忆肯定是redo/undo日志 + binlog日志

二、回顾

用当前SQL【update T set c=c+1 where ID=2;】串联一遍上一节的内容流程

第一步：客户端先通过连接管理器建立连接，主要是为了对客户端的连接进行管理以及权限的验证（其中连接主要是通过TCP协议建立长链接）。第二步：建立连接后不会进行缓存查询，因为更新操作会导致缓存失效（8.0之前）。第三步：分析器会对每一个关键词进行解析。第四步：由于ID为主键优化器会直接使用ID列的索引。最后交给执行器执行SQL。

三、Redo log

前置提问

🏁：1.我们常说IO成本高到底高在了哪里？

IO成本就是寻址时间和上线文切换所需要的时间，最主要是用户态和内核态的上下文切换。我们知道用户态是无法直接访问磁盘等硬件上的数据的，只能通过操作系统去调内核态的接口，用内核态的线程去访问。 这里的上下文切换指的是同进程的线程上下文切换，所谓上下文就是线程运行需要的环境信息。

🏁：2.MySQL 里经常说到的 WAL 技术是什么？WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

💡：需要注意的是：“先写日志” 也是先写磁盘，只是写日志是顺序写盘，速度很快。怎么解释呢？写redo log的方式是顺序IO。更新操作是随机IO，随机IO相比顺序IO有一个寻址的过程，所以顺序写盘更快。

🏁：3.当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

⚠️：这里redo log 在哪，他也是在磁盘上。此外，这里的“并更新内存”也有学问！更新内存的意思是先要把这一行记录从磁盘加载到内存中(buffer_pool)，当需要更新的数据页在内存中时，就会直接更新内存中的数据页；如果要更新的数据没有在内存中的时候，在可以使用 change buffer 的情况下，InnoDB会先将更新操作缓存在change buffer中，等到查询操作时触发merge操作，返回更改后的记录值。–这里做好备注，不是特别明白buffer pool和change buffer的区别

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

此外，redo log的为双指针，write pos 是当前记录的位置，checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

🏁：4.1.redo log

1⃣️redo log什么时候会进行更新到数据文件中（刷盘）？系统认为是空闲和redo log满了都会去刷新磁盘。

2⃣️write pos和checkpoint之间空着的部分代表剩余空间，可以记录新的操作，如果write pos 追上 checkpoint，表示空间满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

⚠️：停下来擦掉记录，不会造成请求阻塞吗？ 处理擦掉部分的时候还没有写入，如果这时crash难道不会出问题吗？ 如果光写log没写到库里，那不会导致数据实时性有问题吗？ 为什么要写到写不下才擦掉，队列处理错峰填谷不是更合理吗？–这里做好备注，这些问题也不太懂，随后回头看一下

3⃣️redo log 是 InnoDB引擎所特有的，如果数据库发生异常重启，之前提交的记录都不会丢失。 InnoDB正因为有了 redo log(重做日志)，才有了 crash-safe 的能力（即使mysql服务宕机，也不会丢失数据的能力）。

四、Binlog

🏁：1.前置记忆内容，MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。

正如上面所讲，redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

👋：2.为什么会有两份日志呢？只用binlog不能实现crash-save的功能么？

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

疑问？为了支持crash-safe，需要redolog，而且为了保证逻辑一致，事务提交需要两个阶段：prepare阶段和commit阶段。写redolog并落入磁盘(prepare状态)–>写binlog–>commit。commit的时候是不会落盘的。–这里做好备注，redolog和事务提交的关系是什么？

🏁：大招来了！！

3.redo log和binlog日志的区别是什么？这两种日志有以下三点不同。1⃣️redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可用。2⃣️redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。3⃣️redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

⚠️：有大神对redo log的写入做了详细的解释分析，以下针对各个说明进行拆解。PS：自己实在太优秀了，竟然懂得拆解

🏁：3.1. 1⃣️系统吞吐影响因素有什么？【REDO的写盘时间会直接影响系统吞吐】。

2⃣️REDO的重放为什么必须可重入？【系统崩溃总是发生在始料未及的时候，当重启重放REDO时，系统并不知道哪些REDO对应的Page已经落盘，因此REDO的重放必须可重入，即REDO操作要保证幂等】

3⃣️为了便于通过并发重放的方式加快重启恢复速度，REDO应该是基于Page的，即一个REDO只涉及一个Page的修改。

熟悉的读者会发现，数据量小是Logical Logging的优点，而幂等以及基于Page正是Physical Logging的优点，因此InnoDB采取了一种称为Physiological Logging的方式，来兼得二者的优势。所谓Physiological Logging，就是以Page为单位，但在Page内以逻辑的方式记录。举个例子，MLOG_REC_UPDATE_IN_PLACE类型的REDO中记录了对Page中一个Record的修改，方法如下： （Page ID，Record Offset，(Filed 1, Value 1) … (Filed i, Value i) … ) 其中，PageID指定要操作的Page页，Record Offset记录了Record在Page内的偏移位置，后面的Field数组，记录了需要修改的Field以及修改后的Value。

⚠️：执行一条update语句在执行器（binlog）和存储引擎（redo log）中发生了什么事？举例：以更新ID为2，并把N+1的语句为例

回忆写：

1.执行器先从引擎中找到ID=2的这一行所在的数据页（PS：这里是树查找方式），如果这一行记录所在的数据页在存储引擎的内存中则直接返回给执行器。如果不在内存中从磁盘读取到内存返回给执行器。

2.执行器拿到数据后对N进行N+1操作，在调用引擎接口写入这行新数据

3.引擎将这一行数据更新到内存中，同时将这个更新操作记录到redo log中，此时redo log为prepare状态，然后告知执行器可以提交事务了 --这里不是先写redo log日志再更新内容？

【这里更新到内存中是指更新内存中这条数据所在的数据页中的数据，因为在前面的步骤就已经将这条数据所在的数据页读入到了内存中，所以这里说的更新内存指的就是更新内存中对应数据页的这条数据】

4.执行器收到通知后开始写入binlog日志（写入binlog后会进行刷盘操作）

5.执行器调用引擎的通知接口告知redo log可以进行事务commit状态变更，更新完成

五、两段式提交

🏁：5.1.为什么要有两段式提交？为了让redo log等待binlog写完后有prepare状态改为commit状态

5.2.开头提到的怎样让数据库恢复到半个月内任意一秒的状态？binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。所以一定是DBA保存的有半个月内的日志记录

⚠️：redolog和binlog具有关联行，在恢复数据时，redolog用于恢复主机故障时的未更新的物理数据，binlog用于备份操作。每个阶段的log操作都是记录在磁盘的，在恢复数据时，redolog 状态为commit则说明binlog也成功，直接恢复数据；如果redolog是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。

六、其他

🏁：6.1.Binlog有三种模式，statement 格式的话是记sql语句； row格式会记录行的内容，记两条，更新前和更新后都有【默认】；Mixed（混合模式）待补充

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

2⃣️sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。