メインコンテンツへスキップ

​
はじめに

このガイドでは、ClickHouse の索引を詳しく掘り下げます。具体的には、次の内容を詳しく説明します。 必要に応じて、このガイドに記載されているすべての ClickHouse SQL ステートメントとクエリを、手元のマシンで実行できます。 ClickHouse のインストール方法と使い始めるための手順については、クイックスタートを参照してください。
このガイドでは、ClickHouse のスパースプライマリインデックスに焦点を当てます。ClickHouse のセカンダリデータスキッピングインデックスについては、チュートリアルを参照してください。

​
データセット

このガイドでは、全体を通して匿名化されたサンプルのウェブトラフィックデータセットを使用します。
  • サンプルデータセットから、887万行 (イベント) のサブセットを使用します。
  • 非圧縮データサイズは 887万イベント、約 700 MB です。これを ClickHouse に保存すると、200 MB に圧縮されます。
  • このサブセットでは、各行に 3 つのカラムがあり、あるインターネットユーザー (UserID カラム) が特定の時刻 (EventTime カラム) に URL (URL カラム) をクリックしたことを示します。
この 3 つのカラムだけでも、次のような典型的なウェブアナリティクスのクエリを作成できます。
  • 「特定のユーザーが最も多くクリックした URL の上位 10 件は？」
  • 「特定の URL を最も頻繁にクリックしたユーザーの上位 10 人は？」
  • 「ユーザーが特定の URL をクリックする時刻として最も多いのはいつか (例: 曜日) ？」

​
テストマシン

このドキュメントに記載する実行時間の数値はすべて、Apple M1 Proチップと16GBのRAMを搭載したMacBook Proで、ClickHouse 22.2.1をローカル実行した際の結果に基づいています。

​
フルテーブルスキャン

主キーのないデータセットに対してクエリがどのように実行されるかを確認するため、次の SQL DDL ステートメントを実行して、 (MergeTree テーブルエンジンを使用する) テーブルを作成します。
次に、以下の SQL insert ステートメントを使用して、hits データセットの一部をテーブルに挿入します。 ここでは、clickhouse.com でリモート公開されている完全なデータセットの一部を読み込むために、URL table function を使用します。
応答は次のとおりです。
ClickHouse client の結果出力から、上記のステートメントによって 887 万行がテーブルに挿入されたことがわかります。 最後に、このガイドの以降の説明を簡潔にし、図と結果を再現できるようにするため、FINAL キーワードを使用してテーブルを最適化します。
一般に、テーブルにデータを読み込んだ直後に最適化を実行する必要はなく、推奨もされません。なぜこの例ではそれが必要なのかは、すぐにわかります。
ここで、最初のウェブアナリティクスのクエリを実行します。以下では、UserID 749927693 のインターネットユーザーについて、最も多くクリックされた URL の上位 10 件を算出します。
レスポンスは次のとおりです。
ClickHouse client の出力結果から、ClickHouse がテーブル全体をスキャンしたことがわかります。テーブルの 887 万行すべてが 1 行ずつ ClickHouse にストリーミングされました。これではスケールしません。 これを (はるかに) 効率的かつ (大幅に) 高速にするには、適切な主キーを持つテーブルを使う必要があります。そうすることで、ClickHouse は (主キーのカラムに基づいて) スパースプライマリインデックスを自動的に作成できるようになり、それを使ってこの例のクエリの実行を大幅に高速化できます。

​
ClickHouse の索引設計

​
大規模データ向けの索引設計

従来のリレーショナルデータベース管理システムでは、プライマリインデックスにはテーブルの各行ごとに 1 つのエントリが含まれます。この場合、このデータセットではプライマリインデックスに 887 万件のエントリが含まれることになります。このような索引により特定の行を高速に見つけられるため、ルックアップクエリやポイント更新を効率よく実行できます。B(+)-Tree データ構造でエントリを検索する平均時間計算量は O(log n) です。より正確には、b を B(+)-Tree の分岐係数、n を索引対象の行数とすると、log_b n = log_2 n / log_2 b です。b は通常、数百から数千の範囲にあるため、B(+)-Trees は非常に浅い構造で、レコードの特定に必要なディスク seek もごく少なくて済みます。887 万行で分岐係数が 1000 の場合、必要なディスク seek は平均 2.3 回です。ただし、この性能には代償があります。追加のディスクおよびメモリのオーバーヘッド、テーブルへの新規行追加や索引へのエントリ追加に伴う挿入コストの増加、さらに場合によっては B-Tree の再平衡化も必要になります。 B-Tree 索引に伴うこうした課題を踏まえ、ClickHouse のテーブルエンジンでは別のアプローチを採用しています。ClickHouse の MergeTree Engine Family は、膨大なデータ量を扱えるように設計・最適化されています。これらのテーブルは、毎秒数百万行の挿入を受け付け、非常に大規模な (数百 PB 規模の) データを保存できるよう設計されています。データは パーツごとに テーブルへすばやく書き込まれ、バックグラウンドでそれらのパーツをマージするルールが適用されます。ClickHouse では各パーツがそれぞれ独自のプライマリインデックスを持ちます。パーツがマージされると、マージ後のパーツのプライマリインデックスもマージされます。ClickHouse が対象とするこのような大規模環境では、ディスクとメモリの効率が極めて重要です。そのため、各行に索引を付けるのではなく、パーツのプライマリインデックスは行のグループ (「granule」と呼ばれます) ごとに 1 つの索引エントリ (「mark」と呼ばれます) を持ちます。この手法は スパースインデックス と呼ばれます。 このようなスパースインデックスが可能なのは、ClickHouse がパーツ内の行を主キーカラム順に並べてディスクに保存しているためです。スパースプライマリインデックスは、単一の行を直接特定するのではなく (B-Tree ベースの索引のように) 、索引エントリに対する二分探索によって、クエリに一致する可能性のある行グループをすばやく絞り込めます。こうして特定された一致候補の行グループ (granules) は、その後 ClickHouse engine に並列にストリーミングされ、一致する行が見つけられます。この索引設計により、プライマリインデックスを小さく保つことができ (完全に main memory に収まる必要があり、実際に収まります) 、それでもクエリ実行時間を大幅に短縮できます。特に、データ分析のユースケースで一般的な範囲クエリで効果を発揮します。 以下では、ClickHouse がどのようにスパースプライマリインデックスを構築し、利用しているかを詳しく説明します。記事の後半では、索引の構築に使うテーブルカラム (主キーカラム) の選び方、削除方法、並べ方について、いくつかのベストプラクティスを紹介します。

​
主キーを持つテーブル

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

このガイドの後半での説明をわかりやすくし、図や結果を再現できるようにするため、次のDDLステートメントでは以下を指定しています。

  • ORDER BY 句で、テーブルの複合ソートキーを指定します。
  • 次の設定により、プライマリインデックスの索引エントリ数を明示的に制御します。
    • index_granularity: デフォルト値の 8192 に明示的に設定します。つまり、8192 行ごとの各グループに対して、プライマリインデックスには 1 つの索引エントリが作成されます。たとえば、テーブルに 16384 行が含まれている場合、索引エントリは 2 つになります。
    • index_granularity_bytes: アダプティブインデックス粒度を無効にするため、0 に設定します。アダプティブインデックス粒度とは、次のいずれかの条件を満たす場合に、ClickHouse が n 行のグループに対して 1 つの索引エントリを自動的に作成する仕組みです。
      • n が 8192 未満で、かつその n 行を合わせたデータサイズが 10 MB 以上である場合 (index_granularity_bytes のデフォルト値) 。
      • n 行を合わせたデータサイズが 10 MB 未満で、n が 8192 である場合。
    • compress_primary_key: プライマリインデックスの圧縮を無効にするため、0 に設定します。これにより、必要に応じて後でその内容を確認できます。

上記のDDLステートメントでは、主キーによって、指定した 2 つのキーカラムに基づくプライマリインデックスが作成されます。
次に、データを挿入します。
レスポンスは次のようになります。

次に、テーブルを最適化します。

次のクエリを使用して、テーブルのメタデータを取得できます。
レスポンスは次のとおりです。
ClickHouse client の出力から、次のことがわかります。
  • テーブルのデータは、ディスク上の特定のディレクトリに ワイド形式 で保存されます。つまり、そのディレクトリ内にはテーブルの各カラムごとに 1 つのデータファイル (および 1 つのマークファイル) が存在します。
  • このテーブルには 887 万行があります。
  • すべての行を合わせた非圧縮データサイズは 733.28 MB です。
  • すべての行を合わせたディスク上の圧縮サイズは 206.94 MB です。
  • このテーブルには 1083 個のエントリ (‘marks’ と呼ばれます) を持つプライマリインデックスがあり、そのサイズは 96.93 KB です。
  • 合計すると、テーブルのデータファイル、マークファイル、プライマリインデックスファイルを合わせて、ディスク上で 207.07 MB を使用します。

​
データは主キーカラム順にディスク上へ保存されます

上で作成したテーブルには、次のものがあります。
  • ソートキーだけを指定した場合、主キーは暗黙的にソートキーと同一のものとして定義されます。
  • メモリ効率を高めるため、クエリのフィルタ条件で使用するカラムだけを含む主キーを明示的に指定しています。主キーに基づくプライマリインデックスは、すべてメインメモリに読み込まれます。
  • ガイド内の図との整合性を保ち、圧縮率を最大化するために、テーブルのすべてのカラムを含む別個のソートキーを定義しました (たとえばソートによって、類似したデータがカラム内で近くに配置されると、より高く圧縮できます) 。
  • 両方を指定する場合、主キーはソートキーのプレフィックスである必要があります。
挿入された行は、主キーカラム (およびソートキーに含まれる追加の EventTime カラム) に従って、辞書式順序の昇順でディスク上に保存されます。
ClickHouse では、主キーカラムの値が同一の複数の行を挿入できます。この場合 (下図の行 1 と行 2 を参照) 、最終的な順序は指定されたソートキー、つまり EventTime カラムの値によって決まります。
ClickHouse はカラム指向データベース管理システムです。以下の図に示すとおり、
  • ディスク上の表現では、テーブルの各カラムごとに 1 つのデータファイル (*.bin) があり、そのカラムのすべての値が圧縮フォーマットで保存されます。
  • 887 万行は、主キーカラム (および追加のソートキーカラム) に従って、辞書式順序の昇順でディスク上に保存されます。つまり、この場合は
    • まず UserID、
    • 次に URL、
    • 最後に EventTime の順です。
UserID.bin、URL.bin、EventTime.bin は、UserID、URL、EventTime カラムの値が保存されているディスク上のデータファイルです。
  • 主キーはディスク上の行の辞書式順序を定義するため、テーブルに設定できる主キーは 1 つだけです。
  • ClickHouse の内部的な行番号付け方式 (ログメッセージでも使われます) に合わせるため、行番号は 0 から始めています。

​
データは並列処理のためにグラニュールに編成されます

データ処理の観点では、テーブルのカラム値は論理的にグラニュールへ分割されます。 グラニュールは、データ処理のために ClickHouse にストリーミングされる、これ以上分割できない最小のデータセットです。 つまり、ClickHouse は個々の行を読むのではなく、常に行のまとまり全体 (グラニュール) をストリーミング方式で、かつ並列に読み取ります。
カラム値がグラニュール内に物理的に格納されているわけではありません。グラニュールは、クエリ処理のためにカラム値を論理的にまとめたものにすぎません。
次の図は、テーブルの DDL ステートメントに設定 index_granularity が含まれていることで、 このテーブルの 887 万行の (カラム値) が 1083 個のグラニュールにどのように編成されるかを示しています (この設定にはデフォルト値の 8192 が設定されています) 。 最初の 8192 行 (そのカラム値) は、ディスク上の物理的な順序に基づいて論理的にグラニュール 0 に属し、次の 8192 行 (そのカラム値) はグラニュール 1 に属します。以降も同様です。
  • 最後のグラニュール (グラニュール 1082) に「含まれる」行数は、8192 行未満です。
  • このガイドの冒頭の「DDL Statement Details」で、アダプティブインデックス粒度 を無効にしたことに触れました (このガイドでの説明を簡潔にし、図と結果を再現可能にするためです) 。 そのため、サンプルテーブルのすべてのグラニュール (最後のものを除く) は同じサイズになっています。
  • アダプティブインデックス粒度を持つテーブルでは、index granularity はデフォルトでアダプティブであるため、行データのサイズに応じて、一部のグラニュールは 8192 行未満になることがあります。
  • 主キーのキーカラム (UserID、URL) の一部のカラム値をオレンジ色で示しています。 これらのオレンジ色で示したカラム値は、各グラニュールの先頭行にある主キーのキーカラム値です。 後で説明するように、これらのオレンジ色で示したカラム値が、テーブルのプライマリインデックスのエントリになります。
  • ClickHouse の内部的な番号付け方式 (ログメッセージでも使われています) に合わせるため、グラニュールの番号は 0 から付けています。

​
プライマリインデックスはグラニュールごとに1つのエントリを持ちます

プライマリインデックスは、上の図に示したグラニュールに基づいて作成されます。この索引は非圧縮のフラットな配列ファイル (primary.idx) で、0 から始まる、いわゆる数値のインデックスマークが格納されています。 下の図が示すように、この索引には各グラニュールの先頭行の主キーカラムの値 (上の図でオレンジ色で示した値) が格納されます。 言い換えると、プライマリインデックスには、テーブルの8192行ごとの主キーカラムの値が格納されます (主キーカラムで定義される物理的な行順序に基づきます) 。 例えば、
  • 最初の索引エントリ (下の図の「mark 0」) には、上の図のグラニュール 0 の先頭行のキーカラムの値が格納されています。
  • 2番目の索引エントリ (下の図の「mark 1」) には、上の図のグラニュール 1 の先頭行のキーカラムの値が格納されており、以降も同様です。
合計すると、887万行、1083グラニュールから成るこのテーブルの索引には、1083個のエントリがあります。
  • アダプティブインデックス粒度 を使用するテーブルでは、最後のテーブル行の主キーカラムの値を記録する追加の「final」マークも 1 つプライマリインデックスに格納されます。ただし、このガイドでの説明を簡潔にし、図や結果を再現できるようにするため、ここではアダプティブインデックス粒度を無効にしているため、この例のテーブルの索引にはこの final マークは含まれていません。
  • プライマリインデックスファイルは全体が主記憶に読み込まれます。ファイルサイズが利用可能な空きメモリ容量を超えると、ClickHouse はエラーを返します。

セルフマネージドのClickHouseクラスターでは、サンプルテーブルのプライマリインデックスの内容を調べるために、file table functionを使用できます。そのためには、まず実行中のクラスター内のノードの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 FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 1082, 1; 結果は次のとおりです。 4292714039 │ http://sosyal-mansetleri...

これは、サンプルテーブルのプライマリインデックスの内容を示した図と完全に一致しています:

主キーのエントリは インデックスマーク と呼ばれます。各 index entry は特定のデータ範囲の開始位置を示すためです。サンプルテーブルでは、具体的には次のようになります:
  • UserID インデックスマーク: プライマリインデックスに格納されているUserIDの値は昇順に並んでいます。
    したがって、上の図の「mark 1」は、グラニュール 1 のすべてのテーブル行と、それ以降のすべての グラニュール に含まれるUserIDの値が、4.073.710 以上であることを示しています。
後で説明するように、この全体的な順序により、主キーの先頭カラムでクエリを絞り込む場合、ClickHouse は先頭キーカラムの インデックスマーク に対して二分探索アルゴリズムを使用できます。
  • URL インデックスマーク: 主キーカラム UserID と URL のカーディナリティがかなり近いため、一般に、2 番目以降のすべてのキーカラムのインデックスマークが示せるデータ範囲は、少なくとも現在の グラニュール 内のすべてのテーブル行で直前のキーカラムの値が同じである場合に限られます。
    たとえば、上の図では mark 0 と mark 1 の UserID の値が異なるため、ClickHouse は グラニュール 0 内のすべてのテーブル行の URL の値が 'http://showtopics.html%3...' 以上であるとは判断できません。ですが、上の図で mark 0 と mark 1 の UserID の値が同じであれば (つまり、グラニュール 0 内のすべてのテーブル行で UserID の値が変わらないことを意味します) 、ClickHouse は グラニュール 0 内のすべてのテーブル行の URL の値が 'http://showtopics.html%3...' 以上であると判断できます。     これがクエリ実行性能にどのような影響を与えるかについては、後ほどさらに詳しく説明します。

​
プライマリインデックスはグラニュールの選択に使われます

これで、プライマリインデックスを利用してクエリを実行できるようになりました。 以下では、UserID 749927693 に対して最も多くクリックされた URL の上位 10 件を求めます。
応答は次のとおりです。
ClickHouse client の出力では、フルテーブルスキャンを実行する代わりに、わずか 8,190 行だけが ClickHouse にストリーミングされたことが示されるようになりました。 トレースログが有効になっている場合、ClickHouse のサーバーログファイルには、749927693 の UserID カラム値を持つ行を含む可能性があるグラニュールを特定するために、1083 個の UserID インデックスマーク に対して ClickHouse が二分探索を実行していたことが表示されます。これには、平均時間計算量 O(log2 n) で 19 ステップが必要です:
上のトレースログを見ると、既存の1083個のマークのうち、クエリ条件を満たしたのは1つだけであることがわかります。

マーク176が特定され (‘found left boundary mark’ は境界を含み、‘found right boundary mark’ は境界を含みません) 、その結果、グラニュール176の8192行すべてが (これは行1.441.792から始まります。これについてはこのガイドの後半で説明します) 、UserIDカラムの値が 749927693 である実際の行を見つけるために ClickHouse に読み込まれます。

また、サンプルクエリで EXPLAIN句 を使うことで、これを再現することもできます。
レスポンスは次のようになります。
クライアントの出力は、1083 個のグラニュールのうち 1 個が、UserID カラムの値 749927693 を持つ行を含む可能性があるものとして選択されたことを示しています。
結論クエリが複合キーを構成するカラムのうち先頭のキーカラムで絞り込みを行う場合、ClickHouse はそのキーカラムのインデックスマークに対して二分探索アルゴリズムを実行します。

前述のとおり、ClickHouse は、クエリに一致する行を含む可能性があるグラニュールをすばやく (二分探索によって) 選択するために、スパースプライマリインデックスを使用します。 これは、ClickHouse のクエリ実行における 第 1 段階 (グラニュールの選択) です。 第 2 段階 (データの読み取り) では、ClickHouse は選択されたグラニュールの位置を特定し、クエリに実際に一致する行を見つけるために、それらに含まれるすべての行を ClickHouse エンジンにストリーミングします。 次のセクションでは、この第 2 段階についてさらに詳しく説明します。

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

次の図は、このテーブルのプライマリインデックスファイルの一部を示しています。 前述のとおり、索引内の 1083 個の UserID mark に対して binary search を行った結果、mark 176 が特定されました。したがって、対応する グラニュール 176 には、UserID カラムの値が 749.927.693 の行が含まれている可能性があります。

上の図が示すように、mark 176 は、対応する グラニュール 176 の最小 UserID 値が 749.927.693 より小さく、かつ次の mark (mark 177) に対応する グラニュール 177 の最小 UserID 値がこの値より大きい、最初の index entry です。したがって、UserID カラムの値が 749.927.693 の行を含む可能性があるのは、mark 176 に対応する グラニュール 176 だけです。

グラニュール 176 に UserID カラム値 749.927.693 を持つ行が含まれているかどうかを確認するには、この グラニュール に属する 8192 行すべてを ClickHouse にストリーミングする必要があります。 そのためには、ClickHouse が グラニュール 176 の物理的位置を把握している必要があります。 ClickHouse では、このテーブルのすべての グラニュール の物理的位置は マークファイル に格納されます。data file と同様に、テーブルの各カラムに対して 1 つの マークファイル があります。 次の図は、テーブルの UserID、URL、EventTime カラムに対応する グラニュール の物理的位置を格納する 3 つの マークファイル、UserID.mrk、URL.mrk、EventTime.mrk を示しています。 ここまで説明してきたように、プライマリインデックスはフラットな非圧縮の配列ファイル (primary.idx) で、0 から始まる番号が付いた index mark を含んでいます。 同様に、マークファイル もフラットな非圧縮の配列ファイル (*.mrk) で、0 から始まる番号が付いた mark を含んでいます。 ClickHouse が、クエリに一致する行を含む可能性がある グラニュール の index mark を特定して選択すると、マークファイル に対して位置ベースの配列ルックアップを行うことで、その グラニュール の物理的位置を取得できます。 特定のカラムに対応する各 マークファイル エントリには、offset の形で 2 つの位置が格納されています。
  • 1 つ目の offset (上図の ‘block_offset’) は、選択された グラニュール の圧縮版を含む、圧縮されたカラムデータファイル内の block の位置を示します。この圧縮 block には、複数の圧縮された グラニュール が含まれている可能性があります。特定された圧縮ファイル block は、読み取り時にメインメモリ上で展開されます。
  • 2 つ目の offset (上図の ‘granule_offset’) は マークファイル から取得され、非圧縮の block data 内における グラニュール の位置を示します。
その後、特定された非圧縮 グラニュール に属する 8192 行すべてが、後続の処理のために ClickHouse にストリーミングされます。
  • ワイド形式 で、かつ アダプティブインデックス粒度 を使用しないテーブルでは、ClickHouse は上図のような .mrk マークファイル を使用します。これには、各エントリに 8 バイト長のアドレスが 2 つ含まれています。これらのエントリは、いずれも同じサイズを持つ グラニュール の物理的位置を表します。
Index granularity は default で adaptive ですが、この例のテーブルでは アダプティブインデックス粒度 を無効にしています (このガイドでの説明を簡単にし、図と結果を再現可能にするためです) 。このテーブルが ワイド形式 を使用しているのは、データサイズが min_bytes_for_wide_part より大きいためです (セルフマネージドクラスターではデフォルトで 10 MB) 。
  • ワイド形式でアダプティブインデックス粒度を使用するテーブルでは、ClickHouse は .mrk2 マークファイル を使用します。これには .mrk マークファイル と同様のエントリが含まれますが、各エントリには追加の 3 つ目の値として、そのエントリに対応する グラニュール の行数が格納されます。
  • compact パーツ のテーブルでは、ClickHouse は .mrk3 マークファイル を使用します。
なぜマークファイルなのかプライマリインデックスに、インデックスマークに対応するグラニュールの物理的位置が直接含まれていないのはなぜでしょうか。それは、ClickHouse が想定する非常に大規模なスケールでは、ディスクとメモリを極めて効率的に使うことが重要だからです。プライマリインデックスファイルはメインメモリに収まる必要があります。このサンプルクエリでは、ClickHouse はプライマリインデックスを使って、クエリに一致する行を含む可能性がある 1 つのグラニュールを選択しました。ClickHouse が対応する行を後続処理のためにストリーミングする際に物理的位置を必要とするのは、その 1 つのグラニュールについてだけです。さらに、このオフセット情報が必要なのは UserID カラムと URL カラムだけです。クエリで使われないカラム、たとえば EventTime にはオフセット情報は必要ありません。このサンプルクエリで ClickHouse に必要なのは、UserID データファイル (UserID.bin) 内のグラニュール 176 に対応する 2 つの物理位置オフセットと、URL データファイル (URL.bin) 内のグラニュール 176 に対応する 2 つの物理位置オフセットだけです。マークファイルによる間接参照によって、3 つすべてのカラムについて、1083 個すべてのグラニュールの物理的位置エントリをプライマリインデックス内に直接格納せずに済みます。これにより、メインメモリに不要な (実際には使われないかもしれない) データを保持せずに済みます。
次の図とその下の説明では、このサンプルクエリにおいて ClickHouse が UserID.bin データファイル内のグラニュール 176 をどのように特定するかを示しています。 このガイドの前半で説明したように、ClickHouse はプライマリインデックスマーク 176 を選択し、したがってグラニュール 176 を、クエリに一致する行を含む可能性があるものとして選びました。 次に ClickHouse は、グラニュール 176 の位置を特定するために必要な 2 つのオフセットを取得するため、索引から選択したマーク番号 (176) を使って UserID.mrk マークファイルに対して配列の位置ルックアップを行います。 図のとおり、1 つ目のオフセットは UserID.bin データファイル内の圧縮ファイルブロックを指しており、そのブロックにはグラニュール 176 の圧縮データが含まれています。 特定したファイルブロックをメインメモリ上で展開すると、マークファイル内の 2 つ目のオフセットを使って、非圧縮データ内のグラニュール 176 の位置を特定できます。 ClickHouse がこのサンプルクエリ (UserID 749.927.693 のインターネットユーザーについて、最もクリック数の多い URL 上位 10 件) を実行するには、UserID.bin データファイルと URL.bin データファイルの両方についてグラニュール 176 を特定し、そこに含まれるすべての値をストリーミングする必要があります。 上の図は、ClickHouse が UserID.bin データファイルのグラニュールをどのように特定しているかを示しています。 並列に、ClickHouse は URL.bin データファイルのグラニュール 176 についても同じ処理を行っています。対応する 2 つのグラニュールは揃えられたうえで ClickHouse engine にストリーミングされ、そこで後続処理、すなわち UserID が 749.927.693 であるすべての行について URL の値をグループごとに集約してカウントし、最後に件数の降順で上位 10 個の URL グループを出力します。

​
複数のプライマリインデックスを使う

​
セカンダリキーカラムは効率的とは限らない

クエリが複合キーの一部であるカラム、かつ先頭のキーカラムでフィルタリングしている場合、ClickHouse はそのキーカラムの索引マークに対して二分探索アルゴリズムを実行します。 では、クエリが複合キーの一部ではあるものの、先頭のキーカラムではないカラムでフィルタリングしている場合はどうなるのでしょうか。
ここでは、クエリが先頭のキーカラムではなく、セカンダリキーカラムに対して明示的にフィルタリングしているケースを扱います。クエリが先頭のキーカラムと、それに続くいずれかのキーカラムの両方でフィルタリングしている場合、ClickHouse は先頭のキーカラムの索引マークに対して二分探索を実行します。


"http://public_search" という URL を最も頻繁にクリックしたユーザー上位 10 人を算出するクエリを使用します:
応答は次のとおりです。
クライアントの出力は、URL カラムが複合主キーの一部であるにもかかわらず、ClickHouse がテーブルのほぼ全体をスキャンしたことを示しています。ClickHouse は、テーブルの 887 万行のうち 881 万行を読み取っています。 trace_logging が有効な場合、ClickHouse サーバーのログファイルには、ClickHouse が 1083 個の URL 索引マーク に対して汎用排除検索を使用し、URL カラムの値が “http://public&#95;search” である行を含む可能性のあるグラニュールを特定したことが表示されます。
上記のサンプルのトレースログから、1083個のグラニュールのうち1076個が、一致するURL値を含む行が存在する可能性があるものとして (マークを介して) 選択されたことがわかります。 その結果、実際にURL値 “http://public&#95;search” を含む行を特定するために、881万行がClickHouse engineにストリーミングされます (10本のストリームを使って並列に) 。 しかし、後で見るように、選択された1076個のグラニュールのうち、実際に一致する行を含んでいるのは39個のグラニュールだけです。 複合主キー (UserID, URL) に基づくプライマリインデックスは、特定のUserID値を持つ行で絞り込むクエリを高速化するうえで非常に有用でしたが、特定のURL値を持つ行で絞り込むクエリの高速化には、この索引はそれほど役立っていません。 その理由は、URLカラムがキーの先頭カラムではないためです。そのためClickHouseは、URLカラムの索引マークに対してbinary searchではなく汎用排除検索アルゴリズムを使用しており、そのアルゴリズムの有効性は、URLカラムと、その直前のキーカラムであるUserIDとのカーディナリティの差に依存します。 これを示すために、汎用排除検索がどのように機能するかを少し詳しく見ていきます。

​
汎用排除検索アルゴリズム

以下では、先行するキーカラムのカーディナリティが低い場合と高い場合に、セカンダリカラムを使って グラニュール が選択される際、ClickHouse generic exclusion search algorithm がどのように動作するかを説明します。 両方のケースの例として、以下を仮定します。
  • URL 値 = “W3” の行を検索するクエリ。
  • UserID と URL の値を簡略化した、抽象化された hits テーブル。
  • 索引には同じ複合主キー (UserID, URL) を使用すること。つまり、行はまず UserID の値で並び替えられ、同じ UserID 値を持つ行はその後 URL で並び替えられます。
  • グラニュール のサイズは 2、つまり各 グラニュール には 2 行が含まれます。
以下の図では、各 グラニュール の先頭行に対応するキーカラム値をオレンジ色で示しています。 先行キーカラムのカーディナリティが低い場合 UserID のカーディナリティが低いとします。この場合、同じ UserID 値が複数のテーブル行や グラニュール、したがって複数の 索引マーク にまたがって存在する可能性が高くなります。同じ UserID を持つ 索引マーク では、URL 値は昇順に並びます (テーブル行がまず UserID、次に URL で並び替えられているためです) 。これにより、以下で説明する効率的なフィルタリングが可能になります。 上の図にある抽象化されたサンプルデータの グラニュール 選択プロセスでは、3 つの異なるシナリオがあります。
  1. URL 値が W3 より小さく、かつ直後の 索引マーク の URL 値も W3 より小さい 索引マーク 0 は除外できます。これは mark 0 と 1 が同じ UserID 値を持つためです。この除外の前提条件により、グラニュール 0 はすべて U1 の UserID 値で構成されていることが保証されるため、ClickHouse は グラニュール 0 の最大 URL 値も W3 より小さいと判断して、この グラニュール を除外できます。
  2. URL 値が W3 以下で、かつ直後の 索引マーク の URL 値が W3 以上である 索引マーク 1 は選択されます。これは、グラニュール 1 に URL が W3 の行が含まれている可能性があることを意味するためです。
  3. URL 値が W3 より大きい 索引マーク 2 と 3 は除外できます。プライマリインデックスの 索引マーク には各 グラニュール の先頭行のキーカラム値が格納され、テーブル行はディスク上でキーカラム値に従ってソートされているため、グラニュール 2 と 3 に URL 値 W3 が含まれることはありません。
先行キーカラムのカーディナリティが高い場合 UserID のカーディナリティが高い場合、同じ UserID 値が複数のテーブル行や グラニュール にまたがる可能性は低くなります。つまり、索引マーク の URL 値は単調増加にはなりません。 上の図からわかるように、URL 値が W3 より小さい表示中のすべての mark は、対応する グラニュール の行を ClickHouse engine にストリーミングするために選択されます。 これは、図中のすべての 索引マーク が上で説明したシナリオ 1 に該当する一方で、直後の 索引マーク が現在の mark と同じ UserID 値を持つ という除外の前提条件を満たしておらず、除外できないためです。 たとえば、URL 値が W3 より小さく、かつ直後の 索引マーク の URL 値も W3 より小さい 索引マーク 0 を考えてみましょう。これを除外できないのは、直後の 索引マーク 1 が現在の mark 0 と同じ UserID 値を持っていないためです。 その結果、ClickHouse は グラニュール 0 内の最大 URL 値について前提を置けなくなります。代わりに、グラニュール 0 には URL 値 W3 の行が含まれている可能性があるとみなす必要があり、mark 0 を選択せざるを得ません。 同じことが mark 1、2、3 にも当てはまります。
結論ClickHouse では、クエリが複合キーの一部ではあるものの先頭のキーカラムではないカラムで絞り込みを行う場合、二分探索アルゴリズムではなく汎用排除検索アルゴリズムを使用します。これは、直前のキーカラムのカーディナリティが低いほど効果を発揮します。
このサンプルデータセットでは、両方のキーカラム (UserID、URL) のカーディナリティはいずれも同程度に高く、前述のとおり、URL カラムの直前のキーカラムのカーディナリティが高い、あるいは同程度の場合、汎用排除検索アルゴリズムはあまり効果的ではありません。

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

UserID と URL はどちらも同程度に高いカーディナリティを持つため、URL で絞り込むクエリ では、複合主キー (UserID, URL) を持つテーブル の URL カラムに セカンダリデータスキッピングインデックス を作成しても、あまり効果はありません。 たとえば、次の 2 つのステートメントは、テーブルの URL カラムに minmax データスキッピングインデックスを作成し、それにデータを投入します。
ClickHouse は、4 つ連続するグラニュールの各グループごとに (上記の ALTER TABLE ステートメントの GRANULARITY 4 句に注目してください) 、URL の最小値と最大値を格納する追加の索引を作成しました。 最初の索引エントリ (上図の ‘mark 0’) には、テーブルの最初の 4 つのグラニュールに属する行について、URL の最小値と最大値が格納されています。 2 番目の索引エントリ (‘mark 1’) には、テーブルのその次の 4 つのグラニュールに属する行について、URL の最小値と最大値が格納されており、以降も同様です。 (ClickHouse は、索引マークに対応するグラニュールのグループを特定するために、データスキッピングインデックス用の特別なマークファイルも作成しました。) UserID と URL はどちらもカーディナリティが高いため、このセカンダリデータスキッピングインデックスは、URL でフィルタするクエリの実行時に、選択対象からグラニュールを除外するのに役立ちません。 クエリが探している特定の URL 値 (すなわち ‘http://public&#95;search&#39;) は、各グラニュールグループについて索引に格納された最小値と最大値の間に入る可能性が非常に高いため、ClickHouse はそのグラニュールグループを選択せざるを得ません (クエリに一致する行が含まれている可能性があるためです) 。

​
複数のプライマリインデックスが必要になる理由

そのため、特定の URL を持つ行で絞り込むサンプルクエリを大幅に高速化したい場合は、そのクエリに最適化されたプライマリインデックスを使う必要があります。 さらに、特定の UserID を持つ行で絞り込むサンプルクエリの高いパフォーマンスも維持したい場合は、複数のプライマリインデックスを使用する必要があります。 以下では、それを実現する方法を示します。

​
追加のプライマリインデックスを作成するための選択肢

特定の UserID を持つ行で絞り込むクエリと、特定の URL を持つ行で絞り込むクエリという 2 つのサンプルクエリをどちらも大幅に高速化したい場合は、次の 3 つの方法のいずれかで複数のプライマリインデックスを使う必要があります。
  • 異なる主キーを持つ 2 つ目のテーブル を作成する。
  • 既存のテーブル上に materialized view を作成する。
  • 既存のテーブルに プロジェクション を追加する。
これら 3 つの方法はすべて、テーブルのプライマリインデックスと行のソート順を再構成するために、サンプルデータを実質的に追加のテーブルへ複製します。 ただし、この 3 つの方法は、クエリや insert ステートメントのルーティングという観点で、その追加テーブルがユーザーに対してどの程度透過的かという点で異なります。 異なる主キーを持つ 2 つ目のテーブル を作成する場合、クエリはその内容に最も適したテーブルに明示的に送る必要があり、さらに両方のテーブルの同期を保つため、新しいデータも両方のテーブルに明示的に insert する必要があります。 materialized view では、追加のテーブルが暗黙的に作成され、データは両方のテーブル間で自動的に同期された状態に保たれます。 一方、プロジェクション は最も透過的な選択肢です。暗黙的に作成された (かつ隠された) 追加テーブルがデータの変更に合わせて自動的に同期されるだけでなく、ClickHouse がクエリごとに最も効果的なテーブルを自動的に選択するためです。 以下では、複数のプライマリインデックスを作成して使用するためのこれら 3 つの方法について、実例を交えながらさらに詳しく説明します。

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

元のテーブルと比べて主キー内のキーカラムの順序を入れ替えた、新しい追加テーブルを作成します。
元のテーブルから追加のテーブルに、887万行をすべて挿入します:
レスポンスは以下のようになります。
最後に、テーブルを最適化します:
主キー内のカラムの順序を入れ替えたことで、挿入された行はディスク上で以前とは異なる辞書式順序で保存されるようになり (元のテーブルと比較すると) 、その結果、そのテーブルの 1083 個のグラニュールに含まれる値も以前とは異なります。 結果として得られる主キーは次のとおりです。 これにより、URL カラムでフィルタリングし、URL “http://public&#95;search” を最も頻繁にクリックした上位 10 人のユーザーを算出するクエリ例を、大幅に高速化できます。
応答は次のとおりです。
これで ClickHouse は、ほぼフルテーブルスキャンを行う 代わりに、そのクエリをはるかに効率よく実行できるようになりました。 UserID が最初、URL が 2 番目のキーカラムだった 元のテーブル のプライマリインデックスでは、ClickHouse はそのクエリの実行時に索引マークに対して 汎用排除検索 を使用していましたが、UserID と URL はどちらも同程度にカーディナリティが高いため、あまり効果的ではありませんでした。 プライマリインデックスの先頭カラムが URL になったことで、ClickHouse は索引マークに対して 二分探索 を行うようになりました。 ClickHouse server のログファイル内の対応するトレースログからも、そのことが確認できます。
ClickHouse は、汎用排除検索を使用した場合の 1076 個ではなく、39 個の索引マークしか選択しませんでした。 この追加テーブルは、URL で絞り込むこのサンプルクエリの実行を高速化するよう最適化されている点に注意してください。 元のテーブルでのそのクエリのパフォーマンス不良と同様に、UserIDs で絞り込むサンプルクエリも、この新しい追加テーブルではあまり効率よく実行されません。これは、UserID がそのテーブルのプライマリインデックスにおける 2 番目のキーカラムになっており、その結果、ClickHouse がグラニュールの選択に汎用排除検索を使用するためです。これは、UserID と URL のように同程度に高いカーディナリティに対してはあまり効果的ではありません。 詳細は詳細ボックスを開いて確認してください。

応答は次のとおりです。
サーバーログ:

これでテーブルは 2 つになりました。UserIDs で絞り込むクエリの高速化用と、URL で絞り込むクエリの高速化用に、それぞれ最適化されています。

​
オプション 2: materialized view

既存のテーブルにmaterialized viewを作成します。
レスポンスは次のようになります。
  • ビューの主キーでは、元のテーブル と比べてキーカラムの順序を入れ替えています
  • materialized view は、指定した主キー定義に基づく行順序とプライマリインデックスを持つ暗黙的に作成されるテーブルを基盤としています
  • この暗黙的に作成されるテーブルは SHOW TABLES クエリに表示され、名前は .inner で始まります
  • また、materialized view のバックエンドテーブルを先に明示的に作成しておき、その後ビューから TO [db].[table] 句 でそのテーブルを指定することもできます
  • POPULATE キーワードを使うことで、ソーステーブル hits_UserID_URL にある 887 万行すべてを、暗黙的に作成されるテーブルに即座に投入します
  • ソーステーブル hits_UserID_URL に新しい行が挿入されると、それらの行は暗黙的に作成されるテーブルにも自動的に挿入されます
  • 実質的には、暗黙的に作成されるテーブルは、明示的に作成したセカンダリテーブル と同じ行順序およびプライマリインデックスを持ちます:
ClickHouse は、暗黙的に作成されるテーブルの カラムデータファイル (.bin)、mark file (.mrk2)、および プライマリインデックス (primary.idx) を、ClickHouse server の data directory 内にある特別なフォルダーに保存します:
materialized view を支える暗黙的に作成されるテーブル (およびそのプライマリインデックス) は、URL カラムで絞り込むこの例のクエリを大幅に高速化するために利用できるようになりました:
レスポンスは次のとおりです。
実質的には、materialized view を支えるために暗黙的に作成されるテーブル (およびそのプライマリインデックス) は、明示的に作成した secondary table と同一であるため、クエリは明示的に作成したテーブルの場合と実質的に同じ方法で実行されます。 ClickHouse server のログファイル内の対応するトレースログから、ClickHouse が索引マークに対して二分探索を実行していることが確認できます:“

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

既存のテーブルにプロジェクションを作成します：
次に、プロジェクションをマテリアライズします：
  • プロジェクションは、プロジェクションで指定された 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 を参照)
  • 実際には、暗黙的に作成される非表示テーブルは、明示的に作成した secondary table と同じ行順序およびプライマリインデックスを持ちます。
ClickHouse は、非表示テーブルの column data files (.bin) 、mark files (.mrk2) 、および プライマリインデックス (primary.idx) を、ソーステーブルのデータファイル、mark files、プライマリインデックスファイルの隣にある特別なフォルダー (下のスクリーンショットではオレンジ色で示されています) に保存します。
プロジェクションによって作成された非表示テーブル (およびそのプライマリインデックス) は、URL カラムでフィルタリングするこの例のクエリの実行を大幅に高速化するために、今後は (暗黙的に) 利用できるようになります。なお、このクエリは構文上はプロジェクションのソーステーブルを対象としています。
応答は次のとおりです。
projection によって作成される非表示テーブル (およびそのプライマリインデックス) は、実質的に 明示的に作成した secondary table と同一であるため、クエリも明示的に作成したテーブルの場合と実質的に同じ方法で実行されます。 これに対応する ClickHouse server のログファイル内のトレースログから、ClickHouse が索引マークに対して二分探索を実行していることが確認できます。

​
要約

複合プライマリキー (UserID, URL) を持つテーブルのプライマリインデックスは、UserID で絞り込むクエリの高速化に非常に有効でした。しかし、URL カラムも複合プライマリキーの一部であるにもかかわらず、この索引は URL で絞り込むクエリの高速化にはあまり役立ちません。 逆に、 複合プライマリキー (URL, UserID) を持つテーブルのプライマリインデックスは、URL で絞り込むクエリの高速化には有効でしたが、UserID で絞り込むクエリにはあまり効果がありませんでした。 これは、主キーカラムである UserID と URL のカーディナリティがどちらも同程度に高いため、2 番目のキーカラムで絞り込むクエリは、そのカラムが 2 番目のキーカラムとして索引に含まれていても、あまり恩恵を受けないからです。 そのため、プライマリインデックスから 2 番目のキーカラムを除外し (その結果、索引のメモリ消費量も減ります) 、代わりに複数のプライマリインデックスを使用するのが理にかなっています。 ただし、複合プライマリキーを構成するキーカラム間でカーディナリティに大きな差がある場合は、主キーカラムをカーディナリティの低い順に並べることが、クエリにとって有効です。 キーカラム間のカーディナリティの差が大きいほど、それらのカラムをキー内でどう並べるかが重要になります。次の節でこれを示します。

​
キーカラムの順序を効率的に決める

複合プライマリキーでは、キーカラムの順序が次の 2 点に大きく影響する可能性があります。
  • クエリで後続のキーカラムをフィルタリングする際の効率
  • テーブルのデータファイルの圧縮率
それを示すために、Web トラフィックのサンプルデータセット の一種を使用します。 このデータセットでは、各行に 3 つのカラムがあり、インターネットの「ユーザー」 (UserID カラム) による URL (URL カラム) へのアクセスが、ボットトラフィックとしてマークされたかどうかを示します (IsRobot カラム) 。 ここでは、前述の 3 つのカラムすべてを含む複合プライマリキーを使用します。これは、次のような典型的な web analytics のクエリを高速化するために使用できます。
  • 特定の URL へのトラフィックのうち、どの程度 (何パーセント) がボットによるものか
  • 特定のユーザーがボットである (またはボットではない) と、どの程度確信できるか (そのユーザーからのトラフィックのうち、何パーセントがボットトラフィックだと見なされるか、またはそうでないと見なされるか)
複合プライマリキーのキーカラムとして使用したい 3 つのカラムのカーディナリティを計算するには、次のクエリを使用します (ローカルテーブルを作成しなくても TSV データに対してアドホックにクエリできるよう、URL table function を使用している点に注意してください) 。このクエリを 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) をカーディナリティの昇順で並べたテーブルに対する同じクエリです。
応答は次のとおりです。
キーカラムをカーディナリティの昇順で並べたテーブルでは、クエリ実行の効率が大幅に向上し、処理も高速化されることがわかります。 その理由は、汎用排除検索アルゴリズムが、先行するキーカラムのカーディナリティの方が低い場合に、セカンダリキーカラム経由でグラニュールを選択すると最も効果的に機能するためです。この点については、このガイドの前の節で詳しく説明しています。

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

このクエリでは、先ほど作成した 2 つのテーブルにおける UserID カラムの圧縮率を比較します。
レスポンスは次のとおりです:
UserID カラムの圧縮率は、キーカラム (IsRobot, UserID, URL) をカーディナリティの昇順に並べたテーブルのほうが、明らかに高いことがわかります。 両方のテーブルに格納されているデータはまったく同じであるにもかかわらず (両方のテーブルに同じ 887 万行を insert しました) 、複合プライマリキーにおけるキーカラムの順序は、テーブルの 圧縮された データが カラムデータファイル 上で必要とするディスク容量に大きく影響します。
  • 複合プライマリキー (URL, UserID, IsRobot) を持ち、キーカラムをカーディナリティの降順に並べたテーブル hits_URL_UserID_IsRobot では、UserID.bin データファイルは 11.24 MiB のディスク容量を使用します
  • 複合プライマリキー (IsRobot, UserID, URL) を持ち、キーカラムをカーディナリティの昇順に並べたテーブル hits_IsRobot_UserID_URL では、UserID.bin データファイルが使用するディスク容量は 877.47 KiB にすぎません
ディスク上のテーブルのカラムデータの圧縮率が高いと、ディスク容量を節約できるだけでなく、そのカラムからデータを読み取る必要があるクエリ (特に分析クエリ) も高速になります。これは、カラムデータをディスクからメインメモリ (オペレーティングシステムのファイル cache) へ移動するのに必要な I/O が少なくて済むためです。 以下では、テーブルの各カラムの圧縮率という観点から、主キーカラムをカーディナリティの昇順に並べることが有効である理由を説明します。 次の図は、キーカラムをカーディナリティの昇順に並べた主キーにおける、ディスク上の行の並び順の概略を示しています。 すでに、テーブルの行データは主キーカラム順に並べられてディスクに格納されることを説明しました。 上の図では、テーブルの行 (ディスク上の各カラム値) は、まず cl の値で並べられ、同じ cl 値を持つ行は ch の値で並べられます。そして、最初のキーカラム cl は low cardinality であるため、同じ 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 行) 。 貼り付けられた内容を識別して取得する (特定バージョンを取り出す) 方法の 1 つは、その内容のハッシュを、その内容を格納するテーブル行の UUID として使うことです。 次の図は、以下を示しています。
  • 内容が変化したときの行の挿入順序 (たとえば、テキストエリアに文字を入力するキーストロークによる変化) と
  • PRIMARY KEY (hash) を使用した場合の、挿入された行のデータのディスク上での並び順:
hash カラムが主キーカラムとして使われるため、
  • 特定の行は非常に高速に取得できますが、
  • テーブルの行 (そのカラムデータ) は、ディスク上では (一意でランダムな) hash 値の昇順で保存されます。そのため、content カラムの値もデータ局所性のないランダムな順序で保存され、content カラムデータファイルの圧縮率は最適になりません。
特定の行を高速に取得しつつ、content カラムの圧縮率を大幅に改善するため、pastila.nl では特定の行を識別するために 2 つのハッシュ (と複合プライマリキー) を使っています。 次の図は、以下を示しています。
  • 内容が変化したときの行の挿入順序 (たとえば、テキストエリアに文字を入力するキーストロークによる変化) と
  • 複合 PRIMARY KEY (fingerprint, hash) を使用した場合の、挿入された行のデータのディスク上での並び順:
この場合、ディスク上の行はまず fingerprint で並べられ、同じ fingerprint 値を持つ行どうしでは、hash 値によって最終的な順序が決まります。 わずかな違いしかないデータには同じ fingerprint 値が与えられるため、似たデータどうしが content カラム内で近くに保存されるようになります。これは content カラムの圧縮率にとって非常に有利です。一般に圧縮アルゴリズムはデータ局所性の恩恵を受けるためです (データが似ているほど圧縮率は高くなります) 。 その代わり、複合 PRIMARY KEY (fingerprint, hash) によって得られるプライマリインデックスを最適に活用するには、特定の行を取得する際に 2 つのフィールド (fingerprint と hash) が必要になります。
最終更新日 2026年6月10日