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

文章首发于公众号「蝉沐风」，认真写好每一篇文章，欢迎大家关注交流

这是图解 MySQL 的第 2 篇文章，这篇文章会通过一条 SQL 更新语句的执行流程让大家清楚地明白：

• 什么是 InnoDB 页？缓存页又是什么？为什么这么设计？

• 什么是表空间？不同存储引擎的表在文件系统的底层表示上有什么区别？

• Buffer Pool 是什么？为什么需要？有哪些我们需要掌握的细节？

• MySQL 的三种日志文件 redo 日志、undo 日志、binlog 分别是什么？为什么需要这么多种类型的日志？

正文开始！

之前我们讲过了一条SQL查询语句是如何执行的，那么插入（INSERT）、更新（UPDATE）和删除（DELETE）操作的流程又是什么样子呢？

其实对于 MySQL 而言，只有两种通常意义的操作，一种是 Query（查询），另一种是 Update（更新），后者包含了我们平常使用的 INSERT、UPDATE 和 DELETE 操作。

那么 MySQL 的更新流程和查询流程有什么区别呢？

其实基本的流程是一致的，也要经过处理连接、解析优化、存储引擎几个步骤。主要区别在更新操作涉及到了 MySQL 更多的细节。

注：我们接下来的所有描述，针对的都是 InnoDB 存储引擎，如果涉及到其他存储引擎，将会特殊说明

1. 一些需要知道的概念

对于 MySQL 任何存储引擎来说，数据都是存储在磁盘中的，存储引擎要操作数据，必须先把磁盘中的数据加载到内存中才可以。

那么问题来了，一次性从磁盘中加载多少数据到内存中合适呢？当获取记录时，InnoDB 存储引擎需要一条条地把记录从磁盘中读取出来吗？

当然不行！我们知道磁盘的读写速度和内存读写速度差了几个数量级，如果我们需要读取的数据恰好运行在磁盘的不同位置，那就意味着会产生多次 I/O 操作。

因此，无论是操作系统也好，MySQL 存储引擎也罢，都有一个预读取的概念。概念的依据便是统治计算机界的局部性原理。

空间局部性：如果当前数据是正在被使用的，那么与该数据空间地址临近的其他数据在未来有更大的可能性被使用到，因此可以优先加载到寄存器或主存中提高效率

就是当磁盘上的一块数据被读取的时候，我们干脆多读一点，而不是用多少读多少。

1.1 InnoDB 页

InnoDB 存储引擎将数据划分为若干个页，以页作为磁盘和内存之间交互的最小单位。InnoDB 中页的大小默认为 16KB。也就是默认情况下，一次最少从磁盘中读取 16KB 的数据到内存中，一次最少把内存中 16KB 的内容刷新到磁盘上。

对于 InnoDB 存储引擎而言，所有的数据（存储用户数据的索引、各种元数据、系统数据）都是以页的形式进行存储的。

1.2 表空间

为了更好地管理页，MySQL 又设计了「表空间」的概念。表空间又有很多类型，具体类型我们不需要知道，我们只需要知道，一个表空间可以划分成很多个 InnoDB 页，InnoDB 表数据都存储在某个表空间的页中。

为了方便我们定位，MySQL 贴心的为表空间设计了一个唯一标识——表空间 ID（space ID）。同理，InnoDB 页也有自己的唯一编号——页号（page number）。

因此，我们可以这么认为。给定表空间 ID 和页号以及页的偏移量，我们就可以定位到 InnoDB 页的某条记录，也就是数据库表的某条记录。

1.2.1 数据表在文件系统中的表示

为了更好地让大家理解这个抽象的概念，我创建了名为test的数据库，在其下分别创建了 3 张表t_user_innodb，t_user_myisam，t_user_memory，对应的存储引擎分别为InnoDB、MyISAM、MEMORY。

进入 MySQL 的数据目录，找到test目录，看一下test数据库下所有表对应的本地文件目录

drwxr-x--- 2 mysql mysql  4096 Jan 26 09:28 .drwxrwxrwt 6 mysql mysql  4096 Jan 26 09:24 ..-rw-r----- 1 mysql mysql    67 Jan 26 09:24 db.opt-rw-r----- 1 mysql mysql  8556 Jan 26 09:28 t_user_innodb.frm-rw-r----- 1 mysql mysql 98304 Jan 26 09:28 t_user_innodb.ibd-rw-r----- 1 mysql mysql  8556 Jan 26 09:27 t_user_memory.frm-rw-r----- 1 mysql mysql     0 Jan 26 09:28 t_user_myisam.MYD-rw-r----- 1 mysql mysql  1024 Jan 26 09:28 t_user_myisam.MYI-rw-r----- 1 mysql mysql  8556 Jan 26 09:28 t_user_myisam.frm

1.2.2 InnoDB 是如何存储表数据的

「表空间」是 InnoDB 存储引擎独有的概念。

我们看到t_user_innodb表在数据库对应的test目录下会生成以下两个文件

• t_user_innodb.frm

• t_user_innodb.ibd

其中，t_user_innodb.ibd 就是t_user_innodb表对应的表空间在文件系统上的表示；t_user_innodb.frm 用来描述表的结构，如表有哪些列，列的类型是什么等。

1.2.3 MyISAM 是如何存储表数据的

和 InnoDB 不同，MyISAM 没有表空间的概念，表的数据和索引全都直接存放在对应的数据库子目录下，可以看到t_user_myisam对应了三个文件

• t_user_myisam.MYD

• t_user_myisam.MYI

• t_user_myisam.frm

其中，t_user_myisam.MYD 表示表的数据文件，也就是我们实际看到的数据表的内容；t_user_myisam.MYI 表示表的索引文件，为该表创建的索引都会存放在这个文件中；t_user_myisam.frm 用来描述表的结构。

1.2.4 MEMORY 是如何存储表数据的

MEMORY 存储引擎对应的数据表只有一个描述表结构的文件 t_user_memory.frm。

2. 缓冲池 Buffer Pool

为了更好的利用局部性原理带给我们的优势，InnoDB 在处理客户端请求时，如果需要访问某个页的数据，会把该数据所在的页的全部数据加载到内存中。哪怕是只需要访问一个页中的一条数据，也需要加载整个页。

从磁盘中加载数据到内存中的操作太昂贵了！有什么办法可以提高数据操作的效率呢？缓存！

为了缓存磁盘的页，InnoDB 在 MySQL 服务器启动时会向操作系统申请一片连续的内存区域，这片内存区域就是 Buffer Pool。

很容易理解，为了更好地缓存页数据，Buffer Pool 对应的一片连续内存空间也被划分为若干个页，而且默认情况下，Buffer Pool 页的大小和 InnoDB 页大小一样，都是 16KB。为了区分两种不同的页，我们将 Buffer Pool 中的页面称为缓冲页。

读取数据的时候，InnoDB 先判断数据是否在 Buffer Pool 中，如果是，则直接读取数据进行操作，不用再次从磁盘加载；如果不是，则从磁盘加载到 Buffer Pool 中，然后读取数据进行操作。

修改数据的时候，也是将数据先写到 Buffer Pool 缓冲页中，而不是每次更新操作都直接写入磁盘。当缓冲页中的数据和磁盘文件不一致的时候，缓冲页被称为脏页。

那么脏页是什么时候被同步到磁盘呢？

InnoDB 中有专门的后台线程每隔一段时间会把脏页的多个修改刷新到磁盘上，这个动作叫做「刷脏」。

3. redo 日志

3.1 为什么需要 redo 日志

不定时刷脏又带来一个问题。如果脏页的数据还没有刷新到磁盘上，此时数据库突然宕机或重启，这些数据就会丢失。

首先想到的最简单粗暴的解决方案就是在事务提交之前，把该事务修改的所有页面都刷新到磁盘。但是上文说过，页是内存和磁盘交互的最小单位，如果只修改了 1 个字节，却要刷新 16KB 的数据到磁盘上，不得不说太浪费了，此路不通！

所以，必须要有一个持久化的措施。

为了解决这个问题，InnoDB 把对所有页的更新操作（再强调一遍，包含 INSERT、UPDATE、DELETE）专门写入一个日志文件。

当有未同步到磁盘中的数据时，数据库在启动的时候，会根据这个日志文件进行数据恢复。我们常说的关系型数据库的ACID特性中的D（持久性），就是通过这个日志来实现的。

这个日志文件就是大名鼎鼎的 redo 日志。

「re」在英文中的词根含义是“重新”，redo 就是「重新做」的意思，顾名思义就是 MySQL 根据这个日志文件重新进行操作

这就出现了一个有意思的问题，刷新磁盘和写 redo 日志都是进行磁盘操作，为什么不直接把数据刷新到磁盘中呢？

3.2 磁道寻址

我们需要稍微了解一下磁道寻址的过程。磁盘的构造如下图所示。

每个硬盘都有若干个盘片，上图的硬盘有 4 个盘片。

每个盘片的盘面上有一圈圈的同心圆，叫做「磁道」。

从圆心向外画直线，可以将磁道划分为若干个弧段，每个磁道上一个弧段被称之为一个「扇区」（右上图白色部分）。数据是保存在扇区当中的，扇区是硬盘读写的最小单元，如果要读写数据，必须找到对应的扇区，这个过程叫做「寻址」。

3.2.1 随机 I/O

如果我们需要的数据是随机分散在磁盘上不同盘片的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的盘片然后继续寻找对应的扇区，才能找到我们所需要的一块数据，持续进行此过程直到找完所有数据，这个就是随机 I/O，读取数据速度非常慢。

3.2.2 顺序 I/O

假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据之后，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序 I/O。

现在回答之前的问题。因为刷脏是随机 I/O，而记录日志是顺序 I/O（连续写的），顺序 I/O 效率更高，本质上是数据集中存储和分散存储的区别。因此先把修改写入日志文件，在保证了内存数据的安全性的情况下，可以延迟刷盘时机，进而提升系统吞吐。

3.3 redo 日志的系统变量

redo 日志位于 MySQL 数据目录下，默认有ib_logfile0和ib_logfile1两个文件，如下图所示。

可以发现，两个 redo 日志文件的大小都是 50331648，默认 48MB。为什么这个大小是固定的呢？因为如果我们要使用顺序 I/O，就必须在申请磁盘空间的时候一次性决定申请的空间大小，这样才能保证申请的磁盘空间在地址上的连续性。

这也就决定了 redo 日志的旧数据会被覆盖，一旦文件被写满，就会触发 Buffer Pool 脏页到磁盘的同步，以腾出额外空间记录后面的修改。

可以通过以下指令查看 redo 日志的系统属性。

mysql> show variables like 'innodb_log%';+-----------------------------+----------+| Variable_name               | Value    |+-----------------------------+----------+| innodb_log_buffer_size      | 16777216 || innodb_log_checksums        | ON       || innodb_log_compressed_pages | ON       || innodb_log_file_size        | 50331648 || innodb_log_files_in_group   | 2        || innodb_log_group_home_dir   | ./       || innodb_log_write_ahead_size | 8192     |+-----------------------------+----------+

介绍到这里，读者朋友可以发现，我们刚才探索的是如何让已经提交的事务保持持久化，但是如果某些事务偏偏在执行到一半的时候出现问题怎么办？

事务的原子性要求事务中的所有操作要么都成功，要么都失败，不允许存在中间状态。就好比我在写这篇文章的时候，会时不时地敲一下ctrl+Z返回到上一步或者过去好几步之前的状态，MySQL 也需要“留一手”，把事务回滚时需要的东西都记录下来。

比如，插入数据的时候，至少应该把新增的这条记录的主键的值记录下来，这样回滚的时候只要把这个主键值对应的记录删除就可以了。

MySQL 又一个鼎鼎大名的日志——undo 日志，正式登场！

4. undo 日志

undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态，分为 insert undo log 和 update undo log。

如果修改数据时出现异常，可以用 undo log 来实现回滚操作（保持原子性）。可以理解为 undo 日志记录的是反向的操作，比如 INSERT 操作会记录 DELETE，UPDATE 会记录 UPDATE 之前的值，和 redo 日志记录在哪个物理页面做了什么操作不同，所以这是一种逻辑格式的日志。

undo 日志和 redo 日志与事务密切相关，被统称为「事务日志」。

关于 undo 日志，我们目前只需要了解这么多即可

5. SQL 更新语句的执行总结——初版

有了事务日志之后，我们来简单总结一下更新操作的流程，这是一个简化的过程。

name 原值是chanmufeng。

update t_user_innodb set name ='chanmufeng1994' where id = 1;

1. 事务开始，从内存（Buffer Pool）或磁盘取到包含这条数据的数据页，返回给 Server 的执行器；

2. Server 的执行器修改数据页的这一行数据的值为 chanmufeng1994；

3. 记录 name=chanmufeng 到 undo log；

4. 记录 name=chanmufeng1994 到 redo log；

5. 调用存储引擎接口，记录数据页到 Buffer Pool（修改 name=penyuyan）；

6. 事务提交。

6. binlog 日志

之前我们讲过，从 MySQL 整体架构来看，其实可以分成两部分

• Server 层，它主要做的是 MySQL 功能层面的事情，比如处理连接、解析优化等；

• 存储引擎层，负责存储相关的具体事宜。

redo 日志是 InnoDB 存储引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志），它可以被所有存储引擎使用。

6.1 为什么有了 redo 日志还需要 binlog？

我想你可能会问出这个问题，实际上，更准确的问法是为什么有了 binlog 还需要有 redo 日志？主要有以下几个原因。

1. 因为最开始 MySQL 里并没有 InnoDB 存储引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有崩溃恢复的能力，InnoDB 后来以插件的形式被引入，顺便带来了 redo 日志；

2. binlog 日志是用来归档的，binlog 以事件的形式记录了所有的 DDL 和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑日志），但是不具备宕机恢复的功能，因为可能没有来得及刷新脏页，造成脏页数据的丢失，而这些操作也没有保存到 binlog 中从而造成数据丢失；

3. binlog 记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而 redo 日志记录的是关于每个页的更改的物理情况。功能压根不是一回事儿。

6.2 binlog 日志的作用

6.2.1 主从复制

binlog 是实现 MySQL 主从复制功能的核心组件。

master 节点会将所有的写操作记录到 binlog 中，slave 节点会有专门的 I/O 线程读取 master 节点的 binlog，将写操作同步到当前所在的 slave 节点。

6.2.2 数据恢复

假如你在阅读这篇文章的时候觉得我写得实在太好，拍案叫绝的时候一不小心把公司的数据库给删了，你该怎么做才能恢复到你删库之前的那个时刻的状态？

这个时候就要用到 binlog 了，前提是 binlog 没有被删除，否则，神仙也救不了你了。

通常情况下，公司会定期对数据库进行全量备份，可能隔一个月，一周，甚至可能每天都备份一次。运气好的话你可以使用前一天的全量备份，恢复到前一天的某时刻状态（或者一周、一月之前），然后从全量备份的时刻开始，从 binlog 中提取该时刻之后（前提是你的 binlog 里面存放了这段时间的日志）的所有写操作（当然，你得过滤掉你的删库操作），然后进行操作回放就可以了。

是不是很简单？

问题又来了。再看一眼我们的更新语句。

update t_user_innodb set name ='chanmufeng1994' where id = 1;

假如这条更新语句已经被写入到了 redo 日志，还没来得及写 binlog 的时候，MySQL 宕机重启了，我们看一下会发生什么。

因为 redo 日志可以在重启的时候用于恢复数据，所以写入磁盘的是 chanmufeng1994。但是 binlog 里面没有记录这个逻辑日志，所以这时候用 binlog 去恢复数据或者同步到从库，就会出现数据不一致的情况。

所以在写两个日志的情况下，就类似于「分布式事务」的情况，如果你不清楚分布式事务是个什么东西也没关系，我在之后的文章会介绍到。能够明确的就是 redo 日志和 binlog 日志如果单纯依次进行提交是无法保证两种日志都写成功或者都写失败的。

我们需要「两阶段提交」。

6.3 两阶段提交

两阶段提交不是 MySQL 的专利，两阶段提交是一种跨系统维持数据逻辑一致性的常见方案，尤其在分布式事务上，所以请读者重点体会思想

我们把 redo 日志的提交分成两步，两步中 redo 日志的状态分别是prepare和commit。步骤如下

1. InnoDB 存储引擎将更改更新到内存中后，同时将这个更新操作记录到 redo 日志里面，此时 redo 日志处于prepare状态；

2. 执行器生成这个操作的 binlog，并将 binlog 刷盘；

3. 执行器调用 InnoDB 的提交事务接口，InnoDB 把刚刚写入的 redo 日志改成commit状态。至此，所有操作完成。

加上两阶段提交之后我们再来看一下 SQL 更新语句的执行流程。

7. SQL 更新语句的执行总结——终版

1. 客户端发送更新命令到 MySQL 服务器，经过处理连接、解析优化等步骤；

2. Server 层向 InnoDB 存储引擎要 id=1 的这条记录；

3. 存储引擎先从缓存中查找这条记录，有的话直接返回，没有则从磁盘加载到缓存中然后返回；

4. Server 层执行器修改这条记录的 name 字段值；

5. 存储引擎更新修改到内存中；

6. 存储引擎记录 redo 日志，并将状态设置为prepare状态；

7. 存储引擎通知执行器，修改完毕，可以进行事务提交；

8. Server 先写了个 binlog；

9. Server 提交事务；

10. 存储引擎将 redo 日志中和当前事务相关的记录状态设置为commit状态。

完！

推荐阅读

• 一条SQL查询语句是如何执行的
  

参考资料

1. MySQL 实战 45 讲

2. MySQL 是怎样运行的