JOINsを最小化し最適化する

ClickHouseは、さまざまなJOINタイプとアルゴリズムをサポートしており、JOINのパフォーマンスは最近のリリースで大幅に改善されています。しかし、JOINは本質的に単一の非正規化テーブルからのクエリよりもコストが高いです。非正規化は、クエリ時間から挿入または前処理時間への計算作業の移行を意味し、これにより実行時のレイテンシが大幅に低下することがよくあります。リアルタイムまたはレイテンシに敏感な分析クエリの場合、非正規化が強く推奨されます。

一般的に、次のような場合に非正規化を行います：

• テーブルがめったに変更されないか、バッチリフレッシュが許容される場合。
• リレーションシップが多対多でない場合、または高いカーディナリティを持たない場合。
• クエリされるカラムの限定的なサブセットのみを対象とする場合、つまり特定のカラムは非正規化から除外できます。
• Flinkなどの上流システムに処理を移行する能力があり、リアルタイムの強化やフラット化を管理できる場合。

すべてのデータを非正規化する必要はありません—頻繁にクエリされる属性に焦点を当ててください。また、全体のサブテーブルを複製するのではなく、集約を段階的に計算するためにマテリアライズドビューを検討してください。スキーマの更新がまれで遅延が重要な場合、非正規化は最適なパフォーマンスのトレードオフを提供します。

ClickHouseでのデータの非正規化に関する完全なガイドについては、こちらを参照してください。

JOINが必要な場合

JOINが必要な場合は、バージョン24.12以上、できれば最新のバージョンを使用していることを確認してください。JOINのパフォーマンスは各新しいリリースで改善され続けています。ClickHouse 24.12以降、クエリプランナーは自動的に小さなテーブルをJOINの右側に配置して最適なパフォーマンスを実現します。これは以前は手動で行う必要があったタスクです。さらに多くの改善が近日中に実施される予定で、より積極的なフィルタプッシュダウンや複数のJOINの自動的な再順序付けが含まれます。

JOINのパフォーマンスを改善するためには、次のベストプラクティスに従ってください：

• デカルト積を避ける：左側の値が右側の複数の値に一致する場合、JOINは複数の行を返します—いわゆるデカルト積です。右側から必要なすべての一致を必要とせず、いずれかの一致だけが必要な場合は、ANY JOIN（例：LEFT ANY JOIN）を使用できます。これらは通常のJOINよりも高速で、メモリを少なく使用します。
• JOINされたテーブルのサイズを減少させる：JOINの実行時間とメモリ消費は、左側および右側のテーブルのサイズに比例して増加します。JOINによって処理されるデータ量を減らすために、クエリのWHEREまたはJOIN ON句に追加のフィルタ条件を追加してください。ClickHouseは、フィルタ条件を可能な限り深くクエリプランにプッシュします。フィルタが自動的にプッシュダウンされない場合（何らかの理由で）、JOINの一方をサブクエリとして書き換えてプッシュダウンを強制します。
• 適切であれば辞書を介して直接JOINを使用する：ClickHouseのスタンダードなJOINは、2段階で実行されます。右側を反復してハッシュテーブルを構築するビルドフェーズと、左側を反復してハッシュテーブルのルックアップを通じて一致するJOINパートナーを見つけるプローブフェーズです。右側が辞書またはキー-バリュー特性を持つ別のテーブルエンジン（例：EmbeddedRocksDBまたはJoinテーブルエンジン）の場合、ClickHouseは「直接」JOINアルゴリズムを使用できます。このアルゴリズムは、ハッシュテーブルを構築する必要を効果的に排除し、クエリ処理を加速します。これはINNERとLEFT OUTER JOINに機能し、リアルタイムの分析ワークロードに最適です。
• JOINのためにテーブルのソートを活用する：ClickHouseの各テーブルは、テーブルの主キー列によってソートされています。ソートマージJOINアルゴリズム（例：full_sorting_mergeやpartial_merge）を利用してテーブルのソートを活用できます。標準のJOINアルゴリズムがハッシュテーブルに基づくのとは異なり（下記のparallel_hash、hash、grace_hash参照）、ソートマージJOINアルゴリズムは最初にソートを行い、その後両方のテーブルをマージします。クエリが両方のテーブルをそれぞれの主キー列でJOINする場合、ソートステップを省略できる最適化があります。
• ディスクスピリングJOINを避ける：JOINの中間状態（例：ハッシュテーブル）は、メインメモリに収まらないほど大きくなる場合があります。この状況では、ClickHouseはデフォルトでメモリ不足エラーを返します。一部のJOINアルゴリズム（下記参照）は、例えばgrace_hash、partial_merge、およびfull_sorting_mergeは、中間状態をディスクにスピルし、クエリの実行を続けることができます。ただし、ディスクアクセスはJOIN処理を大幅に遅くする可能性があるため、これらのJOINアルゴリズムの使用には注意が必要です。代わりに、他の方法でJOINクエリを最適化して中間状態のサイズを縮小することをお勧めします。
• 外部JOINでの「ノーマッチ」マーカーとしてのデフォルト値：左/右/フル外部JOINは、左/右/両方のテーブルからすべての値を含みます。他のテーブルでいくつかの値の参加パートナーが見つからない場合、ClickHouseは参加パートナーを特別なマーカーで置き換えます。SQL標準では、データベースはそのようなマーカーとしてNULLを使用することを義務付けています。ClickHouseでは、結果カラムをNullableでラップする必要があり、追加のメモリやパフォーマンスオーバーヘッドを生成します。代わりに、設定join_use_nulls = 0を構成し、結果カラムデータ型のデフォルト値をマーカーとして使用することができます。

辞書の使用に注意

ClickHouseでJOINに辞書を使用する際は、辞書が設計上、重複キーを許可しないことを理解することが重要です。データの読み込み中、重複キーは静かにデデュプリケートされ—特定のキーに対して最後に読み込まれた値のみが保持されます。この動作により、辞書は1対1または多対1の関係に理想的で、最新または権威ある値のみが必要です。しかし、1対多または多対多の関係（例：役割を持つ俳優とのJOIN）で辞書を使用すると、すべての一致する行のうち1つ以外が破棄され、静かにデータが失われます。その結果、辞書は複数の一致間でフルなリレーショナル忠実度が必要なシナリオには適していません。

正しいJOINアルゴリズムの選択

ClickHouseは、スピードとメモリの間でトレードオフを行ういくつかのJOINアルゴリズムをサポートしています：

• 並列ハッシュJOIN（デフォルト）： メモリに収まる小から中規模の右側のテーブルに対して迅速です。
• 直接JOIN： 辞書（またはキー-バリュー特性を持つ他のテーブルエンジン）を使用する場合に理想的で、INNERまたはLEFT ANY JOINに最適な方法です。ハッシュテーブルを構築する必要がありません。
• フルソーティングマージJOIN： 両方のテーブルがJOINキーでソートされているときに効率的です。
• パーシャルマージJOIN： メモリを最小化しますが遅い—限られたメモリで大きなテーブルをJOINするのに最適です。
• グレースハッシュJOIN： 柔軟でメモリ調整可能、大規模データセットに対して調整可能なパフォーマンス特性を持っています。

注記

各アルゴリズムは、JOINタイプに対するサポートが異なります。各アルゴリズムに対するサポートされているJOINタイプの完全なリストは、こちらで確認できます。

ClickHouseに最適なアルゴリズムを選択させるには、join_algorithm = 'auto'（デフォルト）を設定するか、ワークロードに基づいて明示的に制御します。パフォーマンスやメモリオーバーヘッドを最適化するためにJOINアルゴリズムを選択する必要がある場合、このガイドをお勧めします。

最適なパフォーマンスを得るために：

• 高パフォーマンスのワークロードではJOINを最小限に抑えます。
• クエリごとに3〜4以上のJOINを避けます。
• 実データで異なるアルゴリズムをベンチマークします - パフォーマンスはJOINキーの分布やデータサイズによって異なります。

JOIN最適化戦略、JOINアルゴリズム、およびその調整方法については、ClickHouseドキュメントとこのブログシリーズを参照してください。