一条更新语句是如何执行的？

之前你可能经常听DBA同事说，MySQL可以恢复到半个月内任意一秒的状态，惊叹的同时，你是不是心中也会不免会好奇，这是怎样做到的呢？

这个需要从一个表，一条更新语句说起。 假设创建一个表，有一个主键 ID和 一个整型字段 C：

mysql> create table T（ID int primary key, c int); 

现在要将 ID = 2 这一行更新

mysql> update T set C=C+1 where ID=2; 

执行语句前，需要连接器的工作，在一个表上有更新的时候，跟这个表有关的缓存会失效，所以这条语句会把表 T 上所有缓存结果都清空，这也是为啥不推荐使用查询缓存的原因。
分析器通过词法和语法分析这条更新语句，优化器决定要使用 ID 这个索引，然后执行器具体执行，找到这一条，然后更新。 跟查询流程不一样的是，更新流程还涉及两个重要的日志模块， redo log(重做日志)和 binlog(归档日志)。

重做日志 redo log 是啥？

redo Log 是 InnoDB 引擎特有的日志。
如果每一次更新操作都需要写进磁盘，然后磁盘要找到那条记录，然后再更新，整个过程 IO 成本很高，查找成本很高。MySQL 采用了什么方式提高更新效率呢？

MySQL 采用 WAL 技术，Write Ahead Loging,关键点是先写日志再写磁盘，具体执行如下：当有一条记录需要更新的时候，InnoDB 引擎会先把记录写到 redo log里，并更新内存，这个时候更新就算完事了。 当 InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，这个更新一般是在空闲的时候做。

InnoDB 的 redo log 是固定大小的，入股可以配置一组4个文件，每个文件大小是 1G,那么可以记录 4GB 的操作。当满了的时候写入磁盘。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I6OhNZAh-1589966449266)(https://imgkr.cn-bj.ufileos.com/6213857c-e847-46fa-b5f5-1538947f7de0.png)]

write pos 是当前记录的位置，一边写一遍后移，相当于类似循环链表，写到第3号文件末尾后就回到文件开头。

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要记录更新到数据文件。

write pos 和checkpoint 是 redo log 中空闲的部分，可以记录新的操作。 如果 write pos 追上了 checkpoint ,表示 redo log 满了，这个时候不能再更新，需要擦除掉一些记录，把 CheckPoint 推进。

什么是 Crash-safe？

当数据库发生异常重启时，之前提交的记录都不会丢失，这个能力叫做 crash-safe。

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

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

日志归档 binlog 是啥？

MySQL 整体看，一个是 Server 层，主要做的是 MySQL 工作层面的事情，还有一块是存储引擎层，负责存储相关的具体事宜。 Redo Log 是 InnoDB 引擎特有的日志，而 Server层也有自己的日志，就是 binlog(归档日志)。

为啥要binlog 和 redolog 两份日志？

最开始的时候 MySQL 里面没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM， 但是 MyISAM 没有 crash-safe 能力，binlog 日志只能用于归档，而 InnoDB 利用 redolog 可以实现 crash-safe 能力。

Redo Log 和 BinLog 的区别？

1. Redo Log 是 InnoDB 引擎特有的，binlog 是 MySQL 的server 层实现的。索引引擎都可以使用。
2. redo log 是物理日志，记录的是在某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的 c 字段加1”
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 写入磁盘。
5. 执行器调用引擎提交事务的接口，引擎吧刚刚写入的 redo log 改成提交 （commit）状态，更新完成。

两阶段提交

redo log 的写入拆成了两个步骤：prepare 和 commit ，这就是“两阶段提交”，为什么必须有“两阶段提交”。这个是为了让binlog 和 redolog 两者间的逻辑一致。
如果不采用两阶段提交，要么写 redo log 再写binlog 或者，先写binlog 再写 redo log,会有什么问题？ 假设 ID =2 这一行数据，字段 c 的值是 0 现在要执行 update 字段 c + 1 操作。

1. 先写redo log ，然后写 binlog ,如果 redo log 写完，binlog 还没有写完的 MySQl 异常重启，这个时候 redo 写完了数据是可恢复了，这一行的数据c已经更新成1。 但是由于 binlog 还没写完就 crash 了，这个时候 binlog 里面就没有记录这个语句，因此之后备份日志的时候，存起来 binlog里面就没这条语句。这个时候，如果要 binlog 来恢复临时库 ，这条 binlog 没写入，那么恢复出来的话，中一行的值就是 0，与原来的那个库 1 是不同的。
2. 先写 binlog后写 redo log。如果在 binlog写完之后 crash，由于 redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0, 但是binlog里面已经记录了“把c从0改成1这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致你可能会说，这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？其实不是的，不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

如何让数据库恢复半个月内任意一秒的状态？

binlog 记录所有逻辑操作，并且采用“追加写”的形式。如果 DBA 说 半个月内可恢复，说明保存最近半个月的所有 binlog ,同时系统会定期保存最近半个月的所有 binlog,同时系统会定期做整库备份。

• 首先，找到最近的一次全量备份，如果运气好，可能就是昨天晚上的一个备份，从这个库备份到临时库；
• 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放中午误删表之前的那个时刻。

这样流完成了数据库恢复。

程序员开发者社区