MySQL架构（二）SQL 更新语句是如何执行的？

前言

在上一篇文章中，我们从一个查询语句的执行流程知道了 MySQL 架构可分为 Server 层和存储引擎层，以及各个层级的具体部件。

那么在这篇文章中，小鱼将介绍更新语句的执行流程，从中我们又能学到什么呢？

SQL 的更新流程

我们先创建一张表作为演示表，作为演示表只需要一个主键、一个额外字段就可以了。下面是演示表的创建语句：

CREATE TABLE test(ID int primary key, age int);

如果我们需要将 ID=2 目标值自增 1，更新的 SQL 语句如下。

UPDATE test SET age=age+1 WHERE ID=2;

更新目标值时，得先查找的该行数据，所以也会执行SQL查询语句的流程。

1. 在执行语句前，连接上数据库（连接器）。
2. 因为是更新语句，涉及到更新目标表的查询缓存会失效，所以该语句会把 test 表所有缓存结果都清空。
3. 分析器通过词法分析和语法分析知道该语句为更新语句。
4. 优化器决定要使用 ID 索引。
5. 最后，执行器负责更新的具体执行，根据索引找到目标行，再执行更新。
   1. 执行器会先找引擎取出 ID=2 的行数据。因为 ID 是主键索引，引擎直接用索引树搜索找到目标行数据。存在以下两种情况：
      1. 若 ID=2 的行数据所在的数据页已经在内存中，就会直接将内存地址返回给执行器；
      2. 否则，需要先从数据文件（磁盘）中读入到内存，再返回给执行器。
   2. 执行器拿到引擎给的行数据，把这个值加上 1，得到新行数据，再通过调用引擎接口写入这新行数据。
   3. 引擎将更新后的数据更新到内存中，同时会将这个更新操作记录到 redo log 日志文件中，此时 redo log 处于 prepare 状态。然后通知执行器执行完成，并且随时可以提交事务。
   4. 执行器生成本操作的 binlog日志，并把 binlog 日志写入磁盘。
   5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

上面提到的两次写入日志redo log和binlog，就是MySQL的两阶段提交，是为了保证数据的一致性。这里后文会写一篇文章进行单独介绍。

更新语句流程与查询语句流程不一样的地方在于日志模块，更新语句涉及到两个十分重要的日志模块——redo log（重做日志）和 binlog（归档日志）。

Redo log 重做日志

​​Redo Log​​称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。

mysql 数据是被持久化写进磁盘的，每次更新也需要找到目标数据，在进行修改，每次更新都执行一遍该操作，这个过程的 IO 成本是比较高的。

为了解决这个问题，MySQL 采用了先写日志，空余时间再写磁盘的思路来提升更新效率。即是 WAL 技术（预写式日志，WAL 的全称是 Write-Ahead Logging）。

具体来说，当有更新语句执行的时候，InnoDB 引擎会先把更新记录写到 redo log 日志里，并更新内存，这个时候已经完成更新（内存上），实际磁盘上的数据尚未更新。等适当的时候（通常是系统空闲的时候），InnoDB 引擎会将这个操作记录（redo log 中记录的更新语句）更新到磁盘。

写 redo log 的流程如下：

1. 先将目标原始数据从磁盘中读入内存中来，修改数据的内存拷贝。
2. 生成一条重做日志并写入 Redo Log Buffer，记录的是数据被修改后的值。
3. 当事务 commit 时，将 Redo Log Buffer 中的内容采用追加写的方式刷新到 Redo Log File。
4. 定期将内存中修改的数据刷新到数据文件（磁盘）中。

这样做还有一个问题，InnoDB 的 redo log 日志的大小是固定的，它设计的是循环的，即日志文件写满后会覆盖掉最先的记录（从头开始写，写到末尾就又回到开头循环写）。

• write pos：当前记录的位置，一边写一边后移，当写到第 3 号文件末（末尾）时会回到 0 号文件（开头）开头。
• checkpoint：当前要擦除的位置，同样是往后推移并且循环的，擦除记录前要把记录更新到数据文件（更新到磁盘里）。
• write pos 和 checkpoint 之间：redo log 日志文件还空着的部分，可以用来记录新的操作。
  • 如果 write pos 追上 checkpoint，表示 redo log 日志文件写满了，此时不能再执行新的更新操作，会将记录写入数据文件，并执行擦除记录，推进 checkpoint 位置。

试想：对于已经写入 redo log 的记录，在数据库异常重启后，能否恢复？

mysql 重启后，已经写入 redo log 的记录不会丢失，这个能力也称为 crash-safe。

crash-safe 还有个重要的日志——Binlog 日志。

Binlog 归档日志

MySQL 架构分为 Server 层和存储引擎层，redo log 是存储引擎层产生的日志，而 server 层也有日志——Binlog 归档日志。

两者的区别在于以下几点：

• Redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层产生的，任何引擎都存在该日志。
• Redo log 是循环写的，空间固定会用完，用完即从头开始写。binlog 是追加写，即 binlog 文件写到一定大小后会新建日志文件，不会覆盖掉以前的日志。
• redo log 会不断记录，而 binlog 只有在事务提交的时候才记录。Redo log 是物理日志，详细记录了“在某个数据页上做了什么修改”（包含事务的过程操作）；binlog 是逻辑日志，记录的是语句的原始逻辑（对数据最终的影响）。

譬如：一个事务对表做10万行的记录插入，在事务执行过程中，会一直不断的往 Redo Log 顺序写，而这个过程 Binlog 不会记录，直至这个事务提交的时候，才会写入到 Binlog 文件中。

这两份日志存在的意义就是实现 crash-safe 能力。

这两个日志文件结合起来，才真正实现了 crash-safe 能力，让 MySQL 既能保证事务的 ACID 属性，又能支持高效的数据复制和恢复能力。

redo log 和 binlog 设置

查看 redo log 和 binlog 设置

show variables like 'innodb_flush_log_at_trx_commit';
show variables like 'sync_binlog'

• innodb_flush_log_at_trx_commit：
  • 设置成 0，在提交事务时，InnoDB 不会立即触发将缓存日志写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统 fsync 刷新 IO 缓存。
  • 设置成 1 ，每次事务的 redo log 都直接持久化到磁盘，如此可以保证 MySQL 在异常重启后数据不会丢失。
  • 设置成 2，在每个事务提交时，InnoDB 立即将缓存中的 redo 日志回写到日志文件，但并不马上调用 fsync 来刷新 IO 缓存，而是每秒只做一次磁盘 IO 缓存刷新操作。
• sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。对于需要高度数据持久性和不能承受数据丢失的系统，建议将 sync_binlog设置为 1。

设置 redo log 和 binlog 配置

可以在 MySQL 配置文件（通常是 my. Cnf 或 my. Ini）中设置这个变量。设置好后需要重启 mysl，使得配置生效。

[mysqld]
sync_binlog=1
innodb_flush_log_at_trx_commit = 1

或者，也可以在 MySQL 运行时动态设置，但是这种变更只对新的会话有效，对于已经存在的会话，该设置直到会话结束才会生效。

SET GLOBAL sync_binlog=1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;