JOIN を最小化して最適化する
ClickHouse は多様な JOIN の種類とアルゴリズムをサポートしており、最近のリリースで JOIN のパフォーマンスは大幅に改善されています。とはいえ、JOIN は本質的に、単一の非正規化済みテーブルからのクエリよりも高コストです。非正規化は、計算処理をクエリ実行時から挿入時または事前処理時に移すものであり、多くの場合、実行時のレイテンシを大幅に低減します。リアルタイムもしくはレイテンシに敏感な分析クエリに対しては、非正規化を強く推奨します。
一般に、次のような場合は非正規化します:
- テーブルの変更がまれ、またはバッチによるリフレッシュが許容できる場合。
- リレーションが多対多ではない、またはカーディナリティが過度に高くない場合。
- クエリされるカラムが限定的であり、すなわち特定のカラムを非正規化から除外できる場合。
- Flink のような上流システムに処理をオフロードし、リアルタイムなエンリッチメントやフラット化を管理できる場合。
すべてのデータを非正規化する必要はありません。頻繁にクエリされる属性に重点を置いてください。また、サブテーブル全体を重複させる代わりに、集計をインクリメンタルに計算する マテリアライズドビュー の利用も検討してください。スキーマ更新がまれでレイテンシが重要な場合、非正規化は最良のパフォーマンス上のトレードオフを実現できます。
ClickHouse におけるデータ非正規化の詳細なガイドは こちら を参照してください。
JOIN が必要な場合
JOIN が必要な場合は、少なくともバージョン 24.12、可能であれば最新バージョンを使用していることを確認してください。JOIN のパフォーマンスは新しいリリースごとに継続的に改善されています。ClickHouse 24.12 以降では、クエリプランナーが、最適なパフォーマンスのために小さいテーブルを自動的に JOIN の右側に配置するようになりました。これは以前は手動で行う必要があった作業です。今後さらに、より積極的なフィルタのプッシュダウンや、複数の JOIN の自動並べ替えなど、多くの改善が予定されています。
JOIN のパフォーマンスを改善するには、次のベストプラクティスに従ってください:
- デカルト積を避ける: 左側の値が右側の複数の値にマッチする場合、JOIN はいわゆるデカルト積として複数行を返します。ユースケースで右側からのすべての一致が不要で、いずれか 1 件の一致だけでよい場合は、
ANYJOIN（例:LEFT ANY JOIN）を使用できます。これらは通常の JOIN より高速で、メモリ使用量も少なくなります。 - JOIN 対象テーブルのサイズを削減する: JOIN の実行時間とメモリ消費は、左テーブルと右テーブルのサイズに比例して増加します。JOIN によって処理されるデータ量を減らすには、クエリの
WHERE句またはJOIN ON句に追加のフィルタ条件を指定します。ClickHouse はフィルタ条件をクエリプラン内で可能な限り深い位置、通常は JOIN の前までプッシュダウンします。何らかの理由でフィルタが自動的にプッシュダウンされない場合は、JOIN の片側をサブクエリとして書き換えることで、プッシュダウンを強制できます。 - 適切な場合はディクショナリを用いた direct JOIN を使用する: ClickHouse における標準的な JOIN は 2 フェーズで実行されます。まずビルドフェーズで右側を走査してハッシュテーブルを構築し、その後プローブフェーズで左側を走査し、ハッシュテーブル参照によって JOIN パートナーを探索します。右側が dictionary や、キー・バリュー特性を持つ別のテーブルエンジン（例: EmbeddedRocksDB や Join table engine）である場合、ClickHouse はハッシュテーブルの構築を不要にする「direct」 JOIN アルゴリズムを利用でき、クエリ処理を高速化できます。これは
INNERおよびLEFT OUTERJOIN で機能し、リアルタイム分析ワークロードにおいて推奨されます。 - JOIN にテーブルソートを活用する: ClickHouse の各テーブルは、そのテーブルのプライマリキー列によってソートされています。
full_sorting_mergeやpartial_mergeのような、いわゆるソートマージ JOIN アルゴリズムを使用することで、テーブルのソート順を活用できます。ハッシュテーブルに基づく標準的な JOIN アルゴリズム（下記参照:parallel_hash,hash,grace_hash）とは異なり、ソートマージ JOIN アルゴリズムは、まず両テーブルをソートし、その後マージを行います。クエリが両テーブルをそれぞれのプライマリキー列で JOIN する場合、ソートマージにはソート処理を省略する最適化があり、その分の処理時間とオーバーヘッドを削減できます。 - ディスクへのスピルを伴う JOIN を避ける: JOIN の中間状態（例: ハッシュテーブル）が非常に大きくなり、メインメモリに収まりきらない場合があります。この状況では、ClickHouse はデフォルトで out-of-memory エラーを返します。一部の JOIN アルゴリズム（下記参照。例えば
grace_hash、partial_merge、full_sorting_merge）は、中間状態をディスクにスピルしつつクエリ実行を継続できます。ただし、ディスクアクセスは JOIN 処理を大きく低速化し得るため、これらのアルゴリズムを使用する際は注意が必要です。代わりに、JOIN クエリ自体を他の方法で最適化し、中間状態のサイズを削減することを推奨します。 - 外部 JOIN における不一致のマーカーとしてのデフォルト値: left/right/full outer join では、左/右/両方のテーブルからすべての値が含まれます。ある値に対して他方のテーブル側に JOIN パートナーが見つからない場合、ClickHouse は JOIN パートナーを特別なマーカーに置き換えます。SQL 標準では、このようなマーカーとしてデータベースは NULL を使用することが求められています。ClickHouse でこれを行うには、結果列を Nullable でラップする必要があり、その分メモリおよびパフォーマンスのオーバーヘッドが発生します。代替手段として、設定
join_use_nulls = 0を構成し、結果列のデータ型のデフォルト値をマーカーとして使用できます。
ClickHouse で JOIN に辞書を使用する場合、設計上、辞書は重複キーを許可しないことを理解しておくことが重要です。データ読み込み時には、重複するキーは自動的に重複排除され、そのキーに対して最後に読み込まれた値だけが保持されます。この挙動により、辞書は最新または権威となる値のみが必要な 1 対 1 や多対 1 の関係に最適です。
一方で、1 対多や多対多の関係（例: 1 人の俳優が複数の役を持つような、役と俳優の JOIN）に辞書を使用すると、マッチした行のうち 1 行を除いてすべてが破棄され、気付かないうちにデータが失われます。結果として、辞書は複数マッチにわたって完全なリレーショナル整合性が求められるシナリオには適していません。
正しい JOIN アルゴリズムの選択
ClickHouse は、速度とメモリ使用量のトレードオフが異なる複数の JOIN アルゴリズムをサポートしています。
- Parallel Hash JOIN (デフォルト): 右側のテーブルが小〜中規模でメモリに収まる場合に高速です。
- Direct JOIN:
INNERやLEFT ANY JOINと共にディクショナリ（またはキー・バリュー特性を持つ他のテーブルエンジン）を使用する場合に最適です。ハッシュテーブルを構築する必要がないため、ポイントルックアップに対して最速の手法です。 - Full Sorting Merge JOIN: 両方のテーブルが結合キーでソートされている場合に効率的です。
- Partial Merge JOIN: メモリ使用量を最小限に抑えますが低速です。メモリが限られた環境で大規模テーブル同士を結合する場合に最適です。
- Grace Hash JOIN: 柔軟でメモリ使用量を調整可能であり、パフォーマンス特性をチューニングしながら大規模データセットを処理するのに適しています。
各アルゴリズムは対応している JOIN の種類が異なります。各アルゴリズムがサポートする JOIN タイプの一覧はこちらを参照してください。
join_algorithm = 'auto'（デフォルト）を設定して ClickHouse に最適なアルゴリズムを自動選択させることも、ワークロードに基づいて明示的に制御することもできます。パフォーマンスやメモリオーバーヘッドを最適化するために特定の JOIN アルゴリズムを選択する必要がある場合は、このガイドを参照してください。
最適なパフォーマンスを得るために:
- 高性能ワークロードでは JOIN の回数を可能な限り少なく保ちます。
- 1 クエリあたり 3〜4 回を超える JOIN は避けます。
- 実データで異なるアルゴリズムをベンチマークしてください。パフォーマンスは JOIN キーの分布やデータサイズに応じて変動します。
JOIN 最適化戦略、JOIN アルゴリズム、およびそのチューニング方法の詳細については、ClickHouse ドキュメント と、このブログシリーズ を参照してください。
