集計とソートの最適化 - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、オプティマイザとエグゼキュータを使用してGroup-by演算子とOrder-by演算子を処理し、転送されるデータの量を減らし、実行をより効率的にする方法について説明します。

基本的な概念

Aggregate (Agg) 操作のセマンティクスは、GROUP byで指定された列を使用して入力データを集計するか、グループ化せずにすべてのデータを集計することです。PolarDB-X 1.0は、次の集計関数をサポートしています。

カウント
SUM
AVG
MAX
MIN
BIT_OR
BIT_XOR
GROUP_CONCAT

ソート操作のセマンティクスは、ORDER byで指定された列を使用して入力データをソートすることです。

このトピックで説明する実装は、AggまたはSortのすべての実装です。 AggまたはSort演算子がLogicalViewにプッシュされている場合、ストレージレイヤーMySQLは実行方法を選択します。

Agg

Aggは、HashAggとSortAggの2つの主要な演算子によって実装されます。

HashAgg

HashAggは、集計にハッシュテーブルを使用するために次の手順を実行します。

入力行のグループ化列の値を使用してHashを実行し、対応するグループを見つけます。
指定された集計関数を使用して行を集計します。
すべての入力行が処理されるまで、上記の手順を繰り返します。次に、HashAggは集計結果を生成する。

> t1.id = t2.idグループb y t1.name、t2.nameのt1結合t2からの選択カウント (*) を説明します。プロジェクト (count(*)="count(*)")
  HashAgg(group="name,name0", count(*)="COUNT()")
    BKAJoin(condition="id = id", type="inner")
      収集 (concurrent=true)
        LogicalView(tables="t1", shardCount=2, sql="SELECT 'id', 'name' FROM 't1' AS 't1'")
      収集 (concurrent=true)
        LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT 'id', 'name' FROM 't2' AS 't2' WHERE ('id' IN ('?'))")

Explainステートメントの結果には、HashAgg演算子に次の重要な情報も含まれています。

group: GROUP BYフィールドを示します。この例では、t1テーブルのname列はname,name0のnameで参照され、t2テーブルのname列はname,name0のname0で参照されます。同じエイリアスには区別のための番号が付いています。
集計関数: 等号 (=) は、集計関数に対応する出力列名の後に続き、対応する計算方法が続きます。この例のcount(*)="COUNT()" では、count(*) が出力列名を指定し、COUNT() が入力データをカウントします。

HashAggを無効にするには、Hint: /* + TDDL:cmd_extra(ENABLE_HASH_AGG=false)*/ を使用します。

SortAgg

SortAggは、入力データがグループ化列によってソートされた後、各グループを順番に集約します。

SortAggは、入力データが指定された列でソートされるようにします。たとえば、MergeSortまたはMemSortが表示されます。
SortAggは、入力データを行ごとに読み取ります。グループが現在のグループと同じ場合、データは集約されます。
SortAggは、グループが現在のグループと異なる場合、現在のグループの集計結果を生成します。

HashAggと比較して、SortAggは毎回1つのグループのみを処理する必要があり、メモリ消費量が少なくなります。対照的に、HashAggはすべてのグループをメモリに格納する必要があり、より多くのメモリを消費します。

> t1.id = t2.idグループb y t1.name、t2.name注文b y t1.name、t2.nameのt1結合t2からの選択カウント (*) を説明します。プロジェクト (count(*)="count(*)")
  MemSort(sort="name ASC,name0 ASC")
    HashAgg(group="name,name0", count(*)="COUNT()")
      BKAJoin(condition="id = id", type="inner")
        収集 (concurrent=true)
          LogicalView(tables="t1", shardCount=2, sql="SELECT 'id', 'name' FROM 't1' AS 't1'")
        収集 (concurrent=true)
          LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT 'id', 'name' FROM 't2' AS 't2' WHERE ('id' IN ('?'))")

ヒント: /* + TDDL:cmd_extra(ENABLE_SORT_AGG=false)*/ を使用してSortAggを無効にできます。

二相アグリゲーション最適化

二相凝集は、Aggが部分Agg相と最終Agg相とに分割されるプロセスを指す。部分結果セットは、最初に部分集約結果に集約され、次に、すべての部分集約結果が集約されて全体集約結果が得られる。

次の構造化クエリ言語 (SQL) ステートメントでは、HashAggからのPartialAgg分割がMySQL上の各テーブルシャーディングにプッシュダウンされます。ステートメントでは、AVG関数もSUM関数とCOUNT関数に分割され、2つのフェーズで計算を実行します。

> 名前でt2グループからavg (年齢) を選択説明

プロジェクト (avg(age)="sum_pushed_sum / sum_pushed_count")
  HashAgg(group="name", sum_pushed_sum="SUM(pushed_sum)", sum_pushed_count="SUM(pushed_count)")
    収集 (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' AS 't2' 'GROUP BY 'name'")

2相アグリゲーションは、転送されるデータの量を大幅に削減し、実行をより効率的にします。

ソート

PolarDB-X 1.0のソート演算子には、MemSort、TopN、およびMergeSortが含まれます。

MemSort

PolarDB-X 1.0のソートは、通常、メモリ内のクイックソートアルゴリズムを実行するMemSort演算子として実装されます。

次の例では、MemSort演算子を使用します。

> t1.id = t2.idのt1結合t2からの選択t t1.nameを説明します。b y t1.name、t2.name;

プロジェクト (name="name")
  MemSort(sort="name ASC,name0 ASC")
    プロジェクト (name="name", name0="name0")
      BKAJoin(condition="id = id", type="inner")
        収集 (concurrent=true)
          LogicalView(tables="t1", shardCount=2, sql="SELECT 'id', 'name' FROM 't1' AS 't1'")
        収集 (concurrent=true)
          LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT 'id', 'name' FROM 't2' AS 't2' WHERE ('id' IN ('?'))")

TopN

ORDER BYとLIMITの両方がSQLに表示されると、Sort演算子とLimit演算子がTopN演算子として組み合わされます。

TopN演算子は、最大ヒープまたは最小ヒープを維持します。ヒープは、ソートキーの値を使用して、常に最大N行のデータと最小N行のデータを保持します。すべての入力データが処理されると、ヒープに残っているN行以下が必要な結果になります。

次の例では、TopN演算子を使用します。

> t1.id = t2.id注文b y t1.name、t2.name制限10のt1結合t2からのt t1.nameを説明します。プロジェクト (name="name")
  TopN(sort="name ASC,name0 ASC", offset=0, fetch=? 0)
    プロジェクト (name="name", name0="name0")
      BKAJoin(condition="id = id", type="inner")
        収集 (concurrent=true)
          LogicalView(tables="t1", shardCount=2, sql="SELECT 'id', 'name' FROM 't1' AS 't1'")
        収集 (concurrent=true)
          LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT 'id', 'name' FROM 't2' AS 't2' WHERE ('id' IN ('?'))")

MergeSort
一般に、セマンティクスが許せば、SQLのソート操作は、PolarDB − X 1.0を実行するためにMySQLにプッシュダウンされ、実行層は、最終的なマージ操作のみを実行する。これはMergeSortによって実装されます。厳密に言えば、MergeSortはソート演算子であるだけでなく、データ再配布演算子でもあります。 MergeSortはGather演算子に似ています。
次のSQL文は、t1テーブルをソートします。 PolarDB-X 1.0のクエリオプティマイザがSQL文を最適化すると、Sort演算子が各MySQLシャードにプッシュされ、マージ操作は上位レイヤーでのみ実行されます。
```
> 名前によってt1順序からの選択の名前を説明して下さい;

MergeSort(sort="名前ASC")
  LogicalView(tables="t1", shardCount=2, sql="SELECT 'name' FROM 't1' AS 't1' ORDER BY 'name'") 
```
MemSortと比較して、MergeSortアルゴリズムは、PolarDB-X 1.0のメモリ消費量をPolarDB-X 1.0削減し、MySQLレイヤーのコンピューティング機能を最大限に活用できます。

最適化の組み合わせの例

最適化の組み合わせの次の例では、次の最適化ルールが適用されます。

AggはJoinを通じて押し下げられます。
結合アルゴリズムとしてSortMergeJoinが選択されている
SortAggがAggアルゴリズムとして選択される
SortAggの整然とした出力は、SortMergeJoinに必要なソートに使用されます。
二相Agg
Aggプッシュダウン
プッシュダウンの並べ替え

> 説明する選択カウント (*) からt1結合t2 o n t1.name = t2.nameグループb y t1.name;

プロジェクト (count(*)="count(*) * count(*)0")
  SortMergeJoin(condition="name = name", type="inner")
    SortAgg(group="name", count(*)="SUM(count(*))")
      MergeSort(sort="名前ASC")
        LogicalView(tables="t1", shardCount=2, sql="SELECT 'name', COUNT(*) AS 'count(*)'FROM 't1' AS 't1' GROUP BY 'name' ORDER BY 'name'")
    SortAgg(group="name", count(*)="SUM(count(*))")
      MergeSort(sort="名前ASC")
        LogicalView(tables="t2_[0-3]", shardCount=4, sql="SELECT 'name', COUNT(*) AS 'count(*)'FROM 't2' AS 't2' GROUP BY 'name' ORDER BY 'name'")