某些优化适用于使用IN
(or =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
and
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)
为简单起见,以下讨论假定一对外部和内部表达式值。
刚才描述的转换有其局限性。只有当我们忽略可能的NULL
值时它才有效。也就是说,只要以下两个条件都为真
, “下推”策略就有效:
当这些条件中的一个或两个都不成立时,优化就会更加复杂。
假设outer_expr
已知那是一个非NULL
值,但子查询不会生成满足
outer_expr
=
的行inner_expr
。然后
评估如下:
outer_expr
IN (SELECT
...)
在这种情况下,查找行的方法
不再有效。有必要寻找这样的行,但如果没有找到,还要寻找行 where
is
。粗略地说,子查询可以转换成这样:
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
”变体。但是,它们在EXPLAIN
输出中不可见,因此您必须使用EXPLAIN
EXTENDED
后跟SHOW
WARNINGS
(注意checking NULL
警告消息中的 ):
mysql> EXPLAIN EXTENDED
SELECT outer_expr IN (SELECT maybe_null_key FROM t2) 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: maybe_null_key
key: maybe_null_key
key_len: 5
ref: func
rows: 2
Extra: Using index
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select (`test`.`t1`.`outer_expr`,
(((`test`.`t1`.`outer_expr`) in t2 on
maybe_null_key checking NULL))) AS `outer_expr IN (SELECT
maybe_null_key FROM t2)` from `test`.`t1`
附加OR ... IS NULL
条件使查询执行稍微复杂一些(并且子查询中的一些优化变得不适用),但通常这是可以容忍的。
outer_expr
可
情况更糟的时候
NULL
。根据NULL
作为“未知值”
的 SQL 解释,应该评估为:
NULL IN (SELECT
inner_expr
...)
为了进行正确的评估,有必要能够检查 是否SELECT
产生了任何行,因此
不能下推到子查询中。这是一个问题,因为许多现实世界的子查询变得非常慢,除非可以降低相等性。
outer_expr
=
inner_expr
本质上,必须有不同的方法来执行子查询,具体取决于 的值
outer_expr
。
outer_expr
优化器选择 SQL 合规性而不是速度,因此它考虑
了以下可能性
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()
函数中的等式不是查询优化器的一流谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词,因此它们假定 any
是未知函数并忽略它。这些优化可以使用触发的等式:
trigcond(
X
)
引用优化: 可用于构造 、 或 表访问。
trigcond(
X
=Y
[ORY
IS NULL])ref
eq_ref
ref_or_null
基于索引查找的子查询执行引擎: 可用于构造 或 访问。
trigcond(
X
=Y
)unique_subquery
index_subquery
表条件生成器:如果子查询是多个表的join,则尽快检查触发条件。
当优化器使用触发条件创建某种基于索引查找的访问时(对于前面列表的前两项),它必须有一个回退策略来应对条件关闭的情况。这种回退策略总是相同的:进行全表扫描。在
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 EXTENDED
后跟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 IN (SELECT inner_expr FROM ...)
用这个表达式:
(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))
Then
NULL IN (SELECT ...)
永远不会被评估,因为AND
一旦表达式结果明确,MySQL 就会停止评估部分。另一种可能的重写:
EXISTS (SELECT inner_expr FROM ... WHERE inner_expr=outer_expr)
这适用于不需要区
NULL
分子FALSE
查询结果的情况,在这种情况下您可能确实需要EXISTS
.
系统变量的
subquery_materialization_cost_based
标志optimizer_switch
可以控制子查询物化和子查询转换之间的
IN
选择EXISTS
。请参阅
第 8.9.2 节,“可切换优化”。