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

8.2.2.2 実体化を使用したサブクエリーの最適化

オプティマイザは実体化を使用して、より効率的なサブクエリー処理を可能にします。 実体化は、通常メモリー内に一時テーブルとしてサブクエリー結果を生成することによって、クエリー実行を高速化します。 MySQL ははじめてサブクエリー結果を必要としたときに、その結果を一時テーブルに実体化します。 あとで結果が必要になったときに、MySQL は再度一時テーブルを参照します。 オプティマイザはハッシュインデックスを使用してテーブルをインデックス付けし、高速かつ低コストにルックアップできる場合があります。 インデックスには、重複を排除してテーブルを小さくするための一意の値が含まれています。

サブクエリーの実体化では、可能な場合はインメモリー一時テーブルが使用され、テーブルが大きすぎるとディスク上の記憶域にフォールバックします。 セクション8.4.4「MySQL での内部一時テーブルの使用」を参照してください。

実体化を使用しない場合、オプティマイザは、非相関サブクエリーを相関サブクエリーとして書き換えることがあります。 たとえば、次の IN サブクエリーは非相関です (where_condition には t2 からのカラムのみが含まれ、t1 からは含まれません)。

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

オプティマイザはこれを EXISTS 相関サブクエリーとして書き換えることがあります。

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

一時テーブルを使用したサブクエリー実体化により、そのような書き換えを回避し、外部クエリーの行ごとに 1 回ではなく、1 回だけサブクエリーを実行させることができます。

MySQL でサブクエリー実体化を使用するには、optimizer_switch システム変数 materialization フラグを有効にする必要があります。 (セクション8.9.2「切り替え可能な最適化」を参照してください。) materialization フラグを有効にすると、実体化は、次のユースケースのいずれかに該当する述語に対して、(選択リスト、WHERE, ON, GROUP BY, HAVING または ORDER BY) 任意の場所に出現するサブクエリー述語に適用されます:

• 外側の式 oe_i または内側の式 ie_i が NULL 可能でない場合に、述語はこの形式になります。 N は 1 以上です。

  (oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

• 単一の外側の式 oe と内側の式 ie がある場合に、述語はこの形式になります。 式は NULL 可能にできます。

  oe [NOT] IN (SELECT ie ...)

• 述語は IN または NOT IN で UNKNOWN (NULL) の結果は FALSE の結果と同じ意味になります。

次の例に、UNKNOWN および FALSE 述語評価の同等性の要件が、サブクエリー実体化を使用できるかどうかにどのように影響するかを示します。 サブクエリーが非相関になるように、where_condition に t2 からのカラムのみが含まれ、t1 からは含まれないとします。

このクエリーは実体化の対象になります。

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

ここでは、IN 述語が UNKNOWN を返すか、FALSE を返すかは問題ではありません。 どちらも t1 からの行はクエリー結果に含まれません。

サブクエリーの実体化が使用されていない例は、次のクエリーで、t2.b は NULL 値可能なカラムです:

SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
                          WHERE where_condition);

サブクエリー実体化の使用には、次の制限が適用されます:

• 内部式と外部式の型は一致する必要があります。 たとえば、両方の式が整数であるか、両方が小数の場合、オプティマイザは実体化を使用できますが、一方の式が整数でもう一方が小数の場合は使用できません。

• 内部式は BLOB にできません。

クエリーで EXPLAIN を使用すると、オプティマイザがサブクエリーの実体化を使用するかどうかがわかります:

• 実体化を使用しないクエリー実行と比較して、select_type が DEPENDENT SUBQUERY から SUBQUERY に変更されることがあります。 これは、外部行ごとに 1 回実行されるサブクエリーの場合、実体化によってサブクエリーが 1 回だけ実行されるようにできることを示します。

• 拡張 EXPLAIN 出力の場合、次の SHOW WARNINGS によって表示されるテキストには、materialize および materialized-subquery が含まれます。

MySQL 8.0.21 以降では、MySQL は、単一テーブル UPDATE へ、または[NOT] IN または[NOT] EXISTS サブクエリー述語を使用する DELETE ステートメントへサブクエリー実体化が、ステートメントが ORDER BY または LIMIT を使用せず、およびサブクエリーの実体化がオプティマイザヒントにより、または optimizer_switch 設定により許可されていれば、提供できます。