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

前提条件

クラスターのバージョンはPolarDB for MySQL 8.0で、クラスターのリビジョンバージョンは8.0.2.2.1以降です。 クラスターのバージョンを表示する方法については、「エンジンバージョンの照会」をご参照ください。

背景

サブクエリは、分析クエリで広く使用されています。 TPC-Hの22のクエリの3分の1以上にサブクエリが含まれています。 外部クエリからネストされていないサブクエリは、データ行に対して外部クエリが実行されるたびに実行されます。 外部クエリが大量のデータを生成し、サブクエリがインデックスに関連付けられていない場合、サブクエリの実行には非常に時間がかかります。 サブクエリのネスト解除は、サブクエリを同等のJOINステートメントに変換します。 これにより、サブクエリを複数回実行することを回避し、オプティマイザがJOINステートメントをさらに最適化できるようになります。

ウィンドウ関数を使用してクエリをネスト解除する

概要

サブクエリのネスト解除機能の使用

• polar_optimizer_switchパラメーターを使用して、サブクエリのネスト解除を有効にします。 詳細については、「クラスターとノードパラメーターの設定」をご参照ください。

  パラメーター	レベル	説明
  polar_optimizer_switch	グローバルとセッション	PolarDBのクエリ最適化機能を有効にするかどうかを指定します。 unnest_use_window_function: ウィンドウ関数を使用してサブクエリのネストを解除するかどうかを指定します。 デフォルト値: ON。 有効な値： ON オフ unnest_use_group_by: GROUP by句を使用してサブクエリのネストを解除するかどうかを指定します。 このサブクエリのネスト解除方法は、コストベースのクエリ最適化に基づいて実装されます。 デフォルト値: ON。 有効な値： ON オフ derived_merge_cost_based: コストベースのクエリ最適化に基づいて派生マージ機能を実装するかどうかを指定します。 デフォルト値: OFF。 有効な値： ON オフ

  次の例では、TPC-Hの最小コストサプライヤークエリ (Q2) を使用して機能を示します。 クエリは、特定のタイプおよびサイズのコンポーネントを販売する地域内のすべてのサプライヤの中から最低価格を請求するサプライヤを取得するために使用されます。 MySQL Community Editionでは、最初に外部クエリを実行して、特定のタイプとサイズのコンポーネントを販売するサプライヤの情報を取得します。 次に、データの各行に対してサブクエリを実行して、コンポーネントを最低価格で販売するサプライヤを取得します。 最後に、外部クエリの結果に含まれる価格が、サブクエリの結果に含まれる価格と比較され、2つの値が一致するかどうかが判定される。

  SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
   s_address、s_phone、s_comment
  部品、サプライヤー、partsupp、国家、地域から
  WHERE p_partkey = ps_partkey
     AND s_suppkey = ps_suppkey
     そしてp_size = 30
     そしてp_type '% STEEL' のように
     AND s_nationkey = n_nationkey
     AND n_regionkey = r_regionkey
     AND r_name=「アジア」
     AND ps_supplycost = (
         SELECT MIN(ps_supplycost)
         partsupp、サプライヤー、国、地域から
         WHERE p_partkey = ps_partkey
             AND s_suppkey = ps_suppkey
             AND s_nationkey = n_nationkey
             AND n_regionkey = r_regionkey
             AND r_name=「アジア」
     )
  ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
  LIMIT 100; 

  ウィンドウ関数を使用して集計関数をグループで実行し、結果を行に追加します。 TPC-Hの第2四半期には、特定の地域にあり、特定のサイズとタイプのコンポーネントを販売するサプライヤの情報が取得されます。 サプライヤは、コンポーネント情報に基づいてグループ化される。 集計関数は、グループ化された情報を使用して最低価格を取得します。 次に、行の価格とそのグループの最低価格を比較して、使用する値を決定します。 上記のクエリは、次のクエリに変換されます。

  SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
    s_address、s_phone、s_comment
  から (
      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
      部品、partsupp、サプライヤー、国、地域から
      WHERE p_partkey = ps_partkey
        AND s_suppkey = ps_suppkey
        AND s_nationkey = n_nationkey
        AND n_regionkey = r_regionkey
        そしてp_size = 30
        そしてp_type '% STEEL' のように
        派生としてのAND r_name = 'ASIA')
  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] ...]) # Window関数またはGROUP BY句は、polar_optimizer_switch値に関係なく、サブクエリのネストを解除するために使用されます。 
  NO_UNNEST([@ query_block_name] [strategy [, strategy] ...]) # Window関数またはGROUP BY句は、polar_optimizer_switch値に関係なく、サブクエリのネストを解除するために使用されません。 

  strategyは、WINDOW_FUNCTIONまたはGROUP_BYに設定できます。

  例：

  # ウィンドウ関数を介したサブクエリのネスト解除。
  SELECT ... FROM ... どこで... = (SELECT /* + UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /* + UNNEST(@ 'select#2 'WINDOW_FUNCTION)*/ ... FROM ... どこで... = (SELECT agg FROM ...)
  
  # ウィンドウ関数を介してサブクエリのネストを解除しないでください。
  SELECT ... FROM ... どこで... = (SELECT /* + NO_UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /* + NO_UNNEST(@ 'select#2 'WINDOW_FUNCTION)*/ ... FROM ... どこで... = (SELECT agg FROM ...) 

パフォーマンス向上

GROUP BY句を使用したサブクエリのネスト解除

概要

サブクエリのネスト解除機能の使用

• polar_optimizer_switchパラメーターを使用して、サブクエリのネスト解除を有効にします。 詳細については、「クラスターとノードパラメーターの設定」をご参照ください。

  パラメーター	レベル	説明
  polar_optimizer_switch	グローバルとセッション	PolarDBのクエリ最適化機能を有効にするかどうかを指定します。 unnest_use_window_function: ウィンドウ関数を使用してサブクエリのネストを解除するかどうかを指定します。 デフォルト値: ON。 有効な値： ON オフ unnest_use_group_by: GROUP by句を使用してサブクエリのネストを解除するかどうかを指定します。 このサブクエリのネスト解除方法は、コストベースのクエリ最適化に基づいて実装されます。 デフォルト値: ON。 有効な値： ON オフ derived_merge_cost_based: コストベースのクエリ最適化に基づいて派生マージ機能を実装するかどうかを指定します。 デフォルト値: OFF。 有効な値： ON オフ

  この例では、クエリを使用して、数量が合計金額の10% を超える注文の詳細を取得します。

  SELECT *
  sale_lineitem slから
  WHERE sl.sl_quantity>
      (SELECT 0.1 * SUM(pl.pl_quantity)
       購入ラインアイテムplから
       WHERE pl.pl_objectkey = sl.sl_objectkey); 

  クエリ変換が実行されない場合、sale_lineitemテーブルの各行が反復され、サブクエリで使用されるsl_objectkey値が取得されます。 値が取得されるたびに、サブクエリが実行されて、合計金額の10% 結果が計算され、合計金額がその行の売上金額と比較されます。 サブクエリが実行される回数は、sale_lineitemテーブルの行数と同じです。 pl_objectkey列にインデックスがある場合でも、通常はsl_objectkey列に多数の重複値が存在するため、purchase_lineitemテーブルで大量の複製スキャンと計算が必要になります。 このような非効率的なサブクエリは、GROUP by句を使用してPolarDBでネストされません。 上記のクエリは、次のクエリに変換されます。

  SELECT *
  sale_lineitem slから
  左の参加
    (SELECT (0.1 * sum(pl.pl_quantity)) Name_exp_1、
            pl.pl_objectkey AS Name_exp_2
     購入ラインアイテム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回だけスキャンする必要があります。 クエリは、実行効率を向上させるために結合順序を変更することによってさらにネスト解除できます。

• ヒントを使用してGROUP BY句によるサブクエリのネストを解除する

  UNNESTヒントは、ネスト解除を制御するために使用できます。 形式：

  UNNEST([@ query_block_name] [strategy [, strategy] ...]) # Window関数またはGROUP BY句は、polar_optimizer_switch値に関係なく、サブクエリのネストを解除するために使用されます。 
  NO_UNNEST([@ query_block_name] [strategy [, strategy] ...]) # Window関数またはGROUP BY句は、polar_optimizer_switch値に関係なく、サブクエリのネストを解除するために使用されません。 

  strategyは、WINDOW_FUNCTIONまたはGROUP_BYに設定できます。

  例：

  # GROUP BY句によるUnnestサブクエリ
  SELECT... FROM ... どこで... = (SELECT /* + UNNEST(GROUP_BY)*/ agg FROM ...)
  SELECT /* + UNNEST(@ 'select#2 'GROUP_BY)*/ ... FROM ... どこで... = (SELECT agg FROM ...)
  
  # GROUP BY句を介してサブクエリのネストを解除しない
  SELECT... FROM ... どこで... = (SELECT /* + NO_UNNEST(GROUP_BY)*/ agg FROM ...)
  SELECT /* + NO_UNNEST(@ 'select#2 'GROUP_BY)*/ ... FROM ... どこで... = (SELECT agg FROM ...)