Movatterモバイル変換

このページは機械翻訳したものです。

特定の最適化は、IN (または=ANY) 演算子を使用してサブクエリーの結果をテストする比較に適用できます。 このセクションでは、これらの最適化について、特にNULL 値が存在する課題に関して説明します。 この説明の最後の部分では、オプティマイザの支援方法を示します。

次のようなサブクエリーの比較を考慮します。

outer_expr IN (SELECTinner_expr FROM ... WHEREsubquery_where)

MySQL は「外側から内側に」クエリーを評価します。 つまり、まず外側の式outer_expr の値を取得してから、サブクエリーを実行し、それによって生成される行を取得します。

内側の式inner_expr がouter_expr と等しい行だけが目的の行であることをサブクエリーに「通知する」ことは、かなり役に立つ最適化です。 これを行うには、サブクエリーのWHERE 句に適切な等価をプッシュダウンして、より限定的にします。 変換された比較は次のようになります:

EXISTS (SELECT 1 FROM ... WHEREsubquery_where ANDouter_expr=inner_expr)

変換後、MySQL はプッシュダウンされた等価を使用して、サブクエリーを評価するために調査する必要がある行数を制限できます。

より一般的には、N 個の値とN 値の行を返すサブクエリーとの比較は、同じ変換の対象になります。oe_i とie_i が対応する外側と内側の式の値を表す場合、次のサブクエリー比較は:

(oe_1, ...,oe_N) IN  (SELECTie_1, ...,ie_N FROM ... WHEREsubquery_where)

次のようになります。

EXISTS (SELECT 1 FROM ... WHEREsubquery_where                          ANDoe_1 =ie_1                          AND ...                          ANDoe_N =ie_N)

簡単にするために、次の説明では、外部式と内部式の値の単一のペアを想定しています。

前述の「「プッシュダウン」」戦略は、次のいずれかの条件に該当する場合に機能します:

outer_expr はNULL 以外の値であることがわかっているが、サブクエリーはouter_expr =inner_expr となるような行を生成しないものとします。 その場合、outer_expr IN (SELECT ...) は次のように評価されます。

この状況では、outer_expr =inner_expr である行を探すアプローチは有効でなくなります。 そのような行を探すことは必要ですが、何も見つからない場合には、inner_expr がNULL となる行も探します。 概して言えば、サブクエリーは次のように変換できます:

EXISTS (SELECT 1 FROM ... WHEREsubquery_where AND        (outer_expr=inner_expr ORinner_expr IS NULL))

追加のIS NULL 条件を評価する必要性は、MySQL にref_or_null アクセスメソッドがある理由です。

mysql> EXPLAIN       SELECTouter_expr IN (SELECT t2.maybe_null_key                             FROM t2, t3 WHERE ...)       FROM t1;*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1...*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: t2         type: ref_or_nullpossible_keys: maybe_null_key          key: maybe_null_key      key_len: 5          ref: func         rows: 2        Extra: Using where; Using index...

unique_subquery およびindex_subquery サブクエリー固有のアクセスメソッドには「orNULL」 バリアントもあります。

追加のOR ... IS NULL 条件によってクエリーの実行は多少複雑になり、サブクエリー内の最適化の一部も適用できなくなりますが、通常これは許容できます。

outer_expr がNULL になる可能性がある場合、状況ははるかに悪くなります。「不明な値」としてのNULL の SQL の解釈によると、NULL IN (SELECTinner_expr ...) は次のように評価されるはずです。

正しい評価には、SELECT がとにかく何らかの行を生成したかどうかを確認できるようにする必要があるため、outer_expr =inner_expr をサブクエリーにプッシュダウンすることはできません。 等価をプッシュダウンできないかぎり、多くの実世界のサブクエリーが非常に遅くなるため、これは問題です。

基本的に、outer_expr の値に応じて、サブクエリーを実行するさまざまな方法が存在する必要があります。

オプティマイザは速度よりも SQL 準拠を選択するため、outer_expr がNULL である可能性があります:

条件をサブクエリーにプッシュダウンするかどうかの問題を解決するために、条件は「trigger」 関数内にラップされます。 したがって、次の形式の式は:

outer_expr IN (SELECTinner_expr FROM ... WHEREsubquery_where)

次に変換されます:

EXISTS (SELECT 1 FROM ... WHEREsubquery_where                          AND trigcond(outer_expr=inner_expr))

より一般的には、サブクエリーの比較が外側の式と内側の式の複数のペアに基づく場合、変換は次の比較をします。

(oe_1, ...,oe_N) IN (SELECTie_1, ...,ie_N FROM ... WHEREsubquery_where)

これを次の式に変換します:

EXISTS (SELECT 1 FROM ... WHEREsubquery_where                          AND trigcond(oe_1=ie_1)                          AND ...                          AND trigcond(oe_N=ie_N)       )

各trigcond(X) は、次の値に評価される特殊な関数です。

trigcond() 関数内でラップされる等価は、クエリーオプティマイザのファーストクラス述語ではありません。 ほとんどの最適化では、クエリーの実行時にオンまたはオフになる可能性のある述語を処理できないため、trigcond(X) をすべて不明な関数であるとみなし、無視します。 トリガーされた等価は、次の最適化で使用できます:

オプティマイザがトリガー条件を使用して、何らかの種類のインデックスルックアップベースのアクセスを作成する場合 (上記リストの最初の 2 項目に関して)、条件がオフである場合のフォールバック戦略が必要です。 このフォールバック戦略は常に同じで、フルテーブルスキャンを実行します。EXPLAIN の出力で、フォールバックはExtra カラムにFull scan on NULL key と表示されます。

mysql> EXPLAIN SELECT t1.col1,       t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1        ...*************************** 2. row ***************************           id: 2  select_type: DEPENDENT SUBQUERY        table: t2         type: index_subquerypossible_keys: key1          key: key1      key_len: 5          ref: func         rows: 2        Extra: Using where; Full scan on NULL key

EXPLAIN の後にSHOW WARNINGS を実行すると、トリガーされた条件が表示されます:

*************************** 1. row ***************************  Level: Note   Code: 1003Message: select `test`.`t1`.`col1` AS `col1`,         <in_optimizer>(`test`.`t1`.`col1`,         <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2         on key1 checking NULL         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having         trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`         from `test`.`t1`

トリガー条件を使用すると、パフォーマンスに多少の影響があります。 現在NULL IN (SELECT ...) 式では、以前に実行されなかった (遅い) フルテーブルスキャンが行われる可能性があります。 これは、正しい結果を得るために支払われる価格です (トリガー条件戦略の目的は、速度ではなくコンプライアンスを向上させることです)。

複数テーブルサブクエリーの場合、結合オプティマイザは外部式がNULL の場合に最適化しないため、NULL IN (SELECT ...) の実行は特に遅くなります。 それは、左辺がNULL の場合のサブクエリーの評価はめったにないものと想定しています (そうでないことを示す統計があっても)。 一方、外側の式がNULL になる可能性があっても実際にそうなることがない場合、パフォーマンスの低下はありません。

クエリーオプティマイザがクエリーをより効率的に実行できるようにするには、次の提案を使用します:

optimizer_switch システム変数のsubquery_materialization_cost_based フラグを使用すると、サブクエリー実体化とIN からEXISTS へのサブクエリー変換の選択を制御できます。セクション8.9.2「切り替え可能な最適化」を参照してください。