Query planning
クエリの分析
クエリのパフォーマンスを最適化する方法はよく聞かれる質問です。遅いクエリはユーザーエクスペリエンスとクラスタのパフォーマンスに影響を与えます。クエリのパフォーマンスを分析して最適化することが重要です。
fe/log/fe.audit.logでクエリ情報を表示することができます。各クエリにはQueryID が対応しており、クエリのQueryPlanとProfileを検索するために使用できます。QueryPlanはSQL文を解析してFEによって生成された実行プランです。ProfileはBEの実行結果であり、各ステップの実行時間や各ステップで処理されるデータの量などの情報を含んでいます。
プランの分析
StarRocksでは、SQL文のライフサイクルをクエ リ解析、クエリプランニング、クエリ実行の3つのフェーズに分けることができます。クエリ解析は一般的にボトルネックではありません。なぜなら、分析ワークロードの必要なQPSは高くないためです。
StarRocksでのクエリのパフォーマンスは、クエリプランニングとクエリ実行によって決まります。クエリプランニングはオペレータ（Join/Order/Aggregate）を調整する役割を担い、クエリ実行は具体的な操作を実行する役割を担います。
クエリプランはDBAにとってクエリ情報へのマクロなアクセス手段を提供します。クエリプラン はクエリのパフォーマンスの鍵であり、DBAが参照するための良いリソースです。次のコードスニペットは、TPCDS query96を使用してクエリプランを表示する方法を示しています。
-- query96.sql
select count(*)
from store_sales
,household_demographics
,time_dim
, store
where ss_sold_time_sk = time_dim.t_time_sk
and ss_hdemo_sk = household_demographics.hd_demo_sk
and ss_store_sk = s_store_sk
and time_dim.t_hour = 8
and time_dim.t_minute >= 30
and household_demographics.hd_dep_count = 5
and store.s_store_name = 'ese'
order by count(*) limit 100;
クエリプランには、論理クエリプランと物理クエリプランの2つのタイプがあります。ここで説明するクエリプランは論理クエリプランを指します。TPCDS query96.sqlに対応するクエリプランは以下の通りです。
+------------------------------------------------------------------------------+
| Explain String |
+------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 11> |
| PARTITION: UNPARTITIONED |
| RESULT SINK |
| 12:MERGING-EXCHANGE |
| limit: 100 |
| tuple ids: 5 |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| STREAM DATA SINK |
| EXCHANGE ID: 12 |
| UNPARTITIONED |
| |
| 8:TOP-N |
| | order by: <slot 11> ASC |
| | offset: 0 |
| | limit: 100 |
| | tuple ids: 5 |
| | |
| 7:AGGREGATE (update finalize) |
| | output: count(*) |
| | group by: |
| | tuple ids: 4 |
| | |
| 6:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: left hash join node can not do colocate |
| | equal join conjunct: `ss_store_sk` = `s_store_sk` |
| | tuple ids: 0 2 1 3 |
| | |
| |----11:EXCHANGE |
| | tuple ids: 3 |
| | |
| 4:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: left hash join node can not do colocate |
| | equal join conjunct: `ss_hdemo_sk`=`household_demographics`.`hd_demo_sk`|
| | tuple ids: 0 2 1 |
| | |
| |----10:EXCHANGE |
| | tuple ids: 1 |
| | |
| 2:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: table not in same group |
| | equal join conjunct: `ss_sold_time_sk` = `time_dim`.`t_time_sk` |
| | tuple ids: 0 2 |
| | |
| |----9:EXCHANGE |
| | tuple ids: 2 |
| | |
| 0:OlapScanNode |
| TABLE: store_sales |
| PREAGGREGATION: OFF. Reason: `ss_sold_time_sk` is value column |
| partitions=1/1 |
| rollup: store_sales |
| tabletRatio=0/0 |
| tabletList= |
| cardinality=-1 |
| avgRowSize=0.0 |
| numNodes=0 |
| tuple ids: 0 |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 11 |
| UNPARTITIONED |
| |
| 5:OlapScanNode |
| TABLE: store |
| PREAGGREGATION: OFF. Reason: null |
| PREDICATES: `store`.`s_store_name` = 'ese' |
| partitions=1/1 |
| rollup: store |
| tabletRatio=0/0 |
| tabletList= |
| cardinality=-1 |
| avgRowSize=0.0 |
| numNodes=0 |
| tuple ids: 3 |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| STREAM DATA SINK |
| EXCHANGE ID: 10 |
| UNPARTITIONED |
| |
| 3:OlapScanNode |
| TABLE: household_demographics |
| PREAGGREGATION: OFF. Reason: null |
| PREDICATES: `household_demographics`.`hd_dep_count` = 5 |
| partitions=1/1 |
| rollup: household_demographics |
| tabletRatio=0/0 |
| tabletList= |
| cardinality=-1 |
| avgRowSize=0.0 |
| numNodes=0 |
| tuple ids: 1 |
| |
| PLAN FRAGMENT 4 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| STREAM DATA SINK |
| EXCHANGE ID: 09 |
| UNPARTITIONED |
| |
| 1:OlapScanNode |
| TABLE: time_dim |
| PREAGGREGATION: OFF. Reason: null |
| PREDICATES: `time_dim`.`t_hour` = 8, `time_dim`.`t_minute` >= 30 |
| partitions=1/1 |
| rollup: time_dim |
| tabletRatio=0/0 |
| tabletList= |
| cardinality=-1 |
| avgRowSize=0.0 |
| numNodes=0 |
| tuple ids: 2 |
+------------------------------------------------------------------------------+
128 rows in set (0.02 sec)
クエリ96は、複数のStarRocksのコンセプトを含むクエリプランを示しています。
名前 | 説明 |
|---|---|
avgRowSize | スキャンされたデータ行の平均サイズ |
cardinality | スキャンされたテーブルのデータ行の合計数 |
colocate | テーブルがコロケートモードにあるかどうか |
numNodes | スキャンするノードの数 |
rollup | マテリアライズドビュー |
preaggregation | プレ処理 |
predicates | プレディケート、クエリのフィルタ |
クエリ96のクエリプランは、0から4までの5つのフラグメントに分かれています。クエリプランは、下部から順番に読むことができます。
フラグメント4では、time_dimテーブルのスキャンと関連するクエリ条件（ つまり、time_dim.t_hour = 8および time_dim.t_minute >= 30）の事前実行を担当します。このステップはプレディケートプッシュダウンとも呼ばれます。StarRocksは集計テーブルに対してPREAGGREGATIONを有効にするかどうかを決定します。前の図では、time_dimのプレ処理は無効になっています。この場合、time_dimのすべてのディメンション列が読み込まれるため、テーブルに多くのディメンション列がある場合はパフォーマンスに影響を与える可能性があります。time_dimテーブルがデータの分割にrange partitionを選択する場合、クエリプランでいくつかのパーティションがヒットし、関係のないパーティションが自動的にフィルタリングされます。マテリアライズドビューがある場合、StarRocksはクエリに基づいて自動的にマテリアライズドビューを選択します。マテリアライズドビューが存在しない場合、クエリはベーステーブル（たとえば、前の図のrollup: time_dim）を自動的にヒットします。
スキャンが完了すると、フラグメント4は終了します。データは他のフ ラグメントに渡され、前の図のEXCHANGE ID : 09で示されているように、受信ノード9に送信されます。
クエリ96のクエリプランでは、フラグメント2、3、および4は似たような機能を持っていますが、異なるテーブルをスキャンする役割を担っています。具体的には、クエリのOrder/Aggregation/Join操作はフラグメント1で実行されます。
フラグメント1では、BROADCASTメソッドを使用してOrder/Aggregation/Join操作を行います。つまり、小さなテーブルを大きなテーブルにブロードキャストします。両方のテーブルが 大きい場合は、SHUFFLEメソッドを使用することをお勧めします。現在、StarRocksはHASH JOINのみをサポートしています。colocateフィールドは、2つの結合対象テーブルが同じようにパーティショニングおよびバケットを行っていることを示すために使用されます。これにより、データの移行を行わずに結合操作をローカルで実行することができます。Join操作が完了すると、上位レベルのaggregation、order by、およびtop-n操作が実行されます。
具体的な式を削除して、オペレータのみを残すことで、クエリプランをよりマクロな視点で表示することができます。以下の図に示すように。
クエリヒント
クエリヒントは、クエリオプティマイザにクエリの実行方法を明示的に示す指令またはコメントです。現在、StarRocksは2つのタイプのヒント、すなわち変数設定ヒントと結合ヒントをサポートしています。ヒントは単一のクエリ内でのみ効果があります。
変数設定ヒント
SET_VARヒントの形式で、SELECTおよびSUBMIT TASKステートメント、または他のステートメントに含まれるSELECT節内で、1つまたは複数のシステム変数を設定することができます。
構文
[...] SELECT [/*+ SET_VAR(key=value [, key = value]*) */] ...
SUBMIT [/*+ SET_VAR(key=value [, key = value]*) */] TASK ...
例
集計クエリの集計方法にシステム変数streaming_preaggregation_modeおよびnew_planner_agg_stageを設定することによってヒントを指定します。
SELECT /*+ SET_VAR (streaming_preaggregation_mode = 'force_streaming',new_planner_agg_stage = '2') */ SUM(sales_amount) AS total_sales_amount FROM sales_orders;
ステートメントの実行タイムアウトの期限をquery_timeoutシステム変数で指定するためにSUBMIT TASKステートメントでヒントを指定します。
SUBMIT /*+ SET_VAR(query_timeout=3) */ TASK AS CREATE TABLE temp AS SELECT count(*) AS cnt FROM tbl1;
マテリアライズドビューを作成するときにSELECT節内のシステム変数query_timeoutでクエリの実行タイムアウトの期限を指定するためにヒントを指定します。
CREATE MATERIALIZED VIEW mv
PARTITION BY dt
DISTRIBUTED BY HASH(`key`)
BUCKETS 10
REFRESH ASYNC
AS SELECT /*+ SET_VAR(query_timeout=500) */ * from dual;
結合ヒント
複数のテーブルを結合するクエリの場合、オプティマイザは通常、最適な結合実行方法を選択します。特殊なケースでは、結合ヒントを使用して、結合実行方法をオプティマイザに明示的に示すか、Joinの順序を無効にすることができます。現在、結合ヒントはShuffle Join、Broadcast Join、Bucket Shuffle Join、またはColocate Joinを結合実行方法として提案することができます。結合ヒントを使用する場合、オプティマイザはJoin Reorderを実行しません。そのため、右テーブルとして小さいテーブルを選択する必要があります。また、Colocate JoinまたはBucket Shuffle Joinを結合実行方法として提案する場合、結合対象テーブルのデータ分布がこれらの結合実行方法の要件を満たしていることを確認してください。そうでない場合、提案された結合実行方法は効果を発揮できません。
構文
... JOIN { [BROADCAST] | [SHUFFLE] | [BUCKET] | [COLOCATE] | [UNREORDER]} ...
注記
結合ヒントは大文字小文字を区別しません。
例
-
Shuffle JoinテーブルAとテーブルBから、結合操作を行う前に同じバケットキー値を持つデータ行 をシャッフルして同じマシンに配置する必要がある場合、結合実行方法をShuffle Joinとしてヒントします。
select k1 from t1 join [SHUFFLE] t2 on t1.k1 = t2.k2 group by t2.k2; -
Broadcast JoinテーブルAが大きなテーブルで、テーブルBが小さなテーブルである場合、結合実行方法をBroadcast Joinとしてヒントします。テーブルBのデータは、テーブルAのデータが存在するマシンに完全にブロードキャストされ、その後に結合操作が行われます。Shuffle Joinに比べて、Broadcast JoinはテーブルAのデータのシャッフルコストを節約します。
select k1 from t1 join [BROADCAST] t2 on t1.k1 = t2.k2 group by t2.k2; -
Bucket Shuffle Join結合クエリのJoin等価結合式がテーブルAのバケット化キーを含み、特にテーブルAとテーブルBの両方が大きなテーブルの場合、結合実行方法をBucket Shuffle Joinとしてヒントします。テーブルBのデータは、テーブルAのデータのデータ分布に従って、テーブルAのデータが存在するマシンにシャッフルされ、その後に結合操作が行われます。Broadcast Joinに比べて、Bucket Shuffle JoinはテーブルBのデータ転送のコストを大幅に削減します。
select k1 from t1 join [BUCKET] t2 on t1.k1 = t2.k2 group by t2.k2; -
Colocate JoinテーブルAとテーブルBが同 じ共有配置グループに属している場合、結合操作でテーブルAとテーブルBのバケットキー値が同じデータ行は同じBEノードに分散されます。Join等価結合式が結合クエリにテーブルAとテーブルBのバケット化キーを含む場合、結合実行方法をColocate Joinとしてヒントします。同じキー値を持つデータが直接ローカルで結合され、ノード間のデータ転送にかかる時間が短縮され、クエリのパフォーマンスが向上します。
select k1 from t1 join [COLOCATE] t2 on t1.k1 = t2.k2 group by t2.k2;
結合実行方法の表示
EXPLAINコマンドを使用して実際の結合実行方法を表示することができます。返された結果が結合ヒントと一致している場合、結合ヒントが有効であることを意味します。
EXPLAIN select k1 from t1 join [COLOCATE] t2 on t1.k1 = t2.k2 group by t2.k2;
SQLフィンガープリント
SQLフィンガープリントは、遅いクエリを最適化し、システムリソースの利用を改善するために使用されます。StarRocksは、SQLフィンガープリント機能を使用して遅いクエリログ（fe.audit.log.slow_query）のSQL文を正規化し、異なるタイプのSQL文に分類し、各SQLタイプのMD5ハッシ ュ値を計算して遅いクエリを識別します。MD5ハッシュ値はDigestフィールドで指定されます。
2021-12-27 15:13:39,108 [slow_query] |Client=172.26.xx.xxx:54956|User=root|Db=default_cluster:test|State=EOF|Time=2469|ScanBytes=0|ScanRows=0|ReturnRows=6|StmtId=3|QueryId=824d8dc0-66e4-11ec-9fdc-00163e04d4c2|IsQuery=true|feIp=172.26.92.195|Stmt=select count(*) from test_basic group by id_bigint|Digest=51390da6b57461f571f0712d527320f4
SQLステートメントの正規化は、ステートメントテキストをより正規化された形式に変換し、重要なステートメント構造のみを保存します。
- オブジェクトID（データベース名やテーブル名など）を保持します。
- 定数を疑問符（?）に変換します。
- コメントを削除し、スペースを整形します。
たとえば、次の2つのSQL文は正規化後に同じタイプに属します。
- 正規化前のSQL文
SELECT * FROM orders WHERE customer_id=10 AND quantity>20
SELECT * FROM orders WHERE customer_id = 20 AND quantity > 100
- 正規化後のSQL文
SELECT * FROM orders WHERE customer_id=? AND quantity>?