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

仕組み

以下の図は、サブクエリを含むクエリの構造を示しています。

T1、T2、T3 はそれぞれ 1 つ以上のテーブルおよびビューのコレクションを表します。図中の点線で示されるように、T2（サブクエリ内）は T3 とネストされています。一方、T1 は外部クエリ内にあり、T2 とはネストされていません。

以下のすべての条件が満たされる場合に、ウィンドウ関数戦略が適用されます。

• スカラーサブクエリに LIMIT 句または DISTINCT 句は含まれず、出力は集計関数です。

• サブクエリ内のテーブルは、外部クエリ内のテーブルの部分集合です。

• サブクエリは外部クエリと等結合で接続されています。また、外部クエリには、サブクエリ内の共通テーブルに対する同一のセマンティクスを持つ結合条件およびフィルター条件が含まれています。

• サブクエリ内の結合列は、プライマリキーまたは一意キーの列です。

• サブクエリおよび外部クエリのいずれにも、カスタム関数または RANDOM 関数は含まれません。

ネスト解除後、ウィンドウ関数はグループ単位で集計を実行し、その結果を各行にアタッチすることで、サブクエリの再実行を不要にします。

例

以下の例では、TPC-H Q2（最小コストサプライヤークエリ）を使用します。これは、特定のタイプおよびサイズのコンポーネントを販売するリージョン内のすべてのサプライヤーのうち、最も低い価格を提供するサプライヤーを検索するクエリです。

MySQL Community Edition では、まず外部クエリが該当するすべてのサプライヤーを取得し、その後、各行に対してサブクエリを実行して最小の供給コストを算出します。大規模なデータセットでは、この処理により、サプライヤーの各行ごとにサブクエリが 1 回実行されることになります。

元のクエリ：

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;

PolarDB では、このクエリを MIN() OVER(PARTITION BY ps_partkey) を用いて書き換え、部品ごとの最小コストを 1 回のスキャンで算出し、その後、供給コストがグループ単位の最小値と一致する行をフィルターします。

書き換え後のクエリ：

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;

パフォーマンス向上効果

TPC-H スケールファクター 10 における測定結果：

• Q2：1.54 倍の高速化

• Q17：4.91 倍の高速化

仕組み

以下の図は、サブクエリを含むクエリの構造を示しています。

以下のすべての条件が満たされる場合に、GROUP BY 戦略が適用されます。

• スカラーサブクエリに GROUP BY 句または LIMIT 句は含まれず、出力は集計関数です。

• スカラーサブクエリは、JOIN、WHERE、または SELECT 条件内に出現します。

• スカラーサブクエリは、AND で結合された条件を用いた等結合により外部クエリと接続されています。

• スカラーサブクエリには、カスタム関数または RANDOM 関数は含まれません。

ネスト解除後、サブクエリは結合キーでグループ化された事前集約済みの結果を持つ派生テーブルとなります。その後、外部クエリはこの派生テーブルを 1 回だけ結合することで、繰り返しのサブクエリ実行を置き換えます。

例

以下の例では、同一製品の総購入金額の 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 を読み取ったうえで、その値に対してサブクエリを再実行します。このサブクエリは、sale_lineitem 内の行数分だけ実行されます。sl_objectkey には通常多数の重複値が含まれるため、purchase_lineitem に対する同一の集約処理が何度も繰り返されてしまいます — インデックスが pl_objectkey に存在する場合でも同様です。

PolarDB では、このクエリを、事前集約済みの派生テーブルに対する LEFT JOIN として書き換えます。purchase_lineitem テーブルは 1 回だけスキャンされます。

書き換え後のクエリ：

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;

オプティマイザーは、コスト見積もりに基づく結合の並べ替えをさらに適用することで、実行性能をさらに向上させることができます。