MySQL随笔02_一条SQL更新语句是如何执行的

一、回顾一条查询语句的执行过程

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等阶段后，最后到达存储引擎。

二、更新语句的执行过程

更新SQL语句的执行过程与查询的基本一致。通过分析器的词法和语法解析判断出是一条更新语句，优化器决定使用的索引等，执行器负责具体执行，找到数据行后进行更新。

更新语句的执行流程涉及到两个重要的日志模块——redo log(重做日志) 和 binglog(归档日志)。

redo log 是 InnoDB 的日志模块，binglog 是 Server 层的日志模块。

三、重要的日志模块——redo log

MySQL在做更新操作的时候，如果每一次的更新都要写进磁盘，整个过程的IO成本、数据查询成本比较高，为了解决这类问题，MySQL从设计上采用了类似粉板+账本

的思路来提升更新效率。

粉板+账本配合的过程，其实就是MySQL中的WAL技术(Write-Ahead Logging)，其关键点在于先写日志，再写磁盘。

日志文件

InnoDB的redo log 文件是固定大小的，可配置一组4个文件，每个文件大小1GB，则总共可以记录4GB的操作。

从头开始写，写到末尾就又回到起始位置循环写，如下图所示：

crash-safe

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

四、重要的日志模块——binglog

MySQL从整体上来看，主要就两块：

1. Server 层 ，主要做的是MySQL功能层面的事情；

2. 引擎层，负责存储相关的具体事宜。

为什么会有两份日志

因为最开始的时候 MySQL 中并没有 InnoDB 引擎，只有自带的 MyISAM 引擎，而 MyISAM 自身并没有 crash-safe能力，binglog 日志只能用于归档。后来开发的 InnoDB 引擎 以插件的形式引入MySQL，由于当时依赖的binglog 没有 crash-safe 能力，所以InnoDB使用了通过 redo log 来实现 crash-safe 能力的日志系统。

两种日志的区别

1. redo log 是 InnoDB 引擎特有的；binglog 是MySQL的 Server 层实现的，所有引擎共用。
2. redo log 是物理日志；binglog 是逻辑日志。
3. redo log 是循环写的，空间固定会用完；binglog 是可以追加写入的。

五、执行器和InnoDB执行update语句的内部流程

简单的update语句如下：

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

相应的执行流程图如下，其中浅色表示InnoDB内部执行的，深色表示在执行器中执行的。

如上图，执行器 写完binglog后，调用引擎的提交事务接口，引擎把上面刚刚写入的redo log改成 commit 状态，更新完成。

六、两阶段提交

上面流程图中的最后三个步骤，将 redo log 的写入拆解成两个步骤：prepare 和 commit 就是 两阶段提交。

两阶段提交的作用

为了让两份日志之间保持逻辑一致，以便在数据发送异常需要进行数据恢复时，可以准确进行数据恢复。

为什么日志需要两阶段提交

redo log 和 binglog 是两个独立的逻辑，若不使用两阶段提交，即先写 redo log 再写 binglog 或先写 binglog 再写 redo log，这两种方式存在的问题。

1. 先写 redo log 后写 binglog。假设在redo log写完，binglog还没有写完的时候，MySQL异常重启，由于redo log 具有 crash-safe 能力，系统即使崩溃，仍然能够把数据恢复回来。但是由于binglog未写完就崩溃了，此时binglog中没有记录到崩溃前的语句，因此之后备份日志时，存起来的binglog中缺失语句。最后通过binglog日志进行数据恢复到临时库时，恢复出来的数据与原库值不一致。
2. 先写 binglog 后写 redo log。假设在binglogx写完之后 crash，由于redo log还没有写，崩溃恢复后操作的事务无效，但是binglog里面已经记录了某个修改的操作日志。 所以在之后用binglog恢复时就多出来一个事务，与原库的值不一致。

由此可知，若是不使用两阶段提交，数据库的状态就有可能和用他的日志恢复出来的库的状态不一致。

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

七、小结

MySQL中最重要的两个日志——物理日志redo log 和 逻辑日志binglog。

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit参数为1用于设置每次事务的redo log都直接持久化到磁盘。建议设置为1以保证MySQL异常重启后数据不丢失。

sync_binglog参数值为1用于设置每次事务的binglog都持久化到磁盘，建议设置为1以保证MySQL异常重启后binglog不丢失。

MySQL日志系统中的两阶段提交。两阶段提交是跨系统维持数据逻辑一致性时常用的方案。

八、思考题

定期全量备份的周期，在什么场景下，一天一备比一周一备更有优势？它影响数据库系统的哪个指标？

参考解答：

一天一备的好处是“最长恢复时间”更短，在此模式中，最坏的情况下需要应用一天的binglog。而一周一备最坏的情况下要应用一周的binglog。

系统对应的指标——RTO 恢复目标时间。

更频繁的全量备份需要消耗更多的存储空间，所以RTO是成本换来的，具体根据业务来评估选择备份模式。

本随笔源课程: https://time.geekbang.org/column/article/68633
本文由博客一文多发平台 OpenWrite 发布！