TT Lab
はじめる
学ぶ 学習パス コース

ClickHouse — 列指向分析 DB を中身から

ソートキーの順序を変えながら読み飛ばしたグラニュールを数える

TT Labで続きを見る

目標

同じ200万行をソートキーの違う複数のテーブルに入れ、EXPLAIN indexes = 1のグラニュール数とrows_readで、スパースなプライマリインデックスが何をスキップするのかを確認します。最後に、与えられたクエリに合うソートキーを自分で選びます。

なぜ重要なのか

ClickHouseのテーブルのソートキーは、作成後に変えにくく、誤って選ぶとインデックスがあっても毎回すべてを読みます。どの列を先頭に置くかは、勘ではなく「このクエリは何グラニュールを選ぶのか」で決めます。このラボの採点ツールは、書き込まれた数値をそのまま信用しません。保存されたSELECTにEXPLAINを付けて読み取り専用でもう一度実行し、同じSELECTを再実行して読んだ行数を測って突き合わせます。

ステップ

  1. データベースsparseとテーブルsparse.hitsを作成してください。列はsite LowCardinality(String), user_id UInt32, ts DateTime, dur_ms UInt32(この順序)、エンジンはMergeTree、ソートキーはORDER BY (site, user_id)です。
  2. /opt/lab/fixtures/sparse/hits.sqlを1回だけ実行して200万行を入れ、OPTIMIZE TABLE sparse.hits FINALでパートを1つにマージしてください。
  3. site = 'docs.example'の行のdur_msの合計を求めるクエリ(/root/ch/sparse/q_site.sql)を作成してください。そのSELECTにEXPLAIN indexes = 1を付けた出力を保存し(保存先: /root/ch/sparse/explain_site.txt)、PrimaryKey段階で選ばれたグラニュール数と全グラニュール数、および検索方式をselected・total・searchとして書き込んでください(保存先: /root/ch/sparse/granules.json)。
  4. user_id = 4242の行のdur_msの合計を求めるクエリ(/root/ch/sparse/q_user.sql)を作成し、EXPLAINで選ばれたグラニュール数・検索方式と、このクエリのrows_readをselected・search・rows_readとして書き込んでください(保存先: /root/ch/sparse/user.json)。
  5. 同じ列でソートキーだけORDER BY (user_id, site)のsparse.hits_usを作成し、sparse.hitsの行を移してパートを1つにマージしてください。そのテーブルでuser_id = 4242の合計を求めるクエリ(/root/ch/sparse/q_user_us.sql)と、site = 'docs.example'の合計を求めるクエリ(/root/ch/sparse/q_site_us.sql)を作成し、2つのクエリのrows_readをuser_rows_read・site_rows_readとして書き込んでください(保存先: /root/ch/sparse/order.json)。
  6. sparse.hitsと同じ列・ソートキーでSETTINGS index_granularity = 1024を指定したsparse.hits_g1kを作成し、行を移してマージしてください。そのテーブルのmarks・primary_key_size(system.parts)と、user_id = 4242の合計を求めるクエリ(/root/ch/sparse/q_user_g1k.sql)のrows_readを、marks・primary_key_size・user_rows_readとして書き込んでください(保存先: /root/ch/sparse/granularity.json)。
  7. ソートキーORDER BY (site, user_id, ts)にプライマリキーPRIMARY KEY (site, user_id)を別に指定したsparse.hits_pkと、同じソートキーでPRIMARY KEYを指定しないsparse.hits_fullを作成し、行を移してマージしてください。2つのテーブルのprimary_key_sizeをhits_pk・hits_fullとして書き込んでください(保存先: /root/ch/sparse/pk.json)。
  8. 1日分のサイトレポートWHERE site = 'api.example' AND ts >= '2026-09-10 00:00:00' AND ts < '2026-09-11 00:00:00'のdur_msの合計をsparse.hitsから求めるクエリ(/root/ch/sparse/q_day_hits.sql)と、ソートキーを自分で選んで作ったsparse.hits_day(同じ列・同じ行、パート1つ)から求めるクエリ(/root/ch/sparse/q_day.sql)を作成してください。q_day.sqlのrows_readがq_day_hits.sqlの4分の1以下でなければなりません。2つの値をhits_rows_read・day_rows_readとして書き込んでください(保存先: /root/ch/sparse/day.json)。

参考

種類の少ない列を先頭に置いたテーブル

データベースsparseとテーブルsparse.hitsを作成してください。列はsite LowCardinality(String), user_id UInt32, ts DateTime, dur_ms UInt32の順序、エンジンはMergeTree、ソートキーはORDER BY (site, user_id)です。

CREATE DATABASEとCREATE TABLEの2文です。siteは値が5種類、user_idは5万種類です。種類の少ない列が先頭に来る順序です。列名・型・順序が違うと、次のステップの元スクリプトが入りません。

200万行を入れてパートを1つにする

/opt/lab/fixtures/sparse/hits.sqlを1回だけ実行してsparse.hitsに2,000,000行を入れ、OPTIMIZE TABLE sparse.hits FINALでアクティブなパートを1つにしてください。

1回のINSERTが、ブロックサイズによって複数のパートを作ることがあります。パートが複数あるとEXPLAINがパートごとにグラニュールを別々に数えるので、比較する前に1つにマージします。行数が400万なら、2回入れています。TRUNCATEしてから、もう一度入れてください。

最初のキー列で絞り込むと二分探索

site = 'docs.example'の行のdur_msの合計を求めるクエリ(/root/ch/sparse/q_site.sql)を作成し、そのSELECTにEXPLAIN indexes = 1を付けた出力を保存してください(保存先: /root/ch/sparse/explain_site.txt)。PrimaryKey段階のGranules: 고른/전체(プレースホルダーは選ばれたグラニュール数と全グラニュール数です)とSearch Algorithmを、selected・total・searchとして書き込んでください(保存先: /root/ch/sparse/granules.json)。

EXPLAINの出力にはGranulesが2回出てきます。ReadFromMergeTreeのすぐ下の行は最終結果で、このステップが尋ねているのは、Indexesの下のPrimaryKeyブロックの数値です。検索方式の文字列は、空白までそのまま書き写します。最初のキー列はソートされているので、範囲の始まりと終わりをすぐに見つけられます。

2番目のキー列だけで絞り込むと

user_id = 4242の行のdur_msの合計を求めるクエリ(/root/ch/sparse/q_user.sql)を作成してください。EXPLAINのPrimaryKey段階で選ばれたグラニュール数と検索方式を、--use_query_condition_cache 0で実行したこのクエリのstatistics.rows_readとともに、selected・search・rows_readとして書き込んでください(保存先: /root/ch/sparse/user.json)。

user_idはサイトのまとまりごとに最初からソートし直されているので、全体ではソートされていません。そのため二分探索の代わりに、隣り合うマークの間で前の列の値が変わらない区間だけを除外する方式を使います。サイトのまとまりが5つであることと、選ばれたグラニュール数を結びつけて考えてみてください。rows_readは、選ばれたグラニュール数×8192の近くになります。

キーの順序を逆にしたテーブル

同じ列でソートキーだけORDER BY (user_id, site)のsparse.hits_usを作成し、sparse.hitsの行を移してOPTIMIZE ... FINALでマージしてください。そのテーブルでuser_id = 4242のdur_msの合計を求めるクエリ(/root/ch/sparse/q_user_us.sql)と、site = 'docs.example'の合計を求めるクエリ(/root/ch/sparse/q_site_us.sql)を作成し、2つのクエリのrows_readをuser_rows_read・site_rows_readとして書き込んでください(保存先: /root/ch/sparse/order.json)。

CREATE TABLE ... AS sparse.hits ENGINE = MergeTree ORDER BY (...)で、列をコピーしつつソートキーだけを新しく指定できます。1グラニュール8192行の中にユーザーが約200人入っているとしたら、隣り合う2つのマークの間で、最初の列のuser_idが同じになることはあるでしょうか。ないとしたら、後ろの列のsiteでは、どの区間も除外できません。

グラニュールを1024行に縮めると

sparse.hitsと同じ列・ソートキーでSETTINGS index_granularity = 1024を指定したsparse.hits_g1kを作成し、行を移してマージしてください。system.partsのmarks・primary_key_sizeと、user_id = 4242の合計を求めるクエリ(/root/ch/sparse/q_user_g1k.sql)のrows_readを、marks・primary_key_size・user_rows_readとして書き込んでください(保存先: /root/ch/sparse/granularity.json)。

SETTINGS句はORDER BYの後ろに付けます。グラニュールが小さくなると、同じ条件が選ぶグラニュール数は似ていても、グラニュールごとに読む行が減ります。その代わり、マークが増えてインデックスファイル(primary_key_size)が大きくなります。インデックスをメモリに置く設計なので、これがコストです。marksはグラニュール数より1つ多くなります。

PRIMARY KEYをソートキーの先頭部分にする

ソートキーORDER BY (site, user_id, ts)にPRIMARY KEY (site, user_id)を別に指定したsparse.hits_pkと、同じソートキーでPRIMARY KEYを指定しないsparse.hits_fullを作成し、それぞれ行を移してマージしてください。2つのテーブルのsystem.partsのprimary_key_sizeを、hits_pk・hits_fullとして書き込んでください(保存先: /root/ch/sparse/pk.json)。

PRIMARY KEYを指定しないと、ソートキー全体がプライマリキーになります。別に指定するときは、ソートキーの先頭部分でなければなりません。(user_id)のように先頭部分でないものを書くと、テーブルが作成されません。ソート順は同じなので、ファイルサイズの違いは、インデックスに記録される列の数から生まれます。

クエリに合うソートキーを選ぶ

1日分のサイトレポートの条件site = 'api.example' AND ts >= '2026-09-10 00:00:00' AND ts < '2026-09-11 00:00:00'で、dur_msの合計を求めるクエリを2つ作成してください。sparse.hitsを読むクエリ(/root/ch/sparse/q_day_hits.sql)と、自分でソートキーを選んで作ったsparse.hits_day(同じ列、sparse.hitsの全行、パート1つ)を読むクエリ(/root/ch/sparse/q_day.sql)です。q_day.sqlのrows_readがq_day_hits.sqlの4分の1以下になるようにキーを選び、2つの値をhits_rows_read・day_rows_readとして書き込んでください(保存先: /root/ch/sparse/day.json)。

sparse.hitsはsiteでは絞り込めますが、その中がuser_id順なので、tsの範囲がインデックスにかかりません。条件の中で等号で来る列と範囲で来る列はどれか、そして2番目の列がインデックスにかかるためには、前の列がどうなっている必要があるか(ステップ4)を思い出してみてください。選んだあとは、まずEXPLAINで確認します。