数据库（八）--SQL执行过程详解

MySQL驱动

在系统和 MySQL 进行交互之前，MySQL驱动在底层帮我们做了对数据库的连接，我们只需要发送 SQL 语句就可以执行 CRUD 了，只有建立了连接，才能有后面的交互。

**一次 SQL 请求就会建立一个连接，多个请求就会建立多个连接。**系统中肯定不是一个人在使用的，换句话说肯定是存在多个请求同时去争抢连接的情况。我们的 web 系统一般都是部署在 tomcat 容器中的，而 tomcat 是可以并发处理多个请求的，这就会导致多个请求会去建立多个连接，然后使用完再都去关闭，如下图所示：

java 系统在通过 MySQL 驱动和 MySQL 数据库连接的时候是基于 TCP/IP 协议的，所以如果每个请求都是新建连接和销毁连接，那这样势必会造成不必要的浪费和性能的下降，也就说上面的多线程请求的时候频繁的创建和销毁连接显然是不合理的。必然会大大降低我们系统的性能，但是如果给你提供一些固定的用来连接的线程，就不需要反复的创建和销毁连接了，这就是数据库连接池。
数据库连接池：维护一定的连接数，方便系统获取连接，使用就去池子中获取，用完放回去就可以了，我们不需要关心连接的创建与销毁，也不需要关心线程池是怎么去维护这些连接的。

常见的数据库连接池有 Druid、C3P0、DBCP，采用连接池大大节省了不断创建与销毁线程的开销，这就是有名的池化思想，不管是线程池还是 HTTP 连接池，都能看到它的身影。

数据库连接池

系统在访问MySQL数据库的时候，建立的连接并不是每次请求都会去创建的，而是从数据库连接池中去获取，这样就解决了因为反复创建和销毁连接而带来的性能损害问题。
MySQL的架构体系中也提供了一个类似的池子，也是数据库连接池。双方都是通过数据库连接池来管理各个连接的，这样一方面线程之间不需要是争抢连接，更重要的是不需要反复的创建的销毁连接。

网络连接必须由线程来处理

网络中的连接都是由线程来处理的，所谓网络连接就是一次请求，每次请求都会有相应的线程去处理的。对于 SQL 语句的请求在 MySQL 中是由一个个的线程去处理的。

SQL接口

MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理。

查询解析器

解析器会将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言。

现在SQL已经被解析成MySQL认识的样子了，在执行之前还会帮我们选择最优的查询路径（就是MySQL会按照自己认为的效率最高的方式去执行查询）。

查询优化器

MySQL 会帮我们去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面：IO 成本和 CPU 成本。

1. IO成本
   即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关。
2. CPU成本
   将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。

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

优化器执行选出最优索引等步骤后，会去调用存储引擎接口，开始去执行被 MySQL 解析过和优化过的 SQL 语句。

执行器

执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行。执行器最终最根据一系列的执行计划去调用存储引擎的接口去完成 SQL 的执行。

存储引擎

查询优化器会调用存储引擎的接口，去执行 SQL，也就是说正执行 SQL 的动作是在存储引擎中完成的。数据是被存放在内存或者是磁盘中的。

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

当系统发出这样的查询去交给 MySQL 的时候，MySQL 会按照我们上面介绍的一系列的流程最终通过执行器调用存储引擎去执行，在执行这个 SQL 的时候 SQL 语句对应的数据要么是在内存中，要么是在磁盘中，如果直接在磁盘中操作，那这样的随机IO读写的速度肯定让人无法接受的，所以每次在执行 SQL 的时候都会将其数据加载到内存中，这块内存就是 InnoDB 中一个非常重要的组件：缓冲池 Buffer 。

Buffer Pool

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

SQL语句的执行步骤大致是这样子的：

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

undo日志文件

该文件用于记录数据被修改前的样子。
在准备更新一条语句的时候，该条语句已经被加载到 Buffer pool 中了，实际上这里还有这样的操作，就是在将该条语句加载到 Buffer Pool 中的时候同时会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。
Innodb 存储引擎的最大特点就是支持事务，如果本次更新失败，也就是事务提交失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响。

执行到这一步，要执行的 SQL 语句已经被加载到 Buffer Pool 中了，然后开始更新这条语句，更新的操作实际是在Buffer Pool中执行的，那问题来了，按照我们平时开发的一套理论缓冲池中的数据和数据库中的数据不一致时候，我们就认为缓存中的数据是脏数据，那此时 Buffer Pool 中的数据岂不是成了脏数据？没错，目前这条数据就是脏数据，Buffer Pool 中的记录是小强 数据库中的记录是旺财 ，这种情况 MySQL是怎么处理的呢，接着往下看。

redo文件

记录数据被修改后的样子。
除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo 日志文件就起作用了。
redo 日志文件是 InnoDB 特有的，他是存储引擎级别的，不是 MySQL 级别的。
redo 记录的是数据修改之后的值，不管事务是否提交都会记录下来。

MySQL 的执行器调用存储引擎将一条 SQL 加载到缓冲池并记录到日志中的流程如下：

1. 准备更新一条 SQL 语句
2. MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
3. 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
4. innodb 会在 Buffer Pool 中执行更新操作
5. 更新后的数据会记录在 redo log buffer 中

语句更新好了就需要将更新的值提交，也就是需要提交本次的事务了，因为只要事务成功提交了，才会将最后的变更保存到数据库，在提交事务前仍然会具有相关的其他操作。
将 redo Log Buffer 中的数据持久化到磁盘中，就是将 redo log buffer 中的数据写入到 redo log 磁盘文件中，一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘。

如果 redo log Buffer 刷入磁盘后，数据库服务器宕机了，那我们更新的数据怎么办？此时数据是在内存中，数据岂不是丢失了？不，这次数据就不会丢失了，因为 redo log buffer 中的数据已经被写入到磁盘了，已经被持久化了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 redo 日志文件内容恢复到 Buffer Pool 中。

到此为止，从执行器开始调用存储引擎接口做了哪些事情？

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

bin log日志文件

MySQL级别的日志文件，用于记录整个操作过程。
redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录做了更新操作” 。两者的主要特点总结如下:

bin log文件是如何刷入磁盘的？
bin log 的刷盘是有相关的策略的，策略可以通过sync_bin log来修改，默认为 0，表示先写入 os cache，也就是说在提交事务的时候，数据不会直接到磁盘中，这样如果宕机bin log数据仍然会丢失。所以建议将sync_bin log设置为 1 表示直接将数据写入到磁盘文件中。
刷入bin log有以下几种模式：

• STATMENT
  基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 语句会记录到 bin log 中。
  优点：不需要记录每一行的变化，减少了bin log日志量，节约了IO，从而提高了性能。
  缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。

• ROW
  基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了。
  优点：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题。
  缺点：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨。

• MIXED
  基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 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 数据库中，这样就将内存和数据库的数据保持统一了。

总结

Buffer Pool、Redo Log Buffer、undo log、redo log、bin log ：

• Buffer Pool： MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的。
• undo log：记录数据操作之前的样子。
• redo log：记录数据被操作后的样子，是InnoDB存储引擎特有的。
• bin log：记录的是整个操作记录（对于主从复制非常有意义）

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

• 首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
• 在数据被缓存到缓存池的同时，会写入 undo log 日志文件
• 更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中
• 完成之后就可以提交事务了，在提交的同时会做以下三件事：
  （1）将redo log buffer中的数据刷入到 redo log 文件中
  （2）将本次操作记录写入到bin log文件中
  （3）将bin log文件名字和更新内容在bin log中的位置记录到 redo log 中，同时在 redo log 最后添加commit标记。
• 至此整个更新事务已经完成。
  感谢并参考：
  https://pdai.tech/md/db/sql-mysql/sql-mysql-execute.html