ORDER BY a PRIMARY KEY v ClickHouse: výběr indexu

ORDER BY a PRIMARY KEY v ClickHouse: jak nešlápnout vedle s indexem

1. Proč je ORDER BY to nejdůležitější, co v tabulce uvedeš

V běžných databázích (PostgreSQL, MySQL) existují dva pojmy: clusterovaný index (primární klíč, který fyzicky řadí data na disku) a sekundární indexy (samostatné B-stromy). Můžeš index kdykoli přidat nebo odebrat, aniž bys tabulku přetvářel.

V ClickHouse je to jinak. Zde existuje pouze jeden fyzický pořádek dat na disku – ten, který jsi uvedl v ORDER BY. A změnit ho bez přetvoření tabulky – nelze. Vůbec. Vůbec. Je to jako zalít beton a zjistit, že jsi dal výztuž špatně. Předělávat – jen všechno vylámat a znovu.

Proč tak přísně? Protože ClickHouse ukládá data ve sloupcovém formátu, hustě komprimovaná. Aby se změnilo pořadí řádků, musely by se přepsat všechny sloupce znovu. Nikdo nechce čekat hodiny nebo dny na reorganizaci terabajtové tabulky.

Proto je volba ORDER BY strategické rozhodnutí. Musíš předpovědět, které dotazy budou nejčastější, a navrhnout klíč tak, aby se prováděly bleskově. Chyba tě bude draho stát.

Analogii ze života: Představ si, že jsi knihovník a potřebuješ seřadit všechny knihy na policích v určitém pořadí. Můžeš zvolit pořadí např. podle žánru a uvnitř podle příjmení autora. Pak můžeš knihy rychle hledat, pokud hledáš podle těchto kritérií. Ale pokud se rozhodneš, že by bylo pohodlnější pořadí podle data vydání – budeš muset všechny knihy z polic přerovnat znovu. Hodiny.

2. PRIMARY KEY ⊆ ORDER BY – vzácné pravidlo

V ClickHouse máš dva parametry:

• ORDER BY – určuje fyzické pořadí řádků na disku (povinný).
• PRIMARY KEY – určuje index (volitelný).

A platí železné pravidlo: sloupce uvedené v PRIMARY KEY musí být prvními sloupci v ORDER BY. To znamená, že PRIMARY KEY je prefixem ORDER BY.

-- ✅ Správně: PRIMARY KEY jsou první dva sloupce ORDER BY
CREATE TABLE bets
(
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at, amount)    -- úplné pořadí
PRIMARY KEY (user_id, created_at);         -- prefix: první dva

-- ❌ Chyba: PRIMARY KEY není prefixem
ORDER BY (user_id, created_at, amount)
PRIMARY KEY (created_at, user_id);   -- jiné pořadí – ClickHouse vyhodí chybu

-- ⚠️ Lze vůbec neuvažovat PRIMARY KEY
-- Pak se automaticky shoduje s 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)

K čemu je tedy PRIMARY KEY, když je to jen prefix? K tomu: index ClickHouse (řídký index) se vytváří pouze podle sloupců z PRIMARY KEY. Pokud uvedeš PRIMARY KEY kratší než ORDER BY, ušetříš paměť na indexu, ale pořadí řádků bude stále úplné (podle všech sloupců ORDER BY). To je užitečné, když sloupce ovlivňující fyzické pořadí nejsou v indexu potřeba.

Příklad: V ORDER BY (user_id, created_at, amount) – řádky nejprve podle user_id, uvnitř podle created_at, uvnitř podle amount. Ale nepotřebuješ vyhledávání podle amount, proto PRIMARY KEY (user_id, created_at) je kratší, index menší, a fyzické uspořádání pomáhá komprimovat data (stejné amount leží vedle sebe).

3. Řídký index: jeden záznam na 8192 řádků (granule)

Index v ClickHouse se nazývá řídký index – „sparse index“. Neukládá ukazatel na každý řádek jako B-strom v PostgreSQL. Místo toho ukládá jeden záznam na každých 8192 řádků (tato skupina se nazývá granule).

Jak to vypadá uvnitř:

Granule (řádky 1–8192)	Hodnota PRIMARY KEY pro první řádek granule
Granule 1	user_id=100, created_at=2025-01-01 00:00:01
Granule 2	user_id=100, created_at=2025-01-01 10:15:23
Granule 3	user_id=200, created_at=2025-01-01 00:00:05
...	...

Jak ClickHouse hledá data:

1. Máš dotaz WHERE user_id = 100 AND created_at >= '2025-01-01'.
2. ClickHouse se podívá do řídkého indexu a uvidí granule.
3. Zjistí, že user_id=100 se vyskytuje v granulích 1, 2, možná 3 a dále.
4. Ale neví přesně, kde uvnitř granule je hledaný řádek – protože index ukazuje pouze na začátek granule.
5. Proto ClickHouse přečte celé všechny granule, které mohou obsahovat hledané řádky (někdy více, než je třeba – tomu se říká filtrování podle indexu).

Analogii: Řídký index je jako obsah v knize, kde každá kapitola má 100 stran. Obsah říká: „Kapitola 3 začíná na straně 201.“ Pokud potřebuješ konkrétní frázi na straně 210, stejně budeš číst strany 201–300 celé, protože neznáš přesné místo. V PostgreSQL by ti index B-strom dal číslo strany 210.

Proč je to v ClickHouse rychlé? Protože:

• ClickHouse čte sloupce výběrově – pokud je v WHERE potřeba user_id a v SELECT amount, přečte jen tyto dva sloupce.
• Data uvnitř granule jsou komprimovaná a čtení 8192 řádků najednou je velmi efektivní (minimální objem ~64 KB, velikost granule se nastavuje pomocí index_granularity).
• Pro analytické dotazy (které čtou miliony řádků) je taková granularita v pořádku.

4. Pravidlo kardinality: nejdříve řídké, pak časté

Kardinalita – počet unikátních hodnot ve sloupci. Například:

• sport_id (druh sportu: fotbal, hokej, tenis) – kardinalita 20 (nízká)
• market_id (trh sázek: výsledek, total, handicap) – kardinalita 1000 (střední)
• created_at (čas do sekundy) – kardinalita miliardy (vysoká)

Zlaté pravidlo ClickHouse: v ORDER BY by sloupce s nízkou kardinalitou měly být před sloupci s vysokou kardinalitou.

Proč? Protože řídký index bude efektivněji odřezávat granule.

Špatný klíč: ORDER BY (created_at, sport_id)

• Data jsou seřazena nejprve podle času. sport_id u sousedních řádků bude skákat: fotbal, hokej, tenis, pak zase fotbal...
• Dotaz WHERE sport_id = 1 donutí ClickHouse číst všechny granule, protože sport_id=1 je rozházeno po celé tabulce.

Dobrý klíč: ORDER BY (sport_id, created_at)

• Nejprve všechny řádky fotbalu (sport_id=1), seřazené podle času. Pak všechny řádky hokeje (sport_id=2) – kompaktně.
• Dotaz WHERE sport_id = 1 odřízne všechny granule, které nepatří k fotbalu, na úrovni indexu. ClickHouse přečte jen granule s sport_id=1.

Analogii: Představ si, že třídíš balíček karet. Pokud seřadíš nejprve podle barvy (nízká kardinalita – 4 hodnoty) a pak podle hodnoty (vysoká – 13 hodnot), všechny piky budou pohromadě. Pokud naopak – nejprve hodnota, pak esa všech barev budou rozházena po celém balíčku. Hledat všechny piky bude obtížné.

5. Příklad pro betting: jak vybrat správný ORDER BY

Porovnejme dvě varianty pro tabulku sázek v sázkové kanceláři.

Varianta A (špatná): ORDER BY (created_at, sport_id)

CREATE TABLE bets_bad
(
    sport_id    UInt8,       -- 1 = fotbal, 2 = hokej, 3 = tenis
    market_id   UInt32,      -- ID trhu sázek
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, sport_id, market_id);

Jak se provedou typické dotazy:

-- Dotaz: všechny sázky na fotbal za poslední hodinu
SELECT sum(amount) FROM bets_bad
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN ukáže: čtení téměř všech granulí, protože sport_id=1 je rozházeno po celé časové ose

Index (created_at, sport_id) špatně pomáhá, protože sport_id je druhý sloupec. ClickHouse může použít prefix created_at, ale pak bude muset filtrovat sport_id na úrovni granulí a číst navíc.

Varianta B (dobrá): 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);

Stejné dotazy:

-- Dotaz: sázky na fotbal za poslední hodinu
SELECT sum(amount) FROM bets_good
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN ukáže: čtení pouze granulí, kde sport_id = 1, je jich výrazně méně

Proč je to lepší: ClickHouse podle indexu může okamžitě najít bloky s sport_id = 1 a uvnitř nich už leží data seřazená podle market_id a created_at. Časový filtr created_at >= ... se aplikuje na úrovni granulí už uvnitř těchto bloků.

6. Rovnost vs. rozsah: co je efektivnější

Pro sloupce v ORDER BY existuje hierarchie efektivity:

1. Rovnost (=) – nejefektivnější. Pokud hledáš přesnou hodnotu, ClickHouse může přeskočit celé bloky granulí.
2. Nerovnost (>=, <=, BETWEEN) – méně efektivní, ale může fungovat, pokud je to poslední sloupec v klíči.
3. LIKE nebo jiné funkce – často index vůbec nepoužijí (jen pokud se nepřevedou na rozsah).

Pravidlo: V ORDER BY dávej sloupce s podmínkami rovnosti před sloupce s rozsahy.

Příklad pro klíč (user_id, created_at):

-- ✅ Výborně: user_id = rovnost (první sloupec), created_at >= rozsah (druhý)
SELECT * FROM bets WHERE user_id = 123 AND created_at >= '2025-06-01';

-- ❌ Špatně: created_at rozsah (první sloupec), user_id = rovnost (druhý)
-- Index může odříznout jen podle created_at, ale user_id se bude muset filtrovat uvnitř granulí
SELECT * FROM bets WHERE created_at >= '2025-06-01' AND user_id = 123;

Proč tomu tak je? Protože data jsou fyzicky seřazena podle (user_id, created_at). Všechny záznamy jednoho user_id leží kompaktně a uvnitř nich podle času. Pokud hledáš časový rozsah, je to snadné. Ale pokud hledáš nejprve podle času, záznamy jednoho user_id jsou rozházeny po celé tabulce – nelze je odříznout indexem.

Analogii: Představ si telefonní seznam seřazený nejprve podle příjmení, pak podle jména. Hledat „všechny Nováky“ je snadné (příjmení – první sloupec). Hledat „všechny narozené po roce 1990“ – budeš muset přečíst celý seznam.

7. Složený klíč z UInt8+UInt32+DateTime vs. jen DateTime

Někdy se zdá: „A proč neudělat ORDER BY created_at – jednoduché a přehledné?“ Rozeberme to na příkladu z gamlingu.

Dotazy, které dashboard skutečně potřebuje:

• Sázky konkrétního uživatele za poslední týden: WHERE user_id = 123 AND created_at >= today() - 7
• Statistika podle druhu sportu za den: WHERE sport_id = 1 AND created_at = yesterday()
• Agregace podle trhu za hodinu: WHERE market_id = 100 AND created_at >= now() - 1 hour

Varianta 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;

Problémy:

• Dotazy podle user_id budou pomalé – bude se muset skenovat vše.
• Dotazy podle sport_id – stejný příběh.

Varianta 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);

Nyní:

• Dotaz WHERE user_id = 123 AND created_at >= ... – výborně (používá prefix user_id).
• Dotaz WHERE sport_id = 1 AND created_at = ... – špatně, protože sport_id není první sloupec. ClickHouse nemůže odříznout podle sport_id v indexu.

Kompromis: Vyber nejčastější vzor filtrování a dej jeho sloupce na začátek ORDER BY. Pokud nejčastěji hledáš podle user_id – dej user_id první. Pokud častěji podle brand_id – dej jeho první.

Pravidlo palce: V ORDER BY by měly být alespoň 2–4 sloupce. Jeden sloupec je málokdy optimální.

8. Jak zkontrolovat efektivitu klíče pomocí EXPLAIN

ClickHouse poskytuje výkonné nástroje pro analýzu toho, jak se index používá.

EXPLAIN indexes = 1

-- Zapneme výpis informací o použití indexu
EXPLAIN indexes = 1
SELECT sum(amount) FROM bets
WHERE user_id = 123 AND created_at >= '2025-06-01';

Výsledek ukáže něco jako:

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

Co znamenají čísla: 15 / 1280 – z 1280 granulí v tabulce bylo přečteno pouze 15. Skvělý výsledek. Pokud bude 1200 / 1280 – index téměř nepomohl.

system.query_log

Systémová tabulka query_log uchovává statistiky pro každý dotaz. Nejužitečnější sloupce pro analýzu indexu:

-- Najdeme pomalé dotazy a podíváme se, kolik řádků četly
SELECT 
    query,
    read_rows,          -- kolik řádků bylo přečteno
    result_rows,        -- kolik řádků bylo vráceno
    read_rows / result_rows AS efficiency,  -- čím blíže 1, tím lépe
    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 interpretovat:

• read_rows / result_rows ≈ 1..10 – index funguje dobře
• read_rows / result_rows > 1000 – čteš tisíce řádků kvůli jednomu – špatný index
• read_rows blízko celkovému počtu řádků v tabulce – full scan

columns_read z system.query_log

SELECT 
    query,
    read_rows,
    written_rows,
    result_rows,
    columns_read,      -- seznam sloupců, které byly přečteny
    columns_written
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
LIMIT 10;

Pokud v columns_read vidíš sloupce, které nejsou v SELECT a WHERE – ClickHouse čte zbytečně (možná kvůli špatnému ORDER BY).

9. Vzory pro gamlingový průmysl

Vzor 1: Dotazy podle konkrétního hráče

Pokud je nejčastější dotaz „zobrazit historii sázek uživatele“, klíč (user_id, created_at) je ideální.

CREATE TABLE bets_by_user
(
    user_id     UInt64,
    created_at  DateTime,
    sport_id    UInt8,
    amount      Decimal(18,2)
)
ORDER BY (user_id, created_at);   -- Všechny sázky user_id kompaktně a podle času

Dotaz WHERE user_id = 123 AND created_at BETWEEN ... přečte jen granule tohoto uživatele, a těch je málo.

Vzor 2: Multi-brand platforma

Máš několik značek (casino_A, casino_B) a dotazy téměř vždy obsahují brand_id. Pak:

CREATE TABLE bets_multi_brand
(
    brand_id    UInt8,        -- Nízká kardinalita (5 značek)
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ORDER BY (brand_id, created_at);

Dotaz WHERE brand_id = 1 AND created_at >= ... odřízne všechna data ostatních značek na úrovni indexu.

Vzor 3: Dashboard podle druhů sportu

Pokud reporty seskupují podle sport_id (fotbal, hokej) a filtrují podle času:

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

Univerzální klíč neexistuje. Musíš vybrat jeden nebo dva nejčastější vzory dotazů a optimalizovat pro ně. Ostatní dotazy budou pomalejší – to je nevyhnutelný kompromis.

10. Změna ORDER BY po vytvoření tabulky – nelze

To je nejsmutnější, ale důležité poznání. Nelze změnit ORDER BY nebo PRIMARY KEY u existující tabulky příkazy jako ALTER.

-- ❌ Nic takového neexistuje
ALTER TABLE bets MODIFY ORDER BY (new_column, created_at);   -- CHYBA!

Proč? Protože fyzické pořadí řádků je již určeno. Aby se změnilo, je třeba tabulku přetvořit.

Co dělat, když zjistíš, že jsi udělal chybu?

Způsob 1: Vytvořit novou tabulku, přelít data, přejmenovat

-- 1. Vytvoříme novou tabulku se správným ORDER BY
CREATE TABLE bets_new
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- nový klíč

-- 2. Přelijeme data (lze asynchronně, pokud je tabulka velká)
INSERT INTO bets_new SELECT * FROM bets;

-- 3. Prohodíme tabulky (atomická operace)
RENAME TABLE bets TO bets_old, bets_new TO bets;

-- 4. Zkontrolujeme, že vše funguje, a smažeme starou
DROP TABLE bets_old;

Způsob 2: Použít materializovaný pohled (pokud lze data ukládat ve dvou pořadích současně)

-- Ponecháme starou tabulku pro některé dotazy
-- Vytvoříme materializovaný pohled s jiným ORDER BY pro jiné dotazy
CREATE MATERIALIZED VIEW bets_by_sport_mv
ENGINE = MergeTree() ORDER BY (sport_id, created_at)
AS SELECT * FROM bets;   -- data se budou duplikovat

Způsob 3: Smířit se a žít se špatným klíčem (někdy je levnější navýšit zdroje než přelévat terabajty)

Rada: Před vytvořením tabulky s velkými daty (miliardy řádků) vždy testuj ORDER BY na vzorku. Vytvoř kopii s 10 miliony řádků, spusť EXPLAIN indexes=1, otestuj různé dotazy. Ušetří ti to týdny bolesti později.

Co dál

Teď už chápeš, že ORDER BY v ClickHouse není jen řazení, ale strategický index. Další témata:

• Jak nastavit index_granularity – změnit velikost granule z 8192 na jinou hodnotu (téměř nikdy není potřeba).
• Partitioning vs ORDER BY – kdy pomáhají oddíly a kdy index.
• Skip indexy (bloom filter indexes) – sekundární indexy pro sloupce, které nejsou v ORDER BY.
• Analýza pomalých dotazů pomocí system.query_log – hluboké profilování.

Shrnutí: Vzorec ideálního ORDER BY v ClickHouse: sloupce s nízkou kardinalitou a s podmínkami rovnosti – na začátek; pak sloupce s vysokou kardinalitou a rozsahy. Nesnaž se obsáhnout neobsáhlé – vyber nejčastější dotazy a na ostatní zapomeň. A nikdy nezapomínej na EXPLAIN indexes=1 – nejlepší přítel ClickHouse vývojáře.

---

← Předchozí: Partitionování v ClickHouse: Jak spravovat data na úrovni složek
→ Další: TTL v ClickHouse: Automatické řízení životního cyklu dat

— Editorial Team