MySQL 8.0.22 and later supports derived condition pushdown for eligible subqueries. MySQL 8.0.22及更高版本支持符合条件的子查询的派生条件下推。For a query such as 对于像SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > , it is possible in many cases to push the the outer constantWHERE condition down to the derived table, in this case resulting in SELECT * FROM (SELECT i, j FROM t1 WHERE i > . constant) AS dtSELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > 这样的查询，在很多情况下，可能把外部constantWHERE条件下推到派生表，在这种情况下，产生SELECT * FROM (SELECT i, j FROM t1 WHERE i > 。constant) AS dtWhen a derived table cannot be merged into the outer query (for example, if the derived table uses aggregation), pushing the outer 当无法将派生表合并到外部查询中时（例如，如果派生表使用聚合），将外部WHERE condition down to the derived table should decrease the number of rows that need to be processed and thus speed up execution of the query.WHERE条件向下推到派生表应减少需要处理的行数，从而加快查询的执行。
Prior to MySQL 8.0.22, if a derived table was materialized but not merged, MySQL materialized the entire table, then qualified all of the resulting rows with the 在MySQL 8.0.22之前，如果派生表被物化但未合并，MySQL物化整个表，然后使用WHERE condition. WHERE条件限定所有结果行。This is still the case if derived condition pushdown is not enabled, or cannot be employed for some other reason.如果派生条件下推未启用，或由于其他原因无法使用，则仍然是这种情况。
Outer 在以下情况下，可以将WHERE conditions can be pushed down to derived materialized tables under the following circumstances:WHERE条件下推到派生物化表中：
When the derived table uses no aggregate or window functions, the outer 当派生表不使用聚合或窗口函数时，可以直接将外部WHERE condition can be pushed down to it directly. WHERE条件下推到它。This includes 这包括多个用WHERE conditions having multiple predicates joined with AND, OR, or both.AND、OR或两者连接的谓词的WHERE条件。
For example, the query 例如，查询SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11 is rewritten as SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt.SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11会被重写为SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt。
When the derived table has a 当派生表具有GROUP BY and uses no window functions, an outer WHERE condition referencing one or more columns which are not part of the GROUP BY can be pushed down to the derived table as a HAVING condition.GROUP BY且不使用窗口函数时，可以将引用一个或多个不属于GROUP BY的列的外部WHERE条件作为HAVING条件下推到派生表。
For example, 例如，根据派生条件下推，SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100 is rewritten following derived condition pushdown as SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt.SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100被重写为SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt。
When the derived table uses a 当派生表使用GROUP BY and the columns in the outer WHERE condition are GROUP BY columns, the WHERE conditions referencing those columns can be pushed down directly to the derived table.GROUP BY且外部WHERE条件中的列为GROUP BY列时，可以将引用这些列的WHERE条件直接下推到派生表中。
For example, the query 例如，查询SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 is rewritten as SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt.SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10被重写为SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt。
In the event that the outer 如果外部WHERE condition has predicates referencing columns which are part of the GROUP BY as well as predicates referencing columns which are not, predicates of the former sort are pushed down as WHERE conditions, while those of the latter type are pushed down as HAVING conditions. WHERE条件具有引用属于GROUP BY的列的谓词以及引用不属于GROUP BY的列的谓词，则前一种类型的谓词将作为WHERE条件下推，而后一种类型的谓词将作为HAVING条件下推。For example, in the query 例如，在查询SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 AND sum > 100, the predicate i > 10 in the outer WHERE clause references a GROUP BY column, whereas the predicate sum > 100 does not reference any GROUP BY column. SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 AND sum > 100中，外部WHERE子句中的谓语i > 10引用了一个GROUP BY子句，而谓语sum > 100没有引用任何GROUP BY例。Thus the derived table pushdown optimization causes the query to be rewritten in a manner similar to what is shown here:因此，派生表下推优化导致以类似于此处所示的方式重写查询：
SELECT * FROM (
SELECT i, j, SUM(k) AS sum FROM t1
WHERE i > 10
GROUP BY i, j
HAVING sum > 100
) AS dt;To enable derived condition pushdown, the 要启用派生条件下推，必须将optimizer_switch system variable's derived_condition_pushdown flag (added in this release) must be set to on, which is the default setting. optimizer_switch系统变量的derived_condition_pushdown标志（在此版本中添加）设置为on，这是默认设置。If this optimization is disabled by 如果optimizer_switch, you can enable it for a specific query using the DERIVED_CONDITION_PUSHDOWN optimizer hint. optimizer_switch禁用此优化，则可以使用DERIVED_CONDITION_PUSHDOWN优化器提示为特定查询启用它。To disable the optimization for a given query, use the 要禁用给定查询的优化，请使用NO_DERIVED_CONDITION_PUSHDOWN optimizer hint.NO_DERIVED_CONDITION_PUSHDOWN优化器提示。
The following restrictions and limitations apply to the derived table condition pushdown optimization:以下限制和限制适用于衍生表格条件下推优化：
The optimization cannot be used if the derived table contains 如果派生表包含UNION.UNION，则无法使用优化。
The derived table cannot use a 派生表不能使用LIMIT clause.LIMIT子句。
Conditions containing subqueries cannot be pushed down.无法向下推包含子查询的条件。
The optimization cannot be used if the derived table is an inner table of an outer join.如果派生表是外部联接的内部表，则无法使用优化。
If a materialized derived table is a common table expression, conditions are not pushed down to it if it is referenced multiple times.如果物化派生表是公共表表达式，则如果多次引用，则不会向下推送条件。
Conditions using parameters can be pushed down if the condition is of the form 如果条件的形式为derived_column > ?derived_column > ?If a derived column in an outer 如果外部WHERE condition is an expression having a ? in the underlying derived table, this condition cannot be pushed down.WHERE条件中的派生列是在底层派生表中具有?的表达式，则此条件不能向下推。