MySql教程

MySQL 使用 EXISTS 优化子查询

本文主要是介绍MySQL 使用 EXISTS 优化子查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

某些优化适用于使用IN(或=ANY)来测试子查询结果的比较,特别是关于NULL带来的。讨论的最后一部分建议如何优化。

以下两个表数据基本一致都在一万左右

考虑以下子查询比较:

SELECT
	* 
FROM
	retired_cadre_base 
WHERE
	cardre_id IN (
	SELECT
		cardre_id 
	FROM
		retired_cadre_family 
	WHERE
	post_code = '317000')

MySQL“从外到内”执行查询。也就是说,它首先获取外部表达式的值,然后运行子查询并获取它产生的行。

这里的优化可以是赋连接条件于子查询,只有内部表达式inner_expr等于outer_expr的行才是所关注的。这是通过向子查询的WHERE子句下推一个适当的相等项来实现的,限制查询范围。转换后的SQL语句如下所示:

SELECT
	* 
FROM
	retired_cadre_base a 
WHERE
	EXISTS (
	SELECT
		1 
	FROM
		retired_cadre_family b 
	WHERE
	post_code = '317000' 
	AND a.cardre_id = b.cardre_id)

转换之后,MySQL就能够使用下推而来的等式去限制行的数量,即在计算子查询时必须使用该等式进行检查。

更一般地说,将N个值与返回N个行值的子查询进行比较时,也需要进行同样的转换。如果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)

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

如果以下任何一个条件为真,则上述讨论过的“下推”策略也有效:

outer_expr和inner_expr都不能为NULL。

如果该子查询是WHERE子句中的OR或AND的组成部分,则MySQL假设你不会在意。因此你不必区分子查询结果是NULL还是FALSE。优化器注意到另一种不必区分子查询结果是NULL或FALSE的实例,请看如下构建的条件:

... WHERE outer_expr IN (subquery)

在这种情况下,WHERE子子句都会拒绝不管IN (subquery)返回NULL还是FALSE时的行。

假设已知outer_expr的值是非空的,但是,子查询不能产生符合outer_expr = inner_expr的行。那么,outer_expr IN (SELECT inner_expr …)计算结果如下:

如果SELECT产生的任何行的条件是inner_expr是NULL,那么以上表达式计算结果是NULL

如果SELECT产生的只有非空值或什么也没有产生,那么以上表达式计算结果是FALSE

在这种情况下,寻找符合outer_expr = inner_expr的方法已无效。虽然寻找这样的行是必要的,但是如果找不到,那么也要寻找符合inner_expr is NULL的行。粗略地说,该子查询可以被转换成以下形式:

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

需要额外计算的IS NULL条件就是为什么MySQL有ref_or_null(引用或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             # 引用或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(索引子查询)也有 “or NULL”变体。

另外,条件OR … IS NULL使查询稍微复杂一些(且子查询中的一些优化变得不可用),但是,通常这是可接受的。

当outer_expr(外部表达式)也可以是NULL时,情况会更糟。按照SQL会把NULL解析为“未知值(unkown value)”这种逻辑,那么,对NULL IN (SELECT inner_expr …)的计算应该按照如下规则进行:

如果SELECT产生任何行,则NULL

如果SELECT没有产生行,则FALSE

为了进行正确地计算,必须要能够检查SELECT究竟是否已经产生了任何行,所以,不能把 outer_expr = inner_expr下推到子查询中。这是许多真实世界中子查询变得非常慢的原因之一,除非该等式能被下推。

本质上,根据外部表达式(outer_expr)的值,必须有不同的执行子查询的方法。

优化器选择了SQL遵从而不是速度,因此它考虑了外部表达式可能是NULL的可能性:

如果外部表达式是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)

如果没有这种转换,则子查询执行速度是慢的。

为了解决是否要下推条件到子查询中这样的困境,需要使用“触发器(trigger)”函数把这些条件包裹起来。因此,使用以下形式的表达式:

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

转换为:

EXISTS (SELECT 1 
				FROM ... 
				WHERE subquery_where
                         AND trigcond(outer_expr = inner_expr))   # trigcond: 条件触发

更一般地说,如果子查询的比较是基于几对外部和内部表达式,那么采取以下转换进行比较:

(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)都是计算以下值的特殊函数:

当“连接”的外部表达式oe_i不是NULL时,结果是X

当“连接”的外部表达式oe_i是NULL时,结果是TRUE

注意:
这里的触发器函数不是使用CREAT TRIGGER创建的触发器。

包裹在trigcond()函数中的等式并不是查询优化器的头等谓词。大多数优化器不能处理这种在查询执行期间可以开和关的谓词,因此它们假设任何trigcond()都是未知函数而忽略它。触发的等式可以在以下优化中使用:

引用优化:可以使用 trigcond(X = Y [OR Y IS NULL])来构造ref、eq_ref或ref_or_null表的访问。

基于索引查找的子查询执行引擎:trigcond(X = Y)可用于构建唯一子查询或索引子查询访问。

表条件生成器:如果子查询是多个表的连接,则应该尽快检查该触发的条件。

当优化器使用触发条件来创建某种基于索引查找的访问时(就像上面列表中的前两项一样),它必须有一个回退策略,以应对出现条件关闭时的情形。这种回退策略始终是一样的:即做全表扫描。在EXPLAIN输出的Extra列中,回退显示为Full scan on NULL key(在NULL键上进行全表扫描):

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…)表达式可能会导致全表扫描(速度很慢),而以前不会。这是为得到正确的结果所付出的代价(触发条件策略的目标是提高遵从性,而不是速度)。

对于多表子查询,由于连接优化器对外部表达式是NULL的情况不能优化,因此,NULL IN (SELECT …)的执行特别慢。即使有统计数据表明不是这样,它还是假设左侧为NULL的子查询计算非常稀少。另一方面,如果外部表达式可能为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 …)永远不会执行,因为一旦此表达式结果已经明确(译者:如果outer_expr是NULL,则(outer_expr IS NOT NULL)就是(NULL IS NOT NULL),此表达式整体计算结果是FALSE**,也就是通过如此转换,如果子查询返回NULL就转换成FALSE),MySQL就会立即停止计算AND部分。

另一种可能的重写:

[NOT] EXISTS (SELECT inner_expr FROM ...
        WHERE inner_expr = outer_expr)

optimizer_switch(优化器切换)系统变量的subquery_materialization_cost_based(基于子查询物化成本)标志控制着选择那一种策略:子查询物化,还是,IN-to-EXISTS子查询转换。

这篇关于MySQL 使用 EXISTS 优化子查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!