MySQL 8.0 リファレンスマニュアル MySQL NDB Cluster 8.0 を含む
このページは機械翻訳したものです。
特定の最適化は、IN
(または =ANY
) 演算子を使用してサブクエリーの結果をテストする比較に適用できます。 このセクションでは、これらの最適化について、特に NULL
値が存在する課題に関して説明します。 この説明の最後の部分では、オプティマイザの支援方法を示します。
次のようなサブクエリーの比較を考慮します。
outer_expr
IN (SELECTinner_expr
FROM ... WHEREsubquery_where
)
MySQL は「外側から内側に」クエリーを評価します。 つまり、まず外側の式 outer_expr
の値を取得してから、サブクエリーを実行し、それによって生成される行を取得します。
内側の式 inner_expr
が outer_expr
と等しい行だけが目的の行であることをサブクエリーに「通知する」ことは、かなり役に立つ最適化です。 これを行うには、サブクエリーの WHERE
句に適切な等価をプッシュダウンして、より限定的にします。 変換された比較は次のようになります:
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
ANDouter_expr
=inner_expr
)
変換後、MySQL はプッシュダウンされた等価を使用して、サブクエリーを評価するために調査する必要がある行数を制限できます。
より一般的には、N
個の値と N
値の行を返すサブクエリーとの比較は、同じ変換の対象になります。 oe_i
と ie_i
が対応する外側と内側の式の値を表す場合、次のサブクエリー比較は:
(oe_1
, ...,oe_N
) IN (SELECTie_1
, ...,ie_N
FROM ... WHEREsubquery_where
)
次のようになります。
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
ANDoe_1
=ie_1
AND ... ANDoe_N
=ie_N
)
簡単にするために、次の説明では、外部式と内部式の値の単一のペアを想定しています。
前述の「「プッシュダウン」」戦略は、次のいずれかの条件に該当する場合に機能します:
outer_expr
と inner_expr
は NULL
にできません。
NULL
と FALSE
サブクエリーの結果を区別する必要はありません。 サブクエリーが WHERE
句の OR
式または AND
式の一部である場合、MySQL では考慮されないものとみなされます。 オプティマイザが NULL
と FALSE
サブクエリーの結果を区別する必要がないことに気付いた別のインスタンスは、次の構成です:
... WHEREouter_expr
IN (subquery
)
この場合、IN (
が subquery
)NULL
または FALSE
を返すかどうかにかかわらず、WHERE
句は行を拒否します。
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 ... WHEREsubquery_where
AND (outer_expr
=inner_expr
ORinner_expr
IS NULL))
追加の IS NULL
条件を評価する必要性は、MySQL に ref_or_null
アクセスメソッドがある理由です。
mysql>EXPLAIN
SELECT
outer_expr
IN (SELECT t2.maybe_null_keyFROM 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
」 バリアントもあります。
追加の OR ... IS NULL
条件によってクエリーの実行は多少複雑になり、サブクエリー内の最適化の一部も適用できなくなりますが、通常これは許容できます。
outer_expr
が NULL
になる可能性がある場合、状況ははるかに悪くなります。 「不明な値」としての NULL
の SQL の解釈によると、NULL IN (SELECT
は次のように評価されるはずです。
inner_expr
...)
SELECT
が何らかの行を生成する場合は NULL
SELECT
が行を生成しない場合は FALSE
正しい評価には、SELECT
がとにかく何らかの行を生成したかどうかを確認できるようにする必要があるため、
をサブクエリーにプッシュダウンすることはできません。 等価をプッシュダウンできないかぎり、多くの実世界のサブクエリーが非常に遅くなるため、これは問題です。
outer_expr
= inner_expr
基本的に、outer_expr
の値に応じて、サブクエリーを実行するさまざまな方法が存在する必要があります。
オプティマイザは速度よりも SQL 準拠を選択するため、outer_expr
が NULL
である可能性があります:
outer_expr
が NULL
の場合、次の式を評価するには、SELECT
を実行して行を生成するかどうかを判断する必要があります:
NULL IN (SELECTinner_expr
FROM ... WHEREsubquery_where
)
前述の種類と同等にプッシュダウンせずに、ここで元の SELECT
を実行する必要があります。
一方、outer_expr
が NULL
でない場合、次の比較が絶対に必要です:
outer_expr
IN (SELECTinner_expr
FROM ... WHEREsubquery_where
)
プッシュダウン条件を使用する次の式に変換する必要があります:
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
ANDouter_expr
=inner_expr
)
この変換を行わないと、サブクエリーは遅くなります。
条件をサブクエリーにプッシュダウンするかどうかの問題を解決するために、条件は 「trigger」 関数内にラップされます。 したがって、次の形式の式は:
outer_expr
IN (SELECTinner_expr
FROM ... WHEREsubquery_where
)
次に変換されます:
EXISTS (SELECT 1 FROM ... WHEREsubquery_where
AND trigcond(outer_expr
=inner_expr
))
より一般的には、サブクエリーの比較が外側の式と内側の式の複数のペアに基づく場合、変換は次の比較をします。
(oe_1
, ...,oe_N
) IN (SELECTie_1
, ...,ie_N
FROM ... WHEREsubquery_where
)
これを次の式に変換します:
EXISTS (SELECT 1 FROM ... WHEREsubquery_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
の出力で、フォールバックは 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
の後に 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
[NOT] IN (SELECTinner_expr
FROM ...)
次の式で:
(outer_expr
IS NOT NULL) AND (outer_expr
[NOT] IN (SELECTinner_expr
FROM ...))
式の結果が明らかになるとすぐに MySQL が AND
部分の評価を停止するため、NULL IN (SELECT ...)
は評価されません。
別のリライトも可能です:
[NOT] EXISTS (SELECTinner_expr
FROM ... WHEREinner_expr
=outer_expr
)
optimizer_switch
システム変数の subquery_materialization_cost_based
フラグを使用すると、サブクエリー実体化と IN
から EXISTS
へのサブクエリー変換の選択を制御できます。 セクション8.9.2「切り替え可能な最適化」を参照してください。