如何通過Hint實現手動調整Join順序 - AnalyticDB

本文介紹如何通過Hint實現手動調整Join順序。

功能介紹

AnalyticDB MySQL版支援複雜的聯表（Join）查詢且預設提供了自動調整Join順序的功能。但查詢語句和表的過濾條件隨時可能發生變化，而且如果資料特徵複雜，自動調整Join順序功能不一定在所有情境下都能很好地預估查詢特徵，並選擇出最優的Join順序。而不優的Join順序可能造成中間結果集膨脹中間結果集資料量較大、記憶體消耗大等問題，進而影響查詢效能。

為解決上述問題，AnalyticDB MySQL版支援通過Hint/*+ reorder_joins*/來開啟或關閉調整Join順序功能，其中：

/*+ reorder_joins=true*/：開啟自動調整Join順序功能。開啟後，系統會自動調整Join順序。AnalyticDB MySQL版預設開啟調整Join順序功能，因此在執行SQL查詢時無需使用該Hint也會自動調整Join順序。
/*+ reorder_joins=false*/：關閉自動調整Join順序功能。關閉後，您可以根據查詢的資料特徵手動調整Join順序，讓查詢直接根據SQL書寫方式中的Join順序來執行。

說明

/*+ reorder_joins*/為會話層級的Hint，僅對目標SQL查詢語句生效。

調整方法

調整前
- 查詢語句
  原始的Query10語句如下：
  說明
  本文以TPC-H中的Query10為例介紹手動調整Join順序的方法及查詢效果。更多關於TPC-H的詳細說明，請參見TPC-H。
  由於AnalyticDB MySQL版的自動調整Join順序功能預設開啟，在執行如下查詢語句時使用了Hint/*+ reorder_joins=false*/來類比Join順序不優的情境。
```
SELECT   c_custkey,
         c_name,
         Sum(l_extendedprice * (1 - l_discount)) AS revenue,
         c_acctbal,
         n_name,
         c_address,
         c_phone,
         c_comment
FROM     customer c,
         orders o,
         lineitem l,
         nation n
WHERE    c_custkey = o_custkey
AND      l_orderkey = o_orderkey
AND      o_orderdate >= date '1993-10-01'
AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
AND      l_returnflag = 'R'
AND      c_nationkey = n_nationkey
GROUP BY c_custkey,
         c_name,
         c_acctbal,
         c_phone,
         n_name,
         c_address,
         c_comment
ORDER BY revenue DESC
LIMIT    20;
```
- Join順序
  如果按照如上SQL的書寫方式，Join的順序應該是：
```
customer JOIN orders JOIN lineitem JOIN nation;
```
- 查詢結果
  此時，執行計畫中各個JOIN的臨時結果如下：
  說明
  查看執行計畫的步驟，請參見使用執行計畫分析查詢。
  1. 表customerJoin表orders，輸出57069行資料至臨時結果集tmp1。
  2. 臨時結果集tmp1Join表lineitem，輸出114705行資料至另一個臨時結果集tmp2。
  3. 臨時結果集tmp2Join表nation，輸出114705行資料作為最終結果。
  3次Join累積輸出結果行數為：57069 + 114705 + 114705 = 286479。
調整後
- 查詢語句
  在SQL語句中添加/*+ reorder_joins=false*/的Hint來關閉AnalyticDB MySQL版的自動調整Join順序功能，並手動調整Join的順序，調整順序後的SQL語句如下：
```
/*reorder_joins=false*/
SELECT   c_custkey,
         c_name,
         Sum(l_extendedprice * (1 - l_discount)) AS revenue,
         c_acctbal,
         n_name,
         c_address,
         c_phone,
         c_comment
FROM     customer c,
         orders o,
         nation n,
         lineitem l
WHERE    c_custkey = o_custkey
AND      c_nationkey = n_nationkey
AND      l_orderkey = o_orderkey
AND      o_orderdate >= date '1993-10-01'
AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
AND      l_returnflag = 'R'
GROUP BY c_custkey,
         c_name,
         c_acctbal,
         c_phone,
         n_name,
         c_address,
         c_comment
ORDER BY revenue DESC
LIMIT    20;
```
- Join順序
  如果按照如上SQL的書寫方式，Join的順序應該是：
```
customer JOIN orders JOIN nation JOIN lineitem
```
- 查詢結果
  此時，執行計畫中各個JOIN的臨時結果如下：
  說明
  查看執行計畫的步驟，請參見使用執行計畫分析查詢。
  1. 表customerJoin表orders，輸出57069行資料至臨時結果集tmp1。
  2. 臨時結果集tmp1Join表nation，輸出57069行資料至臨時結果集tmp2。
  3. 臨時結果集tmp2Join表lineitem，輸出114705行資料作為最終結果。
  3次Join累積輸出結果行數為：57069 + 57069 + 114705 = 228843。
  相較於Join順序調整前（輸出286479行資料），減少了20%的輸出行數。從上述對比結果中可以看出，不同的Join順序會影響中間臨時結果集的大小，因此若發現AnalyticDB MySQL版的Join順序不優，您可以手動調整Join順序來提升SQL查詢效能。