ClickHouseにおける主キーインデックスの実践的な入門
はじめに
このガイドでは、ClickHouseのインデックスについて深く掘り下げていきます。詳細に説明し議論する内容は以下の通りです：
- ClickHouseのインデックスが従来のリレーショナルデータベース管理システムとどのように異なるか
- ClickHouseがテーブルのスパース主キーインデックスをどのように構築し使用しているか
- ClickHouseのインデックスに関するベストプラクティス
このガイドに記載されている全てのClickHouse SQL文やクエリは、お使いのマシン上で実行することも可能です。 ClickHouseのインストールと開始手順については、クイックスタートを参照してください。
データセット
このガイド全体で、サンプルの匿名化されたWebトラフィックデータセットを使用します。
- サンプルデータセットの8.87百万行（イベント）のサブセットを使用します。
- 非圧縮データサイズは8.87百万イベントで約700 MBです。ClickHouseに保存すると200 MBに圧縮されます。
- このサブセットには、特定の時刻（
EventTimeカラム）にURL（URLカラム）をクリックしたインターネットユーザーを示す3つのカラムが含まれています（UserIDカラム）。
これらの3つのカラムを使用して、次のような典型的なWeb分析クエリを既に構築できます：
- "特定のユーザーが最もクリックした上位10のURLは何ですか？"
- "特定のURLを最も頻繁にクリックした上位10のユーザーは誰ですか？"
- "特定のURLをユーザーがクリックする最も人気のある時間（例えば、曜日）はいつですか？"
テストマシン
この文書に記載されている全ての実行時数値は、Apple M1 Proチップを搭載したMacBook Pro上でClickHouse 22.2.1をローカルで実行した結果に基づいています。
フルテーブルスキャン
主キーなしでデータセットに対してクエリがどのように実行されるかを見るために、次のSQL DDL文を実行してテーブル（MergeTreeテーブルエンジンを使用）を作成します：
次に、以下のSQL挿入文でヒットデータセットのサブセットをテーブルに挿入します。 これは、clickhouse.comでホストされているフルデータセットのサブセットをロードするためにURLテーブル関数を使用します：
レスポンスは：
ClickHouseクライアントの結果出力は、上記の文がテーブルに8.87百万行を挿入したことを示しています。
最後に、このガイドの後の議論を簡素化し、図および結果を再現可能にするために、FINALキーワードを使用してテーブルを最適化します：
一般的に、データをロードした後に即座にテーブルを最適化することは必要ではなく、推奨されません。この例においてなぜそれが必要なのかは明白になるでしょう。
次に、最初のWeb分析クエリを実行します。以下は、UserID 749927693のインターネットユーザーが最もクリックした上位10のURLを計算しています：
レスポンスは：
ClickHouseクライアントの結果出力は、ClickHouseがフルテーブルスキャンを実行したことを示しています！私たちのテーブルの8.87百万行の各行がClickHouseにストリーミングされました。それではスケールしません。
これを（はるかに）効率的で（さらに）高速にするためには、適切な主キーを持つテーブルを使用する必要があります。これにより、ClickHouseは自動的に（主キーのカラムに基づいて）スパース主キーインデックスを作成でき、これが私たちの例のクエリの実行を大幅にスピードアップします。
ClickHouseインデックス設計
大規模データスケールのためのインデックス設計
従来のリレーショナルデータベース管理システムでは、主インデックスはテーブル行ごとに1つのエントリを含みます。これにより、主インデックスにはデータセットに対して8.87百万のエントリが含まれることになります。このようなインデックスは特定の行を迅速に特定することを可能にし、検索クエリおよびポイント更新の効率が向上します。B(+)-Treeデータ構造でエントリを検索する場合の平均時間計算量はO(log n)です。より正確には、log_b n = log_2 n / log_2 bとなり、bはB(+)-Treeの分岐係数で、nはインデックスされた行の数です。通常bは数百から数千の間であるため、B(+)-Treesは非常に浅い構造であり、レコードを特定するために必要なディスクシークが少なくて済みます。8.87百万行および分岐係数が1000の場合、平均して2.3のディスクシークが必要です。この能力はコストを伴います：ディスクとメモリのオーバーヘッドが追加され、新しい行をテーブルに追加したりインデックスにエントリを追加する際のコストが高くなり、時にはB-Treeの再バランスも必要です。
B-Treeインデックスに関連する課題を考慮し、ClickHouseのテーブルエンジンは異なるアプローチを採用しています。ClickHouseのMergeTreeエンジンファミリーは、大規模データボリュームを処理するために設計および最適化されています。これらのテーブルは、秒間に数百万の行の挿入を受け取り、非常に大きな（数百ペタバイト）データボリュームを格納するように設計されています。データは、背景でパーツをマージするためのルールが適用されながら、パーツごとに迅速にテーブルに書き込まれます。ClickHouseでは、各パーツには独自の主インデックスがあります。パーツがマージされると、マージされたパーツの主インデックスもマージされます。ClickHouseが設計されている非常に大規模の範囲では、ディスクとメモリの効率が非常に重要です。したがって、すべての行をインデックスする代わりに、パーツの主インデックスには、行のグループ（「グラニュール」）ごとに1つのインデックスエントリ（「マーク」と呼ばれる）が存在します。この技術はスパースインデックスとして知られています。
スパースインデックスは、ClickHouseがパーツの行を主キーのカラムによってディスクに順序付けて存储しているために可能です。単一の行を特定するのではなく（B-Treeベースのインデックスのように）、スパース主インデックスは、インデックスエントリに対する二分探索を通じて、クエリと一致する可能性のある行のグループを迅速に特定します。特定されたグループの潜在的に一致する行（グラニュール）は、クエリの一致を見つけるためにClickHouseエンジンに並行してストリーミングされます。このインデックス設計により、主インデックスは小さく（完全にメインメモリに収まる必要があり）、クエリ実行時間を大幅に短縮し、特にデータ分析ユースケースで典型的な範囲クエリにおいて効果を発揮します。
以下では、ClickHouseがスパース主インデックスをどのように構築・使用しているかを詳細に示します。記事の後半では、インデックスを構築するために使用されるテーブルカラム（主キーのカラム）を選定、削除、及び順序付けるためのベストプラクティスについて議論します。
主キーを持つテーブル
UserIDおよびURLをキーとした複合主キーを持つテーブルを作成します：
DDL文の詳細
このガイドの後の議論を簡素化し、図や結果を再現可能にするために、DDL文は以下を指定します：
ORDER BY句を通じてテーブルに対する複合ソートキーを指定します。主インデックスのエントリ数を設定を通じて明示的に制御します：
index_granularity：デフォルト値の8192に明示的に設定されます。これは、8192行ごとに主インデックスに１つのインデックスエントリが存在することを意味します。例えば、テーブルが16384行を含むなら、インデックスは2つのインデックスエントリを持ちます。index_granularity_bytes：0に設定して、適応インデックスグラニュラリティを無効にします。適応インデックスグラニュラリティとは、ClickHouseが自動的にn行のグループのために１つのインデックスエントリを作成することを意味します。これは以下のいずれかが真である場合です：nが8192未満で、そのn行の結合行データのサイズが10 MB（index_granularity_bytesのデフォルト値）以上である場合。n行の結合データサイズが10 MB未満である場合でも、nが8192である場合。
compress_primary_key：0に設定して、主インデックスの圧縮を無効にします。これにより、後でその内容をオプションで調べることができるようになります。
上記のDDL文の主キーは、指定された2つのキー列に基づいて主インデックスの作成を引き起こします。
次にデータを挿入します：
レスポンスは以下のようになります：
そしてテーブルを最適化します：
次のクエリを使用してテーブルに関するメタデータを取得できます：
レスポンスは：
ClickHouseクライアントの出力は以下のことを示しています：
- テーブルのデータは、特定のディレクトリ内のワイドフォーマットで保存され、ディレクトリ内に各テーブルカラムごとに1つのデータファイル（および1つのマークファイル）があります。
- テーブルには8.87百万行があります。
- 全行の非圧縮データサイズは733.28 MBです。
- 全行の圧縮サイズは206.94 MBです。
- テーブルには1083のエントリ（「マーク」と呼ばれる）を持つ主インデックスがあり、インデックスのサイズは96.93 KBです。
- 合計で、テーブルのデータ、マークファイル、および主インデックスファイルは207.07 MBを占めます。
データは主キーのカラムで順序付けられてディスクに保存される
上記で作成したテーブルは以下の条件を満たしています：
-
もしソートキーのみを指定していた場合、主キーは暗黙的にソートキーと等しいものとして定義されます。
-
メモリ効率を向上させるため、クエリでフィルタリングするカラムのみを含む主キーを明示的に指定しました。主キーに基づく主インデックスは完全にメインメモリにロードされます。
-
ガイドの図の一貫性を保ち、圧縮比を最大化するために、すべてのテーブルカラムを含む別のソートキーを定義しました（カラム内に類似のデータが近接して配置されると、そのデータはより良く圧縮されます）。
-
主キーは、両方が指定されている場合には、ソートキーのプレフィックスである必要があります。
挿入された行は、主キーのカラム（およびソートキーからの追加カラムであるEventTime）によって、辞書順（昇順）でディスクに保存されます。
ClickHouseは、同一の主キーのカラム値を持つ複数の行を挿入することを許可しています。この場合（下の図の行1および行2を参照）、最終的な順序は指定されたソートキーによって決定されるため、EventTime列の値に基づいて決まります。
ClickHouseは列指向データベース管理システムです。以下の図に示すように
- ディスク上の表現においては、各テーブルカラムごとに単一のデータファイル (*.bin) があり、そのカラムのすべての値が圧縮形式で保存され、
- 8.87百万行は主キーのカラム（および追加のソートキーのカラム）によって、辞書順の昇順でディスクに保存されています。つまり、この場合は
- まず
UserIDによって、 - 次に
URLによって、 - 最後に
EventTimeによって：
- まず
UserID.bin、URL.bin、およびEventTime.binは、UserID、URL、およびEventTimeカラムの値が保存されるディスク上のデータファイルです。
-
主キーはディスク上の行の辞書順を決定するため、テーブルには1つの主キーしか持てません。
-
行はClickHouseの内部の行番号付け方式に合わせて0から始まる番号付けをしています。この方式は、ログメッセージにも使用されています。
データは並行データ処理のためにグラニュールに編成されている
データ処理の目的で、テーブルのカラム値は論理的にグラニュールに分割されます。 グラニュールはClickHouseにストリーミングされる最小の不可分なデータセットです。 これは、個々の行を読み込むのではなく、ClickHouseが常に行のグループ（グラニュール）全体を（ストリーミング方式で並行して）読み込むことを意味します。
カラム値はグラニュール内に物理的に保存されているわけではありません。グラニュールはクエリ処理のためのカラム値の論理的な組織です。
以下の図は、テーブルの8.87百万行の（カラム値が）どのように1083のグラニュールに編成されているかを示しています。これは、テーブルのDDL文が設定index_granularity（デフォルト値の8192に設定）を含んでいるためです。
最初（物理ディスク上の順序に基づく）8192行（そのカラム値）は論理的にグラニュール0に属し、次の8192行（そのカラム値）はグラニュール1に属する、そしてそのように続いています。
-
最後のグラニュール（グラニュール1082）は8192行未満を"含んでいます"。
-
このガイドの冒頭で「DDL文の詳細」の中で、適応インデックスグラニュラリティを無効にしたことを示しました（このガイドの議論を簡素化するため、および図や結果を再現できるように）。
したがって、私たちの例のテーブルのすべてのグラニュール（最後のものを除く）は、同じサイズを持ちます。
-
適応インデックスグラニュラリティを持つテーブルの場合（インデックスグラニュラリティはデフォルトで適応であるため）、いくつかのグラニュールのサイズは行のデータサイズに応じて8192行未満になることがあります。
-
主キーのカラム（
UserID、URL）のいくつかのカラム値にオレンジ色のマークを付けました。 これらのオレンジ色でマークされたカラム値は、各グラニュールの最初の行の主キーのカラム値です。 以下で見えるように、これらのオレンジ色でマークされたカラム値がテーブルの主インデックスのエントリになります。 -
グラニュールは0から番号付けを始めており、ClickHouseの内部の番号付け方式に合わせています。これもログメッセージに使用されています。
主インデックスはグラニュールごとに1つのエントリを持つ
主インデックスは、上記の図に示されたグラニュールに基づいて作成されます。このインデックスは、呼ばれる数値インデックスマークの無圧縮フラット配列ファイル（primary.idx）です。0から始まります。
以下の図は、インデックスがグラニュールごとに各最初の行の主キーのカラム値（上記の図にオレンジ色でマークされた値）を保存していることを示しています。 つまり、主インデックスはテーブルの各8192行目の主キーのカラム値を保存します（物理行の順序は主キーのカラムによって定義されています）。
例えば、
- 最初のインデックスエントリ（以下の図で「マーク0」）は、上記のグラニュール0の最初の行のキーのカラム値を保存しています。
- 2番目のインデックスエントリ（以下の図で「マーク1」）は、上記のグラニュール1の最初の行のキーのカラム値を保存しています。そしてそのように続きます。
合計で、インデックスは8.87百万行と1083のグラニュールを持つテーブルに対して1083のエントリを持ちます：
-
適応インデックスグラニュラリティを持つテーブルの場合、追加の「最終」マークが主インデックスに保存され、テーブルの最後の行の主キーのカラム値を記録します。しかし、私たちはガイドを簡素化するために適応インデックスグラニュラリティを無効にしたため（このガイドの議論を簡素化するため）、私たちの例のテーブルのインデックスにはこの最終マークは含まれていません。
-
主インデックスファイルは完全にメインメモリにロードされています。ファイルが利用可能な空きメモリよりも大きい場合、ClickHouseはエラーを発生させます。
主インデックスの内容を調査する
セルフマネージドのClickHouseクラスターでは、ファイルテーブル関数を使用して、サンプルテーブルの主インデックスの内容を調べることができます。
そのためには、まず主インデックスファイルを稼働中のクラスターのノードのuser_files_pathにコピーする必要があります：
- ステップ1：主インデックスファイルを含むパートのパスを取得
- ステップ2：user_files_pathを取得 デフォルトのuser_files_pathはLinuxでは
- ステップ3：主インデックスファイルをuser_files_pathにコピーします
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を返します。
/var/lib/clickhouse/user_files/であり、変更があったか確認するにはLinuxで次のコマンドを実行します：$ grep user_files_path /etc/clickhouse-server/config.xml
テストマシンでは、パスは/Users/tomschreiber/Clickhouse/user_files/です。
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経由で主インデックスの内容を調査できます：
- エントリの数を取得
- 最初の2つのインデックスマークを取得
- 最後のインデックスマークを取得
SELECT count( )<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String');
は1083を返します。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値、および以降のすべてのグラニュールの値が4,073,710以上であることを保証します。
後で見るように、このグローバルな順序により、ClickHouseはクエリが主キーの最初のカラムのフィルタリングを行う場合、インデックスマークに対して二分探索アルゴリズムを使用できます。
-
URLインデックスマーク：
主キーのカラムである
UserIDおよびURLのカーディナリティが非常に似ているため、主キーの最初のカラム以降のすべてのキーのインデックスマークは、一般的に、前のキーのカラム値が現在のグラニュール内のすべてテーブルの行について同じである限り、データ範囲を示しています。
例えば、上の図のマーク0とマーク1のUserID値が異なるため、ClickHouseはグラニュール0内のすべてのテーブル行のURL値が'http://showtopics.html%3...'以上であると仮定することはできません。しかし、上述の図でマーク0とマーク1のUserID値が同じであった場合（それはグラニュール0内のすべてのテーブル行のUserID値が同じことを意味します）、ClickHouseはすべてのテーブル行のURL値が'http://showtopics.html%3...'以上であると仮定できました。このクエリ実行性能への影響について、後でさらに詳しく議論します。
主インデックスはグラニュールを選択するために使用される
これで、主インデックスのサポートを受けてクエリを実行することができます。
以下は、UserID 749927693のために最もクリックされた上位10のURLを計算します。
レスポンスは：
ClickHouseクライアントの出力は、フルテーブルスキャンを行うのではなく、わずか8.19千行がClickHouseにストリーミングされたことを示しています。
もしトレースログが有効になっている場合、ClickHouseのサーバーログファイルは、ClickHouseが749927693という値を持つUserIDの列を含む行を特定するために、1083のUserIDインデックスマークに対して二分探索を実行したことを示しています。これは平均して19ステップを要し、計算量がO(log2 n)となります：
上記のトレースログで、1083の既存のマークのうち1つがクエリを満たしていたのが分かります。
トレースログの詳細
マーク176が特定されました（'見つかった左境界マーク'は包括的で、'見つかった右境界マーク'は排他的です）、したがって、グラニュール176のすべての8192行（行1,441,792から始まる - これは後でこのガイドで確認します）がClickHouseにストリーミングされ、最終的にUserID列の値が749927693である行を特定することになります。
このことは、私たちの例のクエリでEXPLAIN句を使って再現することもできます。
レスポンスは以下のようになります：
クライアント出力は、1083のグラニュールのうち1つが749927693というUserIDの値を持つ行を含む可能性があるとして選択されたことを示しています。
クエリが複合キーの一部であり、最初のキーのカラムに対してフィルタリングする場合、ClickHouseはそのキーのカラムのインデックスマークに対して二分探索アルゴリズムを実行します。
上記で議論したように、ClickHouseはスパース主インデックスを使用して、クエリと一致する行を含む可能性のあるグラニュールを迅速に（バイナリサーチを介して）選択します。
これはClickHouseのクエリ実行の**第一段階（グラニュール選択）**です。
**第二段階（データ読み込み）**では、ClickHouseは選択されたグラニュールを特定し、すべての行をClickHouseエンジンにストリーミングして、実際にクエリに一致する行を見つけ出します。
この第二段階については、次のセクションでさらに詳しく議論します。
Mark files are used for locating granules
次の図は、私たちのテーブルの主インデックスファイルの一部を示しています。
上記で説明したように、インデックスの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」）は、選択されたグラニュールの圧縮版を含むデータファイル内の ブロック を特定しています。この圧縮ブロックは、いくつかの圧縮グラニュールを含む可能性があります。特定された圧縮ファイルブロックは、読み取り時に主メモリに展開されます。
-
Mark-fileからの2番目のオフセット（上の図の「granule_offset」）は、非圧縮ブロックデータ内のグラニュールの位置を提供します。
特定された非圧縮グラニュールに属するすべての8192行は、さらに処理するためにClickHouseにストリーミングされます。
- ワイドフォーマットのテーブルと、適応型インデックスの粒度がないテーブルの場合、ClickHouseは、上記のように、各エントリについて2つの8バイト長のアドレスを含むエントリを持つ
.mrkマークファイルを使用します。これらのエントリは、すべて同じサイズのグラニュールの物理的な位置です。
インデックスの粒度はデフォルトで適応的ですが、私たちの例のテーブルでは適応型インデックス粒度を無効にしているため（このガイドのディスカッションを簡素化し、図や結果を再現可能にするため）、テーブルはワイドフォーマットを使用しています。データサイズが min_bytes_for_wide_part より大きいため（デフォルトでセルフマネージドクラスター向けに10MB）。
-
ワイドフォーマットのテーブルと適応型インデックス粒度を持つテーブルの場合、ClickHouseは、現在のエントリに関連付けられたグラニュールの行数の追加の3番目の値を持つ
.mrk2マークファイルを使用します。 -
コンパクトフォーマットのテーブルの場合、ClickHouseは
.mrk3マークファイルを使用します。
なぜ主インデックスはインデックスマークに対応するグラニュールの物理的な位置を直接含まないのですか？
ClickHouseが設計されているその非常に大規模な規模において、ディスクとメモリの効率を非常に高く保つことが重要だからです。
主インデックスファイルは主メモリに収まる必要があります。
私たちのサンプルクエリのために、ClickHouseは主インデックスを使用し、特定の行を含む可能性のある単一のグラニュールを選択しました。ClickHouseは、対応する行をさらに処理するためにストリーミングするために、その一つのグラニュールの物理的な位置を必要とします。
さらに、このオフセット情報はUserIDとURLカラムにのみ必要です。
クエリで使用されないカラム（例：EventTime）にはオフセット情報は必要ありません。
私たちのサンプルクエリでは、ClickHouseは、UserID データファイル (UserID.bin) 内のグラニュール176の物理的な位置オフセット2つと、URL データファイル (URL.bin) 内のグラニュール176の物理的な位置オフセット2つのみを必要とします。
マークファイルによって提供される間接性は、主インデックス内にインデックスマークに対してすべての1083グラニュールの物理的な位置を直接保存することを避けることができます。したがって、主メモリに不要（おそらく未使用の）データを持つことを回避できます。
次の図と、以下のテキストは、私たちの例のクエリに対してClickHouseがUserID.binデータファイル内のグラニュール176をどのように特定するかを示しています。
このガイドで以前に説明したように、ClickHouseは主インデックスマーク176を選択し、したがってグラニュール176を、私たちのクエリに一致する行を含む可能性があるものとして選択しました。
ClickHouseは、グラニュール176を特定するために、当該マーク番号（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グループを出力します。
Using multiple primary indexes
Secondary key columns can (not) be inefficient
クエリが複合キーの一部であり最初のキー列である列にフィルタリングされている場合、この場合、ClickHouseはキー列のインデックスマークに対してバイナリ検索アルゴリズムを実行します。
しかし、クエリが、複合キーの一部である列にフィルタリングされている場合、最初のキー列でない場合はどうなりますか？
クエリが最初のキー列でフィルタリングしていないが、セカンダリキー列でフィルタリングしているシナリオを説明します。
クエリが最初のキー列と、最初の後の任意のキー列でフィルタリングされている場合、ClickHouseは最初のキー列のインデックスマークに対してバイナリ検索を実行します。
私たちは、URL "http://public_search"を最も頻繁にクリックしたトップ10ユーザーを計算するクエリを使用します：
クライアント出力は、ClickHouseが複合主キーの一部であるにもかかわらず、テーブル全体のスキャンを実行したことを示しています！ ClickHouseは、テーブルの887万行から881万行を読み取ります。
trace_loggingが有効になっている場合、ClickHouseサーバーログファイルは、ClickHouseが、URLカラムの値が"http://public_search"を含む行が含まれる可能性のあるグラニュールを特定するために1083のURLインデックスマークに対して一般的な除外検索を使用したことを示しています：
サンプルトレースログでは、1076（マーク経由で）1083のグラニュールが選択されたことがわかります。
これにより、実際にURL値"http://public_search"を含む行を特定するために、ClickHouseエンジンに埋め込まれた881万行がストリーミングされ（10のストリームを使用して並行処理）、実際に行を特定します。
ただし、後で見るように、選択された1076グラニュールのうち、実際に一致する行を含むのは39グラニュールのみです。
ユーザーIDの特定の値で行をフィルタリングするための非常に役立つ複合主キーに基づいた主インデックスが、特定のURL値で行をフィルタリングするクエリの速度を向上させる上ではそれほど効果的ではありません。
その理由は、URLカラムが最初のキー列でないため、ClickHouseがURLカラムのインデックスマークに対して一般的な除外検索アルゴリズム（バイナリ検索の代わりに）を使用しているためです。そして、このアルゴリズムの効果は、URLカラムとその前のキー列UserIDとの間の基数の違いに依存します。
これを示すために、一般的な除外検索がどのように機能するかについての詳細を示します。
Generic exclusion search algorithm
以下は、ClickHouseの一般的な除外検索アルゴリズムが、前のキー列が低（低い）または高（高い）基数のときに、セカンダリカラムを介してグラニュールが選択されるときにどのように機能するかを示しています。
両方のケースの例として、次のことを仮定します：
- URL値 = "W3"を持つ行を検索するクエリ。
- UserIDとURLの簡略化された値を持つ抽象的なヒットテーブルのバージョン。
- インデックスのために同じ複合主キー（UserID、URL）。これは、行がまずUserID値で順序付けられ、同じUserID値を持つ行が次にURLで順序付けられることを意味します。
- グラニュールサイズは2、つまり各グラニュールに2行が含まれます。
以下の図では、各グラニュールの最初のテーブル行のキー列の値をオレンジ色でマークしています。
UserIDが低い基数を持っていると仮定すると、この場合、同じUserID値が複数のテーブル行およびグラニュール、したがってインデックスマークに分散している可能性が高いです。同じUserIDを持つインデックスマークに対して、インデックスマークのURL値は昇順にソートされています（テーブル行がまずUserIDで順序付けられ、その後URLで順序付けられます）。これは、以下に示すように効率的なフィルタリングを可能にします：
上の図の抽象的なサンプルデータについてのグラニュール選択プロセスには3つの異なるシナリオがあります：
-
インデックスマーク0、URL値がW3より小さく、直接続くインデックスマークのURL値もW3より小さい場合、マーク0および1は同じUserID値を持っているため除外できます。この除外の前提により、グラニュール0は完全にU1のUserID値で構成されていると仮定され、したがってClickHouseはグラニュール0の最大URL値もW3より小さいと仮定し、グラニュールを除外できます。
-
インデックスマーク1、URL値がW3以下で、直接続くインデックスマークのURL値がW3以上の場合 選択されます。これは、グラニュール1がURL W3を含む行を持つ可能性があることを意味します。
-
インデックスマーク2および3に対して、URL値がW3より大きいため、除外できます。これは、主インデックスのインデックスマークが各グラニュールの最初のテーブル行のキー列の値を保存し、テーブル行がディスク上でキー列の値でソートされているため、グラニュール2および3はURL値W3を含むことはできません。
UserIDが高い基数を持っているとき、同じUserID値が複数のテーブル行およびグラニュールに分散している可能性は低いです。これは、インデックスマークのURL値が単調増加ではないことを意味します：
上の図で示されているように、URL値がW3より小さいすべてのマークが、関連するグラニュールの行をClickHouseエンジンにストリーミングするために選択されています。
これは、上の図のすべてのインデックスマークが上で述べたシナリオ1に該当するものの、直接続くインデックスマークが現在のマークと同じUserID値を持つという除外の前提を満たさないために除外できないからです。
たとえば、インデックスマーク0は、URL値がW3より小さく、直接続くインデックスマークのURL値もW3より小さい場合です。これは除外できません。なぜなら、直接続くインデックスマーク1は現在のマーク0と同じUserID値を持っていないからです。
最終的に、これによりClickHouseはグラニュール0の最大URL値についての仮定を作ることができなくなります。代わりに、グラニュール0がURL値W3を含む行を持つ可能性があると仮定し、マーク0を選択することを余儀なくされます。
マーク1、2、3についても同様のシナリオが当てはまります。
ClickHouseが複合キーの一部である列でフィルタリングされるが、最初のキー列でない場合に使用する一般的な除外検索アルゴリズムは、前のキー列の基数が低いときに最も効果的です。
サンプルデータセットでは、両方のキー列（UserID、URL）は類似の高い基数を持っており、説明したように、URL列の前のキー列の基数が高く（または類似している）場合、一般的な除外検索アルゴリズムはあまり効果的ではありません。
Note about data skipping index
UserIDとURLの基数が同様に高いため、私たちのURLでフィルタリングするクエリは、私たちの複合主キー (UserID、URL)のURL列に対するセカンダリデータスキッピングインデックスを作成してもあまり利益を得ることはできません。
たとえば、これらの2つの文は、私たちのテーブルのURLカラムに対してminmaxデータスキッピングインデックスを作成し、埋め込むものです：
ClickHouseは、私たちのテーブルの最初の4つのグラニュールの各グループに対し、最小および最大URL値を保存する追加のインデックスを作成しました（上記のALTER TABLE文のGRANULARITY 4句に注意）：
最初のインデックスエントリ（上の図の「mark 0」）は、私たちのテーブルの最初の4つのグラニュールに属する行の最小および最大URL値を保存しています。
2番目のインデックスエントリ（「mark 1」）は、私たちのテーブルの次の4つのグラニュールに属する行の最小および最大URL値を保存しています。
（ClickHouseは、インデックスマークに関連付けられたグラニュールのグループを特定するためのデータスキッピングインデックス用の特別なマークファイルも作成しました。）
UserIDとURLの基数が同様に高いため、このセカンダリデータスキッピングインデックスは、私たちのURLでフィルタリングするクエリが実行されるときにグラニュールの除外に役立つことはありません。
クエリが探している特定のURL値（つまり、'http://public_search'）は、非常に高い確率で各グラニュールグループのインデックスによって保存された最小および最大値の間にあるため、ClickHouseはそのグラニュールグループを選択することを余儀なくされます（なぜなら、それらはクエリに一致する行が含まれる可能性があるからです）。
A need to use multiple primary indexes
その結果、特定のURLで行をフィルタリングするサンプルクエリの速度を大幅に向上させたい場合は、そのクエリに最適化された主インデックスを使用する必要があります。
さらに、特定のUserIDで行をフィルタリングするサンプルクエリの良好なパフォーマンスを維持したい場合は、複数の主インデックスを使用する必要があります。
次に、これを達成するための方法を示します。
Options for creating additional primary indexes
特定のUserIDで行をフィルタリングし、特定のURLで行をフィルタリングする両方のサンプルクエリを大幅に高速化したい場合は、次の3つのオプションのいずれかを使用して複数の主インデックスを使用する必要があります：
- 異なる主キーで2番目のテーブルを作成する。
- 既存のテーブルにマテリアライズドビューを作成する。
- 既存のテーブルにプロジェクションを追加する。
この3つのオプションはすべて、サンプルデータを効果的に別のテーブルに複製し、テーブル主インデックスと行のソート順を再整理します。
しかし、3つのオプションは、クエリや挿入ステートメントのルーティングに関して、追加のテーブルがユーザーにどれだけ透過的かにおいて異なります。
異なる主キーで2番目のテーブルを作成する場合、クエリはクエリに最も適したテーブルバージョンに明示的に送信され、テーブルを同期させるためには新しいデータを両方のテーブルに明示的に挿入する必要があります：
マテリアライズドビューの場合、追加のテーブルが暗黙的に作成され、データは両方のテーブル間で自動的に同期されます：
そしてプロジェクションは、暗黙的に作成された（非表示の）追加テーブルをデータ変更と自動的に同期させるだけでなく、ClickHouseがクエリに最も効果的なテーブルバージョンを自動的に選択するため、最も透過的なオプションです：
以下で、複数の主インデックスを作成して使用するためのこの3つのオプションについて、詳細にリアルな例を交えて説明します。
Option 1: Secondary Tables
主キーのキー列の順序を（元のテーブルと比較して）入れ替えた新しい追加テーブルを作成します：
元のテーブルから887万行すべてを追加のテーブルに挿入します：
応答は次のようになります：
そして最後にテーブルを最適化します：
主キー内の列の順序を入れ替えたため、挿入された行は、（元のテーブルと比較して）ディスク上で異なる辞書順で保存され、したがってそのテーブルの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の基数が類似しているとあまり効果的ではないのです。 詳細を開いて具体的に見てみましょう。
今や私たちには二つのテーブルがあります。UserIDsでフィルタリングするクエリの速度を向上させるように最適化されたテーブルと、URLでフィルタリングするクエリの速度を向上させるように最適化されたテーブルです。
Option 2: Materialized Views
既存のテーブルに対してマテリアライズドビューを作成します。
応答は次のようになります：
- 主キーのキー列の順序を（元のテーブルと比較して）入れ替えています。
- マテリアライズドビューは、指定された主キー定義に基づいた暗黙的に作成されたテーブルによってバックアップされます。その行の順序と主インデックスも同様です。
- 暗黙的に作成されたテーブルは、
SHOW TABLESクエリによってリストされ、.innerで始まる名前を持っています。 - マテリアライズドビューのために、最初にバックアップテーブルを明示的に作成し、その後ビューが
TO [db].[table]句を使用してそのテーブルをターゲットにすることも可能です。 POPULATEキーワードを使用して、元のテーブルhits_UserID_URLから暗黙的に作成されたテーブルに887万行すべてを直ちに挿入します。- 元のテーブルhits_UserID_URLに新しい行が挿入されると、それらの行も自動的に暗黙的に作成されたテーブルに挿入されます。
- 実質的に、暗黙的に作成されたテーブルは、明示的に作成したセカンダリテーブルと同じ行の順序と主インデックスを持ちます：
ClickHouseは、暗黙的に作成されたテーブルのカラムデータファイル (.bin)、マークファイル (.mrk2)、および主インデックス (primary.idx)をClickHouseサーバーのデータディレクトリ内の特別なフォルダーに保存しています。
暗黙的に作成されたテーブル（およびその主インデックス）が私たちのURLカラムでフィルタリングする例のクエリの実行を大幅に高速化するために使用できます：
応答は次のとおりです：
実質的に、暗黙的に作成されたテーブル（およびその主インデックス）が私たちの明示的に作成したセカンダリテーブルと同じであるため、このクエリは明示的に作成されたテーブルと同じ効果的な方法で実行されます。
ClickHouseサーバーログファイルの対応するトレースログは、ClickHouseがインデックスマークに対してバイナリ検索を実行していることを確認しています：
Option 3: Projections
既存のテーブルにプロジェクションを作成します：
プロジェクションをマテリアライズします：
- このプロジェクションは、指定された
ORDER BY句に基づいた隠れたテーブルを作成します。 - 隠れたテーブルは
SHOW TABLESクエリによってリストされません。 - 私たちは
MATERIALIZEキーワードを使用して、元のテーブルhits_UserID_URLから隠れたテーブルに887万行すべてを直ちに挿入します。 - 元のテーブルhits_UserID_URLに新しい行が挿入されると、それらの行も自動的に隠れたテーブルに挿入されます。
- クエリは常に（構文的に）元のテーブルhits_UserID_URLをターゲットにしていますが、隠れたテーブルの行の順序と主インデックスがクエリ実行をより効果的にする場合、その隠れたテーブルが代わりに使用されます。
- プロジェクションは、ORDER BYがプロジェクションのORDER BY宣言と一致しても、ORDER BYを使用するクエリをより効率的にするものではありません（https://github.com/ClickHouse/ClickHouse/issues/47333を参照）。
- 実質的に、暗黙的に作成された隠れたテーブルは、明示的に作成したセカンダリテーブルと同じ行の順序と主インデックスを持ちます：
ClickHouseは、隠れたテーブルのカラムデータファイル (.bin)、マークファイル (.mrk2)、および主インデックス (primary.idx)を、元のテーブルのデータファイル、マークファイル、および主インデックスファイルの隣にある特別フォルダーに保存しています：
プロジェクションによって生成された隠れたテーブル（およびその主インデックス）は、私たちのURLカラムでフィルタリングする例のクエリの実行を大幅に迅速化するために（暗黙的に）使用できます。クエリは、プロジェクションのソーステーブルを構文的にターゲットにしています。
応答は次のとおりです：
実質的に、プロジェクションによって生成された隠れたテーブル（およびその主インデックス）は、私たちの明示的に作成したセカンダリテーブルと同じであり、クエリは明示的に作成されたテーブルと同じ効果的な方法で実行されます。
ClickHouseサーバーログファイルの対応するトレースログは、ClickHouseがインデックスマークに対してバイナリ検索を実行していることを確認しています：
概要
私たちの複合主キー (UserID, URL) を持つテーブルの主インデックスは、UserID に基づいてフィルタリングするクエリを高速化するのに非常に役立ちました。しかし、そのインデックスは、URL カラムが複合主キーの一部であるにもかかわらず、URL に基づいてフィルタリングするクエリの高速化にはあまり効果がありません。
逆もまた真です： 私たちの複合主キー (URL, UserID)を持つテーブルの主インデックスは、URL に基づいてフィルタリングするクエリを高速化しましたが、UserID に基づいてフィルタリングするクエリにはあまりサポートを提供しませんでした。
主キーのカラム UserID と URL の高いカーディナリティの類似性のため、2 番目のキー カラムでフィルタリングするクエリは、インデックスに 2 番目のキー カラムがあることからあまり利益を得られないのです。
したがって、主インデックスから 2 番目のキー カラムを削除し（インデックスのメモリ使用量を減らす結果）、代わりに複数の主インデックスを使用することが理にかなっています。
ただし、複合主キーのキー カラム間に大きなカーディナリティの違いがある場合、クエリにとっては有益なため、主キーのカラムをカーディナリティが昇順になるように順序付けることが重要です。
カーディナリティの違いが大きくなるほど、キー内のそれらのカラムの順序が重要になります。次のセクションでそれを示します。
キーカラムを効率的に並べる
複合主キーでは、キー カラムの順序が次の両方に大きく影響します：
- クエリ内のセカンダリーキー カラムのフィルタリングの効率、および
- テーブルのデータファイルの圧縮率。
これを示すために、各行がインターネットの「ユーザー」 (UserID カラム) による URL (URL カラム) へのアクセスがボットトラフィック (IsRobot カラム)としてマークされたかどうかを示す三つのカラムを含む、私たちのウェブトラフィックサンプルデータセットのバージョンを使用します。
通常のウェブ分析クエリを高速化するために使用できるすべての3つの上述のカラムからなる複合主キーを使用して計算します：
- 特定の URL に対するトラフィックのうち、どれだけの割合がボットが占めるか、または
- 特定のユーザーがボットである可能性がどれほど高いか（そのユーザーからのトラフィックの何パーセントがボットトラフィックではないと見なされているか）
私たちはこのクエリを実行して、複合主キーとして使用したい3つのカラムのカーディナリティを計算します（注意：TSVデータをローカルテーブルを作成せずにその場でクエリするために、URL テーブル関数を使用しています）。このクエリを clickhouse client で実行します：
応答は以下の通りです：
カーディナリティ、特に URL と IsRobot カラム間の間に大きな違いがあることが分かります。そして、したがって、複合主キー内のこれらのカラムの順序は、フィルタリングするクエリの効率の向上と、そのテーブルのカラムデータファイルの最適な圧縮率を達成するために重要になります。
これを示すために、ボットトラフィック分析データのために2つのテーブル バージョンを作成します：
(URL, UserID, IsRobot)という複合主キーを持つテーブルhits_URL_UserID_IsRobotでは、カーディナリティに従ってキー カラムを降順に並べます。(IsRobot, UserID, URL)という複合主キーを持つテーブルhits_IsRobot_UserID_URLでは、キー カラムを昇順に並べます。
複合主キー (URL, UserID, IsRobot)を持つテーブル hits_URL_UserID_IsRobotを作成します：
そして 887 万行でそれを埋めます：
これは応答です：
次に、複合主キー (IsRobot, UserID, URL) を持つテーブル hits_IsRobot_UserID_URL を作成します：
前のテーブルに使用したのと同じ 887 万行でそれを埋めます：
応答は以下の通りです：
セカンダリーキー カラムの効率的なフィルタリング
クエリが複合キーの一部であり、最初のキー カラムである少なくとも1つのカラムでフィルタリングしている場合、ClickHouse はキー カラムのインデックスマークに対してバイナリ検索アルゴリズムを実行します。
クエリが複合キーの一部であるカラムで（のみ）フィルタリングしているが、最初のキー カラムではない場合、ClickHouse はキー カラムのインデックスマークに対して一般的な除外探索アルゴリズムを使用しています。
2 番目のケースでは、複合主キー内のキー カラムの順序が一般的な除外探索アルゴリズムの有効性にとって重要です。
これは、カラムの順序が (URL, UserID, IsRobot) でカーディナリティが降順に並べられたテーブルの UserID カラムでフィルタリングするクエリです：
応答は以下の通りです：
これは、カラムの順序が (IsRobot, UserID, URL) でカーディナリティが昇順に並べられたテーブルに対する同じクエリです：
応答は以下の通りです：
カーディナリティが昇順に並べられたテーブルで、クエリの実行が著しく効果的かつ迅速であることが分かります。
その理由は、一般的な除外探索アルゴリズムは、グラニュールが、先行するキー カラムが低いカーディナリティであるセカンダリーキー カラムを介して選択されるときに最も効果的に機能するからです。このガイドの前のセクションで詳細に説明しました。
データファイルの最適な圧縮率
このクエリは、上記で作成した二つのテーブルの UserID カラムの圧縮率を比較します：
応答は以下の通りです：
UserID カラムの圧縮率は、カラムのキー カラムを昇順で並べたテーブルに対して著しく高いことが分かります。
両方のテーブルには正確に同じデータが格納されているにもかかわらず（両方のテーブルに887万行を挿入しました）、複合主キー内のキー カラムの順序が、テーブルのカラムデータファイルの圧縮されたデータが必要とするディスクスペースに重要な影響を与えます：
- 複合主キー
(URL, UserID, IsRobot)を持つテーブルhits_URL_UserID_IsRobotでは、カーディナリティに従ってキー カラムを降順に並べた、そのUserID.binデータファイルは11.24 MiBのディスクスペースを必要とします。 - 複合主キー
(IsRobot, UserID, URL)を持つテーブルhits_IsRobot_UserID_URLでは、カーディナリティに従って低順にキー カラムを並べ、そのUserID.binデータファイルはわずか877.47 KiBのディスクスペースを必要とします。
テーブルのカラムのデータの良い圧縮率は、ディスク上のスペースを節約するだけでなく（特に解析用のクエリは）、データをディスクからメインメモリ（オペレーティングシステムのファイルキャッシュ）に移動するために必要な I/O を減らすので、特にそのカラムからのデータの読み込みが速くなります。
次に、最適な圧縮率を達成するために、テーブルのカラムの圧縮率がカーディナリティに従ってキー カラムを昇順に並べることが有益である理由を示します。
下の図は、キー カラムがカーディナリティに従って昇順に並べられた場合の主キーのオンディスク順を示しています：
私たちはテーブルの行データは主キーのカラムに従ってディスクに保存されていることを説明しました。
上記の図では、テーブルの行（ディスク上のカラム値）は最初にその cl 値で順序付けされ、同じ cl 値を持つ行はその ch 値で順序付けされます。そして、最初のキー カラム cl のカーディナリティが低いため、同じ cl 値を持つ行が存在する可能性が高くなります。そのため、ch 値が（同じ cl 値の行に対して）ローカルに順序付けられている可能性があります。
もし、あるカラムに似たデータが近くに配置されている場合、たとえばソートによって、データはより良く圧縮されます。 一般的に、圧縮アルゴリズムはデータのラン長（見えるデータが多いほど圧縮に有利）と局所性（データが似ているほど圧縮率が良くなる）から恩恵を受けます。
上の図とは対照的に、下の図は、デカーディナリティに従って並べられた主キーのオンディスク順を示しています：
現在、テーブルの行は最初にその ch 値で順序付けされ、同じ ch 値を持つ行はその cl 値で順序付けされます。
しかし、最初のキー カラム ch は高いカーディナリティを持つため、同じ ch 値を持つ行が存在する可能性は低いです。そのため、仮に cl 値が（同じ ch 値を持つ行に対して）ローカルに順序付けされている可能性も低くなります。
したがって、cl 値は無作為に並んでいる可能性が高く、局所性が悪く、圧縮率も悪化します。
概要
クエリにおけるセカンダリーキー カラムの効率的なフィルタリングと、テーブルのカラムデータファイルの圧縮率の両方に対して、主キー内のカラムをカーディナリティに従って昇順に並べることが有益です。
単一行を効率的に特定する
一般的に、ClickHouseにとって最適なユースケースではないものの、 時々、ClickHouseの上に構築されたアプリケーションは、ClickHouse テーブルの単一行を特定する必要があります。
直感的な解決策は、行ごとに一意の値を持つUUID カラムを使用し、そのカラムを主キー カラムとして使用して行を迅速に取得することです。
最も迅速な取得のためには、UUID カラムは最初のキー カラムである必要があります。
私たちは、ClickHouse テーブルの行データは主キー カラムに従ってディスクに保存されるため、非常に高いカーディナリティのカラム（UUID カラムのような）が主キーまたは複合主キーの前に置かれると、他のテーブルカラムの圧縮率にとって不利であることを説明しました。
最も迅速な取得と最適なデータ圧縮の間の妥協は、UUID を最後のキー カラムとして使用する複合主キーを使用し、低（または）カーディナリティのキー カラムがいくつかのテーブルのカラムの良い圧縮率を確保するために使用されます。
具体的な例
具体的な例は、Alexey Milovidov が開発し、ブログで説明したプレーンテキストのペーストサービス https://pastila.nl です。
テキストエリアが変更されるたびに、そのデータは自動的に ClickHouse テーブルの行に保存されます（変更ごとに1行）。
ペーストコンテンツを特定して取得する方法の一つは、そのコンテンツのハッシュをテーブル行のUUIDとして利用することです。
次の図は
- コンテンツが変更されたときの行の挿入順（たとえば、テキストエリアに入力されたキーストロークによる）と
PRIMARY KEY (hash)が使用されるときの挿入された行からのデータのディスク上の順序を示しています：
hash カラムが主キー カラムとして使用されるため、
- 特定の行を非常に迅速に取得できますが、
- テーブルの行（そのカラムデータ）はディスクに (ユニークでランダムな) ハッシュ値昇順で保存されます。したがって、コンテンツカラムの値も無作為に保存され、コンテンツカラムデータファイルの圧縮率が最適ではありません。
コンテンツカラムの圧縮率を大幅に改善しながら、特定の行の迅速な取得を達成するために、pastila.nl では特定の行を識別するために2つのハッシュ（および複合主キー）を使用しています：
- 前述のコンテンツのハッシュ、そのデータに対して異なるもの、
- 小さなデータ変更に対して変化しないローカリティスィンシティブハッシュ（フィンガープリント）。
次の図は
- コンテンツが変更されたときの行の挿入順（たとえば、テキストエリアにタイピングされたキーストロークによる）と
- 複合
PRIMARY KEY (fingerprint, hash)が使用されるときの挿入された行からのデータのディスク上の順序を示しています：
これでディスク上의行はまず fingerprint によって順序付けられ、同じフィンガープリント値を持つ行の場合、その hash 値が最終的な順序を決定します。
データがわずかな変化のみで異なると、同じフィンガープリント値のみが付与され、コンテンツカラム内の似たデータがディスク上で近くに保存されます。これはコンテンツカラムの圧縮率には非常に良い結果をもたらします。一般に、圧縮アルゴリズムはデータの局所性から恩恵を受け（データが似ているほど圧縮率が良い）、最適な圧縮率を実現します。
妥協は、複合 PRIMARY KEY (fingerprint, hash)から派生する主インデックスを最適に利用するために、特定の行を取得するために二つのフィールド (fingerprint と hash) が必要であることです。
