ClickHouseにおけるJOINの利用

ClickHouseは完全なJOINサポートを提供しており、多くの結合アルゴリズムを利用可能です。パフォーマンスを最大化するために、このガイドに掲載されている結合最適化の提案に従うことをお勧めします。

• 最適なパフォーマンスを得るために、ユーザーはクエリ内のJOINの数を減らすことを目指すべきです。特にミリ秒単位のパフォーマンスが求められるリアルタイム分析のワークロードでは、その傾向が顕著です。クエリ内の結合は最大で3〜4個に抑えることをお勧めします。デノーマライゼーション、辞書、およびマテリアライズドビューを含むデータモデリングセクションで、結合を最小化するための変更について詳述しています。
• 現在、ClickHouseは結合の順序を変更しません。常に最小のテーブルをJOINの右側に配置することを確認してください。これにより、ほとんどの結合アルゴリズムでメモリに保持され、クエリのメモリオーバーヘッドが最小限に抑えられます。
• もしクエリが直接結合を必要とする場合、すなわちLEFT ANY JOINが必要な場合、可能であれば辞書を使用することをお勧めします。

• 内部結合を行う場合は、IN句を使用してサブクエリとして書く方が最適です。以下のクエリは機能的に同等ですが、どちらも質問にClickHouseについての言及はないが、commentsには言及があるpostsの数を数えます。

ANY INNER JOINを使用していることに注意してください。単なるINNER結合ではなく、デカルト積を望まないため、すなわち各投稿に対して1つの一致のみを希望しています。

この結合はサブクエリを使用して書き直すことができ、パフォーマンスが劇的に向上します：

ClickHouseは条件をすべての結合句とサブクエリに適用しようとしますが、ユーザーは常に可能な限りすべてのサブ句に条件を手動で適用することをお勧めします。これによりJOINするデータのサイズを最小限に抑えることができます。以下の例を考えてみましょう。ここでは2020年以降のJava関連の投稿へのアップボートの数を計算したいとします。

左側に大きなテーブルがあるという単純なクエリは、56秒で完了します：

この結合の順序を変更すると、パフォーマンスが劇的に向上し、1.5秒になります：

右側のテーブルにフィルターを追加すると、パフォーマンスはさらに改善され、0.5秒になります。

このクエリは、先ほど指摘したように、INNER JOINをサブクエリに移動することでさらに改善できます。外部および内部の両方のクエリでフィルターを維持します。

結合アルゴリズムの選定

ClickHouseは多くの結合アルゴリズムをサポートしています。これらのアルゴリズムは通常、パフォーマンスとメモリ使用量をトレードオフします。以下に、相対的なメモリ消費量と実行時間に基づくClickHouseの結合アルゴリズムの概要を示します。

これらのアルゴリズムは、結合クエリの計画と実行の方法を指定します。デフォルトでは、ClickHouseは結合タイプおよび結合テーブルのエンジンに基づいて、直接結合またはハッシュ結合アルゴリズムを使用します。 あるいは、ClickHouseは実行時にリソースの使用状況や可用性に応じて、動的に結合アルゴリズムを選択して変更するように構成できます。join_algorithm=autoの場合、ClickHouseはまずハッシュ結合アルゴリズムを試み、そのアルゴリズムのメモリ制限が違反された場合には、アルゴリズムが自動的に部分マージ結合に切り替わります。どのアルゴリズムが選択されたかは、トレースログを介して確認できます。 ClickHouseはまた、ユーザーが自分で希望する結合アルゴリズムをjoin_algorithm設定を使用して指定することも許可しています。

各結合アルゴリズムでサポートされているJOINタイプは以下に示されており、最適化の際に考慮する必要があります。

各JOINアルゴリズムの詳細な説明はこちらにあり、利点、欠点、およびスケーリング特性が含まれています。

適切な結合アルゴリズムの選択は、メモリの最適化を期待するか、パフォーマンスの最適化を期待するかによります。

JOINパフォーマンスの最適化

キーとしている最適化指標がパフォーマンスであり、可能な限り迅速に結合を実行したい場合は、次の意思決定ツリーを使用して適切な結合アルゴリズムを選択できます：

• (1) 右側のテーブルからデータをメモリ内の低遅延キー・バリュー構造にプリロードできる場合、例えば辞書の場合、結合キーが基礎となるキー・バリュー・ストレージのキー属性と一致する場合、かつLEFT ANY JOINのセマンティクスが適切である場合には、直接結合が適用され、最も早いアプローチを提供します。

• (2) テーブルの物理行順序が結合キーのソート順と一致する場合、次は条件によります。この場合、完全ソートマージ結合はスキップしたソートフェーズにより、メモリ使用量が大幅に削減され、データサイズや結合キーの値の分布に応じて、いくつかのハッシュ結合アルゴリズムよりも速い実行時間を得られます。

• (3) 右のテーブルがメモリに収まる場合でも、並列ハッシュ結合の追加メモリ使用オーバーヘッドを考慮する必要があります。その場合、このアルゴリズムまたはハッシュ結合の方が速くなります。データサイズ、データ型、および結合キーのカラムの値の分布に依存します。

• (4) 右のテーブルがメモリに収まらない場合、再び条件によります。ClickHouseは3つのメモリに制約のない結合アルゴリズムを提供しており、すべて一時的にデータをディスクに吐き出します。完全ソートマージ結合と部分マージ結合はデータの事前ソートを必要とします。一方、グレースハッシュ結合はデータからハッシュテーブルを構築します。データのボリューム、データタイプ、および結合キーのカラムの値の分布にもよりますが、データからハッシュテーブルを構築する方がデータのソートより速くなるシナリオや、その逆が考えられます。

部分マージ結合は、大きなテーブルを結合する際にメモリ使用量を最小化することに最適化されていますが、その分結合速度が非常に遅くなります。これは特に、左のテーブルの物理行順序が結合キーのソート順と一致しない場合に当てはまります。

グレースハッシュ結合は、3つのメモリに制約のない結合アルゴリズムの中で最も柔軟性が高く、grace_hash_join_initial_buckets設定により、メモリ使用量と結合速度の制御に優れています。データボリュームに応じて、グレースハッシュは部分マージアルゴリズムよりも速くなることがあり、またその逆もあり得ます。バケットの量が、両方のアルゴリズムのメモリ使用量がほぼ一致するように選ばれている場合、グレースハッシュ結合のメモリ使用量が完全ソートマージのメモリ使用量とほぼ一致するように設定されている場合、私たちのテストでは常に完全ソートマージの方が早かったです。

3つのメモリに制約のないアルゴリズムのうち、どれが最も速いかは、データの量、データ型、および結合キーのカラムの値の分布によって異なります。最も速いアルゴリズムを特定するには、現実的なデータ量で実際のデータを使用してベンチマークを実行することが最善です。

メモリの最適化

最も速い実行時間よりも最低のメモリ使用量を最適化したい場合は、代わりに次の意思決定ツリーを使用できます。

• (1) テーブルの物理行順序が結合キーのソート順と一致する場合、完全ソートマージ結合のメモリ使用量は最低限になります。ソートフェーズが無効にされているため、結合速度も良好です。
• (2) グレースハッシュ結合は、結合スピードの対価として構成された多くのバケットによって非常に低いメモリ使用量に調整できます。部分マージ結合は意図的に主メモリを低容量で使用します。外部ソートが有効になっている完全ソートマージ結合は、一般に部分マージ結合よりもメモリを多く使用します（行順序がキーのソート順と一致しない場合であることを前提とします）が、実行時間は大幅に改善されます。

以上に関して追加の詳細を必要とするユーザーには、次のブログシリーズをお勧めします。