使用STATEMENT OUTLINE功能精確控制SQL執行計畫-雲原生資料庫 PolarDB-阿里雲

為產生更優的執行計畫，PolarDB PostgreSQL版（相容Oracle）提供了執行計畫固定（Statement OUTLINE）功能，簡稱為OUTLINE。該功能支援對某類SQL自動增加指定HINT，在不影響商務應用程式的情況下，精確控制SQL執行計畫。

背景資訊

在實際啟動並執行資料庫環境中，慢SQL問題常常困擾著營運團隊和開發人員。慢SQL的成因多種多樣，可能源於資料分布不均、統計資訊不準確等因素。

為了產生更優的執行計畫，使用HINT幹預最佳化器行為是一種常見且有效手段。然而，直接要求資料庫管理員在業務SQL語句中添加HINT會面臨諸多挑戰：

添加HINT的複雜性：許多應用的SQL是通過中介軟體自動產生的，手動添加HINT既不現實又十分繁瑣。
發布流程的複雜性：在應用程式中手動添加HINT，並且每次調整HINT都需要發布新的應用版本，這不僅耗時耗力，還可能帶來額外的風險。
HINT管理的複雜性：在應用程式中添加了大量HINT後，管理成本隨之增加，資料庫管理員難以清晰瞭解整個系統中存在的HINT數量。

針對上述困境，PolarDB PostgreSQL版（相容Oracle）推出對某類SQL自動增加HINT的能力，即執行計畫固定（Statement OUTLINE），簡稱OUTLINE。該功能可在不影響商務應用程式的情況下，精確控制SQL執行計畫。

功能介紹

OUTLINE功能允許您手動固定特定SQL語句的執行計畫，並對這類SQL的HINT進行添加和修改。OUTLINE提供了以下優勢：

獨立於應用程式：無需修改應用程式代碼，也無需重新發布，僅在資料庫層面進行配置即可生效。
快速響應和調整：及時響應並調整執行計畫，以有效解決慢SQL問題，提升系統的穩定性與效能。
精細化控制和管理：為不同的SQL提供相應的OUTLINE，可以清晰呈現整個系統的OUTLINE及其狀態（例如命中情況、是否開啟等）。

在不影響商務應用程式的情況下，您可通過OUTLINE功能精確控制SQL執行計畫，從而高效解決慢SQL問題。

基本概念

HINT：最佳化器提示，通過注釋的方式幹預最佳化器產生不同的執行計畫。
OUTLINE：指標對某類SQL指定增加特定的HINT，稱之為建立了一個OUTLINE。
OUTLINE DDL：指對OUTLINE的修改，包括新增、刪除、狀態改變。
SQL_ID：SQL語句標識符，由資料庫系統自動產生，用於區分不同的SQL。OUTLINE使用SQL_ID區分和匹配目標SQL。

適用範圍

支援的的版本如下：
- （核心小版本2.0.16.9.9.0及以上）。
- （核心小版本2.0.14.13.28.0及以上）。
說明
您可在控制台查看核心小版本號碼，也可以通過SHOW polardb_version;語句查看。如未滿足核心小版本要求，請升級核心小版本。
需建立外掛程式，且外掛程式版本不低於1.4.1。

適用範圍

支援的PolarDB PostgreSQL版（相容Oracle）的版本如下： Oracle文法相容 2.0，且核心小版本需為2.0.14.13.28.0及以上。
說明
您可在控制台查看核心小版本號碼，也可以通過SHOW polardb_version;語句查看。如未滿足核心小版本要求，請升級核心小版本。
需建立pg_hint_plan外掛程式，且外掛程式版本不低於1.4.1。

注意事項

SQL_ID匹配標準：資料庫核心會歸一化一類SQL，忽略以下因素對OUTLINE匹配的影響。
- 空格、換行和注釋。
- SQL中的參數，包括常量和變數。例如，a = 1、a = $1、a = 2將被歸一化。
- 關鍵字的大小寫。例如，SELECT a、select a、Select a將被歸一化。
- 對於連續參數，將忽略參數個數的影響。例如，a IN (1,2,3)、a IN (1,2)將被歸一化，但是，a IN (1,2,3)與a IN (1)不會執行歸一化，原因是IN (1)不是連續的參數。
支援為一類SQL建立多個OUTLINE：多個OUTLINE將相互疊加，且該SQL語句受到多個HINT的影響。
OUTLINE優先順序高於SQL語句中內建的HINT：對SQL建立OUTLINE之後，原SQL語句中的HINT將失效，僅受OUTLINE中HINT影響。
OUTLINE和pg_hint_plan的hint_table功能互斥：OUTLINE與hint_table功能類似，開啟OUTLINE功能後，hint_table功能預設失效。

效能影響

OUTLINE設計了高並發緩衝模組，對效能影響極小。啟用並添加OUTLINE後，Sysbench標準壓測顯示TPS和QPS僅下降1%~2%左右。

開啟OUTLINE功能

在需要使用OUTLINE功能的資料庫中確認是否已安裝pg_hint_plan外掛程式，以及外掛程式版本是否大於等於1.4.1。
```
SELECT extname, extversion >= '1.4.1' AS outline_version_ok FROM pg_extension WHERE extname = 'pg_hint_plan';
```
如返回結果如下則外掛程式安裝成功且滿足OUTLINE功能使用版本要求。
```
   extname    | outline_version_ok
--------------+--------------------
 pg_hint_plan | t
(1 row)
```
若返回結果不一致，可能存在以下問題。您可通過對應方式進行處理：
說明
請在目標資料庫中使用具有相應許可權的帳號執行以下語句。
- 外掛程式不存在：
```
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;
```
- 已安裝外掛程式，但不滿足外掛程式版本要求：
```
ALTER EXTENSION pg_hint_plan UPDATE;
```
開啟OUTLINE功能。設定參數pg_hint_plan.polar_enable_outline為on，修改該參數不會造成叢集重啟。通過控制台設定叢集參數詳細操作，請參見設定叢集參數。

使用OUTLINE功能

建立OUTLINE

PolarDB提供hint_plan.create_outline函數，用於建立OUTLINE。輸入需要固定的SQL後，將自動提取SQL中的Hints並加入OUTLINE。

樣本

CALL hint_plan.create_outline($$ SELECT /*+ Set(enable_bitmapscan off) */ * FROM t WHERE a = 1 $$);

查看OUTLINE

PolarDB PostgreSQL版（相容Oracle）提供hint_plan.outlines_status視圖，用於查看資料庫內的OUTLINE。

您可以通過以下語句查詢資料庫中的OUTLINE：

SELECT * FROM hint_plan.outlines_status;

返回欄位說明如下：

列名	資料類型	描述
id	BIGINT	主鍵，由系統自動產生，用於區分OUTLINE的編號。
sql_id	BIGINT	OUTLINE對應的SQL_ID。
hints	TEXT	OUTLINE對應的HINT。
state	CHARACTER(1)	OUTLINE目前狀態，取值如下： Y：開啟。 N：關閉。
depends_rels	TEXT[]	使用當前OUTLINE產生的計劃中依賴的所有`relation`名稱。
query_string	TEXT	建立OUTLINE的SQL。
create_user	TEXT	建立OUTLINE的使用者。
create_time	TIMESTAMP WITHOUT TIME ZONE	建立OUTLINE的時間。
total_hints	TEXT	當前SQL_ID需要添加的HINT資訊，當多個OUTLINE擁有相同SQL_ID時，按照ID順序彙總。
calls	BIGINT	當前OUTLINE已被命中且使用的次數。

開啟或關閉目標OUTLINE

建立OUTLINE後，可使用以下函數選擇是否啟用該OUTLINE。

hint_plan.enable_outline：用於開啟OUTLINE，輸入OUTLINE對應id。
hint_plan.disable_outline：用於關閉OUTLINE，輸入OUTLINE對應id。

樣本

--- 查詢資料庫內的OUTLINE id
SELECT * FROM hint_plan.outlines_status;

--- 開啟OUTLINE
CALL hint_plan.enable_outline(1);

--- 關閉OUTLINE
CALL hint_plan.disable_outline(1);

刪除OUTLINE

對於不再使用的OUTLINE，可以使用hint_plan.del_outline函數來刪除目標OUTLINE。在輸入對應的OUTLINE ID後，將刪除相應的OUTLINE。

樣本

--- 查詢資料庫內的OUTLINE id
SELECT * FROM hint_plan.outlines_status;

--- 刪除目標OUTLINE
CALL hint_plan.del_outline(1);

完整樣本

開啟OUTLINE功能。具體操作，請參見開啟OUTLINE功能。

基礎測試資料準備。

CREATE TABLE t(a int,b int,PRIMARY KEY(a));
CREATE INDEX ON t(b);
INSERT INTO t SELECT i,i FROM generate_series(1,100000)i;
ANALYZE t;

最佳化器認為主鍵索引與b列索引的成本相當，因此其計划具有一定的隨機性。

EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

返回結果如下：

          QUERY PLAN
-------------------------------
 Index Scan using t_b_idx on t
   Index Cond: (b = 1)
   Filter: (a = 1)
(3 rows)

使用HINT幹預最佳化器，使執行計畫選擇主鍵索引，並希望使用OUTLINE幹預計劃，使得SQL計劃不變。
1. 使用HINT幹預最佳化器，使執行計畫選擇主鍵索引。
```
EXPLAIN (costs off) /*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1;
```
  返回結果如下：
```
          QUERY PLAN
------------------------------
 Index Scan using t_pkey on t
   Index Cond: (a = 1)
   Filter: (b = 1)
(3 rows)
```
2. 為該SQL建立OUTLINE，需要注意SQL文本和應用程式的模板要一致。普通的參數、常量、HINT、空格以及注釋不影響匹配，額外的::類型轉換、多指定Table的Schema、改變表名大小寫、改變列名大小寫等將影響匹配，詳細的匹配規則請參見SQL_ID匹配標準。
```
CALL hint_plan.create_outline($$/*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1;$$);
```
  說明
  如使用DMS串連叢集執行以上語句出現類似ERROR: invalid transaction termination報錯，可使用其他用戶端操作，例如psql等，詳情請參見串連資料庫叢集。
  再次驗證目標SQL，將使用期望的主鍵索引：
```
EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;
```
  返回結果如下：
```
          QUERY PLAN
------------------------------
 Index Scan using t_pkey on t
   Index Cond: (a = 1)
   Filter: (b = 1)
(3 rows)
```
  使用其他參數、多空格、多注釋等與模板一致的SQL，不影響OUTLINE的匹配，依舊使用主鍵索引。
```
EXPLAIN (costs off) SELECT * -- comment
FROM t
WHERE b = 2 AND a = 4;
```
  返回結果如下：
```
          QUERY PLAN
------------------------------
 Index Scan using t_pkey on t
   Index Cond: (a = 4)
   Filter: (b = 2)
(3 rows)
```

通過hint_plan.outlines_status視圖查看所有OUTLINE狀態。

SELECT * FROM hint_plan.outlines_status;

返回結果如下：

 id |        sql_id        |        hints        | state | depends_rels |                           query_string                           | create_user |        create_time         |     total_hints     | calls
----+----------------------+---------------------+-------+--------------+------------------------------------------------------------------+-------------+----------------------------+---------------------+-------
  1 | -3220256307655713529 | IndexScan(t t_pkey) | Y     | {public.t}   | /*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1; | postgres    | 2024-11-11 11:24:44.063143 | IndexScan(t t_pkey) |     2
(1 row)

您可按需關閉或刪除不再使用的OUTLINE。

關閉id為1的OUTLINE。
```
CALL hint_plan.disable_outline(1);
```
刪除id為1的OUTLINE。
```
CALL hint_plan.del_outline(1);
```

關閉或刪除相應OUTLINE後計劃將變回原樣。

EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

返回結果如下：

          QUERY PLAN
-------------------------------
 Index Scan using t_b_idx on t
   Index Cond: (b = 1)
   Filter: (a = 1)
(3 rows)