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

目录

• 引言
• 更新流程图
• 更新流程说明
  • 第一步：更新数据
    • 数据页内存
    • Change Buffer
  • 第二步：缓存日志内容
    • redo log buffer
    • binlog cache
  • 第三步：日志写入磁盘
  • 两个JOB：脏页落盘 和 redo log Buffer 落盘
    • 脏页落盘
    • redo log 落盘
• 总结

引言#

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

极客时间林晓斌老师的图

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

但是对于存储引擎内部的执行流程没有讲到。本文用一条更新语句带你了解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 的写入策略：

1. redo log prepere write
2. redo log prepere fsync
3. binlog wirte
4. binlog fsync
5. redo log commit write
6. redo log commit fsync

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

1. redo log prepere write
2. binlog wirte
3. redo log prepere fsync
4. binlog fsync
5. 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官方文档，若有不明白或错漏之处，欢迎留言与我讨论。