batched_key_access (default off)

BKA 結合アルゴリズムの使用を制御します

block_nested_loop (default on)

BNL 結合アルゴリズムの使用を制御します MySQL 8.0.18 以降では、BNL および NO_BNL オプティマイザヒントと同様に、ハッシュ結合の使用も制御します。 MySQL 8.0.20 以降では、ブロックネストループのサポートは MySQL サーバーから削除され、このフラグはハッシュ結合の使用のみを制御します。

condition_fanout_filter (default on)

条件フィルタリングの使用を制御します。

derived_condition_pushdown (default on)

導出条件プッシュダウンを制御します。

derived_merge (default on)

導出テーブルおよびビューの外部クエリーブロックへのマージを制御します。

engine_condition_pushdown (default on)

エンジンコンディションプッシュダウンを制御します

hash_join (default on)

ハッシュ結合を制御します (MySQL 8.0.18 のみ。MySQL 8.0.19 以降では影響しません)。

index_condition_pushdown (default on)

インデックスコンディションプッシュダウンを制御します

use_index_extensions (default on)

インデックス拡張の使用を制御します

index_merge (default on)

すべてのインデックスマージ最適化を制御します

index_merge_intersection (default on)

インデックスマージ共通集合アクセス最適化を制御します

index_merge_sort_union (default on)

インデックスマージソート和集合アクセス最適化を制御します

index_merge_union (default on)

インデックスマージ和集合アクセス最適化を制御します

use_invisible_indexes (default off)

不可視インデックスの使用を制御します。

prefer_ordering_index (default on)

LIMIT 句を含む ORDER BY または GROUP BY を持つクエリーの場合に、オプティマイザが順序付けされていないインデックス、filesort またはその他の最適化のかわりに順序付けられたインデックスを使用しようとするかどうかを制御します。 この最適化は、オプティマイザがこの最適化を使用するとクエリーの実行速度が速くなると判断するたびに、デフォルトで実行されます。

この決定を行うアルゴリズムではすべての同義のケースを処理できないため (データの分散が常に均一であるか、それほど均一でないことが前提となります)、この最適化が望ましくない場合があります。 MySQL 8.0.21 より前は、この最適化を無効にすることはできませんが、MySQL 8.0.21 以降ではデフォルトの動作のままですが、prefer_ordering_index フラグを off に設定することで無効にできます。

mrr (default on)

Multi-Range Read 戦略を制御します

mrr_cost_based (default on)

mrr=on の場合にコストベースの MRR の使用を制御します

duplicateweedout (default on)

準結合重複除去ストラテジを制御します。

firstmatch (default on)

準結合 FirstMatch 戦略を制御します。

loosescan (default on)

準結合 LooseScan 戦略を制御します (Loose Index Scan for GROUP BY と混同しないでください)。

semijoin (default on)

すべての準結合方針を制御します。

MySQL 8.0.17 以降では、これはアンチ結合の最適化にも適用されます。

skip_scan (default on)

スキップスキャンアクセス方法の使用を制御します。

materialization (default on)

実体化 (準結合実体化を含む) を制御します。

subquery_materialization_cost_based (default on)

原価ベースの実体化の選択を使用します。

subquery_to_derived (default off)

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

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 サブクエリー述語の最適化」 を参照してください。