PolarDB:結合条件プッシュダウン

前提条件

開始する前に、以下を確認してください：

• リビジョンバージョン 8.0.2.2.10 以降を実行している PolarDB for MySQL 8.0 クラスター

仕組み

標準の MySQL では、外部クエリにマージできない派生テーブル (インラインビューとも呼ばれます) は、外部結合が実行される前に完全にマテリアライズされる必要があります。例えば、GROUP BY、集計関数、またはウィンドウ関数を使用するテーブルがこれに該当します。派生テーブルに大量のデータが含まれている場合、完全なマテリアライズには時間がかかります。

結合条件プッシュダウンを使用すると、PolarDB のクエリオプティマイザは、外部結合条件が派生テーブル内の列を参照していることを検出し、その条件を派生テーブル内でより早い段階で適用するように実行計画を書き換えます。これにより、派生テーブルはマテリアライズの前にインデックスを使用して行をフィルタリングできるため、外部クエリが処理するデータ量が削減されます。

オプティマイザはコスト計算を使用して、条件をプッシュダウンすることがより効率的な計画になるかどうかを判断します。プッシュダウンは、インデックスによるフィルタリングで大部分の行が除外される場合にのみ適用されます。

結合条件プッシュダウンの利用シーン

結合条件プッシュダウンは、以下の両方の条件が満たされる場合に最も効果的です：

• インデックス付き内部列を持つネステッドループ結合：外部クエリと派生テーブルがネステッドループ方式で結合され、派生テーブル内の結合列にインデックスが存在する場合。結合条件をプッシュダウンすることで、派生テーブルはそのインデックスを使用して、一致する行のみをマテリアライズできます。

• 高い選択性を持つ条件：結合条件がプッシュダウンされた後、大部分の行がフィルタリングされる場合。インデックスによって除外されるデータが多いほど、パフォーマンスの向上は大きくなります。

結合条件プッシュダウンの有効化

以下のパラメーターを使用して、結合条件プッシュダウンを制御します。パラメーターの設定方法については、「クラスターおよびノードパラメーターの設定」をご参照ください。

結合条件プッシュダウンを完全に無効にするには、loose_polar_optimizer_switch で join_predicate_pushdown=OFF を設定します。読み取り専用ノードだけでなくすべてのノードで有効にするには、loose_join_predicate_pushdown_opt_mode を ON に設定します。

例

以下の例は、3 つの派生テーブルを結合するクエリを示しています。os 派生テーブルはウィンドウ関数 (ROW_NUMBER() OVER) を使用しているため、外部クエリにマージできず、マテリアライズされる必要があります。

プッシュダウン前

結合条件プッシュダウンがない場合、os は完全にマテリアライズされます。つまり、結合条件 od.id = os.detail_id が適用される前に、update_date フィルターに一致するすべての行がスキャンされ、集計されます。このクエリの実行には約 65 秒かかります。

SELECT *
FROM (
  SELECT *
  FROM sample_table.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM sample_table.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN (
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM sample_table.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
  ) os
  ON od.id = os.detail_id;

プッシュダウン後

結合条件プッシュダウンを有効にすると、オプティマイザは実行計画を書き換え、od.id = detail_id を os 派生テーブルにプッシュします。これは LATERAL 結合として表現されます。os テーブルは detail_id インデックスを使用して、ウィンドウ関数が実行される前に行をフィルタリングし、早い段階でほとんどのデータを除外します。このクエリの実行には約 0.5 秒かかります。

SELECT *
FROM (
  SELECT *
  FROM db_order.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM db_order.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN LATERAL((
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM db_order.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
      AND od.id = detail_id
  )) os;

主な変更点は、os サブクエリ内の AND od.id = detail_id と LEFT JOIN LATERAL 構文の組み合わせです。これにより、オプティマイザは os をマテリアライズした後ではなく、その前にインデックスベースのフィルターを適用できます。

一般的な基準を満たしているにもかかわらず、クエリがプッシュダウンの恩恵を受けられない場合は、派生テーブル内の結合列にインデックスが付けられていること、および結合条件が高い選択性を持っていることを確認してください。