8.2.2.3 使用 EXISTS 策略优化子查询
某些优化适用于使用IN
(或=ANY
)运算符测试子查询结果的比较。本节讨论这些优化,尤其是针对NULL
值所带来的挑战。讨论的最后部分提出了如何帮助优化器的建议。
考虑以下子查询比较:
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
MySQL“从外到内”评估查询。也就是说,它首先获取外部 table 达式* outer_expr
*的值,然后运行子查询并捕获其产生的行。
一个非常有用的优化是“通知”子查询仅感兴趣的行是那些内部 table 达式* 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
*table 示相应的外部和内部 table 达式值,则此子查询比较:
(oe_1, ..., oe_N) IN
(SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
Becomes:
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND oe_1 = ie_1
AND ...
AND oe_N = ie_N)
为简单起见,下面的讨论假定一对外部 table 达式值和内部 table 达式值。
刚刚描述的转换有其局限性。仅当我们忽略可能的NULL
值时才有效。也就是说,只要满足以下两个条件,就可以使用“下推”策略:
-
outer_expr
和inner_expr
*不能为NULL
。
-
您无需将
NULL
与FALSE
子查询结果区分开。如果子查询是WHERE
子句中OR或ANDtable 达式的一部分,则 MySQL 假定您不在乎。优化器注意到不需要区分NULL
和FALSE
子查询结果的另一个实例是以下结构:
... WHERE outer_expr IN (subquery)
在这种情况下,无论IN (subquery)
返回NULL
还是FALSE
,WHERE
子句都拒绝该行。
当这些条件中的任何一个或两个都不成立时,优化将更加复杂。
假定* outer_expr
是一个非NULL
值,但是子查询不会产生 outer_expr
* = * inner_expr
*的行。然后outer_expr IN (SELECT ...)
评估如下:
在这种情况下,使用outer_expr = inner_expr
查找行的方法不再有效。有必要查找这样的行,但是如果找不到,则还要查找* inner_expr
*为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
时,情况就更糟了。根据NULL
的 SQL 解释为“未知值”,NULL IN (SELECT inner_expr ...)
的计算结果应为:
为了进行正确的评估,必须能够检查SELECT是否已产生任何行,因此outer_expr = inner_expr
无法下推到子查询中。这是一个问题,因为许多现实世界中的子查询会变得非常缓慢,除非可以降低相等性。
本质上,根据* outer_expr
*的值,必须有不同的方法来执行子查询。
优化器选择 SQL 遵从性而不是速度,因此考虑了* outer_expr
*可能是NULL
的可能性:
- 如果*
outer_expr
*是NULL
,则要计算以下 table 达式,必须执行SELECT以确定它是否产生任何行:
NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)
必须在此处执行原始的SELECT,而没有前面提到的那种下推式等式。
- 另一方面,当*
outer_expr
*不是NULL
时,此比较绝对必要:
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
转换为使用下推条件的 table 达式:
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
没有这种转换,子查询将很慢。
为了解决是否将条件下推到子查询中的难题,将条件包装在“触发”函数中。因此,以下形式的 table 达式:
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
转换为:
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond(outer_expr=inner_expr))
更一般而言,如果子查询比较基于几对外部和内部 table 达式,则转换将采用以下比较:
(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
并将其转换为以下 table 达式:
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond(oe_1=ie_1)
AND ...
AND trigcond(oe_N=ie_N)
)
每个trigcond(X)
是一个特殊函数,其结果为以下值:
-
X
当“链接的”外部 table 达式oe_i
*不是NULL
时
-
TRUE
当“链接的”外部 table 达式*oe_i
*为NULL
时
Note
触发器函数不是您用CREATE TRIGGER创建的那种*触发器。
trigcond()
函数中包装的等式不是查询优化器的第一类谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词,因此它们假定任何trigcond(X)
为未知函数并忽略它。那些优化可以使用触发的等式:
-
参考优化:
trigcond(X=Y [OR Y IS NULL])
可用于构造ref,eq_ref或ref_or_nulltable 访问。 -
基于索引查找的子查询执行引擎:
trigcond(X=Y)
可用于构造unique_subquery或index_subquery访问。 -
table 条件生成器:如果子查询是多个 table 的联接,则将尽快检查触发条件。
当优化器使用触发条件创建某种基于索引查找的访问时(对于前面列 table 的前两项),对于条件关闭的情况,优化器必须具有回退策略。此后备策略始终相同:执行全 table 扫描。在EXPLAIN输出中,后备广告在Extra
列中显示为Full scan on NULL key
:
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`,
<in_optimizer>(`test`.`t1`.`col1`,
<exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
on key1 checking NULL
where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
`t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
from `test`.`t1`
使用触发条件会影响性能。现在,NULL IN (SELECT ...)
table 达式以前可能没有引起全 table 扫描(这很慢)。这是为获得正确结果而付出的代价(触发条件策略的目标是提高合规性,而不是速度)。
对于多 table 子查询,NULL IN (SELECT ...)
的执行特别慢,因为联接优化器不会针对外部 table 达式为NULL
的情况进行优化。它假定左侧带有NULL
的子查询评估非常少见,即使有统计数据 table 明并非如此。另一方面,如果外部 table 达式可能是NULL
但实际上不是,则不会影响性能。
为了帮助查询优化器更好地执行查询,请使用以下建议:
-
如果确实是一列,则将其声明为
NOT NULL
。通过简化色谱柱的条件测试,这也有助于优化程序的其他方面。 -
如果您不需要区分
NULL
和FALSE
子查询结果,则可以轻松避免执行路径缓慢。替换如下所示的比较:
outer_expr IN (SELECT inner_expr FROM ...)
具有以下 table 达式:
(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))
然后永远不会评估NULL IN (SELECT ...)
,因为一旦 table 达式结果明确,MySQL 就会停止评估AND个部分。
另一种可能的重写:
EXISTS (SELECT inner_expr FROM ...
WHERE inner_expr=outer_expr)
当您不需要区分NULL
和FALSE
子查询结果时,这将适用,在这种情况下,您实际上可能需要EXISTS
。
optimizer_switch系统变量的subquery_materialization_cost_based
标志可控制子查询实现和IN
到EXISTS
子查询转换之间的选择。参见第 8.9.2 节“可切换的优化”。