MySQL 8.0 官方文档第八章优化（二十八）—— 派生的条件下推优化

本文链接：https://blog.csdn.net/qq_39785418/article/details/117216906

第八章优化（二十八）—— 派生的条件下推优化

8.2 优化SQL语句

8.2.2 优化子查询、派生表、视图引用和公共表表达式

8.2.2.5 派生的条件下推优化

Mysql 8.0.22及更高的版本支持对合适的子查询进行派生条件下推。对于诸如：

SELECT * FROM (
		SELECT i, j FROM t1) 
		AS dt 
WHERE i > constant

之类的查询，在很多情况下，都可以把外部的WHERE条件下推到派生表中，而在本例中，将导致以下结果：

SELECT * FROM (
		SELECT i, j FROM t1 
			WHERE i > constant) 
		AS dt

当派生表不能被合并到外部查询中时（例如，派生表使用了聚合函数），那么，把外部的WHERE条件下推到派生表中应该能减少需要进行处理的行数，从而提升查询执行速度。

注意
在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
```

当派生表带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中部分列的谓词，则这种谓词被作为派生表的WHERE条件被下推；如果有引用了不在GROUP BY列中的谓词，则这种谓词被当成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，此优化不能使用。
派生表不能使用LIMIT子句。
中含有子查询的条件不能被下推。
如果派生表是外连接中的内部表，则不能使用此优化。
如果物化的派生表是一个公共表表达式，则如果此派生表被引用多次，那么条件不能下推至此派生表中。
如果条件形式为 derived_column > ?（派生表的列 > ?），则带参数的条件可以被下推。如果外部WHERE条件中的派生表列是基础派生表中有?的表达式，则此条件不能被下推。