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

发表于 分类于 阅读次数： 阅读次数： Changyan： Disqus： 本文字数： 4.5k 阅读时长 ≈ 4 分钟

我们平时经常使用 MySQL，那么一条 MySQL 的语句从输入到返回结果，到底经历了哪些过程呢？下面我们一起来了解一下

MySQL 逻辑架构

首先，我们一起来看一下 MySQL 的逻辑架构：

我们可以看出，MySQL 可以分为 Server 层和存储引擎层两大模块。

Server 层包括连接器、查询缓存、分析器、优化器、执⾏器等，MySQL 的⼤多数核⼼服务功能，所有内置函数，以及跨存储引擎的功能都在这⼀层实现。

存储引擎层负责数据的读写。其架构模式是插件式的，⽀持 InnoDB、MyISAM、Memory 等多个存储引擎。我们最常⽤的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。可以使用 engine=引擎名 的方式来指定使用的引擎。

查询过程

连接器

首先我们需要通过连接器连接上数据库，连接器负责跟客户端建⽴连接、获取权限、维持和管理连接，其命令如下：

1
mysql -h$ip -P$port -u$user -p

输入密码完成连接之后，在本次连接中的权限逻辑判断都会依赖此时读到的权限 (⼀个⽤户成功建⽴连接后，即使⽤管理员账号对这个⽤户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使⽤新的权限设置）

客户端如果长时间没有运行指令，连接器会自动断开，自动断开时间由 wait_timeout 参数控制，默认 8 小时。

建立连接比较耗时，所以通常会使用长连接，但是如果长时间不断开连接的话，由于 MySQL 执行过程中的内存是管理在连接中的，只有连接断开时才会释放，所以长时间积累长连接的话，可能会导致 OOM，所以一般我们需要定期断开长连接，或执行 mysql_reset_connection 来初始化连接资源 (5.7 之后）。

查询缓存

建立连接之后，就可以开始执行语句了，以如下查询语句为例：

1
mysql> select * from T where ID=10；

MySQL 获取到此命令后，会先去查询缓存，看这条语句是否执行过，如果是则直接返回结果。但是一般我们不要开启查询缓存，因为只要有一个对表的更新，这个表上的所有查询缓存都会清空，所以查询缓存的命中率很低，并且 MySQL 8.0 之后的版本移除了这个功能，说明他们确实也觉得这个功能没有必要。

分析器

如果没有命中缓存，就要开始解析 SQL 语句了。

首先，分析器会做词法分析，把输入的字符串解析成一个一个有意义的关键字，然后对整个语句做语法分析，根据语法规则对词法分析的结果进行校验，如果语句不对，就会抛出You hanve an error in your SQL syntax的错误。

分析器处理语法和解析查询之后会生成一棵解析树，进⼀步检查解析树的合法性。⽐如：数据表和数据列是否存在，别名是否有歧义等。如果通过则⽣成新的解析树，再提交给优化器。

优化器

优化器主要是将 SQL 经过语法解析/词法解析后得到的语法树，通过 MySQL 的数据字典和统计信息的内容，经过一系列运算，从而得出一个执行计划树的构成。比如在表⾥⾯有多个索引的时候，决定使⽤哪个索引；或者在⼀个语句有多表关联（join）的时候，决定各个表的连接顺序等。

优化器的输入是一棵语法树，输出是一棵执行树（也称为执行计划）。

执行器

开始执⾏的时候，要先判断⼀下你对这个表 T 有没有执⾏查询的权限，如果没有，就会返回没有权限的错误；如果有权限，就打开表继续执⾏。打开表的时候，执⾏器就会根据表的引擎定义，去使⽤这个引擎提供的接⼝。

其执行过程如下：

  1. 调⽤InnoDB 引擎接⼝取这个表的第⼀⾏（如果有索引则取满足条件的第一行），判断 ID 值是不是 10，如果不是则跳过，如果是则将这⾏存在结果集中；
  2. 调⽤引擎接⼝取“下⼀⾏”，重复相同的判断逻辑，直到取到这个表的最后⼀⾏。
  3. 执⾏器将上述遍历过程中所有满⾜条件的⾏组成的记录集作为结果集返回给客户端。

更新过程

以下面这条更新语句为例：

1
mysql> update T set c=c+1 where ID=2;

我们要更新一个字段，一定需要先查出来这个字段，所以查询的流程一定也会走一遍，然后还会涉及三个重要的日志文件：Undo Log（回滚日志），Redo Log（重做日志），和 Binlog（归档日志）

Undo Log

undo log 主要记录了数据的逻辑变化，比如一条修改 +3 的逻辑语句，undo log 会记录对应一条 -3 的逻辑日记，一条插入语句则会记录一条删除语句，这样发生错误时，根据执行 undo log 就可以回滚到事务之前的数据状态。

undo log 就像你刚刚在 Git 中 Commit 了一下，然后再做一个较为复杂的改动，但是改着改着你的心态崩了，不想要刚刚的改动了，于是直接 git reset –hard $lastCommitId 回到了上一个版本。

Redo Log

redo log 是 InnoDB 独有的日志，包括两部分：一个是内存中的日志缓冲 (redo log buffer)，另一个是磁盘上的日志文件 (redo log file)。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种先写日志，再写磁盘的技术就是 MySQL 里经常说到的 WAL(Write-Ahead Logging) 技术。

InnoDB 的 redo log 是固定⼤⼩的，⽐如可以配置为⼀组 4 个⽂件，每个⽂件的⼤⼩是 1GB，那么总共就可以记录 4GB 的操作。实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志，如下图所示：

write pos 是当前记录的位置，⼀边写⼀边后移，写到第 4 号⽂件末尾后就回到 1 号⽂件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据⽂件。write pos 和 checkpoint 之间的是缓存中还空着的部分，可以⽤来记录新的操作。如果 write pos 追上 checkpoint，表示缓存满了，这时候不能再执⾏新的更新，得停下来先擦掉⼀些记录，把 checkpoint 推进⼀下。

有了 redo log，InnoDB 就可以保证即使数据库发⽣异常重启，之前提交的记录都不会丢失，这个能⼒称为 crash-safe。

redo log 就像你在命令行敲了很长的命令，敲回车执行，结果报错了。此时我们只需要再敲个↑就会拿到上一条命令，再执行一遍即可。

Binlog

binlog 用于记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中。binlog 是 mysql 的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。可以简单理解为记录的就是 sql 语句。

在实际应用中，binlog 的主要使用场景有两个，分别是主从复制和数据恢复。

  1. 主从复制：在 Master 端开启 binlog，然后将 binlog 发送到各个 Slave 端，Slave 端重放 binlog 从而达到主从数据一致。
  2. 数据恢复：通过使用 mysqlbinlog 工具来恢复数据。

Redo Log 与 Binlog 区别

redo log binlog
文件大小 redo log 的大小是固定的。 binlog 可通过配置参数 max_binlog_size 设置每个 binlog 文件的大小。
实现方式 redo log 是 InnoDB 引擎层实现的，并不是所有引擎都有。 binlog 是 Server 层实现的，所有引擎都可以使用 binlog
日志记录方式 redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。 binlog 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上
日志记录内容 redo log 是物理⽇志，记录的是“在某个数据⻚上做了什么修改”。 binlog 是逻辑⽇志，记录的是这个语句的原始逻辑，⽐ 如“给 ID=2 这⼀⾏的 c 字段加 1”。
适用场景 redo log 适用于崩溃恢复 (crash-safe) binlog 适用于主从复制和数据恢复

由 binlog 和 redo log 的区别可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。但只有 redo log 也不行，因为 redo log 是 InnoDB 特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog 和 redo log 二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

那么为什么 redo log 具有 crash-safe 的能力，而 binlog 没有？

举个栗子，binlog 记录了两条日志：

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

在记录 1 刷盘后，记录 2 未刷盘时，数据库 crash。重启后，只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘，哪条没有写入磁盘，不管是两条都恢复至内存，还是都不恢复，对 ID=2 这行数据来说，都不对。

但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。这就是为什么 redo log 具有 crash-safe 的能力，而 binlog 不具备。

Update 执行流程

  1. 执⾏器先找引擎取 ID=2 这⼀⾏。ID 是主键，引擎直接⽤树搜索找到这⼀⾏。如果 ID=2 这⼀⾏所在的数据⻚本来就在内存中，就直接返回给执⾏器；
  2. 如果数据页不在内存中，需要先从磁盘读⼊内存，然后再返回。
  3. 生成修改的反向语句，写入 undo log。
  4. 执⾏器拿到引擎给的⾏数据，把这个值加上 1，⽐如原来是 N，现在就是 N+1，得到新的⼀⾏数据，再调⽤引擎接⼝写⼊这⾏新数据。
  5. 执行器将修改写入 redo log buffer 中。
  6. 引擎将这⾏新数据更新到内存中，同时将这个更新操作记录到 redo log ⾥⾯，此时 redo log 处于 prepare 状态。然后告知执⾏器执⾏完成了，随时可以提交事务。
  7. 执⾏器⽣成这个操作的 binlog，并把 binlog 写⼊磁盘。
  8. 执⾏器调⽤引擎的提交事务接⼝，引擎把刚刚写⼊的 redo log 改成提交（commit）状态，更新完成。

其流程图如下：

从第 6 到第 8 步可以看出，redo log 的写⼊有两个步骤：prepare 和 commit，这就是 2PC（两阶段提交）。之所以要有两阶段提交，是为了让两份⽇志之间的逻辑能够保持⼀致，那么为什么两阶段提交就可以保证逻辑一致呢？

  1. 假设 redo log 刷入成功了，但是还没来得及刷入 binlog MySQL 就挂了。此时重启之后会发现 redo log 并没有 Commit 标识，此时会进行回滚。

  2. 假设 redo log 刷入成功，而且 binlog 也刷入成功了，但是还没有来得及将 redo log 从 prepare 改成 commit MySQL 就挂了，此时重启会发现虽然 redo log 没有 commit 标识，但是查询到的 binlog 却已经成功刷入磁盘了，此时会自动提交这个事务，并将 redo log 状态改为 commit。

  3. 假设两者都写入成功并且 redo log 也是 commit 状态，万事大吉。

总结

本篇中，我们了解了 MySQL 的逻辑架构，查询和更新的流程，知道了连接器，查询缓存，分析器，优化器以及执行器的作用和工作流程，知道了 MySQL 三种重要的日志：undo log，redo log 和 binlog，同时知道了两阶段提交及其意义。