SQL执行流程详解

一、概览

mysql中的SQL执行流程

 二、执行流程

1、查询缓存

server如果在查询缓存中发现了这条SQL语句，就会直接结果返回给客户端，如果没有，进入到解析器阶段，查询缓存效率一般不高，MYSQL8.0之后抛弃了这个功能

 Mysql对一个查询请求，会先到查询缓存看看，之前是否执行过这条语句，之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询语句，value是查询的结果，如果查询能够直接在缓存中找到key，那么这个value就会被直接返回给客户端，如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。所以，如果查询命中缓存，MYSQL不需要执行后面的复杂操作，就可以直接返回结果这个效率会很高。

大多数情况查询缓存是不能命中，为什么？

查询缓存是把之前的查询结果缓存起来，这样下次不需要执行就可以直接拿结果。在MYSQL中的查询缓存，不是缓存执行计划，而是缓存查询对应的结果。只有相同的查询操作才会命中查询缓存，两个查询请求在任何字符上的不同（空格，注释，大小写）都会导致缓存不会命中。如果查询请求中包含某些系统函数，用户自定义的变量和函数，一些系统表（mysql，information_schema，performance_schema等数据库中表）那这个请求就不会缓存。

比如，函数now()，每次调用都会产生最新的当前时间，如果在一个查询请求中调了这个函数，即使请求的文本信息完全一样，不同的时间两次查询应该得到不同的结果。

既然是缓存，缓存失效也要考虑，MYSQL的缓存系统会检测涉及到的每张表，只要该表的结构或者数据被修改，出现如下操作（insert，update，delete，truncat table，alter table，drop table，drop database等）时所有缓存查询将变为无效，对于更新压力大的数据库来说，查询缓存命中率更低。

一般建议在静态表（很少更新的表，比如系统配置表，字典表）使用缓存查询，MYSQL提供了按需设置的方式。在my.cnf文件中query_cache_type设置为DEMANO，代表当sql语句中有SQL_CACHE关键词时才有缓存。

mysql8之前默认是不开起查询缓存

mysql8之后开始抛弃了该参数，在mysql8.x中查询该参数，查询不到

 在5.x的my.cnf中修改参数

 对于默认的SQL语句都不使用查询缓存，对于确定要使用的查询缓存的语句可以使用SQL_CACHE显示指定，例如如下sql

SELECT  SQL_CACHE  *  FROM test WHERE ID>5
 

监控查询缓存命中率，使用如下参数

 

 2、解析器

对SQL进行语法和语义分析

 如果SQL语法正确，会生成一颗语法树

 语法词法分析机制

 3、优化器

 在优化器中会确定SQL语句的执行路径，比如是全表检索还是索引检索

一条查询可以有很多种执行方式，最后都返回相同的结果，优化器是为了找到最好的执行计划

 优化器在表里有多个索引的时候，决定使用哪一个索引，或者在一个语句有多表关联的时候吧，决定各个表的连接顺序，还有表达式简化，子查询转为连接，外连接转为内连接等

例如，如下语句是执行两个表的JOIN

查询优化器中 ，分为逻辑查询优化阶段和物理查询优化阶段

（1）逻辑查询优化

通过改变SQL语句的内容来使得SQL查询更高效，同时为物理查询优化提供更多的候选执行计划，一般采用的方式是对SQL语句进行等价变换，对sql查询进行重写，而查询重写的数学基础就是关系代数，对条件表达式进行等价谓词重写，条件简化，对视图重写，对子查询优化，对连接语义进行外连接消除，嵌套连接消除等

（2）物理查询优化

基于关系代数进行查询重写，而关系代数的每一步都对应物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效的使用索引，提升查询效率

4、执行器

当产出了执行计划后，进入执行器阶段

 在执行前判断用户是否有权限，如果没有，返回权限错误。如果有权限，则打开表继续执行，打开表的时候执行器就会根据表的引擎定义，调用存储引擎API对表进行读写，存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现需要看存储引擎层

 如果，表test中，id字段没有索引，那么以Innodb为例，

select  *  from  test  where id =1

执行器的执行流程是

（1）调用Innodb引擎接口获取这个表的第一行

（2）判断id是不是符合要求，如果不符合则跳过，如果符合则将这行存在结果集中

（3）调用引擎接口获取下一行数据，重复（2）的逻辑直到这个表的最后一行

（4）执行器将上述遍历的结果集返回给客户端

至此一个查询执行过程结束

三、执行流程案例

SQL执行需要使用的资源是怎么样的，可以通过profiling在MYSQL中对一条SQL语句的执行进行分析

1、开启profiling

默认是不开启

 为了便于测试，在session中开启

 再次查看

 此时profiling已经开启

profile的命令详解

 举例，查看某一个请求在执行过程中的cpu情况

2、MYSQL8.x执行流程

在MYSQL8.x中执行两次如下sql

select * from employees

 也可以查看上面query_id中具体的sql的profile

3、MYSQL5.x执行流程

 上图中 storing result in query cache 代表开启查询缓存

上图可以看出query8直接命中的query7的查询结果。