MySQL十八：写语句的执行过程

转载~

当我们需要修改一个记录时，数据库会先根据条件找到要修改的数据，然后执行修改写入操作，因此我们再分析写操作的执行过程时，其实是包含读语句的执行过程的。

一、读语句的执行过程

在之前《MySQL运行机制》文中，详细说明了一个查询语句的执行的过程，查询sql的执行过程基本上分为六步:

• 「建立连接（Connectors&Connection Pool）」
• 「查询缓存（Cache&Buffer）」
• 「解析器（Parser）」
• 「预处理器（preprocessor）」
• 「查询优化器（Optimizer）」
• 「操作引擎执行 SQL 语句」

通过以上六步即可在数据库中查询到相应的数据，针对每个步骤的过程，这之前的文中有详细说明，这里不再赘述。

1.1 查询缓存弊大于利

「查询缓存分为Cache和Buffer，两者都是缓存，但是作用不一样：」

• Cache：缓存读取的数据

  「cache是从磁盘读取数据然后存起来方便以后使用。实现数据的重复使用，减少读取的磁盘IO」。

  将硬盘中的数据读取出来放在内存的缓存区中，这样以后再次访问同一个资源，速度会快很多。

• Buffer：缓冲修改过的数据

  「buffer是为了提高内存和硬盘的IO设计的，写入到磁盘的数据会先写入buffer中，然后一起刷盘」。

  从内存中将数据往硬盘中写入，并不是直接写入，而是缓冲到一定大小之后刷入硬盘中。

「两者共性都属于内存，数据都是临时的，一旦关机数据都会丢失」。

查询缓存的弊端在《MySQL运行机制》文中未曾说明，这里做一下补充说明：

「大多数情况下一般不要使用查询缓存，因为查询缓存往往弊大于利」。

• 「当对一个表进行更新操作时，这个表上所有的查询缓存都会被清空」。所以当对更新比较频繁的表使用查询缓存的时候，命中率会非常低，得不偿失。一般我们可以对静态表使用查询缓存（比如字典表，配置表）。也是基于此，MySQL才提供了上述开启查询缓存的方式，直接在SQL语句中指定，按需使用。
• 「MySQL 8.0版本已经没有查询缓存这个功能了，直接将查询缓存整个模块删掉了」。

二、写语句的执行过程

2.1写语句是怎么执行的

前面说到，更新操作时，也会走先查询，所以它的执行流程也是大同小异的。

还是通过这张图，按照【读语句的执行过程】的六个步骤去执行，不同的是：

• 「分析器」

  通过【「词法分析」】和【「语法解析」】确定当前的SQL语句是一条更新语句。

• 「优化器」

  确定索引，执行计划

• 「执行器」

  内存中修改数据，调用存储引擎的修改接口，最终修改数据

「以上就是更新语句的执行过程，看起来似乎跟查询语句没啥区别，只不过一个查询语句，一个是更新语句，两者调用的存储引擎的接口不一样而已。事实上也的确如此，它们很类似，但是更新语句会比查询语句多两个步骤」。

2.2写语句比读语句多了什么

上面说到更新语句会比查询语句多两个步骤，具体是多了什么呢？我们假设一下

按照上述的方式进行更新，似乎是没有问题的，数据也确实能写到数据库中，最终通过存储引擎写入磁盘中。但是有一个问题，我们知道「磁盘是很慢的，而我们的程序操作内存是需要IO操作的，当更新比较频繁的时候，磁盘IO必然会很慢，会降低数据库的性能，高并发下，很容易就会导致数据库宕机」。

既然有这种隐患，那么MYSQL不可能没有解决的，这里就涉及到了MySQL中两个非常重要的日志模块：

• Undo log (撤销日志)
• Redo log（重做日志）
• Binlog（归档日志）

1. MySQL正式利用这两个日志来解决上述频繁IO问题的。也就是经常说的「预写式日志记录（Write-Ahead Logging），即WAL技术，核心就是先写日志，再写磁盘」。
2. 它很类似MQ【异步、削峰】的特性，在更新的时候，先写日志，不更新磁盘（即异步刷盘），在数据库不繁忙的时候（特定的时间点或者时机）再将日志记录更新磁盘（即削峰）

关于Undo log / Redo log与Binlog在之前的《一文详解六大日志》中也有详细的介绍，这里就不再具体描述，只重点说一下在更新操作的中使用过程。

如果对Undo log / Redo log与Binlog不是很熟悉的话，可以看一下之前的文章，以作参考

2.3 Undo log（撤销日志）

「撤消日志是在事务开始之前保存的被修改数据的备份，由InnoDB存储引擎实现」。

主要作用：

• 「用于回滚事务」，直接从undo日志中取到原始值
• 「MVCC机制的实现」：对不同隔离级别下事物能读取到的数据

MVCC机制的实现原理在之前的《读懂MVCC多版本并发控制》 中已经详细描述，感兴趣的可以参考看一下。

2.3 Redo log（重做日志）

「当执行一条更新语句的时候，InnoDB引擎会先把记录写到redo log里，并更新内存，到此更新操作就完成了，此时数据并没有写入磁盘，InnoDB会在特定的时机将记录写入磁盘中」。

我们知道「InnoDB的redo log是固定大小的，所以为了避免在刷盘之前redo log被写满，所以redo log采用的是循环写的方式」，如下：

• write pos ：表示 redo log 当前记录的位置，一边写一边后移
• check point ：表示 「数据页更改记录」 刷盘后对应擦除的位置。

write pos 到 check point 之间的部分是 redo log 空着的部分，用于记录新的记录；

check point 到 write pos 之间是 redo log 待落盘的数据页更改记录。

当 write pos追上check point 时，会先推动 check point 向前移动（先刷盘，后擦除），空出位置再记录新的日志。

2.4 Binlog（归档日志）

在MySQL系列的第一篇《架构体系》中的就已经阐述了基本架构组成包含Server层与存储引擎层，上述的「Redo log是InnoDB引擎所特有的一种日志，而MySQL支持的引擎是多种的，因此Server层也有自己的日志，即binlog（归档日志）」。

顾名思义：binlog日志只能用于归档，

Redo log能够保证MySQL在任何时间段突然奔溃，重启后以前提交的记录都不会丢失，也就是「crash-safe」能力。

• 「简单说一下Redo log与Binlog的不同」

• • Redo log是InnoDB引擎特有的,属于物理日志；binlog是MySQL的Server层实现的，所有引擎都可以使用，属于逻辑日志。

    Redo log：记录的是结果，某个数据页某条记录做了什么修改，记录修改结果

    Binlog：记录的是原始逻辑，也就是修改的过程

  • redo log是循环写的，空间固定会用完，用完就刷盘再清空；binlog是追加写入的，文件写到一定大小后会切换到下一个，不会覆盖之前的日志。

2.5 写入语句的执行过程

通过对以下的几篇文章的介绍，可以使我们对MySQL的写入有了一个大概的认识，内部的执行原理也有了比较清晰的认知，接下来看一下一条sql在执行的整个流程中，从它经历组件，各个组件做的操作等角度来分析一下写操作的执行过程，下面来看一下具体的写操作的执行过程。

update user set name='星河之码' where id=1;

来看看执行上述这个修改语句的整个过程，前面建立连接等几个步骤就省略了，直接看执行器执行时的过程，如下图：

• 「执行器通过存储引擎查找【id=1】的记录」。

  存储引擎查找到记录之后将这条记录所在的数据页全部从磁盘读入内存，然后返回给执行器。

• 「执行器获取到返回的记录后，修改【name='星河之码'】，调用存储引擎修改数据」。

  「存储引擎接收到name='张三'的数据之后，将其更新到内存中对应的数据页中，同时写入redo log日志中，但是redo log写入之后未提交，处于准备（prepare）阶段」。

• 「执行器调用存储引擎修改数据后，会产生一个binlog，并将其写入磁盘中」。

  每个写操作mysql在Server层都会生成一个binlog

• 「binlog写入完成，执行器调用存储引擎的提交事务接口」。

  「存储引擎接收到提交事务请求后，会把写入的redo log状态改成提交（commit）状态，完成更新」。

• 「后台线程会定时将Buffer Pool中的修改过的缓存页加载到磁盘中」

通过以上执行过程分析图，写入操作就完成了，由此可见，虽然我们就写了一句update语句，但是实际上mysql还是帮助我们做了很多工作的。

对上图做了一个简化，其中比较重要的流程就是修改Buffer Pool 与日志同步的过程，如下图：

以上就是MySQ的InnoDB在写入的执行过程，其中涉及到很多细节，这里没有展开，比如数据页的读取，修改数据页之后Buffer Pool怎么刷脏，怎么保证Buffer Pool在有限的内存中加载到更多的热点数据，怎么提高Buffer Pool的命中率等，这些问题在以往的文章中都有详细介绍，有兴趣的可以看一下以下几篇文章：

• Change Buffer
• 36张图理解Buffer Pool
• 一文详解六大日志

2.6 两阶段提交

上述的写操作执行过程中，写入Redo log的时候有两个阶段：「准备阶段与提交阶段」，为什么不直接一步到位，而要分了两步，再调用一下提交事务的接口呢？是否是多此一举呢？

实际上，在写Redo Log 与Binlog的时候采用「准备与提交」两个阶段的方式实现，是为了「保证数据一致性」。如果不用这个方式，而是两个日志都采用直接提交的方式，无论谁先谁后，都可能在数据路宕机时丢失数据导致不一致。

update user set name='星河之码' where id=1;

还是以这条更新语句来看，如下：

• 「先写Redo log后写Binlog」

  「假设先写Redo log，并且写入成功,Binlog还没有写完的时候，数据库宕机了」，那我们在重启数据库后，可以通过Redo log进行恢复，恢复后id=1的记录name为星河之码，但是Binlog没有写完就宕机，所以「Binlog里面是没有这条更新语句」的，当我们使用binlog做日志备份，数据同步或者恢复的时候，由于「binlog丢失就会导致数据与原库不一致」。

• 「先写Binlog后写Redo log」

  「假设先写binlog，Redo log还没有写完的时候，数据库宕机了」，那数据库崩溃恢复后，「Redo log写入失败，事物无效，id=1的记录name为原值」。但是binlog已经记录了这条更新语句，当我们使用binlog做日志备份，数据同步或者恢复的时候，就会多这条更新事物，「导致恢复/备份的id=1的记录name为星河之码，最终导致与原库数据不一致」。

基于此，可以明确无论先写那个日志都会导致数据库不一致，因此，MySQL的设计了准备与提交的两阶段提交的方式。「Redo log和Binlog用于记录事物的行为状态，两阶段提交可以让这两个状态保持逻辑上的一致，以此保证数据的一致性」。