【MYSQL】MYSQL 的学习教程（六）之 SQL 语句执行流程

Love The Way You Lie 2024-02-05 17:59 16阅读 0赞

## 1. 一条 SQL 查询语句是如何被执行的 ##

MySQL 的基本架构示意图如下所示：

![在这里插入图片描述][91849a9ffa92415c853b228ff3941e44.png]

MYSQL 线程处理请求流程：

1.  SQL 接口：MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理
2.  查询解析器：解析器会将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言
    
     *  词法分析：一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入下一步
     *  语法分析：主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法
3.  查询优化器：MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面：IO 成本和 CPU 成本
    
     *  IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关
     *  CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。

> MySQL 优化器 会计算 「IO 成本 + CPU」 成本最小的那个索引来执行

1.  执行器：执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行。执行器最终最根据一系列的执行计划去调用存储引擎的接口去完成 SQL 的执行
2.  存储引擎：真正执行 SQL 的动作是在存储引擎中完成的。数据是被存放在内存或者是磁盘中的

## 2. 一条 SQL 更新语句是如何执行的 ##

查询语句的那一套流程，更新语句也是同样会走一遍，与查询流程不一样的是，更新语句涉及到事务，就必须保证事务的四大特性：ACID，所以更新流程涉及到两个重要的日志模板：redo log（重做日志）和 binlog（归档日志）

以一个更新的 SQL 语句来说明，SQL 如下：

UPDATE students SET stuName = '小强' WHERE id = 1

在执行这个 SQL 的时候，SQL 语句对应的数据要么是在内存中，要么是在磁盘中，如果直接在磁盘中操作，那这样的随机 IO 读写的速度肯定让人无法接受的。所以，每次在执行 SQL 的时候都会将其数据加载到内存中，这块内存就是 InnoDB 中一个非常重要的组件：**缓冲池 Buffer Pool**

### 2.1 Buffer Pool ###

> Buffer Pool 是 MySQL 用于缓存数据页的内存区域，而查询缓存是 MySQL 的一个功能，用于缓存查询结果。两者是不同的概念和功能

Buffer Pool （缓冲池）是 InnoDB 存储引擎中非常重要的内存结构，顾名思义，缓冲池其实就是类似 Redis 一样的作用，起到一个缓存的作用，因为我们都知道 MySQL 的数据最终是存储在磁盘中的，如果没有这个 Buffer Pool 那么我们每次的数据库请求都会磁盘中查找，这样必然会存在 IO 操作，这肯定是无法接受的。但是有了 Buffer Pool 就是我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中，如下图：

![在这里插入图片描述][f533903ed0484f16815eb82204292b64.png]

按照上面的那幅图，这条 SQL 语句的执行步骤大致是这样子的：

1.  innodb 存储引擎会在缓冲池中查找 id=1 的这条数据是否存在
2.  发现不存在，那么就会去磁盘中加载，并将其存放在缓冲池中
3.  该条记录会被加上一个独占锁

### 2.2 undo 日志文件：记录数据被修改前的样子 ###

`undo log` 就是没有发生事情的一些日志

在准备更新一条语句的时候，该条语句已经被加载到 Buffer pool 中了，实际上这里还有这样的操作，就是在将该条语句加载到 Buffer Pool 中的时候，同时会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。

> 这样做的目的是什么？

Innodb 存储引擎的最大特点就是支持事务，如果本次更新失败，也就是事务提交失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响。看图说话：

![在这里插入图片描述][2829a01869e0410d95ec09a088261569.png]

到这一步，我们的执行的 SQL 语句已经被加载到 Buffer Pool 中了，然后开始更新这条语句，更新的操作实际是在 Buffer Pool 中执行的。

> 那问题来了，按照我们平时开发的一套理论缓冲池中的数据和数据库中的数据不一致时候，我们就认为缓存中的数据是脏数据

那此时 Buffer Pool 中的数据岂不是成了脏数据？没错，目前这条数据就是脏数据，Buffer Pool 中的记录是`小强`，数据库中的记录是`旺财`。这种情况 MySQL是怎么处理的呢

### 2.3 redo 日志文件（InnoDB 特有）：记录数据被修改后的样子 ###

除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。

如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo 日志文件就需要来大显神通了

`redo` 记录的是数据修改之后的值，不管事务是否提交都会记录下来

例如，此时将要做的是 `update students set stuName='小强' where id=1;` 那么这条操作就会被记录到 redo log buffer 中。MySQL 为了提高效率，所以将这些操作都先放在内存中去完成，然后会在某个时机将其持久化到磁盘中

此时，如果 MySQL 真的宕机了，那么缓存中的数据还是丢失了，那么没关系的，因为 MySQL 会认为本次事务是失败的，所以数据依旧是更新前的样子，并不会有任何的影响。

语句也更新好了那么需要将更新的值提交啊，也就是需要提交本次的事务了，因为只要事务成功提交了，才会将最后的变更保存到数据库，在提交事务前仍然会具有相关的其他操作：将 redo Log Buffer 中的数据持久化到磁盘中，就是将 redo log buffer 中的数据写入到 redo log 磁盘文件中（WAL：事务提交前先写日志，再修改页）。

一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘

![在这里插入图片描述][9c950c7e5758432285927bf3c2169ad9.png]

如果 redo log Buffer 刷入磁盘后，数据库服务器宕机了，那我们更新的数据怎么办？

因为 redo log buffer 中的数据已经被写入到磁盘了，已经被持久化了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 redo 日志文件内容恢复到 Buffer Pool 中，所以，数据就不会丢失

从执行器开始调用存储引擎接口做了哪些事情呢？

1.  准备更新一条 SQL 语句
2.  MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）
3.  在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
4.  innodb 会在 Buffer Pool 中执行更新操作
5.  更新后的数据会记录在 redo log buffer 中
6.  MySQL 提交事务的时候，会将 redo log buffer 中的数据写入到 redo 日志文件中，刷磁盘可以通过 innodb\_flush\_log\_at\_trx\_commit 参数来设置
    
     *  0 表示不刷入磁盘
     *  1 表示立即刷入磁盘
     *  2 表示先刷到 os cache
7.  MYSQL 重启的时候会将 redo 日志恢复到缓冲池中

## 2.4 bin log 日志文件（MYSQL 级别）：记录整个操作过程 ##

上面介绍到的 redo log 是 InnoDB 存储引擎特有的日志文件，而 bin log 属于是 MySQL 级别的日志。redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log 是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录做了更新操作”

两者的主要特点总结如下:

<table> 
 <thead> 
  <tr> 
   <th>性质</th> 
   <th>redo Log</th> 
   <th>bin Log</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>文件大小</td> 
   <td>redo log 的大小是固定的（配置中也可以设置，一般默认的就足够了）</td> 
   <td>bin log 可通过配置参数max_bin log_size设置每个bin log文件的大小（但是一般不建议修改）</td> 
  </tr> 
  <tr> 
   <td>实现方式</td> 
   <td>redo log是InnoDB引擎层实现的（也就是说是 Innodb 存储引起过独有的）</td> 
   <td>bin log是 MySQL 层实现的，所有引擎都可以使用 bin log日志</td> 
  </tr> 
  <tr> 
   <td>记录方式</td> 
   <td>redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志</td> 
   <td>bin log 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上</td> 
  </tr> 
  <tr> 
   <td>使用场景</td> 
   <td>redo log适用于崩溃恢复(crash-safe)（这一点其实非常类似与 Redis 的持久化特征）</td> 
   <td>bin log 适用于主从复制和数据恢复</td> 
  </tr> 
 </tbody> 
</table>

> bin log 文件是如何刷入磁盘的?

bin log 的刷盘是有相关的策略的，策略可以通过sync\_bin log来修改，默认为 0，先写入 os cache。即：在提交事务的时候，数据不会直接到磁盘中，这样如果宕机 `bin log` 数据仍然会丢失

所以建议将 sync\_bin log 设置为 1 表示直接将数据写入到磁盘文件中。

刷入 bin log 有以下几种模式：

1.  STATMENT：基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 语句会记录到 bin log 中
    
     *  【优点】：不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能
     *  【缺点】：在某些情况下会导致主从数据不一致，比如执行 sysdate()、slepp() 等
2.  ROW：基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了
    
     *  【优点】：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题
     *  【缺点】：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨
3.  MIXED：基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log

> 那既然 bin log 也是日志文件，那它是在什么记录数据的呢？

其实 MySQL 在提交事务的时候，不仅仅会将 redo log buffer 中的数据写入到 redo log 文件中，同时也会将本次修改的数据记录到 bin log文件中，同时会将本次修改的 bin log 文件名和修改的内容在 bin log 中的位置记录到 redo log 中，最后还会在 redo log 最后写入 commit 标记，这样就表示本次事务被成功的提交了。

![在这里插入图片描述][6ea3fbc145a44f27a455f75b0f24bafb.png]

> 如果在数据被写入到 bin log 文件的时候，刚写完，数据库宕机了，数据会丢失吗？

首先可以确定的是，只要redo log最后没有 commit 标记，说明本次的事务一定是失败的。但是数据是没有丢失了，因为已经被记录到redo log的磁盘文件中了。在 MySQL 重启的时候，就会将 redo log 中的数据恢复（加载）到 Buffer Pool 中

好了，到目前为止，一个更新操作基本介绍得差不多，但是有没有感觉少了哪件事情还没有做？

> 这个时候被更新记录仅仅是在内存中执行的，哪怕是宕机又恢复了也仅仅是将更新后的记录加载到 Buffer Pool 中，这个时候 MySQL 数据库中的这条记录依旧是旧值，也就是说内存中的数据在我们看来依旧是脏数据，那这个时候怎么办呢？

其实 MySQL 会有一个后台线程，它会在某个时机将我们 Buffer Pool 中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了

## 3. 总结 ##

关于 Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念：

1.  Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
2.  Undo log 记录的是数据操作前的样子
3.  redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）
4.  bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）

从准备更新一条数据到事务的提交的流程描述：

1.  首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
2.  在数据被缓存到缓存池的同时，会写入 undo log 日志文件
3.  更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中
4.  完成以后就可以提交事务，在提交的同时会做以下三件事：
    
     *  （第一件事）将redo log buffer中的数据刷入到 redo log 文件中
     *  （第二件事）将本次操作记录写入到 bin log文件中
     *  （第三件事）将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记

至此表示整个更新事务已经完成

[91849a9ffa92415c853b228ff3941e44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/a7540b86be7e466c96eb60e6ae06e57e.png
[f533903ed0484f16815eb82204292b64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/86d78817cd254ad5b3b2dc83c0faf0bc.png
[2829a01869e0410d95ec09a088261569.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/f67db11623de44b7947d07f1094e2682.png
[9c950c7e5758432285927bf3c2169ad9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/36c1b168a6d4420c839e2a530e6ab9f1.png
[6ea3fbc145a44f27a455f75b0f24bafb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/50b7356ccd4d4cc7a9fa50f7cb97e001.png