某些优化适用于比较使用在(或=任何)操作符来测试子查询的结果。本节讨论这些优化,特别是关于挑战零值。的最后一部分讨论建议如何帮助优化器。

考虑下面的子查询的比较:

outer_expr(选择inner_expr从…在哪里subquery_where)

MySQL评估查询”从外到内。”也就是说,它首先获得外表达式的值outer_expr,然后运行子查询和捕捉它产生的行。

一个非常有用的优化”通知”子查询,只有行感兴趣的是那些内心的表达inner_expr等于outer_expr。这是通过压低到子查询的一个适当的平等在哪里更严格的条款。转换比较是这样的:

存在(选择1…在哪里subquery_where和outer_expr=inner_expr)

转换后,MySQL可以利用下推平等限制的行数必须检查评估子查询。

更一般的,比较N子查询返回的值N值行相同的转换。如果oe_i和ie_i代表相应的外部和内部表达式的值,这个子查询的比较:

(oe_1、……oe_N)(选择ie_1、……ie_N从…在哪里subquery_where)

就变成:

存在(选择1…在哪里subquery_where和oe_1=ie_1和…和oe_N=ie_N)

为简单起见,下面的讨论假设一个单一的外部和内部表达式的值。

的”后进先出存储的”战略刚刚描述的作品如果这些条件是正确的:

假设outer_expr是一个非-零值,但子查询不会产生这样一行outer_expr=inner_expr。然后outer_expr(选择…)评估如下:

在这种情况下,寻找行的方法outer_expr=inner_expr不再有效。有必要寻找这样的行,但如果没有找到,也行,寻找inner_expr是零。大致说来,子查询可以转换成这样:

存在(选择1…在哪里subquery_where和(outer_expr=inner_expr或inner_expr为空)

评估额外的需要为空就是为什么MySQL的条件ref_or_null访问方法:

mysql >解释选择outer_expr(选择t2。maybe_null_key t2、t3的地方…)从t1;* * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type:主要表:t1……* * * * * * * * * * * * * * * * * * * * * * * * * * * 2。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 2 select_type:依赖表子查询:t2类型:ref_or_null possible_keys: maybe_null_key关键:maybe_null_key key_len: 5裁判:func行:2额外的:使用的地方;使用索引…

的unique_subquery和index_subquerysubquery-specific访问方法也有”或零”变体。

额外的还是……为空条件使查询执行更为复杂的(和一些优化子查询中变得不适用),但通常这是可以忍受的。

时的情况更加糟糕outer_expr可以零。根据SQL的解释零作为”未知的价值,”零(选择inner_expr…)应该评估:

适当的评估,它可以检查是否是必要的选择产生了任何行,所以呢outer_expr=inner_expr不能下推到子查询。这是一个问题,因为许多实际的子查询变得非常缓慢,除非平等可以下推。

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

优化器选择SQL执行速度,所以占的可能性outer_expr可能是零:

解决的困境是否降低条件到子查询,包裹在条件”触发”功能。因此,下面的表达式:

outer_expr(选择inner_expr从…在哪里subquery_where)

转化为:

存在(选择1…在哪里subquery_where和trigcond (outer_expr=inner_expr))

更一般地,如果子查询比较是基于外部和内部表达式的几双,转换采用这种比较:

(oe_1、……oe_N)(选择ie_1、……ie_N从…在哪里subquery_where)

并将其转换为表达式:

存在(选择1…在哪里subquery_where和trigcond (oe_1=ie_1)和…和trigcond (oe_N=ie_N))

每一个trigcond (X)是一个特殊函数,计算结果为以下值:

包装内的平等trigcond ()函数并不是一流的谓词的查询优化器。大多数优化不能处理谓词可能开启和关闭在查询执行时间,因此他们承担trigcond (X)一个未知函数,忽略它。触发平等可以使用这些优化:

当优化器使用一个触发条件创建某种指数lookup-based访问(前两项前面的列表),它必须有一个回退策略条件的情况下是关闭的,这种回退策略总是相同的:做一个全表扫描。在解释输出,回退了全扫描零的关键在额外的专栏:

mysql >解释选择t1。col1, t1。col1(选择t2。从t2 key1 t2.col2 = t1.col2)从t1 \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 select_type:主要表:t1……* * * * * * * * * * * * * * * * * * * * * * * * * * * 2。行* * * * * * * * * * * * * * * * * * * * * * * * * * * id: 2 select_type:依赖表子查询:t2类型:index_subquery possible_keys: key1关键:key1 key_len: 5裁判:func行:2额外的:使用的地方;全扫描零的关键

如果你运行解释紧随其后的是显示警告,您可以看到触发条件:

* * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * *水平:注意代码:1003信息:选择“测试”。“t1”。“col1”“col1”, < in_optimizer >(“测试”“t1”。存在“col1”, < > (< index_lookup >(<缓存>(“测试”“t1”。“col1”)在t2 key1检查NULL(‘测试’。‘t2’的地方。col2 =‘测试’。‘t1’。“col2”)拥有trigcond (< is_not_null_test >('测试' ' t2 '。' key1 ')))))的t1。col1(选择t2。key1从t2 t2.col2 = t1.col2)“从‘测试’。‘t1’

使用触发条件有一些性能影响。一个零(选择…)表达现在可能导致全表扫描(缓慢)当它以前没有。付出的代价就是正确的结果(触发器条件的目标策略是提高合规,没有速度)。

多个表子查询的执行零(选择…)特别慢,因为加入优化器不优化的情况外的表情是什么零。它假设子查询评估零左边是非常罕见的,即使有统计表明并非如此。另一方面,如果外面的表达式零但从来没有真正是,没有性能损失。

帮助查询优化器更好地执行你的查询,使用这些建议:

的subquery_materialization_cost_based国旗的optimizer_switch系统变量使控制子查询实体化和之间的选择在- - - - - - - - -存在子查询的转换。看到部分8.9.2,“可切换的优化”。