查看“MySQL 优化：优化 SQL 语句：优化子查询，派生表和视图引用”的源代码

[[category:MySQL]]

== 关于 ==
MySQL 查询优化器有多种策略可用于评估子查询：
# 对于'''IN'''（或=ANY）子查询，优化器具有以下选择：
## 半连接
## 实体化
## EXISTS策略
# 对于'''NOT IN'''（或<>ALL）子查询，优化器具有以下选择：
## 实体化
## EXISTS 策略
# 对于派生表，优化器具有以下选择（这也适用于视图引用）：
## 将派生表合并到外部查询块中
## 将派生表实体化为内部临时表


* 使用子查询修改单个表的“UPDATE”和“DELETE”语句的局限性在于，优化器不使用半联接或物化子查询优化。解决方法是，尝试将它们重写为使用联接而不是子查询的多表“UPDATE”和“DELETE”语句。

== 使用'''半联接'''转换优化子查询，派生表和视图引用 ==
半联接是准备时转换，它启用多种执行策略，例如：表提取，重复删除，首次匹配，松散扫描和实体化。


对于两个表之间的内部联接，该联接从一个表返回一行的次数是另一表中存在匹配项的次数。但是对于某些问题，唯一重要的信息是是否存在匹配项，而不是匹配数：
: 假设存在名为 class 和 roster 的表，这些表分别列出了班级表中的班级和班级花名册（每个班级的学生人数）。要列出实际招收学生的班级，您可以使用以下联接：
: <syntaxhighlight lang="mysql">
SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;
</syntaxhighlight>
: 但是，结果为每个注册学生列出一次每个班级。对于所提出的问题，这是不必要的信息重复。
: 假设 class_num 是 class 表中的主键，则可以通过使用“SELECT DISTINCT”来抑制重复，但是先生成所有匹配的行仅用于稍后消除重复是无效的。
: 可以通过使用子查询获得相同的无重复结果：
<syntaxhighlight lang="mysql">
SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);
</syntaxhighlight>
在这里，优化器可以识别“IN”子句要求子查询仅返回 roster 表中每个班级号的一个实例。在这种情况下，查询可以使用半联接。也就是说，该操作仅返回 class 中的每一行的一个实例，该实例与 roster 中的行匹配。


外部查询规范中允许使用外部联接和内部联接语法，并且表引用可以是基表，派生表或视图引用。


在 MySQL 中，子查询必须满足以下条件才能作为半联接进行处理：【！！！】
# 它必须是出现在“WHERE”或“ON”子句的顶级（可能作为“AND”表达式中的术语）的“IN”（或“=ANY”）子查询。例如：
#: <syntaxhighlight lang="mysql">
SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);
</syntaxhighlight>
#: 在这里，ot_i 和 it_i 表示查询的外部和内部部分中的表，oe_i 和 ie_i 表示引用外部和内部表中的列的表达式。
# 它必须是没有“UNION”构造的单个“SELECT”。
# 它不能包含“GROUP BY”或“HAVING”子句。
# 不能将其隐式分组（不能包含任何聚合函数）。
# 它不能带有“ORDER BY”和“LIMIT”。
# 该语句不得在外部查询中使用“STRAIGHT_JOIN”连接类型。
# “STRAIGHT_JOIN”修饰符不能存在。
# 外部表和内部表的总数必须小于联接中允许的最大表数。
子查询可以是相关的或不相关的。除非已使用“ORDER BY”，否则允许“DISTINCT”，也允许“LIMIT”。


如果子查询满足上述条件，MySQL 会将其转换为半联接并从以下策略中进行基于成本的选择：
# 将子查询转换为联接，或使用表提取，并将查询作为子查询表与外部表之间的内部联接运行。表提取将表从子查询中拉出到外部查询。
# 重复删除：像运行连接一样运行半连接，并使用临时表删除重复记录。
# FirstMatch：当扫描内部表中的行组合并且给定值组有多个实例时，请选择一个而不是全部返回。这种“快捷方式”扫描可以消除不必要行的产生。
# LooseScan（松散扫描）：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择一个值。
# 将子查询实体化到用于执行联接的索引临时表中，在该临时表中，索引用于删除重复项。当将临时表与外部表连接时，该索引以后也可能用于查找。如果不是，则扫描表。


可以使用以下“optimizer_switch”系统变量标志来启用或禁用这些策略中的每一个：
# “semijoin”标志控制是否使用半联接。
# 如果启用了“semijoin”，则“firstmatch”，“loosescan”，“duplicateweedout”和“materialization”标志可对允许的半联接策略进行更好的控制。
# 如果禁用了“duplicateweedout”半联接策略，则除非所有其他适用的策略也都被禁用，否则将不使用它。
# 如果禁用“duplicateweedout”，则有时优化器可能会生成一个远非最佳的查询计划。这是由于贪婪搜索期间的启发式修剪而发生的，可以通过设置“optimizer_prune_level=0”来避免。
默认情况下启用这些标志。


优化器将视图和派生表的处理差异最小化。这会影响使用“STRAIGHT_JOIN”修饰符的查询以及带有“IN”子查询的视图，该视图可以转换为半联接。以下查询说明了这一点，因为处理中的更改导致转换中的更改，从而导致不同的执行策略：
<syntaxhighlight lang="mysql">
CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;
</syntaxhighlight>
优化器首先查看视图，并将“IN”子查询转换为半联接，然后检查是否有可能将视图合并到外部查询中。因为外部查询中的'''“STRAIGHT_JOIN”修饰符防止半联接'''，所以优化器拒绝合并，从而导致使用物化表进行派生表评估。


EXPLAIN 输出指示使用半连接策略，如下所示：
* 半联接表显示在外部选择中。对于扩展的 EXPLAIN 输出，以下“SHOW WARNINGS”显示的文本显示了重写的查询，该查询显示了半联接结构。由此您可以了解哪些表已从半联接中拉出。如果将子查询转换为半联接，则将看到该子查询谓词已消失，并且其表和“WHERE”子句已合并到外部查询联接列表和“WHERE”子句中。
* “Extra”列中的“Start temporary”和“End temporary”表示用于重复除草的临时表。未拉出并且在“Start temporary”和“End temporary”覆盖的 EXPLAIN 输出行范围内的表在临时表中具有 rowid。
* “Extra”列中的“FirstMatch(tbl_name)”表示加入快捷方式。
* “Extra”列中的“LooseScan(m..n)”表示使用了 LooseScan 策略。（m 和 n 是关键 Component 编号）
* 用于实现的临时表由“select_type”值为“MATERIALIZED”的行和表值为“<subqueryN>”的行指示。

== 通过'''实体化'''来优化子查询 ==
实体化通过生成子查询结果作为'''临时表'''（通常在内存中）来加快查询执行速度：
: '''MySQL 第一次需要子查询结果时，会将结果具体化为临时表。任何随后的需要结果的时间，MySQL 都会再次引用临时表'''。优化器可以使用'''哈希索引'''对表进行索引，以使查找快速，廉价。索引包含唯一值，以消除重复项并使表更小。
* 子查询实体化在可能的情况下使用'''内存'''中的临时表，如果表太大，则会退回到磁盘上的存储。


如果'''未使用实体化'''，则优化器有时会'''将不相关的子查询重写为相关的子查询'''。
: 例如，以下“IN”子查询是不相关的（where_condition 仅涉及 t2 而不是 t1 的列）：
: <syntaxhighlight lang="mysql">
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
</syntaxhighlight>
: 优化器可能将此重写为 EXISTS 相关子查询：【！！！！！！！！！！！】
: '''<syntaxhighlight lang="mysql">
SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);
</syntaxhighlight>'''
使用'''临时表的子查询实现避免了这样的重写'''，并使得只可能执行一次子查询，而不是对外部查询的每一行执行一次。


为了使子查询实现在 MySQL 中使用，必须启用“optimizer_switch”系统变量“materialization”标志。
启用 materialization 标志，实现适用于出现在以下任何一种情况下的谓词：在任何位置（在选择列表 WHERE，ON，GROUP BY，HAVING 或 ORDER BY 中）出现的子查询谓词：【！！！】
* 当没有外部表达式 oe_i 或内部表达式 ie_i 为空时，谓词具有这种形式。（N 为 1 或更大）。
*: <syntaxhighlight lang="mysql">
(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)
</syntaxhighlight>
* 当存在单个外部表达式 oe 和内部表达式 ie 时，谓词具有这种形式。表达式可以为空。
*: <syntaxhighlight lang="mysql">
oe [NOT] IN (SELECT ie ...)
</syntaxhighlight>
* 谓词是“'''IN'''”或“'''NOT IN'''”，并且“UNKNOWN(NULL)”的结果与“FALSE”的结果具有相同的含义。

以下示例说明了 UNKNOWN 和 FALSE 谓词评估的等价要求如何影响是否可以使用子查询实现。假定 where_condition 仅包含来自 t2 而不是 t1 的列，因此子查询是不相关的。
: 此查询需要具体实现：
: <syntaxhighlight lang="mysql">
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
</syntaxhighlight>
: 在这里，IN 谓词返回 UNKNOWN 或 FALSE 都没有关系。无论哪种方式，t1 中的行都不会包含在查询结果中。
: 以下查询是不使用子查询实现的示例，其中 t2.b 是可为空的列：
: <syntaxhighlight lang="mysql">
SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
                          WHERE where_condition);
</syntaxhighlight>


以下限制适用于子查询实现的使用：【！！！！！】
* '''内部和外部表达式的类型必须匹配'''。例如，如果两个表达式都是整数或两个都是十进制，那么优化器可能可以使用实现，但是如果一个表达式是整数而另一个表达式是十进制，则优化器不能使用实现。
* '''内部表达式不能为 BLOB'''。


在查询中使用 EXPLAIN 可以表明优化器是否使用子查询实现：
* 与不使用实现的查询执行相比，select_type 可能从“DEPENDENT SUBQUERY”更改为“SUBQUERY”。这表明，'''对于将对每个外行执行一次的子查询，实体化将使子查询仅执行一次'''。
* 对于扩展的 EXPLAIN 输出，随后的“SHOW WARNINGS”显示的文本包括“materialize”和“materialized-subquery”。

== 使用'''EXISTS策略'''优化子查询 ==

== 通过'''合并'''或'''实体化'''来优化派生表和视图引用 ==
优化器可以使用两种策略来处理派生表引用（这也适用于视图引用）：
# 将派生表'''合并'''到'''外部查询块'''中
#: <syntaxhighlight lang="mysql">
SELECT * FROM (SELECT * FROM t1) AS derived_t1;
</syntaxhighlight>
#: 通过合并派生表 derived_t1，该查询的执行类似于：
#: <syntaxhighlight lang="mysql">
SELECT * FROM t1;
</syntaxhighlight>
# 将派生表'''实体化'''为'''内部临时表'''
#: <syntaxhighlight lang="mysql">
SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;
</syntaxhighlight>
#: 通过合并派生表 derived_t2，该查询的执行类似于：
#: <syntaxhighlight lang="mysql">
SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;
</syntaxhighlight>
* 优化器以相同的方式处理派生表和视图引用：尽可能避免不必要的实现，这可以将条件从外部查询下推到派生表，并产生更有效的执行计划。
* 如果合并将导致一个外部查询块引用超过 61 个基本表，则优化程序将选择实现。


如果满足以下所有条件，则优化器将派生表或视图引用中的“ORDER BY”子句传播到外部查询块：
# 外部查询未分组或聚合。
# 外部查询未指定“DISTINCT”，“HAVING”或“ORDER BY”。
# 外部查询将此派生表或视图引用作为“FROM”子句中的唯一源。
否则，优化器将忽略“ORDER BY”子句。


可以使用以下方法来影响优化器是否尝试将派生表和视图引用合并到外部查询块中：
* 假定没有其他规则阻止合并，则可以使用“optimizer_switch”系统变量的“derived_merge”标志。
** 默认情况下，该标志启用以允许合并。禁用该标志可防止合并并避免“ER_UPDATE_TABLE_USED”错误。
** derived_merge标志还适用于不包含 ALGORITHM 子句的视图。因此，如果使用与子查询等效的表达式的视图引用发生 ER_UPDATE_TABLE_USED 错误，则在视图定义中添加“ALGORITHM=TEMPTABLE”可以防止合并并优先于 derived_merge 值。【？？？】
* 可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响不那么明显。
*: 防止合并的构造与派生表和视图引用相同：
** 聚合函数（SUM()，MIN()，MAX()，COUNT()等）
** DISTINCT
** GROUP BY
** HAVING
** LIMIT
** UNION 或 UNION ALL
** 选择列表中的子查询
** 分配给用户变量
** 仅引用 Literals 值（在这种情况下，没有基础表）


如果优化器选择实体化策略而不是合并派生表，则它将按以下方式处理查询：
* 优化程序将派生表的'''实体化推迟'''到'''查询执行期间需要其内容之前'''。这会提高性能，因为延迟实现可能会导致根本不必这样做。考虑一个将派生表的结果连接到另一个表的查询：如果优化器首先处理该另一个表并发现它不返回任何行，则不需要进一步执行联接，并且优化器可以完全跳过实体化派生表。【延迟实体化】
* 在查询执行期间，优化器可以将'''索引'''添加到派生表中，以加快从中获取行的速度。【添加索引】


【延迟实体化】
# 考虑以下 EXPLAIN 语句，用于包含派生表的 SELECT 查询：
#: <syntaxhighlight lang="mysql">
EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;
</syntaxhighlight>
#: 优化程序通过将其推迟到 SELECT 执行期间需要结果之前来避免实体化派生表。在这种情况下，查询不会执行（因为它发生在 EXPLAIN 语句中），因此永远不需要结果。
# 即使对于已执行的查询，派生表实体化的延迟也可以使优化程序完全避免实现。发生这种情况时，查询执行将比实现实现所需的时间更快。考虑以下查询，该查询将派生表的结果连接到另一个表：
#:<syntaxhighlight lang="mysql">
SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;
</syntaxhighlight>
#:如果最先进行优化的过程 t1 和 WHERE 子句产生的结果为空，则联接必须一定为空，并且派生表不必实现。
【添加索引】
# 对于派生表需要实体化的情况，优化器可以向实现表添加索引以加快对其的访问。如果这样的索引启用对表的 ref 访问，则可以大大减少查询执行期间读取的数据量。考虑以下查询：
#:<syntaxhighlight lang="mysql">
SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;
</syntaxhighlight>
#:如果这样做可以使 ref 访问用于最低成本的执行计划，则优化器将在 derived_t2 的 f1 列上构造索引。添加索引后，优化器可以将物化派生表与具有索引的常规表相同，并且它从生成的索引中也可以得到类似的好处。与没有索引的查询执行成本相比，索引创建的开销可以忽略不计。如果 ref 访问会比其他访问方法带来更高的成本，则优化器不会创建索引，也不会丢失任何内容。


对于优化程序跟踪输出，未将合并的派生表或视图引用显示为节点。仅其基础表出现在顶部查询的计划中。【？？？】