ClickHouseの主キーインデックスの実用的な紹介

はじめに

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

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

このガイドに記載されたすべてのClickHouse SQL文やクエリは、ご自身のマシンで実行することができます。 ClickHouseのインストールおよび始め方の手順については、クイックスタートを参照してください。

注記

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

ClickHouseの二次データスキッピングインデックスについては、チュートリアルをご覧ください。

データセット

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

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

この3つのカラムを使って、以下のような典型的なウェブ分析クエリを提案できます：

• "特定のユーザーの最もクリックされたURLのトップ10は？"
• "特定のURLを最も頻繁にクリックしたユーザーのトップ10は誰か？"
• "特定のURLをユーザーがクリックする最も人気のある時間帯（例：曜日）は？"

テストマシン

このドキュメントに記載されたすべての実行時間の数字は、Apple M1 Proチップと16GBのRAMを搭載したMacBook Pro上でClickHouse 22.2.1をローカルに実行した際のものです。

フルテーブルスキャン

プライマリーキーなしでデータセットに対してクエリが実行される方法を確認するために、次のSQL DDL文を実行して（MergeTreeテーブルエンジンを使用して）テーブルを作成します：

次に、以下のSQL挿入文を使用して、ヒットデータセットのサブセットをテーブルに挿入します。 これには、クリックハウス.comにリモートホストされた完全なデータセットのサブセットを読み込むためにURLテーブル関数が使用されています。

レスポンスは以下の通りです：

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

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

注記

一般的に、データを読み込んだ後にテーブルをすぐに最適化する必要はありませんし、推奨もされません。この例でなぜこれが必要かは後で明らかになります。

最初のウェブ分析クエリを実行します。以下は、UserID 749927693のインターネットユーザーに対して最もクリックされたURLのトップ10を計算するクエリです。

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

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

これを（はるかに）効率よく（ずっと）速くするためには、適切な主キーを持つテーブルを使用する必要があります。これにより、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エンジンファミリーは、大規模なデータボリュームを処理するために設計および最適化されています。これらのテーブルは毎秒百万行の挿入を受け入れ、非常に大きなデータ（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つのマークファイルがあります。
• テーブルには8.87百万行が含まれています。
• すべての行の合計の非圧縮データサイズは733.28MBです。
• すべての行のディスク上での圧縮サイズは206.94MBです。
• テーブルには1083エントリ（「マーク」と呼ばれる）の主インデックスがあり、そのサイズは96.93KBです。
• 合計で、テーブルのデータファイル、マークファイル、および主インデックスファイルは、ディスク上で207.07MBを占めています。

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

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

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

注記

• ソートキーのみを指定している場合、主キーは暗黙のうちにソートキーに等しいと定義されます。

• メモリ効率を考慮して、クエリでフィルタリングされるカラムのみを含む主キーを明示的に指定しました。主キーに基づいている主インデックスは、メインメモリに完全に読み込まれます。

• ガイドの図の一貫性を維持し、圧縮率を最大化するために、すべてのテーブルカラムを含む別のソートキーを定義しました（同じデータが近くに配置されている場合、例えばソートを通じて、そのデータはより良く圧縮されます）。

• 主キーを指定する場合、主キーはソートキーのプレフィックスである必要があります。

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

注記

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

ClickHouseは列指向データベース管理システムです。以下の図示は示しています：

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

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

注記

• 主キーはディスク上の行の辞書式の順序を定義するため、テーブルには1つの主キーしか存在できません。

• 行の番号は0から始めており、ClickHouseの内部的な行番号付けのスキームと整合させています。これは、ログメッセージにも使用されます。

データはグラニュールに整理されており、並列データ処理が可能

データ処理の目的で、テーブルのカラム値は論理的にグラニュールに分割されます。 グラニュールは、データ処理のためにClickHouseにストリームされる最小の不可分なデータセットです。 これは、個々の行を読み込むのではなく、ClickHouseが常に一度に一群（グラニュール）の行を読み込むことを意味します。

注記

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

次の図は、8.87百万行のテーブルの（カラム値が）1083のグラニュールに整理されるさまを示しています。これはテーブルのDDL文に設定されたindex_granularity（デフォルト値8192に設定）を考慮した結果です。

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

注記

• 最後のグラニュール（グラニュール1082）は、8192行未満の「含むから」です。

• このガイドの最初で、私たちは適応インデックスグラニュラリティを無効にしたと述べました（このガイドでの議論を簡略化するため、または図や結果を再現可能にするためにです）。

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

• 適応インデックスグラニュラリティのあるテーブル（インデックスグラニュラリティはデフォルトで適応である場合、いくつかのグラニュールは行データ長に応じて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: 主インデックスファイルを含むパーツパスを取得します。
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を取得します。
Linuxのデフォルトのuser_files_pathは /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<br/>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...'以上であると仮定できます。

  この点については、クエリの実行性能への影響を後で詳しく説明します。

主キーインデックスはグラニュールを選択するために使用されます

これで、主キーインデックスのサポートを受けてクエリを実行できます。

次のクエリは、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エンジンにストリーミングして、クエリに実際に一致する行を見つけます。

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

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

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

上で説明したように、1083のUserIDマークに対する二分検索を介してマーク176が特定されました。したがって、対応するグラニュール176はUserID列の値749,927,693を含む行を持つ可能性があります。

グラニュール選択の詳細

上の図は、マーク176が関連するグラニュール176の最小UserID値が749,927,693未満で、次のマーク（マーク177）のグラニュール177の最小UserID値がこの値よりも大きい最初のインデックスエントリであることを示しています。したがって、マーク176の対応するグラニュール176のみがUserID列の値749,927,693を含む可能性があります。

グラニュール176に行が含まれているかどうかを確認するためには、このグラニュールに属する全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マークファイルを使用します。そのエントリには、各エントリごとに2つの8バイト長のアドレスが含まれており、これらはすべて同じサイズのグラニュールの物理的場所です。

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

• ワイドフォーマットのテーブルで適応インデックス粒度がある場合、ClickHouseは.mrk2マークファイルを使用します。これには、現在のエントリに関連付けられたグラニュールの行数という追加の3番目の値が含まれています。

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

マークファイルの理由

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

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

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

例のクエリでは、ClickHouseは主インデックスを使用して、クエリに一致する行を持つ可能性のある単一のグラニュールを選択しました。その1つのグラニュールのみのために、ClickHouseはそれに関連する行をストリーミングするために物理的な場所を必要とします。

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

例のクエリにおいて、ClickHouseはUserIDデータファイル（UserID.bin）内のグラニュール176の物理的な場所オフセット2つと、URLデータファイル（URL.bin）内のグラニュール176の物理的な場所オフセット2つのみが必要です。

マークファイルによって提供される間接性により、主インデックス内に3列のすべての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を特定（およびすべての値をストリーミング）する必要があります。

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

並行して、ClickHouseはURL.binデータファイルのグラニュール176について同様の操作を行っています。これらの2つのグラニュールは整列され、ClickHouseエンジンにストリーミングされてさらなる処理が行われ、すなわちUserIDが749927693であるすべての行のURL値をグループ別に集計およびカウントした後、最終的にカウントの降順で10の最大URLグループを出力します。

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

セカンダリキー列は（非効率的ではない）ことができます

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

しかし、クエリが複合キーの一部である列でフィルタリングされていますが、それが最初のキー列でない場合はどうなりますか？

注記

クエリが最初のキー列でフィルタリングしていないが、セカンダリキー列でフィルタリングしているシナリオを説明します。

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

URL「http://public_search」を頻繁にクリックした上位10のユーザーを計算するクエリを使用します。

応答は以下のとおりです：

クライアントの出力は、ClickHouseが主キー列URLの一部としているにも関わらず、ほぼ完全なテーブルスキャンを実行したことを示しています！ClickHouseは、8.87百万行のテーブルから8.81百万行を読み込みました。

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

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

この結果、ClickHouseエンジンに8.81百万行がストリーミングされます（10のストリームを使用して並行して）。これは、実際に「http://public_search」の値を持つ行を特定するためです。

しかし、後で説明するように、選択された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. インデックスマーク0は、URL値がW3より小さく、直接後のインデックスマークのURL値もW3より小さい場合は排除されます。なぜなら、マーク0と１は同じUserID値を持つためです。この排除前提条件により、グラニュール0はすべてU1のUserID値で構成されていると仮定できるので、ClickHouseはグラニュール0内の最大URL値がW3よりも小さいとも断定し、グラニュールを排除できます。

2. インデックスマーク1は、URL値がW3より小さい（または等しい）場合、および直接後のインデックスマークのURL値がW3以上の場合は選択されます。これにより、グラニュール1がURL W3を含む可能性があることを示します。

3. インデックスマーク2および3は、URL値がW3より大きい場合に排除されます。主インデックスのインデックスマークが各グラニュールの最初のテーブル行のキー列の値を格納しているため、テーブル行はディスク上でキー列の値で順序付けられています。したがって、グラニュール2と3はURL値W3を含むことはできません。

前のキー列が高いカーディナリティの場合

UserIDが高いカーディナリティである場合は、同じUserID値が複数のテーブル行やグラニュールに広がる可能性が低くなります。これにより、インデックスマークのURL値は単調に増加することはありません：

上の図からわかるように、W3より小さいすべてのマークはその関連するグラニュールの行をClickHouseエンジンにストリーミングするために選択されます。

これは、図内のすべてのインデックスマークが上記のシナリオ1に該当しますが、インデックスマーク0に対しては、直接後のインデックスマークが現在のマークとは同じUserID値を持っているという排除前提条件が満たされないため、排除できません。

例えば、インデックスマーク0はURL値がW3より小さく、直接後のインデックスマークがW3よりも小さい場合であっても、直接後のインデックスマーク1が現在のマーク0と同じUserID値を持たないため、排除できません。

これが最終的にClickHouseがグラニュール0の最大URL値に関する仮定を行うことを妨げます。代わりに、ClickHouseは、グラニュール0がURL値W3を含む可能性があると仮定せざるを得ず、マーク0を選択せざるを得ません。

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

結論

ClickHouseが複合キーの一部である列のフィルタリングに対して一般的な排除検索アルゴリズムを用いている場合、前のキー列が低いカーディナリティがある場合に最も効果的です。

サンプルデータセットでは、両方のキー列（UserID、URL）は高いカーディナリティを持ち、上記の通り、URL列の前のキー列のカーディナリティが高いか似ている場合、一般的な排除検索アルゴリズムはあまり効果的ではありません。

データスキッピングインデックスに関する注意

UserIDとURLの似たような高いカーディナリティのため、私たちのURLに基づくクエリフィルタリングも、私たちの複合主キー（UserID、URL）を持つテーブルのURLカラムに対してセカンダリデータスキッピングインデックスを作成することからあまり恩恵を受けませんでした。

例えば、次の2つの文は、私たちのテーブルのURLカラムに対してminmaxデータスキッピングインデックスを作成し、人口を与えます：

ClickHouseは現在、4つの連続したグラニュールのグループごと（上記のALTER TABLE文のGRANULARITY 4句に注意）に、最小および最大のURL値を格納する追加のインデックスを作成しました：

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

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

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

UserIDとURLの似たような高いカーディナリティのため、このセカンダリデータスキッピングインデックスは、私たちのURLに基づくクエリフィルタリングが実行される際に、グラニュールを除外するのに役立ちません。

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

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

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

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

次に、それを達成する方法を示します。

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

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

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

3つのオプションはすべて、サンプルデータを追加テーブルに効果的に重複させ、テーブルの主インデックスと行のソート順序を再編成することになります。

ただし、3つのオプションは、クエリや挿入文のルーティングに対する追加テーブルの透明性の点で異なります。

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

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

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

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

オプション 1: セカンダリテーブル

私たちは、主キーにおけるカラムの順序を（元のテーブルと比較して）変更する新しい追加テーブルを作成しています：

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

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

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

主キーのカラムの順序を変更したため、挿入された行は現在、元のテーブルと比較して異なる辞書順でディスクに保存され、そのテーブルの1083のグラニュールには以前とは異なる値が含まれています：

これが生成された主キーです：

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

レスポンスは以下の通りです：

今、ほぼフルテーブルスキャンを行うことなく、ClickHouseはそのクエリをより効率的に実行しました。

元のテーブルの主インデックスではUserIDが最初のカラムで、URLが2番目のカラムでしたが、ClickHouseはそのクエリを実行するためにインデックスマークに対して一般的な除外検索を使用していましたが、UserIDとURLの似たような高いカーディナリティのため、それはあまり効果的ではありませんでした。

URLが主インデックスの最初のカラムとして配置されたことで、ClickHouseはバイナリサーチをインデックスマークの上で実行しています。 ClickHouseサーバーログファイルの対応するトレースログは次のように確認します：

ClickHouseは1076のインデックスマークではなく、39のインデックスマークのみを選択しました。

追加テーブルが、URLでフィルタリングされたサンプルクエリの実行を高速化するように最適化されていることに注意してください。

元のテーブルでのそのクエリの悪いパフォーマンスとは異なり、UserIDsに対する私たちの例のクエリのフィルタリングは、新しい追加テーブルでは非常に効果的には実行されません。なぜなら、UserIDがそのテーブルの主インデックスの2番目のキー列であり、したがってClickHouseはグラニュール選択のために一般的な除外検索を使用することになるからです。これは同様に高いカーディナリティにはあまり効果的ではありません。

詳細ボックスを開いて具体例を確認してください。

UserIDsに基づくクエリフィルタリングのパフォーマンスが悪くなった

レスポンスは次の通りです：

サーバーログ：

私たちは現在、2つのテーブルを持っています。UserIDsでフィルタリングされたクエリの実行を高速化するように最適化され、URLでフィルタリングされたクエリの実行を高速化するように最適化されています：

オプション 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がインデックスマークに対してバイナリサーチを実行していることを確認します：

オプション 3: プロジェクション

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

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

注記

• プロジェクションは、指定されたORDER BY句に基づいて行順序と主インデックスを持つ隠されたテーブルを作成します。
• 隠されたテーブルはSHOW TABLESクエリでリストされません。
• MATERIALIZEキーワードを使用して、ソーステーブルhits_UserID_URLのすべての8.87百万行で隠されたテーブルを即座に人口します。
• ソーステーブルhits_UserID_URLに新しい行が挿入されると、これらの行も自動的に隠されたテーブルに挿入されます。
• クエリは常に（構文的に）ソーステーブルhits_UserID_URLをターゲットにしますが、隠されたテーブルの行順序と主インデックスがクエリの実行をより効果的にする場合、その隠されたテーブルが代わりに使用されます。
• プロジェクションは、プロジェクションの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つのカラムを含む、私たちのウェブトラフィックサンプルデータセットのバージョンを使用します。

その3つのカラムを含む複合主キーを使って、特定のURLへのトラフィックのパーセンテージがボットからのものであるか、特定のユーザーが（ボットではない）ボットであるかどうかの自信がどの程度あるか（そのユーザーからのトラフィックの何パーセントがボットトラフィックであると見なされているか）を計算するという典型的なウェブ分析のクエリを高速化するためのキーとして使います。

これらのキー列として使いたい3つのカラムのカーディナリティを計算するために、このクエリを使用します（注意：ローカルテーブルを作成せずにTSVデータをアドホックにクエリするためにURLテーブル関数を使用しています）。クリックハウスクライアントでこのクエリを実行してください：

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

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

そのことを証明するために、ボットトラフィック分析データのために2つのテーブルバージョンを作成します。

• キー列のカーディナリティを降順に並べる複合主キー(URL, UserID, IsRobot)を持つテーブルhits_URL_UserID_IsRobot
• キー列のカーディナリティを昇順に並べる複合主キー(IsRobot, UserID, URL)を持つテーブルhits_IsRobot_UserID_URL

複合主キー(URL, UserID, IsRobot)を持つテーブルhits_URL_UserID_IsRobotを作成します：

そして8.87百万行でそれを満たします：

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

次に、複合主キー(IsRobot, UserID, URL)を持つテーブルhits_IsRobot_UserID_URLを作成します：

そして、先程のテーブルで使用したのと同じ8.87百万行を満たします：

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

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

クエリが複合キーの一部である列でフィルタリングしている場合、その列が最初のキー列であれば、ClickHouseはそのカラムのインデックスマークに整列検索を実行しています。

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

このクエリは、キー列を(URL, UserID, IsRobot)のカラム順序で持つテーブルのUserID列でフィルタリングしています：

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

次は、カラムを(IsRobot, UserID, URL)のカラム順序で持つテーブルでの同じクエリです：

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

キー列の順序をカーディナリティに応じて並べたテーブルでのクエリ実行が、はるかに効果的で速いことが確認できます。

その理由は、一般的な除外検索アルゴリズムは、前のキー列のカーディナリティが低い場合にセカンダリーキー列で選択されたグラニュールを最も効果的に処理するからです。これはこのガイドの以前のセクションで詳しく説明しました。

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

このクエリは、上で作成した二つのテーブル間でのUserIDカラムの圧縮率を比較します：

これがレスポンスです：

UserIDカラムの圧縮率は、キーのカラムを高次元の順に整列させたテーブルで著しく高いことがわかります。

両方のテーブルには同じデータが保存されています（双方のテーブルに同じ8.87百万行を挿入しました）が、複合主キーでのキーのカラムの順序は、テーブルのカラムデータファイルにある圧縮されたデータが必要とするディスクスペースに大きな影響を与えます：

• 複合主キー(URL, UserID, IsRobot)のテーブルhits_URL_UserID_IsRobotでは、キーのカラムを高次元に降順で整列させたため、UserID.binデータファイルは11.24 MiBのディスクスペースを必要とします
• 複合主キー(IsRobot, UserID, URL)のテーブルhits_IsRobot_UserID_URLでは、キーのカラムを高次元に昇順で整列させたため、UserID.binデータファイルはわずかに877.47 KiBのディスクスペースを必要とします

テーブルのカラムデータのディスク上で良好な圧縮率を持つことは、ディスクのスペースを節約するだけでなく、そのカラムからデータを読み取る必要があるクエリ（特に分析を必要とするもの）を速くします。なぜなら、カラムのデータをディスクから主記憶（オペレーティングシステムのファイルキャッシュ）に移動する際に必要な入出力が少なくて済むからです。

以下に、テーブルのカラムの圧縮率を高めるために、主キーのカラムを高次元に昇順に整列させることが有益である理由を示します。

下の図は、主キーが高次元に昇順で整列されている場合のディスク上の行の順序を示しています：

私たちは、テーブルの行データが主キーのカラムで整列されて保存されていることを議論しました。

上の図では、テーブルの行（ディスク上のカラム値）が最初にその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テーブルの行に保存されます（変更ごとに一行）。

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

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

hashカラムがプライマリキーとして使用されているため、

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

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

• 前述のように、異なるデータに対して一意であるコンテンツのハッシュ、及び
• 小さなデータの変更の際には変化しない局所感応ハッシュ（フィンガープリント）。

以下の図は、

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

現在、ディスク上の行はまずfingerprintによって整列され、同じフィンガープリント値を持つ行に対してはそのhash値が最終的な順序を決定します。

わずかに変更されたデータが同じフィンガープリント値を受け取るため、類似データがディスク上で互いに近くに保存され、これはコンテンツカラムの圧縮率にとって非常に良いことです。一般的に圧縮アルゴリズムはデータのローカリティから恩恵を受けます（データが類似しているほど圧縮率が良くなります）。

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