オプティマイザは実体化を使用して、より効率的なサブクエリー処理を可能にします。 実体化は、通常メモリー内に一時テーブルとしてサブクエリー結果を生成することによって、クエリー実行を高速化します。 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) 任意の場所に出現するサブクエリー述語に適用されます:

次の例に、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);

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

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

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