MySQL 8.0-13.2.11.7 Correlated Subqueries(关联子查询)

MySQL 8.0关联子查询优化与转换

最新推荐文章于 2024-03-05 14:33:48 发布

翻译最新推荐文章于 2024-03-05 14:33:48 发布 · 1.8k 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://dev.mysql.com/doc/refman/8.0/en/correlated-subqueries.html

文章标签：

#mysql #elasticsearch #数据库

本文详细介绍了MySQL 8.0中关联子查询的工作原理，包括其在查询中的作用和作用域规则。从8.0.24版本开始，优化器能够将关联的标量子查询转换为派生表，前提是满足特定条件，如子查询不能包含JOIN条件、LIMIT或OFFSET子句，以及特定的WHERE子句限制。这个优化可以提高查询效率，减少多次物质化。

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query. For example:

相关子查询是一个子查询，它包含对同样出现在外部查询中的表的引用。例如:

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

Notice that the subquery contains a reference to a column of t1, even though the subquery's FROM clause does not mention a table t1. So, MySQL looks outside the subquery, and finds t1 in the outer query.

注意，子查询包含对t1列的引用，即使子查询的FROM子句没有提到表t1。MySQL查找子查询的外部，并在外部查询中找到t1。

Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile, table t2 contains a row where column1 = 5 and column2 = 7. The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example, the WHERE clause within the subquery is FALSE (because (5,6) is not equal to (5,7)), so the expression as a whole is FALSE.

假设表t1包含一行，其中column1 = 5, column2 = 6;同时，表t2包含一行，其中column1 = 5和column2 = 7。这个简单的表达…WHERE column1 = ANY (SELECT column1 FROM t2)将为TRUE，但在本例中，子查询中的WHERE子句为FALSE(因为(5,6)不等于(5,7))，因此表达式作为一个整体是FALSE。

Scoping rule: MySQL evaluates from inside to outside. For example:

作用域规则:MySQL从内部到外部计算。例如:

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3
      WHERE x.column2 = t3.column1));

In this statement, x.column2 must be a column in table t2 because SELECT column1 FROM t2 AS x ... renames t2. It is not a column in table t1 because SELECT column1 FROM t1 ... is an outer query that is farther out.

在这个语句中，x.column2必须是表t2中的一列，因为SELECT column1 FROM t2 AS x…重命名t2。它不是表t1中的列，因为SELECT column1 FROM t1…是一个更远的外部查询。

Beginning with MySQL 8.0.24, the optimizer can transform a correlated scalar subquery to a derived table when the subquery_to_derived flag of the optimizer_switch variable is enabled. Consider the query shown here:

从MySQL 8.0.24开始，当启用optimizer_switch变量的subquery_to_derived标志时，优化器可以将相关的标量子查询转换为派生表。考虑这里显示的查询:

SELECT * FROM t1 
    WHERE ( SELECT a FROM t2 
              WHERE t2.a=t1.a ) > 0;

To avoid materializing several times for a given derived table, we can instead materialize—once—a derived table which adds a grouping on the join column from the table referenced in the inner query (t2.a) and then an outer join on the lifted predicate (t1.a = derived.a) in order to select the correct group to match up with the outer row. (If the subquery already has an explicit grouping, the extra grouping is added to the end of the grouping list.) The query previously shown can thus be rewritten like this:

为了避免对给定的派生表进行多次具体化，我们可以一次性具体化一个派生表，该派生表将在内部查询(t2.a)中引用的表的join列上添加一个分组，然后在提升谓词(t1. a)上添加一个外部连接。A = derived.a)，以便选择正确的组与外部行匹配。(如果子查询已经具有显式分组，则额外的分组被添加到分组列表的末尾。)前面显示的查询可以这样重写:

SELECT t1.* FROM t1 
    LEFT OUTER JOIN
        (SELECT a, COUNT(*) AS ct FROM t2 GROUP BY a) AS derived
    ON  t1.a = derived.a 
        AND 
        REJECT_IF(
            (ct > 1),
            "ERROR 1242 (21000): Subquery returns more than 1 row"
            )
    WHERE derived.a > 0;

In the rewritten query, REJECT_IF() represents an internal function which tests a given condition (here, the comparison ct > 1) and raises a given error (in this case, ER_SUBQUERY_NO_1_ROW) if the condition is true. This reflects the cardinality check that the optimizer performs as part of evaluating the JOIN or WHERE clause, prior to evaluating any lifted predicate, which is done only if the subquery does not return more than one row.

在重写的查询中，REJECT_IF()表示一个内部函数，它测试给定的条件(这里是比较ct > 1)，并在条件为真时引发给定的错误(在本例中是ER_SUBQUERY_NO_1_ROW)。这反映了优化器在计算JOIN或WHERE子句之前执行的基数检查，只有在子查询不返回多于一行的情况下才会执行基数检查。

This type of transformation can be performed, provided the following conditions are met:

只要满足以下条件，就可以进行这种类型的转换:

The subquery can be part of a SELECT list, WHERE condition, or HAVING condition, but cannot be part of a JOIN condition, and cannot contain a LIMIT or OFFSET clause. In addition, the subquery cannot contain any set operations such as UNION.
子查询可以是SELECT列表、WHERE条件或HAVING条件的一部分，但不能是JOIN条件的一部分，且不能包含LIMIT或OFFSET子句。此外，子查询不能包含任何集合操作，如UNION。
The WHERE clause may contain one or more predicates, combined with AND. If the WHERE clause contains an OR clause, it cannot be transformed. At least one of the WHERE clause predicates must be eligible for transformation, and none of them may reject transformation.
WHERE子句可以包含一个或多个谓词，与AND组合在一起。如果WHERE子句包含OR子句，则不能对其进行转换。必须至少有一个WHERE子句谓词符合转换条件，而且它们都不能拒绝转换。
To be eligible for transformation, a WHERE clause predicate must be an equality predicate in which each operand should be a simple column reference. No other predicates—including other comparison predicates—are eligible for transformation. The predicate must employ the equality operator = for making the comparison; the null-safe ≪=> operator is not supported in this context.
要符合转换条件，WHERE子句谓词必须是相等谓词，其中每个操作数都应该是简单列引用。没有其他谓词(包括其他比较谓词)符合转换条件。谓词必须使用相等操作符=进行比较;null-safe =>操作员在这种情况下不被支持。
A WHERE clause predicate that contains only inner references is not eligible for transformation, since it can be evaluated before the grouping. A WHERE clause predicate that contains only outer references is eligible for transformation, even though it can be lifted up to the outer query block. This is made possible by adding a cardinality check without grouping in the derived table.
只包含内部引用的WHERE子句谓词不适合转换，因为它可以在分组之前计算。只包含外部引用的WHERE子句谓词符合转换条件，即使它可以提升到外部查询块。这可以通过在派生表中添加不进行分组的基数检查实现。
To be eligible, a WHERE clause predicate must have one operand that contains only inner references and one operand that contains only outer references. If the predicate is not eligible due to this rule, transformation of the query is rejected.
要符合条件，WHERE子句谓词必须有一个只包含内部引用的操作数和一个只包含外部引用的操作数。如果谓词不符合此规则，则拒绝查询的转换。
A correlated column can be present only in the subquery's WHERE clause (and not in the SELECT list, a JOIN or ORDER BY clause, a GROUP BY list, or a HAVING clause). Nor can there be any correlated column inside a derived table in the subquery's FROM list.
相关列只能出现在子查询的WHERE子句中(而不能出现在SELECT列表、JOIN或ORDER BY子句、GROUP BY列表或HAVING子句中)。子查询的FROM列表中的派生表中也不能有任何相关列。
A correlated column can not be contained in an aggregate function's list of arguments.
关联列不能包含在聚合函数的参数列表中。
A correlated column must be resolved in the query block directly containing the subquery being considered for transformation.
必须在直接包含要考虑进行转换的子查询的查询块中解析相关列。
A correlated column cannot be present in a nested scalar subquery in the WHERE clause.
关联列不能出现在WHERE子句中的嵌套标量子查询中。
The subquery cannot contain any window functions, and must not contain any aggregate function which aggregates in a query block outer to the subquery. A COUNT() aggregate function, if contained in the SELECT list element of the subquery, must be at the topmost level, and cannot be part of an expression.
子查询不能包含任何窗口函数，也不能包含在子查询外部的查询块中聚合的聚合函数。如果COUNT()聚合函数包含在子查询的SELECT列表元素中，则必须位于最高层，并且不能是表达式的一部分。