MySQL 8.4 Release Notes
- 10.2.2.1 使用半连接和反连接转换优化 IN 和 EXISTS 子查询谓词10.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations
- 10.2.2.2 通过物化优化子查询10.2.2.2 Optimizing Subqueries with Materialization
- 10.2.2.3 使用 EXISTS 策略优化子查询10.2.2.3 Optimizing Subqueries with the EXISTS Strategy
- 10.2.2.4 使用合并或物化优化派生表、视图引用和公用表表达式10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization
- 10.2.2.5 派生条件下推优化10.2.2.5 Derived Condition Pushdown Optimization
MySQL 查询优化器有不同的策略可用于评估子查询：The MySQL query optimizer has different strategies available to evaluate subqueries:
对于与
IN、= ANY或EXISTS谓词一起使用的子查询，优化器有以下选择：For a subquery used with anIN,= ANY, orEXISTSpredicate, the optimizer has these choices:半连接Semijoin
物化Materialization
EXISTS策略EXISTSstrategy
对于与
NOT IN、<> ALL或NOT EXISTS谓词一起使用的子查询，优化器有以下选择：For a subquery used with aNOT IN,<> ALLorNOT EXISTSpredicate, the optimizer has these choices:物化Materialization
EXISTS策略EXISTSstrategy
对于派生表，优化器有以下选择（这些选择也适用于视图引用和公用表表达式）：For a derived table, the optimizer has these choices (which also apply to view references and common table expressions):
将派生表合并到外部查询块中Merge the derived table into the outer query block
将派生表物化为内部临时表Materialize the derived table to an internal temporary table
以下讨论提供了关于前述优化策略的更多信息。The following discussion provides more information about the preceding optimization strategies.
Note
使用子查询修改单个表的 UPDATE 和 DELETE 语句的一个限制是优化器不会使用半连接或物化子查询优化。作为解决方法，可以尝试将它们重写为使用连接而非子查询的多表 UPDATE 和 DELETE 语句。A limitation on UPDATE and
DELETE statements that use a
subquery to modify a single table is that the optimizer does
not use semijoin or materialization subquery optimizations. As
a workaround, try rewriting them as multiple-table
UPDATE and
DELETE statements that use a
join rather than a subquery.