ORDER BY und PRIMARY KEY in ClickHouse: Indexauswahl

ORDER BY und PRIMARY KEY in ClickHouse: So setzen Sie den Index richtig

1. Warum ORDER BY das Wichtigste ist, das Sie in einer Tabelle angeben

In konventionellen Datenbanken (PostgreSQL, MySQL) gibt es zwei Konzepte: einen Clustered Index (der Primärschlüssel, der die Daten physisch auf der Festplatte ordnet) und sekundäre Indizes (separate B-Bäume). Sie können jederzeit einen Index hinzufügen oder löschen, ohne die Tabelle neu zu erstellen.

In ClickHouse ist das anders. Hier gibt es nur eine physische Reihenfolge der Daten auf der Festplatte – die, die Sie in ORDER BY angegeben haben. Und Sie können sie nicht ändern, ohne die Tabelle neu zu erstellen. Gar nicht. Punkt. Es ist, als ob Sie Beton gießen und feststellen, dass Sie die Bewehrung falsch platziert haben. Sie müssen alles herausbrechen und von vorne beginnen.

Warum so streng? Weil ClickHouse Daten im Spaltenformat speichert, stark komprimiert. Um die Zeilenreihenfolge zu ändern, müssten Sie alle Spalten von Grund auf neu schreiben. Niemand möchte Stunden oder Tage warten, um eine terabytegroße Tabelle neu zu organisieren.

Daher ist die Wahl von ORDER BY eine strategische Entscheidung. Sie müssen vorhersagen, welche Abfragen am häufigsten vorkommen, und den Schlüssel so gestalten, dass sie blitzschnell ausgeführt werden. Ein Fehler wird teuer.

Analogie aus dem echten Leben: Stellen Sie sich vor, Sie sind Bibliothekar und müssen alle Bücher in den Regalen in einer bestimmten Reihenfolge anordnen. Sie können eine Reihenfolge wählen, z. B. nach Genre und darin nach dem Nachnamen des Autors. Dann können Sie Bücher schnell finden, wenn Sie nach diesen Kriterien suchen. Aber wenn Sie entscheiden, dass eine Sortierung nach Erscheinungsdatum praktischer wäre, müssen Sie alle Bücher wieder aus den Regalen räumen. Stundenlang.

2. PRIMARY KEY ⊆ ORDER BY – Eine seltene Regel

In ClickHouse haben Sie zwei Parameter:

• ORDER BY – definiert die physische Reihenfolge der Zeilen auf der Festplatte (obligatorisch).
• PRIMARY KEY – definiert den Index (optional).

Und es gibt eine harte Regel: Die in PRIMARY KEY aufgeführten Spalten müssen die ersten Spalten in ORDER BY sein. Mit anderen Worten: PRIMARY KEY ist ein Präfix von ORDER BY.

-- ✅ Korrekt: PRIMARY KEY sind die ersten beiden Spalten von ORDER BY
CREATE TABLE bets
(
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at, amount)    -- vollständige Reihenfolge
PRIMARY KEY (user_id, created_at);         -- Präfix: die ersten beiden

-- ❌ Fehler: PRIMARY KEY ist kein Präfix
ORDER BY (user_id, created_at, amount)
PRIMARY KEY (created_at, user_id);   -- andere Reihenfolge – ClickHouse gibt einen Fehler aus

-- ⚠️ Sie können PRIMARY KEY ganz weglassen
-- Dann entspricht er automatisch 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)

Warum brauchen Sie dann PRIMARY KEY, wenn es nur ein Präfix ist? Hier ist der Grund: Der ClickHouse-Index (sparse index) wird nur auf den Spalten in PRIMARY KEY aufgebaut. Wenn Sie einen kürzeren PRIMARY KEY als ORDER BY angeben, sparen Sie Speicher für den Index, aber die Zeilenreihenfolge bleibt vollständig (nach allen ORDER BY-Spalten). Dies ist nützlich, wenn Spalten, die die physische Reihenfolge beeinflussen, nicht im Index benötigt werden.

Beispiel: In ORDER BY (user_id, created_at, amount) – Zeilen sind zuerst nach user_id, dann nach created_at, dann nach amount sortiert. Aber Sie müssen nicht nach amount suchen, also ist PRIMARY KEY (user_id, created_at) kürzer, der Index ist kleiner, und das physische Layout hilft bei der Komprimierung (identische Beträge werden zusammen gespeichert).

3. Sparse Index: Ein Eintrag pro 8192 Zeilen (Granula)

Der Index in ClickHouse wird als sparse index bezeichnet. Er speichert keinen Zeiger auf jede Zeile wie ein B-Baum in PostgreSQL. Stattdessen speichert er einen Eintrag für jeweils 8192 Zeilen (diese Gruppe wird als Granula bezeichnet).

Wie es intern aussieht:

Granula (Zeilen 1–8192)	PRIMARY KEY-Wert für die erste Zeile der Granula
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
...	...

Wie ClickHouse nach Daten sucht:

1. Sie haben eine Abfrage WHERE user_id = 100 AND created_at >= '2025-01-01'.
2. ClickHouse betrachtet den sparse index und sieht die Granulae.
3. Es stellt fest, dass user_id=100 in den Granulae 1, 2, möglicherweise 3 und darüber hinaus vorkommt.
4. Aber es weiß nicht genau, wo innerhalb einer Granula die gewünschte Zeile ist – weil der Index nur auf den Beginn der Granula verweist.
5. Daher liest ClickHouse alle Granulae, die die benötigten Zeilen enthalten könnten (manchmal mehr als nötig – dies wird als Filterung nach Index bezeichnet).

Analogie: Ein sparse index ist wie ein Inhaltsverzeichnis in einem Buch, in dem jedes Kapitel 100 Seiten umfasst. Das Inhaltsverzeichnis sagt: „Kapitel 3 beginnt auf Seite 201.“ Wenn Sie einen bestimmten Satz auf Seite 210 benötigen, müssen Sie dennoch die Seiten 201–300 vollständig lesen, weil Sie die genaue Position nicht kennen. In PostgreSQL würde Ihnen ein B-Baum-Index die Seite 210 liefern.

Warum ist das in ClickHouse schnell? Weil:

• ClickHouse Spalten selektiv liest – wenn WHERE user_id benötigt und SELECT amount, liest es nur diese beiden Spalten.
• Daten innerhalb einer Granula sind komprimiert, und das Lesen von 8192 Zeilen auf einmal ist sehr effizient (Mindestvolumen ~64 KB, Granulagröße ist über index_granularity konfigurierbar).
• Für analytische Abfragen (die Millionen von Zeilen lesen) ist eine solche Granularität in Ordnung.

4. Kardinalitätsregel: Niedrig zuerst, dann hoch

Kardinalität ist die Anzahl der eindeutigen Werte in einer Spalte. Zum Beispiel:

• sport_id (Sportart: Fußball, Hockey, Tennis) – Kardinalität 20 (niedrig)
• market_id (Wettmarkt: Ergebnis, Gesamt, Handicap) – Kardinalität 1000 (mittel)
• created_at (Zeit bis zur Sekunde) – Kardinalität Milliarden (hoch)

Die goldene Regel von ClickHouse: In ORDER BY sollten Spalten mit niedriger Kardinalität vor Spalten mit hoher Kardinalität kommen.

Warum? Weil der sparse index dann effektiver Granulae ausschließen kann.

Schlechter Schlüssel: ORDER BY (created_at, sport_id)

• Daten sind zuerst nach Zeit sortiert. sport_id für benachbarte Zeilen springt hin und her: Fußball, Hockey, Tennis, dann wieder Fußball...
• Abfrage WHERE sport_id = 1 zwingt ClickHouse, alle Granulae zu lesen, weil sport_id=1 über die gesamte Tabelle verstreut ist.

Guter Schlüssel: ORDER BY (sport_id, created_at)

• Zuerst alle Fußballzeilen (sport_id=1) nach Zeit sortiert. Dann alle Hockeyzeilen (sport_id=2) – kompakt.
• Abfrage WHERE sport_id = 1 schließt auf Indexebene alle Granulae aus, die nicht Fußball betreffen. ClickHouse liest nur Granulae mit sport_id=1.

Analogie: Stellen Sie sich vor, Sie sortieren ein Kartenspiel. Wenn Sie zuerst nach Farbe (niedrige Kardinalität – 4 Werte) und dann nach Rang (hoch – 13 Werte) sortieren, sind alle Pik zusammen. Wenn Sie es andersherum machen – zuerst nach Rang, dann sind die Asse aller Farben im gesamten Deck verstreut. Alle Pik zu finden wird schwierig.

5. Beispiel für Wetten: So wählen Sie das richtige ORDER BY

Vergleichen wir zwei Optionen für eine Wett-Tabelle in einem Buchmacher.

Option A (schlecht): ORDER BY (created_at, sport_id)

CREATE TABLE bets_bad
(
    sport_id    UInt8,       -- 1 = Fußball, 2 = Hockey, 3 = Tennis
    market_id   UInt32,      -- Wettmarkt-ID
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, sport_id, market_id);

Wie typische Abfragen abschneiden:

-- Abfrage: alle Fußballwetten der letzten Stunde
SELECT sum(amount) FROM bets_bad
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN zeigt: fast alle Granulae werden gelesen, weil sport_id=1 über die Zeitachse verstreut ist

Der Index (created_at, sport_id) hilft wenig, weil sport_id die zweite Spalte ist. ClickHouse kann das Präfix created_at nutzen, aber dann muss die Filterung nach sport_id auf Granula-Ebene erfolgen, wobei zusätzliche Daten gelesen werden.

Option B (gut): 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);

Gleiche Abfragen:

-- Abfrage: Fußballwetten der letzten Stunde
SELECT sum(amount) FROM bets_good
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN zeigt: nur Granulae mit sport_id = 1 werden gelesen, deutlich weniger

Warum es besser ist: ClickHouse kann über den Index sofort Blöcke mit sport_id = 1 finden, und innerhalb dieser Blöcke sind die Daten nach market_id und created_at sortiert. Der Zeitfilter created_at >= ... wird auf Granula-Ebene innerhalb dieser Blöcke angewendet.

6. Gleichheit vs. Bereich: Was ist effizienter

Für Spalten in ORDER BY gibt es eine Hierarchie der Effizienz:

1. Gleichheit (=) – am effizientesten. Wenn Sie nach einem exakten Wert suchen, kann ClickHouse ganze Blöcke von Granulae überspringen.
2. Ungleichheit (>=, <=, BETWEEN) – weniger effizient, kann aber funktionieren, wenn es die letzte Spalte im Schlüssel ist.
3. LIKE oder andere Funktionen – nutzen den Index oft gar nicht (es sei denn, sie werden in einen Bereich umgewandelt).

Regel: In ORDER BY sollten Spalten mit Gleichheitsbedingungen vor Spalten mit Bereichsbedingungen kommen.

Beispiel für Schlüssel (user_id, created_at):

-- ✅ Großartig: user_id = Gleichheit (erste Spalte), created_at >= Bereich (zweite)
SELECT * FROM bets WHERE user_id = 123 AND created_at >= '2025-06-01';

-- ❌ Schlecht: created_at Bereich (erste Spalte), user_id = Gleichheit (zweite)
-- Der Index kann nur nach created_at ausschließen, aber user_id muss innerhalb der Granulae gefiltert werden
SELECT * FROM bets WHERE created_at >= '2025-06-01' AND user_id = 123;

Warum ist das so? Weil die Daten physisch nach (user_id, created_at) sortiert sind. Alle Datensätze für eine user_id sind kompakt gespeichert und darin nach Zeit sortiert. Wenn Sie nach einem Zeitbereich suchen, ist das einfach. Aber wenn Sie zuerst nach Zeit suchen, sind die Datensätze für eine user_id über die gesamte Tabelle verstreut – sie können nicht durch den Index ausgeschlossen werden.

Analogie: Stellen Sie sich ein Telefonbuch vor, das zuerst nach Nachnamen, dann nach Vornamen sortiert ist. „Alle Ivanovs“ zu finden ist einfach (Nachname ist die erste Spalte). „Alle, die nach 1990 geboren sind“ zu finden, erfordert das Lesen des gesamten Buches.

7. Zusammengesetzter Schlüssel aus UInt8+UInt32+DateTime vs. nur DateTime

Manchmal scheint es: „Warum nicht einfach ORDER BY created_at – einfach und klar?“ Analysieren wir das anhand eines Glücksspielbeispiels.

Abfragen, die ein Dashboard tatsächlich benötigt:

• Wetten eines bestimmten Benutzers in der letzten Woche: WHERE user_id = 123 AND created_at >= today() - 7
• Statistiken nach Sportart für einen Tag: WHERE sport_id = 1 AND created_at = yesterday()
• Aggregation nach Markt für eine Stunde: WHERE market_id = 100 AND created_at >= now() - 1 hour

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

Probleme:

• Abfragen nach user_id werden langsam sein – Sie müssen alles scannen.
• Abfragen nach sport_id – dieselbe Geschichte.

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

Jetzt:

• Abfrage WHERE user_id = 123 AND created_at >= ... – ausgezeichnet (nutzt Präfix user_id).
• Abfrage WHERE sport_id = 1 AND created_at = ... – schlecht, weil sport_id nicht die erste Spalte ist. ClickHouse kann im Index nicht nach sport_id ausschließen.

Kompromiss: Wählen Sie das häufigste Filter-Muster und setzen Sie dessen Spalten an den Anfang von ORDER BY. Wenn Sie am häufigsten nach user_id suchen, setzen Sie user_id an die erste Stelle. Wenn öfter nach brand_id, setzen Sie das zuerst.

Faustregel: ORDER BY sollte mindestens 2–4 Spalten haben. Eine Spalte ist selten optimal.

8. So überprüfen Sie die Schlüsseleffizienz mit EXPLAIN

ClickHouse bietet leistungsstarke Werkzeuge, um zu analysieren, wie der Index verwendet wird.

EXPLAIN indexes = 1

-- Ausgabe von Indexnutzungsinformationen aktivieren
EXPLAIN indexes = 1
SELECT sum(amount) FROM bets
WHERE user_id = 123 AND created_at >= '2025-06-01';

Das Ergebnis zeigt etwa Folgendes:

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

Was die Zahlen bedeuten: 15 / 1280 – von 1280 Granulae in der Tabelle wurden nur 15 gelesen. Hervorragendes Ergebnis. Wenn es 1200 / 1280 anzeigt, hat der Index kaum geholfen.

system.query_log

Die Systemtabelle query_log speichert Statistiken für jede Abfrage. Die nützlichsten Spalten für die Indexanalyse:

-- Langsame Abfragen finden und sehen, wie viele Zeilen gelesen wurden
SELECT 
    query,
    read_rows,          -- wie viele Zeilen gelesen
    result_rows,        -- wie viele Zeilen zurückgegeben
    read_rows / result_rows AS efficiency,  -- näher an 1 ist besser
    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;

Interpretation:

• read_rows / result_rows ≈ 1..10 – Index funktioniert gut
• read_rows / result_rows > 1000 – Sie lesen Tausende von Zeilen für eine – schlechter Index
• read_rows nahe der Gesamtzeilenzahl der Tabelle – vollständiger Scan

columns_read aus system.query_log

SELECT 
    query,
    read_rows,
    written_rows,
    result_rows,
    columns_read,      -- Liste der gelesenen Spalten
    columns_written
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
LIMIT 10;

Wenn Sie in columns_read Spalten sehen, die nicht in SELECT oder WHERE vorkommen, liest ClickHouse zusätzliche Daten (möglicherweise aufgrund eines schlechten ORDER BY).

9. Muster für die Glücksspielbranche

Muster 1: Abfragen nach einem bestimmten Spieler

Wenn die häufigste Abfrage „Wettverlauf eines Benutzers anzeigen“ ist, ist der Schlüssel (user_id, created_at) ideal.

CREATE TABLE bets_by_user
(
    user_id     UInt64,
    created_at  DateTime,
    sport_id    UInt8,
    amount      Decimal(18,2)
)
ORDER BY (user_id, created_at);   -- Alle Wetten eines Benutzers sind kompakt und nach Zeit

Abfrage WHERE user_id = 123 AND created_at BETWEEN ... wird nur die Granulae dieses Benutzers lesen, die wenige sind.

Muster 2: Multi-Brand-Plattform

Sie haben mehrere Marken (casino_A, casino_B), und Abfragen enthalten fast immer brand_id. Dann:

CREATE TABLE bets_multi_brand
(
    brand_id    UInt8,        -- Niedrige Kardinalität (5 Marken)
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ORDER BY (brand_id, created_at);

Abfrage WHERE brand_id = 1 AND created_at >= ... schließt auf Indexebene alle Daten anderer Marken aus.

Muster 3: Dashboard nach Sportart

Wenn Berichte nach sport_id (Fußball, Hockey) gruppieren und nach Zeit filtern:

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

Es gibt keinen universellen Schlüssel. Sie müssen ein oder zwei der häufigsten Abfragemuster auswählen und dafür optimieren. Andere Abfragen werden langsamer sein – das ist ein unvermeidlicher Kompromiss.

10. Ändern von ORDER BY nach Tabellenerstellung – nicht möglich

Dies ist das traurigste, aber wichtigste Wissen. Sie können ORDER BY oder PRIMARY KEY in einer vorhandenen Tabelle nicht mit Befehlen wie ALTER ändern.

-- ❌ So etwas gibt es nicht
ALTER TABLE bets MODIFY ORDER BY (new_column, created_at);   -- FEHLER!

Warum? Weil die physische Reihenfolge der Zeilen bereits festgelegt ist. Um sie zu ändern, müssen Sie die Tabelle neu erstellen.

Was tun, wenn Sie feststellen, dass Sie einen Fehler gemacht haben?

Methode 1: Neue Tabelle erstellen, Daten migrieren, umbenennen

-- 1. Neue Tabelle mit dem richtigen ORDER BY erstellen
CREATE TABLE bets_new
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- neuer Schlüssel

-- 2. Daten migrieren (kann asynchron sein, wenn die Tabelle groß ist)
INSERT INTO bets_new SELECT * FROM bets;

-- 3. Tabellen tauschen (atomare Operation)
RENAME TABLE bets TO bets_old, bets_new TO bets;

-- 4. Überprüfen, ob alles funktioniert, dann alte Tabelle löschen
DROP TABLE bets_old;

Methode 2: Materialisierte Ansicht verwenden (wenn Sie Daten in zwei Reihenfolgen gleichzeitig speichern können)

-- Alte Tabelle für einige Abfragen behalten
-- Materialisierte Ansicht mit einem anderen ORDER BY für andere Abfragen erstellen
CREATE MATERIALIZED VIEW bets_by_sport_mv
ENGINE = MergeTree() ORDER BY (sport_id, created_at)
AS SELECT * FROM bets;   -- Daten werden dupliziert

Methode 3: Akzeptieren und mit einem schlechten Schlüssel leben (manchmal ist es billiger, Ressourcen zu erhöhen, als Terabytes zu migrieren)

Ratschlag: Bevor Sie eine Tabelle mit großen Datenmengen (Milliarden von Zeilen) erstellen, testen Sie ORDER BY immer an einer Stichprobe. Erstellen Sie eine Kopie mit 10 Millionen Zeilen, führen Sie EXPLAIN indexes=1 aus, testen Sie verschiedene Abfragen. Das erspart Ihnen später wochenlange Schmerzen.

Wie es weitergeht

Jetzt verstehen Sie, dass ORDER BY in ClickHouse nicht nur eine Sortierung ist, sondern ein strategischer Index. Nächste Themen:

• So konfigurieren Sie index_granularity – Granulagröße von 8192 auf einen anderen Wert ändern (fast nie nötig).
• Partitionierung vs. ORDER BY – wann Partitionen helfen und wann der Index.
• Skip-Indizes (Bloom-Filter-Indizes) – sekundäre Indizes für Spalten, die nicht in ORDER BY sind.
• Analyse langsamer Abfragen über system.query_log – tiefgehendes Profiling.

Fazit: Die Formel für das ideale ORDER BY in ClickHouse: Spalten mit niedriger Kardinalität und Gleichheitsbedingungen zuerst; dann Spalten mit hoher Kardinalität und Bereichen. Versuchen Sie nicht, alles abzudecken – wählen Sie die häufigsten Abfragen und ignorieren Sie den Rest. Und vergessen Sie nie EXPLAIN indexes=1 – der beste Freund eines ClickHouse-Entwicklers.

---

← Vorherige: Partitionierung in ClickHouse: So verwalten Sie Daten auf Ordnerebene
→ Nächste: TTL in ClickHouse: Automatisches Datenlebenszyklus-Management

— Editorial Team