8.2.2.5 派生条件下推优化_MySQL 8.0 参考手册

MySQL 8.0 参考手册 / 第8章优化 / 8.2 优化SQL语句 / 8.2.2 优化子查询、派生表、视图引用和公用表表达式 / 8.2.2.5 派生条件下推优化

8.2.2.5 派生条件下推优化

MySQL 8.0.22 及更高版本支持符合条件的子查询的派生条件下推。对于诸如之类的查询，在许多情况下可以将外部条件下推到派生表，在这种情况下会导致 . 当派生表无法合并到外层查询时（例如，如果派生表使用聚合），将外部条件下推到派生表应该减少需要处理的行数，从而加快执行速度询问。 SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constantWHERESELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dtWHERE

笔记

在 MySQL 8.0.22 之前，如果派生表被具体化但未合并，MySQL 会具体化整个表，然后使用条件限定所有结果行 WHERE。如果未启用派生条件下推，或者由于某些其他原因无法使用，情况仍然如此。

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

当派生表不使用聚合或窗口函数时，WHERE可以直接将外部条件下推给它。这包括 WHERE具有多个谓词与AND、 OR或两者连接的条件。
例如，查询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.
当派生表具有 a并且不使用窗口函数时，引用一个或多个不属于的列GROUP BY 的外部条件可以作为条件下推到派生表。 WHEREGROUP BYHAVING
例如，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。
当派生表使用 aGROUP 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列，则前一类谓词作为 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的谓词引用了一个列，而谓词不引用任何 i > 10WHEREGROUP BYsum > 100GROUP 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。MySQL 8.0.29 中取消了此限制。考虑两个表 t1和t2，以及一个v包含它们联合的视图，创建如下所示：
```
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  c1 INT, 
  KEY i1 (c1)
);

CREATE TABLE t2 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  c1 INT, 
  KEY i1 (c1)
);

CREATE OR REPLACE VIEW v AS
     SELECT id, c1 FROM t1
     UNION ALL
     SELECT id, c1 FROM t2;
```
从的输出中可以看出， EXPLAIN查询顶层中存在的条件 SELECT * FROM v WHERE c1 = 12现在可以下推到派生表中的两个查询块：
```
mysql> EXPLAIN FORMAT=TREE SELECT * FROM v WHERE c1 = 12\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on v  (cost=1.26..2.52 rows=2)
    -> Union materialize  (cost=2.16..3.42 rows=2)
        -> Covering index lookup on t1 using i1 (c1=12)  (cost=0.35 rows=1)
        -> Covering index lookup on t2 using i1 (c1=12)  (cost=0.35 rows=1)

1 row in set (0.00 sec)
```
在 MySQL 8.0.29 及更高版本中，派生表条件下推优化可以与 UNION查询一起使用，但以下情况除外：
- UNION如果属于递归公用表表达式的任何具体化派生表是 UNION递归公用表表达式（请参阅递归公用表表达式），则条件下推不能与查询一起使用。
- 包含不确定表达式的条件不能下推到派生表。
派生表不能使用LIMIT 子句。
不能下推包含子查询的条件。
如果派生表是外连接的内表，则不能使用优化。
如果物化派生表是公用表表达式，则如果它被多次引用，则条件不会下推到它。
如果条件的形式为，则可以下推使用参数的条件 derived_column > ?。如果外部 WHERE条件中的派生列是在基础派生表中具有 a 的表达式，?则无法下推该条件。
对于条件在使用ALGORITHM=TEMPTABLE 而不是视图本身创建的视图的表上的查询，在解析时不识别多重相等性，因此不能不下推条件。这是因为，在优化查询时，条件下推发生在解析阶段，而多重相等性传播发生在优化期间。
在这种情况下，对于使用的视图来说，这不是问题 ALGORITHM=MERGE，因为可以传播相等性并下推条件。
SELECT 从 MySQL 8.0.28 开始，如果派生表的列表包含对用户变量的任何分配，则无法下推条件。（漏洞 #104918）