谈一谈一条SQL的查询、更新语句究竟是如何执行的?

本篇文章是基于《MySQL45讲》来写的个人理解与感悟。

理解

先看下图：

上一篇文章我们讨论了一条SQL查询语句的执行流程，并介绍了执行过程中涉及的处理模块。

回顾一下：

大体来说，MySQL可以分为Server层和存储引擎层两部分。就是对应着图中的两个圈。

server层包含查询缓存、分析器、优化器、执行器等，以及及所有的内置函数（如日期、时间…）所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取，而存储引擎架构模式是插件式的，有很多种，比如持InnoDB、MyISAM、Memory等，这也就意味着也就是说不同存储引擎共用一个server层。

即，一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

如果有不太清楚的小伙伴可以去下面的文章进行阅读，因为执行和更新语句的流程其实差别不是特别大，所以先去了解执行流程之后，再来看本篇文章，那么就很清晰易懂了！

上篇文章链接在下面👇🏻👇🏻👇🏻

文章链接：谈一谈一条SQL查询语句究竟是如何执行的?

执行流程

背景信息：

下面是这个表的创建语句，这个表有一个主键ID和一个整型字段c：

create table T(id int primary key,c int
);

如果要将ID=2这一行的值加1，SQL语句就会这么写：

update T set c=c+1 where ID=2;

可以确定的说，查询语句的那一套流程，更新语句必然同样会走一遍，但是会有稍许不同，接下来进行介绍。

更新流程区别

首先建立连接，然后当走到在查询缓存的时候，更新表的操作会导致缓存的清空【区别】，所以这个时候T表的所有缓存结果都清空。

这也是为什么一般不建议使用查询缓存的原因。

接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用ID这个索引。然后，执行器负责具体执行，找到这一行，然后更新…

更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志） 、binlog （归档日志）这三种日志：

• undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• redo log（重做日志） ：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

衍生知识

redo log

我们先来看个小故事：

在《孔乙己》这篇文章，酒店掌柜有一个粉板，专门用来记录客人的赊账记录。

如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，但是这个时候掌柜一定还有一个专门记录赊账的账本。

如果有人要赊账或者还账的话，掌柜一般有两种做法：

   1. 直接把账本翻出来，把这次赊的账加上去或者扣除掉；
   2. 先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。
首先，你得找到这个人的赊账总额那条记录。你想想，密密麻麻几十页，掌柜要找到那个名字，可能还得带上老花镜慢慢找，找到之后再拿出算盘计算，最后再将结果写回到账本上。

这整个过程想想都麻烦。相比之下，还是先在粉板上记一下方便。你想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低得让人难以忍受？
而粉板和账本配合的整个过程，其实就是MySQL里经常说到的WAL技术【写时复制】，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（粉板）里面，并更新内存，这个时候更新就算完成了。
同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的
事。

如果今天赊账的不多，掌柜可以等打烊后再整理。但如果某天赊账的特别多，粉板写满了，又怎么办呢？
这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间。

与此类似，InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写，如下图所示：

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。

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

write pos到checkpoint的可以用来记录新的操作。如果write pos 追上checkpoint，表示redo满了，得停下来先擦掉一些记录，把 checkpoint推进一下。

总结

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo里，并更新内存【这个时候更新就算完了】。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面【往往是在系统比较空闲的时候做】。但是如果redo满了就不行了【redo的大小是固定的，假设配置了4组1GB，那么总共就是4GB。然后就是从头开始写，写到末尾再重新从头开始写】

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个
能力称为crash-safe。
要理解crash-safe这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或
写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板
上的数据明确赊账账目。

binlog

MySQL整体来看，其实就两块：一块是Server层，它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板redo log是InnoDB引擎特有的日志。而Server层也有自己的日志，称为binlog（归档日志）。

为什么会有两份日志？

因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。

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

两种日志有以下三点不同：

  1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

  2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

下面，我们再来看看看执行器和InnoDB引擎在执行下面更新语句的内部流程：

update T set c=c+1 where ID=2;

先看图：

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，这就是"两阶段提交"。

为了保证事务的持久性和一致性，MySQL需要确保redo log和binlog的同步持久化。MySQL通过“两阶段提交”的机制来实现在事务提交时，这两个日志必须保持一致，以避免出现数据不一致的问题。

PS：
因为两阶段提交我之前写了一篇文章专门介绍，所以就不在这里占用篇幅了，大家可以点击下面链接进行查看。👇🏻👇🏻👇🏻👇🏻

文章链接：谈一下MySQL的两阶段提交机制

ps:

至于undo log我会在接下来的相关的MVCC文章进行介绍，所以有兴趣的小伙伴记得点个关注，谢谢你的支持！