オプティマイザは、次の 2 つの戦略を使用して導出テーブル参照を処理できます (ビュー参照および共通テーブル式にも適用されます):

例 1:

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

導出テーブル derived_t1 のマージでは、そのクエリーは次のように実行されます:

SELECT * FROM t1;

例 2:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

導出テーブル derived_t2 のマージでは、そのクエリーは次のように実行されます:

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

実体化では、derived_t1 と derived_t2 はそれぞれ、それぞれのクエリー内で個別のテーブルとして扱われます。

オプティマイザは、導出テーブル、ビュー参照および共通テーブル式を同様に処理: これにより、可能なかぎり不要な実体化が回避され、外部クエリーから導出テーブルへの条件のプッシュダウンが可能になり、より効率的な実行計画が生成されます。 (例については、セクション8.2.2.2「実体化を使用したサブクエリーの最適化」を参照してください。)

マージによって 61 を超える実テーブルを参照する外部クエリーブロックが生成される場合、オプティマイザはかわりに実体化を選択します。

次の条件がすべて満たされている場合、オプティマイザは導出テーブルまたはビュー参照の ORDER BY 句を外部クエリーブロックに伝播します:

それ以外の場合、オプティマイザは ORDER BY 句を無視します。

オプティマイザが導出テーブル、ビュー参照および共通テーブル式を外部クエリーブロックにマージしようとするかどうかに影響を与えるために、次の方法を使用できます:

オプティマイザが導出テーブルのマージではなく実体化戦略を選択した場合、クエリーは次のように処理されます:

導出テーブルを含む SELECT クエリーについて、次の EXPLAIN ステートメントを考えてみます:

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

オプティマイザは、SELECT の実行中に結果が必要になるまで導出テーブルを遅延させることで、導出テーブルの実体化を回避します。 この場合、(EXPLAIN ステートメントで発生するため) クエリーは実行されないため、結果は必要ありません。

実行されるクエリーの場合でも、導出テーブルの実体化の遅延により、オプティマイザは実体化を完全に回避できます。 これが発生すると、クエリー実行は実体化の実行に必要な時間が短縮されます。 導出テーブルの結果を別のテーブルに結合する次のクエリーについて考えてみます:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

最適化が最初に t1 を処理し、WHERE 句で空の結果が生成される場合、結合は空である必要があり、導出テーブルを実体化する必要はありません。

導出テーブルに実体化が必要な場合は、オプティマイザによって実体化ビューにインデックスが追加され、アクセスが高速化される可能性があります。 このようなインデックスによってテーブルへの ref アクセスが可能になると、クエリーの実行中に読み取られるデータ量を大幅に削減できます。 次のクエリーを考慮してください。

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

オプティマイザは、コストが最も低い実行計画に対して ref アクセスを使用できるようにする場合、derived_t2 からカラム f1 に対するインデックスを構成します。 インデックスを追加した後、オプティマイザは実体化導出テーブルをインデックス付きの通常のテーブルと同様に処理でき、生成されたインデックスと同様の利点があります。 インデックス作成のオーバーヘッドは、インデックスを使用しないクエリー実行のコストと比較して無視できます。 ref アクセスによって他のアクセス方法よりコストが高くなる場合、オプティマイザはインデックスを作成せず、何も失われません。

オプティマイザトレース出力の場合、マージされた導出テーブルまたはビュー参照はノードとして表示されません。 最上位のクエリー計画には、基礎となるテーブルのみが表示されます。

導出テーブルの実体化に関して正しい記述は、共通テーブル式 (CTE) についても当てはまります。 さらに、CTE には特に次の考慮事項があります。

CTE がクエリーによって実体化されている場合、クエリーが複数回参照していても、その CTE はクエリーに対して 1 回実体化されます。

再帰 CTE は常に実体化されます。

CTE が実体化されている場合、オプティマイザは、トップレベルのステートメントによる CTE へのアクセスを高速化できると予想すると、関連するインデックスを自動的に追加します。 これは導出テーブルの自動インデックス付けと似ていますが、CTE が複数回参照される場合、オプティマイザは複数のインデックスを作成して、各参照によるアクセスを最適な方法で高速化できます。

CTE には、MERGE および NO_MERGE オプティマイザヒントを適用できます。 トップレベルのステートメントの各 CTE 参照には独自のヒントを指定でき、CTE 参照を選択的にマージまたは実体化できます。 次のステートメントは、ヒントを使用して、cte1 をマージし、cte2 を実体化する必要があることを示します:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT /*+ MERGE(cte1) NO_MERGE(cte2) */ cte1.b, cte2.d
FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

CREATE VIEW の ALGORITHM 句は、ビュー定義の SELECT ステートメントの前にある WITH 句の実体化には影響しません。 次のステートメントがあるとします。

CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH ... SELECT ...

ALGORITHM 値は、WITH 句ではなく、SELECT の実体化にのみ影響します。

MySQL 8.0.16 より前では、internal_tmp_disk_storage_engine=MYISAM でディスク上の一時テーブルを使用して CTE を実体化しようとすると、CTE の場合、ディスク上の内部一時テーブルに使用されるストレージエンジンを MyISAM にできなかったため、エラーが発生しました。 MySQL 8.0.16 以降、TempTable ではディスク上の内部一時テーブルに InnoDB が常に使用されるようになったため、これは問題ではなくなりました。

前述のように、CTE(実体化されている場合) は、複数回参照されていても実体化されます。 一時的な実体化を示すために、オプティマイザトレース出力には、creating_tmp_table のオカレンスと reusing_tmp_table のオカレンスが含まれます。

CTE は、materialized_from_subquery ノードが参照に従う導出テーブルに似ています。 これは、複数回参照される CTE に当てはまるため、materialized_from_subquery ノードの複製はありません (これにより、サブクエリーが複数回実行され、不必要に冗長な出力が生成されるインプレッションが提供されます)。 CTE への参照には、サブクエリー計画の説明を含む完全な materialized_from_subquery ノードのみが含まれます。 その他の参照では、materialized_from_subquery ノードが削減されます。 TRADITIONAL 形式の EXPLAIN 出力にも同じ考えが適用されます: 他の参照のサブクエリーは表示されません。