MySql优化之IN和EXISTS 优化_mysql in 优化-CSDN博客

本文链接：https://blog.csdn.net/leesinbad/article/details/131135821

一、用半联接变换优化IN和EXISTS子查询谓词

半联接是一种准备时转换，它支持多种执行策略，如表拉出、重复转发、首次匹配、松散扫描和物化。优化器使用半联接策略来改进子查询的执行，如本节所述。
对于两个表之间的内部联接，联接从一个表返回一行的次数与另一个表中的匹配次数一样多。但对于一些问题，唯一重要的信息是是否有比赛，而不是比赛次数。假设有一个名为“班级”和“名册”的表，分别列出课程中的班级和班级名册（每个班级的学生）。要列出实际有学生注册的课程，您可以使用此联接：

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

然而，结果为每个注册学生列出了每个班级一次。对于所提出的问题，这是不必要的信息重复。
假设class_num是类表中的主键，则可以使用SELECT DISTINCT来抑制重复，但先生成所有匹配的行，然后再消除重复是低效的。
使用子查询可以获得相同的无重复结果：

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

在这里，优化器可以识别出IN子句要求子查询只返回花名册表中每个类名的一个实例。在这种情况下，查询可以使用半联接；也就是说，只返回类中与花名册中的行匹配的每一行的一个实例的操作。
下面的语句包含EXISTS子查询谓词，相当于前面的语句包含IN子查询谓词：

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM roster WHERE class.class_num = roster.class_num);

在MySQL 8.0.16及更高版本中，任何带有EXISTS子查询谓词的语句都要经过与带有等效In子查询谓词语句相同的半联接转换。
从MySQL 8.0.17开始，以下子查询被转换为反联接：

NOT IN (SELECT ... FROM ...)
NOT EXISTS (SELECT ... FROM ...).
IN (SELECT ... FROM ...) IS NOT TRUE
EXISTS (SELECT ... FROM ...) IS NOT TRUE.
IN (SELECT ... FROM ...) IS FALSE
EXISTS (SELECT ... FROM ...) IS FALSE.

简而言之，形式为In（SELECT…FROM…）或EXISTS（SELECT…FROM…）的子查询的任何否定都将转换为反联接。
反联接是一种只返回不匹配的行的操作。请考虑此处显示的查询：

SELECT class_num, class_name
    FROM class
    WHERE class_num NOT IN
        (SELECT class_num FROM roster);

该查询在内部重写为antijoin SELECT class_num，class_name FROM class antijoin花名册ON class_num。它返回类中每行的一个实例，该实例与花名册中的任何行都不匹配。这意味着，对于类中的每一行，只要在花名册中找到匹配项，就可以丢弃类中的行。
如果要比较的表达式可以为null，则在大多数情况下无法应用反联接转换。该规则的一个例外是（…NOT IN（SELECT…））不是FALSE，其等价项（…IN（SELECT..））不是TRUE可以转换为反联接。
外部查询规范中允许使用外部联接和内部联接语法，表引用可以是基表、派生表、视图引用或公共表表达式。
在MySQL中，子查询必须满足以下条件才能作为半联接处理（或者，在MySQL 8.0.17及更高版本中，如果NOT修改子查询，则为反联接）：

它必须是出现在WHERE或ON子句顶层的IN、=ANY或EXISTS谓词的一部分，可能是AND表达式中的一个术语。例如：
```
SELECT ...
    FROM ot1, ...
    WHERE (oe1, ...) IN
        (SELECT ie1, ... FROM it1, ... WHERE ...);
```
这里，ot_i和it_i表示查询的外部和内部部分中的表，oe_i和ie_i表示引用外部和内部表中的列的表达式。
在MySQL 8.0.17及更高版本中，子查询也可以是NOT、IS[NOT]TRUE或IS[NOT]FALSE修改的表达式的参数。
它必须是一个没有UNION构造的SELECT。
它不能包含HAVING子句。
它不能包含任何聚合函数（无论是显式分组还是隐式分组）。
它不能有LIMIT子句。
语句不能在外部查询中使用STRIGHT_JOIN联接类型。
STRIGHT_JOIN修饰符不能存在。
外部表和内部表的总数必须小于联接中允许的最大表数。
子查询可以是相关的，也可以是不相关的。在MySQL 8.0.16及更高版本中，decorrelation查看用作EXISTS参数的子查询的WHERE子句中的平凡相关谓词，并使其可以像在In（SELECT b FROM…）中使用一样进行优化。平凡相关一词意味着谓词是相等谓词，是WHERE子句中唯一的谓词（或与and组合），并且一个操作数来自子查询中引用的表，而另一操作数来自外部查询块。
DISTINCT关键字是允许的，但被忽略。半联接策略自动处理重复删除。
GROUP BY子句是允许的，但会被忽略，除非子查询还包含一个或多个聚合函数。
ORDER BY子句是允许的，但会被忽略，因为排序与半联接策略的评估无关。

二、使用 EXISTS 策略优化子查询

某些优化适用于使用IN（或=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个值行的子查询进行比较要经过相同的转换。如果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。
您不需要区分NULL和FALSE子查询结果。如果子查询是WHERE子句中OR或AND表达式的一部分，MySQL会认为您不在乎。优化器注意到不需要区分NULL和FALSE子查询结果的另一个实例是以下构造：
```
... WHERE outer_expr IN (subquery)
```
在这种情况下，无论In（子查询）返回NULL还是FALSE，WHERE子句都会拒绝该行。

假设outer_expr是一个非NULL值，但是子查询没有生成这样的行，即outer_expr=inner_expr。然后outer_expr IN（SELECT…）的计算结果如下：

NULL，如果SELECT生成inner_expr为NULL的任何行
如果SELECT只生成非NULL值或不生成任何值，则返回FALSE

在这种情况下，查找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。。。ISNULL条件使查询执行稍微复杂一些（并且子查询中的一些优化变得不适用），但通常这是可以容忍的。
当outer_expr可以为NULL时，情况会更糟。根据SQL将NULL解释为“未知值”，NULL IN（SELECT inner_expr…）的计算结果应为：

NULL，如果SELECT生成任何行
如果SELECT不生成行，则返回FALSE

为了进行正确的评估，必须能够检查SELECT是否生成了任何行，因此outer_expr=inner_expr不能向下推送到子查询中。这是一个问题，因为许多现实世界中的子查询变得非常慢，除非可以降低相等性。
本质上，根据outer_expr的值，必须有不同的方式来执行子查询。
优化器选择SQL遵从性而不是速度，因此它考虑了outer_expr可能为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)
```
如果没有这种转换，子查询就会很慢。

为了解决是否将条件下推到子查询中的困境，条件被封装在“trigger”函数中。因此，以下形式的表达式：

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)
       )

每个tricond（X）都是一个特殊的函数，其求值结果如下：