子查询解关联

前提条件

集群版本需为PolarDB MySQL版8.0版本且Revision version为8.0.2.2.1及以上。您可以通过查询版本号确认集群版本。

背景信息

关联子查询在分析场景中被广泛使用，例如在决策支持基准TPC-H的22个查询中，有超过1/3的查询包含关联子查询。在没有解关联的时候，外层查询的每一行数据，子查询都会被执行一次。对于外层查询产生数据量较多、子查询没有关联索引等情况，子查询多次执行会导致查询耗时严重。子查询解关联将关联子查询变换为等价的Join语句，可以避免子查询多次执行，同时优化器可以对Join做进一步优化。

利用Window Function解关联

概述

使用方法

• 通过系统参数polar_optimizer_switch开启利用Window function解关联功能。具体操作请参见设置集群参数和节点参数。

  参数名称	级别	描述
  polar_optimizer_switch	Global、Session	PolarDB查询优化的控制开关。取值范围： unnest_use_window_function：利用Window function解关联的功能开关。 ON（默认值）：开启利用Window function解关联功能。 OFF：关闭利用Window function解关联功能。 unnest_use_group_by：利用Group by解关联的功能开关，该查询变换受基于代价的查询变换控制。 ON（默认值）：开启利用Group by解关联功能。 OFF：关闭利用Group by解关联功能。 derived_merge_cost_based：derived merge功能是否受基于代价的查询变换的控制。 OFF（默认值）：derived merge功能不受基于代价的查询变换的控制。 ON：derived merge功能受基于代价的查询变换的控制。

  示例：以TPC-H中的Q2为例，该查询希望查找特定区域，对于指定类型和大小的零件，能够提供最小批发价的供应商。该查询在社区MySQL中执行需要先执行外层查询获取指定类型和大小的零件供应商的信息，然后对获取的每一条数据通过子查询计算该零件在指定区域中所有供应商最小的批发价，然后判断获取的供应商批发价和子查询获取的最小批发价是否一致。

  SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
   s_address, s_phone, s_comment
  FROM part, supplier, partsupp, nation, region
  WHERE p_partkey = ps_partkey
     AND s_suppkey = ps_suppkey
     AND p_size = 30
     AND p_type LIKE '%STEEL'
     AND s_nationkey = n_nationkey
     AND n_regionkey = r_regionkey
     AND r_name = 'ASIA'
     AND ps_supplycost = (
         SELECT MIN(ps_supplycost)
         FROM partsupp, supplier, nation, region
         WHERE p_partkey = ps_partkey
             AND s_suppkey = ps_suppkey
             AND s_nationkey = n_nationkey
             AND n_regionkey = r_regionkey
             AND r_name = 'ASIA'
     )
  ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
  LIMIT 100;

  利用Window function按照指定的分组方式分组计算聚合函数，在原有数据行上增加分组计算的聚合结果。对于TPC-H中的Q2，可以获取特定区域，指定类型和大小零件的供应商同时按照零件信息进行分组计算获得最小批发价。然后通过比较获取的数据行中批发价和分组最小批发价比较来选择需要的数据。Q2经过查询变换后，获得以下查询的等价执行。

  SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
    s_address, s_phone, s_comment
  FROM (
      SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min,
        ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, s_address,
        s_phone, s_comment
      FROM part, partsupp, supplier, nation, region
      WHERE p_partkey = ps_partkey
        AND s_suppkey = ps_suppkey
        AND s_nationkey = n_nationkey
        AND n_regionkey = r_regionkey
        AND p_size = 30
        AND p_type LIKE '%STEEL'
        AND r_name = 'ASIA') as derived
  WHERE ps_supplycost = derived.win_min
  ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
  LIMIT 100;

• 通过Hint来控制满足条件的子查询利用Window function解关联。

  可以通过UNNEST hint来控制该查询变换。具体变换形式如下：

  UNNEST([@query_block_name] [strategy [, strategy] ...])   #表示使用Window function或Group By解关联，并且不受polar_optimizer_switch的影响。
  NO_UNNEST([@query_block_name] [strategy [, strategy] ...])  #表示禁止使用Window function或Group By解关联，并且不受polar_optimizer_switch的影响。

  其中strategy支持WINDOW_FUNCTION和GROUP_BY选项。

  示例：

  #指定满足条件的子查询利用WINDOW_FUNCTION解关联
  SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /*+UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)
  
  #指定子查询不要利用WINDOW_FUNCTION解关联
  SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /*+NO_UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

性能效果

利用Group By Aggregation解关联

概述

使用方法

• 通过系统参数polar_optimizer_switch开启利用Group By Aggregation解关联功能。具体操作请参见设置集群参数和节点参数。

  参数名称	级别	描述
  polar_optimizer_switch	Global、Session	PolarDB查询优化的控制开关。取值范围： unnest_use_window_function：利用Window function解关联的功能开关。 ON（默认值）：开启利用Window function解关联功能。 OFF：关闭利用Window function解关联功能。 unnest_use_group_by：利用Group by解关联的功能开关，该查询变换受基于代价的查询变换控制。 ON（默认值）：开启利用Group by解关联功能。 OFF：关闭利用Group by解关联功能。 derived_merge_cost_based：derived merge功能是否受基于代价的查询变换的控制。 OFF（默认值）：derived merge功能不受基于代价的查询变换的控制。 ON：derived merge功能受基于代价的查询变换的控制。

  示例：以下面查询语句为例，希望获取销售订单中大于对应商品采购总量10%的订单明细。

  SELECT *
  FROM sale_lineitem sl
  WHERE sl.sl_quantity >
      (SELECT 0.1 * SUM(pl.pl_quantity)
       FROM purchase_lineitem pl
       WHERE pl.pl_objectkey = sl.sl_objectkey);

  如果不做任何查询变换的话，执行引擎会迭代sale_lineitem表的每一行，获取sl_objectkey填充到子查询中，执行子查询获取采购总量的10%，然后和该行数据上的数量做比较。此时，子查询的执行次数取决于sale_lineitem表的行数，即使在pl_objectkey列上有索引，由于sl_objectkey列一般存在大量重复的值，也会造成purchase_lineitem表的大量重复扫描和重复计算。对于这种执行效率低的关联子查询，在PolarDB中会利用Group By Aggregation解关联。上述查询语句会变换为：

  SELECT *
  FROM sale_lineitem sl
  LEFT JOIN
    (SELECT (0.1 * sum(pl.pl_quantity)) AS Name_exp_1,
            pl.pl_objectkey AS Name_exp_2
     FROM purchase_lineitem pl
     GROUP BY pl.pl_objectkey) derived ON derived.Name_exp_2 = sl.sl_objectkey
  WHERE sl.sl_quantity > derived.name_exp_1;

  变换后会先对各个采购商品做分组计算，然后再与sale_lineitem做Join。这样purchase_lineitem表只需要扫描一次，避免了重复扫描和计算。这里变换后的语句可以进一步消除外连接，Join Order可以调整，提升执行效率。

• 通过Hint来控制满足条件的子查询利用Group By Aggregation解关联。

  可以通过UNNEST hint来控制该查询变换。具体变换形式如下：

  UNNEST([@query_block_name] [strategy [, strategy] ...])   #表示使用Window function或Group By解关联，并且不受polar_optimizer_switch的影响。
  NO_UNNEST([@query_block_name] [strategy [, strategy] ...])  #表示禁止使用Window function或Group By解关联，并且不受polar_optimizer_switch的影响。

  其中strategy支持WINDOW_FUNCTION和GROUP_BY选项。

  示例：

  #指定满足条件的子查询利用GROUP_BY解关联
  SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(GROUP_BY)*/ agg FROM ...)
  SELECT /*+UNNEST(@`select#2` GROUP_BY)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)
  
  #指定子查询不要利用GROUP_BY解关联
  SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(GROUP_BY)*/ agg FROM ...)
  SELECT /*+NO_UNNEST(@`select#2` GROUP_BY)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)