AutoPlanCache功能的基本資料有哪些 - PolarDB

您可以使用PolarDB MySQL版提供的Auto Plan Cache功能，來緩衝SQL語句的執行計畫資訊，以縮短查詢最佳化時間，提升SQL語句的查詢效能。本文介紹了Auto Plan Cache功能的背景資訊、前提條件、依賴的參數和介面等內容。

背景資訊

執行計畫的選擇需要考慮諸多因素，如統計資訊、不同的串連順序和不同的查詢變換等。對於不同的查詢語句，其最佳化時間不同，可能會存在某些SQL語句的查詢最佳化時間在整體執行時間中佔比很大的情況。如果這類SQL語句執行的次數較多，就會因為最佳化時間佔比大導致系統負載增加。通過緩衝並複用SQL語句的執行計畫，可以減少每次執行SQL語句的最佳化時間，從而提升查詢效能，降低資料庫負載，提升吞吐能力。

除此之外，還有很多查詢語句的查詢最佳化時間佔比很小，執行時間受執行計畫的影響非常大，SQL語句中不同參數值對應不同的最優執行計畫。某些情境下，MySQL會根據參數值從引擎擷取實際資料做進一步最佳化。

上述兩類查詢一旦固定執行計畫，查詢回應時間和負載開銷不會有明顯最佳化，反而可能使查詢效能回退。

為了提升最佳化時間佔比太多的SQL語句的查詢效能，降低系統負載，同時避免執行SQL語句時因採用固定執行計畫而導致查詢效能回退問題。PolarDB MySQL版提供了Auto Plan Cache功能。Auto Plan Cache功能提供了AUTO、DEMAND和ENFORCE三種模式，您可以根據需要將loose_plan_cache_type參數設定為三種模式中的任意一種模式，將SQL語句的執行計畫緩衝在Plan Cache中，以減少執行查詢語句時的最佳化時間，提升查詢效能。當緩衝在Plan Cache中的執行計畫涉及的表的統計資訊發生變化，或對緩衝中執行計畫引用的表執行了DDL操作時，緩衝的執行計畫會自動失效。

前提條件

PolarDB叢集版本需滿足以下條件之一：

PolarDB MySQL版8.0.1版本且修訂版本為8.0.1.1.33及以上。
PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.12及以上。

參數說明

您可以在PolarDB控制台上設定下表中的參數，詳細操作步驟請參見設定叢集參數和節點參數。

參數	說明
loose_plan_cache_type	Auto Plan Cache模式。取值如下： OFF（預設）：關閉Auto Plan Cache功能。 AUTO：自動將滿足緩衝條件的SQL語句的執行計畫緩衝在Plan Cache中。說明緩衝條件： SQL語句的整體執行時間閾值大於或等於`loose_auto_plan_cache_time_threshold`參數值，且SQL語句的最佳化時間佔整體執行時間閾值的百分比閾值大於或等於`loose_auto_plan_cache_pct_threshold`參數值時，該SQL語句的執行計畫會被緩衝到Plan Cache中。 DEMAND：將指定SQL語句的執行計畫緩衝在Plan Cache中。 ENFORCE：強制將所有SQL語句的執行計畫緩衝在Plan Cache中。
loose_plan_cache_expire_time	Plan Cache中的執行計畫超過該時間沒有被命中，記憶體會被回收。單位為秒。取值範圍：0~4294967295。預設值為1800。
loose_auto_plan_cache_pct_threshold	最佳化時間占語句整體執行時間的百分比閾值。取值範圍：0~100。預設值為20。
loose_auto_plan_cache_time_threshold	SQL語句整體執行時間閾值。單位為微秒。取值範圍：0~18446744073709551615。預設值為400。
loose_auto_plan_cache_count_threshold	當`loose_plan_cache_type`參數設定為AUTO時，滿足緩衝條件的SQL語句的執行計畫緩衝到Plan Cache中的次數閾值。取值範圍：0~18446744073709551615。預設值為512。說明當緩衝到Plan Cache中的次數閾值大於或等於`loose_auto_plan_cache_count_threshold`參數值時，緩衝中的執行計畫才會生效。

介面說明

dbms_sql.add_plan_cache(schema, query)：將指定SQL語句的執行計畫緩衝到Plan Cache中。
當loose_plan_cache_type參數配置為DEMAND時，您可以通過該內建預存程序將指定SQL語句的執行計畫緩衝到Plan Cache中。樣本如下：
```
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
```
上述語句執行完成後，當您執行的SQL語句滿足SELECT * FROM t_for_plan WHERE c1 > ？ AND c1 < ？模板時，該SQL語句的執行計畫會被緩衝到Plan Cache中。
dbms_sql.display_plan_cache_table()：查看當前Plan Cache中引用的表資訊。樣本如下：
```
CALL dbms_sql.display_plan_cache_table()\G
```
執行結果如下：
```
*************************** 1. row ***************************
 SCHEMA_NAME: test
  TABLE_NAME: t_for_plan
   REF_COUNT: 1
     VERSION: 0
VERSION_TIME: 2023-03-10 17:21:35.605264
```
對應的參數說明如下：
- SCHEMA_NAME：引用的表所在的SCHEMA名稱。
- TABLE_NAME：引用的表名。
- REF_COUNT：引用的表在Plan Cache中被引用的數量。
- VERSION：引用的表在Plan Cache中的版本。
- VERSION_TIME：引用目前的版本的表的時間。
dbms_sql.delete_sharing_by_rowid(row_id)：刪除指定SQL語句的執行計畫。
row_id為儲存在mysql.sql_sharing表中的執行計畫的行ID值。
樣本
1. 執行以下命令，查看緩衝中的執行計畫資訊。
```
SELECT Id, Schema_name, Type, Digest_text FROM mysql.sql_sharing WHERE Type = 'PLAN_CACHE'\G
```
  查詢結果如下：
```
*************************** 1. row ***************************
         Id: 1
Schema_name: test
       Type: PLAN_CACHE
Digest_text: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
```
  從以上查詢結果可以看出，row_id值為1。
2. 刪除上述查詢中的執行計畫。
```
CALL dbms_sql.delete_sharing_by_rowid(1);
```

擷取Plan Cache中的緩衝資訊

SQL語句的執行計畫儲存在SQL Sharing模組中，您可以通過以下SQL語句在INFORMATION_SCHEMA.SQL_SHARING表中查詢Plan Cache中的緩衝資訊。

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

樣本

資料準備。

CREATE TABLE t_for_plan AS WITH RECURSIVE t(c1, c2, c3) AS (SELECT 1, 1, 1 UNION ALL SELECT c1+1, c1 % 50, c1 %200 FROM t WHERE c1 < 1000) SELECT c1, c2, c3 FROM t;
CREATE INDEX i_c1_c2 on t_for_plan(c1, c2);

將Auto Plan Cache模式設定為DEMAND。
您可以通過以下兩種方式來設定Auto Plan Cache模式。
- 在PolarDB控制台的參數配置頁面將loose_plan_cache_type參數設定為DEMAND。設定完成後，斷開當前串連並重新串連資料庫。
- 在當前資料庫連接中執行以下命令，將當前會話中的plan_cache_type參數設定為demand。
```
SET plan_cache_type=demand;
```

執行以下命令，將指定SQL語句的執行計畫緩衝到Plan Cache中。

CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");

執行查詢語句。

SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10;

查詢Plan Cache中的緩衝資訊。

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

查詢結果如下：

*************************** 1. row ***************************
       TYPE: SQL
     REF_BY: ["PLAN_CACHE(DEMAND)"]
     SQL_ID: 9jrvksr3wjux6
SCHEMA_NAME: test
DIGEST_TEXT: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
    PLAN_ID: NULL
       PLAN: NULL
 PLAN_EXTRA: NULL
      EXTRA: {"TRACE_ROW_ID":1}
*************************** 2. row ***************************
       TYPE: PLAN
     REF_BY: ["PLAN_CACHE"]
     SQL_ID: 9jrvksr3wjux6
SCHEMA_NAME: test
DIGEST_TEXT: NULL
    PLAN_ID: 08xftakma6pm6
       PLAN: /*+ INDEX(`t_for_plan`@`select#1` `i_c1_c2`) */
 PLAN_EXTRA: {"access_type":["`t_for_plan`:range"]}
      EXTRA: {"PLAN_CACHE_INFO":{"tables":[`test`.`t_for_plan`], "versions":[0], "hits": 0}}

其中，EXTRA欄位的PLAN_CACHE_INFO中會展示引用的表、引用的表的版本和執行計畫叫用次數。

查詢效能

在叢集規格為8核32 GB，資料庫中已建立25張表，單張表格儲存體400萬行資料的情境下進行壓測。壓測使用的SQL語句為：SELECT id FROM sbtestN WHERE k IN(...)，其中，IN LIST的長度為20。在PS協議和非PS協議下，測試loose_plan_cache_type參數配置為OFF、AUTO和ENFORCE時的效能。測試結果如下：

PS協議下的效能測試結果如下：
非PS協議下的效能測試結果如下：

從以上測試結果可以看出：Auto Plan Cache功能在PS協議和非PS協議下都能夠獲得50%以上的效能提升。