如何通過Hint控制查詢 - PolarDB

Parallel Hints可以指定最佳化器是否選擇並存執行，還支援指定並行度以及需要並行的表，以及各個運算元的並存執行方式。PolarDB MySQL版8.0.1版本目前支援在並行查詢中使用PARALLEL和NO_PARALLEL兩種Hints。PolarDB MySQL版8.0.2版本除了支援PARALLEL和NO_PARALLEL外，還增加了PQ_DISTRIBUTE hint來控制join的並行策略；通過PQ_GROUPBY、PQ_DISTINCT、PQ_WINDOW、PQ_ORDERBY hint來控制各個分析型運算元的並存執行方式。

開啟或關閉並行查詢

開啟並行查詢

您可以使用如下任意一種方式開啟並行查詢：

SELECT /*+PARALLEL(x)*/ ... FROM ...;   -- x >0

SELECT /*+ SET_VAR(max_parallel_degree=n) */  *  FROM ...   -- n > 0

關閉並行查詢

您可以使用如下任意一種方式關閉並行查詢：

```
SELECT /*+NO_PARALLEL()*/ ... FROM ...;
```

SELECT /*+ SET_VAR(max_parallel_degree=0) */  *  FROM ...

通過Hint指定並行表

並行查詢提供了PARALLEL和NO_PARALLEL兩種Hint，可以指定允許哪些表並行掃描，不允許哪些表並行掃描。其中：

通過PARALLEL Hint可以強制查詢並存執行，同時可以指定並行度和並行掃描的表。文法如下所示：
```
/*+ PARALLEL [( [query_block] [table_name]  [degree] )] */
```
通過NO_PARALLEL Hint可以強制查詢串列執行，或者指定不選擇某些表作為並行掃描的表。
```
/*+ NO_PARALLEL [( [query_block] [table_name][, table_name] )] */
```

其中參數說明如下所示

參數	說明
query_block	應用Hint的query block名稱。
table_name	應用Hint的表名稱。
degree	並行度。

樣本：

SELECT /*+PARALLEL()*/ * FROM t1, t2; 
-- 當表記錄數小於records_threshold_for_parallelism設定的行數 （ 預設10000行）時，會強制並行，
-- 並行度用系統預設max_parallel_degree， 如果max_parallel_degree > 0, 
-- 則開啟並行，如果max_parallel_degree等於0時，依舊時關閉並行。

SELECT /*+PARALLEL(8)*/ * FROM t1, t2; 
-- 強制並行度8並存執行， 
-- 當表記錄數小於records_threshold_for_parallelism設定的行數 （ 預設10000行）時，會強制並行，
-- 並行度設定max_parallel_degree為8。

SELECT /*+ SET_VAR(max_parallel_degree=8) */  *  FROM ...   
-- 設定並行度max_parallel_degree為8， 
-- 當表記錄數小於records_threshold_for_parallelism設定的行數（如20000行）時，會自動關閉並行。

SELECT /*+PARALLEL(t1)*/ * FROM t1, t2; 
-- 選擇t1表並行, 對t1表執行/*+PARALLEL()*/ 文法

SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2; 
-- 強制並行度8且選擇t1表並存執行，  對t1表執行/*+PARALLEL(8)*/文法

SELECT /*+PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
--強制subquery並存執行， 並行度用系統預設max_parallel_degree， 
-- 如果max_parallel_degree > 0, 則開啟並行，max_parallel_degree等於0時，依舊時關閉並行

SELECT /*+PARALLEL(@subq1 8)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
--強制subquery並存執行， 並行度設定max_parallel_degree為8

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+PARALLEL()*/ SUM(t1.a) FROM t1); 
--強制subquery並存執行， 
-- 並行度用系統預設max_parallel_degree， 
-- 如果max_parallel_degree > 0, 則開啟並行，max_parallel_degree等於0時，依舊時關閉並行

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+PARALLEL(8)*/ SUM(t1.a) FROM t1); 
--強制subquery並存執行， 設定並行度max_parallel_degree為8

SELECT /*+NO_PARALLEL()*/ * FROM t1, t2; 
-- 禁止並存執行

SELECT /*+NO_PARALLEL(t1)*/ * FROM t1, t2; 
-- 只對t1表禁止並行， 當系統開啟並行時， 有可能對t2進行並行掃描，並存執行

SELECT /*+NO_PARALLEL(t1, t2)*/ * FROM t1, t2; 
-- 同時對t1和t2表禁止並行

SELECT /*+NO_PARALLEL(@subq1)*/ SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+QB_NAME(subq1)*/ SUM(t1.a) FROM t1); 
--禁止subquery並存執行

SELECT SUM(t.a) FROM t WHERE t.a = 
  (SELECT /*+NO_PARALLEL()*/ SUM(t1.a) FROM t1); 
 --禁止subquery並存執行

說明

對於不支援並行的查詢或者並行掃描的表，PARALLEL Hint不生效。

並行Join

通過PQ_DISTRIBUTE hint可以指定join操作以哪種方式來執行並行查詢。

通過PQ_DISTRIBUTE指定某個表的資料分發方式：

/*+ PQ_DISTRIBUTE([query_block] table_name  strategy ) */

其中參數說明如下所示

參數	說明
query_block	應用Hint的query block名稱。
table_name	應用Hint的表名稱。
strategy	資料分發策略，包括： PQ_GATHER：資料匯總到上層1個worker中。 PQ_HASH：資料shuffle分發到上層多個worker中。 PQ_BROADCAST：資料廣播到上層多個worker中。 PQ_NONE：不做資料分發。

樣本：

SELECT /*+ PARALLEL(t1) PQ_DISTRIBUTE(t1 PQ_GATHER) */ * FROM t as t1;
-- 當表記錄數小於records_threshold_for_parallelism設定的行數（ 預設10000行）時，會強制並行，
-- 並行度用系統預設max_parallel_degree， 如果max_parallel_degree > 0,
-- 則開啟並行，如果max_parallel_degree等於0時，依舊關閉並行。
-- 並行掃表後，不做資料分發，結果匯總到Leader
SELECT /*+ PARALLEL(t1) PQ_DISTRIBUTE(t1 PQ_HASH) */t1.a, SUM(t1.b) * FROM t as t1 GROUP BY t1.a;
-- 並行掃表後，將資料按照group by key分發到下層worker

通過PQ_DISTRIBUTE指定兩表的join方式：

/*+ PQ_DISTRIBUTE([query_block] table_name strategy1 [strategy2]) */

以上hint如果只指定了strategy1，則相當於指定某個表的資料分發方式；如果同時指定了strategy1和strategy2，則用來指定table_name表和其前面一張表的並行join方式。

說明

table_name前面的表可以是一張物理表，或者是前面join的中間結果表。

樣本：

SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_HASH PQ_HASH) */ * FROM t as t1 STRAIGHT_JOIN t as t2 on t1.b = t2.c;
-- 在t1表上做並行掃表，然後將資料做shuffle分發到下一階段Workers
-- 在t2表上做並行掃表，然後將資料做shuffle分發到下一階段Workers
-- 在下階段Workers上完成co-location join後，結果匯總到Leader
SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_GATHER PQ_GATHER) */ * FROM t as t1 STRAIGHT_JOIN t as t2 on t1.b = t2.c;
-- 在t1表上做並行掃表，然後將資料做匯總到Leader
-- 在t2表上做並行掃表，然後將資料做匯總到Leader
-- 在Leader上流水線收集資料，完成join操作

對於不支援並行的查詢或者相互矛盾的hint，可能會導致查詢無法並行。樣本如下：

SELECT /*+ PARALLEL(t1) PARALLEL(t2) PQ_DISTRIBUTE(t2 PQ_HASH PQ_GATHER) */ * FROM t as t1 STRAIGHT_JOIN t as t2 on t1.b = t2.c;
-- 在t1表上做並行掃表，然後將資料分發到下一層多個Worker上
-- 在t2表上做並行掃表，然後將資料做匯總到Leader
-- 兩個hint在資料分布方式矛盾，會導致無法產生並行計劃

並行分組聚集

通過PQ_GROUPBY指定分組聚集的執行方式：

/*+ PQ_GROUPBY(strategy) */

其中參數說明如下所示：

參數

說明

strategy

資料分發策略，包括：

PQ_ONEPHASE：資料根據分組鍵分發到多個worker中，一次性並行完成分組聚集操作。
PQ_TWOPHASE_GATHER：資料在現有worker中完成局部聚集，再匯總到Leader上完成最終匯總。
PQ_TWOPHASE_HASH：資料在現有worker中完成局部聚集，再根據分組鍵分發到下階段多個worker中。
PQ_SERIAL：串列完成分組聚集。

樣本：

SELECT /*+ PARALLEL(t1) PQ_GROUPBY(PQ_ONEPHASE) */ t1.a, sum(t1.b) FROM t as t1 GROUP BY t1.a;
-- 在t1表上做並行掃表，然後將資料按照t1.a列分發到下一層多個Worker上
-- 在下一階段多個Worker中完成聚集計算，結果匯總到Leader

SELECT /*+ PARALLEL(t1) PQ_GROUPBY(PQ_TWOPHASE_HASH) */ t1.a, sum(t1.b) FROM t as t1 GROUP BY t1.a;
-- 在t1表上做並行掃表，然後直接在掃表的Workers上，完成一階段的聚集操作
-- 將中間聚集結果按照t1.a列分發到下一層多個Worker上
-- 在下一階段多個Worker中完成最終聚集計算，結果匯總到Leader

並行DISTINCT

通過PQ_DISTINCT指定分組聚集的執行方式：

/*+ PQ_DISTINCT(strategy) */

其中參數說明如下所示：

參數

說明

strategy

資料分發策略，包括：

PQ_TWOPHASE_GATHER：資料在現有worker中完成局部去重，再匯總到Leader上完成最終去重。
PQ_SERIAL：串列完成分組聚集。

並行視窗函數

通過PQ_WINDOW指定視窗函數的執行方式：

/*+ PQ_WINDOW([window_name] strategy) */

說明

帶視窗名稱hint的優先順序高於沒有名稱的hint。

其中參數說明如下所示：

參數

說明

window_name

視窗函數名稱。指定策略對哪個視窗函數生效。如果不設定，預設對所有視窗函數生效。

strategy

資料分發策略，包括：

PQ_ONEPHASE：基於Partition by子句做資料分發，然後在Workers上並行完成視窗計算。
PQ_SERIAL：串列完成分組聚集。

樣本：

SELECT /*+ PQ_WINDOW(PQ_ONEPHASE)  PQ_WINDOW(win PQ_SERIAL) */
  ROW_NUMBER() OVER(win) AS 'row_number',
  RANK() over(partition by name order by salary desc)
FROM employee_salaries WINDOW win as (partition by dept order by salary desc);
-- 對於名稱為win的視窗函數，通過串列方式計算
-- 對於其他視窗函數，通過在partition by key上分布做並行計算

並行Order by

通過PQ_ORDERBY指定排序操作的執行方式：

/*+ PQ_ORDERBY(strategy) */

其中參數說明如下所示：

參數

說明

strategy

資料分發策略，包括：

PQ_TWOPHASE_GATHER：資料在現有worker中完成局部排序，再匯總到Leader上完成最終排序。
PQ_SERIAL：串列完成排序。

並行子查詢

並行子查詢的選擇方式（並行子查詢詳細資料請參見子查詢支援）也可以通過Hint來進行控制，文法及說明如下：

/*+ PQ_PUSHDOWN [( [query_block])] */      #對應的子查詢會選擇push down的並行子查詢執行策略。
/*+ NO_PQ_PUSHDOWN [( [query_block])] */   #對應的子查詢會選擇shared access的並行子查詢執行策略。

樣本：

#子查詢選擇push down並行策略
EXPLAIN SELECT /*+ PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ qb_name(qb1) */ a FROM t1);

#子查詢選擇shared access並行策略
EXPLAIN SELECT /*+ NO_PQ_PUSHDOWN(@qb1) */ * FROM t2 WHERE t2.a =
                 (SELECT /*+ qb_name(qb1) */ a FROM t1);
#不加query block進行控制
EXPLAIN SELECT * FROM t2 WHERE t2.a =
                 (SELECT /*+ NO_PQ_PUSHDOWN() */ a FROM t1);