彙總最佳化原理之HashAgg SortAgg與兩階段彙總-雲原生資料庫 PolarDB-阿里雲

本文介紹了PolarDB-X如何最佳化和執行彙總計算，以達到減少資料轉送量和提高執行效率的效果。

基本概念

彙總操作（Aggregate，簡稱Agg）表示按照GROUP BY指定列對輸入資料進行彙總的計算，或者不分組對所有資料進行彙總的計算。PolarDB-X支援以下彙總函式：

COUNT
SUM
AVG
MAX
MIN
BIT_OR
BIT_XOR
GROUP_CONCAT

本文介紹均為不下推的Agg的實現。如果已被下推到LogicalView中，則由儲存層MySQL來選擇執行方式，彙總運算（Agg）由兩種主要的運算元HashAgg和SortAgg實現。

彙總（Agg）運算元

HashAgg

HashAgg利用雜湊表實現彙總：

根據輸入行的分組列的值，通過Hash找到對應的分組。
按照指定的彙總函式，對該行進行彙總計算。
重複以上步驟直到處理完所有的輸入行，最後輸出彙總結果。

explain select count(*) from t1 join t2 on t1.id = t2.id group by t1.name,t2.name;

返回資訊如下：

Project(count(*)="count(*)")
  HashAgg(group="name,name0", count(*)="COUNT()")
    BKAJoin(condition="id = id", type="inner")
      Gather(concurrent=true)
        LogicalView(tables="t1", shardCount=2, sql="SELECT `id`, `name` FROM `t1` AS `t1`")
      Gather(concurrent=true)
        LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT `id`, `name` FROM `t2` AS `t2` WHERE (`id` IN ('?'))")

HashAgg運算元包含以下關鍵資訊：

group：表示GROUP BY欄位，樣本中該欄位為name，name0分別引用t1、t2表的name列，當存在相同別名會通過尾碼數字區分。
彙總函式：等號（=）前為彙總函式對應的輸出資料行名，其後為對應的計算方法。對應樣本中count(*)="COUNT()" ，第一個count(*) 對應輸出的列名，隨後的COUNT()表示對其輸入資料進行計數。

HashAgg可以通過如下Hint語句來關閉：

/*+TDDL:cmd_extra(ENABLE_HASH_AGG=false)*/

SortAgg

SortAgg在輸入資料已按分組列排序的情況下，對各個分組依次完成彙總。

保證輸入按指定的分組列排序（例如，可能會看到MergeSort或MemSort）。
逐行讀入輸入資料，如果分組與當前分組相同，則對其進行彙總計算。
如果分組與當前分組不同，則輸出當前分組上的彙總結果。

相比HashAgg，SortAgg每次只要處理一個分組，記憶體消耗很小；HashAgg需要把所有分組儲存在記憶體中，會消耗較多的記憶體。

explain select count(*) from t1 join t2 on t1.id = t2.id group by t1.name,t2.name order by t1.name, t2.name;

返回資訊如下：

Project(count(*)="count(*)")
  MemSort(sort="name ASC,name0 ASC")
    HashAgg(group="name,name0", count(*)="COUNT()")
      BKAJoin(condition="id = id", type="inner")
        Gather(concurrent=true)
          LogicalView(tables="t1", shardCount=2, sql="SELECT `id`, `name` FROM `t1` AS `t1`")
        Gather(concurrent=true)
          LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT `id`, `name` FROM `t2` AS `t2` WHERE (`id` IN ('?'))")

SortAgg可以通過如下Hint語句來關閉：

/*+TDDL:cmd_extra(ENABLE_SORT_AGG=false)*/

兩階段彙總最佳化

將彙總運算拆分為部分彙總（Partial Agg）和最終彙總（Final Agg）兩個階段，先對部分結果集做彙總，然後將這些部分彙總結果匯總，得到整體彙總的結果，此過程稱為兩階段彙總。

如下樣本的SQL中，HashAgg中拆分出的部分彙總（PartialAgg）會被下推至MySQL上的各個分表，而其中的AVG函數也被拆分成SUM和COUNT以實現兩階段的計算：

explain select avg(age) from t2 group by name

返回資訊如下：

Project(avg(age)="sum_pushed_sum / sum_pushed_count")
  HashAgg(group="name", sum_pushed_sum="SUM(pushed_sum)", sum_pushed_count="SUM(pushed_count)")
    Gather(concurrent=true)
      LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT `name`, SUM(`age`) AS `pushed_sum`, COUNT(`age`) AS `pushed_count` FROM `t2` AS `t2` GROUP BY `name`")

兩階段彙總的最佳化能大大減少資料轉送量，提高執行效率。

總的來說，大部分情境做彙總的時候都傾向於選擇HashAgg，只有以下情境下才適合選擇SortAgg做彙總：

資料比較多，記憶體嚴重不足。
彙總運算元的輸入已經按照Group By列做好排序，這樣做SortAgg就不需要額外排序，執行效率會更高。
當資料有嚴重傾斜，導致HashAgg執行效率不高，優先使用SortAgg。