柚子快報(bào)邀請(qǐng)碼778899分享：mysql optimizer

http://yzkb.51969.com/

碼到三十五 ：

個(gè)人主頁(yè)

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

目錄

optimizer_switch 的概念查看當(dāng)前的優(yōu)化器標(biāo)志集修改optimizer_switch的值

主要優(yōu)化標(biāo)志介紹如何使用 optimizer_switch注意事項(xiàng)和最佳實(shí)踐結(jié)論

optimizer_switch 的概念

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

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

查看當(dāng)前的優(yōu)化器標(biāo)志集

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的值，指定一個(gè)由一個(gè)或多個(gè)命令組成的逗號(hào)分隔的值:

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

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

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

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

index_merge index_merge 控制是否允許索引合并優(yōu)化。當(dāng)查詢條件可以通過多個(gè)索引來(lái)滿足時(shí)，MySQL 可以合并這些索引以更有效地檢索數(shù)據(jù)。在復(fù)雜查詢中，這可以顯著提高性能。 index_condition_pushdown (ICP) ICP 允許將 WHERE 子句中的條件推送到存儲(chǔ)引擎層進(jìn)行處理。這減少了存儲(chǔ)引擎需要返回給優(yōu)化器的數(shù)據(jù)量，因?yàn)樗梢栽跈z索數(shù)據(jù)時(shí)就過濾掉不符合條件的行。 materialization 當(dāng)查詢包含子查詢時(shí)，materialization 標(biāo)志控制是否將子查詢的結(jié)果物化（即臨時(shí)存儲(chǔ)）。物化子查詢可以減少重復(fù)計(jì)算，但也可能增加內(nèi)存使用。 semijoin 和 loosescan 這兩個(gè)標(biāo)志與半連接優(yōu)化相關(guān)。半連接是一種在處理包含 EXISTS 或 IN 子句的查詢時(shí)特別有效的優(yōu)化策略。semijoin 控制是否使用這種優(yōu)化，而 loosescan 則允許在某些情況下進(jìn)行更高效的掃描。 derived_merge 當(dāng)查詢中包含派生表（由子查詢生成的臨時(shí)表）時(shí)，derived_merge 標(biāo)志控制是否嘗試將這些派生表合并到外部查詢中。這可以減少查詢的復(fù)雜性并提高性能。 exists_to_in 在某些情況下，將 EXISTS 子句轉(zhuǎn)換為 IN 子句可能會(huì)改變查詢的執(zhí)行計(jì)劃并提高性能。exists_to_in 標(biāo)志控制是否進(jìn)行這種轉(zhuǎn)換。 mrr (Multi-Range Read) MRR 是一種優(yōu)化技術(shù)，用于改善范圍查詢和JOIN操作的性能。當(dāng)設(shè)置為on時(shí)，MySQL 會(huì)嘗試使用 MRR 來(lái)更有效地從磁盤讀取數(shù)據(jù)。這通?？梢詼p少磁盤I/O，并提高查詢速度。 mrr_cost_based 當(dāng)此標(biāo)志設(shè)置為on時(shí)，MySQL 將基于成本決定是否使用 MRR。如果查詢優(yōu)化器認(rèn)為使用 MRR 會(huì)更有效，那么它就會(huì)使用這種技術(shù)。否則，它將回退到傳統(tǒng)的讀取方法。 block_nested_loop 這個(gè)標(biāo)志控制是否使用塊嵌套循環(huán)連接（Block Nested Loop Join, BNLJ）。BNLJ 是一種在處理連接操作時(shí)減少I/O次數(shù)的方法。當(dāng)設(shè)置為on時(shí)，MySQL 將考慮使用 BNLJ 來(lái)優(yōu)化連接操作。 batched_key_access

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

use_index_extensions

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

condition_fanout_filter

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

use_invisible_indexes

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

skip_scan

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

duplicateweedout

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

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

如何使用 optimizer_switch

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

SHOW VARIABLES LIKE 'optimizer_switch';

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

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

SET optimizer_switch='index_condition_pushdown=on';

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

SET GLOBAL optimizer_switch='index_condition_pushdown=on';

注意事項(xiàng)和最佳實(shí)踐

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

結(jié)論

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

聽說...關(guān)注下面公眾號(hào)的人都變牛了，純技術(shù)，純干貨 !

柚子快報(bào)邀請(qǐng)碼778899分享：mysql optimizer

http://yzkb.51969.com/

推薦閱讀