Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


8.2.1.18 サブクエリーの最適化

MySQL クエリーオプティマイザには、サブクエリーの評価に使用できるさまざまな戦略があります。IN (または =ANY) サブクエリーの場合、オプティマイザには次の選択肢があります。

  • 準結合

  • 実体化

  • EXISTS 戦略

NOT IN (または <>ALL) サブクエリーの場合、オプティマイザには次の選択肢があります。

  • 実体化

  • EXISTS 戦略

次のセクションでは、これらの最適化戦略について詳しく説明します。

8.2.1.18.1 準結合変換によるサブクエリーの最適化

MySQL 5.6.5 現在、オプティマイザは、このセクションで説明するように、準結合戦略を使用して、サブクエリーの実行を改善します。

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

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

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

class_numclass テーブル内の主キーとすると、重複の抑制は、SELECT DISTINCT を使用して実現できますが、あとで重複を除去するためにだけ、まずすべての一致する行を生成することは非効率的です。

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

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

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

MySQL 5.6.6 より前では、外部クエリーの指定は、単純なテーブルスキャンやカンマ構文を使用した内部結合に制限されており、ビュー参照は不可能でした。5.6.6 現在、外部クエリー指定で外部結合および内部結合構文を使用でき、テーブル参照がベーステーブルでなければいけないという制限はなくなりました。

MySQL では、サブクエリーは準結合として扱われるために、次の条件を満たしている必要があります。

  • それは、おそらく AND 式内の項として、WHERE 句または ON 句のトップレベルに表示される IN (または =ANY) サブクエリーである必要があります。例:

    SELECT ...
    FROM ot1, ...
    WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);

    ここで、ot_iit_i は、クエリーの外側部分と内側部分のテーブルを表し、oe_iie_i は、外部テーブルと内部テーブル内のカラムを参照する式を表します。

  • それは UNION コンストラクトのない単一の SELECT である必要があります。

  • それには GROUP BY または HAVING 句または集約関数が含まれていてはなりません。

  • それには、LIMIT を使用した ORDER BY があってはなりません。

  • 外部テーブルとおよび内部テーブルの合計数が結合で許可されている最大テーブル数より少なくなければなりません。

サブクエリーは相関する場合と相関しない場合があります。LIMIT と同様に、ORDER BY も使用しなければ、DISTINCT を使用できます。

サブクエリーが先述の条件を満たしている場合、MySQL はそれを準結合に変換し、次の戦略からコストに基づいた選択を行います。

  • サブクエリーを結合に変換するか、テーブルプルアウトを使用して、クエリーをサブクエリーテーブルと外部テーブル間の内部結合として実行します。テーブルプルアウトは、テーブルをサブクエリーから外部クエリーに引き出します。

  • 重複の除去: 準結合を結合のように実行し、一時テーブルを使用して、重複レコードを削除します。

  • FirstMatch: 行の組み合わせの内部テーブルをスキャンし、指定した値グループの複数のインスタンスがある場合、それらすべてを返すのではなく、1 つを選択します。これはスキャンを「ショートカット」し、不要な行の生成をなくします。

  • LooseScan: 各サブクエリーの値グループから単一の値を選択できるようにするインデックスを使用して、サブクエリーテーブルをスキャンします。

  • サブクエリーをインデックス付きの一時テーブルに実体化し、その一時テーブルを使用して、結合を実行します。インデックスは重複の削除に使用されます。さらに、インデックスはあとで一時テーブルと外部テーブルを結合する際のルックアップにも使用されることがあります。そうでない場合はテーブルがスキャンされます。

重複の除去を除いて、これらの各戦略を有効または無効にするには、optimizer_switch システム変数を使用します。semijoin フラグは準結合を使用するかどうかを制御します。これが on に設定されている場合、firstmatchloosescan、および materialization フラグによって、使用可能な準結合戦略を詳細に制御できます。これらのフラグはデフォルトで on です。セクション8.8.5.2「切り替え可能な最適化の制御」を参照してください。

準結合戦略の使用は、EXPLAIN 出力で次のように示されます。

  • 準結合されたテーブルは、外側の選択に表示されます。EXPLAIN EXTENDEDSHOW WARNINGS には書き換えられたクエリーが示され、準結合構造が表示されます。ここから、準結合から引き出されたテーブルについての情報を得ることができます。サブクエリーが準結合に変換された場合、サブクエリー述語がなくなっており、そのテーブルと WHERE 句が外部クエリー結合リストと WHERE 句にマージされたことがわかります。

  • 重複の除去のための一時テーブルの使用は、Extra カラムの Start temporaryEnd temporary によって示されます。引き出されておらず、Start temporaryEnd temporary によってカバーされる EXPLAIN 出力行の範囲内にあるテーブルは、一時テーブルにそれらの rowid が格納されます。

  • Extra カラムの FirstMatch(tbl_name) は結合のショートカットを示します。

  • Extra カラムの LooseScan(m..n) は LooseScan 戦略の使用を示します。mn はキーパート番号です。

  • MySQL 5.6.7 現在、実体化のための一時テーブルの使用は、MATERIALIZEDselect_type 値のある行と、<subqueryN>table 値のある行によって示されます。

    MySQL 5.6.7 より前では、実体化のための一時テーブルの使用は、Extra カラムに、単一のテーブルが使用された場合 Materialize によって示され、複数のテーブルが使用された場合 Start materializeEnd materialize によって示されます。Scan が存在する場合、テーブルの読み取りに一時テーブルインデックスは使用されません。そうでない場合は、インデックスルックアップが使用されます。

8.2.1.18.2 サブクエリー実体化によるサブクエリーの最適化

MySQL 5.6.5 現在、オプティマイザは、サブクエリー処理の効率向上を可能にする戦略として、サブクエリー実体化を使用します。

実体化を使用しない場合、オプティマイザは、非相関サブクエリーを相関サブクエリーとして書き換えることがあります。たとえば、次の 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 ははじめてサブクエリー結果を必要としたときに、その結果を一時テーブルに実体化します。あとで結果が必要になったときに、MySQL は再度一時テーブルを参照します。テーブルはルックアップを高速にし、負荷を軽減するため、ハッシュインデックスによってインデックス設定されます。このインデックスは一意で、重複がないため、テーブルを小さくします。

サブクエリー実体化では、可能なかぎりインメモリー一時テーブルを使用しようとし、テーブルが大きくなりすぎた場合、ディスク上のストレージに戻ります。セクション8.4.4「MySQL が内部一時テーブルを使用する仕組み」を参照してください。

MySQL で使用されるサブクエリー実体化では、optimizer_switch システム変数の materialization フラグが on である必要があります。その後実体化は、任意の場所 (選択リスト、WHEREONGROUP BYHAVING、または 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 INUNKNOWN (NULL) の結果は FALSE の結果と同じ意味になります。

次の例に、UNKNOWN および FALSE 述語評価の同等性の要件が、サブクエリー実体化を使用できるかどうかにどのように影響するかを示します。サブクエリーが非相関になるように、where_conditiont2 からのカラムのみが含まれ、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 を使用すると、オプティマイザがサブクエリー実体化を使用しているかどうかの何らかの指示が得られます。実体化を使用しないクエリー実行と比較して、select_typeDEPENDENT SUBQUERY から SUBQUERY に変更されることがあります。これは、外部行ごとに 1 回実行されるサブクエリーの場合、実体化によってサブクエリーが 1 回だけ実行されるようにできることを示します。さらに、EXPLAIN EXTENDED の場合、次の SHOW WARNINGS によって表示されるテキストには materialize materialize および materialized-subquery (MySQL 5.6.6 より前では materialized subselect) が含まれます。

8.2.1.18.3 FROM 句内のサブクエリー (派生テーブル) の最適化

MySQL 5.6.3 現在、オプティマイザは FROM 句内のサブクエリー (つまり派生テーブル) をより効率的に処理します。

  • FROM 句内のサブクエリーの実体化は、クエリー実行中にその内容が必要になるまで延期されるので、パフォーマンスが向上します。

    • これまで、FROM 句内のサブクエリーは EXPLAIN SELECT ステートメントに対して実体化されていました。これにより、EXPLAIN の目的がクエリーを実行することではなく、クエリー計画情報を取得することであっても、SELECT が部分的に実行されました。この実体化は行われなくなったため、EXPLAIN はそのようなクエリーに対して高速化しています。

    • EXPLAIN 以外のクエリーでは、実体化の遅延によって、それをまったく実行する必要がなくなることがあります。FROM 句内のサブクエリーの結果を別のテーブルに結合するクエリーを考慮します。オプティマイザはその他方のテーブルを最初に処理し、それが行を返さないことがわかると、それ以上結合を実行する必要はないため、オプティマイザはサブクエリーの実体化を完全にスキップできます。

  • クエリー実行中に、オプティマイザは派生テーブルにインデックスを追加して、そこからの行の取得を高速化できます。

SELECT クエリーの FROM 句にサブクエリーが表示される、次の EXPLAIN ステートメントを考慮します。

EXPLAIN SELECT * FROM (SELECT * FROM t1);

オプティマイザは、SELECT 実行中に結果が必要になるまで、サブクエリーの実体化を遅延させて、それを回避します。この例では、クエリーが実行されないため、結果が必要になることはありません。

実行されるクエリーの場合でも、サブクエリー実体化の遅延によって、オプティマイザは実体化を完全に避けられることがあります。FROM 句内のサブクエリーの結果を別のテーブルに結合する次のクエリーを考慮します。

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

最適化によって t1 が最初に処理され、WHERE 句で空の結果が生成された場合、結合は空である必要があり、サブクエリーは実体化される必要はありません。

最悪の場合 (派生テーブルが実体化される)、追加の作業が実行されないため、クエリーの実行に MySQL 5.6.3 より前と同じ時間がかかります。最善の場合 (派生テーブルが実体化されない)、実体化の実行に必要な時間の分だけ、クエリーの実行が速くなります。

FROM 句内のサブクエリーに実体化が必要な場合、オプティマイザは、実体化されたテーブルにインデックスを追加して、結果へのアクセスを高速化できます。そのようなインデックスによって、テーブルに ref アクセスできる場合、クエリー実行中に読み取る必要があるデータの量を大幅に削減できます。次のクエリーを考慮してください。

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

オプティマイザは、derived_t2 のカラム f1 に対してインデックスを構築することで、最低コストの実行プランでの ref アクセスの使用が可能になる場合に、そのようにします。インデックスの追加後、オプティマイザは、実体化された派生テーブルをインデックス付きの通常のテーブルと同じように扱うことができ、生成されたインデックスから同様の利点が得られます。インデックス作成のオーバーヘッドは、インデックスを使用しないクエリー実行のコストと比較して無視できます。ref アクセスが、ほかのアクセスメソッドよりコストが高くなる場合は、インデックスが作成されず、オプティマイザは何も失いません。

8.2.1.18.4 EXISTS 戦略によるサブクエリーの最適化

IN 演算子を使用して (または、同等の =ANY を使用して) サブクエリーの結果をテストする比較に、特定の最適化を適用できます。このセクションでは、これらの最適化について、特に NULL 値が存在する課題に関して説明します。説明の最後の部分では、オプティマイザを支援するためにユーザーが実行できることに関する提案も紹介します。

次のようなサブクエリーの比較を考慮します。

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL は外側から内側にクエリーを評価します。つまり、まず外側の式 outer_expr の値を取得してから、サブクエリーを実行し、それによって生成される行を取得します。

内側の式 inner_exprouter_expr と等しい行だけが目的の行であることをサブクエリーに通知することは、かなり役に立つ最適化です。これを実行するには、適切な等式をサブクエリーの WHERE 句にプッシュダウンします。つまり、この比較は次のように変換されます。

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

変換後、MySQL はプッシュダウンされた等式を使用して、サブクエリーの評価時に検査する必要のある行数を制限できます。

より一般的には、N 個の値と N 値の行を返すサブクエリーとの比較は、同じ変換の対象になります。oe_iie_i が対応する外側と内側の式の値を表す場合、次のサブクエリー比較は:

(oe_1, ..., oe_N) IN
  (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

次のようになります。

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)

以下の説明では、簡単にするために、1 組の外側と内側の式の値があると仮定します。

先述の変換には制限があります。これは、可能性のある NULL 値を無視する場合にかぎり有効です。つまり、プッシュダウン戦略は、次の 2 つの条件が両方とも true であるかぎり機能します。

  • outer_exprinner_exprNULL にできません。

  • FALSE サブクエリー結果と NULL を区別する必要はありません。(サブクエリーが WHERE 句内の OR または AND 式の一部である場合に、MySQL はユーザーが気にしないものと想定します。)

これらの条件の一方または両方が成立しない場合、最適化は複雑になります。

outer_exprNULL 以外の値であることがわかっているが、サブクエリーは outer_expr = inner_expr となるような行を生成しないものとします。その場合、outer_expr IN (SELECT ...) は次のように評価されます。

  • inner_exprNULL である行を SELECT が生成する場合は NULL

  • SELECTNULL 以外の値のみを生成するかまたは何も生成しない場合は FALSE

この状況では、outer_expr = inner_expr である行を探すアプローチは有効でなくなります。そのような行を探すことは必要ですが、何も見つからない場合には、inner_exprNULL となる行も探します。大ざっぱに言うと、サブクエリーは次のように変換できます。

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
        (outer_expr=inner_expr OR inner_expr IS NULL))

追加の IS NULL 条件を評価する必要性は、MySQL に ref_or_null アクセスメソッドがある理由です。

mysql> EXPLAIN
    -> SELECT outer_expr IN (SELECT t2.maybe_null_key
    ->                       FROM t2, t3 WHERE ...)
    -> FROM t1;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: ref_or_null
possible_keys: maybe_null_key
          key: maybe_null_key
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Using index
...

unique_subquery および index_subquery サブクエリー固有のアクセスメソッドには or NULL バリアントもあります。ただし、それらは EXPLAIN の出力に表示されないため、EXPLAIN EXTENDED のあとに SHOW WARNINGS を付けて使用する必要があります (警告メッセージの checking NULL に注意してください)。

mysql> EXPLAIN EXTENDED
    -> SELECT outer_expr IN (SELECT maybe_null_key FROM t2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: index_subquery
possible_keys: maybe_null_key
          key: maybe_null_key
      key_len: 5
          ref: func
         rows: 2
        Extra: Using index

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select (`test`.`t1`.`outer_expr`,
         (((`test`.`t1`.`outer_expr`) in t2 on
         maybe_null_key checking NULL))) AS `outer_expr IN (SELECT
         maybe_null_key FROM t2)` from `test`.`t1`

追加の OR ... IS NULL 条件によってクエリーの実行は多少複雑になり、サブクエリー内の最適化の一部も適用できなくなりますが、通常これは許容できます。

outer_exprNULL になる可能性がある場合、状況ははるかに悪くなります。不明な値としての NULL の SQL の解釈によると、NULL IN (SELECT inner_expr ...) は次のように評価されるはずです。

  • SELECT が何らかの行を生成する場合は NULL

  • SELECT が行を生成しない場合は FALSE

正しい評価には、SELECT がとにかく何らかの行を生成したかどうかを確認できるようにする必要があるため、outer_expr = inner_expr をサブクエリーにプッシュダウンすることはできません。等式をプッシュダウンできないと、多くの実際のサブクエリーが非常に遅くなるため、これは問題になります。

基本的に、outer_expr の値に応じて、サブクエリーを実行するさまざまな方法が存在する必要があります。

オプティマイザは速度よりも SQL 準拠を選択するため、outer_exprNULL になる可能性を考慮します。

outer_exprNULL の場合、次の式を評価するには、SELECT を実行して何らかの行が生成されるかどうかを判断する必要があります。

NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)

ここで、先述の種類のプッシュダウンされた等式を使用せずに、元の SELECT を実行する必要があります。

一方、outer_exprNULL でない場合、次の比較が絶対に必要です:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

この比較をプッシュダウンされた条件を使用する式に変換

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

この変換を行わないと、サブクエリーが遅くなります。条件をサブクエリーにプッシュダウンするかどうかのジレンマを解決するには、条件をトリガー関数にラップします。したがって、次の形式の式は:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

次のように変換されます。

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(outer_expr=inner_expr))

より一般的には、サブクエリーの比較が外側の式と内側の式の複数のペアに基づく場合、変換は次の比較をします。

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

さらに、それを次の式に変換します。

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(oe_1=ie_1)
                          AND ...
                          AND trigcond(oe_N=ie_N)
       )

trigcond(X) は、次の値に評価される特殊な関数です。

  • リンクされた外側の式 oe_iNULL でない場合は X

  • リンクされた外側の式 oe_iNULL の場合は TRUE

トリガー関数は、CREATE TRIGGER で作成する種類のトリガーではありません

trigcond() 関数にラップされた等式は、クエリーオプティマイザにとって最高の述語ではありません。ほとんどの最適化では、クエリーの実行時にオンまたはオフになる可能性のある述語を処理できないため、trigcond(X) をすべて不明な関数であるとみなし、無視します。現時点では、トリガー等式は次の最適化で使用できます。

  • 参照の最適化: trigcond(X=Y [OR Y IS NULL]) を使用して、refeq_ref、または ref_or_null テーブルアクセスを構築できます。

  • インデックスルックアップベースのサブクエリー実行エンジン: trigcond(X=Y) を使用して、unique_subquery または index_subquery アクセスを構築できます。

  • テーブル条件ジェネレータ: サブクエリーが複数のテーブルの結合である場合、トリガー条件は可能なかぎり早く確認されます。

オプティマイザがトリガー条件を使用して、何らかの種類のインデックスルックアップベースのアクセスを作成する場合 (上記リストの最初の 2 項目に関して)、条件がオフである場合のフォールバック戦略が必要です。このフォールバック戦略は常に同じで、フルテーブルスキャンを実行します。EXPLAIN の出力で、フォールバックは Extra カラムに Full scan on NULL key と表示されます。

mysql> EXPLAIN SELECT t1.col1,
    -> t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
        ...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: index_subquery
possible_keys: key1
          key: key1
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Full scan on NULL key

EXPLAIN EXTENDED に続いて SHOW WARNINGS を実行すると、トリガー条件を確認できます。

*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
         <in_optimizer>(`test`.`t1`.`col1`,
         <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
         on key1 checking NULL
         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
         trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
         from `test`.`t1`

トリガー条件を使用すると、パフォーマンスに多少の影響があります。現在 NULL IN (SELECT ...) 式では、以前に実行されなかった (遅い) フルテーブルスキャンが行われる可能性があります。これは正しい結果を得るための代価です (トリガー条件戦略の目的は、速度ではなく適合性を向上させることでした)。

複数テーブルサブクエリーでは、外側の式が NULL である場合に、結合オプティマイザが最適化を行わないため、NULL IN (SELECT ...) の実行が特に遅くなります。それは、左辺が NULL の場合のサブクエリーの評価はめったにないものと想定しています (そうでないことを示す統計があっても)。一方、外側の式が NULL になる可能性があっても実際にそうなることがない場合、パフォーマンスの低下はありません。

クエリーオプティマイザでクエリーがより適切に実行されるようにするには、次のヒントを使用してください。

  • カラムが実際に NOT NULL である場合は、そのように宣言します。(これにより、カラムの条件テストを簡単にすることで、オプティマイザのほかの側面にも役立ちます。)

  • NULLFALSE サブクエリー結果を区別する必要がない場合、遅い実行パスを簡単に回避できます。次のような比較を置き換えます。

    outer_expr IN (SELECT inner_expr FROM ...)

    次の式で:

    (outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))

    これにより、MySQL は式の結果が明確になるとただちに AND 部分の評価を停止するため、NULL IN (SELECT ...) が評価されることはなくなります。

subquery_materialization_cost_based により、サブクエリー実体化と IN -> EXISTS サブクエリー変換の選択を制御できます。セクション8.8.5.2「切り替え可能な最適化の制御」を参照してください。


User Comments
Sign Up Login You must be logged in to post a comment.