最后一条记录_随手记02日志系统：一条SQL更新语句是如何执行的？

• 上节系统了解了一个查询语句的执行流程&＃xff0c;并介绍了执行过程中涉及的出合理模块。
• 连接器、分析器、优化器、执行器等功能模块&＃xff0c;最后到达存储引擎。
• 一条SQL更新语句的执行流程
• 从一个表的一条sql语句说起
• create table T(ID int primary key, c int);
• 表的创建语句&＃xff0c;这个表有一个主键 ID 和一个整型字段 c
  • 将 ID &＃61; 2 这一行的值加1&＃xff0c;SQL语句:

• update T set c &＃61; c &＃43; 1 where ID &＃61; 2

• • • 连接器&＃xff1a;
      • 执行语句前先连接数据库。

• • • 查询缓存&＃xff1a;
      • 有一个表有更新的时候&＃xff0c;跟这个表有关的查询缓存会失效&＃xff0c;
      • 所以这条语句就会把表 T 上所有的缓存的结果都清空。

• • • 不建议使用查询缓存的原因。
    • 分析器&＃xff1a;
      • 通过 词法 和 语法 解析 知道这是一条更新语句。

• • • 优化器&＃xff1a;
      • 决定使用 ID 这个 索引

• • • 执行器&＃xff1a;
      • 具体执行&＃xff0c;找到这一行&＃xff0c;然后更新。

• • 与查询流程不同的是。
    • 更新流程还涉及两个重要的日志模块&＃xff0c;它们正是我们今天要讨论的主角&＃xff1a;
      • redo log&＃xff08; 重做日志 &＃xff09;
      • binlog &＃xff08; 归档日志 &＃xff09;。

• 重要的日志模块&＃xff1a;redo log&＃xff08;重做日志&＃xff09;
  • EG&＃xff1a;
    • 《孔乙己》
      • 酒店掌柜有一个粉板&＃xff0c;专门用来记录客人的赊账记录。
        • 如果赊账的人不多&＃xff0c;那么他可以把顾客的名和账目写在板上。
        • 如果赊账的人多了。粉板总会有记不下的时候&＃xff0c;

这个时候掌柜一定有一个专门记录赊账的账本。

• • • 如果有人要赊账或者还账的话。掌柜一般有两种做法&＃xff1a;
      • 直接把账本翻出来&＃xff0c;把这次赊账加上 或 扣除掉&＃xff1b;
      • 先在粉板上记下这次的账&＃xff0c;等打打烊的时候翻出来核算&＃xff1b;

• • • 生意红火时&＃xff0c;掌柜选择后者&＃xff0c;因前者操作太麻烦了。
      • 找到这人的赊账总额那条记录
      • 再进行核算
      • 最后将结果写到账本上&＃xff1b;
      • 效率低下&＃xff1b;

• • 同理&＃xff0c;MySQL也有这样的问题&＃xff1a;
    • 如果每次更新操作都需要写进磁盘&＃xff0c;然后磁盘找到对应记录&＃xff0c;再进行更新。
    • 整个过程 IO 成本、查找成本都很高。
    • 为了解决这个问题&＃xff0c;MySQL就用了类似掌柜的粉板思路来提升更新效率

• • 预写日志 - Write-AheadLogging
    • 粉板 和 账本配合的整个过程&＃xff0c;其实就是MySQL里的 WAL 技术 - 全称&＃xff1a;Write-AheadLogging。&＃xff08;预写日志&＃xff09;
      • 关键点&＃xff1a;先写日志&＃xff0c;再写磁盘&＃xff08;先写粉板&＃xff0c;不忙时写账本&＃xff09;
      • 当有一条记录需要更新的时候&＃xff0c;
        • InnoDB引擎会先把记录写到 redo log-重做日志&＃xff08;粉板&＃xff09;里面&＃xff0c;并更新内存&＃xff0c;这个时候更新就算完成了。
        • InnoDB引擎会在适当的时候&＃xff0c;将这个操作记录更新到磁盘里面&＃xff0c;而这个更新往往是再系统比较空闲的时候做&＃xff0c;就像打烊后掌柜做的事。

• • 如果今天赊账不多&＃xff0c;可以等打烊后再整理。
    • 如果某天赊账特别多&＃xff0c;粉板写满了&＃xff1a;
      • 这时&＃xff0c;掌柜放下手中的活&＃xff0c;把粉板中的一部分赊账记录更新到账本中&＃xff0c;然后把这些记录从粉板上擦掉&＃xff0c;为记新账腾出空间。

• • crash-safe
    • 同理&＃xff1a;InnoDB的 redo log 是固定大小的&＃xff1b;
      • 比如可以配置为一组 4 个文件&＃xff0c;每个文件的大小是 1GB&＃xff0c;
      • 那么这块“粉板”总共就可以记录 4GB 的操作。
      • 从头开始写&＃xff0c;写到末尾就又回到开头循环写&＃xff1b;
      • 如下面这个图所示。

• • • • • write pos 是当前记录的位置&＃xff0c;一边写一边后移&＃xff0c;

写到第 3 号文件末尾后就回到 0 号文件开头。

• • • • • checkpoint 是当前要擦除的位置&＃xff0c;也是往后推移并且循环的&＃xff0c;

擦除记录前要把记录更新到数据文件。

• • • • • write pos 和 checkpoint 之间的是“粉板”上还空着的部分&＃xff0c;可以用来记录新的操作。
        • 如果 write pos 追上 checkpoint&＃xff0c;表示“粉板”满了&＃xff0c;

这时候不能再执行新的更新&＃xff0c;得停下来先擦掉一些记录&＃xff0c;

把 checkpoint 推进一下。

• • • • 有了 redo log
        • nnoDB 就可以保证即使数据库发生异常重启&＃xff0c;
        • 之前提交的记录都不会丢失&＃xff0c;这个能力称为 crash-safe。

• • • • 理解 crash-safe 这个概念&＃xff0c;
        • 想想赊账记录的例子。

只要赊账记录记在了粉板上或写在了账本上&＃xff0c;

之后即使掌柜忘记了&＃xff0c;比如突然停业几天&＃xff0c;

恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

• • • • • 人忘记了&＃xff0c;但是之前的记录还存在&＃xff1b;

• 重要的日志模块&＃xff1a;binlog&＃xff08;归档日志&＃xff09;
  • 上节说过&＃xff0c;MySQL整体来看&＃xff0c;分为两块&＃xff1a;
    • Server层&＃xff1a;主要做MySQL功能层面的事情&＃xff1b;
    • 引擎层&＃xff1a;负责存储相关的具体事宜。
    • redo log&＃xff08;重做日志&＃xff09; 是InnoDB引擎特有的日志。
    • Server层也有自己的日志&＃xff0c;称为binlog&＃xff08;归档日志&＃xff09;。
    • 为什么有两份日志&＃xff1f;
      • 因为最开始 MySQL 里并没有 InnoDB 引擎。
      • MySQL 自带的引擎是 MyISAM&＃xff0c;
        • MyISAM 没有 crash-safe 的能力&＃xff0c;binlog 日志只能用于归档。

• • • • InnoDB 是另一个公司以插件形式引入 MySQL 的&＃xff0c;
        • 既然只依靠 binlog 是没有 crash-safe 能力的&＃xff0c;
        • 所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

• • 两种日志区别&＃xff1a;
    • redo log 是 InnoDB特有的&＃xff1b;
      • binlog 是MySQL的Server层实现的&＃xff0c;所有引擎都可以使用。

• • • redo log 是物理日志&＃xff0c;记录的是 “ 在某个数据页上做了什么修改 ”&＃xff1b;
      • binlog 是 逻辑日志。记录的是这个语句的原始逻辑&＃xff1b;
        • eg&＃xff1a;“ 给 ID &＃61; 2 这一行的 c 字段 加1 ”&＃xff1b;

• • • redo log 是循环写的&＃xff0c;空间固定会写完&＃xff1b;
      • binlog 是可以追加写入的。
        • “ 追加写 ”是指binlog 文件 写到一定大小后会切换到下一个&＃xff0c;并不会覆盖以前的日志。

• • 执行器 和 InnoDB 引擎在执行 update语句时的内部流程。
    • 执行器 先 找 引擎 取 ID &＃61; 2 这一行。
      • ID 是主键&＃xff0c;引擎直接用树搜索找到这一行。
        • 如果 ID 这一行所在的数据页本来就在内存中&＃xff0c;就直接返回给执行器&＃xff1b;
        • 否则&＃xff0c;需要先从磁盘读入内存&＃xff0c;然后在返回&＃xff1b;

• • • • 执行器 拿到 引擎 给的 行数据&＃xff0c;把这个值 加上 1&＃xff0c;得到新的一行数据&＃xff0c;再调用引擎接口写入这行数据。
      • 引擎将这行新数据更新到内存中&＃xff0c;同时将这个更新操作记录到redo log 里面&＃xff0c;此时redo log 处于 prepare状态。
        • 然后告知执行器执行完成了&＃xff0c;随时可以提交事务。

• • • • 执行器生成这个操作的 binlog&＃xff0c;并把这binlog写入磁盘。
      • 执行器调用引擎的提交事务接口&＃xff0c;引擎把刚刚写入的 redo log 改成 提交&＃xff08;commit&＃xff09;状态&＃xff0c;更新完成。
      • update语句的执行流程图&＃xff1a;浅色框表示是在 InnoDB 内部执行的&＃xff0c;深色框表示是在执行器中执行的。

• • • • 最后三步
        • 
          将 redo log 的写入拆成了两个步骤&＃xff1a;prepare 和 commit&＃xff0c;
        • 这就是"两阶段提交"。

• 两阶段提交
  • 为什么必须有两阶段提交&＃xff1f;
    • 这是为了让两份日志之间的逻辑一致。
    • 关系&＃xff1a;怎样让数据库恢复到半个月任意一秒的状态&＃xff1f;

• • 前面我们说过了&＃xff0c;binlog 会记录所有的逻辑操作&＃xff0c;并且是采用“追加写”的形式。
  • 如果你的 DBA 承诺说半个月内可以恢复&＃xff0c;那么备份系统中一定会保存最近半个月的所有 binlog&＃xff0c;同时系统会定期做整库备份。
  • 这里的“定期”取决于系统的重要性&＃xff0c;可以是一天一备&＃xff0c;也可以是一周一备。
  • 当需要恢复到指定的某一秒时&＃xff0c;
    • 比如某天下午两点发现中午十二点有一次误删表&＃xff0c;需要找回数据&＃xff0c;
    • 那你可以这么做&＃xff1a;
      • 首先&＃xff0c;找到最近的一次全量备份&＃xff0c;如果你运气好&＃xff0c;可能就是昨天晚上的一个备份&＃xff0c;从这个备份恢复到临时库&＃xff1b;
      • 然后&＃xff0c;从备份的时间点开始&＃xff0c;将备份的 binlog 依次取出来&＃xff0c;重放到中午误删表之前的那个时刻。
      • 这样你的临时库就跟误删之前的线上库一样了&＃xff0c;然后你可以把表数据从临时库取出来&＃xff0c;按需要恢复到线上库去。

• • 说完了数据恢复过程&＃xff0c;我们回来说说&＃xff0c;为什么日志需要“两阶段提交”。
    • 这里不妨用反证法来进行解释。
    • 由于 redo log 和 binlog 是两个独立的逻辑&＃xff0c;
      • 如果不用两阶段提交&＃xff0c;
      • 要么就是先写完 redo log 再写 binlog&＃xff0c;或者采用反过来的顺序。我们看看这两种方式会有什么问题。仍然用前面的 update 语句来做例子。
        • 假设当前 ID&＃61;2 的行&＃xff0c;字段 c 的值是 0&＃xff0c;再假设执行 update 语句过程中在写完第一个日志后&＃xff0c;第二个日志还没有写完期间发生了 crash&＃xff0c;会出现什么情况呢&＃xff1f;

先写 redo log 后写 binlog。假设在 redo log 写完&＃xff0c;binlog 还没有写完的时候&＃xff0c;MySQL 进程异常重启。

由于我们前面说过的&＃xff0c;redo log 写完之后&＃xff0c;系统即使崩溃&＃xff0c;仍然能够把数据恢复回来&＃xff0c;所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了&＃xff0c;这时候 binlog 里面就没有记录这个语句。因此&＃xff0c;之后备份日志的时候&＃xff0c;存起来的 binlog 里面就没有这条语句。然后你会发现&＃xff0c;如果需要用这个 binlog 来恢复临时库的话&＃xff0c;由于这个语句的 binlog 丢失&＃xff0c;这个临时库就会少了这一次更新&＃xff0c;恢复出来的这一行 c 的值就是 0&＃xff0c;与原库的值不同。

先写 binlog 后写 redo log。如果在 binlog 写完之后 crash&＃xff0c;由于 redo log 还没写&＃xff0c;崩溃恢复以后这个事务无效&＃xff0c;所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志

• • 如果不使用“两阶段提交”&＃xff0c;
    • 那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
      • 不只是误操作后需要用这个过程来恢复数据。
      • 当你需要扩容的时候&＃xff0c;也就是需要再多搭建一些备库来增加系统的读能力的时候&＃xff0c;
        • 现在常见的做法也是用全量备份加上应用 binlog 来实现的&＃xff0c;
        • 这个“不一致”就会导致你的线上出现主从数据库不一致的情况。
        • 简单说&＃xff0c;redo log 和 binlog 都可以用于表示事务的提交状态&＃xff0c;而两阶段提交就是让这两个状态保持逻辑上的一致。

• 小结
  • MySQL 里面最重要的两个日志&＃xff0c;
    • 即物理日志 redo log 和逻辑日志 binlog。
      • redo log 用于保证 crash-safe 能力。
        • innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候&＃xff0c;表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1&＃xff0c;这样可以保证 MySQL 异常重启之后数据不丢失。

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

• • 两阶段提交”
    • 两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案&＃xff0c;即使你不做数据库内核开发&＃xff0c;日常开发中也有可能会用到。

• • 思考题&＃xff1f;
    • 定期全量备份的周期“取决于系统重要性&＃xff0c;有的是一天一备&＃xff0c;有的是一周一备”。那么在什么场景下&＃xff0c;一天一备会比一周一备更有优势呢&＃xff1f;或者说&＃xff0c;它影响了这个数据库系统的哪个指标&＃xff1f;

• • Redo log 和 Binlog
    • Redo log不是记录数据页“更新之后的状态”&＃xff0c;而是记录这个页 “做了什么改动”。
    • Binlog有两种模式&＃xff0c;statement 格式的话是记sql语句&＃xff0c; row格式会记录行的内容&＃xff0c;记两条&＃xff0c;更新前和更新后都有。

• • 他人总结&＃xff1a;
    • redo是物理的&＃xff0c;binlog是逻辑的&＃xff1b;
    • 现在由于redo是属于InnoDB引擎&＃xff0c;所以必须要有binlog&＃xff0c;因为你可以使用别的引擎保证数据库的一致性&＃xff0c;必须要保证2份日志一致&＃xff0c;使用的2阶段式提交&＃xff1b;其实感觉像事务&＃xff0c;不是成功就是失败&＃xff0c;不能让中间环节出现&＃xff0c;也就是一个成功&＃xff0c;一个失败&＃xff1b;
    • 如果有一天mysql只有InnoDB引擎了&＃xff0c;有redo来实现复制&＃xff0c;那么感觉oracle的DG就诞生了&＃xff0c;物理的速度也将远超逻辑的&＃xff0c;毕竟只记录了改动向量&＃xff1b;
    • binlog几大模式&＃xff0c;一般采用row&＃xff0c;因为遇到时间&＃xff0c;从库可能会出现不一致的情况&＃xff0c;但是row更新前后都有&＃xff0c;会导致日志变大&＃xff1b;
    • 最后2个参数&＃xff0c;保证事务成功&＃xff0c;日志必须落盘&＃xff0c;这样&＃xff0c;数据库crash后&＃xff0c;就不会丢失某个事务的数据了&＃xff1b;
    • 两阶段提交&＃xff1a;

• Bin log 用于记录了完整的逻辑记录&＃xff0c;所有的逻辑记录在 bin log 里都能找到&＃xff0c;所以在备份恢复时&＃xff0c;是以 bin log 为基础&＃xff0c;通过其记录的完整逻辑操作&＃xff0c;备份出一个和原库完整的数据。
• 在两阶段提交时&＃xff0c;若 redo log 写入成功&＃xff0c;bin log 写入失败&＃xff0c;则后续通过 bin log 恢复时&＃xff0c;恢复的数据将会缺失一部分。(如 redo log 执行了 update t set status &＃61; 1&＃xff0c;此时原库的数据 status 已更新为 1&＃xff0c;而 bin log 写入失败&＃xff0c;没有记录这一操作&＃xff0c;后续备份恢复时&＃xff0c;其 status &＃61; 0&＃xff0c;导致数据不一致&＃xff09;。
• 若先写入 bin log&＃xff0c;当 bin log 写入成功&＃xff0c;而 redo log 写入失败时&＃xff0c;原库中的 status 仍然是 0 &＃xff0c;但是当通过 bin log 恢复时&＃xff0c;其记录的操作是 set status &＃61; 1&＃xff0c;也会导致数据不一致。
  • • • 其核心就是&＃xff0c; redo log 记录的&＃xff0c;即使异常重启&＃xff0c;都会刷新到磁盘&＃xff0c;而 bin log 记录的&＃xff0c; 则主要用于备份。

• • • 对问题的理解
      • 备份时间周期的长短&＃xff0c;感觉有2个方面&＃xff1a;
        • 首先&＃xff0c;是恢复数据丢失的时间&＃xff0c;既然需要恢复&＃xff0c;肯定是数据丢失了。如果一天一备份的话&＃xff0c;只要找到这天的全备&＃xff0c;加入这天某段时间的binlog来恢复&＃xff0c;如果一周一备份&＃xff0c;假设是周一&＃xff0c;而你要恢复的数据是周日某个时间点&＃xff0c;那就&＃xff0c;需要全备&＃43;周一到周日某个时间点的全部binlog用来恢复&＃xff0c;时间相比前者需要增加很多&＃xff1b;看业务能忍受的程度&＃xff1b;
        • 其次&＃xff0c;是数据库丢失&＃xff0c;如果一周一备份的话&＃xff0c;需要确保整个一周的binlog都完好无损&＃xff0c;否则将无法恢复&＃xff1b;而一天一备&＃xff0c;只要保证这天的binlog都完好无损&＃xff1b;当然这个可以通过校验&＃xff0c;或者冗余等技术来实现&＃xff0c;相比之下&＃xff0c;上面那点更重要