MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.9.2 切り替え可能な最適化

MySQL 8.0 リファレンスマニュアル / ... / 切り替え可能な最適化

8.9.2 切り替え可能な最適化

optimizer_switch システム変数を使用するとオプティマイザの動作を制御できます。その値はフラグのセットで、それぞれ対応するオプティマイザの動作を有効にするかまたは無効にするかを示す on または off の値を持ちます。この変数はグローバル値およびセッション値を持ち、実行時に変更できます。グローバル値のデフォルトはサーバーの起動時に設定できます。

オプティマイザの現在のフラグセットを表示するには、変数値を選択します。

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on,firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on
1 row in set (0.00 sec)

optimizer_switch の値を変更するには、1 つ以上のコマンドのカンマ区切りのリストから構成される値を割り当てます。

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

各 command 値は、次の表に示すいずれかの形式になるようにしてください。

コマンドの構文	意味
`default`	すべての最適化をそのデフォルト値にリセットします
`opt_name=default`	指定した最適化をそのデフォルト値に設定します
`opt_name=off`	指定した最適化を無効にします
`opt_name=on`	指定した最適化を有効にします

default コマンドが存在する場合最初に実行されますが、値の中のコマンドの順序は問題ではありません。 opt_name フラグを default に設定すると、そのデフォルト値が on または off のどちらであってもそれに設定されます。値に特定の opt_name を複数回指定することは許可されず、エラーが発生します。値のエラーによって、割り当てがエラーを伴って失敗し、optimizer_switch の値が変更されないままになります。

次のリストは、最適化戦略別にグループ化された、許可される opt_name フラグ名を示しています:

バッチキーアクセスフラグ
- batched_key_access (default off)
  
  BKA 結合アルゴリズムの使用を制御します
batched_key_access が on に設定されている場合に何らかの効果を持つためには、mrr フラグも on である必要があります。現在、MRR のコスト見積もりはきわめて悲観的です。したがって、BKA を使用するには、mrr_cost_based を off にする必要もあります。

詳細は、セクション8.2.1.12「Block Nested Loop 結合と Batched Key Access 結合」を参照してください。
ブロックネストループフラグ
- block_nested_loop (default on)
  
  BNL 結合アルゴリズムの使用を制御します MySQL 8.0.18 以降では、BNL および NO_BNL オプティマイザヒントと同様に、ハッシュ結合の使用も制御します。 MySQL 8.0.20 以降では、ブロックネストループのサポートは MySQL サーバーから削除され、このフラグはハッシュ結合の使用のみを制御します。
詳細は、セクション8.2.1.12「Block Nested Loop 結合と Batched Key Access 結合」を参照してください。
条件フィルタリングフラグ
- condition_fanout_filter (default on)
  
  条件フィルタリングの使用を制御します。
詳細は、セクション8.2.1.13「条件フィルタ」を参照してください。
導出条件プッシュダウンフラグ
- derived_condition_pushdown (default on)
  
  導出条件プッシュダウンを制御します。
詳細は、セクション8.2.2.5「導出条件プッシュダウン最適化」を参照してください
導出テーブルマージフラグ
- derived_merge (default on)
  
  導出テーブルおよびビューの外部クエリーブロックへのマージを制御します。
derived_merge フラグは、オプティマイザが導出テーブル、ビュー参照および共通テーブル式を外部クエリーブロックにマージしようとするかどうかを制御します。ただし、他のルールがマージを妨げることはないと想定しています。たとえば、ビューの ALGORITHM ディレクティブが derived_merge 設定より優先されます。デフォルトでは、マージを有効にするフラグは on です。

詳細は、セクション8.2.2.4「マージまたは実体化を使用した導出テーブル、ビュー参照および共通テーブル式の最適化」を参照してください。
エンジン条件プッシュダウンフラグ
- engine_condition_pushdown (default on)
  
  エンジンコンディションプッシュダウンを制御します
詳細は、セクション8.2.1.5「エンジンコンディションプッシュダウンの最適化」を参照してください。
ハッシュ結合フラグ
- hash_join (default on)
  
  ハッシュ結合を制御します (MySQL 8.0.18 のみ。MySQL 8.0.19 以降では影響しません)。
詳細は、セクション8.2.1.4「ハッシュ結合の最適化」を参照してください。
インデックス条件プッシュダウンフラグ
- index_condition_pushdown (default on)
  
  インデックスコンディションプッシュダウンを制御します
詳細は、セクション8.2.1.6「インデックスコンディションプッシュダウンの最適化」を参照してください。
インデックス拡張フラグ
- use_index_extensions (default on)
  
  インデックス拡張の使用を制御します
詳細は、セクション8.3.10「インデックス拡張の使用」を参照してください。
インデックスマージフラグ
- index_merge (default on)
  
  すべてのインデックスマージ最適化を制御します
- index_merge_intersection (default on)
  
  インデックスマージ共通集合アクセス最適化を制御します
- index_merge_sort_union (default on)
  
  インデックスマージソート和集合アクセス最適化を制御します
- index_merge_union (default on)
  
  インデックスマージ和集合アクセス最適化を制御します
詳細については、セクション8.2.1.3「インデックスマージの最適化」を参照してください。
インデックス可視性フラグ
- use_invisible_indexes (default off)
  
  不可視インデックスの使用を制御します。
詳細は、セクション8.3.12「不可視のインデックス」を参照してください。
制限最適化フラグ
- prefer_ordering_index (default on)
  
  LIMIT 句を含む ORDER BY または GROUP BY を持つクエリーの場合に、オプティマイザが順序付けされていないインデックス、filesort またはその他の最適化のかわりに順序付けられたインデックスを使用しようとするかどうかを制御します。この最適化は、オプティマイザがこの最適化を使用するとクエリーの実行速度が速くなると判断するたびに、デフォルトで実行されます。
  
  この決定を行うアルゴリズムではすべての同義のケースを処理できないため (データの分散が常に均一であるか、それほど均一でないことが前提となります)、この最適化が望ましくない場合があります。 MySQL 8.0.21 より前は、この最適化を無効にすることはできませんが、MySQL 8.0.21 以降ではデフォルトの動作のままですが、prefer_ordering_index フラグを off に設定することで無効にできます。
詳細および例については、セクション8.2.1.19「LIMIT クエリーの最適化」を参照してください。
複数範囲検針フラグ
- mrr (default on)
  
  Multi-Range Read 戦略を制御します
- mrr_cost_based (default on)
  
  mrr=on の場合にコストベースの MRR の使用を制御します
詳細は、セクション8.2.1.11「Multi-Range Read の最適化」を参照してください。
準結合フラグ
- duplicateweedout (default on)
  
  準結合重複除去ストラテジを制御します。
- firstmatch (default on)
  
  準結合 FirstMatch 戦略を制御します。
- loosescan (default on)
  
  準結合 LooseScan 戦略を制御します (Loose Index Scan for GROUP BY と混同しないでください)。
- semijoin (default on)
  
  すべての準結合方針を制御します。
  
  MySQL 8.0.17 以降では、これはアンチ結合の最適化にも適用されます。
semijoin, firstmatch, loosescan および duplicateweedout フラグを使用すると、準結合戦略を制御できます。 semijoin フラグは、準結合を使用するかどうかを制御します。 on に設定されている場合、firstmatch および loosescan フラグを使用すると、許可された準結合戦略をより細かく制御できます。

duplicateweedout 準結合戦略が無効になっている場合は、他のすべての適用可能な戦略も無効にしないかぎり、使用されません。

semijoin と materialization の両方が on の場合、準結合でも実体化が使用されます (該当する場合)。これらのフラグはデフォルトで on です。

詳細は、セクション8.2.2.1「準結合変換による IN および EXISTS サブクエリー述語の最適化」を参照してください。
スキャンフラグのスキップ
- skip_scan (default on)
  
  スキップスキャンアクセス方法の使用を制御します。
詳細は、スキャン範囲アクセス方法のスキップを参照してください。
サブクエリー実体化フラグ
- materialization (default on)
  
  実体化 (準結合実体化を含む) を制御します。
- subquery_materialization_cost_based (default on)
  
  原価ベースの実体化の選択を使用します。
materialization フラグはサブクエリー実体化を使用するかどうかを制御します。 semijoin と materialization の両方が on の場合、準結合でも実体化が使用されます (該当する場合)。これらのフラグはデフォルトで on です。

subquery_materialization_cost_based フラグを使用すると、サブクエリー実体化と IN から EXISTS へのサブクエリー変換の選択を制御できます。フラグが on (デフォルト) の場合、オプティマイザはサブクエリー実体化と IN- から -EXISTS サブクエリー変換 (いずれかの方法を使用できる場合) の間でコストベースの選択を実行します。フラグが off の場合、オプティマイザは IN から EXISTS へのサブクエリー変換よりもサブクエリーの実体化を選択します。

詳細は、セクション8.2.2「サブクエリー、導出テーブル、ビュー参照および共通テーブル式の最適化」を参照してください。

サブクエリー変換フラグ

subquery_to_derived (default off)

MySQL 8.0.21 以降、オプティマイザは多くの場合、SELECT, WHERE, JOIN または HAVING 句のスカラーサブクエリーを導出テーブルの左外部結合に変換できます。 (導出テーブルの NULL 値可能性によっては、内部結合にさらに簡略化される場合があります。) これは、次の条件を満たすサブクエリーに対して実行できます:

サブクエリーでは、RAND() などの非決定的関数は使用されません。
サブクエリーは、MIN() または MAX() を使用するようにリライトできる ANY または ALL サブクエリーではありません。
親クエリーはユーザー変数を設定しません。リライトすると実行順序に影響する可能性があるため、同じクエリーで変数に複数回アクセスすると、予期しない結果が発生する可能性があります。
サブクエリーは相関付けしないでください。つまり、外部クエリーのテーブルからカラムを参照したり、外部クエリーで評価される集計を含むことはできません。

MySQL 8.0.22 より前は、サブクエリーに GROUP BY 句を含めることはできませんでした。

この最適化は、GROUP BY を含まない IN, NOT IN, EXISTS または NOT EXISTS の引数であるテーブルサブクエリーにも適用できます。

このフラグのデフォルト値は off ですが、ほとんどの場合、この最適化を有効にしてもパフォーマンスが著しく向上することはありません (多くの場合、クエリーの実行速度が遅くなることもあります)。ただし、subquery_to_derived フラグを on に設定することで最適化を有効にできます。主にテストで使用することを目的としています。

スカラーサブクエリーを使用する例:

d
mysql> CREATE TABLE t1(a INT);

mysql> CREATE TABLE t2(a INT);

mysql> INSERT INTO t1 VALUES ROW(1), ROW(2), ROW(3), ROW(4);

mysql> INSERT INTO t2 VALUES ROW(1), ROW(2);

mysql> SELECT * FROM t1
    ->     WHERE t1.a > (SELECT COUNT(a) FROM t2);
+------+
| a    |
+------+
|    3 |
|    4 |
+------+

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
+-----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL

mysql> SET @@optimizer_switch='subquery_to_derived=on';


mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
+-----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=on%';
+----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=on%' |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where; Using join buffer (hash join)
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL

2 番目の EXPLAIN ステートメントの直後に SHOW WARNINGS を実行するとわかるように、最適化を有効にすると、クエリー SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2) は次に示すような形式でリライトされます:

SELECT t1.a FROM t1
    JOIN  ( SELECT COUNT(t2.a) AS c FROM t2 ) AS d
            WHERE t1.a > d.c;

IN (subquery) でクエリーを使用する例:

mysql> DROP TABLE IF EXISTS t1, t2;

mysql> CREATE TABLE t1 (a INT, b INT);
mysql> CREATE TABLE t2 (a INT, b INT);

mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);
mysql> INSERT INTO t2
    ->    VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130);

mysql> SELECT * FROM t1
    ->     WHERE   t1.b < 0
    ->             OR
    ->             t1.a IN (SELECT t2.a + 1 FROM t2);
+------+------+
| a    | b    |
+------+------+
|    2 |   20 |
|    3 |   30 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where

mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 9
          ref: std2.t1.a
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

このクエリーで EXPLAIN を実行した後の SHOW WARNINGS の結果のチェックおよび簡略化は、subquery_to_derived フラグが有効になっている場合、SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2) が次に示すような形式でリライトされることを示しています:

SELECT a, b FROM t1
    LEFT JOIN (SELECT DISTINCT a + 1 AS e FROM t2) d
    ON t1.a = d.e
    WHERE   t1.b < 0
            OR
            d.e IS NOT NULL;

例:EXISTS (subquery) と前述の例と同じテーブルおよびデータを使用したクエリーを使用します:

mysql> SELECT * FROM t1
    ->     WHERE   t1.b < 0
    ->             OR
    ->             EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1);
+------+------+
| a    | b    |
+------+------+
|    1 |   10 |
|    2 |   20 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where

mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

subquery_to_derived が有効になっているときに、クエリー SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1) で EXPLAIN を実行した後に SHOW WARNINGS を実行し、結果の 2 行目を簡略化すると、次のような形式でリライトされていることがわかります:

SELECT a, b FROM t1
LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d
ON t1.a + 1 = d.e2
WHERE   t1.b < 0
        OR
        d.e1 IS NOT NULL;

詳細は、セクション8.2.2.4「マージまたは実体化を使用した導出テーブル、ビュー参照および共通テーブル式の最適化」、セクション8.2.1.19「LIMIT クエリーの最適化」およびセクション8.2.2.1「準結合変換による IN および EXISTS サブクエリー述語の最適化」を参照してください。

optimizer_switch に値を割り当てると、指定されていないフラグはそれらの現在の値を維持します。これにより、ほかの動作に影響を与えることなく、単一のステートメントで特定のオプティマイザの動作を有効または無効にできます。ステートメントは、ほかの存在するオプティマイザフラグやそれらの値に依存しません。すべてのインデックスマージ最適化が有効になっているとします。

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on, firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on

サーバーが特定のクエリーに対して Index Merge Union または Index Merge Sort-Union アクセス方法を使用しており、オプティマイザがそれらなしでパフォーマンスを向上できるかどうかを確認する場合は、次のように変数値を設定します:

mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=off,
                    index_merge_sort_union=off,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on, firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on

関連キーワード: 　key, 結合, フラグ, 制御, インデックス, off, FROM, type, derived, default

PREV HOME UP NEXT