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

1. 一条 SQL 查询语句的执行流程

在之前的文章 【MYSQL】MYSQL 的学习教程（一） 之 MYSQL 简介 & 架构 & 存储引擎 第三章节《MYSQL 架构》中，已经涉及过，流程如下：

一条查询 SQL 语句的执行流程：

1. 客户端通过连接器连接 MySQL 服务
2. 连接成功后向 SQL 接口发送 SQL 语句请求
3. SQL 接口接收到 SQL 查询语句后，会先去缓存查询【MYSQL 8.0 已删除】，如果命中返回给客户端，否则交给解析器
4. 解析器在拿到 SQL 语句后会判断语法是否正确，正确会生成 SQL 语法树交给优化器，否则报错给客户端
5. 预处理器进一步检查 SQL 语句中的表名、列名是否存在，把 select * 替换为具体的列，以及用户是否有相应的访问权限
6. 优化器会根据 SQL 语法树生成一个最优的执行计划交给执行器执行
7. 执行器拿到执行计划调用存储引擎来获取数据响应给客户端

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 中，如下图：

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

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

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

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

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

这样做的目的是什么？

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

到这一步，我们的执行的 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 数据写入磁盘的策略是立即刷入磁盘

如果 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 的记录做了更新操作”

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

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

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 标记，这样就表示本次事务被成功的提交了。

如果在数据被写入到 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 标记

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