MySQL :: MySQL 8.4 参考手册 :: 10.2.2.5 派生条件下推优化

版本 8.4

9.0
8.0

8.0 日语

MySQL 8.4 参考手册 / ... / 派生条件下推优化

10.2.2.5 派生条件下推优化

对于符合条件的子查询，MySQL 支持派生条件下推。对于诸如 SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > 常量 之类的查询，在许多情况下，可以将外部 WHERE 条件下推到派生表，在本例中，结果为 SELECT * FROM (SELECT i, j FROM t1 WHERE i > 常量) AS dt。当派生表无法合并到外部查询中时（例如，如果派生表使用聚合），将外部 WHERE 条件下推到派生表应该会减少需要处理的行数，从而加快查询的执行速度。

在以下情况下，可以将外部 WHERE 条件下推到派生的物化表：

当派生表不使用聚合或窗口函数时，外部 WHERE 条件可以直接下推到该派生表。这包括具有多个使用 AND、OR 或两者连接的谓词的 WHERE 条件。
例如，查询 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。
当派生表具有 GROUP BY 并且不使用窗口函数时，可以将引用一个或多个不属于 GROUP BY 的列的外部 WHERE 条件作为 HAVING 条件下推到派生表。
例如，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。
当派生表使用 GROUP BY 并且外部 WHERE 条件中的列是 GROUP BY 列时，可以将引用这些列的 WHERE 条件直接下推到派生表。
例如，查询 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。
如果外部 WHERE 条件同时具有引用 GROUP BY 中的列的谓词和引用不属于 GROUP BY 的列的谓词，则前一种谓词作为 WHERE 条件下推，而后一种谓词作为 HAVING 条件下推。例如，在查询 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 列。因此，派生表下推优化会导致查询以类似于此处所示的方式重写：
```
SELECT * FROM (
    SELECT i, j, SUM(k) AS sum FROM t1
        WHERE i > 10
        GROUP BY i, j
        HAVING sum > 100
    ) AS dt;
```

要启用派生条件下推，必须将 optimizer_switch 系统变量的 derived_condition_pushdown 标志（在本版本中添加）设置为 on，这是默认设置。如果此优化被 optimizer_switch 禁用，则可以使用 DERIVED_CONDITION_PUSHDOWN 优化器提示为特定查询启用它。要为给定查询禁用此优化，请使用 NO_DERIVED_CONDITION_PUSHDOWN 优化器提示。

以下限制和局限性适用于派生表条件下推优化：

派生表条件下推优化可以与 UNION 查询一起使用，但以下情况除外
- 如果构成 UNION 的任何物化派生表是递归公用表表达式（请参阅递归公用表表达式），则条件下推不能与 UNION 查询一起使用。
- 包含非确定性表达式的条件不能下推到派生表。
派生表不能使用 LIMIT 子句。
包含子查询的条件不能下推。
如果派生表是外连接的内表，则不能使用此优化。
如果物化派生表是公用表表达式，并且被多次引用，则条件不会下推到该表。
如果条件的形式为 derived_column > ?，则可以使用参数下推条件。如果外部 WHERE 条件中的派生列是在底层派生表中具有 ? 的表达式，则无法下推此条件。
对于条件位于使用 ALGORITHM=TEMPTABLE 创建的视图的表上而不是视图本身的查询，在解析时无法识别多个相等性，因此无法下推条件。这是因为，在优化查询时，条件下推发生在解析阶段，而多个相等性传播发生在优化阶段。
对于使用 ALGORITHM=MERGE 的视图，这种情况不是问题，因为可以传播相等性并下推条件。
如果派生表的 SELECT 列表包含对用户变量的任何赋值，则无法下推条件。