PolarDB:オプティマイザの結合リオーダーの設定

SQL は宣言型言語であり、どのような結果を返すかを指定しますが、その計算方法は指定しません。特定の SQL ステートメントに対して、正しい結果を生成できる有効なクエリプランは複数存在する可能性があります。例：

SELECT * FROM t0, t1, t2, t3 WHERE t0.a = t1.a AND t1.a = t2.a AND t2.a = t3.a AND t3.b = t1.b;

上記の SQL ステートメントに対して、次の両方のクエリプランが正しい結果を返すことができます。プラン A とプラン B は、[等価なクエリプラン]と呼ばれます。オプティマイザは、変換を適用することで SQL ステートメントの等価なクエリプランを探索します。たとえば、t1 INNER JOIN t2 と t2 INNER JOIN t1 は、等価なクエリプランのペアです。オプティマイザは t2 INNER JOIN t1 を t1 INNER JOIN t2 から生成できます。このタイプの変換は、[クエリ変換ルール]と呼ばれます。

オプティマイザは、次のワークフローに従います：

1. オプティマイザは、データベースが SQL ステートメントを解析して生成した初期クエリプランを受け取ります。

2. 次に、初期プランにクエリ変換ルールを適用して、等価なクエリプランを生成します。

3. オプティマイザは、統計情報とコストモデルを使用して、推定実行コストが最も低いプランを選択し、最終的な実行計画として実行エンジンに渡します。

クエリ最適化機能は、統計情報に基づいてカーディナリティ推定とコスト計算を実行し、最適なクエリプランを決定します。IMCI では、テーブルの統計情報には次のものが含まれます：

• ヒストグラムは、列の値の分布を記述し、主に単一テーブル上の値の範囲と等価述語の選択度を推定するために使用されます。

• 列内の個別値の数は、主に Group By 句のグループ数を推定するために使用され、等価述語の選択度の推定にも役立ちます。

• 列に一意のインデックスや外部キー制約があるかどうかなど、その他の制約。

オプティマイザは、2 つの要素に基づいてクエリプラン内の各オペレーターのコストを計算します：

• オペレーターによって処理される行の総数。これは統計情報から推定できます。

• クエリプランで使用される各オペレーターのアルゴリズムの複雑さ。

お使いの PolarDB for MySQL クラスターは、次のいずれかのバージョン要件を満たしている必要があります：

• PolarDB for MySQL 8.0.1、リビジョン 8.0.1.1.31 以降。

• PolarDB for MySQL 8.0.2、リビジョン 8.0.2.2.12 以降。

クラスターのバージョン番号を表示して、クラスターのバージョンを確認できます。

次のシナリオでは、カーディナリティ推定で重大なエラーが発生し、オプティマイザが準最適なクエリプランを選択する可能性があります。ヒント構文を使用して、オプティマイザをより良いクエリプランに誘導できます。

• 同じテーブルの異なる列に対して比較オペレーターを使用する述語を含むクエリ。例： t1.c1>t1.c2。

• 統計情報を使用して推定できないオペレーターを使用する述語を含むクエリ。例： t1.c1 MOD 2=1 または t1.c2 LIKE '%ABC%'。

• 最適化中に計算できない式を含む述語を持つクエリ。例： t1.c1+t1.c3>100。

• オペレーターに関与する列に、述語の選択度を推定するために必要な統計情報がないクエリ。例： SELECT a, SUM(b) FROM t1 HAVING SUM(b) > 10。

• 複数の述語が AND オペレーターで結合されているクエリ。例： t1.c1>10 AND t1.c3<5。

• クエリのネスト階層が深すぎます。

• クエリが結合するテーブルが多すぎます。loose_imci_max_enum_join_pairs パラメーターを変更することで、IMCI オプティマイザが検索する結合の数を調整できます。

次の例では、集計関数を含む複数テーブルクエリである TPC-H Q8 を使用します。

SELECT
  o_year,
  SUM(
    CASE
      WHEN nation = 'BRAZIL' THEN volume
      ELSE 0
    END
  ) / SUM(volume) AS mkt_share
FROM
  (
    SELECT
      EXTRACT(
        year
        FROM
          o_orderdate
      ) AS o_year,
      l_extendedprice * (1 - l_discount) AS volume,
      n2.n_name AS nation
    FROM
      lineitem,
      orders,
      part,
      supplier,
      customer,
      nation n1,
      nation n2,
      region
    WHERE
      p_partkey = l_partkey
      AND s_suppkey = l_suppkey
      AND l_orderkey = o_orderkey
      AND o_custkey = c_custkey
      AND c_nationkey = n1.n_nationkey
      AND n1.n_regionkey = r_regionkey
      AND r_name = 'AMERICA'
      AND s_nationkey = n2.n_nationkey
      AND o_orderdate BETWEEN DATE '1995-01-01'
      AND DATE '1996-12-31'
      AND p_type = 'ECONOMY ANODIZED STEEL'
  ) AS all_nations
GROUP By
  o_year
ORDER BY
  o_year;

• IMCI の最適化を無効にした場合のクエリプランは次のとおりです：このプランでは、多数の結合によって大規模な中間結果セットが生成され、後続のオペレーターのデータ量と処理コストが増加するため、クエリレイテンシーが長くなります。TPC-H SF100 データセットを使用する 32 コアのマシンでは、このクエリの完了に 7,017 ms かかりました。

• IMCI の最適化を有効にした場合のクエリプランは次のとおりです：IMCI オプティマイザは結合を並べ替え、ほとんどの結合オペレーターの出力サイズを数百万行に削減します。これにより、後続のオペレーターの処理コストが効果的に削減されます。同じ TPC-H SF100 データセットを使用する 32 コアのマシンでは、このクエリの完了に 1,900 ms かかり、クエリ時間が 73% 短縮されました。