MySQL-5.7-8.2.2 Optimizing Subqueries, Derived Tables, and View References(优化子查询、派生表和视图引用)

8.2.2.1 Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations

8.2.2.2 Optimizing Subqueries with Materialization

8.2.2.3 Optimizing Subqueries with the EXISTS Strategy

8.2.2.4 Optimizing Derived Tables and View References with Merging or Materialization

The MySQL query optimizer has different strategies available to evaluate subqueries:

MySQL查询优化器有不同的策略来评估子查询:

For IN (or =ANY) subqueries, the optimizer has these choices:

对于IN (or =ANY)子查询，优化器有以下选择:

• Semijoin

• 半联结

• Materialization

• 具体化

• EXISTS strategy

• EXISTS  策略

For NOT IN (or <>ALL) subqueries, the optimizer has these choices:

对于NOT IN(或<>ALL)子查询，优化器有以下选择:

• Materialization

• EXISTS strategy

For derived tables, the optimizer has these choices (which also apply to view references):

对于派生表，优化器有这些选择(也适用于视图引用):

• 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

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.

使用子查询修改单个表的UPDATE和DELETE语句的一个限制是，优化器不使用半连接或具体化子查询优化。

As a workaround, try rewriting them as multiple-table UPDATE and DELETE statements that use a join rather than a subquery.

作为一种解决方案，尝试将它们重写为使用联接而不是子查询的多表UPDATE和DELETE语句。
​