Mysql 执行流程

1、逻辑剖析

sql 执行流程为：sql语句 -> 查询缓存 -> 解析器 -> 优化器 -> 执行器。

1.1 服务器处理客户端请求

​ 客户端程序 connectors >> 连接池 >> SQL接口 >> 解析器 >> 优化器 >> 查询缓存 >> 插件式存储引擎 >> File（文件系统/日志文件）

1. 客户端程序 : 包括一些mysql工具如：native 或者语言工具如：php 、go 、python
2. 连接池  : 提供多个用户客户端和服务端交互的线程
3. SQL接口 : 接收sql命令，返回查询结果
4. 解析器  : 进行sql语法的解析、语意解析、生成语法树
5. 优化器  : mysql核心组件，对sql命令进行优化
6. 缓存    : 以key -> value方式缓存查询结果 (如果查询sql指令有缓存直接在SQL接口部分返回缓存结果)
7. 存储引擎 : 与底层文件惊醒交互，查询数据文件系统、日志文件等

1.2 Connertors

connectors 是指在不同语言中与sql的交互。 要使用mysql 可以编写程序与mysql服务端建立tcp连接 按照定义好的mysql协议进行交互。

接下来的mysql server结构分为如下三层。

1.3 连接层

客户端访问mysql服务端前，需要建立tcp连接

经过三次握手连接成功后，mysql服务端对tcp传输的账号密码进行认证、权限获取（通过权限表获取权限写入内存）。

由于多个系统与mysql建立的连接并不止一个，所以为了解决tcp无限创建销毁TCP连接带来的资源消耗、性能下降问题。mysql服务器有专门的tcp连接池限制最大连接数，采用长连接模式复用tcp连接，来解决以上问题

1.4 服务层

服务层主要完成大多数的核心服务功能，如SQL接口，缓存查询、SQL分析以及优化部分内置函数的执行，所有的跨存储引擎功能也在这一层实现，如：存储过程、存储函数。

在该层中，服务器会解析查询并创建相应的解析树、完成对其的优化如：确定表查询的顺序，是否利用索引等，最后生成相应的执行操作。

如果是查询SELECT语句 ，服务会查询内部缓存，如果缓存空间足够大，可以解决大量读操作的环境中很好的提升系统的性能。

• SQL Interface 接口

  • 接收sql命令，返回查询结果
  • 支持DML、DDl、存储过程、视图、触发器、自定义函数等多种SQL语言接口

• Parser：解析器

  • 进行sql语法的解析、语意解析、将sql分解成数据结构
  • sql指令传递到解析器的时候被解析器验证和解析，生成一个语法树，创建好之后还会对sql查询进行语法上的优化，进行查询重写。

• 查询优化器

  • sql命令在解析之后、查询之前会使用查询优化器确定sql语句的执行路径，生成一个执行计划

  • 这个执行计划表明应该使用哪些索引进行查询，表之间的顺序应该如何，最后按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将结果返回

  • 他使用选取-投影-连接策略查询，如：

  • select id,name from user where gender='女'
    

    这个sql查询先根据where进行选取，而不是将数据全部查询出来在进行过滤。

    这个sql先根据id和name进行属性投影，而不是将所有字段取出来在过滤

    将两个条件连接起来生成最后的结果

• 查询缓存组件（ mysql8.0 已经优化掉该过程 ）

  • mysql内部维持着一些cache和buffer，比如query cache用来缓存一条select语句的结果。如果能在查询中找到对应的缓存，则不必在执行解析优化的过程直接返回缓存结果。
  • 缓存机制由一系列小缓存组成。如：表缓存、记录缓存、key缓存、权限缓存,并且可以在不同客户端之间共享
  • 从5.7.2之后不推荐使用查询缓存。在mysql8.0中删除该机制。

1.5 引擎层

mysql的架构可以在不同场景中应用并发挥良好的作用，主要体现在存储引擎上，插件式的引擎架构将查询处理和其他系统任务以及数据的存储提取分离。这种架构可以根据业务去求和实际需要选择合适的存储引擎，同时开源的mysql还允许开发人员设置自己的开发引擎。

插件式的存储引擎层，真正的负责了mysql中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过api与存储引擎通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

1.6 存储层

所有的数据、数据库、表的定义，表的数据、索引等都是存储在文件系统上以文件的形式存在，并且完成存储引擎的交互，在文件系统下，可以使用本地磁盘，也可以使用DAS、NAS、SAN等各种存储系统。

2、SQL执行流程 2.1 mysql中的sql执行流程图

Mysql中的查询流程：

1. 查询缓存： Server如果在缓存中发现了sql语句，则直接返回 如果没有就进入解析器阶段。需要说明的是因为查询缓存往往效率不高，所以8.0就抛弃了这个功能。

   ​ 一般建议在静态表里使用查询缓存，静态表就是极少更新的表，比如系统的配置表、字典表。好在mysql 提供了按需使用的方式，可以将my.cnf参数query_cache_type设置成DEMAND，代表sql语句中有sql_cache关键词时才缓存如：

   #quert_cache_type 0表示关闭查询缓存OFF。1表示开启查询缓存ON.  2 表示 DEMAND
   query_cache_type=2 
   #对于你确定要使用查询缓存的语句时可以用SQL_CACHE显式指定：
   SELECT SQL_CACHE * FROM test where id = 1
   

   ​ 在 5.7中查看是否开启查询缓存：

   show variables like '%query_cache_type%'
   

   ​ 监控查询缓存的命中率：

   show status like '%Qcache%';
   

2. 解析器：介于sql语句的语法分享、语义分析

3. 优化器： 在优化器中会确定sql语句的执行路径，比如是根据全表检索 还是根据索引检索等

   ​ 在查询优化器中，分为逻辑查询优化和物理查询优化两个大块

   • 物理查询优化是通过索引和表连接方式等技术进行优化
   • 逻辑查询优化是通过sql等价变换提升查询效率，直白点说就是换一种执行效率高的写法。

   截止到现在，还没有真正的读表，而是产出了一个执行计划。于是进入到执行器阶段

4. 执行器：
   通过以上阶段所产生的执行计划进行操作。在执行之前需要判断用户是否具备权限。如果没有，返回权限错误，如果具备权限就会执行并返回结果。
   执行器会根据表的引擎定义，调用存储引擎API对表进行读写。存储引擎Api只是抽象接口，下面还有一层存储引擎层，具体实现还要看表选择的存储引擎。

总结

至此sql语句在mysql中就执行完了。对于有索引的表，执行的逻辑也差不多。

2.2 mysql中的sql执行原理

​ 前面的结构很复杂，我们只需要抓去最核心的部分：sql执行原理。 不同的dbms的sql执行原理相同，在不同的软件中，各有各的实现路径

​ 在不同的模块中，sql执行所使用的资源是怎么样的，如何对一条sql语句的执行时间进行分析：

1. 确认profiling值

   ​ 了解查询语句底层执行的过程：SELECT @@profiling; 或者 SHOW variables like '%profiling%';

   ​ 查看是否开启了计划，开启这个值可以让mysql收集在sql语句执行时所使用的资源情况，命令如下：

   SELECT @@profiling; 
   #或
   SHOW variables like '%profiling%';
   
   
   #profiling  0代表关闭 1代表开启
   set profiling = 1;
   
   
   
   #查看执行语句的资源情况
   SHOW PROFILES 
   # 或
   SHOW PROFILE FOR QUERY 157  #SHOW PROFILES 中的id值