Worksheet 2: Desain sort key (ORDER BY)

Perkiraan waktu: 20–25 menit Referensi: Engine MergeTree — bagian ORDER BY Design

Konsep

Klausa ORDER BY di ClickHouse bukan hiasan. Ia mendefinisikan primary index — indeks sparse pada level blok yang memungkinkan ClickHouse melewati blok data yang tidak relevan saat mengevaluasi klausa WHERE. Ia juga menentukan urutan fisik data di dalam part, yang berpengaruh pada kompresi.

ORDER BY yang salah = query lambat + penyimpanan terbuang. ORDER BY yang dimulai dengan UUID berarti tidak ada pelewatan blok untuk query analitik apa pun (UUID bersifat acak; tidak ada prefiks yang bisa diurutkan). ORDER BY yang dimulai dengan tanggal berarti query yang memfilter tanggal melewati sebagian besar tabel.

Tiga aturan desain ORDER BY

Aturan 1: Turunkan dari filter query, bukan dari skema sumber. Lihat kolom WHERE, GROUP BY, dan JOIN di seluruh query yang paling sering Anda jalankan. Kolom yang paling sering difilter sebaiknya muncul di ORDER BY (asalkan kardinalitasnya tidak terlalu tinggi). Primary key tabel sumber (jika ada) biasanya tidak relevan.

Aturan 2: Kardinalitas rendah dulu, kardinalitas tinggi terakhir. Primary index ClickHouse punya satu entri per ~8192 baris (sebuah granule). Kolom berkardinalitas rendah (misalnya, toStartOfMonth(date) = ~48 nilai berbeda selama 4 tahun) mengelompokkan banyak baris bersama-sama — indeks bisa melewati granule secara utuh. Kolom berkardinalitas tinggi (misalnya, trip_id = 50 juta nilai berbeda) unik per baris — menempatkannya di depan berarti indeks tidak bisa melewati apa pun. Urutan ini adalah default, bukan pembatalan Aturan 1: sebuah kolom yang difilter sebuah query secara rentang, sehingga mengeliminasi sebagian besar baris, tetap bisa berhak memimpin di atas kolom berkardinalitas lebih rendah yang hanya pernah difilter secara kesetaraan.

Aturan 3: Untuk ReplacingMergeTree, akhiri dengan pengenal baris yang unik. Kunci deduplikasi adalah seluruh tuple ORDER BY. Jika trip_id tidak ada di ORDER BY, dua perjalanan berbeda dengan pickup_at yang sama dan tanpa kolom tambahan akan diperlakukan sebagai duplikat. Tempatkan trip_id di akhir untuk memastikan keunikan tanpa merusak performa indeks.

Latihan: analisis workload query

Sebelum mendesain sort key, cari tahu kolom apa yang sebenarnya difilter query. Lab NYC Taxi punya 7 query representatif; untuk masing-masing, pilih kolom filter yang mengeliminasi paling banyak baris.

Latihan: estimasi kardinalitas

Untuk setiap kandidat kolom ORDER BY, perkirakan kardinalitasnya pada dataset 4 tahun dengan 50 juta baris. Sebagian besar tabel di bawah adalah data referensi — dua sel yang terbuka adalah estimasi nilai berbeda dan kelas kardinalitas milik pickup_at sendiri. Empat tahun kurang lebih 126 juta detik (dan hanya sekitar 2,1 juta menit), producer memberi timestamp pada setiap perjalanan dari jam dinding, dan PICKUP_AT disimpan sebagai DateTime64(3, 'UTC') — hitung berapa banyak dari slot itu yang bisa diisi 50 juta perjalanan sebelum Anda memilih rentangnya.

Latihan: desain sort key

Dengan analisis workload query dan estimasi kardinalitas Anda, usulkan sebuah ORDER BY untuk trips_raw, fact_trips, dan agg_hourly_zone_trips. Pengingat:

• Kardinalitas rendah dulu → pelewatan blok paling banyak.
• Kolom yang muncul di WHERE/GROUP BY beberapa query → sertakan.
• Untuk tabel ReplacingMergeTree → akhiri dengan pengenal baris yang unik.
• Jangan sertakan kolom yang tidak pernah difilter.

Lalu kerjakan pertanyaan penalaran dan refleksi setelah setiap tabel terisi.

Loading worksheet...

Pindahkan ke migration-plan.md

Setelah Anda mengisi worksheet ini, salin keputusan ORDER BY Anda ke Bagian 4 dari migration-plan.md dan centang:

- [ ] Sort key design: completed