PolarDB：子查詢解關聯

前提條件

叢集版本需為PolarDB MySQL版8.0版本且Revision version為8.0.2.2.1及以上。您可以通過查詢版本號碼確認叢集版本。

背景資訊

關聯子查詢在分析情境中被廣泛使用，例如在決策支援基準TPC-H的22個查詢中，有超過1/3的查詢包含關聯子查詢。在沒有解關聯的時候，外層查詢的每一行資料，子查詢都會被執行一次。對於外層查詢產生資料量較多、子查詢沒有關聯索引等情況，子查詢多次執行會導致查詢耗時嚴重。子查詢解關聯將關聯子查詢變換為等價的Join語句，可以避免子查詢多次執行，同時最佳化器可以對Join做進一步最佳化。

利用Window Function解關聯

概述

使用方法

• 通過系統參數loose_polar_optimizer_switch開啟利用Window function解關聯功能。具體操作請參見設定叢集參數和節點參數。

  參數名稱	層級	描述
  loose_polar_optimizer_switch	Global、Session	查詢最佳化控制開關。取值範圍如下： 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解關聯

概述

使用方法

• 通過系統參數loose_polar_optimizer_switch開啟利用Group By Aggregation解關聯功能。具體操作請參見設定叢集參數和節點參數。

  參數名稱	層級	描述
  loose_polar_optimizer_switch	Global、Session	查詢最佳化控制開關。取值範圍如下： 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 ...)