MySQL 执行优化查询

查询执行的基础

当希望MySQL能够以更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。当向MySQL发送一个请求的时候，MySQL执行过程如图1-1所示：

图1-1 查询执行路径

1. 客户端发送一条查询给服务器。
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端。

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

一般来说，不需要去理解MySQL通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块独立来发送。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前
MySQL会检查一次用户权限。

查询优化处理

语法解析器和预处理

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

查询优化器

现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个（并不是最快）。
优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。相反，动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如WHERE 条件中的取值、索引中条目对应的数据行数等。MySQL能够处理的优化类型如下：

• 重新定义关联表的顺序，数据表的关联并不总是按照在查询中指定的顺序进行。
• 将外连接转化成内连接，并不是所有的OUTER JOIN 语句都必须以外连接的方式执行。诸多因素，例如WHERE 条件、库表结构都可能会让外连接等价于一个内连接。
• 使用等价变换规则，MySQL可以使用一些等价变换来简化并规范表达式。例如，（5=5 AND a>5 ）将被改写为a>5。
• 优化COUNT()、MIN()和MAX()，索引和列是否可为空通常可以帮助MySQL优化这类表达式，B-Tree索引的最左最右端可分别对应最大值和最小值。
• 预估并转化为常数表达式，当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
• 覆盖索引扫描，当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据。
• 子查询优化，MySQL在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。
• 提前终止查询，在发现已经满足查询需求的时候，MySQL能够立刻终止查询，一个典型的例子就是当使用了LIMIT。
• 等值传播，如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE 条件传递到另一列上。例如，我们看下面的查询：其中，salese_region 也会应用到Where语句。
  select order.* from sales_order order inner join sales_region region on order.region_no = region.region_no where order.region_no = 86;
• 列表IN()的比较，MySQL将IN() 列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（log n ）复杂度的操作，等价地转换成OR 查询的复杂度为O（n ），对于IN() 列表中有大量
  取值的时候，MySQL的处理速度将会更快。

数据和索引的统计信息

MySQL架构由多个层次组成。在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。因为服务器层没有任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息，包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

MySQL如何执行关联查询

MySQL认为任何一个查询都是一次“关联”——并不仅仅是一个查询需要到两个表匹配才叫关联。当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。
按照这样的方式查找第一个表记录，再嵌套查询下一个关联表，然后回溯到上一个表，在MySQL中是通过嵌套循环的方式实现——正如其名“嵌套循环关联”。

执行计划

MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。任何多表查询都可以使用一棵树表示，四表关联查询可如下图表示：

图1-2 多表关联的一种方式

MySQL的执行计划总是如图1-3所示，是一棵左测深度优先的树。

图1-3 MySQL实现多表关联

关联查询优化器

MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。由于MySQL对关联执行‘嵌套循环关联操作‘，所以在进行多表关联查询的时候，会将数据少的关联表放在前面，这样子会让查询进行更少的嵌套循环和回溯操作。在我们自己写SQL时也能手动选择，即‘小表Join大表‘。

排序优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。
当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，MySQL将这个过程统一称为文件排序（filesort ）。如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。
MySQL有如下两种算法：
两次传输排序：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次数据传输的成本非常高。
单次传输排序(新版本采用)：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。不再需要从数据表中读取两次数据，对于I/O密集型的应用，这样做的效率高了很多。缺点是，如果需要返回的列非常多、非常大，会额外占用大量的空间。

MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串，例如，如果是VARCHAR 列则需要分配其完整长度；如果使用UTF-8字符集，那么MySQL将会为每个字符预留三个字节。在库表设计不合理的案例中，排序消耗的临时空间可能比磁盘上的原表要大很多倍。

在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。

• ORDER BY 子句中的所有列都来自关联的第一个表：MySQL在关联处理第一个表的时候就进行文件排序，
• ORDER BY 子句中的所有列不全来自关联的第一个表：MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。
  当只需要返回部分排序结果的时候，例如使用了LIMIT 子句，MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。在查询执行阶段，MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，查询中的每一个表由一个handler 的实例表示。MySQL在优化阶段就为每个表创建了一个handler 实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息等。

返回结果给客户端

MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如，我们回头看看前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
这样处理有两个好处：服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。

MySQL 执行优化查询

原文：https://www.cnblogs.com/cd-along/p/15008117.html