一、用半联接变换优化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）都是一个特殊的函数，其求值结果如下：

• 当“链接的”外部表达式oe_i不为NULL时的X
• 当“链接的”外部表达式oe_i为NULL时为TRUE