查看“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策略'''优化子查询 ==

== 通过'''合并'''或'''实体化'''来优化派生表和视图引用 ==