AnalyticDB:結合順序の手動調整

機能の概要

AnalyticDB for MySQL は複雑な結合クエリをサポートしており、デフォルトで結合順序を自動的に調整します。ただし、クエリステートメントやテーブルのフィルタ条件は変化する可能性があります。データの特性が複雑な場合、自動調整では最適な結合順序が選択されない可能性があります。最適でない結合順序は、大きな中間結果セットや高いメモリ使用量につながり、クエリパフォーマンスを低下させます。

調整方法

• 調整前

  • クエリステートメント

    元の Query10 ステートメントは次のとおりです。

    説明

    • このトピックでは、TPC-H の Query10 を例として、結合順序を手動で調整する方法と効果を説明します。TPC-H の詳細については、「TPC-H」をご参照ください。

    • AnalyticDB for MySQL の結合順序の自動調整機能はデフォルトで有効になっています。次のクエリステートメントでは、/*+ reorder_joins=false*/ ヒントを使用して、最適でない結合順序のシナリオをシミュレートしています。

    SELECT   c_custkey,
             c_name,
             Sum(l_extendedprice * (1 - l_discount)) AS revenue,
             c_acctbal,
             n_name,
             c_address,
             c_phone,
             c_comment
    FROM     customer c,
             orders o,
             lineitem l,
             nation n
    WHERE    c_custkey = o_custkey
    AND      l_orderkey = o_orderkey
    AND      o_orderdate >= date '1993-10-01'
    AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
    AND      l_returnflag = 'R'
    AND      c_nationkey = n_nationkey
    GROUP BY c_custkey,
             c_name,
             c_acctbal,
             c_phone,
             n_name,
             c_address,
             c_comment
    ORDER BY revenue DESC
    LIMIT    20;

  • 結合順序

    前述の SQL ステートメントに基づくと、結合順序は次のとおりです。

    customer JOIN orders JOIN lineitem JOIN nation;

  • クエリ結果

    実行計画における各結合の中間結果は次のとおりです。

    説明

    実行計画の表示方法の詳細については、「実行計画を使用したクエリの分析」をご参照ください。

    1. customer テーブルと orders テーブルが結合されます。この結合により、57,069 行が中間結果セット tmp1 に出力されます。

    2. 中間結果セット tmp1 は、o_orderkey = l_orderkey という結合条件で INNER (Hash) Join を使用して lineitem テーブルと結合されます。この操作には 4.88 ms (21.67%) かかり、114,705 行が新しい中間結果セット tmp2 に出力されます。

    3. 中間結果セット tmp2 は nation テーブルと結合されます。この結合により、114,705 行が最終結果として出力されます。結合ノードのタイプは INNER (Hash) で、結合条件は left.c_nationkey = right.n_nationkey です。この操作には 5.71 ms (12.07%) かかります。

    3つの結合で出力された行の合計は、57,069 + 114,705 + 114,705 = 286,479 です。

• 調整後

  • クエリステートメント

    SQL ステートメントに /*+ reorder_joins=false*/ ヒントを追加することで、AnalyticDB for MySQL の結合順序の自動調整機能を無効にし、結合順序を手動で調整できます。調整後の SQL ステートメントは次のとおりです。

    /*+ reorder_joins=false*/
    SELECT   c_custkey,
             c_name,
             Sum(l_extendedprice * (1 - l_discount)) AS revenue,
             c_acctbal,
             n_name,
             c_address,
             c_phone,
             c_comment
    FROM     customer c,
             orders o,
             nation n,
             lineitem l
    WHERE    c_custkey = o_custkey
    AND      c_nationkey = n_nationkey
    AND      l_orderkey = o_orderkey
    AND      o_orderdate >= date '1993-10-01'
    AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
    AND      l_returnflag = 'R'
    GROUP BY c_custkey,
             c_name,
             c_acctbal,
             c_phone,
             n_name,
             c_address,
             c_comment
    ORDER BY revenue DESC
    LIMIT    20;

  • 結合順序

    前述の SQL ステートメントに基づくと、結合順序は次のとおりです。

    customer JOIN orders JOIN nation JOIN lineitem

  • クエリ結果

    実行計画における各結合の中間結果は次のとおりです。

    説明

    実行計画の表示方法の詳細については、「実行計画を使用したクエリの分析」をご参照ください。

    1. customer テーブルと orders テーブルが結合されます。この結合により、57,069 行が中間結果セット tmp1 に出力されます。結合ノードのタイプは INNER (Hash) で、結合条件は c_custkey = o_custkey です。この操作には 4.75 ms (36.7%) かかります。

    2. 中間結果セット tmp1 は nation テーブルと結合されます。この結合により、57,069 行が中間結果セット tmp2 に出力されます。結合ノードのタイプは INNER (Hash) で、結合条件は left.c_nationkey = right.n_nationkey です。この操作には 5.63 ms (25.61%) かかります。

    3. 中間結果セット tmp2 は、left.o_orderkey = right.l_orderkey という結合条件で INNER (Hash) Join を使用して lineitem テーブルと結合されます。この結合により、114,705 行が最終結果として出力され、5.29 ms (合計時間の 9.81%) かかります。

    3つの結合で出力された行の合計は、57,069 + 57,069 + 114,705 = 228,843 です。

    調整前の 286,479 行と比較すると、出力行数が 20% 削減されたことになります。この比較から、異なる結合順序が中間結果セットのサイズに影響を与えることがわかります。AnalyticDB for MySQL の結合順序が最適でないと判断した場合、手動で調整することでクエリパフォーマンスを向上させることができます。