PolarDB：自適應執行能力

背景資訊

最佳化器會根據統計資訊和部分資料採樣資訊來做基數和估算選擇率，這些資訊可能因採樣精度、採樣方式等原因導致估算偏差較大。同時，還有廣泛的情境沒有相關的統計資訊，此時資料庫會根據經驗值或者某種數學假設去評估統計資訊，這可能會導致評估的值與實際值偏差較大。對於最佳化器估算與實際不符導致選錯執行計畫的情境，最佳化器難以預先收集對應的資訊。

自適應執行能力

行列自適應分流

使用列存索引（IMCI）功能時，若您配置的是行列自動分流，執行的查詢可能會由於行存執行代價的估算偏低，導致被路由到行存執行，而非列存執行。

針對上述路由錯誤的問題，之前的解決方案如下：

• 調低路由到列存的代價閾值，此方案容易導致大量的短查詢路由到列存，從而導致列存負載太高，阻塞了其他查詢。

• 使用計劃固化 （Statement Outline）功能，將固定指定模板的查詢路由到列存，如通過添加以下樣本中的outline可以強制該查詢語句路由到列存執行。但該方式會導致營運成本大幅增加。

  CALL dbms_outln.add_optimizer_outline("<Schema_name>", "/*+ SET_VAR(cost_threshold_for_imci=0) SET_VAR(imci_ap_threshold=0) */","<query>");

當前PolarDB MySQL版的自適應執行可以應用在行列路由中，將錯誤路由到行存的慢查自動切換到列存執行，保證執行效率。如下圖所示通過自適應執行，資料庫會在最佳化階區段標記將查詢中各個Query block和整體的掃描資料行數加入到監控資訊中。如果查詢在代價計算後未選擇列存，最佳化器將計算觸發自適應執行的閾值。在新版本的叢集中（如8.0.1.1.49及以上或 8.0.2.2.29及以上），該閾值基於參數loose_adaptive_cost_threshold_for_imci進行計算；而在舊版本的叢集中，則基於參數loose_cost_threshold_for_imci進行計算。在執行階段，當Query block或整體掃描行數觸發自適應執行的閾值，即檢查是否切換列存執行。這裡掃描行數的監控在核心路徑上僅是一個整數比較，並不影響執行效能。同時切換列存前，會保證行存執行的結果集沒有返回給用戶端。選擇切換列存後，會清空已緩衝的行存結果集。

有序索引的自適應選擇

在資料庫查詢最佳化中，prefer_ordering_index 是一個最佳化選項，用來指示查詢最佳化工具優先使用可支援排序操作的索引。然而，在某些情況下，這個最佳化可能會導致查詢效能下降。特別是當排序索引和查詢的選擇性不匹配時，由於排序索引的低選擇性或大量資料掃描回表的情況，選擇排序索引可能會導致查詢掃描大量無關資料，增加I/O和CPU的負載。

針對上述由於prefer_ordering_index導致的慢查詢，解決方案如下：

可以使用計劃固化 （Statement Outline）功能固定指定SQL模板的查詢關閉prefer_ordering_index。但該方式會導致營運成本大幅增加。同時若在該SQL模板下，有部分查詢開啟prefer_ordering_index後執行效率更高，則會受到影響。

CALL dbms_outln.add_optimizer_outline("<Schema_name>", "/*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */","<query>");

當前自適應執行可以應用在有序索引的選擇糾偏中，當查詢因為prefer_ordering_index 最佳化而執行效率不佳時，會重新調整執行計畫，從而將執行計畫從有序索引上糾正回來。如果查詢因為prefer_ordering_index最佳化選擇了有序索引，最佳化器會計算觸發自適應執行的閾值。在執行階段，有序索引上執行行數觸發閾值條件時，會檢查是否切換執行計畫重新執行。這裡掃描行數的監控在核心路徑上僅是一個整數比較，並不影響執行效能。同時切換執行計畫前，會保證執行的結果集沒有返回給用戶端。選擇切換計劃後，會清空已緩衝的行存結果集。

使用前提

• 開啟行列自適應分流（對應使用說明的imci_chosen開關）能力的叢集要求如下：

  • PolarDB MySQL版8.0.1版本且修訂版本為8.0.1.1.39及以上。

  • PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.29及以上。

• 開啟有序索引的自適應選擇（對應使用說明的ordering_index開關）的叢集要求如下：

  • PolarDB MySQL版8.0.1版本且修訂版本為8.0.1.1.47及以上。

  • PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.29及以上。

使用說明

開啟或關閉自適應執行能力

您可以登入PolarDB控制台。在目的地組群的參數配置頁面配置自適應執行功能：

• 將參數loose_adaptive_plans_switch的值設定為'imci_chosen=on'來開啟行列自適應分流的能力。

• 將參數loose_adaptive_plans_switch的值設定為'ordering_index=on'來開啟針對prefer_ordering_index最佳化選中的有序索引進行自適應調整。

• 將參數loose_adaptive_plans_switch的值設定為'imci_chosen=on,ordering_index=on'同時開啟兩項自適應能力，或是通過將其中某項的值設定為'off'關閉對應的自適應能力。

設定參數值的具體操作請參見設定叢集參數和節點參數，使用自適應執行能力時涉及的具體參數見下表。

查看自適應切換執行計畫的次數

您可以在資料庫中執行以下SQL語句，來查看從開啟自適應執行能力開始，截止到目前的自適應切換執行計畫的次數。

SHOW GLOBAL STATUS LIKE 'Adaptive_plan_used';

其中，變數Adaptive_plan_used說明如下：