mysql第二章一条查询sql语句的一生

一 SQL查询语句是如何被执行的

mysql架构图

大体来说 mysql分为Server层和存储引擎层两部分

Server层

Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学、和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
不同存储引擎共用一个Server层。

引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的是InnooDB,
从MysSQL5.5.5版本开始成为了默认存储引擎。
在create table语句中使用engine=memory, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不同，支持的功能也不同。

MySQL解析过程

通过下图先理解sql整体执行过程

• 客户端先发送一条查询给服务器
• 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。负责进入下一阶段。
• 服务器端进行SQL解析、预处理，在优化器生成对应的执行计划
• mysql根据执行计划，调用存储引擎的API执行查询
• 将结果集返回给客户端

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接
连接命令一般是这么写的
mysql -h$ip-P$port -u$user -p
连接命令中的mysql是客户端工具，用来跟服务端建立连接。在完成经典的TCP握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

mysql连接状态

对于mysql连接，任何时刻都有一个状态，该状态表示了mysql当前正在做什么。使用show full processlist命令查看当前状态。在一个查询生命周期中，状态会变化很多次，下面是这些状态的解释

• sleep：线程正在等待客户端发送新的请求；
• query：线程正在执行查询或者正在将结果发送给客户端；
• locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
• analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；
• copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示mysql正在将一个内存临时表放到磁盘上。
• sorting Result：线程正在对结果集进行排序。
• sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在想客户端返回数据。

查询缓存

连接建立完成后，你就可以执行select语句了。执行逻辑就会来到第二步：查询缓存。
在解析一个查询语句之前，如果查询缓存是打开耳朵，那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。
如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。
不建议使用缓存：
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空
缓存的使用方法（配置表或者不会更改的表建议使用）：
select SQL_CACHE * from T where ID=10
MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了

分析器

如果没有命中查询缓存，就要开始真正执行语句了。
分析器先会做“词法分析”，如对下sql会生成一个语法树。
select username from userinfo

MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。
做完了这些识别以后，就要做"语法分析"。根据此法分析的结果，语法分析器会根据语法规则，判断你输入的这个sql语句是否满足MySQL语法。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候,决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：

select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;	

• 既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
• 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器

在解析和优化阶段，mysql将生成查询对应的执行计划，mysql的查询执行引擎则根据这个执行计划来完成整个查询。
执行器根据表的引擎定义，去使用这个引擎提供的接口。
如：select * from T where ID=10;
执行器的执行流程是这样的：

• 调用InnoDB引擎接口去这个表的第一行，判断ID值是不是10,如果不是则跳过,如果是则将这行存在结果集中
• 调用引擎接口取"下一行",重复相同的判断逻辑,直到取到这个表的最后一行
• 执行器将上述遍历过程中满足条件的行组成的记录集作为结果集返回给客户端

至此，这个语句就执行完成了
你会在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

总结

mysql查询语句执行过程

1. 连接
   连接管理模块，接收请求；连接进程和用户模块，通过连接线程和客户端对接
2. 查询
   • 查询缓存 Query Cache
   • 分析器 内建解析树，对其语法检查
   • 优化器 将前面解析树转换成执行计划，并进行评估最优
   • 执行器 获取锁，打开表，通过meta数据，获取数据
3. 返回结果
   返回给连接进程和用户模块，然后清理，重新等待新的连接

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎