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

optimizer_switch 的概念

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

ptimizer_switch系統(tǒng)變量可以控制優(yōu)化器行為。它的值是一組標志，每個標志都有一個on或off值，用于指示相應(yīng)的優(yōu)化器行為是啟用還是禁用。此變量具有全局值和會話值，可以在運行時更改。全局默認值可以在服務(wù)器啟動時設(shè)置。

查看當前的優(yōu)化器標志集

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]...';

每個命令值應(yīng)該具有下表所示的形式之一：

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

主要優(yōu)化標志介紹

• index_merge

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

• index_condition_pushdown (ICP)

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

• materialization

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

• semijoin 和 loosescan

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

• derived_merge

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

• exists_to_in

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

• mrr (Multi-Range Read)

  MRR 是一種優(yōu)化技術(shù)，用于改善范圍查詢和JOIN操作的性能。當設(shè)置為on時，MySQL 會嘗試使用 MRR 來更有效地從磁盤讀取數(shù)據(jù)。這通?？梢詼p少磁盤I/O，并提高查詢速度。

• mrr_cost_based

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

• block_nested_loop

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

• batched_key_access

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

• use_index_extensions

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

• condition_fanout_filter

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

• use_invisible_indexes

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

• skip_scan

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

• duplicateweedout

在執(zhí)行某些類型的 JOIN 操作時，可能會出現(xiàn)重復的行。當 duplicateweedout 設(shè)置為on時，優(yōu)化器將嘗試在結(jié)果集中刪除這些重復的行，從而提高查詢結(jié)果的準確性。

• subquery_materialization_cost_based

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

如何使用 optimizer_switch

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

SHOW VARIABLES LIKE 'optimizer_switch';

這將返回一個包含所有當前設(shè)置的標志及其狀態(tài)的列表。

要更改設(shè)置，你可以使用 SET 語句。例如，要啟用 ICP，你可以執(zhí)行：

SET optimizer_switch='index_condition_pushdown=on';

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

SET GLOBAL optimizer_switch='index_condition_pushdown=on';

注意事項和最佳實踐

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

結(jié)論

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

到此這篇關(guān)于mysql optimizer_switch查詢優(yōu)化器優(yōu)化策略的文章就介紹到這了,更多相關(guān)mysql optimizer_switch查詢優(yōu)化器優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論