SQL执行原理

SELECT 语句执行原理

连接器

当我们通过客户端访问MySQL服务器前，要做的第一步就是需要先经过TCP三次握手，因为 MySQL 是基于TCP协议进行传输的。TCP 网络连接建立成功后，服务端与客户端之间会建立一个session会话，紧接着会对登录的用户名和密码进行效验，首先会查询自身的用户表信息，判断输入的用户名是否存在，如果存在则会判断输入的密码是否正确。密码正确后，会从连接池中分配一条空闲线程维护当前客户端的连接，如果没有空闲线程，则会创建一条新的工作线程。之后线程会查询用户所拥有的权限，并对其授权，后续SQL执行时，都会先判断是否具备相应的权限。

空闲连接在超过最大空闲时长（wait_timeout）之后，连接器会自动将它断开。

一个处于空闲状态的连接被服务端主动断开后，客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到报错。

连接池

Connection Pool是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请→使用→释放。主要是为了复用线程、管理线程以及限制最大连接数。

当一个客户端尝试与 MySQL建立连接时，MySQL内部都会派发一条线程负责处理该客户端接下来的所有工作。线程的频繁创建和销毁都会耗费大量资源，通过复用线程的方式，不仅能减少开销，还能避免内存溢出等问题。

数据库Connection Pool可以设置最小连接数和最大连接数：

• 最小连接数：是连接池一直保持的数据库连接，如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费
• 最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中

查询缓存

如果是查询（select）语句MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句的哈希值，value 为 SQL 语句查询的结果。如果命中查询缓存，那么就会直接返回value给客户端。如果没有命中查询缓存，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。查询缓存往往弊大于利，因为只要有对表的更新，就会导致表上的所有查询缓存被清空。所以，MySQL8.0 版本直接将查询缓存删掉了。

💡 这里说的查询缓存是server层的，也就是MySQL8.0版本移除的是server层的查询缓存，并不是Innodb存储引擎中的Buffer Pool。

解析 SQL

在正式执行SQL查询语句之前，MySQL会先对SQL语句进行解析，这个工作交由解析器来完成。解析器可以将输入的 SQL 语句转换为计算机可以理解的形式（语法树，Syntax Tree）。

解析器会做以下两件事情：

• 词法分析：MySQL会根据输入的字符串识别出关键字出来，构建出SQL语法树
• 语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断输入的SQL语句是否满足语法规则

语法树大致结构如下：

💡 当词法分析和语法分析出错时，分析器会抛出异常。比如语法结构出错、出现了无法识别的字符等。表或字段不存在并不是在分析器里做的，而是在预处理阶段完成。

执行 SQL

每条 SQL 语句主要可以分为以下这三个阶段：

1. prepare预处理阶段：检查 SQL 查询语句中的表或者字段是否存在；将select*中的*符号，扩展为表上的所有字段
2. optimize优化阶段：优化器会根据语法树制定多个执行计划，然后确定最优的执行计划

• 在表里存在多个索引的时候，决定使用哪个索引；
• 在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

1. execute执行阶段：判断用户权限，然后根据执行计划执行 SQL 语句

SELECT 查询过程

1. 客户端通过连接器连接 MySQL 服务
2. 连接成功后向SQL接口发送SQL语句请求
3. SQL接口接收到SQL查询语句会先去缓存查询，如果命中返回给客户端，否则交给解析器
4. 解析器在拿到SQL语句后会判断语法是否正确，正确会生成SQL语法树交给优化器，否则报错给客户端
5. 优化器会根据SQL语法树生成一个最优的执行计划交给执行器执行
6. 执行器拿到执行计划调用存储引擎来获取数据响应给客户端

UPDATE 语句执行原理

update其实包括了更新、插入和删除。看过MyBatis源码的小伙伴应该都知道Executor里面也只有doQuery()和doUpdate()方法，没有doDelete()和doInsert()。

缓冲池

首先，InnoDB的数据都是放在磁盘上的，操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB设计了缓冲池，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫Buffer Pool。

下一次读取相同的页，先判断是不是在缓冲池里面，如果是就直接读取不用再次访问磁盘。修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时

候，我们把它叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，也就是刷新脏页。

Buffer Pool是InnoDB里面非常重要的一个结构，它的内部又分成几块区域。前面的篇幅我们已经介绍了InnoDB的内存结构和磁盘结构，这里我们做一下简单的回顾。

InnoDB 内存结构和磁盘结构

Buffer Pool主要分为3个部分：Buffer Pool、Change Buffer、AdaptiveHash Index，另外还有一个（redo）Log Buffer。

Buffer Pool

BufferPool缓存的是页信息，包括数据页、索引页。内存缓冲区对于提升读写性能有很大的作用。

内存的缓冲池写满了怎么办？InnoDB用LRU算法来管理缓冲池（链表实现，不是传统的 LRU，分成了Younf 和 Old），经过淘汰的数据就是热点数据。

🤔 思考一个问题：当需要更新一个数据页时，如果数据页在Buffer Pool中存在，那么就直接更新好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘 IO，有没有优化的方式呢？

Change Buffer

如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。这一块区域就是Change Buffer。5.5 之前叫Insert Buffer插入缓冲，现在也能支持Delete和Update。我们把Change Buffer记录到数据页的操作叫做merge。

💡 那么什么时候发生merge呢？有几种情况：在访问这个数据页的时候，或者通过后台线程或者数据库关闭、Redo Log写满时触发。

如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 ChangeBuffer（写缓冲）。写多读少的业务，调大这个值：

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

Log Buffer

如果Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现 crash-safe）——用它来实现事务的持久性。这个文件就是磁盘的 Redo Log（叫做重做日志），对应于ib_logfile0 和 ib_logfile1，每个 48M。

这种日志和磁盘配合的整个过程 ，其实就是 MySQL 里的 WAL 技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

show variables like 'innodb_log%';

同样是写磁盘，我们为什么不直接写到db file里面去而是先写日志再写磁盘呢？

我们先来了解一下随机 I/O 和顺序 I/O 的概念：磁盘的最小组成单元是扇区，通常是 512 个字节。操作系统和磁盘打交道，读写磁盘，最小的单位是块 Block。

如果我们所需要的数据是随机分散在不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，依

次进行此过程直到找完所有数据，这个就是随机IO，读取数据速度较慢。

假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序 IO。

刷盘（将内存中的数据写入磁盘）是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。

当然 Redo Log 也不是每一次都直接写入磁盘，在Buffer Pool里面有一块内存区域Log Buffer专门用来保存即将要写入日志文件的数据，默认16M，它一样可以节省磁盘 IO。

需要注意：Redo Log 的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自Buffer Pool。Redo Log写入磁盘，不是写入数据文件。

Redo Log的特点：

1. Redo Log是InnoDB存储引擎实现的，并不是所有存储引擎都有
2. 不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志
3. Redo Log的大小是固定的，前面的内容会被覆盖

除了Redo Log之外，还有一个跟修改有关的日志，叫做Undo Log（撤销日志或回滚日志），记录了事务发生之前的数据状态，分为insert Undo Log 和update Undo Log。如果修改数据时出现异常，可以用Undo Log来实现回滚操作（保持原子性）。

UPDATE 更新过程

有了Redo Log和Undo Log，我们来总结一下一个 Update 操作的流程。

update user set name = 'jason' where id = 1;

1. 在执行前需要

   1. 连接器连接数据库
   2. 分析器通过词法分析和语法分析知道这是一条更新语句
   3. 优化器决定要使用的索引等
   4. 执行器负责具体的执行过程

2. 事务开始从内存Buffer Pool或磁盘取到包含这条数据的数据页，返回给Server的执行器

3. Server的执行器修改数据页的这一行数据的值为 jason

4. 记录 name=jason123（原值）到Undo Log

5. 记录 name=jason到Redo Log

6. 调用存储引擎接口，记录数据页到Buffer Pool（修改 name=jason）

7. 事务提交