如何使用条件下推对SQL查询进行变换_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

PolarDB MySQL版支持条件下推（WHERE to Derived Tables）功能。对于满足前提条件的复杂查询，您可以通过该功能对SQL查询进行变换。即当派生表（Derived Tables）不能合并到外部查询时，将外部WHERE条件下推到派生表（Derived Tables）中减少SQL需要处理的行数，从而提升SQL查询的性能。

前提条件

集群版本适用的数据库引擎版本如下，如何查看集群版本，请参见查询版本号。

MySQL 8.0.1，且小版本需为8.0.1.1.42及以上版本。
MySQL 8.0.2，且小版本需为8.0.2.2.10及以上版本。

使用限制

如果条件表达式中的所有列（或对应的等价列）都来自物化的派生表（Derived Tables），则该条件可以下推至物化表。
如果物化派生表（Derived Tables）有Limit限制，则禁止下推。
外层WHERE条件表达式的列或者映射到物化表对应的列，存在以下情况则不支持下推：
- 该列引用了子查询或者是非确定性的（相同的输入条件下，可能会产生不同的结果）；
- 该列是存储过程或者存储函数。

背景信息

基于用户复杂的查询场景，数据库需要更强大的下推能力来加速用户查询。PolarDB MySQL版基于社区版MySQL原有的条件下推功能进行了功能增强，实现了更加完善和强大的下推能力，主要包括以下方面：

添加了等值条件的传递；
在派生表（Derived Tables）是union的场景下，支持将符合的条件下推到union对应的部分；
与条件下推（HAVING to WHERE）配合使用，可将下推后的条件基于等价关系进一步级联下推。

使用方法

您可以通过设置loose_derived_cond_pushdown_mode参数值来开启从WHERE条件到派生表（Derived Tables）的条件下推功能。具体操作请参见设置集群参数和节点参数。

说明

您也可以在Session级别的数据库连接中通过以下两条命令开启从WHERE条件到派生表（Derived Tables）的条件下推功能。

SET optimizer_switch="derived_condition_pushdown=on";
SET derived_cond_pushdown_mode=on;

参数说明

参数名称

级别

描述

loose_derived_cond_pushdown_mode

Global

从WHERE条件到派生表（Derived Tables）的条件下推功能控制开关。取值范围：

OFF（默认值）：关闭条件下推（WHERE to Derived Tables）功能。
ON：开启条件下推（WHERE to Derived Tables）功能。
REPLICA_ON：仅在只读节点开启条件下推（WHERE to Derived Tables）功能。

示例

将条件从WHERE子句下推至派生表（Derived Tables）中。

示例1：

       SELECT *
       FROM t1, (
                  SELECT x
                  FROM t2
                  GROUP BY x
               ) d_tab, t2
       WHERE t1.a = d_tab.x
               AND t1.a > 6;

条件下推变换后结果为：

      SELECT *
        FROM t1, (
                   SELECT x
                   FROM t2
                   WHERE x > 6
                   GROUP BY x
                ) d_tab
        WHERE t1.a = d_tab.x
                AND t1.a > 6;

示例2：

    SELECT f1
    FROM (
	    SELECT (
		    	SELECT f1
		    	FROM t1
		    	LIMIT 1
		    ) AS f1
	    FROM t1
	    UNION
	    SELECT f2
	    FROM t2
    ) dt
    WHERE f1 = 1;

条件下推变换后结果为：

    SELECT f1
    FROM (
	    SELECT (
		    	SELECT f1
		    	FROM t1
		    	LIMIT 1
		    ) AS f1
	    FROM t1
	    UNION
	    SELECT f2
	    FROM t2
	    WHERE f2 = 1
    ) dt
    WHERE f1 = 1;

示例3：

   SELECT *
   FROM (
	   SELECT f1, f2
	   FROM t1
   ) dt
   GROUP BY f1
   HAVING f1 < 3
   AND f2 > 11
   AND MAX(f3) > 12;

条件下推变换后结果为：

   SELECT *
   FROM (
	   SELECT f1, f2
	   FROM t1
	   WHERE f1 < 3
   ) dt
   WHERE f1 < 3
   GROUP BY f1
   HAVING f2 > 11
   AND MAX(f3) > 12;