05 Sort key

Satu pilihan kolom, dibuat konkret dengan DDL nyata, yang mengubah lookup yang membaca hampir seluruh tabel menjadi lookup yang membaca dua granule -- diukur di sisi server, bukan di jam console.

Hasil akhir

Dalam sekitar 30 menit Anda akan menjawab lookup satu-user yang sama seperti yang dibuka modul 01, dan mengukur seberapa sedikit baris yang harus dibaca ClickHouse untuk menjawabnya. Dua puluh baris yang sama keluar. Baris yang tersentuh jauh lebih sedikit untuk mendapatkannya, dan Anda akan membangun persis tabel yang membawa Anda ke sana.

Lookup-nya, dan kenapa butuh tabel baru

Query yang dibuat cepat oleh modul ini mengembalikan, untuk satu user_pseudo_id, dua puluh event terbaru lebih dulu, dengan persis kolom dan tipe ini:

Kolom	Tipe
`event_time`	`DateTime64(6)`
`event_name`	`String`
`geo_country`	`String`

Tidak ada tabel yang sudah Anda punya yang bisa menjawab itu secara langsung. bq.events_naive sama sekali tidak punya kolom event_time -- ia punya event_timestamp, sebuah Int64 mentah berisi microsecond -- dan juga tidak punya kolom geo_country yang flat, hanya geo.country yang bersarang di dalam tuple. bq.events_tuned, tabel yang dibangun modul 04 untuk Anda, sudah punya kolom flat event_time, event_name, dan geo_country dengan persis nama dan tipe ini -- tapi sort key-nya, (event_date, event_name, user_pseudo_id), dipilih untuk cerita kompresi modul 04, bukan untuk lookup ini.

Gunakan probe user yang sama yang sudah dipakai modul 01 terhadap BigQuery: 3272961.4196485002, 39 event dalam export penuh, cukup di atas minimum 25 event yang membuat "20 terbaru" benar-benar sebuah filter, bukan pembacaan penuh riwayat user itu.

Langkah 1: Ukur baseline-nya

Jalankan query kontrak terhadap bq.events_tuned dulu, jadi Anda punya angka "sebelum" yang nyata, bukan yang diasumsikan. Jalankan sekali, dengan condition cache dimatikan secara eksplisit, dan baca read_rows langsung dari results bar console-nya:

SELECT event_time, event_name, geo_country
FROM bq.events_tuned
WHERE user_pseudo_id = '3272961.4196485002'
ORDER BY event_time DESC
LIMIT 20
SETTINGS use_query_condition_cache = 0;

Terhadap bq.events_tuned, query ini membaca 3.959.712 baris -- praktis seluruh tabelnya. user_pseudo_id berada ketiga di sort key tabel itu, (event_date, event_name, user_pseudo_id), dan sort key MergeTree hanya membiarkan ClickHouse skip granule untuk filter equality pada kolom terdepannya, atau prefix terdepan yang dipegang sama. Posisi ketiga tidak menyumbang apa pun sendirian.

Langkah 2: Bangun tabel yang disortir untuk lookup ini

CREATE TABLE bq.events_by_user
(
  user_pseudo_id String        CODEC(ZSTD(1)),
  event_time     DateTime64(6) CODEC(Delta, ZSTD(1)),
  event_name     LowCardinality(String),
  geo_country    LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (user_pseudo_id, event_time);

INSERT INTO bq.events_by_user
SELECT user_pseudo_id, event_time, event_name, geo_country FROM bq.events_tuned;

user_pseudo_id memimpin sort key-nya, jadi setiap granule yang bisa disingkirkan primary index sparse ClickHouse untuk seorang user, memang disingkirkan: tabelnya secara fisik disortir sehingga baris satu user duduk bersama alih-alih tersebar di seluruh tabel dalam urutan insertion. event_time datang kedua sehingga, di dalam baris satu user, mereka sudah disortir dengan cara yang diinginkan ORDER BY event_time DESC LIMIT 20 kontraknya, menghindari sort in-memory terpisah atas seluruh riwayat user itu.

Dua hal yang akan membuat pengukuran ulang Anda sendiri berbohong

Query condition cache. ClickHouse mengingat, per granule, apakah sebuah WHERE sebelumnya sudah cocok di sana, dan menggunakan kembali jawaban itu untuk query berikutnya dengan predikat literal yang persis sama. Itu menghargai mengajukan pertanyaan yang persis sama berulang-ulang, yang bukan bentuk nyata lookup ini: sebuah halaman profil memfilter berdasarkan siapa pun user yang baru memuat halaman, bukan user_pseudo_id yang sama empat kali berturut-turut. Pertahankan SETTINGS use_query_condition_cache = 0 di setiap run yang diukur waktunya.

Part yang belum termerge setelah bulk load. Satu INSERT massal mendarat sebagai beberapa part, masing-masing disortir secara independen, sehingga baris user yang sama duduk di setiap part sampai background merge menyusulnya: satu granule dibaca per part alih-alih satu granule total, bahkan dengan sort key yang dipilih dengan sempurna. Diukur di sini: tabel membaca 57.344 baris di 5 granule yang tersebar tepat setelah bq.events_by_user di-load, dan 16.384 baris di 2 granule yang bersebelahan beberapa menit kemudian, setelah background merge menyusul sendiri. Beri tabel beberapa menit setelah loading, lalu jalankan ulang query kontraknya -- percayai angka yang belakangan itu.

Langkah 3: Konfirmasi kemenangannya

Jalankan ulang query kontrak yang sama, kali ini terhadap bq.events_by_user:

SELECT event_time, event_name, geo_country
FROM bq.events_by_user
WHERE user_pseudo_id = '3272961.4196485002'
ORDER BY event_time DESC
LIMIT 20
SETTINGS use_query_condition_cache = 0;

Baca read_rows dengan cara yang sama seperti Langkah 1 -- langsung dari results bar console-nya:

3,959,712 barisbq.events_tuned, sebelum

16,384 barisbq.events_by_user, setelah

241.7xbaris dibaca lebih sedikit

Selisih latency antara keduanya hanya sekitar 16x (66 ms lawan 4 ms) -- nyata, tapi hanya sebagian kecil dari cerita, dan itulah angka yang akan menjadi headline kalau latency yang jadi metriknya, bukan read_rows.

Sebelum mempercayai angka itu, lihat kenapa-nya dengan EXPLAIN indexes = 1 di depan query:

EXPLAIN indexes = 1
SELECT event_time, event_name, geo_country
FROM bq.events_by_user
WHERE user_pseudo_id = '3272961.4196485002'
ORDER BY event_time DESC
LIMIT 20;

Baris yang harus dibaca adalah Granules di bawah step PrimaryKey. Jalankan ini terhadap bq.events_tuned dan baris itu melaporkan jumlah granule di atau mendekati total tabelnya -- primary index tidak menemukan apa pun yang bisa disingkirkan, karena user_pseudo_id berada ketiga di sort key itu. Jalankan terhadap bq.events_by_user dan baris yang sama melaporkan jumlah kecil yang dipilih dari total yang sama. Perbandingan before/after itu, bukan stopwatch, adalah cara Anda mengonfirmasi perubahan sort key benar-benar mengubah sesuatu.

Kenapa 16.384, bukan 20

Primary index ClickHouse adalah sparse -- satu entry per granule (8.192 baris secara default), bukan satu per baris. Ia bisa memberi tahu granule mana yang mungkin menyimpan match; ia tidak pernah dibangun untuk mengatakan baris mana di dalam sebuah granule yang menyimpannya. Jadi sort key apa pun, seberapa pun bagus dipilih, tidak bisa membaca lebih sedikit baris dari satu granule penuh untuk match apa pun.

16.384 baris bq.events_by_user adalah persis dua granule (16.384 / 8.192 = 2), bukan satu. Itu bukan hasil yang lebih longgar dari yang seharusnya bisa -- itu adalah lantai realistis untuk lookup jenis ini. Nilai target sebuah lookup equality duduk di suatu tempat dalam rentang yang dicakup satu mark index, dan batas rentang itu jarang persis sejajar dengan tempat baris yang cocok dimulai, sehingga sebuah sparse index umumnya harus membracket sebuah match di antara dua mark yang bersebelahan alih-alih mendarat rapi di dalam satu. Dua puluh baris dari 16.384 yang dibaca tetap dunia yang sama sekali berbeda dari dua puluh baris dari 3.959.712. Itu hanya bukan hal yang sama dengan membaca dua puluh.

Verifikasi rebuild-nya tidak mengubah data

Menyortir kolom yang sama ke tabel baru seharusnya tidak pernah mengubah apa yang dikatakannya -- hanya seberapa cepat ia menjawab. bq.events_by_user adalah copy kolom langsung dari bq.events_tuned, tanpa filter dan tanpa value terhitung, jadi satu-satunya cara ia bisa salah adalah baris yang hilang atau terduplikasi. Konfirmasi jumlahnya cocok dengan sumbernya:

SELECT count() FROM bq.events_by_user;

Harapkan 4.295.584, sama seperti bq.events_naive dan bq.events_tuned.

Selesai kalau

bq.events_by_user mengembalikan bentuk yang benar, jumlah barisnya cocok dengan sumbernya, dan Anda bisa menunjuk output EXPLAIN indexes = 1 yang menjelaskan kenapa read_rows turun dari 3.959.712 ke 16.384. Lanjut ke 06 Dashboard yang mustahil kalau Anda sudah siap.