1叨逼叨
本文主要内容有：
redo log bin log WAL 技术 什么是crash-safe 两阶段提交
2一条SQL更新语句是如何执行的？
上一篇：一条SQL查询语句是如何执行的
一条查询语句的流程一般经过连接器、分析器、优化器、执行器等模块，最终到达存储引擎。
那么问题来了，一条 sql 更新语句是怎么跑的？
以前可能听到大佬或者运维的同事说，MySQL 可以恢复到半个月内任意一秒的状态。
先建一个表
mysql> create table aaaqi_demo2(id int primary key ,c int);
Query OK, 0 rows affected (0.03 sec)
如果将 id 为 2 的这条记录的 c 值更新为 4
mysql> update aaaqi_demo2 set c=4 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
之前查询语句的流程图如下，更新语句流程也是这样走的
还是先连接数据库走连接器。
现在是更新操作，和这个表相关的查询缓存都会失效，这条语句会把 aaaqi_demo2 表上的所有缓存结果清空，这也是一般不建议使用查询缓存的原因。当然像什么配置表之类还是可以的。
接下来走到分析器，分析器会通过词法和语法分析知道这是一条更新语句，优化器会决定使用这个 id 的索引（其实这里优化器不一定使用你期望的索引，听大佬说内部还有个投票选举的机制，会使用票数多的那个索引，后面我会写个demo）。
然后执行器负责具体的执行，找到这一行，然后更新。
更新和查询不一样的地方在于，它还涉及两个重要的日志模块：redo log(重做日志),bin log(归档日志)。 这个两个日志非常重要，MySQL 很多牛皮的功能都会用到。
3重要的日志模块 redo log
这个日志的话，MySQL 有两种做法：
先把记录写到 redo log 里面，等系统空闲的时候再更新到磁盘里面去。 每次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后更新。
但是第2种做法会导致整个过程IO成本、查找成本都会很高。
为了解决这个问题，MySQL设计者用磁盘和rode log进行配合，这个过程就是 MySQL 里面常说的WAL 技术，WAL 的全称是“Write-Ahead Logging”，它的关键点就是先写日志，再写磁盘。
详细点就是说当有一条记录需要更新的时候，Innodb引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，Innodb 引擎会在适当的时候把这个操作记录更新到磁盘里面，而这个更新一般会在系统比较空闲的时候去做。
另外，Innodb的 redo log是固定大小的，比如一组可以配置4 个文件，每个文件大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾又回到开头循环写，如下图：
write pos 是当前记录位置，一边写一边顺时针向后移动，写到 3 号文件末尾就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是顺时针往后移动并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间空着的部分用来记录新的操作。如果 write pos 追上 checkpoint 那么表示redo log 满了，这时候不能再执行更新操作，得先停下来擦掉一些记录，把 checkpoint推进一些。
有了 redo log，Innodb就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
4重要日志模块 binlog
MySQL 整体来看分为两块，一块是server 层，一块是引擎层。server层主要做 MySQL 功能方面的事情；引擎层主要负责存储相关事宜。redo log 是Innodb引擎特有的日志，binlog 是 server 层的日志。
最开始MySQL并没有Innodb引擎，默认是 MyISAM，但是 MyISAM 没有crash-safe的能力，binlog 只能用于归档。而Innodb是另一个公司以插件的形式引入 MySQL 的，只依靠 binlog 是没有 crash-safe 能力的，所以Innodb使用 redo-log 来实现crash-safe 能力。
redo-log、binlog 有几点不同：
redo log 是Innodb引擎特有的；binlog 是MySQL server 层实现的，所有引擎都能使用。 redo log 是物理日志，记录的是在某数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如：给 id 为 2 的这条数据c 字段更新为4。 redo log 是循环写，固定空间会用完；binlog 是追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
通过上面了解后，再看 Innodb 引擎执行之前简单的 update 语句时，MySQL 内部是怎么走的。
执行器先找引擎取id=2 这行。id 是主键，引擎直接用树搜索找到这一行。如果 id=2这行数据本来就在数据页中的话，就直接返回给执行器；否则先要从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，原来是 2，现在更新为 4，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于prepare 状态。告知执行器执行完了，可以随时提交事务。
执行器生成这个操作的 binlog，并把binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log改成 commit 状态，更新完成。
下面是执行流程图：
注意到最后三步了吧，将 redo log的写入拆成两个步骤：prepare 和 commit 这就是二阶段提交。
5二阶段提交
怎样让数据库恢复到半个月内任意一秒的状态？ 来说二阶段提交。
前面提到过 binlog会记录所有逻辑操作，并采用“追加写”的形式。如果运维说一个月内可以恢复，那么备份系统中一定会保存最近一个月的所有 binlog，同时系统还会定期做整库备份。
当需要恢复到某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据（如果是用的阿里的 MySQL，直接到那个后台管理，是可以直接恢复的），那么可以这样做：
首先找到最近的一次全量备份，如果你运气好，可能就是昨晚的一个备份，从这个备份恢复到临时库；
然后，从备份的时间点开始，将备份的binlog一次取出来，重放到中午误删表之前的那个时刻。
现在临时库就和之前误删之前一样了，然后把表数据从临时库中取出按需恢复到线上库。
6反证法 日志为什么需要二阶段提交
由于redo log 和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写 binlog再写 redo log，或者反过来写。会出现什么问题。
前面写的 update 举例，假设 id=2 的这条数据，字段 c=0，再假设执行 update语句中执行完第一个日志后，第二个日志还没写完，期间发生了crash，会出现什么情况？
先写 redo log后写 binlog。如果在 redo log 写完，binlog 还没写完时，MySQL进程异常死掉了重启。前面提到 redo log 写完后，系统即使挂了，仍然能够把数据恢复回来，所以恢复这行数据c 的值是 1。
由于binlog 没写完就crash 了，这时binlog 里面没有记录上这个语句。因此，后面备份日志的时候，存起来的 binlog里就没有这条语句。
然后你就会发现，如果用这个 binlog 恢复临时库的话，由于这个语句的 binlog 丢失，临时库就少了这次更新，恢复出来的这行数据 c 值等于 0和原库的值不同。
先写 binlog 后写 redo log。如果在 binlog 写完后 crash，由于redo log 么写完，崩溃恢复以后这个事务无效，所以这行 c=0。但 binlog里已经记录了把“c 从 2改成 4”这个日志。所以 binlog 恢复出来就会多出一个事务，恢复出的这一行c 的值是 4，与原库的 c 值不同。
上面可以看到，如果不使用二阶段提交，那么数据库的状态就有可能和它通过日志恢复的临时库的状态不一致。
7相关配置
MySQL 里面最重要的两个日志，即物理日志 redo log 和逻辑日志 binlog。
redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 时，表示每次事务的 redo log 都直接持久化到磁盘。线上库建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。
sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。线上库这个参数也建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。
不过我这个版本默认都是开启的，其它版本不清楚：
mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
mysql> SHOW VARIABLES LIKE 'sync_binlog';
版权声明：本文由 阿琦 在 2021年10月05日发表。本文采用CC BY-NC-SA 4.0许可协议，非商业转载请注明出处，不得用于商业目的。
文章题目及链接：《MySQL系列：更新语句是如何执行的》
