碼到三十五 ： 個人主頁

---

在 MySQL 數據庫中，查詢優化器是一個至關重要的組件，它負責確定執行 SQL 查詢的最有效方法。為了提供DBA和開發者更多的靈活性和控制權，MySQL 引入了 optimizer_switch 系統變量。這個強大的工具允許用戶開啟或關閉特定的優化策略，從而可以根據具體的工作負載和數據分布調整查詢的執行計劃。

optimizer_switch 的概念

optimizer_switch 是一個由多個標志組成的字符串，每個標志控制一個特定的優化器行為。這些標志可以被設置為 on 或 off，以啟用或禁用相應的優化策略。通過調整這些標志，數據庫管理員可以精細地控制查詢優化器的行為，以達到最佳的性能表現。

ptimizer_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的值

要修改optimizer_switch的值，指定一個由一個或多個命令組成的逗號分隔的值:

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

每個命令值應該具有下表所示的形式之一：

該值中命令的順序并不重要，但如果存在，默認命令將首先執行。將opt_name標志設置為default將其設置為on或off中的任意一個為其默認值。不允許在值中多次指定任何給定的opt_name，這會導致錯誤。該值中的任何錯誤都會導致賦值失敗，并導致optimizer_switch的值保持不變。

主要優化標志介紹

1. index_merge

   index_merge 控制是否允許索引合并優化。當查詢條件可以通過多個索引來滿足時，MySQL 可以合并這些索引以更有效地檢索數據。在復雜查詢中，這可以顯著提高性能。

2. index_condition_pushdown (ICP)

   ICP 允許將 WHERE 子句中的條件推送到存儲引擎層進行處理。這減少了存儲引擎需要返回給優化器的數據量，因為它可以在檢索數據時就過濾掉不符合條件的行。

3. materialization

   當查詢包含子查詢時，materialization 標志控制是否將子查詢的結果物化（即臨時存儲）。物化子查詢可以減少重復計算，但也可能增加內存使用。

4. semijoin 和 loosescan

   這兩個標志與半連接優化相關。半連接是一種在處理包含 EXISTS 或 IN 子句的查詢時特別有效的優化策略。semijoin 控制是否使用這種優化，而 loosescan 則允許在某些情況下進行更高效的掃描。

5. derived_merge

   當查詢中包含派生表（由子查詢生成的臨時表）時，derived_merge 標志控制是否嘗試將這些派生表合并到外部查詢中。這可以減少查詢的復雜性并提高性能。

6. exists_to_in

   在某些情況下，將 EXISTS 子句轉換為 IN 子句可能會改變查詢的執行計劃并提高性能。exists_to_in 標志控制是否進行這種轉換。

7. mrr (Multi-Range Read)

   MRR 是一種優化技術，用于改善范圍查詢和JOIN操作的性能。當設置為on時，MySQL 會嘗試使用 MRR 來更有效地從磁盤讀取數據。這通常可以減少磁盤I/O，并提高查詢速度。

8. mrr_cost_based

   當此標志設置為on時，MySQL 將基于成本決定是否使用 MRR。如果查詢優化器認為使用 MRR 會更有效，那么它就會使用這種技術。否則，它將回退到傳統的讀取方法。

9. block_nested_loop

   這個標志控制是否使用塊嵌套循環連接（Block Nested Loop Join, BNLJ）。BNLJ 是一種在處理連接操作時減少I/O次數的方法。當設置為on時，MySQL 將考慮使用 BNLJ 來優化連接操作。

10. batched_key_access

當此標志啟用時，MySQL 會嘗試使用批處理鍵訪問（Batched Key Access, BKA）來優化某些類型的 JOIN 操作。BKA 可以減少在 JOIN 操作中訪問索引的次數，從而提高性能。

11. use_index_extensions

這個標志允許優化器使用索引擴展來優化某些類型的查詢。索引擴展是一種技術，其中優化器可以使用索引中的額外信息來過濾結果集，而無需回表查找數據行。

12. condition_fanout_filter

當此標志設置為on時，優化器將嘗試使用條件扇出過濾器（Condition Fanout Filter, CFF）來優化查詢。CFF 是一種在處理具有多個可能值的列時減少不必要行掃描的技術。

13. use_invisible_indexes

這個標志控制優化器是否考慮使用標記為“不可見”的索引。在某些情況下，數據庫管理員可能希望將索引標記為不可見以進行測試或維護，而不影響現有查詢的性能。當此標志設置為on時，即使索引被標記為不可見，優化器也會考慮使用它們。

14. skip_scan

skip_scan 允許優化器在某些情況下使用跳躍掃描來優化范圍查詢。跳躍掃描是一種技術，其中優化器可以跳過某些索引條目以更快地找到滿足查詢條件的條目。

15. duplicateweedout

在執行某些類型的 JOIN 操作時，可能會出現重復的行。當 duplicateweedout 設置為on時，優化器將嘗試在結果集中刪除這些重復的行，從而提高查詢結果的準確性。

16. subquery_materialization_cost_based

    當此標志設置為on時，優化器將基于成本決定是否物化子查詢。物化子查詢是將子查詢的結果集存儲在臨時表中，以便在外部查詢中重復使用。這可以提高某些類型查詢的性能，但也可能增加內存使用。

如何使用 optimizer_switch

要使用 optimizer_switch，你首先需要查看其當前設置：

SHOW VARIABLES LIKE 'optimizer_switch';

這將返回一個包含所有當前設置的標志及其狀態的列表。

要更改設置，你可以使用 SET 語句。例如，要啟用 ICP，你可以執行：

SET optimizer_switch='index_condition_pushdown=on';

注意，上述命令只會更改當前會話的設置。如果你想全局更改設置，需要使用 GLOBAL 關鍵字：

SET GLOBAL optimizer_switch='index_condition_pushdown=on';

注意事項和最佳實踐

• 在更改 optimizer_switch 設置之前，最好先在測試環境中驗證更改的效果。
• 不是所有的優化標志都適用于所有版本的 MySQL。在更改設置之前，請查閱相關文檔以確保你了解每個標志的具體行為和限制。
• 避免在生產環境中盲目更改設置。應該基于實際的性能分析和測試來做出決策。
• 監控數據庫的性能指標，以便及時發現并解決潛在問題。

結論

optimizer_switch 是一個強大的工具，允許數據庫管理員和開發者精細地控制 MySQL 查詢優化器的行為。合理地調整這些設置，可以提高數據庫的性能并優化查詢效率。使用時也要謹慎并基于充分的測試和分析。

---

聽說...關注下面公眾號的人都變牛了，純技術，純干貨 !