【Mysql】一条SQL语句是怎么执行的（二）

1.一条SQL的执行流程（网络层+核心服务层）

网络层

1. 客户端发起请求，数据库连接器收到请求，就会负责跟客户端建立连接、并将这个连接放到连接池中统一管理

核心服务层

2. Mysql Server用的线程池的网络模型，即一个连接会交给线程池里面的线程去处理。

3. 线程会把SQL语句交给SQL接口，处理和接受用户的SQL命令。

4. 先拿着SQL语句去查询Cache ，如果命中缓存，直接返回。Cache 里面缓存的形式类似Map<SQL语句，SQL语句执行完的结果集>

5. 如果没有命中缓存，进入 Parser 解析器和 Preprocessor 预处理模块，这一流程主要是基于 SQL 语法进行词法和语法分析和语义的解析

（1）词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。比如一个简单的 SQL 语句。将SQL拆解为不可再分的原子符号，称为Token。并根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符，变成语法树。

（2）语法分析：

 语法分析就是根据词法分析拆解出来的Token（原子符号）将SQL语句转换为抽象语法树。

Parser Tree会contextualize生成MySQL抽象语法树（Abstract Syntax Tree）

抽象语法树如下：

select name from user where id = 1;

（3）预处理器：

问题：如果我写了一个词法和语法都正确的 SQL，但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？比如： select * from javacoo; 解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？

实际上还是在解析的时候报错，解析 SQL 的环节里面有个预处理器。 它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。 预处理之后得到一个新的解析树。

6. MySQL 查询优化器又叫成本优化器，使用基于成本的优化方式（Cost-based Optimization）

将SQL经过词法解析/语法解析后得到的语法树作为输入，利用内置的成本模型和数据字典信息以及存储引擎的统计信息，经过一系列运算 ，最终得出一个执行计划，包括选择使用哪个索引。

MySQL Server 可以分为两部分：服务器层以及存储引擎层。其中，优化器工作在服务器层，位于存储引擎 API 之上。优化器的工作过程从语义上可以分为三个阶段：

• 逻辑转换： 包括否定消除、等值传递和常量传递、常量表达式求值、外连接转换为内连接、子查询转换、视图合并等；
• 基于成本优化： 包括访问方法和连接顺序的选择等；
• 执行计划改进： 例如表条件下推、访问方法调整、排序避免以及索引条件下推。

逻辑转化：

MySQL 优化器首先可能会以不影响结果的方式（比如移除不必要的括号）对查询进行转换，转换的目标是尝试消除某些操作从而更快地执行查询。

• 常量传递

比如：a = 5 AND b > a可以转换为a = 5 AND b > 5

• 等值传递

比如：a = b and b = c and c = 5 可以转换为 a = 5 and b = 5 and c = 5

• 移除没用的条件

比如：(a < 1 and b = b) OR (a = 6 OR 5 != 5)，很明显 b=b肯定是true，5！=5肯定是false，可以转换为(a < 1 and TRUE) OR (a = 6 OR FALSE)，然后可以化简为a < 1 OR a = 6

• 表达式计算

比如：a = 5 + 1可以化简为 a = 6

基于成本的优化：

一条查询语句在MySQL中的执行成本，主要由两部分组成：

• IO成本：记录存储在磁盘上，检索记录首先需要将记录从磁盘加载到内存，然后才能进行操作。
• CPU成本：记录加载到内存后，CPU负责读取记录、判断是否符合过滤条件、对结果集排序等等操作。

在真正执行一条查询语句之前，MYSQL的优化器会找出所有可以用来执行该语句的方案，并在对比这些方案后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。之后才会调用存储引擎提供的接口真正执行查询。总结一下，过程如下：

1、根据搜索条件，找出所有可能使用的索引。

2、计算全表扫描的代价。

3、计算使用不同索引执行查询的代价。

4、对比各种方案的代价，找出成本最低的方案。

为了找到最佳执行计划，优化器需要比较不同的查询方案。随着查询中表的数量增加，可能的执行计划会呈现指数级增长。

所以优化器不可能遍历所有的执行方案，一种更灵活的优化方法是允许用户控制优化器在查找最佳查询计划时的遍历程度。一般来说，优化器评估的计划越少，则编译查询所花费的时间就越少；但另一方面，由于优化器忽略了一些计划，因此可能找到的不是最佳计划。

执行计划改进

索引条件下推ICP： 就是尽可量利用二级索引筛除不符合where条件的记录，如此一来减少需要回表继续判断的次数

例子：假设这个表有联合索引INDEX(zipcode, lastname, firstname)

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

• 不用ICP，只使用最左匹配原则。那么只能使用联合索引的zipcode，回表记录不能有效去除。
• 使用ICP，除了匹配zipcode的条件之外，额外匹配联合索引的lastname，看其是否符合where条件中的’%etrunia%'，然后进行回表。如此一来，使用联合索引就可以尽可量排除不符合where条件的记录。这就是ICP优化的真谛

7. 输出执行计划

就是sql查询的顺序，以及如何使用索引查询，返回的结果集的行数

 explain select * from brand where clothes_brandId = '0403eaee-a7d5-432d-ac11-cb1ef18e94f2'
 KEY `test` (`clothes_brandId`) USING BTREE

• id: 查询的序号，有几个select就显示几行，id时按照select出现的顺序增长的，id列的值越大执行的优先级越高
• selectType：查询中每个select子句的类型

simple: 查询中不包含子查询和union查询
promary：此查询是最外层查询（包含子查询）
subquery：子查询中的第一个select
union：此查询是union的第二或随后的查询
dependent union：union中是第二个或后面的查询语句，取决于外面的查询
union result: union的结果
dependent subquery：子查询中的第一个select， 取决于外面的查询，即子查询依赖于外层查询的结果
derived：衍生，导出表的select

• type：判断索引有没有命中，并且走的是哪一中索引。sql优化的重要字段，是我们判断sql性能和优化程度的重要指标。

system：表中只有一行记录，比如系统表，一次命中
const：通过索引一次命中，匹配一行数据。性能高
eq_ref: 使用唯一索引，并命中，即表中只有一条数据与之匹配，效率高。可能会回表，因为不是主键。
ref： 使用非唯一索引扫描，命中多个匹配的结果，可能会回表
range：使用一个索引来检索给定范围的行，一帮用于between，<, >
index：遍历索引树，即遍历所有索引，效率低，Index是走了二级索引树，但是where条件不符合最左原则，但刚好select的字段处于覆盖索引
all： 全表扫描，性能差
执行效率：system > const > eq_ref > ref > range > index > all

• table：查询的表
• partitions：有分区表的话，打印分区表的信息
• possible_keys: 可能会走的索引
• key：真正走的索引名字
• key_len：联合字段索引命中的长度
• ref：命中索引的字段名
• rows：执行计划的读取的数据量，越少越优
• filtered：过滤，百分比，返回数据占读取行数的比例，越高越好（读取的数据更有意义）
• extra： 执行计划输出中的 Extra 字段通常会显示更多的信息，可以帮助我们发现性能问题的所在

using_filesort: 结果集需要外部索引，无法通过索引排序的顺序得到正确结果
using_index:是覆盖索引，覆盖索引说明通过在索引树总就可以查找到所需要的数据，不需要回表扫描表数据文件，说明性能较好
using temporary: 是否有临时表，如查询时的多表join情况，说明检索效率不高，建议优化
using where: 使用where过滤，效率较高

2.一条SQL的执行流程（存储引擎层Innodb+系统文件层）

（1）select语句执行流程

根据执行计划要用到主键索引，操作步骤如下：

1. 判断LRU列表存不存在对应的数据先看描述信息，描述信息上面有页的信息。如果存在，根据冷热LRU链表规则调整链表
2. 如果不存在，准备从索引段中找到聚簇索引树的头节点，加载到buffer pool 中
3. 查看Free链表是否存在空闲的页空间可以加载。如果存在，将页写入这个描述数据块对应的空闲缓存页中。把缓存页对应的描述数据块从 Free链表 中移除，表示该缓存页已被使用了。并加入到LRU链表中。
4. 如果不存在，从LRU链表最后一个进行页面置换，先判断最后一个是不是在FLUS链表，需要先进行刷盘操作。然后进行页面置换。
5. 最后根据页目录进行快速定位，对应的数据在哪一个页中，重复获取页到buffer pool 操作（Lru Free,Flush)，直到找到对应的数据页位置，然后加载到buffer pool 里面 返回数据。

（2）insert update delete语句执行流程

1. 判断LRU列表存不存在对应的数据先看描述信息，描述信息上面有页的信息。如果存在，将页进行修改，变成脏页根据冷热LRU链表规则调整链表
2. 如果不存在，则判断这个insert 语句是否涉及到唯一性限制。如果存在，将索引页的数据加载到缓存中，判断是否有重复值，没有重复数据就插入数据，使得其变成脏页。
3. 如果不存在，则将Insert插入步骤加入到change buffer。（change buffer存储的是SQL语句）
4. 生成脏页的时候会产生三种日志 （bin log，redo log和redo log），其中修改缓存页的物理地址改变产生的redo log 和 undo log 产生的redo log 都会写入到log buffer 里面。最后持久化到磁盘的redo log 表空间的log file，而undo log 则是持久化到undo log 表空间或者磁盘系统表空间的undo log 里面
5. 脏页double write 机制
   
6. change buffer的修改也会产生对应的redo log，然后也会持久化到系统表空间的Chang buffer里面

参考文章：

• MySQL 优化器原来是这样工作的
• mysql执行计划