8.2.2.1 準結合変換による IN および EXISTS サブクエリー述語の最適化

準結合は、テーブルのプルアウト、重複の除去、最初の一致、緩やかなスキャン、実体化などの複数の実行戦略を可能にする準備時変換です。オプティマイザは、このセクションで説明するように、準結合戦略を使用してサブクエリーの実行を改善します。

2 つのテーブル間の内部結合の場合、結合は、他方のテーブルに一致がある回数だけ、一方のテーブルから 1 行を返します。ただし、問題によっては、重要な情報は一致の数ではなく、一致があるかどうかだけの場合があります。コースカリキュラムのクラスとクラス名簿 (各クラスに登録されている生徒) をそれぞれ一覧表示する class と roster というテーブルがあるとします。実際に生徒が登録されているクラスを一覧表示するには、次の結合を使用できます。

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

ただし、結果には、登録された生徒ごとに、各クラスが 1 回ずつ一覧表示されます。ここでの問題では、これは不要な情報の重複です。

class_num が class テーブルの主キーであると仮定すると、SELECT DISTINCT を使用して重複抑制が可能ですが、後で重複を排除するためにのみ、最初に一致するすべての行を生成することは非効率的です。

同じ重複のない結果は、次のサブクエリーを使用して取得できます。

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

ここで、オプティマイザは IN 句に roster テーブルから各クラス番号のインスタンスを 1 つだけ返すサブクエリーが必要であることを認識できます。この場合、クエリーでは準結合を使用できます。つまり、roster の行と一致する class の各行のインスタンスを 1 つのみ返す操作です。

EXISTS サブクエリー述語を含む次のステートメントは、IN サブクエリー述語を含む前述のステートメントと同等です:

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM roster WHERE class.class_num = roster.class_num);

MySQL 8.0.16 以降では、EXISTS サブクエリー述語を含むステートメントは、同等の IN サブクエリー述語を含むステートメントと同じ準結合変換の対象となります。

MySQL 8.0.17 以降、次のサブクエリーはアンチ結合に変換されます:

NOT IN (SELECT ... FROM ...)
NOT EXISTS (SELECT ... FROM ...)。
IN (SELECT ... FROM ...) IS NOT TRUE
EXISTS (SELECT ... FROM ...) IS NOT TRUE。
IN (SELECT ... FROM ...) IS FALSE
EXISTS (SELECT ... FROM ...) IS FALSE。

つまり、IN (SELECT ... FROM ...) または EXISTS (SELECT ... FROM ...) 形式のサブクエリーの否定は、アンチ結合に変換されます。

アンチ結合は、一致がない行のみを返す操作です。次に示すクエリーについて考えてみます:

SELECT class_num, class_name
    FROM class
    WHERE class_num NOT IN
        (SELECT class_num FROM roster);

このクエリーは内部的にアンチ結合 SELECT class_num, class_name FROM class ANTIJOIN roster ON class_num としてリライトされ、roster のどの行とも一致しない class の各行のインスタンスが返されます。つまり、class の各行については、roster で一致が見つかるとすぐに class の行を破棄できます。

比較対象の式が NULL 値可能な場合、ほとんどの場合、アンチ結合変換は適用できません。このルールの例外は、(... NOT IN (SELECT ...)) IS NOT FALSE とそれに相当する (... IN (SELECT ...)) IS NOT TRUE をアンチ結合に変換できることです。

外部結合および内部結合の構文は外部クエリー仕様で許可され、テーブル参照は実テーブル、導出テーブル、ビュー参照または共通テーブル式です。

MySQL では、サブクエリーを準結合として処理するには、次の基準を満たす必要があります (MySQL 8.0.17 以降では、NOT がサブクエリーを変更する場合はアンチ結合):

WHERE 句または ON 句の最上位レベルに表示される IN、= ANY または EXISTS 述語の一部である必要があります。AND 式の用語として使用することもできます。例:
```
SELECT ...
    FROM ot1, ...
    WHERE (oe1, ...) IN
        (SELECT ie1, ... FROM it1, ... WHERE ...);
```
ここで、ot_i と it_i は、クエリーの外側部分と内側部分のテーブルを表し、oe_i と ie_i は、外部テーブルと内部テーブル内のカラムを参照する式を表します。
MySQL 8.0.17 以降では、サブクエリーは NOT、IS [NOT] TRUE または IS [NOT] FALSE によって変更された式の引数にすることもできます。
それは UNION コンストラクトのない単一の SELECT である必要があります。
HAVING 句を含めることはできません。
(明示的または暗黙的にグループ化されているかどうかにかかわらず) 集計関数を含めることはできません。
LIMIT 句を指定しないでください。
ステートメントでは、外部クエリーで STRAIGHT_JOIN 結合タイプを使用しないでください。
STRAIGHT_JOIN 修飾子は指定できません。
外部テーブルとおよび内部テーブルの合計数が結合で許可されている最大テーブル数より少なくなければなりません。
サブクエリーは相関する場合と相関しない場合があります。 MySQL 8.0.16 以降では、デコレーションは EXISTS への引数として使用されるサブクエリーの WHERE 句内の簡易相関述語を調べ、IN (SELECT b FROM ...) 内で使用されたかのように最適化できます。 簡易相関という用語は、述語が等価述語であり、WHERE 句の唯一の述語である (または AND と組み合されている) こと、および一方のオペランドがサブクエリーで参照されるテーブルのもので、もう一方のオペランドが外部クエリーブロックのものであることを意味します。
DISTINCT キーワードは使用できますが、無視されます。準結合戦略では、重複削除が自動的に処理されます。
サブクエリーに集計関数も含まれていないかぎり、GROUP BY 句は許可されますが無視されます。
順序付けは準結合戦略の評価には関係ないため、ORDER BY 句は許可されますが無視されます。

サブクエリーが前述の基準を満たす場合、MySQL は準結合 (MySQL 8.0.17 以降ではアンチ結合) に変換し、次の方法からコストベースの選択を行います:

サブクエリーを結合に変換するか、テーブルプルアウトを使用して、クエリーをサブクエリーテーブルと外部テーブル間の内部結合として実行します。テーブルプルアウトは、テーブルをサブクエリーから外部クエリーに引き出します。
重複の除去: 準結合を結合として実行し、一時テーブルを使用して重複レコードを削除します。
FirstMatch: 内部テーブルで行の組合せをスキャンし、特定の値グループに複数のインスタンスがある場合は、すべてを戻すのではなく、いずれかを選択します。これはスキャンを「ショートカット」し、不要な行の生成をなくします。
LooseScan : 各サブクエリー値グループから単一の値を選択できるインデックスを使用して、サブクエリーテーブルをスキャンします。
結合の実行に使用されるインデックス付き一時テーブルにサブクエリーを実体化します。インデックスは重複を削除するために使用されます。さらに、インデックスはあとで一時テーブルと外部テーブルを結合する際のルックアップにも使用されることがあります。そうでない場合はテーブルがスキャンされます。実体化の詳細は、セクション8.2.2.2「実体化を使用したサブクエリーの最適化」を参照してください。

これらの各戦略は、次の optimizer_switch システム変数フラグを使用して有効または無効にできます:

semijoin フラグは、準結合を使用するかどうかを制御します。 MySQL 8.0.17 以降、これはアンチ結合にも適用されます。
semijoin が有効になっている場合、firstmatch, loosescan, duplicateweedout および materialization フラグを使用すると、許可される準結合戦略をより詳細に制御できます。
duplicateweedout 準結合戦略が無効になっている場合は、他のすべての適用可能な戦略も無効にしないかぎり、使用されません。
duplicateweedout が無効になっている場合、オプティマイザによって最適ではないクエリー計画が生成されることがあります。これは、最長一致検索中のヒューリスティックプルーニングが原因で発生します。これは、optimizer_prune_level=0 を設定することで回避できます。

これらのフラグはデフォルトで有効になっています。セクション8.9.2「切り替え可能な最適化」を参照してください。

オプティマイザは、ビューおよび導出テーブルの処理の違いを最小限に抑えます。これは、STRAIGHT_JOIN 修飾子を使用するクエリーおよび準結合に変換可能な IN サブクエリーを含むビューに影響します。次のクエリーは、処理の変更によって変換が変更されるため、実行計画が異なるため、これを示しています:

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

オプティマイザはまずビューを参照し、IN サブクエリーを準結合に変換してから、ビューを外部クエリーにマージできるかどうかをチェックします。外部クエリーの STRAIGHT_JOIN 修飾子によって準結合が妨げられるため、オプティマイザはマージを拒否し、実体化テーブルを使用して導出テーブルを評価します。

EXPLAIN 出力には、次のような準結合戦略の使用が示されています:

拡張 EXPLAIN 出力の場合、次の SHOW WARNINGS によって表示されるテキストは、準結合構造を表示するリライトされたクエリーを示しています。 (セクション8.8.3「拡張 EXPLAIN 出力形式」を参照してください。) このページから、準結合から取得されたテーブルについて理解できます。サブクエリーが準結合に変換された場合は、サブクエリー述語がなくなり、そのテーブルおよび WHERE 句が外部クエリー結合リストおよび WHERE 句にマージされたことがわかります。
重複の除去のための一時テーブルの使用は、Extra カラムの Start temporary と End temporary によって示されます。プルされず、Start temporary および End temporary でカバーされる EXPLAIN 出力行の範囲内にあるテーブルの一時テーブルには、rowid があります。
Extra カラムの FirstMatch(tbl_name) は結合のショートカットを示します。
Extra カラムの LooseScan(m..n) は LooseScan 戦略の使用を示します。m と n はキーパート番号です。
実体化での一時テーブルの使用は、select_type 値が MATERIALIZED の行と、table 値が <subqueryN> の行によって示されます。

MySQL 8.0.21 以降では、準結合変換は、単一テーブル UPDATE、または[NOT] IN または[NOT] EXISTS サブクエリー述語を使用する DELETE ステートメントへ適用もできます。ただし、ステートメントが ORDER BY または LIMIT を使用せず、準結合変換がオプティマイザヒントにより、または optimizer_switch 設定により許可されている場合です。