查询执行的基础

MySQL的一般查询流程如下：

MySQL 客户端/服务器通信协议

MySQL客户但和服务器中间的通信协议时”半双工”的。因此当查询语句较长时参数”max_allowed_packet”很重要。

都是从数据库中的缓存获取数据。可以通过对MySQL的接口，进行设置取消缓冲。

MySQL的查询状态(SHOW FULL PROCESSLIST进行查询)

Sleep:线程正在等待客户端发送新的请求Query:线程正在执行查询或者正在将结果发送给客户端Locked:该线程正在等待表锁，InnoDB的行锁，并不会体现在线程状态中。Analyzing and statistics :线程正在收集存储引擎的统计信息，并生成查询的执行计划Cpoy to tmp table [on disk]:线程正在执行查询，并将其结果集都复制到一个临时表中，一般是GROUP BY操作或者UNION 操作Sorting result:线程正在对结果集进行排序Sending data:线程可能在多个状态之间传递数据，或者在生成结果集，或者在想客户端返回数据

查询缓存

MySQL的缓存查询时一个对大小写敏感的哈希查找实现的。即使有一个字节不同，也不会匹配缓存结果。

查询优化处理

语法解析器和预处理:通过关键字将SQL语句进行解析，并生成一颗对应的”解析树”，再使用语法规则验证和解析查询。检查语句是否合法查询优化器:尝试预测执行的成本，并选择其中成本最小的一个。最小单位时随机读取一个4K数据页的成本。可以通过查询当前会话的’Last_query_cost’计算查询成本;例如: SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor; SHOW STATUS LIKE 'last_query_cost'

输出：1040.599000;表示需要做1040个数据页的随机查找才能完成上述操作。

由于统计信息不准确、基于成本模型而非时间最优、不考虑其他的并发执行、不会考虑模型外的操作成本、无法估算所有成本等原因；其选择错误。

主要优化策略分为两种：

静态优化:直接对解析树进行分析,与查询值无关是编译时优化动态优化:和查询的上下文等因素有关；时运行时优化

MYSQL能处理的优化类型有:

重新定义关联表的顺序将外连接转化为内连接使用等价变换规则优化COUNT()、MIN()和MAX()(最大最小是索引的最后一条)预估并转化为常数表达式:优先进行条件筛选再进行内联覆盖索引扫描:子查询优化:有时将子查询转换为一种效率更高的形式，从而减少多个查询对数据进行访问。提前终止查询:查询为空提前终止等值传播:两个列最大值通过等式关联，MySQL能够把其中一个列的WHERE条件传递到另外一个列上，两个表使用同一个条件查询列表IN()的比较:先进行排序，二分查找确定列表中的值是否满足条件。(O(lg(n))复杂度)，或者转化为多个OR语句(O(n)复杂度)

MySQL如何执行关联查询

任何一次查询都可能是关联;MySQL使用循环嵌套关联，先查一个，获取内容；再按行进行关键迭代查询；直到结束。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KWse2D9a-1602155367895)(https://wangpengcheng.github.io/img/2020-02-11-23-49-58.png)]

执行计划

MySQL使用指令树，而不是生成查询字节码来执行查询。

关联查询优化器

最重要部分，决定了多个表关联时的顺序。可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照最优的关联顺序执行。

当不同关联的表数，超过optimizer_search_depth时，使用贪婪搜索模式，查找最优的关联顺序。

排序优化

排序成本相当高，应该尽可能避免排序或者避免大量数据进行排序。

MySQL使用单次传输排序；先读取查询所需要的所有列;再根据给定列进行排序。相对于两次传输排序(先找数据再排序，第二次读取对应行数据)；减少了I/O;但是返回的列会非常多，占用额外占用大量的空间。

查询所需列的总长度不超过参数max_length_for_sort_data时，使用单次传输。

当使用LIMIT时，先进行结果筛选再排序。

查询执行引擎

MySQL重复执行计划中的各个操作，直到完成所有的数据查询。

返回结果给客户端

一定会返回结果，即使为空。返回过程是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表。开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输。