ORDER BY i PRIMARY KEY w ClickHouse: wybór indeksu

ORDER BY i PRIMARY KEY w ClickHouse: jak nie przegapić z indeksem

1. Dlaczego ORDER BY to najważniejsza rzecz, którą określisz w tabeli

W znanych bazach danych (PostgreSQL, MySQL) istnieją dwa pojęcia: indeks klastrowany (klucz główny, który fizycznie porządkuje dane na dysku) i indeksy wtórne (oddzielne drzewa B). Możesz dodać lub usunąć indeks w dowolnym momencie, nie przebudowując tabeli.

W ClickHouse jest inaczej. Tutaj istnieje tylko jeden fizyczny porządek danych na dysku – ten, który określiłeś w ORDER BY. I zmiana go bez przebudowy tabeli jest niemożliwa. W ogóle. To jak wylanie betonu i zorientowanie się, że zbrojenie jest w złym miejscu. Przeróbka – tylko wykuwanie wszystkiego i zaczynanie od nowa.

Dlaczego tak sztywno? Ponieważ ClickHouse przechowuje dane w formacie kolumnowym, mocno skompresowane. Aby zmienić kolejność wierszy, trzeba by przepisać wszystkie kolumny od nowa. Nikt nie chce czekać godzin czy dni na reorganizację tabeli o rozmiarze terabajtów.

Dlatego wybór ORDER BY to decyzja strategiczna. Musisz przewidzieć, które zapytania będą najczęstsze, i zaprojektować klucz tak, aby były błyskawiczne. Błąd będzie kosztowny.

Analogia z życia: Wyobraź sobie, że jesteś bibliotekarzem i musisz ułożyć wszystkie książki na półkach w określonej kolejności. Możesz wybrać porządek, np. według gatunku, a w jego obrębie – według nazwiska autora. Potem możesz szybko znajdować książki, jeśli szukasz według tych kryteriów. Ale jeśli zdecydujesz, że porządek według daty wydania byłby wygodniejszy – będziesz musiał przekładać wszystkie książki od nowa. Godzinami.

2. PRIMARY KEY ⊆ ORDER BY – rzadka reguła

W ClickHouse masz dwa parametry:

• ORDER BY – określa fizyczny porządek wierszy na dysku (obowiązkowy).
• PRIMARY KEY – określa indeks (opcjonalny).

I obowiązuje żelazna reguła: kolumny wymienione w PRIMARY KEY muszą być pierwszymi kolumnami w ORDER BY. Czyli PRIMARY KEY to prefiks ORDER BY.

-- ✅ Poprawnie: PRIMARY KEY to pierwsze dwie kolumny ORDER BY
CREATE TABLE bets
(
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at, amount)    -- pełny porządek
PRIMARY KEY (user_id, created_at);         -- prefiks: pierwsze dwie

-- ❌ Błąd: PRIMARY KEY nie jest prefiksem
ORDER BY (user_id, created_at, amount)
PRIMARY KEY (created_at, user_id);   -- inna kolejność – ClickHouse wyrzuci błąd

-- ⚠️ Można w ogóle nie podawać PRIMARY KEY
-- Wtedy automatycznie jest równy ORDER BY
CREATE TABLE bets
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- PRIMARY KEY = (user_id, created_at)

Po co więc PRIMARY KEY, skoro to tylko prefiks? Otóż po to: indeks ClickHouse (sparse index) jest budowany tylko na kolumnach z PRIMARY KEY. Jeśli podasz PRIMARY KEY krótszy niż ORDER BY, zaoszczędzisz pamięć na indeksie, ale kolejność wierszy nadal będzie pełna (według wszystkich kolumn ORDER BY). Jest to przydatne, gdy kolumny wpływające na fizyczny porządek nie są potrzebne w indeksie.

Przykład: W ORDER BY (user_id, created_at, amount) – wiersze najpierw według user_id, wewnątrz według created_at, wewnątrz według amount. Ale nie potrzebujesz wyszukiwania po amount, więc PRIMARY KEY (user_id, created_at) jest krótszy, indeks mniejszy, a fizyczne ułożenie pomaga w kompresji (identyczne amount leżą obok siebie).

3. Sparse index: jeden wpis na 8192 wierszy (granula)

Indeks w ClickHouse nazywa się sparse index – „rzadki” lub „nieczęsty”. Nie przechowuje wskaźnika do każdego wiersza, jak drzewo B w PostgreSQL. Zamiast tego przechowuje jeden wpis na każde 8192 wierszy (ta grupa nazywa się granulą, granule).

Jak to wygląda wewnątrz:

Granula (wiersze 1–8192)	Wartość PRIMARY KEY dla pierwszego wiersza granuli
Granula 1	user_id=100, created_at=2025-01-01 00:00:01
Granula 2	user_id=100, created_at=2025-01-01 10:15:23
Granula 3	user_id=200, created_at=2025-01-01 00:00:05
...	...

Jak ClickHouse wyszukuje dane:

1. Masz zapytanie WHERE user_id = 100 AND created_at >= '2025-01-01'.
2. ClickHouse zagląda do sparse index i widzi granule.
3. Znajduje, że user_id=100 występuje w granulach 1, 2, być może 3 i dalej.
4. Ale nie wie dokładnie, gdzie wewnątrz granuli znajduje się szukany wiersz – ponieważ indeks wskazuje tylko na początek granuli.
5. Dlatego ClickHouse odczytuje w całości wszystkie granule, które mogą zawierać szukane wiersze (czasem więcej niż potrzeba – nazywa się to filtering by index).

Analogia: Sparse index jest jak spis treści w książce, gdzie każdy rozdział to 100 stron. Spis treści mówi: „Rozdział 3 zaczyna się na stronie 201”. Jeśli potrzebujesz konkretnego zdania na stronie 210, i tak będziesz czytać strony 201–300 w całości, bo nie znasz dokładnego miejsca. W PostgreSQL indeks B-drzewo podałby numer strony 210.

Dlaczego to jest szybkie w ClickHouse? Ponieważ:

• ClickHouse odczytuje kolumny wybiórczo – jeśli w WHERE potrzebny jest user_id, a w SELECT amount, czyta tylko te dwie kolumny.
• Dane wewnątrz granuli są skompresowane, a odczyt 8192 wierszy na raz jest bardzo wydajny (minimalny rozmiar ~64 KB, rozmiar granuli konfigurowalny przez index_granularity).
• Dla zapytań analitycznych (które czytają miliony wierszy) taka granulacja jest w porządku.

4. Reguła kardynalności: najpierw rzadkie, potem częste

Kardynalność (cardinality) – liczba unikalnych wartości w kolumnie. Na przykład:

• sport_id (rodzaj sportu: piłka nożna, hokej, tenis) – kardynalność 20 (niska)
• market_id (rynek zakładów: wynik, suma, handicap) – kardynalność 1000 (średnia)
• created_at (czas co do sekundy) – kardynalność miliardy (wysoka)

Złota zasada ClickHouse: w ORDER BY kolumny o niskiej kardynalności powinny być przed kolumnami o wysokiej kardynalności.

Dlaczego? Ponieważ sparse index będzie skuteczniej odcinał granule.

Zły klucz: ORDER BY (created_at, sport_id)

• Dane są posortowane najpierw według czasu. sport_id dla sąsiednich wierszy będzie skakać: piłka nożna, hokej, tenis, potem znowu piłka nożna...
• Zapytanie WHERE sport_id = 1 zmusza ClickHouse do odczytu wszystkich granul, ponieważ sport_id=1 jest rozrzucone po całej tabeli.

Dobry klucz: ORDER BY (sport_id, created_at)

• Najpierw wszystkie wiersze piłki nożnej (sport_id=1), posortowane według czasu. Potem wszystkie wiersze hokeja (sport_id=2) – zwarte.
• Zapytanie WHERE sport_id = 1 odcina na poziomie indeksu wszystkie granule niezwiązane z piłką nożną. ClickHouse odczyta tylko granule z sport_id=1.

Analogia: Wyobraź sobie, że sortujesz talię kart. Jeśli posortujesz najpierw według koloru (niska kardynalność – 4 wartości), a potem według wartości (wysoka – 13 wartości), to wszystkie piki będą leżeć razem. Jeśli odwrotnie – najpierw według wartości, to asy wszystkich kolorów będą rozrzucone po całej talii. Szukanie wszystkich pików stanie się trudne.

5. Przykład dla betting: jak wybrać właściwy ORDER BY

Porównajmy dwa warianty dla tabeli zakładów w firmie bukmacherskiej.

Wariant A (zły): ORDER BY (created_at, sport_id)

CREATE TABLE bets_bad
(
    sport_id    UInt8,       -- 1 = piłka nożna, 2 = hokej, 3 = tenis
    market_id   UInt32,      -- ID rynku zakładów
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, sport_id, market_id);

Jak wykonają się typowe zapytania:

-- Zapytanie: wszystkie zakłady na piłkę nożną z ostatniej godziny
SELECT sum(amount) FROM bets_bad
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN pokaże: odczyt prawie wszystkich granul, ponieważ sport_id=1 jest rozrzucone po całej skali czasu

Indeks (created_at, sport_id) słabo pomaga, ponieważ sport_id jest drugą kolumną. ClickHouse może użyć prefiksu created_at, ale potem filtrowanie sport_id będzie musiało odbyć się na poziomie granul, czytając nadmiarowe dane.

Wariant B (dobry): ORDER BY (sport_id, market_id, created_at)

CREATE TABLE bets_good
(
    sport_id    UInt8,
    market_id   UInt32,
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (sport_id, market_id, created_at);

Te same zapytania:

-- Zapytanie: zakłady na piłkę nożną z ostatniej godziny
SELECT sum(amount) FROM bets_good
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN pokaże: odczyt tylko granul, gdzie sport_id = 1, jest ich znacznie mniej

Dlaczego jest lepiej: ClickHouse dzięki indeksowi może od razu znaleźć bloki z sport_id = 1, a wewnątrz nich dane są już posortowane według market_id i created_at. Filtr czasowy created_at >= ... zostanie zastosowany na poziomie granul już wewnątrz tych bloków.

6. Równość vs zakres: co jest bardziej efektywne

Dla kolumn w ORDER BY istnieje hierarchia efektywności:

1. Równość (=) – najbardziej efektywne. Jeśli szukasz dokładnej wartości, ClickHouse może pominąć całe bloki granul.
2. Nierówność (>=, <=, BETWEEN) – mniej efektywne, ale może działać, jeśli jest to ostatnia kolumna w kluczu.
3. LIKE lub inne funkcje – często w ogóle nie korzystają z indeksu (chyba że przekształcają się w zakres).

Zasada: W ORDER BY kolumny z warunkami równości umieszczaj przed kolumnami z zakresami.

Przykład dla klucza (user_id, created_at):

-- ✅ Świetnie: user_id = równość (pierwsza kolumna), created_at >= zakres (druga)
SELECT * FROM bets WHERE user_id = 123 AND created_at >= '2025-06-01';

-- ❌ Źle: created_at zakres (pierwsza kolumna), user_id = równość (druga)
-- Indeks może odciąć tylko po created_at, ale user_id trzeba będzie filtrować wewnątrz granul
SELECT * FROM bets WHERE created_at >= '2025-06-01' AND user_id = 123;

Dlaczego tak? Ponieważ dane są fizycznie posortowane według (user_id, created_at). Wszystkie rekordy jednego user_id leżą zwarte, a wewnątrz nich według czasu. Jeśli szukasz zakresu czasu, jest to łatwe. Ale jeśli szukasz najpierw według czasu, to rekordy jednego user_id są rozrzucone po całej tabeli – nie można ich odciąć indeksem.

Analogia: Wyobraź sobie książkę telefoniczną posortowaną najpierw według nazwiska, potem według imienia. Szukanie „wszystkich Kowalskich” jest łatwe (nazwisko – pierwsza kolumna). Szukanie „wszystkich urodzonych po 1990 roku” – trzeba przeczytać całą książkę.

7. Złożony klucz z UInt8+UInt32+DateTime vs sam DateTime

Czasem wydaje się: „A czemu by nie zrobić ORDER BY created_at – prosto i jasno?” Przeanalizujmy na przykładzie z gamingu.

Zapytania, które są naprawdę potrzebne dashboardowi:

• Zakłady konkretnego użytkownika z ostatniego tygodnia: WHERE user_id = 123 AND created_at >= today() - 7
• Statystyki według rodzaju sportu z danego dnia: WHERE sport_id = 1 AND created_at = yesterday()
• Agregacja według rynku z ostatniej godziny: WHERE market_id = 100 AND created_at >= now() - 1 hour

Wariant 1: ORDER BY (created_at)

CREATE TABLE bets_simple
(
    user_id     UInt64,
    sport_id    UInt8,
    market_id   UInt32,
    created_at  DateTime
)
ORDER BY created_at;

Problemy:

• Zapytania po user_id będą wolne – trzeba skanować wszystko.
• Zapytania po sport_id – ta sama historia.

Wariant 2: ORDER BY (user_id, sport_id, market_id, created_at)

CREATE TABLE bets_composite
(
    user_id     UInt64,
    sport_id    UInt8,
    market_id   UInt32,
    created_at  DateTime
)
ORDER BY (user_id, sport_id, market_id, created_at);

Teraz:

• Zapytanie WHERE user_id = 123 AND created_at >= ... – świetnie (używa prefiksu user_id).
• Zapytanie WHERE sport_id = 1 AND created_at = ... – źle, ponieważ sport_id nie jest pierwszą kolumną. ClickHouse nie może odciąć po sport_id w indeksie.

Kompromis: Wybierz najczęstszy wzorzec filtrowania i umieść jego kolumny na początku ORDER BY. Jeśli najczęściej szukasz po user_id – umieść user_id jako pierwsze. Jeśli częściej po brand_id – to brand_id jako pierwsze.

Reguła kciuka: W ORDER BY powinno być minimum 2–4 kolumny. Jedna kolumna rzadko jest optymalna.

8. Jak sprawdzić efektywność klucza przez EXPLAIN

ClickHouse oferuje potężne narzędzia do analizy wykorzystania indeksu.

EXPLAIN indexes = 1

-- Włączamy wyświetlanie informacji o użyciu indeksu
EXPLAIN indexes = 1
SELECT sum(amount) FROM bets
WHERE user_id = 123 AND created_at >= '2025-06-01';

Wynik pokaże coś w rodzaju:

Expression
  ...
  ReadFromMergeTree
    Indexes:
      PrimaryKey
        Condition: (user_id = 123) AND (created_at >= '2025-06-01')
        Used keys: (user_id, created_at)
        Granules: 15 / 1280

Co oznaczają liczby: 15 / 1280 – z 1280 granul w tabeli odczytano tylko 15. Świetny wynik. Jeśli będzie 1200 / 1280 – indeks prawie nie pomógł.

system.query_log

Tabela systemowa query_log przechowuje statystyki dla każdego zapytania. Najbardziej przydatne kolumny do analizy indeksu:

-- Znajdujemy długie zapytania i sprawdzamy, ile wierszy czytały
SELECT 
    query,
    read_rows,          -- ile wierszy odczytano
    result_rows,        -- ile wierszy zwrócono
    read_rows / result_rows AS efficiency,  -- im bliżej 1, tym lepiej
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish' 
  AND query LIKE '%bets%'
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC;

Jak interpretować:

• read_rows / result_rows ≈ 1..10 – indeks działa dobrze
• read_rows / result_rows > 1000 – czytasz tysiące wierszy dla jednego – zły indeks
• read_rows blisko całkowitej liczby wierszy w tabeli – full scan

columns_read z system.query_log

SELECT 
    query,
    read_rows,
    written_rows,
    result_rows,
    columns_read,      -- lista kolumn, które zostały odczytane
    columns_written
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
LIMIT 10;

Jeśli w columns_read widzisz kolumny, których nie ma w SELECT i WHERE – ClickHouse czyta nadmiarowe dane (prawdopodobnie z powodu złego ORDER BY).

9. Wzorce dla branży gamingowej

Wzorzec 1: Zapytania według konkretnego gracza

Jeśli najczęstsze zapytanie to „pokaż historię zakładów użytkownika”, klucz (user_id, created_at) jest idealny.

CREATE TABLE bets_by_user
(
    user_id     UInt64,
    created_at  DateTime,
    sport_id    UInt8,
    amount      Decimal(18,2)
)
ORDER BY (user_id, created_at);   -- Wszystkie zakłady user_id zwarte i według czasu

Zapytanie WHERE user_id = 123 AND created_at BETWEEN ... odczyta tylko granule tego użytkownika, a jest ich mało.

Wzorzec 2: Platforma multi-brand

Masz kilka marek (casino_A, casino_B), a zapytania prawie zawsze zawierają brand_id. Wtedy:

CREATE TABLE bets_multi_brand
(
    brand_id    UInt8,        -- Niska kardynalność (5 marek)
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ORDER BY (brand_id, created_at);

Zapytanie WHERE brand_id = 1 AND created_at >= ... odcina na poziomie indeksu wszystkie dane innych marek.

Wzorzec 3: Dashboard według rodzajów sportu

Jeśli raporty grupują według sport_id (piłka nożna, hokej) i filtrują według czasu:

CREATE TABLE bets_by_sport
(
    sport_id    UInt8,
    created_at  DateTime,
    user_id     UInt64,
    amount      Decimal(18,2)
)
ORDER BY (sport_id, created_at);

Nie istnieje uniwersalny klucz. Musisz wybrać jeden lub dwa najczęstsze wzorce zapytań i zoptymalizować pod nie. Pozostałe zapytania będą wolniejsze – to nieunikniony kompromis.

10. Zmiana ORDER BY po utworzeniu tabeli – niemożliwa

To najsmutniejsza, ale ważna wiedza. Nie możesz zmienić ORDER BY ani PRIMARY KEY istniejącej tabeli poleceniami typu ALTER.

-- ❌ Nic takiego nie istnieje
ALTER TABLE bets MODIFY ORDER BY (new_column, created_at);   -- BŁĄD!

Dlaczego? Ponieważ fizyczny porządek wierszy jest już określony. Aby go zmienić, trzeba przebudować tabelę.

Co zrobić, jeśli zorientujesz się, że popełniłeś błąd?

Sposób 1: Utworzyć nową tabelę, przenieść dane, zmienić nazwę

-- 1. Tworzymy nową tabelę z poprawnym ORDER BY
CREATE TABLE bets_new
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- nowy klucz

-- 2. Przenosimy dane (można asynchronicznie, jeśli tabela jest duża)
INSERT INTO bets_new SELECT * FROM bets;

-- 3. Zamieniamy tabele miejscami (operacja atomowa)
RENAME TABLE bets TO bets_old, bets_new TO bets;

-- 4. Sprawdzamy, czy wszystko działa, i usuwamy starą
DROP TABLE bets_old;

Sposób 2: Użyć materializowanego widoku (jeśli można przechowywać dane w dwóch porządkach jednocześnie)

-- Zostawiamy starą tabelę dla jednych zapytań
-- Tworzymy materializowany widok z innym ORDER BY dla innych zapytań
CREATE MATERIALIZED VIEW bets_by_sport_mv
ENGINE = MergeTree() ORDER BY (sport_id, created_at)
AS SELECT * FROM bets;   -- dane będą duplikowane

Sposób 3: Pogodzić się i żyć ze złym kluczem (czasem taniej jest zwiększyć zasoby niż przenosić terabajty)

Rada: Przed utworzeniem tabeli z dużymi danymi (miliardy wierszy) zawsze testuj ORDER BY na próbce. Utwórz kopię z 10 milionami wierszy, uruchom EXPLAIN indexes=1, sprawdź różne zapytania. To zaoszczędzi ci tygodni bólu później.

Co dalej

Teraz rozumiesz, że ORDER BY w ClickHouse to nie tylko sortowanie, ale strategiczny indeks. Kolejne tematy:

• Jak skonfigurować index_granularity – zmiana rozmiaru granuli z 8192 na inną wartość (prawie nigdy niepotrzebna).
• Partycjonowanie vs ORDER BY – kiedy partycje pomagają, a kiedy indeks.
• Skip indeksy (bloom filter indexes) – indeksy wtórne dla kolumn nie wchodzących w skład ORDER BY.
• Analiza wolnych zapytań przez system.query_log – głębokie profilowanie.

Podsumowanie: Formuła idealnego ORDER BY w ClickHouse: kolumny o niskiej kardynalności i z warunkami równości – na początek; potem kolumny o wysokiej kardynalności i zakresami. Nie próbuj ogarnąć nieogarnionego – wybierz najczęstsze zapytania i zignoruj resztę. I nigdy nie zapominaj o EXPLAIN indexes=1 – najlepszym przyjacielu dewelopera ClickHouse.

---

← Poprzedni: Partycyjonowanie w ClickHouse: Jak zarządzać danymi na poziomie folderów
→ Następny: TTL w ClickHouse: Automatyczne zarządzanie cyklem życia danych

— Editorial Team