使用 EXISTS 策略优化子查询
某些优化适用于使用IN
(或=ANY
)运算符测试子查询结果的比较。本节讨论这些优化,尤其是关于NULL
价值所面临的挑战。讨论的最后部分提出了如何帮助优化器的建议。
考虑以下子查询比较:
outer_exprIN (SELECT inner_exprFROM ...WHERE subquery_where)
MySQL的评估查询“从外到内。”即,首先获得外表达式的值outer_expr
,然后运行子查询,并且捕获的行,它产生。
一个非常有用的优化是“通知”子查询仅感兴趣的行是内部表达式inner_expr
等于的行outer_expr
。这是通过将适当的等式推入子查询的WHERE
子句以使其更具限制性来完成的。转换后的比较如下所示:
EXISTS (SELECT 1FROM ...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_NFROM ...WHERE subquery_where)
成为:
EXISTS (SELECT 1FROM ...WHERE subquery_where AND oe_1 = ie_1 AND ... AND oe_N = ie_N)
为简单起见,下面的讨论假设使用一对外部表达式值和内部表达式值。
如果满足以下任一条件,则上述“下推”策略将起作用:
outer_expr
而且inner_expr
不能NULL
。你不必区分
NULL
从FALSE
子查询结果。如果子查询是子句中OR
orAND
表达式的一部分WHERE
,则MySQL认为您不在乎。在优化程序注意到另一个实例NULL
和FALSE
子查询结果不需要区分是这样的结构:...
WHERE outer_exprIN (subquery)在这种情况下,该
WHERE
子句拒绝该行,无论是return 还是。IN(subquery)
NULL
FALSE
假设outer_expr
已知这是一个非NULL
值,但子查询不会产生诸如outer_expr
=的行inner_expr
。然后outer_expr IN(SELECT ...)
评估如下:
NULL
中,如果SELECT
产生任何行,其中inner_expr
是NULL
FALSE
,如果SELECT
仅产生非NULL
值或什么都不产生
在这种情况下,使用查找行的方法不再有效。有必要查找这样的行,但是如果找不到,则还要在is处查找行。粗略地说,子查询可以转换为如下形式:outer_expr=inner_expr
inner_expr
NULL
EXISTS (SELECT 1FROM ...WHERE subquery_where AND (outer_expr=inner_expr OR inner_expr IS NULL))
需要评估额外IS NULL
条件是MySQL具有ref_or_null
访问方法的原因:
mysql>EXPLAIN SELECT outer_exprIN (SELECT t2.maybe_null_keyFROM t2, t3WHERE ...)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 Extr : 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_exprFROM ...WHERE subquery_where)必须在
SELECT
此处执行原始文件,而没有前面提到的那种下推式等价物。另一方面,当
outer_expr
不是时NULL
,此比较绝对必要:outer_expr
IN (SELECT inner_exprFROM ...WHERE subquery_where)转换为使用下推条件的表达式:
EXISTS (SELECT 1FROM ...WHERE subquery_where AND outer_expr=inner_expr)没有这种转换,子查询将很慢。
为了解决是否将条件下推到子查询中的难题,将条件包装在“触发器”函数中。因此,以下形式的表达式:
outer_exprIN (SELECT inner_exprFROM ...WHERE subquery_where)
转换为:
EXISTS (SELECT 1FROM ...WHERE subquery_where AND trigcond(outer_expr=inner_expr))
更一般而言,如果子查询比较基于几对外部和内部表达式,则转换将采用以下比较:
(oe_1, ..., oe_N)IN (SELECT ie_1, ..., ie_NFROM ...WHERE subquery_where)
并将其转换为以下表达式:
EXISTS (SELECT 1FROM ...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])
ref
eq_ref
ref_or_null
- 基于索引查找的子查询执行引擎:可用于构造或访问。
trigcond(X=Y)
unique_subquery
index_subquery
- 表条件生成器:如果子查询是多个表的联接,则将尽快检查触发条件。
当优化器使用触发条件创建某种基于索引查找的访问时(对于前面列表的前两项),对于条件关闭的情况,优化器必须具有回退策略。此后备策略始终相同:执行全表扫描。在EXPLAIN
输出中,回退显示Full scan on NULL key
在Extr
列中:
mysql>EXPLAIN SELECT t1.col1, t1.col1IN (SELECT t2.key1FROM t2WHERE 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 Extr : 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 ...)
现在表达可能会导致全表扫描(这是慢)时,它以前没有。这是为获得正确结果而付出的代价(触发条件策略的目标是提高合规性,而不是速度)。
对于多表子查询,NULL IN(SELECT ...)
因为连接优化器未针对外部表达式为的情况进行优化,所以执行特别慢NULL
。它假定NULL
左侧的子查询求值非常少见,即使有统计数据表明并非如此。另一方面,如果外部表达式可能是NULL
但实际上不是,则不会影响性能。
为了帮助查询优化器更好地执行查询,请使用以下建议:
- 声明一列,就
NOT NULL
好像它确实是一样。通过简化色谱柱的条件测试,这也有助于优化程序的其他方面。 如果您不需要区分
NULL
来自FALSE
子查询的结果,你可以很容易地避免慢的执行路径。替换如下所示的比较:outer_expr [NOT]
IN (SELECT inner_exprFROM ...)具有以下表达式:
(outer_expr IS NOT NULL) AND (outer_expr [NOT]
IN (SELECT inner_exprFROM ...))然后
NULL IN(SELECT ...)
永远不会进行评估,因为AND
一旦表达式结果明确,MySQL就会停止评估零件。另一种可能的重写:
[NOT]
EXISTS (SELECT inner_exprFROM ...WHERE inner_expr=outer_expr)
在subquery_materialization_cost_based
所述的标志optimizer_switch
系统变量使得能够在子查询物化和之间的选择控制IN
-到-EXISTS
子查询变换。请参见“可切换的优化”。