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

问题：在 Mysql 中可以将数据库恢复到半个月以内的任意秒的状态，是如何实现的？

2.1、SQL 更新语句的执行过程

• 与执行一条 SQL 语句的流程类似：使用连接器将客户端与服务器之间建立起连接，提及过由于当某个表进行更新，那么其该表对应在查询缓存中的记录会被清空，建立连接后进入分析器进行词法语法分析判断得出该 SQL 语句是一条更新语句，操作的表是表 T，还有其筛选的字段及其操作的字段进行分析。之后进入优化器，优化器选择合适的索引，最后进入执行器，执行器调用引擎提供的接口找到表 T 中要进行更新的记录，之后进行更新操作。

2.2、重要的两个日志

• 与查询语句的执行流程不一样的是，在 SQL 更新语句的执行流程中涉及到两个重要的日志 redo log(重做日志) 和 binlog(归档日志)

2.3、redo log（重做日志）

• 引擎中存储的相关的事宜，而特定的引擎 InnoDB （MyISAM 引擎中没有）内部有个重要的日志 redo log（重做日志）
• 如果每次更新后的记录都立即写回到磁盘当中，在磁盘中也要找对与之对应的表记录在进行更新数据的过程，整个过程的 IO 成本，磁盘查找成本都很高。在 Mysql 中为了提高更新的效率采用了 WAL（Write Ahead Logging 预写日志技术），该技术的关键在于先将更新后的记录写到日志中，等待到 CPU 空闲的时候再将日志中的记录写回到磁盘中去。但是日志的存储空间是固定有限的，例如在 Mysql 中的 InnoDB 引擎中 redo log 的可以配置四个文件，每个文件大小为 1GB，那么整个 redo log 的大小就只有 4 GB 的内存，因此当 redo log 的内存已满的时候不能继续存储更新记录了，当还需要增加更新记录则需要对 redo log 中的记录刷新到磁盘中，之后再将记录从 redo log 中擦除，这样一来记录又可以从头开始写入记录了，如图所示：
  
  • 其中在 redo log 中存在两个指针，其中 checkpoint 表示的清除记录的结束位置，write pos 代表的是写入数据的指针，而从 write pos 开始到 check point 的位置都是空闲的空间可以用于写入更新后的记录。当 write pos 与 check point 重叠时说明 redo log 的内存已满，需要推进 checkpoint 的位置对日志中的记录更新到磁盘中再到日志中进行擦除，这样的设计使得即使 Mysql 因为执行 SQL 语句累积使用管理连接对象中的内存导致内存占满，导致 Mysql 异常重启的现象也不会使得之前提交的更新数据的记录丢失。这样的能力称之为 crash-safe（安全碰撞）

2.4 binlog（归档日志）

1. Server 层存储的是 Mysql 相关功能层面上的事情，其中也有个重要的日志，binlog（归档日志）
2. 为什么在 Mysql 中有两个日志？
   a. 原因是在 Mysql 原始版本中默认使用的是 MyISAM 引擎，而该引擎中没有 redo log 日志，因此没有 crash-safe 安全碰撞的功能，而 binlog 日志只能用于归档。因此另一家公司使用 InnoDB 引擎使用插件的形式引入到 Mysql 中，在 InnoDB 中添加 redo log 重写日志，使其 Mysql 能够拥有 crash-safe 安全碰撞的功能。
3. binlog 和 redolog 的区别
   a. redo log 是 InnoDB 引擎特有的日志，而 binlog 是 Mysql 在 Server 中实现的，无论使用什么引擎都可以使用 binlog 归档日志。
   b. redo log 重写日志中记录的是物理记录，即具体在哪个数据页上做了什么修改；而 binlog 归档日志记录的是 SQL 语句的的原始逻辑，即具体执行了什么样子的 SQL 语句。
   c. redo log 重写日志是循环写的操作，其日志的内存固然会有写完的时候，而 binlog 归档日志是追加写的方式，追加写是指当 binlog 日志写到一定大小之后会切换到下一个数据页，不会覆盖以前的日志。
4. 执行器和 InnoDB 引擎执行一条 update 语句的过程（浅色代表 InnoDB 内部执行，深色代表执行器内部执行。
   

• 执行器先使用引擎提供的接口找到 ID=2 的记录。ID 是主键，引擎直接用树搜索找到这一行记录。如果 ID=2 的记录本来就在内存中，就直接返回给执行器；否则，需要先从磁盘中读入内存，然后再返回。
• 执行器拿到引擎给的数据行，把这个值加上 1，得到新的一行数据，在调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这条更新操作记录到 redo log 日志里面，此时 redo log 处于(prepare) 准备状态，然后告知执行器执行完成了，随后可以提交事务了。
• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘中
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交 (commit) 状态，更新完成。
• 需要注意的是 redo log 提交过程分为了两步，prepare 准备阶段和 commit 提交阶段。（两阶段提交）

2.5 两阶段提交

• 为什么需要 redo log 的两阶段提交：为了使得 redo log 和 binlog 两个日志之间的逻辑一致。
• 解释上面的结论首先了解如何使得数据库恢复到半个月以内任意秒的数据库状态？（数据恢复的过程）
  • 由于 binlog 是以追加写的方式记录你近期所有的逻辑操作记录，同时系统会定期的对整体的系统进行一次整库备份（至于多久进行一次系统的备份取决于系统的重要性，可能是一周或者一天），找到需要恢复记录的前最近的一次整库备份，根据这个整库备份保存在临时库中，再根据从这次整体备份库开始到要恢复的时刻期间在 binlog 中记录的所有逻辑操作全部取出并执行，之后临时库的状态就是指定需要恢复的数据库的状态，之后将临时库覆盖到线上库中去即可。
• 为什么需要 redo log 的两阶段提交？
  • 使用反证法来进行验证，由于 redo log 和 binlog 是两个独立的逻辑，是两个不同的日志，其日志的写记录也比然后先后之分，如哦出现其中一个日志完成写操作后，第二个日志在写操作期间发生了 crash 冲突（异常重启）时会出现什么样的状况：
    • 当先 redo log 后 binlog。假设在 redo log 写完的时候，Mysql 数据库出现了异常重启，由于 redo log 的 crash-safe 具有安全碰撞的功能，能够实现记录在 redo log 的更新记录不受碰撞影响，仍能恢复其更新操作。但是在 binlog 中却因为发生了碰撞导致该条 SQL 更新的逻辑没有写入日志中，如果之后需要恢复到之后的某个时间点时，恢复的临时库会根据其 binlog 中记录的逻辑操作进行恢复，这样倒是恢复的临时库与真实的数据库的状态不一致，少了其发生碰撞时 binlog 中丢失的该条记录。
    • 当先 binlog 后 redo log，如果在 binlog 中写完某个操作的逻辑记录，但是发生了数据库异常重启导致该更新操作还没来得及写入 redo log 中去，崩溃之后恢复该条操作是无效的，因此更新操作失败。但是由于 binlog 日志中保存了这条操作的逻辑记录，因此在之后数据恢复时，临时库根据之前最近一次的整库备份的基础上根据 binlog 进行恢复会导致最后恢复的临时库与实际的恢复时刻的数据库状态多了一条更新操作。
    • 小结
• 因此如果不使用 redo log 的两次提交，无论是先保存 redo log 还是 binlog 都可能会导致数据库恢复的时候与实际的原库不一致。然而虽然因为错误操作导致需要数据库恢复并不是经常的发生的事，但是使用 redo log 两阶段提交仍然是必要的，因此不仅仅是数据库恢复需要，其扩容数据库的过程也是需要的，扩容是因为需要更多的备份库来提高系统的读能力，然后扩容的方式就是采用在整库备份的基础上再根据 binlog 归档日志记录的逻辑操作来创建新的备份库的。

2.6 小结

• 介绍了 Mysql 中最重要的两个日志，即物理日志 redo log 和逻辑日志 binlog。
• redo log 用于保证 crash-safe 碰撞安全的能力，innodb_flush_log_at_trx_commit（使用 InnoDB 引擎的表在提交地时候刷新日志）参数设置为 1，表示开启状态，同时也建议开启，避免在发生 MySQL 异常重启的时候不会发生数据丢失。
• 同时建议 sync_binlog 参数（同步 binlog）设置为 1，选择开启，表示每次事务 binlog 写入到磁盘中，保证在 Mysql 异常重启的时候 binlog 不丢失。

2.7 思考

• 一天一次整库备份与一周一次的整库备份的好处在于 “ 最长恢复的时间最短 ”，根据需求恢复到某一时刻的数据库状态则只需要找到该时刻的前一天的备份，之后引用范围最长为一天的 binlog 中存储的逻辑操作即可完成恢复工作，而一周一次的整库备份则需要使用要求恢复的前一周的备份的基础上引用一周以内的 binlog 中存储的逻辑操作才可以完成恢复工作。但是前者需要消耗更多的内存资源，而后者恢复上需要消耗更多的时间，因此在选择上则基于业务的重要性来进行判断。