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

上篇说到一条SQL查询语句如何执行，这下说一下一条SQL更新语句如何执行的，其实跟一条查询语句执行过程大差不差，只不过更新流程会涉及两个重要的日志模块：redolog和binlog。

先简单说一下大致流程：客户端连接-连接器权限认证-分析器分析词法和语法-优化器决定执行方案-执行器执行优化后的方案-更新成功。（这里不会走到查询缓存的原因很简单：我们执行的语句是更新，而不是查询，缓存的目的就是能减少磁盘I/O的次数，直接在缓存中找到结果然后返回给客户端。）

下面介绍一下我们的主角：redo log(重做日志)和bin log(归档日志)

redo log：InnoDB引擎特有的日志

先讲个故事，如果你是一个小卖部的老板（这时候还没有什么互联网、记账app、备忘录等），假如有人会赊账或者还账，如果人少的话，用**铅笔(注意一下，是用铅笔，后面会说明原因)**写一张便利贴就能搞定，但是人多的话，肯定就需要一个记录赊账/还账的总账本，那么别人来还账/赊账的时候，老板你无疑是两种做法：

  • 直接拿出总账本，找到相关记录进行修改。
  • 先记在便利贴上，等晚上打烊空闲的时候再通过便利贴来一一对总账本里面进行相关修改。

那么，当某些时刻赊账/还账人很多的时候，就不得不使用第二种方案了，毕竟总账本里面记录的人太多了，找到一个人的时间肯定很长，毕竟密密麻麻的字堆一起，找到了再进行计算、修改会很麻烦。

MySQL也是这样，如果每次修改一条记录，都需要从磁盘(总账本)里面找到对应的记录，然后进行计算，回写磁盘，想想光IO查询成本都不低，为了优化，MySQL的设计者就采用了先写便利贴，再抽空根据便利贴里面的记录在总账本找到对应的记录进行修改。

实际这种想法就是MySQL中的WAL技术（Write-Ahead-Logging），顾名思义就是先写日志，再写磁盘，也就是先写便利贴，再写总账本。

大致流程就是：

当一条记录要更新的时候，待事务提交之后，InnoDB引擎会先把操作记录写入日志缓冲区，再将缓冲区中的日志异步写到磁盘中，并更新内存中对应的记录，这个时候更新流程就算完成了，而具体什么时候将修改后的数据更新到磁盘中进行持久化，往往是在系统空闲的时候做，比如中午/晚上，你的店休息/打烊了。

介绍完redo log工作的大致流程，再解释一下上面为啥说的是用铅笔写便利贴，这是因为InnoDB的redo log是固定大小的，它实际上可以虚拟化成一个圆圈，分成四块，每一块可以存储1GB的内容，当写完一圈之后，就会覆盖以前的log日志开始下一轮覆写。如下图所示：

image-20231219145330819

  • write_pos：当前记录的位置，会一边写一边往后移

  • write_pos—check_point区间：表示接下来可写的部分记录新的操作log。

  • check_point：用于擦除以前的log日志，但是在擦除前需要把被擦除部分的数据更新到数据文件中。

提问：如果是redo log是先写入日志缓冲区再写入磁盘，如果在写入缓冲区之后，还没来得及写入磁盘，MySQL服务被kill了，redo log日志不会丢失吗？

bin log：归档日志

先说一下binlog和redolog的区别：

  • redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使⽤。
  • redo log是物理⽇志，记录的是“在某个数据⻚上做了什么修改”；binlog是逻辑⽇志，记录的是这个 语句的原始逻辑，⽐如“给ID=2这⼀⾏的c字段加1 ”
  • redo log是循环写的，空间固定会⽤完；binlog是可以追加写⼊的。“追加写”是指binlog⽂件写到⼀ 定⼤⼩后会切换到下⼀个，并不会覆盖以前的⽇志。

两个log日志互相配合的过程大致如下：

image-20240310120332997

  • 提问：为什么不是先提交redolog再提交binlog或者先提交redolog再提交binlog
  • 回答：主要是为了保证两个日志的数据一致性问题，也是为了保证原子性，防止因为其中一个log提交了事务，但是因为系统故障导致另一个log没有写入日志从而导致的日志数据一致性问题。