likes
comments
collection
share

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

作者站长头像
站长
· 阅读数 10

WAL

  • 在 MySQL 中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。
  • 为了解决这个问题，MySQL 引入 WAL （Write-Ahead Logging）技术。它的关键点在于 先写日志，再写磁盘。而这就涉及到 MySQL 的两大日志模块：rede log 和 binlog
  • 当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做的。

redo log

  • InnoDB的 redo log 是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示

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

  • write pos 是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头
  • checkpoints 是当前已经写入数据文件的位置，当 redo log 上面的记录被写入数据文件后，checkpoints 就可以往后移
  • write pos 和 checkpoint 之间的是 redo log 上还空着的部分，可以用来记录新的操作
  • 如果 write pos 追上 checkpoint ，表示 redo log 被写满了，这时候不能再执行新的更新，得停下来先将一些记录写入数据文件，把 checkpoint 推进一下。
  • 有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
  • redo log是物理日志，记录的是“在某个数据页上做了什么修改”
  • redo log是循环写的，空间固定会用完，会覆盖以前的数据。

binlog

如果你了解 MySQL 的基础架构的话（参考01 | 基础架构：一条SQL查询语句是如何执行的？-极客时间 (geekbang.org)），你会知道 MySQL 其实分为 Server 层 和 引擎层，Server 层主要做的是MySQL功能层面的事情，引擎层主要负责存储相关的具体事宜。上面我们说到的 redo log 其实是 InnoDB 特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。下面是 binlog 的一些特性：

  • binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
  • binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

为什么需要 redo log

为了实现上面提到的 crash-safe 的能力， InnoDB 引擎引入了 redo log 。下面我们来解释一下为什么单独一个 binlog 不能实现 crash-safe 。

  • 通过前面对 redo log 和 binlog 的介绍，我们知道它们之间有一个很大的区别就是，一个是循环写，一个是追加写。

    • 也就是说 redo log 只会记录写入磁盘中数据的日志，已经刷入磁盘的数据都会因为 write pos 向后移动而被覆盖。
    • 但 binlog 存储的是全量的数据。

  • 当数据库 crash 后，假设此时我们只有 binlog，那么会出现这样一个问题：我们无法判断哪些数据已经落盘，哪些数据未落盘。举个例子，

    • binlog 记录了两条日志：

      1. 给 ID=1 这一行的 a 字段加 1
      2. 给 ID=1 这一行的 a 字段加 1

    • 在记录1刷盘后，记录2未刷盘时，数据库 crash。重启后，如果只有 binlog 的存在，不管是执行 1 和 2，或者都不执行，最后的数据都不是正确的数据

  • 但如果引入了 redo log，当 “给 ID=1 这一行的 a 字段加 1” 这一操作已经落盘之后，write pos 会向后移动，这时，数据库 crush ，我们就只需要找到 write pos 后面的日志来恢复数据库，就可以保证数据的正确了！

两阶段提交

在了解了 MySQL 两个日志模块之后，我们来看一下 update t set a = a + 1 where id = 1 这条SQL 语句在 MySQL(InnoDB引擎)中的执行流程是什么样子的。

  1. 执行器调用引擎提供的接口，获取 id = 1 这条记录。如果记录存在内存中，会直接返回，否则需要到内存中读取数据。
  2. 执行器得到引擎层返回的数据后，执行 a = a + 1。
  3. 执行器调用引擎层进行写入数据
  4. 引擎将数据更新到内存。
  5. 将日志写入 redo log，此时 redo log 处于 prepare 阶段。
  6. 将数据写入 binlog。
  7. 提交事务，将 redo log 变更为 commit 阶段。

如图所示，

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

在这里，我们暂时忽略其他细节（数据什么时候刷盘，写入 redo log 是指写入内存还是直接刷盘）着重介绍一下后面三个步骤（5、6、7）

我们注意到，redo log 的写入被拆分成两个步骤：prepare 和 commit 阶段。这就是“两阶段提交”。为什么要设计成两阶段提交呢？要回答这个问题，我们首先要来看一下如何使用 binlog 进行数据库的恢复。

当我们需要恢复到指定的某一个时刻的数据库状态时，可以进行如下操作

  1. 找到这个时刻之间的最后一次数据库备份，把这个备份恢复。
  2. 从备份的时间点开始，将备份的 binlog 依次取出，一直执行到想要恢复的那个时间点。

前面我们提到，一条语句更新内存并把日志写到 redo log 之后，这条语句就算执行完成了，但我们进行数据库恢复的时候，用的却是 binlog 日志，这就要求 binlog 日志和 redo log 日志记录的数据必须严格一致，这也就是“两阶段提交”存在的意义。

现在我们来看一下如果不使用两阶段提交，会发生什么事情？

  1. 先写 redo log ，再写 binlog。

    1. 当 redo log 写完之后，还没来得及写 binlog 数据库就异常重启。
    2. 前面我们提到 redo log 能够实现 crash-safe 。因此，此时我们可以通过 redo log 把刚刚执行一半的语句恢复。
    3. 但是由于此时 redo log 已经写完了，事务完成了，那么 binlog 里面并没有这条语句，而且事务也不会继续执行将语句写入 binlog。
    4. 那么如果需要用这个 binlog 来恢复临时库的话（或者缓存数据库订阅 binlog 解决数据一致性问题），**由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，**恢复出来的数据与原库的值不同。
    5. 但此时这个更改已经记录到 binlog 中，

  2. 先写 binlog ，再写 redo log

    1. 当 binlog 写完之后，还没来得及写 redo log数据库就异常重启。
    2. 前面我们提到单凭 binlog 不能实现 crash-safe 。因此，此时我们可以无法通过 binlog 把刚刚执行一半的语句恢复。而 redo log 里面没有记录这个更改，那么也就是说事务无效，线上数据库没有这个更改。
    3. 但此时这个更改已经记录到 binlog 中，那么如果需要用这个 binlog 来恢复临时库的话（或者缓存数据库订阅 binlog 解决数据一致性问题），**由于这个语句的 binlog 已经被记录，这个临时库就会多了这一次更新，**恢复出来的数据与原库的值不同。

那么在两阶段提交的场景下，MySQL如果发生异常重启，是怎么保证数据完整性的？

我们回看上面那张图

  1. 当数据库在 5 和 6 之间 crash （已经写入 redo log 处于 prepare 阶段，没来得及写入 binlig）

    1. 由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库

  2. 当数据库在 6 和 7 之间 crash （已经写入 binlog，redo log 还没有 commit）,此时会有两种情况

    1. 事务 binlog 存在并完整，提交事务；
    2. 事务 binlog 不存在或者不完整，回滚事务。

其他问题

数据到底什么时候落盘？

  1. 如果是正常运行的实例的话，数据页被修改以后（对应前面的步骤 4 ），跟磁盘的数据页不一致，称为脏页。MySQL会定期触发检查点（Checkpoint）操作，将脏页写入磁盘，以保持内存中的数据与磁盘上的数据的一致性。这个过程与日志系统没有关系。
  2. 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

只要一个日志行不行？

不要 redo log 会造成的问题我们在上面已经聊过了，这会造成数据库失去 crash-safe 的能力。那么，能不能不要 binlog 呢？

  • 从 crash-safe 这方面来说是可以的，失去 binlog 数据库依然拥有 crash-sefe 的能力。

  • 但是 binlog 有着redo log无法替代的功能：

    • redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。也就是我们提到的恢复到指定的某一个时刻的数据库状态的能力也会失去
    • MySQL系统高可用的基础，就是binlog复制。还有通过订阅 binlog 的方式保持缓存数据库和数据库一致性等依赖于 binlog 的功能都会丢失。

总结

在上文中我们介绍了MySQL数据库中的日志系统，包括redo log和 binlog 的区别，以及为什么需要redo log 来保证数据库的 crash-safe 能力。同时，分析了MySQL中update语句的执行流程，介绍了两阶段提交的设计理念。最后说明了一些常见的问题，例如数据什么时候落盘、是否可以只使用redo log 等。

参考资料