MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ)

MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 4 最適化 :: 4.2 SELECT およびその他のステートメントの最適化 :: 4.2.16 IN/=ANY サブクエリーの最適化

Section Navigation [Toggle]

4.2.16. `IN`/`=ANY` サブクエリーの最適化

特定の最適化は、IN 演算子 (または、同等の =ANY) を使用してサブクエリーの結果をテストする比較に適用できます。この節では、これらの最適化について、特に NULL 値の問題に関連して説明します。オプティマイザを支援するための推奨事項は、この説明の最後に示します。

次のようなサブクエリー比較について考えます。

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

MySQL では、クエリーは「外側から内側へ」評価されます。つまり、外側の式 outer_expr の値が最初に取得され、次にサブクエリーが実行されてその結果の行が取り込まれます。

内側の式 inner_expr が outer_expr に等しくなる行だけに興味があることをサブクエリーに「知らせる」ことは、非常に役立つ最適化です。そのためには、適切な等式をサブクエリーの WHERE 節にプッシュダウンします。つまり、この比較は次のように変換されます。

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

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

より一般的には、N 値の行を返すサブクエリーと N 個の値を比較する場合、同じ変換の対象になります。oe_i が外側の式の値、ie_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 つの条件が両方とも成立する場合にかぎり機能します。

outer_expr と inner_expr は NULL になることがない。
サブクエリーの結果の NULL と FALSE を区別する必要がない。(サブクエリーが WHERE 節内の OR 式または AND 式の一部である場合、MySQL はその区別は不要と想定します。)

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

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

inner_expr が NULL となる何らかの行が SELECT で生成される場合は NULL
SELECT で NULL 以外の値だけが生成される場合または何も生成されない場合は FALSE

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

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_expr が NULL になる可能性がある場合、状況は非常に悪くなります。SQL では NULL は「不明な値」と解釈されるため、NULL IN (SELECT inner_expr ...) は次のように評価されます。

SELECT で何らかの行が生成される場合は NULL
SELECT で行が生成されない場合は FALSE

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

基本的には、outer_expr の値に応じて異なる方法でサブクエリーを実行できます。5.1.16 より前の MySQL 5.1 では、オプティマイザは結果の NULL と FALSE を区別することよりも速度を重視したため、クエリーによっては、NULL ではなく FALSE が返されることがありました。

MySQL 5.1.16 以降では、オプティマイザは速度よりも SQL に準拠することを重視して、outer_expr が NULL になる可能性を考慮します。

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

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

ここでは、前述のような等式のプッシュダウンは使用せず、元の SELECT を実行する必要があります。

反対に、outer_expr が NULL でない場合、次の比較は

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_i が NULL でない場合は X
「リンクされた」外側の式 oe_i が NULL の場合は TRUE

トリガー関数は、CREATE TRIGGER で作成される種類のトリガーではないことに注意してください。

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

参照の最適化: trigcond(X=Y [OR Y IS NULL]) を使用して、ref、eq_ref、または ref_or_null テーブルアクセスを作成できます。
インデックス検索ベースのサブクエリー実行エンジン: trigcond(X=Y) を使用して、unique_subquery または index_subquery アクセスを作成できます。
テーブル条件ジェネレータ: 複数のテーブルを結合するサブクエリーの場合、トリガー条件はできるだけ早く確認されます。

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

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 の場合は、カラムをそのように宣言する必要があります。(これはオプティマイザのほかの面でも役立ちます。)
サブクエリーの結果の NULL と FALSE を区別する必要がない場合は、低速な実行パスを簡単に回避できます。次のような比較を
```
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 ...) が評価されることはなくなります。

Top / Previous / Next / Up / Table of Contents

4.2.16. IN/=ANY サブクエリーの最適化

4.2.16. `IN`/`=ANY` サブクエリーの最適化