本文主要是介绍Mysql 查询优化器之派生条件回移Derived Condition Pushdown详解,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
Mysql 优化器之派生条件回移Derived Condition Pushdown详解
派生条件回移优化概述
Mysql 8.0.22之后的版本支持派生条件回移(Derived Condition Pushdown)优化。该优化可以减少派生表处理的行数从而提高查询执行的效率。比方说有如下查询:
SELECT * FROM
(SELECT i, j FROM t1) AS dt
WHERE i > constant
通过派生条件回移优化后类似如下形式(WHERE条件拿到派生表的里面):
SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant)
派生条件回移应用示例
派生表里无分组条件
当派生表里没有分组函数时,派生表外的WHERE条件可以直接拿到派生表内。当然WHERE条件如果包含AND、OR等组合条件也是可以回移的。
见下例:
SELECT * FROM
(SELECT f1, f2 FROM t1) AS d
WHERE f1 < 3 AND f2 > 11
回移优化法:
SELECT f1, f2 FROM
(SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11)
AS d
派生表里分组对应非分组字段
当派生表里有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条件引用派生表里的字段(是分组字段)时等效于在派生表里写成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条件里既用到派生表的分组字段又用到非分组字段,则可以提到派生表内改成对应的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
回移优化法:
SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1WHERE i > 10GROUP BY i, jHAVING sum > 100
) AS dt;
派生条件回移应用的限制
- 派生表里有UNION关键字时,该优化法不适用。
- 派生表里有LIMIT关键字时,该优化法不适用。
- WHERE条件包含子查询时不能回移。
- 如果派生表是和外部进行外连接的不能回移。
- 如果物化派生表是公共表达式(CTE)且被引用多次则WHERE条件不能回移。
- 如果WHERE 条件里是derived_column > ?( 这里的?即对应某个参数)时可以回移。但当WHERE条件里用表达式结合?时则不能回移。
查看优化切换参数的设置
show VARIABLES LIKE '%optimizer_switch%'
Variable_name Value
optimizer_switch index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
engine_condition_pushdown=on
index_condition_pushdown=on
mrr=on
mrr_cost_based=on
block_nested_loop=on
batched_key_access=off
materialization=on
semijoin=on
loosescan=on
firstmatch=on
duplicateweedout=on
subquery_materialization_cost_based=on
use_index_extensions=on
condition_fanout_filter=on
derived_merge=on
use_invisible_indexes=off
skip_scan=on
hash_join=on
subquery_to_derived=off
prefer_ordering_index=on
hypergraph_optimizer=off
derived_condition_pushdown=on
这篇关于Mysql 查询优化器之派生条件回移Derived Condition Pushdown详解的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!