MySQL 8.0 リファレンスマニュアル MySQL NDB Cluster 8.0 を含む
このページは機械翻訳したものです。
準結合は、テーブルのプルアウト、重複の除去、最初の一致、緩やかなスキャン、実体化などの複数の実行戦略を可能にする準備時変換です。 オプティマイザは、このセクションで説明するように、準結合戦略を使用してサブクエリーの実行を改善します。
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(
は LooseScan 戦略の使用を示します。m
..n
)m
と n
はキーパート番号です。
実体化での一時テーブルの使用は、select_type
値が MATERIALIZED
の行と、table
値が <subquery
の行によって示されます。
N
>
MySQL 8.0.21 以降では、準結合変換は、単一テーブル UPDATE
、または[NOT] IN
または[NOT] EXISTS
サブクエリー述語を使用する DELETE
ステートメントへ適用もできます。ただし、ステートメントが ORDER BY
または LIMIT
を使用せず、準結合変換がオプティマイザヒントにより、または optimizer_switch
設定により許可されている場合です。