mysql（八）查询优化处理

mysql在查询的时候包括多个子阶段：解析SQL、预处理、优化SQL执行计划，这个过程中任何错误（例如语法错误）都可能终止查询。这里不打算详细介绍MySQL内部实现，而只是选择性地介绍其中几个独立的部分，在实际执行中，这几部分可能一起执行也可能单独执行。我们的目的是帮助大家理解MySQL如何执行查询，以便写出更优秀的查询。

语法解析器和预处理

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”，MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。

与处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查表和数据列是否存在，还会解析名字和别名，看看他们是否有歧义。

下一步预处理器会验证权限，这通常很快，除非服务器上有非常多的权限配置。

查询优化器

现在语法树被认为是合法的了，并且由优化器将其转化为执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取4K数据页的成本，后来（成本计算公式）变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。

mysql>select sql_no_cache count(*) from sakila.film_actor;

---------------------------------------------------------------------

count(*)

5462

mysql>show status like 'last_query_cost';

variable_name        value

last_query_cost       1040.599000

这个结果表示MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算出来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都要一次磁盘I/O。

MySQL的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。例如优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值，如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种“编译时优化”。

相反，动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值、索引中条目对应的数据行等。这需要在每次查询的时候重新评估，可以认为这是“运行时优化”。

在执行语句和存储过程的时候，动态优化和静态优化的却别非常重要。MySQL对查询的静态优化只需要做一次，但对查询的动态优化则在每次执行时都需要重新评估。有时甚至在查询的过程中也会重新优化。

下面是一些MySQL能够处理的优化类型：

重新定义关联表的顺序

数据表的关联并不总是按照在查询中制定的顺序进行。决定关联的顺序是优化器很重要的一部分功能，本章后面将深入介绍这一点。、

将外连接转化为内连接

并不是所有的OUTER JOIN语句都必须以外连接的方式执行。诸多因素，例如WHERE条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询，让其可以调整关联顺序。

使用等价变换规则

Mysql可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和恒不成立的判断。例如，a<b and b=c and a=5则会改写为b>5 and b=c and a=5.这些规则对于我们编写条件语句很有用，我们将在本章后续继续讨论。

优化min()和max()

索引和列是否可为空通常可以帮助MysQL优化这类表达式。例如，要找某一列的最小值，只需要查询对应的B-Tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以证明这一点，在B-Tree索引中，优化器会将这个表达式作为一个常数对待。类似的，如果要查找一个最大值，也只需读取B-Tree索引的最后一条记录。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到“select tables optimized away”.从字面上意思可以看出，他表示优化器已经从执行计划中移除了该表，并以一个常数取而代之。

预估并转化为常数表达式

当MySQL检测到一个表达式可以转为常数的时候，就会一直把该表达式作为常数进行优化处理。例如，一个用户自定义变量在查询中没有发生变化就可以转换为一个常数。数学表达式则是另一个典型的例子。

让人惊讶的是，在优化阶段，有时候甚至一个查询也能转化为一个常数。一个例子是在索引上执行min（）函数。甚至是主键或者唯一键查找语句也可以转为常数表达式。如果WHERE子句中使用了该类索引的常数条件，MySQL可以查询开始阶段就先查找到这些值，这样优化器就能够知道并转换为常数表达式。下面是一个例子：

mysql>EXPLAIN select film.film_id,film_actor.actor_id

         ->from sakila.film

         -> inner join sakila.film_actor using(film_id)

         ->where film.film_id = 1;

-----------------------------------------------------------------------------------------------------------------------------------------------------------

id    select_type   table           type    key                      ref        rows

1     simple            film             const   primary              const      1

1     simple         film_actor     ref       idx_fk_film_id   const      10

MySQL会分成两步来执行这个查询，也就是上面的两行输出。第一步先从film表找到需要的行。因为在film_id字段上有主键索引，所以MySQL优化器知道这只会返回一行数据，优化器在生成执行计划的时候，就已经通过索引信息知道将返回多少行数据。因为优化器已经明确知道有多少个值（WHERE条件中的值）需要做索引查询，所以这里的表访问类型是const.

在执行计划的第二步，MySQL将第一步中返回的film_id列当作一个已知取值的列来处理。因为优化器清楚在第一步执行完后，该值就是明确的了。注意到正如第一步一样，使用film_actor字段对表的访问类型也是coonst.

第一步中一样，使用film_actor字段对表的访问类型也是const。

另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表，这可以通过WHERE、USING或者ON语句来限制某列数的值为常数。在上面的例子中，因为使用了USING子句，优化器知道这也限制了film_id在整个查询过程中都始终是一个常量——因为他必须等于WHERE子句中的那个取值。

覆盖索引扫描（这个我们以前说过）

提前终止查询

在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。一个典型的例子就是当使用了limit子句的时候。除此之外，MySQL还有几类情况也会提前终止查询，例如发现了一个不成立的条件，这是MysQL可以立刻返回一个空结果。从下面的例子可以看到这点：

mysql> explain select film.film_id from salila.film where film_id = -1;

------------------------------------------------------------------------------------------------------

id ....   Extra

1  ......  Impossible where noticed after reading const tables

从这个例子看到查询在优化阶段就已经终止。除此之外，MySQL在执行过程中，如果发现某些特殊的条件，则会提前终止查询。当存储引擎需要检索“不同取值”或者判断存在性的时候，MySQL都可以使用这类优化。例如，我们现在需要找到没有演员的所有电影：

mysql>select film.film_id

         ->from sakila.film

         ->left outer join sakila.film_actor using(film_id)

         ->where film_actor.film_id is null;

这个查询将会过滤掉所有有演员的电影。每一步电影可能会有很多演员，但是上面的查询一旦找到任何一个，就会停止并立刻判断下一部电影，因为只要有一名演员，那么WHERE条件则会过滤掉这类电影。类似这种“不同值/不存在”的优化一般可用于DISTINCT、NOT EXIST（）或者LEFT JOIN类型的查询。

等值传播

如果两个列的值通过等式关联，那么MySQL能够把其中一列的WHERE条件传递到另一列上。例如，我们看卡下面的查询：

mysql>select film.film_id

         ->from sakila.film

         ->inner join sakila.film_actor using(film_id)

         ->where film.film_id > 500

因为这里使用了film_id字段进行等值关联，MySQL知道这里的WHERE子句不仅适用于film表，而且对于film_actor表同样适用。如果使用的是其他的数据库管理系统，可能还需要手动通过一些条件来告知优化器这个WHERE条件适用于两个表，那么写法就会如下：

....where film.film_id > 500 and film_actor.film_id > 500

在MySQL中这是不必要的，这样写反而让查询更难维护。

列表IN()的比较

在很多数据库系统中，IN（）完全等同于多个or条件的子句，因为这两者是完全等同的。在MySQL中这点是不成立的，MySQL将IN（）列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（log n）复杂度的操作，等价地转换成OR查询的复杂度为O(n)，对于IN（）列表中有大量取值的时候，MySQL的处理速度将会更快。

上面列举的远不是MySQL优化器的全部，MySQL还会做大量其他的优化，即使本章全部用来描述也会篇幅不足，但上面的这些例子已经足以让大家明白优化器的复杂性和智能型。如果说从上面这段讨论中我们应该学到什么，那就是“不要自以为比优化器更聪明”。最终你可能会占点便宜，但是更有可能使查询变得更加复杂而难以维护，而最终的收益却为零。让优化器按照他的方法工作就可以了。

当然，虽然优化器已经很智能了，但是有时候也无法给出最优的结果。有时候你可能比优化器更了解数据，例如，由于应用逻辑使得某些条件总是成立；还有时，优化器缺少某种功能特性，如哈希索引；再如前面提到的，从优化器的执行成本角度评估出来的最优执行计划，实际运行中可能比其他的计划更慢。

如果能够确认优化器给出的不是最佳选择，并且清楚背后的原理，那么也可以帮助优化器做进一步的优化。例如，可以在查询中添加hint提示，也可以重写查询，或者重新设计更优的库表结构，或者添加更合适的索引。