ClickHouseにおける主キーインデックスの実用的な導入

はじめに

このガイドでは、ClickHouseのインデックスについて詳しく掘り下げていきます。以下について詳細に説明し、議論します：

• ClickHouseにおけるインデクシングが従来のリレーショナルデータベース管理システムとどのように異なるか
• ClickHouseがテーブルのスパース主キーインデックスをどのように構築し使用しているか
• ClickHouseにおけるインデクシングのベストプラクティスは何か

このガイドに記載されているすべてのClickHouse SQLステートメントとクエリを自分のマシンで実行することもできます。 ClickHouseのインストールと開始方法については、クイックスタートを参照してください。

注記

このガイドはClickHouseのスパース主キーインデックスに焦点を当てています。

ClickHouseのセカンダリーデータスキッピングインデックスについては、チュートリアルを参照してください。

データセット

このガイドでは、サンプルの匿名化されたウェブトラフィックデータセットを使用します。

• サンプルデータセットから887万行（イベント）のサブセットを使用します。
• 圧縮されていないデータサイズは887万イベントで約700MBです。ClickHouseに保存すると圧縮後は200MBになります。
• サブセットの各行には、特定の時刻にURL（URLカラム）をクリックしたインターネットユーザー（UserIDカラム）を示す3つのカラムがあります。

これら3つのカラムを使用して、次のような典型的なウェブ分析クエリをすでに策定できます：

• 「特定のユーザーにとって最もクリックされた上位10のURLは何ですか？」
• 「特定のURLを最も頻繁にクリックした上位10のユーザーは誰ですか？」
• 「ユーザーが特定のURLをクリックする際の最も人気のある時間（例えば、曜日）は何ですか？」

テストマシン

このドキュメントに示すすべての実行時間数値は、Apple M1 Proチップを搭載したMacBook Pro上でClickHouse 22.2.1をローカルで実行したものです。

フルテーブルスキャン

主キーなしでデータセット上でクエリがどのように実行されるかを見るために、次のSQL DDLステートメントを実行してテーブル（MergeTreeテーブルエンジンを使用）を作成します：

次に、次のSQL挿入ステートメントを使用して、ヒットデータセットのサブセットをテーブルに挿入します。 これは、クリックハウスのリモートホストにホストされているフルデータセットのサブセットをロードするためにURLテーブル関数を使用します：

応答は次のようになります：

ClickHouseクライアントの結果出力は、上記のステートメントがテーブルに887万行挿入されたことを示しています。

最後に、後の議論を簡素化し、図や結果を再現可能にするために、FINALキーワードを使用してテーブルを最適化します：

注記

一般的には、データをロードした後にすぐにテーブルを最適化することは必要も推奨もされません。この例でこれが必要な理由は明らかになります。

次に、最初のウェブ分析クエリを実行します。以下は、ユーザーID 749927693を持つインターネットユーザーのために最もクリックされた上位10のURLを計算します：

応答は次のようになります：

ClickHouseクライアントの結果出力は、ClickHouseがフルテーブルスキャンを実行したことを示しています！私たちのテーブルの887万行の各行がClickHouseにストリームされました。これはスケールしません。

これを（大幅に）効率的かつ（はるかに）高速にするためには、適切な主キーを持つテーブルを使用する必要があります。これにより、ClickHouseは自動的に（主キーのカラムに基づいて）スパース主キーインデックスを作成し、それを使用して例のクエリの実行速度を大幅に向上させることができます。

関連コンテンツ

• ブログ: ClickHouseのクエリを超高速化する

ClickHouseインデックス設計

大規模データスケールのためのインデックス設計

従来のリレーショナルデータベース管理システムでは、主インデックスにはテーブル行ごとに1つのエントリが含まれます。これにより、主インデックスには887万エントリが含まれることになります。このようなインデックスは特定の行を迅速に特定することができるため、ルックアップクエリやポイントアップデートに対して高い効率をもたらします。B(+)-Treeデータ構造でエントリを検索する平均時間計算量はO(log n)です；より正確には、log_b n = log_2 n / log_2 bであり、ここでbはB(+)-Treeの分岐因子、nはインデックスされた行の数です。通常、bは数百から数千の間にあるため、B(+)-Treesは非常に浅い構造であり、レコードを特定するために必要なディスクシークは少数です。887万行と分岐因子が1000の場合、平均して2.3回のディスクシークが必要です。この能力にはコストが伴います：新しい行をテーブルに追加し、インデックスにエントリを追加する際の追加的なディスクおよびメモリーオーバーヘッド、挿入コストの増加、時にはB-Treeの再バランス。

B-Treeインデックスに関連する課題を考慮すると、ClickHouseのテーブルエンジンは異なるアプローチを利用しています。ClickHouseのMergeTreeエンジンファミリーは、大量のデータボリュームを処理するために設計および最適化されています。これらのテーブルは、毎秒数百万の行挿入を受け取り、非常に大きな（100ペタバイト以上の）データボリュームを保存するように設計されています。データは、バックグラウンドで部分を結合するルールが適用されながら、テーブルに部分ごとに迅速に書き込まれます。ClickHouseでは、各部分にそれぞれの主インデックスがあります。部分がマージされると、マージされた部分の主インデックスもマージされます。ClickHouseが設計された非常に大きなスケールにおいて、ディスクとメモリーの効率が非常に重要です。したがって、すべての行をインデックスするのではなく、部分の主インデックスは行のグループ（「グラニュール」と呼ばれる）ごとに1つのインデックスエントリ（「マーク」と呼ばれる）を持ちます。このテクニックはスパースインデックスと呼ばれます。

スパースインデクシングが可能なのは、ClickHouseが部分の行を主キーのカラムに基づいてディスクに順序付けて保存しているためです。単一の行を直接特定する代わりに（B-Treeベースのインデックスのように）、スパース主インデックスはインデックスエントリのバイナリ検索を介して迅速に一致する可能性がある行のグループを特定できます。見つかった一致する可能性のある行のグループ（グラニュール）は、その後ClickHouseエンジンに並行してストリーミングされて一致を見つけます。このインデックス設計により、主インデックスは小さく（完全にメインメモリにフィットすることが可能であり、及びそれが必要です）、クエリ実行時間を大幅に短縮します：特にデータ分析のユースケースにおいて典型的な範囲クエリの場合に。

以下に、ClickHouseがスパース主インデックスを構築し使用する方法を詳しく示します。後のセクションでは、インデックスを構築するために使用されるテーブルカラム（主キーのカラム）の選択、削除、順序付けのベストプラクティスについて議論します。

主キーを持つテーブル

UserIDとURLのキーカラムを持つ複合主キーのあるテーブルを作成します：

DDLステートメントの詳細

後の議論を簡素化し、図や結果を再現可能にするために、DDLステートメントは：

• ORDER BY句を介してテーブルの複合ソートキーを指定します。

• 設定を通じて主インデックスが持つインデックスエントリの数を明示的に制御します：

  • index_granularity：デフォルト値の8192に明示的に設定されており、8192行ごとに1つのインデックスエントリを持つことを意味します。例えば、テーブルが16384行を持つ場合、インデックスは2つのインデックスエントリを持つことになります。

  • index_granularity_bytes：適応インデックスグラニュラティを無効にするために0に設定されます。適応インデックスグラニュラティは、次のいずれかが真である場合にClickHouseがn行のグループごとに1つのインデックスエントリを自動的に作成することを意味します：

    • nが8192未満であり、そのn行の結合された行データのサイズが10MB以上（index_granularity_bytesのデフォルト値）である場合。

    • n行の結合されたデータサイズが10MB未満であるが、nが8192である場合。

  • compress_primary_key：主インデックスの圧縮を無効にするために0に設定されており、これにより後でオプションでコンテンツを検査できます。

上記のDDLステートメントの主キーは、指定された2つのキーカラムに基づいて主インデックスを作成する原因となります。

次にデータを挿入します：

応答は次のようになります：

テーブルを最適化します：

次のクエリを使用してテーブルのメタデータを取得できます：

応答は次のようになります：

ClickHouseクライアントの出力は次のことを示しています:

• テーブルのデータは、ディスク上の特定のディレクトリに広い形式で保存されており、そのディレクトリ内にはテーブルカラムごとに1つのデータファイル（および1つのマークファイル）があります。
• テーブルには887万行があります。
• すべての行の圧縮されていないデータサイズは733.28MBです。
• すべての行のディスク上の圧縮サイズは206.94MBです。
• テーブルには1083エントリ（「マーク」と呼ばれる）の主インデックスがあり、そのインデックスのサイズは96.93KBです。
• 合計で、テーブルのデータとマークファイル、および主インデックスファイルはディスク上で207.07MBを占めています。

データは主キーのカラムによって順序付けられてディスクに保存される

上記で作成したテーブルは以下の特性を持っています：

• 複合主キー (UserID, URL)と
• 複合ソートキー (UserID, URL, EventTime)。

注記

• もしソートキーのみを指定していた場合、主キーは暗黙的にソートキーと等しいと定義されます。
• メモリ効率を高めるために、クエリがフィルタリングするカラムのみを含む主キーを明示的に指定しました。主キーに基づく主インデックスは、完全にメインメモリにロードされています。
• ガイドの図や情報の一貫性を確保し、圧縮率を最適化するため、すべてのテーブルカラムを含む別のソートキーを定義しました（同じカラムに類似のデータが近接すればするほど、例えばソートを行うことで、データはより良く圧縮されます）。
• 両方が指定されている場合、主キーはソートキーのプレフィックスである必要があります。

挿入された行は、主キーのカラム（およびソートキーの追加的な EventTime カラム）によって、ディスク上で辞書式順序（昇順）で保存されています。

注記

ClickHouseは、同一の主キーのカラム値を持つ複数の行を挿入することを許可します。この場合（以下に図の行1と行2を参照）、最終的な順番は指定されたソートキーによって決まるため、EventTimeカラムの値によって決まります。

ClickHouseは列指向のデータベース管理システムです。以下の図に示すように

• ディスク上の表現では、各テーブルカラムに対して1つのデータファイル（*.bin）があり、すべての値は圧縮された形式で保存され、
• 887万の行はディスク上で主キーのカラム（および追加のソートキーのカラム）によって辞書式昇順で保存されます。すなわち、この場合
  • 最初は UserIDによって、
  • 次は URLによって、
  • 最後に EventTimeによって：

UserID.bin、URL.bin、および EventTime.bin は、UserID、URL、および EventTimeカラムの値が保存されるディスク上のデータファイルです。

注記

• 主キーはディスク上の行の辞書式順序を定義するため、テーブルには1つの主キーしか持てません。
• 行を0から始まる番号付けしているのは、ClickHouseの内部行番号付けスキームと一致させ、ログメッセージにも使用されるためです。

データは並列データ処理のためにグラニュールに整理される

データ処理の目的のために、テーブルのカラムの値は論理的にグラニュールに分割されます。 グラニュールはClickHouseにストリーミングされる最小の不可分なデータセットです。 これにより、数個の行を読み取るのではなく、ClickHouseは常に行のグループ（グラニュール）全体をストリーミング方式かつ並行して読み取ります。

注記

カラムの値は物理的にグラニュール内に保存されるわけではありません：グラニュールはクエリ処理のためのカラム値の論理的な定義です。

以下の図は、当テーブルの8.87百万行の（カラムの値）が、テーブルのDDLステートメントにindex_granularity（デフォルト値の8192に設定）を含むことから、1083グラニュールに整理される様子を示しています。

最初の（物理的なディスク上の順序に基づく）8192行（そのカラムの値）は論理的にグラニュール0に属し、その後の8192行（そのカラムの値）はグラニュール1に属します。

注記

• 最後のグラニュール（グラニュール1082）は、8192行未満を「含む」ことがあります。

• このガイドの冒頭で「DDLステートメントの詳細」において、私たちは適応インデックスグラニュラティを無効にしたことに言及しました（ガイドの議論を簡素化し、図や結果を再現可能にするために）。

  したがって、私たちの例のテーブルのすべてのグラニュール（最後のものを除く）のサイズは同じです。

• 適応インデックスグラニュラティを持つテーブルの場合（index granularityはデフォルトで適応的であり）、一部のグラニュールのサイズは8192行より少なくなる場合があります。

• 私たちは主キーのカラム（UserID、URL）の一部のカラム値をオレンジでマーキングしています。 これらのオレンジでマークされたカラム値は、各グラニュールの最初の行の主キーのカラム値になります。 以下で見ていくように、これらのオレンジでマークされたカラム値はテーブルの主インデックスのエントリになります。

• グラニュールには0から番号を付けており、ClickHouseの内部の番号付けスキームと一致させ、ログメッセージにも使用されます。

主インデックスはグラニュールごとに1つのエントリを持つ

主インデックスは、上記の図に示すグラニュールに基づいて作成されます。このインデックスは圧縮されていないフラットな配列ファイル（primary.idx）であり、0から始まるいわゆる数値インデックスマークを含みます。

以下の図は、インデックスが各グラニュールの最初の行の主キーのカラム値（上記の図でオレンジでマークされた値）を保存していることを示しています。 言い換えれば：主インデックスは、テーブルのすべての8192行における主キーのカラム値を保存しています（物理的な行順序に基づいて主キーのカラムによって定義されます）。 例えば、

• 最初のインデックスエントリ（上の図で「マーク0」と呼ばれる）は、上の図でグラニュール0の最初の行のキーのカラム値を保存しています。
• 2番目のインデックスエントリ（上の図で「マーク1」と呼ばれる）は、上の図でグラニュール1の最初の行のキーのカラム値を保存しています、そして続きます。

私たちのテーブルには887万行と1083グラニュールがあるため、インデックスには合計1083エントリがあります：

注記

• 適応インデックスグラニュラティを持つテーブルの場合、テーブルの最後の行の主キーのカラム値を記録する1つの「最終」の追加マークも主インデックスに保存されますが、適応インデックスグラニュラティを無効にしたため（このガイドの議論を簡素化し、図や結果を再現可能にするため）、私たちの例のテーブルのインデックスにはこの最終のマークは含まれていません。

• 主インデックスファイルは完全にメインメモリにロードされます。ファイルのサイズが利用可能な空きメモリのサイズを超える場合は、ClickHouseはエラーを発生させます。

主インデックスの内容を検査する

セルフマネージドのClickHouseクラスタ上で、以下の手順を踏むことで、例のテーブルの主インデックスのコンテンツを調査するためにfileテーブル関数を使用できます。

そのために、まず、稼働中のクラスタのノードのuser_files_pathに主インデックスファイルをコピーする必要があります：

• ステップ1：主インデックスファイルを含む部分のパスを取得します
SELECT path FROM system.parts WHERE table = 'hits_UserID_URL' AND active = 1

はテストマシン上で/Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4を返します。

• ステップ2：user_files_pathを取得します
デフォルトのuser_files_pathは、Linuxでは /var/lib/clickhouse/user_files/

であり、Linuxでは変更されたかどうかを確認できます：$ grep user_files_path /etc/clickhouse-server/config.xml

テストマシン上のパスは/Users/tomschreiber/Clickhouse/user_files/です。

• ステップ3：主インデックスファイルをuser_files_pathにコピーします

cp /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4/primary.idx /Users/tomschreiber/Clickhouse/user_files/primary-hits_UserID_URL.idx

これで、SQLを介して主インデックスの内容を検査できます：

• エントリの数を取得します
SELECT count( )<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String'); は 1083 を返します。
• 最初の2つのインデックスマークを取得します
SELECT UserID, URL<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 0, 2;

は次のように返します：

240923, http://showtopics.html%3...<br/> 4073710, http://mk.ru&pos=3_0

• 最後のインデックスマークを取得します
SELECT UserID, URL FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 1082, 1; は 4292714039 │ http://sosyal-mansetleri... と返します。

これは、私たちの例のテーブルの主インデックス内容の図と正確に一致します：

主キーエントリはインデックスマークと呼ばれます。なぜなら、各インデックスエントリが特定のデータ範囲の開始を示すためです。具体的には例のテーブルに関して：

• UserIDインデックスマーク：

  主インデックスに保存されたUserIDの値は昇順にソートされています。
  上記の図の「マーク1」は、グラニュール1のすべてのテーブル行のUserIDの値、およびすべての後続のグラニュールのUserIDの値が4.073.710以上であることを保証します。

後で確認するように、このグローバルな順序により、ClickHouseはクエリが主キーの最初のカラムでフィルタリングされるときにインデックスマークに対してバイナリサーチアルゴリズムを使用することができるからです。

• URLインデックスマーク：

  主キーのカラムUserIDとURLの類似の基数により、一般的に主キーの最初のカラムの後に位置するすべてのキーカラムのインデックスマークは、前のキーのカラム値がグラニュール内のすべてのテーブル行で同じである限りデータ範囲を示します。
  例えば、上記の図でマーク0とマーク1のUserID値が異なるため、ClickHouseはグラニュール0内のすべてのテーブル行のURLの値が'http://showtopics.html%3...'以上であるとは仮定できません。しかし、上記の図でマーク0とマーク1のUserID値が同じであれば（すなわち、UserIDの値がグラニュール0内のすべてのテーブル行で同じであれば）、ClickHouseはグラニュール0内のすべてのテーブル行のURLの値が'http://showtopics.html%3...'以上であると仮定できたでしょう。

  これは、クエリ実行パフォーマンスに対しての影響について、後で詳しく説明します。

返答は次のようになります：

ClickHouse クライアントの出力は、フルテーブルスキャンを実行する代わりに、8.19 千の行のみが ClickHouse にストリーミングされたことを示しています。

もし トレースログ が有効になっていると、ClickHouse サーバーログファイルは ClickHouse が 1083 の UserID インデックスマークに対して 二分探索 を実行して、749927693 の UserID カラム値を持つ行を含んでいる可能性のあるグラニュールを特定したことを示しています。これには平均で O(log2 n) の時間計算量を必要とします：

上記のトレースログから、1083 の既存のマークのうち 1 つがクエリを満たしていることが分かります。

トレースログの詳細

マーク 176 が特定されました（「見つかった左境界マーク」は包含的で、「見つかった右境界マーク」は排他的です）、したがって、グラニュール 176 からのすべての 8192 行（これは行 1.441.792 から始まります - これは後でこのガイドで確認します）が ClickHouse にストリーミングされ、749927693 の UserID カラム値を持つ実際の行が見つかります。

この例のクエリで EXPLAIN 句 を使用してこれを再現することもできます：

返答は次のようになります：

クライアントの出力は、1083 のグラニュールのうち 1 つが UserID カラム値 749927693 を持つ行を含んでいる可能性があるとして選択されたことを示しています。

結論

クエリが複合キーの一部であり、最初のキー列であるカラムをフィルタリングする場合、ClickHouse はキー列のインデックスマークの上で二分探索アルゴリズムを実行します。

上記で述べたように、ClickHouse は自社のスパース主インデックスを使用して、クエリに一致する可能性のある行を含むグラニュールを迅速に（二分探索を介して）選択しています。

これは ClickHouse のクエリ実行の 第一段階（グラニュール選択） です。

第二段階（データ読み取り） では、ClickHouse は選択したグラニュールを見つけて、それらのすべての行を ClickHouse エンジンにストリーミングして、クエリに実際に一致する行を見つけるために使用します。

この第二段階について、次のセクションで詳しく説明します。

マークファイルはグラニュールを特定するために使用されます

以下の図は、私たちのテーブルの主インデックスファイルの一部を示しています。

上記で述べたように、インデックスの 1083 の UserID マークに対する二分探索を通じて、マーク 176 が特定されました。したがって、対応するグラニュール 176 はおそらく UserID カラム値 749.927.693 を持つ行を含んでいる可能性があります。

グラニュール選択の詳細

上記の図は、マーク 176 が関連グラニュール 176 の最小 UserID 値が 749.927.693 より小さく、次のマーク（マーク 177）のグラニュール 177 の最小 UserID 値がこの値より大きいという最初のインデックスエントリであることを示しています。したがって、マーク 176 に対応するグラニュール 176 のみが UserID カラム値が 749.927.693 を持つ行を含んでいる可能性があります。

グラニュール 176 の中に UserID カラム値が 749.927.693 を持つ行が含まれているかどうかを確認するためには、このグラニュールに属するすべての 8192 行を ClickHouse にストリーミングする必要があります。

これを達成するために、ClickHouse はグラニュール 176 の物理的位置を知る必要があります。

ClickHouse では、テーブルのすべてのグラニュールの物理的位置がマークファイルに格納されています。データファイルと同様に、カラムごとに 1 つのマークファイルがあります。

以下の図は、テーブルの UserID、URL、および EventTime カラムのグラニュールの物理位置を保存している 3 つのマークファイル UserID.mrk、URL.mrk、および EventTime.mrk を示しています。

主インデックスが 0 から始まる番号を付けられたインデックスマークを含むフラットな未圧縮配列ファイル (primary.idx) であることを説明してきました。

同様に、マークファイルも 0 から始まる番号が付けられたマークを含むフラットな未圧縮配列ファイル (*.mrk) です。

ClickHouse がマッチする可能性のある行を含むグラニュールのインデックスマークを特定して選択した後、マークファイルにおいて位置配列のルックアップが実行され、そのグラニュールの物理位置を取得します。

特定のカラムの各マークファイルエントリは、オフセットの形式で 2 つの位置を保存しています。

• 最初のオフセット（上記の図の「block_offset」）は、選択されたグラニュールの圧縮バージョンを含む ブロック が、圧縮されたカラムデータファイルの中でどこにあるかを指し示しています。この圧縮ブロックは、おそらくいくつかの圧縮されたグラニュールを含んでいます。見つかった圧縮ファイルブロックは、読み込み時に主メモリに展開されます。

• マークファイルの 2 番目のオフセット（上記の図の「granule_offset」）は、非圧縮ブロックデータ内のグラニュールの位置を提供します。

その後、見つかった非圧縮グラニュールに属するすべての 8192 行が、さらなる処理のために ClickHouse にストリーミングされます。

注記

• ワイドフォーマットのテーブルで、適応インデックス粒度がない場合、ClickHouse は上記のように視覚化された .mrk マークファイルを使用し、各エントリには 8 バイトのアドレスが 2 つ含まれています。これらのエントリは、同じサイズを持つすべてのグラニュールの物理位置です。

インデックス粒度は デフォルトで適応式ですが、例のために、適応インデックス粒度を無効にしました（このガイドでの議論を簡素化し、図や結果を再現しやすくするため）。私たちのテーブルは、データのサイズが min_bytes_for_wide_part より大きいため、ワイドフォーマットを使用しています（これはセルフマネージドクラスターのデフォルトで 10 MB です）。

• ワイドフォーマットのテーブルで、適応インデックス粒度がある場合、ClickHouse は .mrk2 マークファイルを使用し、.mrk マークファイルと似たエントリを持っていますが、各エントリに対して追加の 3 番目の値、すなわち現在のエントリに関連するグラニュールの行数があります。

• コンパクトフォーマットのテーブルでは、ClickHouse は .mrk3 マークファイルを使用します。

マークファイルの理由

なぜ主インデックスは、インデックスマークに対応するグラニュールの物理位置を直接含まないのでしょうか？

ClickHouse が設計されている非常に大規模なスケールにおいては、非常にディスクおよびメモリ効率が良いことが重要です。

主インデックスファイルは主メモリに収まる必要があります。

私たちの例のクエリでは、ClickHouse は主インデックスを使用しておそらくマッチする行を含むことができる単一のグラニュールを選択しました。その単一のグラニュールのためにのみ、ClickHouse は対応する行をストリーミングするための物理位置が必要です。

さらに、このオフセット情報は、クエリに使用されていないカラム（例えば EventTime）には必要ありません。

サンプルクエリの場合、ClickHouse は UserID データファイル (UserID.bin) のグラニュール 176 の 2 つの物理位置オフセットと、URL データファイル (URL.bin) のグラニュール 176 の 2 つの物理位置オフセットのみが必要です。

マークファイルによって提供される間接性は、すべての 1083 グラニュールの物理位置のエントリを主インデックスの中に直接格納することを避けることで、メインメモリ内に不要な（使用されていない）データを持つことを回避します。

以下の図とその後のテキストは、例のクエリのために ClickHouse が UserID.bin データファイル内のグラニュール 176 をどのように特定するかを示しています。

このガイドで以前に述べたように、ClickHouse は主インデックスマーク 176 を選択し、したがって私たちのクエリに一致する行を含む可能性のあるグラニュール 176 を選択しました。

ClickHouse は今、選択されたマーク番号 (176) を使用して、UserID.mrk マークファイル内で位置配列ルックアップを行って、グラニュール 176 の位置を特定するための 2 つのオフセットを取得します。

示されているように、最初のオフセットは、UserID.bin データファイル内でグラニュール 176 の圧縮ファイルブロックを特定しています。

見つかったファイルブロックが主メモリに展開されると、マークファイルからの 2 番目のオフセットを使って、非圧縮データ内のグラニュール 176 を特定できます。

ClickHouse は UserID.bin データファイルと URL.bin データファイルの両方からグラニュール 176 を特定し（すべての値をストリーミングする）、サンプルクエリ（UserID 749.927.693 のインターネットユーザーの上位 10 件のクリックされた URL）を実行する必要があります。

上記の図は、ClickHouse が UserID.bin データファイルのグラニュールを特定する方法を示しています。

並行して、ClickHouse は URL.bin データファイルのグラニュール 176 に対しても同様の処理を行います。対応する 2 つのグラニュールは整列して ClickHouse エンジンにストリーミングされ、UserID が 749.927.693 であるすべての行の URL 値をグループごとに集約およびカウントし、最終的に 10 の最大の URL グループを降順で出力します。

複数の主インデックスを使用する

二次キー列は（非効率的）である可能性がある

クエリが複合キーの一部であり、最初のキー列であるカラムをフィルタリングしている場合、ClickHouse はキー列のインデックスマークに対して二分探索アルゴリズムを実行します。

しかし、クエリが複合キーの一部であるが最初のキー列ではないカラムをフィルタリングする場合に何が起こるでしょうか？

注記

ここでは、クエリが最初のキー列ではなく、二次キー列でフィルタリングしているシナリオについて議論します。

クエリが最初のキー列とその後の任意のキー列でフィルタリングしている場合、ClickHouse は最初のキー列のインデックスマークに対して二分探索を実行します。

次のクエリを使用して、最も頻繁に「http://public_search」の URL をクリックした上位 10 人のユーザーを計算します：

返答は次のとおりです：

クライアント出力は、ClickHouse が複合主キーの一部である URL カラム に対してほぼフルテーブルスキャンを実行したことを示しています！ ClickHouse は 887 万行のテーブルから 881 万行を読み取ります。

もし trace_logging が有効になっている場合、ClickHouse サーバーログファイルは、ClickHouse が 1083 の URL インデックスマークに対して 一般的な除外検索 を使用して、「http://public_search」という URL カラム値を持つ行を含む可能性のあるグラニュールを特定したことを示しています：

上記のサンプルトレースログから、1076（マークによる）マークのうちの 1083 が、マッチする URL 値を持つ行を含んでいる可能性があるとして選択されたことがわかります。

その結果、ClickHouse エンジンのために 881 万行がストリーミングされ（10 ストリームを使用して並列で）、実際に「http://public_search」という URL 値が含まれている行を特定します。

しかし、後で見ますが、その選択した 1076 のグラニュールのうち、実際に一致する行を持つのは 39 のグラニュールだけです。

複合主キー（UserID、URL）に基づく主インデックスは、特定の UserID 値を持つ行のフィルタリングを迅速に行うためには非常に便利でしたが、特定の URL 値を持つ行のフィルタリングのクエリを迅速に行う際には大きな助けにはなっていません。

その理由は、URL カラムが最初のキー列ではないため、ClickHouse は URL カラムのインデックスマークに対して一般的な除外検索アルゴリズム（代わりに二分検索）を使用しており、そのアルゴリズムの効果は、URL カラムとその前のキー列である UserID との間の基数の違いに依存します。

これを説明するために、一般的な除外検索がどのように機能するかの詳細をいくつか示します。

一般的な除外検索アルゴリズム

以下は、ClickHouse の一般的な除外検索アルゴリズムが、前のキー列が低いまたは高い基数を持つ二次列でグラニュールが選択されるときにどのように機能するかを示しています。

どちらのケースについても、次の仮定をします：

• URL 値 = "W3" の行を検索するクエリ。
• UserID および URL の簡略値を持つ抽象バージョンのヒットテーブル。
• インデックスの複合主キー（UserID、URL）。これは、行が最初に UserID 値で並べられ、同じ UserID 値を持つ行が URL で並べられていることを意味します。
• グラニュールサイズは 2 です。すなわち、各グラニュールには 2 行が含まれています。

以下の図では、各グラニュールの最初のテーブル行のキー列値をオレンジ色でマークしています。

前のキー列が低い基数を持つ場合

UserID に低い基数があると仮定してください。この場合、同じ UserID 値が複数のテーブル行およびグラニュール、したがってインデックスマークに広がっている可能性が高いです。同じ UserID のインデックスマークの URL 値は、昇順にソートされます（テーブル行は最初に UserID によって、次に URL で並べられるため）。これにより、効率的なフィルタリングが可能です。

上の図には、抽象的なサンプルデータに基づくグラニュール選択プロセスの 3 つの異なるシナリオが示されています：

1. URL 値が W3 より小さく、次のインデックスマークの URL 値も W3 より小さいインデックスマーク 0 は、マーク 0 と 1 が同じ UserID 値を持っていますので除外できます。この除外前提条件により、グラニュール 0 はすべて U1 UserID 値で構成されていることが確認でき、ClickHouse はグラニュール 0 内の最大 URL 値も W3 より小さいと仮定し、グラニュールを除外できます。

2. URL 値が W3 より小さい（または等しい）インデックスマーク 1 と直接後続のインデックスマークの URL 値が W3 より大きい（または等しい）場合は選択されます。これはグラニュール 1 がおそらく URL W3 を含むことを意味します。

3. URL 値が W3 より大きいインデックスマーク 2 および 3 は除外できます。なぜなら、プライマリインデックスのインデックスマークは、各グラニュールの最初のテーブル行のキー列値を保存しており、テーブル行はキー列値に基づいてディスクにソートされるため、グラニュール 2 および 3 では URL 値 W3 が存在できないためです。

前のキー列が高い基数を持つ場合

UserID に高い基数がある場合、同じ UserID 値が複数のテーブル行およびグラニュールに広がる可能性は低くなります。これは、インデックスマークの URL 値が単調に増加しないことを意味します：

上記の図では、W3 よりも URL 値が小さいすべてのマークがその関連するグラニュールの行を ClickHouse エンジンにストリーミングするための選択を受けていることが示されています。

これは、図内のすべてのインデックスマークがシナリオ 1 に該当するが、示された除外前提条件を満たしていないためです。それは、直接後続のインデックスマークが現在のマークと同じ UserID 値を持つことから、除外できないからです。

例えば、URL 値が W3 より小さいインデックスマーク 0 に注目すると、その直接後続のインデックスマーク 1 も W3 より小さいが、マーク 1 の UserID 値は 0 と異なるため除外できません。

これが最終的に、ClickHouse がグラニュール 0 の最大 URL 値についての仮定を行うことを妨げます。代わりに、ClickHouse はグラニュール 0 に行が存在する可能性があると仮定し、マーク 0 の選択を余儀なくされます。

同様のシナリオがマーク 1、2、および 3 に対しても当てはまります。

結論

ClickHouse が一般的な除外検索アルゴリズムを使用するのは、前のキー列が低い基数を持つ場合において、特に効果的です。

サンプルデータセットでは、両方のキー列（UserID、URL）が高い基数を持ち、説明されたように、一般的な除外検索アルゴリズムは、URL カラムの前のキー列が高い（または等しい）基数を持つ場合にはあまり効果的ではありません。

データスキップインデックスについての注意事項

UserID と URL の基数が似て高いため、私たちの URL でのフィルタリングクエリ も、複合主キー (UserID、URL) の URL カラムに対する 二次データスキッピングインデックス 作成からあまり利益を得ることはできません。

例えば、次の 2 つのステートメントは、テーブルの URL カラムに対する minmax データスキッピングインデックスを作成し、充填します：

ClickHouse は、4 つの連続する グラニュール のグループごとに最小および最大の URL 値を保存する追加のインデックスを作成しました（上記の ALTER TABLE ステートメントの GRANULARITY 4 句に注目）。

最初のインデックスエントリ（上の図の「マーク 0」）は、テーブルの最初の 4 つのグラニュールに属する行の最小および最大の URL 値を保存しています。

2 番目のインデックスエントリ（「マーク 1」）は、テーブルの次の 4 つのグラニュールに属する行に対する最小および最大の URL 値を保存し、以下同様です。

（ClickHouse は、インデックスマークに関連付けられたグラニュールのグループを 特定するための 特別なマークファイル も作成しました。）

UserID と URL の基数が似て高いため、この二次データスキッピングインデックスは、私たちの URL でのフィルタリングクエリ が実行された場合にグラニュールの選択から除外するのに役立つことはありません。

クエリが探している特定の URL 値（すなわち 'http://public_search'）は、インデックスがそれぞれのグラニュールグループに保存している最小値と最大値の間にある可能性が高く、そのため ClickHouse はグラニュールグループを選択せざるを得ません（それらがクエリと一致する行を含んでいる可能性があるため）。

複数の主インデックスを使用する必要性

その結果、特定の URL を持つ行のためにサンプルクエリを大幅に高速化する必要がある場合、クエリに最適化された主インデックスを使用する必要があります。

さらに、特定の UserID を持つ行のためにサンプルクエリの良好なパフォーマンスを維持したい場合、複数の主インデックスを使用する必要があります。

これは、次のような方法で実現できます。

追加の主インデックスを作成するオプション

特定の UserID を持つ行をフィルタリングするサンプルクエリと特定の URL を持つ行をフィルタリングするサンプルクエリの両方を大幅に高速化したい場合、次の 3 つのオプションのいずれかを使用して、複数の主インデックスを使用する必要があります：

• 異なる主キーを持つ第二のテーブルを作成する。
• 既存のテーブルにマテリアライズドビューを作成する。
• 既存のテーブルにプロジェクションを追加する。

これら 3 つのオプションは、テーブルの主インデックスおよび行のソート順を再編成するために、サンプルデータを追加のテーブルに効果的に複製します。

しかし、3 つのオプションは、クエリのルーティングや挿入ステートメントに関して、ユーザーに対する追加のテーブルの透過性において異なります。

異なる主キーを持つ第二のテーブルを作成する場合、クエリはクエリに最適なテーブルバージョンに明示的に送信する必要があり、新しいデータは両方のテーブルに明示的に挿入されて、テーブルを同期する必要があります：

マテリアライズドビューの場合、追加のテーブルは自動的に作成され、データは両方のテーブル間で自動的に同期されます：

そして、プロジェクションは最も透過的なオプションであり、暗黙的に作成された（そして隠された）追加のテーブルをデータの変更に基づいて自動的に同期させるだけでなく、ClickHouse はクエリに最も効果的なテーブルバージョンを自動的に選択します：

以下では、複数の主インデックスを作成して使用するための 3 つのオプションについて、さらに詳細に、実際の例と共に議論します。

Option 1: セカンダリテーブル

プライマリキーのキーカラムの順序を元のテーブルと比較して入れ替えた新しい追加テーブルを作成します。

元のテーブルからすべての 8.87百万行を追加テーブルに挿入します:

レスポンスは次のようになります:

最後にテーブルを最適化します:

プライマリキーのカラムの順序を変更したため、挿入された行はディスクに異なる辞書順で保存され（元のテーブルと比較して）、そのテーブルの 1083 グラニュールも以前とは異なる値を含んでいます:

これが結果のプライマリキーです:

これを使用して、URL カラムでフィルタリングされた例のクエリの実行を大幅に高速化できます。これは、最も頻繁に「http://public_search」をクリックしたトップ 10 のユーザーを計算するためのクエリです:

レスポンスは次のようになります:

今や、ほぼ全テーブルスキャンを行う代わりに、ClickHouse はそのクエリをはるかに効果的に実行しました。

元のテーブルのプライマリインデックスでは、UserID が最初で、URL が 2 番目のキーカラムでしたが、ClickHouse はクエリを実行するためにインデックスマークの上で 一般的な排他検索 を使用し、UserID と URL の間の同様に高いカーディナリティにより、あまり効果的ではありませんでした。

URL をプライマリインデックスの最初のカラムとして使用することで、ClickHouse は現在、インデックスマークの上で二分探索を実行しています。 ClickHouse サーバーログファイルの対応するトレースログがそれを確認しました:

ClickHouse は、一般的な排他検索を使用した際の 1076 ではなく、わずか 39 インデックスマークを選択しました。

追加テーブルは、URL でフィルタリングされた例のクエリの実行を高速化するために最適化されています。

元のテーブルでのクエリの悪いパフォーマンスと同様に、UserIDs に対するフィルタリングの例のクエリは新しい追加テーブルであまり効果的には実行されません。なぜなら、UserID がこのテーブルのプライマリインデックスの 2 番目のキーカラムになったからであり、ClickHouse はそのため、グラニュール選択に一般的な排他検索を使用するからです。UserID と URL のカーディナリティが同じように高い場合（/guides/best-practices/sparse-primary-indexes#generic-exclusion-search-algorithm）。

詳細を知りたい場合は、詳細ボックスを開いてください。

UserIDs に対するフィルタリングのクエリのパフォーマンスは悪い

レスポンスは以下のようになります:

サーバーログ:

私たちは現在、2 つのテーブルを所有しています。UserIDs に対するフィルタリングのクエリを高速化するために最適化され、URLs に対するクエリを高速化するために最適化されたテーブルです。

Option 2: マテリアライズドビュウ

既存のテーブルに対してマテリアライズドビューを作成します。

レスポンスは次のようになります:

注記

• ビューのプライマリキーのキーカラムの順序を（元のテーブルと比較して）入れ替えます
• マテリアライズドビューは、所定のプライマリキーディフィニションに基づいて、暗黙的に作成されたテーブルによってバックアップされています
• 暗黙的に作成されたテーブルは、SHOW TABLES クエリによってリスト表示され、名前は .inner で始まります
• マテリアライズドビューのバックアップテーブルを最初に明示的に作成し、その後、TO [db].[table] 句を通じてそのテーブルをターゲットにすることも可能です
• POPULATE キーワードを使用して、元のテーブル hits_UserID_URL から 8.87 百万行すべてで暗黙的に作成されたテーブルを即座に埋めます
• 新しい行がソーステーブル hits_UserID_URL に挿入されると、その行は暗黙的に作成されたテーブルにも自動的に挿入されます
• 実際には、暗黙的に作成されたテーブルは、セカンダリテーブルとして明示的に作成したテーブル と同じ行の順序およびプライマリインデックスを持っています:

ClickHouse は、カラムデータファイル (.bin)、マークファイル (.mrk2)、および暗黙的に作成されたテーブルのプライマリインデックス (primary.idx)を、ClickHouse サーバーディレクトリの特別なフォルダに保存します:

暗黙的に作成されたテーブル（およびそのプライマリインデックス）は、URL カラムでフィルタリングされた例のクエリの実行を大幅に高速化するために今や使用できます:

レスポンスは次のようになります:

実際に、プライマリインデックスのバックアップとして暗黙的に作成されたテーブルは、セカンダリテーブルとして明示的に作成したテーブル と同一のものであり、このためクエリは明示的に作成したテーブルと同じ効果的な方法で実行されます。

ClickHouse サーバーログファイルの対応するトレースログは、ClickHouse がインデックスマークの上で二分探索を実行していることを確認します:

Option 3: プロジェクション

既存のテーブルにプロジェクションを作成します:

そしてプロジェクションをマテリアライズします:

注記

• プロジェクションは、所定の ORDER BY 句に基づく行の順序とプライマリインデックスを持つ隠れたテーブルを作成します
• 隠れたテーブルは、SHOW TABLES クエリではリスト表示されません
• MATERIALIZE キーワードを使用して、元のテーブル hits_UserID_URL から 8.87 百万行すべてで隠れたテーブルを即座に埋めます
• 新しい行がソーステーブル hits_UserID_URL に挿入されると、その行は暗黙的に作成されたテーブルにも自動的に挿入されます
• クエリは常に（文法的に）ソーステーブル hits_UserID_URL をターゲットにしていますが、もし隠れたテーブルの行の順序とプライマリインデックスがより効果的なクエリ実行を可能にする場合、その隠れたテーブルが代わりに使用されます
• プロジェクションは、プロジェクションの ORDER BY ステートメントが一致していても、ORDER BY を使用するクエリがより効率的になるわけではありません (see https://github.com/ClickHouse/ClickHouse/issues/47333)
• 実際には、暗黙的に作成された隠れたテーブルは、セカンダリテーブルとして明示的に作成したテーブル と同じ行の順序およびプライマリインデックスを持っています:

ClickHouse は、カラムデータファイル (.bin)、マークファイル (.mrk2)、および隠れたテーブルのプライマリインデックス (primary.idx) を、ソーステーブルのデータファイル、マークファイル、プライマリインデックスファイルの隣にある特別なフォルダ（下のスクリーンショットでオレンジ色でマーク）に保存します:

プロジェクションによって作成された隠れたテーブル（およびそのプライマリインデックス）は、URL カラムでフィルタリングされた例のクエリの実行を大幅に高速化するために今や使用できます。クエリは文法的にプロジェクションのソーステーブルをターゲットにしています。

レスポンスは以下のようになります:

実際に、プロジェクションによって作成された隠れたテーブル（およびそのプライマリインデックス）は、セカンダリテーブルとして明示的に作成したテーブル と同一であり、このためクエリは明示的に作成したテーブルと同じ効果的な方法で実行されます。

ClickHouse サーバーログファイルの対応するトレースログは、ClickHouse がインデックスマークの上で二分探索を実行していることを確認します:

Summary

UserID と URL の複合プライマリキーを持つテーブルのプライマリインデックスは、UserID に基づくクエリのフィルタリングを高速化するのに役立ちました。しかし、そのインデックスは、URL に基づくクエリのフィルタリングを高速化するのにはあまり明確な助けは提供しません。URL カラムが複合プライマリキーの一部であってもですが。

そして、逆もまた然りです: URL と UserID の複合プライマリキーを持つテーブルのプライマリインデックスは、URL に基づくクエリのフィルタリングを高速化するのには役立ちましたが、UserID に基づくクエリのフィルタリングに対してはあまり効果を提供しません。

UserID と URL のプライマリキーのカラムの同様に高いカーディナリティのため、2 番目のキーカラムでフィルタリングされるクエリは、インデックスにある 2 番目のキーカラムからあまり恩恵を受けない。

したがって、プライマリインデックスから 2 番目のキーカラムを削除し（インデックスのメモリ消費を少なくすることになります）、複数のプライマリインデックスを使用する方が理にかなっています。

ただし、複合プライマリキー内のキーカラムに大きなカーディナリティの違いがある場合、クエリにとって有益な処理を行うために、プライマリキーカラムを昇順にカーディナリティでソートすることの方が良いです。

キーカラム間のカーディナリティ差が大きいほど、それらのカラムの順序は重要となります。次のセクションでそのことを証明していきます。

キーカラムを効率的に順序付ける

複合プライマリキー内のキーカラムの順序は、次の両者に大きな影響を与えます:

• クエリ内のセカンダリキーカラムに対するフィルタリングの効率と、
• テーブルのデータファイルの圧縮率。

これを実証するために、次の 3 つのカラムを持つ、インターネットの「ユーザー」(UserID カラム) が URL (URL カラム) にアクセスした際にボットトラフィックとしてマークされたかを示すサンプルデータセットを使用します。

• 特定の URL へのトラフィックのうち、ボットによるものがどれくらい (パーセント) なのか
• 特定のユーザーが (ボットでない) かどうかの信頼度 (そのユーザーからのトラフィックのうち、どのくらいがボットトラフィックでないと見なされるか)

上記の 3 つのカラムをキーカラムとして使用する複合プライマリキーのカーディナリティを計算するため、このクエリを使用します（注意: TSV データをローカルテーブルを作成することなく、即席でクエリするために URL テーブル関数 を使用しています）。以下のクエリを clickhouse client で実行します:

レスポンスは次のようになります:

私たちは、URL と IsRobot カラムの間で特にカーディナリティに大きな違いがあることを確認できます。したがって、複合プライマリキーでこれらのカラムの順序は、これらのカラムのフィルタリングの効率を高め、テーブルのカラムデータファイルの最適な圧縮比を達成するために重要です。

このことを示すために、私たちはボットトラフィック分析データ用に 2 つのテーブルバージョンを作成します:

• (URL, UserID, IsRobot) の複合プライマリキーを持つテーブル hits_URL_UserID_IsRobot
• (IsRobot, UserID, URL) の複合プライマリキーを持つテーブル hits_IsRobot_UserID_URL

hits_URL_UserID_IsRobot テーブルを (URL, UserID, IsRobot) の複合プライマリキーで作成します:

そして、8.87 百万行で埋め込みます:

レスポンスは次のようになります:

次に、hits_IsRobot_UserID_URL テーブルを (IsRobot, UserID, URL) の複合プライマリキーで作成します:

そして、前のテーブルを埋めるために使用したのと同じ 8.87 百万行で埋め込みます:

レスポンスは次のようになります:

セカンダリキーカラムの効率的なフィルタリング

クエリが複合キーの一部であるカラムでフィルタリングし、かつそれが最初のキーカラムである場合、ClickHouse はインデックスマークの上でバイナリ検索アルゴリズムを実行します。

クエリが複合キーの一部であるカラムでのみフィルタリングしているが、それが最初のキーカラムでない場合、ClickHouse はインデックスマークの上で一般的な排他検索アルゴリズムを使用します。

第二のケースでは、複合プライマリキー内でのキーカラムの順序は、一般的な排他検索アルゴリズムの効果に影響を与えます。

これは、キーカラム (URL, UserID, IsRobot) の順序をカーディナリティに降順にしたテーブルの UserID カラムでフィルタリングしているクエリです:

レスポンスは次のようになります:

次に、キーカラム (IsRobot, UserID, URL) の順序をカーディナリティに昇順にしたテーブルに対して同じクエリを実行します:

レスポンスは次のようになります:

テーブルでのキーカラムの順序をカーディナリティに降順にした場合と比較して、迅速性が非常に大きく効果的であることがわかります。

その理由は、一般的な排他検索アルゴリズムが、前のキーカラムが低いカーディナリティである場合に、セカンダリキーカラムを介してグラニュールが選択されるとうまく機能するからです。このことについては、ガイドの前のセクションで詳しく説明しました。

データファイルの最適圧縮率

次のクエリは、上記で作成した 2 つのテーブルの UserID カラムの圧縮率を比較します:

レスポンスは以下のようになります:

UserID カラムの圧縮率は、カーディナリティに昇順にソートされたテーブルの方が非常に高いことがわかります。

両方のテーブルに正確に同じデータが保存されているにも関わらず（両方のテーブルに同じ 8.87 百万行を挿入しました）、複合プライマリキー内のキーカラムの順序は、テーブルのカラムデータファイル内の圧縮データが必要とするディスクスペースの大きさに大きな影響を与えています:

• 複合プライマリキーが (URL, UserID, IsRobot) でキーカラムの順序がカーディナリティに降順の場合、UserID.bin データファイルのディスクスペースは 11.24 MiB です
• 複合プライマリキーが (IsRobot, UserID, URL) でキーカラムの順序がカーディナリティに昇順の場合、UserID.bin データファイルのディスクスペースは 877.47 KiB です

ディスク上のテーブルのカラムに対して良好な圧縮率を持つことは、ディスクスペースを節約するだけでなく、当該カラムからのデータをメインメモリ（オペレーティングシステムのファイルキャッシュ）に移動するために必要な入出力が少なくなるため、（特に分析用の）クエリがより高速になります。

次のセクションで、テーブルのカラムに対する圧縮率を最適化するためにプライマリキーのカラムを昇順にソートすることがいかに有益であるかを説明します。

以下の図は、カーディナリティによって昇順に並べられたプライマリキーの行がディスク上での順序を示しています:

私たちは、テーブルの行データがプライマリキーのカラムに沿ってディスクに保存されることを確認しました。

上記の図では、テーブルの行（そのカラム値がディスク上）はまずその cl 値によってオーダーされ、同じ cl 値を持つ行はその ch 値によってオーダーされます。そして、最初のキーカラム cl が低いカーディナリティであるため、同じ cl 値を持つ行がある可能性が高く、これにより ch 値がローカルでオーダーされる可能性が高いのです。

もしデータが似たようなものだと近くに配置されている場合（例えば、ソートによって）、そのデータはよりよく圧縮されます。 一般的に、圧縮アルゴリズムは、データのランレングスが多いほど（データが多ければ多いほど圧縮にとって良いことです）および局所性（データが似たようなものであるほど圧縮率が良いことです）に利益を得ます。

上記の図と対照的に、下記の図は、カーディナリティに降順で順序付けられたプライマリキーのディスク上での行を示しています:

ここでは、テーブルの行はまずその ch 値によってオーダーされ、同じ ch 値を持つ行はその cl 値によってオーダーされます。 しかし、最初のキーカラム ch が高いカーディナリティであるため、同じ ch 値を持つ行が存在する可能性は低く、これにより cl 値がローカルでオーダーされる可能性も低くなります。

したがって、cl 値は最も可能性としてランダムな順序にあり、したがって局所性や圧縮率が悪くなります。

Summary

クエリにおけるセカンダリキーカラムの効率的フィルタリングとテーブルのカラムデータファイルの圧縮率の両方に対して、プライマリキー内のカラムの順序をカーディナリティに沿って昇順に並べることが有益です。

関連コンテンツ

• ブログ: ClickHouse のクエリをスーパーチャージする

単一行の特定を効率的に行う

一般的に、ClickHouse にとっての最良の使用ケースではありませんが、時々 ClickHouse 上に構築されたアプリケーションは、ClickHouse テーブルの単一行を特定する必要があります。

その直感的な解決策は、各行にユニークな値を持つ UUID カラムを使用し、そのカラムをプライマリキーとして使用して行を迅速に取得することです。

最も迅速に取得するためには、UUID カラムは最初のキーカラムである必要があります。

私たちはClickHouse テーブルの行データがディスクに保存され、プライマリキーのカラムによって並べられているため、非常に高いカーディナリティのカラム（UUID カラムのような）をプライマリキーまたは複合プライマリキー内の低いカーディナリティのカラムの前に置くことは、テーブルの他のカラムの圧縮率に悪影響を及ぼします。

最も迅速に取得することと、データ圧縮を最適化することとの妥協案は、複合プライマリキーを使用し、UUIDを最後のキーカラム、低（または）カーディナリティのキーカラムの後に配置することです。

A concrete example

一つの具体例は、Alexey Milovidov が開発し、ブログに書いたプレーンテキストペーストサービス https://pastila.nl です。

テキストエリアの変更があるたびに、データは自動的に ClickHouse テーブルの行に保存されます（変更ごとに一行）。

ペーストされたコンテンツの（特定のバージョンの）識別と取得の方法の一つは、コンテンツのハッシュをそのコンテンツを含むテーブル行の UUID として使用することです。

以下の図は

• コンテンツが変更されるときの行の挿入順（例えば、テキストエリアにテキストを入力するキーストロークによる）と
• PRIMARY KEY (hash) が使用される場合の挿入された行からのデータのディスク上の順序を示しています：

hash カラムが主キー列として使用されるため

• 特定の行を 非常に速く 取得できますが、
• テーブルの行（そのカラムデータ）はディスク上に（ユニークでランダムな）ハッシュ値によって昇順に保存されます。したがって、コンテンツカラムの値もランダム順で保存され、データの局所性がないため、コンテンツカラムデータファイルの最適でない圧縮比をもたらします。

コンテンツカラムの圧縮比を大幅に改善しつつ、特定の行の迅速な取得を実現するために、pastila.nl は特定の行を識別するために二つのハッシュ（および複合主キー）を使用しています：

• 上述の通り、異なるデータに対して異なるハッシュであるコンテンツのハッシュと、
• 小さなデータの変更で変わらない 局所感度ハッシュ（フィンガープリント） です。

以下の図は

• コンテンツが変更されるときの行の挿入順（例えば、テキストエリアにテキストを入力するキーストロークによる）と
• 複合 PRIMARY KEY (fingerprint, hash) が使用される場合の挿入された行からのデータのディスク上の順序を示しています：

今やディスク上の行はまず fingerprint によって順序付けられ、同じフィンガープリント値を持つ行においては、その hash 値が最終的な順序を決定します。

データが小さな変更のみで異なる場合でも同じフィンガープリント値が付与されるため、今や似たデータはコンテンツカラム上で近くに保存されます。これは、圧縮アルゴリズムが一般的にデータの局所性から恩恵を受けるため、コンテンツカラムの圧縮比を非常に良くします（データがより似ているほど、圧縮比は良くなります）。

妥協点は、複合 PRIMARY KEY (fingerprint, hash) から得られる主インデックスを最適に利用するために特定の行を取得するには二つのフィールド（fingerprint と hash）が必要であることです。