MySQL：一条更新语句是如何执行的

2021 年 6 月 24 日
筆記
MySQL

引言
更新流程图
更新流程说明
总结

引言

在上篇文章MySQL：一条SQL是如何执行的中我们先讲了一条SQL语句是如何执行的，如图所示：

极客时间林晓斌老师的图

客户端先通过连接器建立连接，连接器自会判断用户权限
(如果开启了查询缓存并且匹配上key就直接返回结果给客户端，不执行下面的流程)
分析器对SQL进行词法分析与语法分析，明确SQL要做什么
优化器生成执行计划，选择索引，明确怎么做
执行器通过操作存储引擎读写接口来获取或更新数据，并将执行结果返回给客户端

但是对于存储引擎内部的执行流程没有讲到。本文用一条更新语句带你了解InnoDB的内存结构和磁盘结构，限于篇幅和作者本身知识储备，或有不详尽之处，欢迎指出。

本文目的是让你知道更新过程中涉及到了哪些东西，这个东西的细节和配置就需要你自己去慢慢探索了。

限于篇幅，本文不讲解WAL机制（redo log 和 binlog），阅读此文的朋友最好对此先有所了解

更新流程图

有点复杂，其实这个是我用来复习的，涉及到的知识点还是挺多的，欢迎取用，下面慢慢讲解下图

上面涉及到了诸多内存与磁盘，下面从InnoDB的内存结构与磁盘结构来理清上述流程。本文主要是想讲流程，对InnoDB的内存结构和磁盘结构只会简单讲解，有遗漏或错误之处还请指正。

更新流程说明

主要讲更新过程中涉及的内存和磁盘，讲是什么和会怎么样，而为什么要这样放在本文末尾拓展知识里，这里主要先让你形成更新脉络。

第一步：更新数据

当我们要更新 id = 2 这条数据时，会先去判断该记录是否在数据页内存中。

如果在，那么更新数据页内存，此时数据页内存中的数据和磁盘上数据不一致，我们叫他脏页
如果不在，先要判断是否是唯一索引
- 如果不是，那么将更新内容写入到Change Buffer中，结束
- 如果是唯一索引，那么将 id = 2 这条记录读入数据页内存中（干净页），然后更新数据页内存（变脏页）

数据页内存

数据页内存是什么

数据页内存是InnoDB buffer pool的一块内存区域，存储的单位是数据页，例如id = 2 这行记录和所在的页数据。

扩展：InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

为什么需要数据页内存

我们要知道缓存机制是为了解决CPU高算力和I/O读写能力的差距。这里也不例外，对于查询来说，如果记录在数据页内存中那么查询要快的多，内存读写比磁盘读写快多了，更新也是一样的道理，不管是更新到数据页内存中还是更新到Change Buffer中，都是读写内存。

Change Buffer

Change Buffer 是什么

是InnoDB buffer pool的另一块内存区域，他和数据页内存不同的是他存储的是更新内容，例如 把id = 2的这条记录中某个列的值从 1 改为 2。下次查询id = 2这条记录时，让磁盘中的记录（此时值为1）执行 change buffer中的更新操作，得到列值2，这个过程叫merge，merge后的结果会放入数据页内存中。

注意这里要判断是不是唯一索引，只有非唯一索引的更新操作才可以使用change buffer。

为什么非唯一索引才能使用change buffer

对于非唯一索引，往往会有多条记录，这些记录往往是随机存储的，不在一个数据页上，假设 id = 2 有 1000条记录，分散在10个数据页上，那么就要10次I/O读，而写入Change Buffer 是内存写，所以Change Buffer对更新性能的提升是很明显的。

为了使用change buffer 提升更新性能，我们是不是可以更多的选择普通索引呢

innodb_change_buffer_max_size 变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。默认情况下， innodb_change_buffer_max_size设置为 25。最大设置为 50。

注意虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

change buffer的前身是insert buffer,只能对insert 操作优化；后来升级了，增加了update/delete的支持，名字也改叫change buffer.

第二步：缓存日志内容

当我们对数据页内存或Change Buffer更新以后，会将更新记录写入 redo log buffer。
- 如果是更新数据页，那么redo log buffer 记入 update ... id = 2
- 如果是写入change buffer, 那么记入 new change buffer item('update ... id = 2')
同时我们会将SQL语句写入到binlog cache中

磁盘I/O是数据库里面成本最高的操作之一，前面将数据更新都落在内存上，大大减少了磁盘I/O次数，那么对于写日志，也有同样的机制来避免直接对磁盘读写。

redo log buffer

在一个事务的更新过程中，日志是要写多次的。例如如下语句

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。执行第二个 insert的时候，再次往redo log buffer中写入一条日志并更新数据页内存。

但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的。

binlog cache

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。
这就涉及到了 binlog cache 的保存问题。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

第三步：日志写入磁盘

到了这一步，就要准备写日志到磁盘了。不管是redo log 还是 binlog，在事务执行过程中，都会先写入到内存中，只有在事务提交的时候才会写磁盘。

我们先来看看磁盘读写相关的知识点

write：指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
fsync：才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

再来看MySQL对binlog的写入策略配置sync_binlog

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

由于本文重点不是探讨WAL，所以这里就不重点分析上述配置对异常恢复的影响了，这里我们把两个参数都设置为1。然后向你介绍组提交机制。

前面说到fsync才占IOPS，那么我们可以尽量延后fsync的执行时间，这样一次I/O写入的数据更多，减少了I/O次数。就像redo log buffer一样，只不过这里把 write 的 page cache 当做了缓冲池。

我们先来看正常情况下原来redo log 和 binlog 的写入策略：

redo log prepere write
redo log prepere fsync
binlog wirte
binlog fsync
redo log commit write
redo log commit fsync

优化的思路是减少磁盘I/O次数，那么可以让 fsync 的动作慢一慢，组提交机制如下所示：

redo log prepere write
binlog wirte
redo log prepere fsync
binlog fsync
redo log commit write

两者对比有两点差别：

原第二步和第三步调换，也就是说将 redo log prepere fsync 放在 binlog wirte 之后，这样binlog write 可以缓存更多内容
原第六步 redo log commit fsync 去掉了，这是因为redo log prepere fsync 和 binlog fsync 执行完已经能够满足异常恢复，内中原因请了解 WAL。

组提交从字面意思很好理解，多组一起提交。

并发场景下，对于 redo log 来说，多个事务日志都在 redo log buffer中，有一个刷盘了其他事务的日志也会跟着一起刷盘，假设有3个事务同时执行，同时结束，那么提交时只需要fsync一次而不是3次。

对于binlog来说，虽然每个线程都有自己的binlog cache 缓存，但是都共享一个binlog文件，即使我们将 sync_binlog 设置为1，由于组提交机制在 binlog wirte 和 binlog fsync 插入了 redo log fsync，那么在并发场景下，binlog page cache也是可能存在多个事务日志的，这样也减少了刷盘次数。

如果将 sync_binlog 设为 100，当累计100个事务才fsync，大大减少了IOPS。这样你可能更容易理解组提交机制了，但要注意如果机器宕机，那么这一百个事务的binlog就丢失了。

两个JOB：脏页落盘和 redo log Buffer 落盘

脏页落盘

前面我们说过，更新操作要么落在数据页内存上要么落在Change Buffer上，并不会立刻写到磁盘上。即使事务提交，我们从上图可以看到事务提交的核心是对redo log和binlog的操作，并不强调把数据页中的脏页刷到磁盘上。那么，数据页内存中的脏页是谁把他写到磁盘上了呢？

这就要说到数据页的刷盘机制了，正常情况下，系统会在“空闲”的时候自动落盘，除此之外，发生以下情况也会触发数据页内存落盘。

redo log 满了
MySQL 正常关闭
Buffer Pool 内存不足

那么如果系统异常关闭了呢？

在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

redo log 落盘

前面说到 redo log 会先写到 redo log buffer 中，然后在事务提交的时候刷到磁盘，但是要注意redo log 在事务没有提交的时候也是会刷到磁盘的，MySQL有个JOB每隔1秒就会把redo log buffer 刷到磁盘。注意redo log buffer 是在语句执行时就写入了，所以redo log 落盘时可能事务还没有提交。

注意Binlog不会在事务未提交前落盘，Binlog只会在事务提交后才刷新到磁盘。

实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

总结

这篇文章写得马马虎虎，有诸多不详尽之处，因为涉及到的内存结构和磁盘结构过多，还有WAL机制，实在不是一篇文章能够讲完的。所以本文主要是想讲是什么，让你知道更新过程中涉及到哪些东西，至于为什么要这样，buffer pool 还有哪些细节，很多点都没有挑明，建议参考MySQL官方文档，若有不明白或错漏之处，欢迎留言与我讨论。

Tags: MySQL