这是我参与「第五届青训营」笔记创作活动的第十五天

MySQL执行流程

查询语句执行流程

图片来源于：小林coding

MySQL的结构分成两层，Server层和存储引擎层

Server层负责建立连接，分析和执行SQL。主要包括连接器、查询缓存、解析器、预处理器、优化器、执行器。另外，所有的内置函数（如日期、事件、数学和加密函数等）和所有跨存储引擎的功能（如触发器、视图等）都在server层实现。
存储引擎层负责数据的存储和提取。支持InnoDB、MyISAM、Memory等多个存储引擎。不同的存储引擎共用一个Server层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。

第一步：连接器

建立连接

MySQL基于TCP进行连接，连接的过程需经过三次握手，然后开始验证用户名密码，用户名密码正确，就会建立连接。

丢失连接

连接器定义了连接的最大空闲时间8小时，如果空闲连接超过了这个时间，连接器就会断开连接。客户端是无感知的，只有当客户端再发送一个请求才会发生丢失连接的错误。

短连接和长连接

MySQL的连接和http一样有短连接和长连接的概念

// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

一般推荐使用长连接，可以减少建立连接和断开连接的过程。如果长连接太多，会导致MySQL内存压力大有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

如何解决长连接占用内存的问题

两种解决方式：

定期断开长连接，断开连接就是释放内存，可以定期断开长连接
客户端主动重置连接，MySQL5.7之后的版本实现了mysql_reset_connection接口，当客户端执行了一个很大的操作后，在代码里调用mysql_reset_connection可以重置连接，达到释放内存的作用，不会再做权限认证，会将连接恢复到刚刚创建时候的样子。

总结

服务端与客户端进行TCP三次握手连接
检验客户端的用户名密码，不正确不会建立连接
用户名密码正确，就会给该用户赋权，后续用户的操作都会判断一下

第二步：查询缓存

缓存逻辑

mysql的缓存是以key-value形式存储的，key是sql查询语句，value是结果。mysql会先从缓存中查询是否存在，存在则直接返回，不存在就会等执行完后，将结果存入缓存，执行表的写入操作，对这个表的所有缓存会清空。

缓存作用低效

但是mysql的缓存挺鸡肋的。每次进行表的写入操作，这个表的缓存都会失效，所以对于写操作频繁，或者重复相同读取少的情况下，mysql缓存作用就非常低，有时候根本就提高不了性能

移除缓存

在MySQL8的时候移除了server层的缓存，当一个查询sql进来不会再走缓存了。

只是移除了server层的缓存，并没有移除InnoDB层的缓存

第三步：解析SQL

解析器会做两方面的工作

词法分析：MySQL会识别出关键字，构建出SQL语法树，这样方便后续模块获取SQL表、字段名、where条件等。
语法分析：判断生成的SQL语法树是否满足MySQL语法规则

很多编程语言在编译期都有词法分析和语法分析这个过程，基本上是通识

注意

检查表名、字段名是否存在不是在解析器里做的，解析器只是做构建语法树和检查语法。

第四步：执行SQL

每条SELECT 查询语句流程主要可以分为下面这三个阶段：

prepare阶段：预处理阶段
optimize阶段：优化阶段
execute阶段：执行阶段

预处理器

检查SQL语句的表和字段是否存在

MySQL8是在预处理器做的，MySQL5.7之前是在解析sql之后，预处理之前做的
将select *扩展为表上的所有列

优化器

经过预处理阶段，优化器会给sql语句制定一个执行计划。优化器主要就是将sql查询语句的执行方案确定下来。比如有多个索引的时候，优化器会基于成本的考虑，来决定使用哪个索引。

要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引

如果查询语句的执行计划里的 key 为 null 说明没有使用索引，那就会全表扫描（type = ALL），这种查询扫描的方式是效率最低档次的

执行器

在确定执行接话后，就开始真正执行sql语句了，在执行过程中，执行器会跟存储引擎交互了。

三种执行过程来解释一下执行器和存储引擎的执行过程：

主键索引查询
```
select * from product where id = 1;
```
优化器会对此sql用主键索引进行查询，而且是等值查询，不会有id相同的记录，索引优化器会决定访问类型为const进行查询。
- 将id = 1交给存储引擎，让存储引擎定位符合条件的第一条记录
- 从主键索引的B+树定位到id = 1如果没有这条记录会返回找不到，如果就就将这条记录返回给执行器
- 执行器拿到结果后会判断是否符合条件，如果符合则返回给客户端，不符合则跳过
- 执行器查询到过程是一个while循环，因为只会查询一条记录，所以下一次就直接break退出
全表扫描
```
select * from product where name = 'iphone';
```
这条sql没有用到索引，所以会选用访问类型为all进行全表扫描
- 从存储引擎读取表中的第一条记录，返回给执行器
- 执行器判断是否符合条件name = 'iphone'，不符合则跳过。符合则传递给客户端
  
  sql引擎层每读到一条记录就会返回给客户端，之所以客户端显示的时候是所有记录，是因为客户端等全部查询完毕后才会显示所有的记录
- 执行器查询的过程是一个while循环，存储引擎会一直返回记录给执行器判断，直到存储引擎读完表中的所有记录，返回读取完毕的信息
- 执行器收到存储引擎的完毕的信息，退出循环。
索引下推
```
select * from t_user  where age > 20 and reward = 100000;
```
我们对age和reward字段做联合索引

联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引。

补充：回表操作通常是指索引无法满足查询条件，需要通过索引存储的指针回到数据库表中查找相应的数据行。这个操作被称为回表操作，它会增加额外的IO操作，降低查询效率

那么，不使用索引下推（MySQL 5.6 之前的版本）时：
- 从存储引擎从二级索引B+树中的中定位到第一条age>20的记录
- 存储引擎获取主键值，然后进行回表操作，将完整的记录返回给执行器
- 执行器判断是否符合条件reward = 100000，如果符合则返回客户端，不符合则跳过
- 执行器查询是一个while循环，存储引擎会一直进行回表操作，然后将age>20的记录返回给执行器，执行器判断是否符合条件，直到存储引擎返回读取完毕的信息
- 执行器收到完毕的信息，退出while循环，结束查询
可以看到，没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给执行器，接着执行器会判断是否符合条件 reward=100000

当使用索引下推后，判断reward = 100000的工作交给了存储引擎层，过程如下：
- 存储引擎定位到第一条满足查询条件age>20的记录，先不执行回表操作，而是判断一下该索引中包含的列（reward列）的条件是否成立，如果不成立，则跳过。如果成立，则执行回表操作，将记录返回给执行器
- 执行器会判断其他的查询条件是否成立，如果成立则将其返回给客户端；否则跳过此记录。依次循环
- 直到存储引擎返回完毕的信息，跳出循环
可以看到使用索引下推后，判断记录的reward=100000的工作交给了存储引擎层，过滤不符合的记录，才会执行回表操作。相对于没有使用索引下推节省了很多回表操作。

当你发现执行计划里的 Extr 部分显示了 “Using index condition”，说明使用了索引下推。

学习地址： xiaolincoding.com/mysql/

MySQL执行流程 ｜ 青训营笔记