PolarDB:サブクエリのネスト解除

前提条件

クラスターは、リビジョンバージョンが 8.0.2.2.1 以降の PolarDB for MySQL 8.0 クラスターである必要があります。バージョン番号をクエリして、クラスターのバージョンを確認できます。

背景

相関サブクエリは、分析ワークロードで広く使用されています。たとえば、TPC-H ベンチマークの 22 のクエリのうち、3 分の 1 以上に相関サブクエリが含まれています。ネスト解除を行わない場合、外部クエリが処理する各行に対してサブクエリが実行されます。この繰り返し実行は、特に外部クエリが多数の行を返す場合や、サブクエリに適切なインデックスがない場合に、クエリの実行時間が長くなる原因となる可能性があります。サブクエリのネスト解除は、相関サブクエリを同等の JOIN ステートメントに変換します。この変換により、サブクエリの繰り返し実行が回避され、オプティマイザがさらなる結合最適化を適用できるようになります。

ウィンドウ関数を使用したネスト解除

概要

使用方法

• loose_polar_optimizer_switch システムパラメーターを使用して、ウィンドウ関数によるサブクエリのネスト解除を有効にします。手順については、「クラスターパラメーターとノードパラメーターの設定」をご参照ください。

  パラメーター	スコープ	説明
  loose_polar_optimizer_switch	グローバル、セッション	クエリ最適化機能を制御します。オプションは次のとおりです： unnest_use_window_function：ウィンドウ関数を使用するサブクエリのネスト解除を制御します。 ON (デフォルト)：この機能を有効にします。 OFF：この機能を無効にします。 unnest_use_group_by：GROUP BY 句を使用するサブクエリのネスト解除を制御します。このクエリ変換は、コストベースのクエリ最適化の対象となります。 ON (デフォルト)：この機能を有効にします。 OFF：この機能を無効にします。 derived_merge_cost_based：派生マージ機能がコストベースのクエリ最適化の対象となるかどうかを指定します。 OFF (デフォルト)：派生マージ機能はコストベースのクエリ最適化の対象になりません。 ON：派生マージ機能はコストベースのクエリ最適化の対象になります。

  例：次の例では、TPC-H Q2 クエリを使用します。このクエリは、特定の地域内で特定の部品タイプとサイズに対して最も低い供給コストを提供するサプライヤーを見つけます。MySQL Community Edition では、実行エンジンはまず外部クエリを実行して、指定された部品のサプライヤーを見つけます。次に、返された各行に対してサブクエリを実行し、指定された地域のすべてのサプライヤーからその部品の最小供給コストを計算します。最後に、サプライヤーの供給コストとサブクエリからの最小供給コストを比較します。

  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;

  ウィンドウ関数を使用すると、クエリは指定されたパーティションに対して集約関数を計算し、その結果を元の行に追加できます。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;

• ヒントを使用して、特定のクエリのサブクエリのネスト解除を制御します。

  このクエリ変換を制御するには、UNNEST ヒントを使用します。形式は次のとおりです：

  UNNEST([@query_block_name] [strategy [, strategy] ...])   # loose_polar_optimizer_switch の設定を上書きし、ウィンドウ関数または GROUP BY 句を使用してサブクエリのネストを解除します。
  NO_UNNEST([@query_block_name] [strategy [, strategy] ...])  # loose_polar_optimizer_switch の設定を上書きし、サブクエリのネスト解除を防止します。

  strategy オプションは WINDOW_FUNCTION と GROUP_BY をサポートします。

  例：

  # ウィンドウ関数を使用して強制的にネスト解除します。
  SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /*+UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)
  
  # ウィンドウ関数を使用したネスト解除を防止します。
  SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /*+NO_UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

パフォーマンス

GROUP BY 句を使用したネスト解除

概要

使用方法

• loose_polar_optimizer_switch システムパラメーターを使用して、GROUP BY 句によるサブクエリのネスト解除を有効にします。手順については、「クラスターパラメーターとノードパラメーターの設定」をご参照ください。

  パラメーター	スコープ	説明
  loose_polar_optimizer_switch	グローバル、セッション	クエリ最適化機能を制御します。オプションは次のとおりです： unnest_use_window_function：ウィンドウ関数を使用するサブクエリのネスト解除を制御します。 ON (デフォルト)：この機能を有効にします。 OFF：この機能を無効にします。 unnest_use_group_by：GROUP BY 句を使用するサブクエリのネスト解除を制御します。このクエリ変換は、コストベースのクエリ最適化の対象となります。 ON (デフォルト)：この機能を有効にします。 OFF：この機能を無効にします。 derived_merge_cost_based：派生マージ機能がコストベースのクエリ最適化の対象となるかどうかを指定します。 OFF (デフォルト)：派生マージ機能はコストベースのクエリ最適化の対象になりません。 ON：派生マージ機能はコストベースのクエリ最適化の対象になります。

  例：次のクエリは、数量が対応する品目の購入総数量の 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 テーブルの行ごとに 1 回実行されます。pl_objectkey 列にインデックスがあっても、sl_objectkey 列には重複値が多く含まれていることが多いため、このプロセスでは purchase_lineitem テーブルに対するスキャンと計算が繰り返されることになります。このような非効率な相関サブクエリを最適化するため、PolarDB は GROUP BY 句を使用してネストを解除します。元のクエリは次のように変換されます。

  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 テーブルに結合します。この変換により、purchase_lineitem テーブルのスキャンは 1 回だけで済み、反復的なスキャンと計算を回避できます。オプティマイザは、外部結合を排除し、結合順序を調整して実行効率を向上させることで、変換されたステートメントをさらに最適化できます。

• ヒントを使用して、特定のクエリのサブクエリのネスト解除を制御します。

  このクエリ変換を制御するには、UNNEST ヒントを使用します。形式は次のとおりです：

  UNNEST([@query_block_name] [strategy [, strategy] ...])   # loose_polar_optimizer_switch の設定を上書きし、ウィンドウ関数または GROUP BY 句を使用してサブクエリのネストを解除します。
  NO_UNNEST([@query_block_name] [strategy [, strategy] ...])  # loose_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 ...)