使用Persist plan持久化SQL Hints與實現SQL攔截-雲原生資料倉儲AnalyticDB-阿里雲

Persist plan功能支援在SQL參數化層級使Hints持久化。Query-Blocker功能支援參數化SQL的攔截，實現SQL防火牆的功能。本文適用於AnalyticDB MySQL版3.1.4及以上版本的叢集。

功能介紹

在叢集層級開啟進階最佳化特性，可能影響範圍較大。通過Persist plan設定指定SQL的Hints，可以使Hints只對相同Pattern（即參數化後的SQL）的SQL生效。Persist plan規則支援通過系統資料表查看和刪除。

說明

計算SQL參數化後的Pattern和Sign：PARAMETERIZE $sql
針對指定SQL Pattern，添加Hints：/*+hints*/ PERSIST_PLAN $sql
針對指定SQL Pattern，刪除Hints：DELETE_PLAN $sql或DELETE_PLAN_BY_SIGN $Sign
查詢指定SQL是否配置過Hints：
- PERSIST_PLAN_CHECK $sql
- SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY
查詢配置過Hints的所有SQL Pattern：SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY

使用/*+query_blocker=true*/ persist_plan + SQL，針對指定Pattern攔截同類型的Bad SQL。樣本如下：

/*+query_blocker=true*/
PERSIST_PLAN
SELECT t1.c1
FROM t1
    INNER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 < 9999;

計算SQL參數化後的Pattern&Sign
針對以下SQL計算其對應的Pattern和Sign值，樣本如下：
```
PARAMETERIZE
SELECT
  t1.c1
FROM
  t1
  INNER JOIN t2 ON t1.c1 = t2.c1
WHERE
  t1.c2 < 9999;
```
執行命令的結果如下：
sign
sql
2506ed2c1f53ea59a1ef996a98a50411
SELECT t1.c1 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 < ?

針對指定SQL Pattern，添加Hints

為相同Pattern的SQL，執行nested loop join策略，樣本如下：

/*+nested_loop_join=true*/
PERSIST_PLAN
SELECT t1.c1FROM t1
    INNER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 < 9999;

查詢指定SQL是否配置過Hints
```
PERSIST_PLAN_CHECK
SELECT t1.c1
FROM t1
    INNER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 < 9999;
```
執行命令的結果如下：
sign
Hints
hitAppiled
sql
2506ed2c1f53ea59a1ef996a98a50411
nested_loop_join=true
12
SELECT t1.c1 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 < ?
說明
- PERSIST_PLAN_CHECK $sql命令在Data Management服務系統中執行時，執行命令成功，但不會顯示上表中的結果。若您想看到具體返回資訊，請登入MySQL用戶端執行。
- 返回結果中，hitApplied欄位表示建立該規則後被應用的次數，若更新該規則，hitApplied欄位會清零，重新開始計數。

查詢配置過Hints的所有SQL Pattern

SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY;

針對指定SQL Pattern，刪除Hints
AnalyticDB MySQL版支援刪除指定SQL Pattern的Hints，您可以選擇以下任意一種實現方式：
- 使用DELETE_PLAN $sql命令，刪除Hints。
```
DELETE_PLAN
SELECT t1.c1
FROM t1
    INNER JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 < 9999;
```
- 使用DELETE_PLAN_BY_SIGN命令，根據Sign值刪除Hints。
```
DELETE_PLAN_BY_SIGN 2506ed2c1f53ea59a1ef996a98a50411;
```
  說明
  查詢SQL Pattern的Sign值有以下兩種方法：
  通過PARAMETERIZE $sql，在返回結果中查看Sign值。
  通過SELECT * FROM INFORMATION_SCHEMA.KEPLER_PERSIST_PLAN_SUMMARY，在返回結果中查看Sign值。

sign	sql
2506ed2c1f53ea59a1ef996a98a50411	SELECT t1.c1 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 < ?

sign	Hints	hitAppiled	sql
2506ed2c1f53ea59a1ef996a98a50411	nested_loop_join=true	12	SELECT t1.c1 FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 < ?