MySQL之查询性能优化（二）

查询执行的基础

当希望MySQL能够以更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。MySQL执行一个查询的过程，根据图1-1，我们可以看到当向MySQL发送一个请求时，MySQL都做了什么：

图1-1   查询执行路径

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

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

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

这种协议让MySQL通信简单快速，但是也从很多地方限制住了MySQL。一个明显的限制是，这意味着无法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它。这就像来回的抛球游戏：任何时刻只有一个人能控制球，而且只有控制球的一方才能将球抛回去（发送消息）。

客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候，参数max_allowed_packet 就特别重要了，如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误。一旦客户端发送了请求，它能做的事情，就只是等待结果了。

相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后，就粗暴的断开连接，都不是好主意。这也是在必要的时候一定要在查询语句中加上LIMIT限制的原因。

换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个数据拉取的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也无法让服务器停下来。

多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等待所有的数据都已经发送给客户端才能释放这条查询所占的资源，所有接受全部结果通常可以减少服务器压力，让查询能够早点结束、早点释放相应的资源。

当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取的数据，而实际上都是从这个库函数的缓冲读取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能尽早的开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。

查询状态

对于一个MySQL的连接，或者说是一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。有很多种方式能查看当前的状态，最简单的是使用SHOW FULL PROCESSLIST 命令（该命令返回结果中的Command列就表示当前的状态）。在一个查询的生命周期中，状态会变化很多次。MySQL官方手册对这些状态值的含义有最权威的解释，下面将这些状态列出来，并做一个简单的解释。

• Sleep：线程正在等待客户端发送新的请求
• Query：线程正在执行查询或者正在将结果发送给客户端。
• Locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎实现的锁，例如Innodb的行锁，并不会体现在该线程状态中。对于myisam来说这是一个比较典型的状态，但在其他没有行锁的引擎中也会长出现。
• Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
• Copying to tmp table[on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是做Group by 操作，要么是文件排序操作，或者是UNION 操作。如果这个状态后面还有on disk 标记，那么表示MySQL正在将一个内存临时表放到磁盘上。
• Sorting result：线程正在对结果集进行排序。
• Sending data：这表示多种情况：线程可能是在多个状态之间传送数据，或者结果集，或者在向客户端返回数据。
• 了解这些状态的基本含义非常有用，这可以让你更快的了解当前谁正在持球。在一个繁忙的服务器上，可能会看到大量的不正常状态，例如statistics正在占用大量的时间。这通常表示，某个地方有异常了。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况查询会进入下一个阶段的处理。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再按照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误（例如语法错误）都可能终止查询。这里不打算详细介绍MySQL内部实现，而只是选择性的介绍其中几个独立的部分，在实际中，这几部分可能一起执行也可能单独执行。我们的目的是帮助大家理解MySQL是如何执行查询的，以便写出更优秀的查询。

语法解析器和预处理

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

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

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

查询优化器

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

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

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM actor;
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW STATUS LIKE 'Last_query_cost';       
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 20.249000 |
+-----------------+-----------+
1 row in set (0.10 sec)

　　

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

很多原因会导致MySQL优化器选择错误的执行计划，如下所示：

• 统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确的统计信息。
• 执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精确，优化器给出的执行计划也可能不是最优的。例如有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更小。因为如果这些页面都是顺序读取或者这些页面都已经在内存中的话，那么它的访问成本将会很小。MySQL层面并不知道那些页面在内存中，那些在磁盘上，所以查询实际执行的过程中到底需要多少次物理IO是无法得知的。
• MySQL的最优化可能和你想的最后不一样。你可能希望执行时间尽可能的短，但是MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。所以，这里我们看到的根据执行成本来选择执行计划并不是完美的模型。
• MySQL从不考虑其他并发的执行查询，这可能会影响到当前的查询速度。
• MySQL也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则，例如，如果存在全文所搜的MATCH()子句，则在全文索引的时候就使用全文索引。即使有时候使用别的索引和WHERE条件可以远比这方式要快，MySQL仍然会使用对应的全文索引。
• MySQL不会考虑其控制的操作成本，例如执行存储过程或者用户自定义函数的成本。
• 后面我们还会看到，优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

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

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

在执行语句和存储过程的时候，动态优化和静态优化的区别非常重要。MySQL对查询的静态优化只需要做一次，但对查询的动态优化规则在每次执行的时候都需要评估，有时候甚至在查询的过程中重新优化（例如，在关联操作过程中，范围检查的执行计划会针对每一行重新评估索引。可以通过EXPLAIN 执行计划红的Extra列是否有“range checked for each record” 来确认这一点。该执行计划还会增加select_full_range_join 这个服务器变量的值）。

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

重新定义关联表的顺序

数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能。

将外连接转化成内连接

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

使用等价变换规则

MySQL可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如（5=5 AND a>5） 将被改写成a>5 。类似的，如果有（a<b AND b=c） AND a=5则会改写成b>5 AND b=c AND a=5。这些条件对于我们编写条件语句很有用。

优化COUNT() ,MIN()和MAX()

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

预估并转化为常数表达式

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

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

mysql> EXPLAIN SELECT film.film_id, film_actor.actor_id FROM film inner join film_actor USING(film_id) WHERE film.film_id = 1;
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | film       | NULL       | const | PRIMARY        | PRIMARY        | 2       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | film_actor | NULL       | ref   | idx_fk_film_id | idx_fk_film_id | 2       | const |   10 |   100.00 | Using index |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

　　

MySQL将分两步来执行这个查询。第一步先从film表找到需要的行。因为在film_id 字段上有主键索引，所以MySQL优化器知道这只会返回一条数据。因为优化器已经明确指定有多少个值需要做索引查询，所以这里的表访问类型是const 。

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

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

覆盖索引扫描

当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行。 

子查询优化

MySQL在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问。

提前终止查询

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

mysql> EXPLAIN SELECT film_id from film WHERE 1 = -1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.01 sec)

　　

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

SELECT film.film_id FROM film left outer join film_actor using(film_id) WHERE film_actor.film_id IS NULL;

　　

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

等值传播

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

SELECT film_id FROM film INNER JION film_actor USING(film_id) WHERE 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提示，也可以重写查询，或者重新设计更优的库表结构，或者添加更合适的索引。

数据和索引的统计信息

MySQL架构由多个层次构成，在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（也可以按照不同的格式存储统计信息）。某些引擎，例如Archive引擎，则根本没有存储任何统计信息。

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

MySQL如何执行关联查询

MySQL中的“关联”一词所包涵的意义比一般意义上理解的要更广泛。总的来说，MySQL认为任何一个查询都是一个“关联”，并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都有可能是关联。

我们先来看一个UNION查询的例子，对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表来完成UNION查询。在MySQL的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

当前MySQL关联执行得策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询结果中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更更多行以后，MySQL返回到上一层次的关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。

按照这样的方式查找第一个表的记录，再嵌套查询下一个关联表，然后回溯到上一个表，在MySQL中时通过嵌套循环的方式实现的，请看下面的例子中的简单查询：

SELECT tbl1.col1, tbl2.col2 FROM tbl1 INNER JOIN tbl2 USING (col3) WHERE tbl1.col1 in (5,6);

　　

假设MySQL按照查询中的表顺序进行关联操作，我们则可以用下面的伪代码表示MySQL将如何完成这个查询：

outer_iter = iterator_over tbl1 where col1 in(5,6)
    outer_row = outer_iter.next
    while outer_row
        inner_iter = iterator over tbl2 where col3=outer_row.col3
        inner_row = inner_iter.next
        while inner_row
            output[outer_row.col1,inner_row.col2]
            inner_row = inner_iter.next
        end
		out_row = outer_iter.next
    end

　　

上面的执行计划对于单表查询和多表关联查询都适用，如果是一个单表查询，那么只需要完成上面的外层的基本操作。对于外连接和上面的执行过程任然适用。例如，我们将上面的查询修改如下：

SELECT tbl1.col1 ,tbl2.col2 FROM tbl1 LEFT OUTER JOIN tbl2 USING(col3) WHERE tbl1.col1 IN (5,6)

　　

对应的伪代码如下：

outer_iter = iterator_over tbl1 where col1 in(5,6)
    outer_row = outer_iter.next
    while outer_row
        inner_iter = iterator over tbl2 where col3=outer_row.col3
        inner_row = inner_iter.next
        while inner_row
            output[outer_row.col1,inner_row.col2]
            inner_row = inner_iter.next
        end
		out_row = outer_iter.next
    end

　　

另一种可视化查询执行计划的方法是根据优化器执行的路径绘制出对应的“泳道图”。如图1-2所示，绘制了前面示例中内连接的泳道图，请从左至右，从上至下地看这幅图。

图1-2   通过泳道图展示MySQL如何完成关联查询

 

从本质上说，MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到的子查询时，先执行子查询并将其结果放到一个临时表中（MySQL的临时表时没有任何索引的，在编写复杂的子查询和关联查询的时候需要注意这一点，这一点对UNION查询也一样），然后将这个临时表作为一个普通的表对待（正如其名“派生表”）。MySQL在执行UNION操作时也使用类似的临时表，在遇到右外连接的时候，MySQL将其改写成等价的左外连接，简而言之，当前版本的MySQL会将所有的查询类型都转换成类似的执行计划（在MySQL5.6和MariaDB中有了重大改变，这两个版本都引入了更加复杂的执行计划）。

不过，不是所有的查询都可以转换成上面的形式。例如，全外连接就无法通过嵌套循环和回溯的方式完成，这时当发现关联表中没有找到任何匹配行的时候，则可能是因为关联恰好是从一个没有任何匹配的表开始。这大概也是MySQL并不支持全外连接的原因。还有些场景，虽然可以转换成嵌套循环的方式，但是效率却非常差。

执行计划

和很多其他关系型数据库不同，MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED后，在执行SHOW WARNINGS，就可以看到重构出的查询。

任何多表查询都可以使用一棵树来表示，例如，可以按照图1-3执行一个四表的关联操作：

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

在计算机科学中，这被称为一棵平衡树。但是，这并不是MySQL执行查询的方式。正如我们之前介绍的，MySQL总是从一个表开始一直嵌套循环、回溯完成所有表的关联。所以，MySQL的执行计划总是如图1-4所示，是一棵左侧深度优先树：

图1-4   MySQL如何实现多表关联

关联查询优化器

MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联的顺序。通常多表关联的时候，可以有多重不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

下面的查询可以通过不同顺序的关联最后都获得相同的结果：

SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name FROM film INNER JOIN film_actor USING (film_id) INNER JOIN actor USING(actor_id);

　　

容易看出，可以通过一些不同的执行计划来完成上面的查询。例如：MySQL可以从film表开始，使用film_actor 表的索引film_id来查找对应的actor_id的值，然后再根据actor表的主键找到对应的记录。Oracle用户会用下面的术语描述：film表作为驱动表现查找film_actor表，然后以此结果为驱动表在查找actor表。这样的效率应该会不错。我们再使用EXPLAIN 看看MySQL如何执行这个查询：

mysql> EXPLAIN SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name FROM film INNER JOIN film_actor USING (film_id) INNER JOIN actor USING(actor_id)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 200
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: ref
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY
key_len: 2
ref: sakila.actor.actor_id
rows: 27
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.film_id
rows: 1
filtered: 100.00
Extra: NULL
3 rows in set, 1 warning (0.00 sec)

　　

这和我们前面给出的执行计划完全不同。MySQL从actor表开始，然后与我们前面的计划按照相反的顺序进行关联。这样做是否效率更高呢？我们来看看，先使用STRAIGHT_JOIN 关键字，按照我们之前的顺序执行，这里是对应的EXPLAIN输出结果：

mysql> EXPLAIN SELECT film.film_id, film.title, film.release_year,actor.actor_id, actor.first_name, actor.last_name FROM film STRAIGHT_JOIN film_actor USING (film_id) STRAIGHT_JOIN actor USING(actor_id)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 5
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.film_actor.actor_id
         rows: 1
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)

　　

我们来分析一下为什么MySQL会将关联顺序倒转过来：可以看到，关联顺序倒转后的第一个关联表只需要扫描很少的行数。在两种关联顺序之下，第二个和第三个关联表都是根据索引查询的，速度都很快，不同的是需要扫描的索引项的数量不同：

• 将film表作为第一个关联表时，会找到1000条记录，然后对film_actor表和actor表进行嵌套循环查询。
• 如果MySQL选择首先扫描actor表，只会返回200条记录然后进行后面的嵌套循环查询。

换句话说，倒转关联顺序会让查询进行更少的嵌套循环和回溯操作。为了验证优化器的选择是否正确，我们单独执行这两个查询，并且看看对应的Last_query_cost状态值。我们看到倒转的关联顺序的预估成本是2819，而原来的查询的预估成本是2529。

这个简单的例子主要想说明MySQL是如何选择合适的关联顺序让查询执行的成本尽可能低的。重新定义关联表的顺序是优化器非常重要的一部分功能。不过有的时候，优化器可能给出的不是最优的关联顺序。这时可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为最优的关联顺序执行，不过老实说，人的判断很难那么精准，绝大多数的时候，优化器做出的选择都比普通人判断的更要准确。

关联优化器会尝试在所有关联顺序中选择一个成本最小的来生成执行计划树。如果可能，优化器会遍历每一个表，然后逐个做循环嵌套计算每一刻可能的执行计划树的成本，最后返回一个最优的执行计划。

不过，糟糕的是，如果有超过n给表的关联，那么需要检查n的阶乘中关联顺序，我们称之为所有可能的执行计划的“搜索空间” ，搜索空间的增长速度非常快——例如，若是有10个表的关联，那么共有362800种可能不同的关联顺序，当搜索空间非常大的时候，优化器不可能逐一评估每一种关联顺序的成本。实际上，当需要关联的表超过optimizer_search_depth的限制的时候，就会攥着“贪婪”搜索模式了。

在MySQL这些年的发展过程中，优化器积累了很多启发式的优化策略来假设执行计划的生成，绝大多数情况下，这都是有效的，但因为不会计算每一种关联顺序的成本，所以偶尔也会选择一个不是最优的执行计划。

有时，各个查询的顺序不能随意安排，这时关联优化器可以根据这些规则大大减少索引空间，例如，左连接、相关子查询。这是因为，后面的表的查询需要依赖于前面表的查询结果。这种依赖关系通常可以帮助优化器大大减少需要扫描的执行计划数量。

排序优化

无论如何排序都是一个成本很高的操作，所以从性能的角度考虑，应该尽可能避免排序或者尽可能避免对大量数据进行排序。

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程同一称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此。

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

MySQL有如下两种排序法：

两次传输排序（旧版本使用）：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机IO，所以两次数据传输的成本非常高。当使用的是MyISAM表的时候，成本可能会更高，因为MyISAM使用系统调用进行数据的读取（MyISAM非常依赖操作系统对数据的缓存）。不过这样做的优点是：在排序的时候存储尽可能少的数据，这就让“排序缓冲”尽可能容纳更多的行数进行排序。

单次传输排序（新版本使用）：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。因为不再需要从数据表中读取两次数据，对IO密集型应用，这样做的效率高了很多。另外相比两次传输排序，这个算法只需要一次顺序IO读取所有数据，而无需任何随机IO。缺点是，如果需要返回的列非常多、非常大，会额外占用大量的空间，而这些列对排序操作本身来说是没有任何作用的。因为单条排序记录很大，所以可能会有更多的排序块需要合并。

很难说哪个算法效率更高，两种算法都有各自最好和最糟的场景。当查询需要所有的列的总长度不超过参数max_length_for_sort_data时，MySQL使用“单次传输排序”，可以通过调整这个参数来影响MySQL排序算法的选择。

MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象中要大得多。原因在于MySQL排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串。如果是VARCHAR列则需要分配其完整长度，如果使用UTF-8字符集，那么MySQL将会为每个字符预留三个字节。

在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表时就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段会有Using filesort。除此之外的所有情况，MySQL都会将关联的结果存放在一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下，在MySQL的EXPLAIN结果的Extra字段可以看到Using temporary;Using filesort。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。

MySQL5.6在这里做了很多重要的改进。当只需要返回部分排序结果的时候，例如使用了LIMIT子句，MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。

相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler API”的接口。查询中的每一个表由一个handler的实例表示。前面我们有意忽略了这点，实际上，MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。

存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像“搭积木”一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条目的下一个条目的功能，有了这两个功能我们就可以完成全索引扫描的操作了。这种简单的接口模式，让MySQL的存储引擎插件式架构成为可能，但也会给优化器带来了一定的限制。

并不是所有的操作都由handler完成。例如，当MySQL需要进行表锁的时候。handler可能会实现自己的级别的、更细粒度的锁，如InnoDB就实现了自己的行级锁，但这并不能代替服务器层的表锁，如果是所有存储引擎共有的特性则有服务器层实现，比如时间和日期函数、视图、触发器等。

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

返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。

如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放在查询缓存中。

MySQL将结果集返回给客户端是一个增量、逐步返回的过程。例如，我们回头看看前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。

这样处理有两个好处：服务器端无需存储太多结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输。