メインコンテンツへスキップ
このページでは、可能な限り JOIN を避け、JOIN が必要な場合にはそのコストを抑えるための具体的な指針を示します。構文とセマンティクスについては、JOIN 句リファレンスを参照してください。実行アルゴリズムとその選択方法については、テーブルの結合を参照してください。

JOINはできるだけ避ける

ClickHouse は多種多様な JOINの種類とアルゴリズムをサポートしています。ただし、JOIN は本質的に、単一の非正規化されたテーブルに対するクエリよりもコストが高くなります。非正規化では、計算処理をクエリ実行時から INSERT 時または前処理時に移すため、実行時のレイテンシを大幅に低減できることがよくあります。リアルタイム処理やレイテンシに敏感な分析クエリでは、非正規化を強く推奨します。 一般に、次のような場合は非正規化を検討してください。
  • テーブルの更新頻度が低い、またはバッチ更新を許容できる。
  • 関係が多対多ではなく、カーディナリティも極端に高くない。
  • クエリされるカラムが一部に限られており、つまり特定のカラムは非正規化の対象から外せる。
  • Flink のような上流システムに処理を移し、リアルタイムの enrichment やフラット化を管理できる体制がある。
すべてのデータを非正規化する必要はありません。頻繁にクエリされる属性に絞って対応してください。また、サブテーブル全体を複製する代わりに、集計をインクリメンタルに計算する方法として materialized view も検討してください。スキーマ更新がまれで、レイテンシが重要な場合、非正規化はパフォーマンス面で最も優れたトレードオフをもたらします。 ClickHouse でのデータの非正規化について詳しくは、非正規化 のガイドを参照してください。

JOIN が必要な場合

ClickHouse 26.8 以降を使用してください。複数テーブルの JOIN の順序変更と、オプティマイザによる利用可能な統計情報の活用は、25.12 以降デフォルトで有効になっています。カラム統計は 26.3 で一般提供となり、デフォルトで有効になりました。26.8 以降は、対象となるテーブルで挿入時にカラム統計をマテリアライズする動作がデフォルトとなり、コストベースの JOIN 最適化における推定精度が向上しています。
JOIN のパフォーマンスを向上させるには、以下のベストプラクティスに従ってください。
  • 一致が 1 件あれば十分な場合は ANY JOIN を使用します。 通常の JOIN は一致するすべての行を返すため、キーが重複しているとデカルト積が生じる可能性があります。1 件の一致で十分な場合は、LEFT ANY JOIN または ANY INNER JOIN を使用して、結果のサイズとメモリ使用量を削減してください。
  • JOIN の前に両側のデータを絞り込みます。 WHERE 句および JOIN ON 句で選択性の高い条件を適用してください。ClickHouse は可能な限りフィルタをプッシュダウンしますが、明示的にサブクエリを使用すれば、JOIN の前に各入力を確実にフィルタできます。
  • INNER JOIN の代わりに IN を検討します。 相手テーブルにキーが存在するかどうかを確認するだけでよいクエリでは、IN サブクエリを使うとその意図を直接表現でき、INNER JOIN の結果を生成するよりもメモリ使用量を抑えられる場合があります。
  • 繰り返し使用するルックアップには Join テーブルエンジンを使用します。 Join テーブルエンジンは、宣言された JOINの種類、strictness、キーに基づいて、事前計算済みのインメモリ構造を保持します。ClickHouse Cloud では、MergeTree をバックエンドテーブルとする SharedJoin として実装されており、Join(ANY, LEFT, key) を使うとキー単位で更新可能なルックアップを実現できます。詳細は Fast, updatable lookups with the Join table engine を参照してください。
  • 可能であればテーブルの sorting key で結合します。 両方のテーブルが 結合キーで物理的にソートされている場合、full_sorting_merge はソート処理を省略でき、メモリ使用量を削減できます。実行計画を確認したうえで、代表的なデータを用いてこのアルゴリズムとデフォルトのアルゴリズムをベンチマークで比較してください。
  • ディスクへのスピルが発生する JOIN は避けます。 JOIN の中間状態をディスクにスピルすればクエリをメモリ制限内に収められますが、通常はディスク I/O によってレイテンシが増加します。ハッシュ結合は設定されたスピル threshold に達すると grace hash に切り替えることができ、マージ結合は外部ソートを使用できます。スピルに頼る前に、入力の行数やカラム数を減らす、あるいはデータモデルを見直すといった対策を検討してください。詳細は 最適な JOIN アルゴリズムの選択 を参照してください。
  • 必要に応じてデフォルト値を不一致のマーカーとして利用します。 デフォルトの join_use_nulls = 0 では、外部 JOIN で一致しなかったカラムはそのデータ型のデフォルト値で埋められるため、Nullable でラップされずに済みます。一致しなかった行と、デフォルト値を含む一致した行をクエリで区別する必要がある場合にのみ、join_use_nulls を有効化してください。

最適な JOIN アルゴリズムの選択

ClickHouse は、速度とメモリのトレードオフが異なる複数の JOIN アルゴリズムをサポートしています。 join_algorithm のデフォルト値は direct,parallel_hash,hash,ie_join で、ClickHouse は JOIN の種類、strictness、条件、テーブルエンジンに対して適用可能な最初のアルゴリズムを選択します。この設定の変更を検討するのは、両方の入力を削減したうえで、代表的なデータでの計測により別のメモリ戦略または実行戦略が必要だと判明した場合に限ってください。アルゴリズムの実行の詳細とサポートされる JOIN の種類については、JOIN アルゴリズムの選択を参照してください。 最適化で最も重視する指標がパフォーマンスであり、結合をできるだけ高速に実行したい場合は、次の決定木を使って適切な結合アルゴリズムを選択できます。
結合パフォーマンス最適化の決定木

  • (1) 右側テーブルのデータを Dictionary などの低レイテンシなインメモリのキー・バリューデータ構造に事前ロードでき、結合キーが基盤となるキー・バリューストレージのキー属性と一致し、かつ LEFT ANY JOIN のセマンティクスで十分な場合は、direct join を適用できます。多くの場合、これが最も高速な方法です。
  • (2) テーブルの物理的な行の順序が結合キーのソート順と一致する場合は、状況次第です。この場合、完全ソートマージ結合 はソートフェーズをスキップするため、メモリ使用量が大幅に削減されます。さらに、データサイズや結合キーの値の分布によっては、一部のハッシュ結合アルゴリズムより実行時間が短くなります。
  • (3) 並列ハッシュ結合 の追加のメモリ使用量のオーバーヘッドを考慮しても右テーブルがメモリに収まる場合は、このアルゴリズムまたはハッシュ結合の方が高速になる可能性があります。どちらが速いかは、データサイズ、データ型、および結合キーカラムの値の分布によって決まります。
  • (4) 右テーブルがメモリに収まらない場合、ハッシュ結合 と 並列ハッシュ結合 は、max_bytes_before_external_join または max_bytes_ratio_before_external_join に達した時点で grace hash に切り替え、ディスクへスピルできます。完全ソートマージ結合 と 部分マージ結合 も外部ストレージを使用できます。どの方法が最も高速かは、データ量、データ型、および結合キーカラムの値の分布によって決まります。
部分マージ結合は、大きなテーブルを結合する際のメモリ使用量を最小限に抑えるよう最適化されていますが、その代償として結合速度はかなり遅くなります。特に、左テーブルの物理的な行の順序が結合キーのソート順と一致しない場合にこの傾向が顕著です。 Grace Hash Join は、メモリに制約されない 3 つの結合アルゴリズムの中で最も柔軟性が高く、設定によるきめ細かな制御が可能です。grace_hash_join_initial_buckets 設定は初期バケット数を制御するもので、メモリ使用量と結合速度のバランスを調整できます。 3 つのディスクベースのアルゴリズムのうちどれが最も高速かは、データ量、データ型、および結合キーカラムの値の分布によって決まります。最速のアルゴリズムを見極めるには、実運用に近いデータ量でベンチマークを実施するのが最善です。

メモリ最適化

結合の最適化において、実行時間の短さよりもメモリ使用量の少なさを優先したい場合は、代わりに次の決定木を利用できます。
結合のメモリ最適化に関する決定木

  • (1) テーブルの物理的な行順序が結合キーのソート順と一致している場合、完全ソートマージ結合のメモリ使用量は最小限に抑えられます。さらに、ソートフェーズが無効化されるため、良好な結合速度も得られます。
  • (2) Grace Hash Joinは、grace_hash_join_initial_bucketsを増やすことで、結合速度と引き換えにメモリ使用量を非常に低く抑えるようチューニングできます。部分マージ結合は、主記憶の使用量が少なくなるよう意図的に設計されています。外部ソートを有効にした完全ソートマージ結合は、行順序がキーのソート順と一致しない場合、一般に部分マージ結合よりも多くのメモリを使用しますが、その代わりに結合の実行時間は大幅に短縮されます。ハッシュ結合および並列ハッシュ結合も、設定済みのスピル threshold に達した時点で grace hash に切り替えることで、メモリ逼迫を軽減できます。

JOIN の最適化の例

以下の例では Stack Overflow データセット を使用し、推奨事項を段階的に適用していきます。 以下の測定値は、ClickHouse 26.8.1 が稼働する ClickHouse Cloud サービス上で max_threads = 4 を設定し、5 回実行した結果の中央値です。各クエリは事前に 1 回ウォームアップし、クエリの実行順序を交互に入れ替えたうえで、use_query_cache と use_query_condition_cache を無効化して計測しています。

ANY INNER JOIN を IN に置き換える

このテストでは、IN を使用したクエリのほうが 2.7 倍高速で、ピークメモリ使用量も 83% 少なくなりました。 このクエリは、本文では ClickHouse に言及していないものの、ClickHouse に言及しているコメントが少なくとも 1 件ある投稿の数をカウントします。
このクエリは結果として 86 を返します。経過時間の中央値は 27.101 秒、ピークメモリ使用量は 970.01 MiB でした。読み取ったデータは 1億5,020万行、51.77 GiB です。 このクエリでは、一致するコメントが存在するかどうかさえわかれば十分です。IN サブクエリを使用すれば、JOIN結果を生成せずに済みます。
IN クエリも 86 を返します。経過時間の中央値は 10.206 秒、ピークメモリ使用量は 162.81 MiB でした。読み取ったデータは 1 億 7,420 万行、19.02 GiB です。

JOIN を置き換える前に両側をフィルタする

このテストでは、書き換えによって経過時間の中央値が 18%、ピークメモリ使用量が 14% 削減されました。 このクエリは、2020 年以降に作成された Java 関連の投稿に対する賛成票の数を集計します。日付の条件は、JOIN の前に両方の入力に適用されています。
このクエリは結果として 261915 を返します。経過時間の中央値は 0.850 秒、ピークメモリ使用量は 59.96 MiB でした。読み取った行数は 8,094 万行、データ量は 1.13 GiB です。 各投票について必要なのは一致する投稿が存在するかどうかだけなので、両方の入力に対するフィルタを維持したまま、JOIN を IN に置換できます。
IN クエリも 261915 を返します。経過時間の中央値は 0.696 秒、ピークメモリ使用量は 51.70 MiB でした。読み取ったのは 4,620 万行、817.49 MiB のデータです。

関連資料