mysql exits优化_mysql8 参考手册--使用EXISTS策略优化子查询

某些优化适用于使用IN(或=ANY)运算符测试子查询结果的比较。本节讨论这些优化，尤其是关于NULL价值所面临的挑战。讨论的最后部分提出了如何帮助优化器的建议。

考虑以下子查询比较：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL的评估查询“ 从外到内。” 即，首先获得外表达式的值 outer_expr，然后运行子查询，并且捕获的行，它产生。

一个非常有用的优化是“ 通知 ”子查询仅感兴趣的行是内部表达式inner_expr等于的行outer_expr。这是通过将适当的等式推入子查询的WHERE子句以使其更具限制性来完成的。转换后的比较如下所示：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

转换后，MySQL可以使用下推式相等性来限制为评估子查询而必须检查的行数。

更一般而言，将N 值与返回N-value行的子查询 进行比较将进行相同的转换。如果oe_i和 ie_i代表相应的外部和内部表达式值，则此子查询比较：

(oe_1, ..., oe_N) IN

(SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

成为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where

AND oe_1 = ie_1

AND ...

AND oe_N = ie_N)

为简单起见，下面的讨论假设使用一对外部表达式值和内部表达式值。

如果满足以下任一条件，则 上述“ 下推 ”策略将起作用：

1、outer_expr而且 inner_expr不能 NULL。

2、你不必区分NULL从 FALSE子查询结果。如果子查询是子句中ORor AND表达式 的一部分WHERE，则MySQL认为您不在乎。在优化程序注意到另一个实例NULL和FALSE 子查询结果不需要区分是这样的结构：

...WHERE outer_expr IN (subquery)

在这种情况下，该WHERE子句拒绝该行，无论是return 还是。 IN (subquery)NULLFALSE

假设outer_expr已知这是一个非NULL值，但子查询不会产生诸如outer_expr= 的行 inner_expr。然后 outer_expr IN (SELECT ...)评估如下：

1、NULL中，如果 SELECT产生任何行，其中inner_expr是 NULL

2、FALSE，如果 SELECT仅产生非NULL值或什么都不产生

在这种情况下，查找外部表达式=内部表达式的行的方法不再有效。有必要查找这样的行，但如果没有找到，也可以查找内部表达式为空的行。粗略地说，子查询可以转换为如下内容：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND

(outer_expr=inner_expr OR inner_expr IS NULL))

需要评估额外IS NULL条件是MySQL具有 ref_or_null访问方法的原因：

mysql> EXPLAIN

SELECT outer_expr IN (SELECT t2.maybe_null_key

FROM t2, t3 WHERE ...)

FROM t1;

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: t1

...

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: t2

type: ref_or_null

possible_keys: maybe_null_key

key: maybe_null_key

key_len: 5

ref: func

rows: 2

Extra: Using where; Using index

...

在unique_subquery和 index_subquery 子查询，具体的访问方法也有“ 或 NULL ”变种。

附加OR ... IS NULL条件使查询执行稍微复杂一些(并且子查询中的某些优化变得不适用)，但是通常这是可以容忍的。

的情况更糟糕的时候 outer_expr可能 NULL。据的SQL解释NULL为“ 未知值， ” 应该评估为： NULL IN (SELECT inner_expr ...)

NULL，如果 SELECT产生任何行

FALSE，如果不 SELECT产生任何行

为了进行正确的评估，有必要能够检查是否SELECT完全生成了任何行，因此 不能将其下推到子查询中。这是一个问题，因为许多现实世界中的子查询会变得非常缓慢，除非可以降低相等性。 outer_expr = inner_expr

本质上，取决于的值，必须有不同的方法来执行子查询 outer_expr。

优化选择超速SQL合规性，所以它占的可能性 outer_expr可能是 NULL：

如果outer_expr为 NULL，则要计算以下表达式，必须执行 SELECT以确定它是否产生任何行：

NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)

必须在SELECT此处执行原始文件 ，而没有前面提到的那种下推式等价物。

另一方面，当outer_expr不是 时 NULL，此比较绝对必要：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

转换为使用下推条件的表达式：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

没有这种转换，子查询将很慢。

为了解决是否将条件下推到子查询中的难题，将条件包装在 “ 触发器 ”函数中。因此，以下形式的表达式：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

转换为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where

AND trigcond(outer_expr=inner_expr))

更一般而言，如果子查询比较基于几对外部和内部表达式，则转换将采用以下比较：

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

并将其转换为以下表达式：

EXISTS (SELECT 1 FROM ... WHERE subquery_where

AND trigcond(oe_1=ie_1)

AND ...

AND trigcond(oe_N=ie_N)

)

每个 函数都是一个特殊函数，其求值结果如下： trigcond(X)

X当 “ 链接的 ”外部表达 oe_i不是 NULL

TRUE当“ 链接的 ” 外部表达oe_i是 NULL

注意

触发器函数不是您使用创建的那种触发器CREATE TRIGGER。

trigcond()函数 中包装的等式 不是查询优化器的第一类谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词，因此它们假定任何谓词 都是未知函数，而忽略它。那些优化可以使用触发的等式： trigcond(X)

参考优化： 可用于构建 ， 或 表访问。 trigcond(X=Y [OR Y IS NULL])refeq_refref_or_null

基于索引查找的子查询执行引擎： 可用于构造 或 访问。 trigcond(X=Y)unique_subqueryindex_subquery

表条件生成器：如果子查询是多个表的联接，则将尽快检查触发条件。

当优化器使用触发条件创建某种基于索引查找的访问时(对于前面列表的前两项)，对于条件关闭的情况，优化器必须具有回退策略。此后备策略始终相同：执行全表扫描。在 EXPLAIN输出中，回退显示Full scan on NULL key在 Extra列中：

mysql> EXPLAIN SELECT t1.col1,

t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: t1

...

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: t2

type: index_subquery

possible_keys: key1

key: key1

key_len: 5

ref: func

rows: 2

Extra: Using where; Full scan on NULL key

如果你运行EXPLAIN之后 SHOW WARNINGS，你可以看到触发条件：

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: select `test`.`t1`.`col1` AS `col1`,

(`test`.`t1`.`col1`,

(((`test`.`t1`.`col1`) in t2

on key1 checking NULL

where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having

trigcond((`test`.`t2`.`key1`))))) AS

`t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`

from `test`.`t1`

使用触发条件会影响性能。甲NULL IN (SELECT ...) 现在表达可能会导致全表扫描(这是慢)时，它以前没有。这是为获得正确结果而付出的代价(触发条件策略的目标是提高合规性，而不是速度)。

对于多表子查询，NULL IN (SELECT ...)因为连接优化器未针对外部表达式为的情况进行优化，所以执行特别慢NULL。它假定NULL左侧的子查询求值非常少见，即使有统计数据表明并非如此。另一方面，如果外部表达式可能是 NULL但实际上不是，则不会影响性能。

为了帮助查询优化器更好地执行查询，请使用以下建议：

声明一列，就NOT NULL好像它确实是一样。通过简化色谱柱的条件测试，这也有助于优化程序的其他方面。

如果您不需要区分NULL来自 FALSE子查询的结果，你可以很容易地避免慢的执行路径。替换如下所示的比较：

outer_expr [NOT] IN (SELECT inner_expr FROM ...)

具有以下表达式：

(outer_expr IS NOT NULL) AND (outer_expr [NOT] IN (SELECT inner_expr FROM ...))

然后NULL IN (SELECT ...)永远不会进行评估，因为AND一旦表达式结果明确，MySQL就会停止评估 零件。

另一种可能的重写：

[NOT] EXISTS (SELECT inner_expr FROM ...

WHERE inner_expr=outer_expr)

在subquery_materialization_cost_based 所述的标志optimizer_switch 系统变量使得能够在子查询物化和之间的选择控制 IN-到- EXISTS子查询变换。