一条查询语句的执行过程

一、一条查询语句的执行过程

1. 概述

MySQL的框架分为Server层和存储引擎层

1. Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
2. 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。
   

2.第一步连接器

• 我们使用过Linux的伙伴都知道，要想使用Linux上的MySQL数据库需要先建立连接，通过命令mysql -u root -p来连接MySQL服务，登录成功后连接器会根据登录信息获取到登录者相应的权限，并将权限保存下来，以后的操作都会根据这些权限进行判断。
• 如果一个用户连接了MySQL服务，中途改变了用户的权限，也不会影响已经存在连接的权限，只有再次连接才会更新权限。
• 空闲的连接会一直占用吗？这个肯定是不会的，MySQL中规定了最大空闲时长，默认是8个小时（28800s），如果超出这个时长将会断开连接，我们也可以通过命令show variables like 'wait_timeout’来查看，当然我们也可以手动断开，通过命令 kill connnection + id
  
  

3. 第二步查询缓存

• 缓存的数据结构：哈希表的数据结构，通过key-value的形式存储的，其中key为sql语句，value为查询的结果。
• 查询过程：通过第一步客户端就可以向MySQL服务发送给sql语句，MySQL服务通过判断如果该sql语句为查询语句的时候，则会先到查询缓存那里看看是否之前执行过该命令，，如果找到了就直接返回给客户端，如果没找到，就继续执行，执行完之后将结果写到缓存里面。
• 缺点：每次执行更新操作时，都会清空缓存，如果你先查出来一个大量的数据，然后又更新完了这些数据，这时缓存了个寂寞。所以在MySQL8.0版本之后就将查询缓存删掉了

注意：MySQL8.0版本只是删除了Server层的查询缓存，并没有把InnoDB引擎中的buffer pool删除。

4.第三步解析sql

通过前面的步骤之后，开始对sql语句进行解析，解析主要是由解析器来完成。解析器会做一下两件事：

1. 词法分析。MySQL会根据传过来的字符串识别关键字。例如select name from user其中select,from被识别为关键字，name和user被识别为非关键字。
2. 语法分析。根据词法分析的结果，来判断输入的sql命令符合语法规则，如果无语法错误，则构建语法树，反之，则报错。
   

5. 第四步执行SQL

执行SQL分为预处理阶段，优化阶段，执行阶段。

1. 预处理阶段：主要是预处理器。检查sql语句上的表和字段是否存在；将select * 上的 *拓展为表上的所有的列；
2. 优化阶段：优化器，基于查询成本的考虑， 选择查询成本最小的执行计划。
3. 执行阶段：执行器，根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。

5. 几种执行过程

1. 主键索引查询
   对于select *from user where id = 1这条查询语句而言，用到了主键所以，由于主键是唯一的所以优化器决定访问类型为const类型，具体执行流程如下：
   • 执行第一次查询时，指针会指向read_first_record函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，将id = 1的条件传给存储引擎让存储引擎定位符合条件的第一条记录
   • 存储引擎通过B+树索引定位到id = 1的第一条记录，如果查到了则会将记录返回给执行器，如果记录是不存在的，就会给执行器上报记录找不到的错误，然后查询结束。
   • 执行器通过执行引擎返回的记录时，接着判断记录是否符合查询条件，如果符合则返回个客户端，不符合则跳过该记录，继续执行。
   • 执行器查询过程是一个while循环，所以还会在查一次，不过这次会调用read_record函数指针指向的函数，因为优化器访问类型为const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。
2. 全扫描
   例如执行下面这条语句

select * from user where name = 'zhangsan'

这条语句没有用到索引，所以优化器选择的访问类型为ALL进行查询，也就是全表扫描的过程。
-执行第一次查询，会调用read_first_reacord函数指针指向的函数，由于优化器ALL的访问类型，这个函数指针被指向为InnoDB引擎全扫描接口，让存储引擎读取表中第一条数据。

• 执行器会对读取到的这条数据进行条件判断，如果满足查询条件，则返回给客户端(Server层从存储引擎读到一条数据都会返回给客户端，之所以客户端展示的是所有的查询结果，是因为客户端要等到查询结束才会将结果显示出来），如果不满足查询条件，则跳过。
• 执行器查询过程是一个while循环，所以还会查一次，这次会调用read_record这个函数指针指向的函数，因为优化器选择的访问类型为all，所以read_record函数指针还是指向InnoDB引擎全扫描接口，接着向存储引擎要求继续读取刚才那条记录的下一条记录，存储引擎将下一条记录读取出来返回给执行器(Server层)，然后判断该条记录是否符合查询条件，如果符合则返回给客户端，否则，跳过该记录。
• 一直重复上述步骤，直到将存储引擎里面所有的记录读取完成，然后向执行器发送读取完成的信息
• 执行器接收到存储引擎报告的查询完毕的信息，退出循环，查询结束。

3. 索引下推
   索引下推能够减少二级索引在查询过程中回表的操作，提高查询效率，将Sever层的活，交给了存储引擎来干。
   举一个具体的例子，方便大家理解，这里一张用户表如下，我对 age 和 reward 字段建立了联合索引
   
   执行下面这条语句

select * from user age > 20 and reward = 100000

联合索引当出现范围查询(<,>)时,则会停止匹配，也就是说只有age会使用联合索引，而reaward不会。
如果没有发生索引下推的话，执行流程如下：

• Server层首先调用存储引擎定位到满足查询条件的第一条二级索引记录，也就是条件age>20的第一条记录。
• 存储引擎根据二级索引的B+Tree快速定位到第一条记录，并获取主键值，进行回表操作，将完整的记录返回给Server层
• Server层接收到存储引擎返回的记录在判断reward=100000这个条件，如果满足则返回给客户端，反之则跳过该记录。
• 接着，继续向存储引擎要下一条记录，存储引擎在二级索引定位到下一条记录时，获取主键值，然后进行回表操作，将完整的记录返回给Server层。
• 如此反复，直到将存储引擎把表中所有的记录都读完。

而使用了索引下推，reward=100000条件的判断就交给存储引擎来处理，具体流程如下：

• Server层首先会调用存储引擎定位到满足查询条件的第一条二级索引记录，也就是age>20的第一条记录。
• 存储引擎定位到二级索引的第一条记录，先不进行回表操作，而是先判断表中的reaward字段的值是否满足查询条件，如果条件不存在，则直接跳过二级索引，反之则进行回表操作将完成的结果返回给Server层
• Server层在判断其他的查询条件是否成立，成立则返回给客户端，反之则跳过该记录。
• 如此往复，直到存储引擎把表中的所有记录读完。

可以看到使用索引下推，虽然reward无法使用二级索引，但是他包含在联合索引(age,reward)，索引存储引擎直接过滤出reward=100000的条件之后再去进行回表的操作，获取整条记录，大大减少了回表的次数。

总结

执行一条 SQL 查询语句，期间发生了什么？

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：如果命中查询缓存就直接返回，如果没命中再去存储引擎里去找，MySQL 8.0 已删除该模块；
• 解析SQL：通过解析器的词法分析和语法分析，建立语法树，方便后续模块获取表名、字段等
• 执行SQL的三大阶段：
  • 预处理阶段：检查SQL上表和字段是否存在；将select * 上的 ‘*’扩展为表中的各个字段。
  • 优化阶段：基于查询成本的考虑，会优先选择查询成本较低的执行计划。
  • 执行阶段：根据优化阶段指定出来的执行计划，执行SQL语句，从存储引擎获取数据返回给客户端。

如果还不够清楚的话可以看一下，下面具体执行的顺序：