ワークシート2: sort key の設計 (ORDER BY)
NYC タクシーの各テーブルについて、そのクエリワークロードから ORDER BY を導きます。回答ごとに即時にフィードバックが返ります。
所要時間の目安: 20〜25分 参照: MergeTree エンジン — ORDER BY の設計のセクション
概念
ClickHouse の ORDER BY 句は飾りではありません。これが primary index を定義します —
スパースなブロック単位のインデックスで、WHERE 句を評価するときに ClickHouse が無関係な
データブロックをスキップできるようにします。さらにパート内でのデータの物理的なソート順も
決めるため、圧縮率にも影響します。
誤った ORDER BY = 遅いクエリ + 無駄なストレージ。 UUID を先頭に置いた ORDER BY は、 どの分析クエリでもブロックスキップが効かないことを意味します（UUID はランダムであり、 ソート可能な先頭部分が存在しません）。日付を先頭に置いた ORDER BY なら、日付で絞り込む クエリはテーブルの大部分をスキップできます。
ORDER BY 設計の3つのルール
ルール1: ソーススキーマではなく、クエリのフィルタから導く。 最も頻繁なクエリ全体で、
WHERE、GROUP BY、JOIN に現れるカラムを見てください。最もよく絞り込みに使われるカラムは、
おそらく ORDER BY に含めるべきです（カーディナリティが高すぎない限り）。ソーステーブルの
primary key（もしあれば）は、通常は関係ありません。
ルール2: 低カーディナリティを先、高カーディナリティを後。 ClickHouse の primary index は
約8192行（グラニュール）ごとに1エントリを持ちます。低カーディナリティのカラム（例:
toStartOfMonth(date) = 4年間で約48個の異なる値）は多くの行をまとめて集めるため —
インデックスはグラニュールを丸ごとスキップできます。高カーディナリティのカラム（例:
trip_id = 5000万個の異なる値）は行ごとに一意なので、それを先頭に置くとインデックスは何も
スキップできません。この並べ方はデフォルトであって、ルール1を上書きするものではありません。
範囲でフィルタされて大半の行を除外できるカラムは、等価比較でしか絞り込まれない、より
カーディナリティの低いカラムより先頭を取ることが依然としてありえます。
ルール3: ReplacingMergeTree では、一意な行識別子で終える。 重複排除のキーは ORDER BY
タプル全体です。ORDER BY に trip_id が欠けていると、同じ pickup_at を持ち、それ以上の
カラムがない2つの異なる trip が重複として扱われてしまいます。インデックス性能を損なわずに
一意性を確保するため、trip_id は最後に置いてください。
演習: クエリワークロードの分析
sort key を設計する前に、クエリが実際にどのカラムで絞り込んでいるのかを洗い出します。NYC タクシーのラボには代表的なクエリが7つあります。それぞれについて、最も多くの行を除外する フィルタカラムを選んでください。
演習: カーディナリティの見積もり
ORDER BY の候補となる各カラムについて、4年・5000万行のデータセットにおけるカーディナリティを
見積もってください。以下の表のほとんどは参考データです — 開いている2つのセルは pickup_at
自身の推定される異なる値の数と、そのカーディナリティ区分です。4年は約1億2600万秒（そして
わずか約210万分）であり、プロデューサーは全 trip のタイムスタンプを壁時計から付け、
PICKUP_AT は DateTime64(3, 'UTC') として保存されています — 5000万件の trip がそのうち
いくつのスロットを占めうるかを割り出してから、区分を選んでください。
演習: sort key の設計
クエリワークロードの分析とカーディナリティの見積もりを使って、trips_raw、fact_trips、
agg_hourly_zone_trips の ORDER BY を提案してください。要点:
- 低カーディナリティを先に → ブロックスキップが最大になります。
- 複数のクエリの
WHERE/GROUP BYに現れるカラム → 含めてください。 - ReplacingMergeTree のテーブルでは → 一意な行識別子で終えてください。
- 絞り込みに一度も使われないカラムは含めないでください。
すべての表を埋めたら、推論と振り返りの問いに取り組みます。
Loading worksheet...
migration-plan.md への転記
このワークシートを記入し終えたら、ORDER BY の判断を migration-plan.md のセクション4に
コピーし、次をチェックしてください。
- [ ] Sort key design: completed