第九章 解析器与优化器

MySQL服务器接收的查询语句为SQL格式。一旦接收到查询，首先需要对其进行解析，即将本质上的文本格式转换为内部二进制结构的组合，以便优化器能够轻松处理。

在此语境下，当我们提及优化器时，指的是服务器中负责创建和执行计划，以检索查询所请求记录的模块。优化器会确定表的连接顺序、读取记录的方式（例如，从索引读取还是全表扫描）以及使用哪些键。其目标是以尽可能短的时间返回查询结果。

在本章中，我们将详细探讨解析器和优化器。

解析器

与许多其他解析器一样，MySQL的解析器由两部分组成：词法扫描器和语法规则模块。词法扫描器将整个查询分解为标记（不可分割的元素，如列名），而语法规则模块会找出能生成该标记序列的SQL语法规则组合，并执行与这些规则相关的代码。最终会生成一个解析树，供优化器使用。

与一些将查询的文本表示转换为字节码的解析器不同，MySQL的解析器直接将其转换为程序内存中的内部互连C/C++结构。

例如，假设服务器接收到以下查询：

SELECT count(*), state FROM customer GROUP BY state

词法扫描器会检查查询字符流，将其分解为标记，并识别每个标记。它找到的标记如下：

SELECT
count (
*
)
,
state
FROM
customer
GROUP BY
state

每个标记都会被赋予一种类型，例如关键字、字符串字面量、数字、操作符或函数名。语法规则模块将标记流与一组规则进行匹配，找到正确的规则，在这个例子中是select规则（见sql/sql_yacc.yy）。它会相应地初始化解析树结构，随后会导致sql/sql_select.cc中的mysql_select()函数被执行。

解析器有两个主要目标，重要性不分先后。第一，它必须速度极快。许多MySQL安装需要支持每秒处理数千个查询的负载。如果仅解析操作就耗时一毫秒，这是不可能实现的。第二，生成的解析树必须以一种能让优化器高效访问数据的方式提供信息。优化器需要快速访问WHERE子句、表、字段和键列表、ORDER BY和GROUP BY表达式、子查询结构以及其他数据的各个部分。尽管要实现这两个目标很困难，但到目前为止，MySQL开发团队在很大程度上已经成功完成了这项任务。

词法扫描器

许多开源项目使用非常流行的工具GNU Flex来生成词法扫描器。程序员只需提供一组字符分类准则，Flex就会生成用于扫描的C代码，并可与代码的其他部分集成。

MySQL则不同，它拥有自己的词法扫描器，以获得更高的性能和灵活性。手写的标记标识符可以通过优化进行微调，而这对于生成的代码来说是不可能的。此外，它还可以编写为具有上下文敏感性来识别标记。

在服务器编译之前，一个名为gen_lex_hash（见sql/gen_lex_hash.cc）的特殊工具会生成一个非常高效的关键字查找哈希表，然后与代码的其他部分一起编译。生成的哈希表是完美的，即不存在哈希冲突。扫描器（见sql/sql_lex.cc）会将每个标记标记为关键字、函数名、特定类型的数字或语法规则中有意义的其他特殊符号。

关键字列表位于sql/lex.h中的symbols[]数组中。函数列表包含在同一文件的sql_functions[]数组中。

请注意，在5.1版本的后续发行版中有一个变化。大多数内置函数从sql_functions[]数组中移出，放入了native_functions_hash中。现在，内置函数由语法规则模块查找，而不是词法扫描器。

词法扫描器的入口点是sql/sql_lex.cc中的yylex()函数。这个函数名具有特殊意义：它需要与语法规则模块生成器GNU Bison兼容，Bison期望通过调用这个名称的函数来检索标记。

语法规则模块

这个模块通常被称为解析器，但为了将它与服务器的词法扫描器部分区分开来，我将其称为语法规则模块。与许多其他开源项目一样，语法规则模块是使用解析器生成工具GNU Bison生成的。如果你打算修改MySQL语法，或者只是想更好地理解解析过程，建议你熟悉一下Bison。你可以从Bison手册（由自由软件基金会出版）中了解更多相关内容，也可以在网上访问http://www.gnu.org/software/bison/manual 。

语法规则在sql/sql_yacc.yy中定义。Bison处理这个文件以生成sql/sql_yacc.cc。语法规则模块的入口点是yyparse()函数。

解析树

解析器执行的最终结果是解析树。可以想象，SQL语法的复杂性要求有一个同样复杂的结构，以便有效地存储执行每一条可能的SQL语句所需的信息。虽然在本章的篇幅内，甚至可能在一本书的篇幅内，都无法全面描述解析树的所有元素，但我将尝试简要概述其要点。

解析树由LEX类型的对象表示，LEX是sql/sql_lex.h中st_lex结构的类型定义别名。LEX有许多成员。我们将关注其中两个：enum_sql_command sql_command和SELECT_LEX select_lex。

sql_command显示我们正在执行的SQL查询类型，无论是select、update、insert、delete还是其他查询类型。这个字段的值在mysql_execute_command()（见sql/sql_parse.cc）中用于将执行流导向与该特定查询类型相关的函数。

select_lex成员属于SELECT_LEX类型，SELECT_LEX是st_select_lex类的类型定义别名，同样在sql/sql_lex.h中定义。这个类有许多成员，包含各种查询细节的信息，如WHERE子句、表列表、字段列表、优化器提示信息、子查询对其他SELECT_LEX实例的交叉引用、ORDER BY、GROUP BY和HAVING表达式，以及许多其他详细信息。我们将关注Item* where成员，它是WHERE子句树的根节点，因为优化器所需的大部分信息都从WHERE子句中提取。

在sql/item.h中定义的Item类是所有其他Item_类的基类，这些类表示表达式树的节点。这个类家族涵盖了算术运算（例如，加法、减法、乘法、除法）、各种SQL函数、逻辑操作符（如AND和OR）、对表字段的引用、返回单行的子查询，以及在WHERE、HAVING、GROUP BY、ORDER BY或select查询的字段列表中找到的SQL表达式的每个其他元素。

Item有几个名称以val_开头的方法。方法名的其余部分取决于返回值的类型。例如，如果返回值是整数，方法名就是val_int()。优化器随后会使用LEX_SELECT的where成员中包含的Item来为其检查的记录组合构建一个过滤表达式。过滤表达式通过调用Item::val_int()进行求值。如果它返回1，则认为该记录满足约束条件，并包含在结果集中；否则，该记录将被丢弃。

如果优化器无法对WHERE子句进行任何改进，过滤表达式将与原始WHERE子句相同。否则，它可能会被重写，以消除不必要的计算，并尽可能更改约束条件，为使用键创造条件。它也可能包含HAVING子句的部分内容。

图9-1展示了一个WHERE子句的表达式树示例。该示例中的表达式可能来自以下查询：

SELECT count(*) FROM customer WHERE lname='Jones' AND age BETWEEN 25 AND 30

图 9-1 典型 WHERE 子句的解析树

优化器

为了帮助你理解优化器的作用，考虑以下查询：

SELECT c.first_name,c.last_name,c.phone,p.name,p.price
FROM customer c,orders o, product p
WHERE c.id = o.customer_id AND o.product_id = p.id AND o.payment_status = 'FAILED'
ORDER BY c.last_name,c.first_name

我们希望检索所有因某种原因支付失败的订单的客户名字、姓氏、电话号码以及产品名称和价格。

一种简单的方法是遍历customer表的所有记录，对于customer表的每一条记录，再遍历order表的所有记录，然后对于这两个表记录的每一种组合，遍历product表的每一条记录。对于每一个三条记录的组合，检索过程会检查该组合是否匹配WHERE子句，只保留匹配的组合。之后，检索过程会对匹配的记录进行排序，并将它们返回给客户端。

可以看出，这种方法效率不高。假设每个表有10,000条记录。优化器将不得不检查10,000×10,000×10,000种组合，即1万亿种组合。如果处理器每秒能够检查100万条记录，那么这个查询将花费100万秒，即超过11天。

另一方面，假设我们在customer.id、orders.payment_status和product.id上有键，并且customer.id和product.id上的键是唯一的。由于我们有一个可能具有限制性的约束条件，可以通过orders.payment_status消除大量记录，因此首先使用payment_status键从orders表中查找所有匹配的记录是合理的。对于这些记录中的每一条，我们使用customer表id列上的键检索匹配的记录，同时使用product表id列上的键检索匹配的记录。现在，我们需要检查的记录组合数量与orders表中payment_status值为'FAILED'的记录数量相同。即使在我们10,000条记录的表中碰巧是每一条记录，我们现在也只需要检查10,000种记录组合。

虽然使用键确实增加了创建每个组合所需的时间，但最终这种开销是值得的。根据标准的MySQL优化器成本估算模型，对于同一个表，每次键访问所需的时间是扫描访问的三倍。因此，在简单方法中，我们创建一个记录组合的成本是1+1+1=3，而改进后的方法对于相同操作的成本是3+3+3=9。忽略检查组合的时间，我们现在每秒只能处理333,333种组合，而不是100万种。然而，我们现在需要处理的组合不超过10,000种，我们的查询应该花费不到0.03秒，而不是11天。

因此，很明显，优化器不仅必须找到一种方法来返回查询所请求的记录，而且必须以最优的方式进行，或者至少能够提供令人满意的性能。这比仅仅返回结果要困难得多，因此这个模块被称为优化器是合理的。

MySQL的优化器有几个重要任务：

• 确定可以用于从表中检索记录的键，并为每个表选择最佳的键。
• 对于每个表，决定全表扫描是否比基于键读取更好。如果有很多记录与键值匹配，键的优势就会降低，全表扫描会变得更快。
• 当查询中存在多个表时，确定表的连接顺序。
• 重写WHERE子句以消除无效代码，减少不必要的计算，并尽可能更改约束条件，为使用键创造条件。
• 从连接中删除未使用的表。
• 确定键是否可用于ORDER BY和GROUP BY。
• 尝试用内连接替换外连接。
• 尝试简化子查询，并确定其结果可以在多大程度上被缓存。
• 合并视图（将视图引用展开为宏）。

优化器算法基础

在MySQL优化器的术语中，每个查询都是一组连接。这里的“连接”一词比在SQL命令中的使用范围更广泛。对单个表的查询是一种退化的连接。虽然我们通常不会将从单个表读取记录视为连接，但用于常规连接的相同结构和算法完全适用于解析单表查询。

没有子查询或UNION的简单查询仅由一个连接组成。包含无法优化的子查询的查询以及UNION查询将涉及多个连接。有些子查询可能需要所谓的递归连接：在执行一个连接时，优化器需要为连接的每一行执行一个子查询，这会导致其自身的连接。尽管如此，连接仍是优化器工作的基本单元。在源代码中，一个连接与sql/sql_select.h中定义的连接描述符类JOIN相关联。每个连接都通过调用sql/sql_select.cc中的mysql_select()函数启动。

因此，本节描述的过程分为两部分：首先，优化器确定最佳连接顺序，然后通过嵌套循环完成连接。

连接本质上是表子集的笛卡尔积。每个子集是通过基于单个键值、键范围（或一组键范围）、全索引扫描或全表扫描从表中读取记录获得的。然后，如有必要，使用WHERE子句中的约束条件删除记录。

优化器选择记录访问方法，并将表按其认为能最小化成本的顺序排列，而成本通常与它必须检查的记录组合总数成正比。查询优化问题可以分解为两个部分：第一，对于给定的连接顺序，为每个表找到最佳访问路径；第二，一旦具备这种能力，在短时间内找到最佳连接顺序，或者至少是一个相当好的连接顺序。

第一个问题由sql/sql_select.cc中的best_access_path()函数解决。访问路径定义了优化器是要基于键读取、全表扫描（ALL）还是扫描键（index）。如果执行键读取，它会定义如何使用该键，例如，基于一个值读取一条记录（eq_ref）、可能基于一个值读取多条记录（ref）或基于一个值范围（range）。best_access_path()函数会传入部分计划（连接顺序）的预先计算的访问路径。因此，旧的部分计划的最佳访问路径已经计算出来，优化器只需要为新添加的表计算它。旧部分计划中表的选择和顺序对新表的最佳访问路径有很大影响。例如，在一种情况下，旧表可能包含一个列，其值可用于执行键读取，而在另一种情况下，这种可能性可能不存在，这就需要对新表进行全表扫描。

寻找最佳连接顺序的剩余问题可以通过两种方式解决：穷举搜索（sql/sql_select.cc中的find_best()函数）和贪心搜索（sql/sql_select.cc中的greedy_search()函数）。穷举搜索会检查所有可能的表组合，并找到最佳计划。然而，这可能需要很长时间。贪心搜索的工作方式如下：首先尝试查询中n个表中optimizer_search_depth个表（optimizer_search_depth是一个服务器配置变量）的所有可能组合，并找到最佳组合。从结果集中取出第一个表，并将其放在部分连接顺序的首位。然后检查剩余n - 1个表中optimizer_search_depth个表的所有可能组合。对于每个测试的组合，将其附加到现有的部分计划中并评估成本。选择成本最低的组合，并将该组合中的第一个表放在部分计划的下一个位置。重复此过程，直到剩余表集的基数达到optimizer_search_depth。

穷举搜索和贪心搜索都有优化措施，即如果当前部分组合的成本超过了目前找到的最佳成本，则停止对该路径的探索。因此，虽然理论上穷举搜索最多可以检查n!种组合，贪心搜索最多可以检查optimizer_search_depth! * (n - optimizer_search_depth)种组合，但在实践中，这些数字通常会大幅减少。

因此，虽然贪心搜索可能并不总是能找到最佳计划，但它的复杂度是可控的，并且在性能上优于穷举搜索。实际上，如果优化器找到最佳计划所节省的执行时间被寻找计划所花费的时间抵消，那么找到最佳计划也就没有意义了。

有关更多详细信息，请查看sql/sql_select.cc中的make_join_statistics()、choose_plan()、optimize_key_use()、best_access_path()、get_best_combination()、create_ref_for_key()、find_best()和greedy_search()函数。

在5.0版本之前，只有穷举搜索可用。5.0版本实现了贪心搜索。

确定连接顺序后，优化器开始执行连接。连接通过一系列嵌套循环执行，从第一个表开始。对于第一个表的每一条记录，优化器遍历第二个表以创建组合。对于第二个表的每一条记录，优化器又会遍历第三个表的每一条记录，依此类推，为最内层循环的每次迭代创建一个记录组合。

然后，将该组合与查询的WHERE子句（或者更准确地说，与从原始WHERE子句生成的优化过滤表达式）进行比较。例如，如果WHERE子句是lname='Johnson' and age=31+1，过滤表达式就变为lname='Johnson' and age=32。你可能想知道为什么会有人以未优化的形式编写这样的约束条件。在许多应用程序中，查询通常由复杂的业务逻辑算法生成，这些算法经常生成人们永远不会手动编写的未优化查询。此外，当列引用被常量替换时，查询重写可能会产生这样的查询。因此，像这里讨论的这种简单优化通常会带来显著的速度提升。

请注意，WHERE子句中的表达式会尽可能早地进行求值。例如，如果WHERE子句中的某个条件仅涉及第一个表，则在从第一个表读取一行之后、将其与第二个表连接之前对其进行求值（见sql_select.cc中的make_cond_for_table()函数）。

匹配的记录会被传递到与连接相关的结果处理对象的send_data()方法。结果处理对象可能会将记录发送给客户端，写入文件或临时表，或者传递到其他地方进行进一步处理。结果处理对象类型是select_result类（见sql/sql_class.h和sql/sql_class.cc）的派生类。

使用EXPLAIN理解优化器

MySQL的EXPLAIN命令用于让优化器展示其查询计划。查询计划描述了优化器为解决查询所采取的操作。例如，先从orders表开始；根据payment_status键读取记录；对于orders表中的每条记录，在customer表中根据id键查找相应记录；对于每一个(order, customer)组合，再使用id键在product表中查找对应的记录；使用生成的(order, customer, product)组合来更新一个临时汇总表；完成后，遍历临时表以获取GROUP BY的结果。

研究EXPLAIN对一个查询的输出结果，可以让我们学到很多东西。EXPLAIN展示了连接中表的处理顺序、理论上可以使用哪些键、实际使用了哪些键以及如何使用、是否通过WHERE子句的约束提前排除了一些记录、每个连接子集的预估大小、是否使用了临时表、记录是否已经按键顺序读取还是需要为ORDER BY进行额外排序，以及其他与查询优化相关的信息。

让我们从一个EXPLAIN的示例开始。假设我们有如下查询：

SELECT  count(*)  FROM  orders o,  customer  c
WHERE  o.customer_id  =  c.id  AND  c.state  =   'UT'

为了理解该查询计划，在MySQL命令行客户端中执行以下命令：

EXPLAIN  SELECT  count(*)  FROM  orders  o,  customer  c WHERE  o.customer_id  =  c.id  AND  c.state  =   'UT'  \G

查询末尾的\G选项用于请求垂直显示结果集。EXPLAIN的输出包含很多列，默认的水平输出模式常常导致内容难以阅读。

EXPLAIN产生的输出如下：

1  ***************************  1. row  ***********************
2                       id:  1
3       select_type:  SIMPLE
4                table:  c
5                   type: ref
6   possible_keys:  PRIMARY,state
7                     key:  state
8              key_len:  2
9                     ref:  const
10                   rows:  12
11                Extra:  Using where
12  ***************************  2. row  **********************
13                     id:  1
14     select_type:  SIMPLE
15               table: o
16                  type: ref
17  possible_keys:  customer_id
18                    key:  customer_id
19            key_len: 4
20                   ref:  book.c.id
21                  rows:  5
22                Extra: Using  index

第4行的输出告诉我们，优化器将首先检查customer表。它可以选择根据PRIMARY键或state键读取（第6行），并选择了state键（第7行）。查询state键时会提供一个键值，但结果可能包含多条记录（第5行）。优化器将使用该键的前2个字节，在这种情况下，这就是整个键（第8行）。使用的键值是直接在WHERE子句中提供的常量，或者通过其他方式获得，而不是某个可能变化的其他列的值（第9行）。优化器估计键查找将匹配12条记录（第10行）。从该表检索到的记录将检查是否与WHERE子句匹配（第11行）。

第15行显示连接中的第二个表是orders表。只能使用一个键：customer_id（第17行），并且确实使用了该键（第18行）。使用该键的前4个字节（第19行），在这种情况下，这就是整个键。与customer表中state键的访问方法类似，查询customer_id键时会提供一个键值，结果可能包含多条记录（第16行）。然而，这次键的值不再是常量。它取自当前处理的customer表记录的id字段（第20行）。随着优化器检索不同的customer表记录，该值会发生变化。请注意，只有在连接顺序中customer表排在orders表之前时，这种优化策略才可行。因此，我们说orders表依赖于customer表。

优化器估计，对于连接顺序中前面的表（在这种情况下只有一个表，即customer表）的每个记录组合，平均需要检查orders表中的5条记录（第21行）。由于优化器只需要customer_id的值，因此仅读取键的值而不检索整个记录就足够了（第22行）。

为什么在这个示例中优化器选择这样做呢？为了帮助你理解，我们将强制它选择不同的查询计划：

EXPLAIN    SELECT  count(*)  FROM orders o  STRAIGHT_JOIN  customer  c WHERE  o.customer_id  =  c.id  AND  c.state  =   'UT'  \G

STRAIGHT_JOIN指令告诉优化器，在其考虑的所有可能连接顺序中，orders表必须排在customer表之前。在这种情况下，STRAIGHT_JOIN指令只留下一种可能的组合：先orders表，后customer表。EXPLAIN产生的输出如下：

1  *************************  1. row  *************************
2                       id:  1
3       select_type:  SIMPLE
4                table: o
5                   type:  index
6   possible_keys:  customer_id
7                      key:  customer_id
8              key_len: 4
9                      ref:  NULL
10                  rows:  19566
11                Extra: Using  index
12  ************************  2. row  *************************
13                     id:  1
14     select_type:  SIMPLE
15               table:  c
16                  type: eq_ref
17  possible_keys:  PRIMARY,state
18                       key:  PRIMARY
19            key_len: 4
20                   ref:  book.o.customer_id
21                  rows:  1
22                Extra:  Using where

优化器扫描orders表中的customer_id索引（第5行和第7行），并估计它将匹配19,566条记录（第10行）。对于orders表中每个匹配的记录，会使用主键在customer表中查找相应记录（第18行），在这种情况下，每个给定值只能有一个匹配项（第16行）。由于主键定义上必须是唯一的，所以确实应该只期望有一个匹配项。之前从orders表读取的customer_id值用于键查找（第20行）。

为什么第一个计划比这个更好呢？优化器会选择成本最低的计划，而成本在很大程度上是根据它估计需要检查的记录组合总数来衡量的。记录组合总数的估计值是每个表预估平均检索记录数（EXPLAIN输出中的rows字段）的乘积。因此，根据估计，原始计划需要检查12×4 = 48个组合，而替代计划需要检查的组合数要多得多：19,566×1 = 19,566！

优化器还可以有其他不同的做法吗？由于WHERE子句的特性，在customer表中有两个可能使用的键：主键和state键。让我们尝试使优化器使用原始的连接顺序，但使用主键：

EXPLAIN    SELECT  count(*)   FROM  customer  c   FORCE  KEY(PRIMARY)  STRAIGHT_JOIN orders o
WHERE  o.customer_id  =  c.id  AND  c.state  =   'UT'  \G

EXPLAIN产生的输出如下：

1  ************************  1. row  ************************
2                       id:  1
3       select_type:  SIMPLE
4                table:  c
5                    type: ALL
6    possible_keys:  PRIMARY
7                     key:  NULL
8              key_len:  NULL
9                      ref:  NULL
10                   rows:  3913
11                Extra:  Using where
12  ************************  2. row  ************************
13                     id:  1
14     select_type:  SIMPLE
15               table: o
16                  type: ref
17  possible_keys:  customer_id
18                    key:  customer_id
19            key_len: 4
20                   ref:  book.c.id
21                  rows:  5
22                Extra: Using  index

优化器被迫仅使用主键，但它认为这不值得，因此选择扫描整个表（第5行）。确实，由于没有主键的参考值，需要对整个键进行遍历。如果可以从键中获取连接和WHERE子句匹配所需的所有内容而无需访问数据文件，优化器就会使用该键。然而，为了检查state = 'UT'这个条件，优化器需要读取state字段的值，而该字段不是主键的一部分。因此，必须获取整个记录，这使得基于键的读取比全表扫描更慢。记录组合的总数为3,913×5 = 19,565，这比原始计划中的48要大得多！

理解EXPLAIN的输出

如你在前面的示例中所见，EXPLAIN会产生一组行。每一行描述了参与连接的一个表，并展示了如何从该表中检索记录。行的顺序与算法中的连接顺序相对应。它还展示了查询的顺序，只有在涉及多个查询（例如包含子查询的查询）时，这个顺序才有意义。

EXPLAIN的输出本质上是JOIN类（见sql/sql_select.h）的可读转储内容，JOIN类用作查询计划描述符。表9 - 1定义了EXPLAIN字段与源代码中相应元素之间的关系。

EXPLAIN字段	描述	源代码元素
id	查询ID。仅在使用子查询时才有意义。	select_lex->select_number
select_type	指示从表中检索的结果集会如何处理。不涉及子查询或UNION的连接，该值将设置为simple。详见下一节“选择类型”。	select_lex->type
table	查询中表的引用别名。如果未使用别名，则为该表的实际名称。	对于常规（非派生）表，join_tab[k - 1].table->alias，其中k是连接顺序中表的编号。对于派生表，join_tab[k - 1].table->derived_select_number。
type	从表中检索记录的方法。详见下一节“记录访问类型”。	join_tab[k - 1].type，其中k是连接顺序中表的编号。
possible_keys	可与WHERE子句结合使用以从该表检索记录的键列表。	join_tab[k - 1].keys，其中k是连接顺序中表的编号。
key	用于检索记录的键的名称。当使用index_merge优化时，包含键的列表。	如果键用于根据键或其前缀的一个值查找一个或多个记录，则键的从零开始的索引编号包含在join_tab[k - 1].ref.key中。如果正在扫描索引，则键编号在join_tab[k - 1].index中。如果执行范围优化，则键编号在join_tab[k - 1].select->quick->index中。键的定义存储在从join_tab[k - 1].table->key_info开始的KEY结构数组中。键的名称存储在KEY结构的name成员中。
key_len	查询中使用的键的长度。不一定是键的全长，也可能只使用键的前缀。	关于如何定位键定义结构，见key字段的解释。正在使用的键的长度是KEY结构的key_length成员。
ref	其他表中其值参与本表索引查找的字段列表。	join_tab[k - 1].ref.key_copy
rows	每次连接迭代时预计从该表检索的平均记录数。	join_tab[k - 1].best_positions.records_read
Extra	关于优化策略的其他注释。详见“Extra字段”一节。	从多个连接描述符数据成员中收集。

选择类型

本节描述EXPLAIN命令输出中select_type字段可能指示的选择类型。

• SIMPLE：不使用UNION或子查询的选择。例如：

SELECT  count(*)  FROM  customer  c,  orders  o  WHERE  c.id  =  o.customer_id  AND  c.state = 'CA'

• PRIMARY：最外层的选择或union中的第一个选择。在以下示例中，从orders表的选择被标记为PRIMARY。例如：

SELECT  *  FROM  orders  WHERE  customer_id  IN   (SELECT  id   FROM  customer  WHERE  state  = 'CA')

• UNION：属于union且在查询中不是第一个的选择。在以下示例中，SELECT id FROM customer WHERE state = 'AZ'被标记为UNION，而SELECT id FROM customer WHERE state = 'NV'被标记为PRIMARY。

SELECT  id  FROM  customer  WHERE   state  =   'NV' UNION  SELECT   id   FROM  customer  WHERE state  =  'AZ'

• DEPENDENT UNION：与UNION相同，但用于依赖子查询中。如果优化器认为子查询可能会使用外层选择中每一行都会变化的信息，则该子查询被视为依赖于外层选择。遗憾的是，这意味着优化器会为外层选择的每一行重新运行子查询。例如：

SELECT  *  FROM  orders  WHERE  customer_id  IN   (SELECT  id   FROM  customer  WHERE  state  = 'NV'  UNION  SELECT  id   FROM  customer  WHERE  state  =   'AZ')

• UNION RESULT：union的结果。例如：

SELECT  id  FROM  customer  WHERE   state  =   'NV' UNION  SELECT   id  FROM  customer WHERE state  =  'AZ'

• SUBQUERY：非依赖子查询。优化器认为只需运行一次。例如：

SELECT  *   FROM  orders  WHERE  customer_id  =   (SELECT   id  FROM  customer  WHERE fname='Paul' AND  lname='Jones')

• DEPENDENT SUBQUERY：依赖子查询。优化器认为需要为外层查询的每一行运行一次。请注意，即使可能没有必要这样做，优化器也可能只是没有注意到子查询的独立性。在以下示例中就是这种情况：

SELECT  *  FROM  orders  WHERE  customer_id  IN   (SELECT  id   FROM  customer  WHERE  state  = 'NV')

• DERIVED：用于创建派生表的选择。如果一个表是由另一个查询的结果集生成的，则该表称为派生表。在SQL标准中，这类表被称为“FROM子句中的子查询”。在以下示例中，wy表是派生表：

SELECT  count(*)  FROM orders,(SELECT  id  FROM  customer WHERE  state='WY')  wy WHERE wy.id  = orders.customer_id

记录访问类型

本节介绍EXPLAIN命令输出中type字段可能表示的记录访问类型。

• system：表仅有一条记录的特殊情况。
• const：表最多有一个匹配行，在查询开始时仅读取一次。当表有唯一键且WHERE子句为其提供一个值时会出现这种情况。在下面的示例中，我们假设id是customer表中的唯一键：

SELECT  *   FROM  customer WHERE   id  =  32

• eq_ref：与const类似，但值不是固定常量，而是取自另一个表。只检索一条记录。因此，键必须是唯一的。在以下示例中，使用eq_ref根据order.customer_id的值在主键上查找customer.id的值：

SELECT  DISTINCT  customer.id  FROM  customer,orders  WHERE  customer.id  = orders.customer_id AND orders.payment_status  =  'FAILED'

• ref：与eq_ref和const类似，只使用一个值进行键查找。但是，可能检索到多条记录。当键不是唯一的，或者只有键的前缀可用时会出现这种情况。例如：

SELECT  count(*)  FROM  customer WHERE  last_name  =   'Johnson'

• ALL：全表扫描。当无法使用键约束，且优化器需要读取未被索引覆盖的列时会发生这种情况。在以下示例中，我们假设customer表没有涵盖first_name、last_name和state的键：

SELECT  first_name,last_name,state  WHERE  first_name='James'  AND last_name='Johnson' AND  state='IN'

• range：记录将通过索引，利用一个或多个范围约束条件进行读取。这种记录访问方法仅适用于支持范围查询的键。B树（B-tree）键支持范围查询，而哈希（hash）键不支持。在以下示例中，我们假设customer表在last_name上有一个支持范围查询的键：

SELECT  last_name,first_name  FROM  customer  WHERE  last_name  >   'B'  AND  last_name  <   'P'

• index：整个索引将被扫描。这不是一种高效的索引使用方式，意味着用户没有很好地利用索引。尽管如此，这是优化器针对用户提供的查询所能做出的最佳选择。由于对索引值没有限制条件，无法减少需要读取的值的数量。在使用整个索引进行扫描时，只会访问记录中被索引覆盖的部分。如果索引仅覆盖整个记录的一小部分，这种索引扫描可能比全表扫描更高效。在以下示例中，我们假设customer表有一个涵盖last_name的键：

SELECT  last_name  FROM  customer

• fulltext：优化器使用全文匹配方法检索记录。这仅适用于支持全文搜索的键，目前仅在MyISAM存储引擎中实现。在以下示例中，我们假设customer表在description上有一个全文键：

SELECT  *  FROM  customer  WHERE  MATCH(description)  AGAINST   ('pays  bills')

• ref_or_null：与ref类似，只不过会额外搜索NULL值。在以下示例中，我们假设last_name是一个可以包含NULL值的键：

SELECT  *  FROM  customer WHERE  last_name  =   'Johnson'  OR  last_name  IS  NULL

• unique_subquery：用于优化带有子查询的IN操作，当子查询选择唯一键值时使用。在以下示例中，我们假设id是customer表中的唯一键：

SELECT  *   FROM  orders  WHERE  customer_id   IN   (SELECT  id  FROM  customer  WHERE lname='Johnson')

• index_subquery：与unique_subquery类似，只不过索引不是唯一的。在以下示例中，我们假设customer_id是orders表中的非唯一键：

SELECT  *   FROM  customer  WHERE   id  IN   (SELECT  customer_id   FROM  orders  WHERE product_id  =  3)

• index_merge：分别使用两个键，并将结果合并。在以下示例中，我们假设product表在price上有一个键，在name上有另一个键：

SELECT  *  FROM  product WHERE  name='AMD   Laptop'  OR  price=1300.00

Extra字段

本节介绍EXPLAIN命令输出中Extra字段可能出现的字符串。

Using where

WHERE子句用于排除一些记录。除非优化器能够检测到通过某个键读取的所有记录都自动满足WHERE子句，否则这是必要的操作。在下面的示例中，假设price不是product表中的键，我们会看到Using where：

SELECT * FROM product WHERE price = 1300.00

注意，如果我们在price上添加一个键，Using where就会消失。优化器会请求读取价格等于1300.00的所有记录的价格键，这样就自动满足了WHERE子句。

Using index

优化器注意到它所需的所有列都包含在一个键中，因此决定只扫描该键，而不是扫描整个数据。在下面的示例中，假设name是product表中的键：

SELECT name FROM product WHERE name LIKE '%laptop%'

Using index for group-by

优化器能够通过仅读取每个不同键值的键的第一条和/或最后一条记录，来优化GROUP BY或DISTINCT操作。对于GROUP BY，只有在除了MIN()和MAX()之外没有其他聚合函数、查询只涉及一个表、优化器选择的索引涵盖了所有需要的列，并且GROUP BY中的列顺序与WHERE子句和/或MIN()/MAX()中的列协同工作，使得无需查看每个不同键值的所有记录就能得出答案时，才可能实现这种优化。在下面的示例中，假设product表在(name, price)上有一个键：

SELECT name, MAX(price), MIN(price) FROM product GROUP BY name

注意，如果我们将MAX(price)替换为COUNT(*)，虽然使用的是相同的索引，但Extra字段现在会显示Using index。查询会以不同的方式进行优化，因为计算COUNT(*)需要查看索引中的所有值，它需要知道有多少个值，而目前存储引擎接口并没有提供一种方式，让优化器询问，即使存储引擎存储了这个值，也无法进行通信。

Using filesort

优化器被要求按排序顺序（ORDER BY）检索记录，但它的记录访问方法无法保证这一点，因此需要进行后置排序。术语filesort指的是MySQL的排序算法，它会在内存中对小数据块执行基数排序或快速排序。如果要排序的整个记录集无法放入排序缓冲区，临时结果会存储在文件中，然后对所有数据块执行合并步骤。在下面的示例中，假设product表在price上没有键：

SELECT * FROM product WHERE price < 1000.00 AND name LIKE 'AMD%' ORDER BY price

注意，如果我们在price上添加一个键，Using filesort消息就会消失。优化器能够使用该键，并按键的顺序常规检索记录，从而无需进行后置排序。

Using temporary

优化器需要创建一个临时表来存储中间结果。例如，当在非键列上执行GROUP BY时，优化器会创建一个带有由GROUP BY表达式组成的唯一键的临时表。对于常规结果集中的每条记录（不包括GROUP BY部分），都会尝试将其插入临时表中。如果由于唯一约束冲突导致插入失败，就会适当地更新现有记录。一旦临时表填充完毕，结果集就会被排序并返回给客户端。在下面的示例中，假设product表在name上没有键：

SELECT name, COUNT(*) FROM product GROUP BY name

如果我们在name上添加一个键，就不再需要使用临时表。现在可以通过遍历键来执行GROUP BY操作。

Distinct

优化器能够通过在查询中使用DISTINCT关键字，消除连接中的重复记录。在示例中，假设orders表在product_id上有一个键，id是product表中的唯一键，并且优化器在连接顺序中把orders表排在前面：

SELECT DISTINCT orders.product_id FROM orders, product WHERE orders.product_id = product.id AND product.name LIKE '%AMD%' AND orders.customer_id = 1

实际上，虽然orders表中可能有许多记录与WHERE子句中orders部分匹配，但对于每个不同的product_id值，只需要检查WHERE子句中product部分即可。由于查询的特性，orders表中任何两个具有相同product_id的记录，其product部分都将相同。因此，由于查询只要求product_id的不同值，一旦优化器在orders表中找到一个与WHERE子句匹配的唯一product_id值，就无需检查具有相似键值的其余记录，可以直接移动到索引中的下一个唯一值。

Not exists

在左连接中使用一种特殊优化来消除记录组合。如果连接是在第二个表中定义为NOT NULL属性的列上进行的，并且WHERE子句要求该列的值为NULL，那么只有当第一个表列的匹配值在第二个表中不存在时，这种情况才会发生。在下面的示例中，假设orders.product_id被定义为NOT NULL：

SELECT product.id FROM product LEFT JOIN orders ON product.id = orders.product_id WHERE orders.product_id IS NULL

实际上，除非是插入到左连接product中以标记ON子句匹配失败的特殊记录，否则orders.product_id不可能为NULL。因此，即使在product表中只发现一条记录与ON子句匹配，优化器也可以安全地移动到product表中的下一条记录，而无需检查该记录与orders表中记录的所有其他组合。

range checked for each record: (index map: N)

优化器没有找到一个可以始终用于给定表的索引。然而，随着连接的进行，前面的表（按照连接顺序）中的某些记录组合可能允许对某些键进行范围查询或索引合并优化。因此，优化器会针对前面表中的每个记录组合检查，以确定使用哪个索引最佳。在示例中，假设w2表有两个索引，一个在s列上，一个在s1列上：

SELECT count(*) FROM w1, w2 WHERE w2.s > w1.s AND w2.s1 < w1.s

在某些情况下，优化器可能会选择使用s列上的索引，而在其他情况下可能会选择使用s1列上的索引。如果范围限制不是很严格，优化器甚至可能选择扫描w2表。选择取决于w1.s的值。index map: N中的N值（在5.0版本中）是此优化中考虑的键的位图的十六进制表达式。

这种优化成本较高且不太常见，更像是一种挽救糟糕查询性能的尝试：如果不进行这种优化，查询的性能可能会非常差。如果优化器选择了这种方式，这其实是在提示你需要编写一个更好的查询。

Using union()

在使用index_merge访问方法时会出现此注释。使用两个或更多的键来检索记录，并且可以通过对结果进行排序列表合并来获得正确的结果。换句话说，每个键的约束条件使得无需按行ID对每个索引中的记录进行排序：每个键自然会生成一个排序列表。当键的所有部分都已知，或者键是聚簇主键（在InnoDB和BDB表中）时，就可以保证按行ID自然排序。

在下面的示例中，假设customer表在state列上有一个键，在(lname, fname)上也有一个键：

SELECT COUNT(state) FROM customer WHERE (lname = 'Jones' AND fname='John') OR (state = 'UT')

Using sort_union()

在使用index_merge访问方法时会出现此注释。使用两个或更多的键来检索记录，但优化器不能确定每个键都会自然生成一个排序列表。因此，为了消除重复行，需要额外的处理。

在示例中，customer表在state列和(lname, fname)上有键，但在lname列上没有键：

SELECT COUNT(*) FROM customer WHERE (lname = 'Jones') OR (state = 'UT')

由于在lname列上没有键，必须使用(lname, fname)键。优化器没有涵盖该键所有部分的约束条件，因此记录不一定按行ID排序。

Using intersect()

在使用index_merge访问方法时会出现此注释。使用两个或更多的键来检索记录，并且可以通过对结果进行排序列表交集运算来获得正确的结果。这种优化与Using union()非常相似，只是结果集是进行交集运算（AND操作），而不是合并运算（OR操作）。

在下面的示例中，假设customer表在state列上有一个键，在(lname, fname)上也有一个键：

SELECT COUNT(state) FROM customer WHERE (lname = 'Jones' AND fname='John') AND (state = 'UT')

Using where with pushed condition

在引入NDB表之前，优化器在操作时假设，从表中通过键读取记录或进行全表扫描，在最坏的情况下都必须访问本地磁盘。即使实际情况并非如此，它也没有太多其他办法，因为现有的存储引擎都没有预过滤记录的能力。随着NDB的引入，预过滤的能力变得很有必要。NDB表的访问通常会导致网络I/O。因此，如果存储引擎足够智能，能够将过滤条件传递给远程节点，性能就可以得到很大程度的优化。

如果存储引擎支持（目前只有NDB支持），优化器可以将过滤条件推送到存储引擎实例的条件栈上。反过来，存储引擎可以使用这些额外信息来优化记录检索。在示例中，tablet表是NDB类型，并且在n列上没有键：

SELECT * FROM t WHERE n = 5

范围优化器

MySQL开发者在优化对键值范围有限制条件的查询方面付出了很多努力。有一个专门用于此目的的模块，称为范围优化器（Range Optimizer）。范围优化器的源代码位于sql/opt_range.h和sql/opt_range.cc中，入口点是SQL_SELECT::test_quick_select()。

范围优化器支持以下优化：

Range

当只有一个键按升序排列且其键值范围已知时，会进行常规范围优化。例如：

SELECT * FROM t WHERE key1 > 'a' AND key1 < 'b'

常规范围优化可以处理与布尔运算符结合使用的各种键值组合，也可以处理各种范围约束运算符。在下面的示例中，t1表在(c1, c2)上有一个键：

SELECT * FROM t1 WHERE (c1 IN(5, 6) AND c2 IN(1, 2)) OR (c1 = 15 AND c2 BETWEEN 1 AND 2) OR (c1 BETWEEN 20 AND 30)

范围优化器会在以下键值区间集合中搜索(c1, c2)：

(1,2)–(1,2); (1,6)–(1,6); (2,2)–(2,2); (2,6)–(2,6); (15,1)–(15,2); (20,–inf)–(30,+inf)

注意，它能够将常量转换为退化区间。这种类型的优化由QUICK_RANGE_SELECT类完成。

当使用空间键时，有一个范围优化的特殊情况。这些由QUICK_RANGE_SELECT_GEOM类处理，它是QUICK_RANGE_SELECT的超类。

Index_merge

当多个键都有范围约束，但结果不是按排序顺序返回，因此需要额外处理时，会使用这种优化。更多详细信息，请参阅前面“Extra字段”一节中对Using sort_union()的解释。

由QUICK_INDEX_MERGE_SELECT类处理。

Range_desc

与Range类似，只是记录按键的降序读取。由QUICK_SELECT_DESC类处理。

Fulltext

实现全文键匹配约束。由FT_SELECT类处理。虽然全文搜索中没有范围的概念，但代码结构使得范围优化器最适合用于全文优化代码。

ROR_intersect

当多个键都有范围约束，每个键的结果集自然是按排序顺序返回，并且最终结果是通过对每个键的结果进行交集运算得到时，会使用这种优化。请参阅前面“Extra字段”一节中对Using intersect()的解释。由QUICK_ROR_INTERSECT_SELECT类处理。

ROR_union

当多个键都有范围约束，每个键的结果集自然是按排序顺序返回，并且最终结果是通过对每个键的结果进行并集运算得到时，会使用这种优化。请参阅前面“Extra字段”一节中对Using union()的解释。由QUICK_ROR_UNION_SELECT类处理。

Group_min_max

处理在几个键都有范围约束的情况下，GROUP BY与MIN()/MAX()函数结合的一些特殊情况。由QUICK_GROUP_MIN_MAX_SELECT类处理。

子查询优化

目前，MySQL对子查询执行的优化相对较少。如果它注意到一个子查询每次求值只会返回一条记录，就会对该查询进行求值，并将整个子查询替换为一个常量。在特殊情况下，它也会尝试对一些子查询进行较小的重写。其他重要的优化仍在计划中，目前计划在5.2版本中实现：

• 能够缓存返回多条记录的子查询结果，并使用这些缓存结果，而不是为每个记录组合都执行子查询。
• 能够在存储FROM子句子查询结果的临时表中创建并使用合适的键。
• 能够在存储WHERE子句子查询结果的临时表中创建并使用合适的键。
• 支持依赖于FROM子句的子查询。
• 能够在子查询优化过程中重写连接顺序。
• 能够使用涉及同一表的子查询对表进行UPDATE/INSERT/DELETE修改。

目前，MySQL子查询优化器仍在不断完善中。