MySQL :: MySQL 8.4 參考手冊 :: 10.9.2 可切換最佳化

版本 8.4

8.0 日語

MySQL 8.4 參考手冊 / ... / 可切換最佳化

10.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,hash_set_operations=on
1 row in set (0.00 sec)

若要變更 optimizer_switch 的值，請指派一個值，其中包含一個或多個以逗號分隔的命令清單

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（預設為 off）
  控制 BKA 聯結演算法的使用。
當設定 batched_key_access 為 on 時，若要使其生效，mrr 旗標也必須為 on。目前，MRR 的成本估算過於保守。因此，若要使用 BKA，mrr_cost_based 也必須為 off。
更多資訊，請參閱第 10.2.1.12 節，「區塊巢狀迴圈和批次索引鍵存取聯結」。
區塊巢狀迴圈旗標
- block_nested_loop (預設為 on)
  控制雜湊聯結的使用，如同 BNL 和 NO_BNL 最佳化提示。
更多資訊，請參閱第 10.2.1.12 節，「區塊巢狀迴圈和批次索引鍵存取聯結」。
條件篩選旗標
- condition_fanout_filter (預設為 on)
  控制條件篩選的使用。
更多資訊，請參閱第 10.2.1.13 節，「條件篩選」。
衍生條件下推旗標
- derived_condition_pushdown (預設為 on)
  控制衍生條件下推。
更多資訊，請參閱第 10.2.2.5 節，「衍生條件下推最佳化」
衍生資料表合併旗標
- derived_merge (預設為 on)
  控制將衍生資料表和檢視合併到外部查詢區塊。
假設沒有其他規則阻止合併，例如檢視的 ALGORITHM 指示優先於 derived_merge 設定，derived_merge 旗標會控制最佳化器是否嘗試將衍生資料表、檢視參照和通用資料表運算式合併到外部查詢區塊。預設情況下，此旗標為 on 以啟用合併。
更多資訊，請參閱第 10.2.2.4 節，「使用合併或實體化最佳化衍生資料表、檢視參照和通用資料表運算式」。
引擎條件下推旗標
- engine_condition_pushdown (預設為 on)
  控制引擎條件下推。
更多資訊，請參閱第 10.2.1.5 節，「引擎條件下推最佳化」。
雜湊聯結旗標
- hash_join (預設為 on)
  在 MySQL 8.4 中沒有作用。請改用 block_nested_loop 旗標。
更多資訊，請參閱第 10.2.1.4 節，「雜湊聯結最佳化」。
索引條件下推旗標
- index_condition_pushdown (預設為 on)
  控制索引條件下推。
更多資訊，請參閱第 10.2.1.6 節，「索引條件下推最佳化」。
索引擴充旗標
- use_index_extensions (預設為 on)
  控制索引擴充的使用。
更多資訊，請參閱第 10.3.10 節，「索引擴充的使用」。
索引合併旗標
- index_merge (預設為 on)
  控制所有索引合併最佳化。
- index_merge_intersection (預設為 on)
  控制索引合併交集存取最佳化。
- index_merge_sort_union (預設為 on)
  控制索引合併排序聯集存取最佳化。
- index_merge_union (預設為 on)
  控制索引合併聯集存取最佳化。
更多資訊，請參閱第 10.2.1.3 節，「索引合併最佳化」。
索引可見性旗標
- use_invisible_indexes (預設為 off)
  控制不可見索引的使用。
更多資訊，請參閱第 10.3.12 節，「不可見索引」。
限制最佳化旗標
- prefer_ordering_index (預設為 on)
  控制在查詢具有帶有 LIMIT 子句的 ORDER BY 或 GROUP BY 的情況下，最佳化器是否嘗試使用已排序索引，而不是未排序索引、檔案排序或其他一些最佳化。只要最佳化器確定使用此最佳化可以更快地執行查詢，預設就會執行此最佳化。
  由於做出此決定的演算法無法處理所有可能的情況（部分原因是假設資料分佈始終或多或少是均勻的），因此在某些情況下，此最佳化可能不適用。可以將 prefer_ordering_index 旗標設定為 off 來停用此最佳化。
更多資訊和範例，請參閱第 10.2.1.19 節，「LIMIT 查詢最佳化」。
多範圍讀取旗標
- mrr (預設為 on)
  控制多範圍讀取策略。
- mrr_cost_based (預設為 on)
  如果 mrr=on，則控制使用以成本為基礎的 MRR。
更多資訊，請參閱第 10.2.1.11 節，「多範圍讀取最佳化」。
半聯結旗標
- duplicateweedout (預設為 on)
  控制半聯結重複篩除策略。
- firstmatch (預設為 on)
  控制半聯結首個符合策略。
- loosescan (預設為 on)
  控制半聯結寬鬆掃描策略（不要與用於 GROUP BY 的寬鬆索引掃描混淆）。
- semijoin (預設為 on)
  控制所有半聯結策略。
  這也適用於反聯結最佳化。
semijoin、firstmatch、loosescan 和 duplicateweedout 旗標可讓您控制半聯結策略。semijoin 旗標控制是否使用半聯結。如果設定為 on，則 firstmatch 和 loosescan 旗標可以更精細地控制允許的半聯結策略。
如果停用了 duplicateweedout 半聯結策略，除非停用了所有其他適用的策略，否則不會使用它。
如果 semijoin 和 materialization 都為 on，則半聯結也會在適用的情況下使用實體化。這些旗標預設為 on。
更多資訊，請參閱使用半聯結轉換來最佳化 IN 和 EXISTS 子查詢述詞。
集合運算旗標
- hash_set_operations (預設為 on)
  為涉及 EXCEPT 和 INTERSECT 的集合運算啟用雜湊表最佳化；預設為啟用。否則，會使用基於暫存資料表的重複資料刪除，如同在先前的 MySQL 版本中一樣。
  此最佳化用於雜湊的記憶體量可以使用 set_operations_buffer_size 系統變數來控制；增加此變數通常會加快使用這些運算的陳述式的執行時間。
跳過掃描旗標
- skip_scan (預設為 on)
  控制跳過掃描存取方法的使用。
更多資訊，請參閱跳過掃描範圍存取方法。
子查詢實體化旗標
- materialization (預設為 on)
  控制實體化（包括半聯結實體化）。
- subquery_materialization_cost_based (預設為 on)
  使用以成本為基礎的實體化選擇。
materialization 旗標控制是否使用子查詢實體化。如果 semijoin 和 materialization 都設定為 on，則半連接 (semijoins) 也會在適用的情況下使用實體化。這些旗標預設為 on。
subquery_materialization_cost_based 旗標可控制子查詢實體化和 IN 轉換為 EXISTS 子查詢之間的選擇。如果該旗標為 on (預設值)，則最佳化器會在子查詢實體化和 IN 轉換為 EXISTS 子查詢之間進行成本考量的選擇，前提是兩種方法都適用。如果該旗標為 off，則最佳化器會選擇子查詢實體化而非 IN 轉換為 EXISTS 子查詢。
更多資訊，請參閱第 10.2.2 節，「最佳化子查詢、衍生表、視圖參考和通用表表達式」。

子查詢轉換旗標

subquery_to_derived (預設為 off)

在許多情況下，最佳化器能夠將 SELECT、WHERE、JOIN 或 HAVING 子句中的純量子查詢轉換為衍生表上的左外部聯接。(根據衍生表的可空性，有時可以進一步簡化為內部聯接。) 對於符合以下條件的子查詢，可以進行此操作

子查詢不使用任何非決定性函數，例如 RAND()。
子查詢不是可以使用 MIN() 或 MAX() 重寫的 ANY 或 ALL 子查詢。
父查詢未設定使用者變數，因為重寫可能會影響執行順序，如果同一個查詢中多次存取該變數，可能會導致非預期的結果。
子查詢不應是相關的，也就是說，它不應參考外部查詢中表格的欄位，或包含在外部查詢中評估的聚合函數。

此最佳化也可以應用於 IN、NOT IN、EXISTS 或 NOT EXISTS 的表格子查詢引數，且該子查詢不包含 GROUP BY。

此旗標的預設值為 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

從執行第二個 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 (子查詢) 的查詢

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 (子查詢) 的查詢，且表格與資料與前一個範例相同

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

如果我們在查詢 SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1) 上執行 EXPLAIN 後執行 SHOW WARNINGS，且 subquery_to_derived 已啟用，並簡化結果的第二列，我們會看到它已重寫為類似於此的形式

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;

當您將值指定給 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

如果伺服器對於某些查詢使用索引合併聯集或索引合併排序聯集存取方法，並且您想檢查最佳化器在沒有它們的情況下是否可以執行得更好，請像這樣設定變數值

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