MergeTree in ClickHouse: Granulat, Teile und dünner Index

MergeTree in ClickHouse: Wie die Engine Analysen in Granula schneidet und Teile zusammenführt

Sieben Jahre Schmerz, drei verlorene Produktionen und eine architektonische Plakette

Als ich zum ersten Mal von MergeTree hörte, dachte ich: „Noch eine Engine mit einem trendigen Namen.“ Dann, in der Produktion, verlangsamte eine Tabelle mit 500 Millionen Wetten Abfragen, die früher flogen. Wir sahen uns EXPLAIN an und lasen Read 250000 granules. Damals wusste ich nicht, was ein Granulum ist.

Es stellte sich heraus, dass ich eine Tabelle mit dem falschen ORDER BY erstellt hatte. Jede Abfrage scannte 80 % aller Daten, obwohl sie nur nach einer Spalte filterte.

MergeTree ist nicht nur eine Engine. Es ist eine Architektur, die bestimmt, wie Ihre Daten auf der Festplatte liegen, wie sie komprimiert werden und – am wichtigsten – wie ClickHouse entscheidet, welche Blöcke gelesen und welche übersprungen werden. Das Verständnis der Interna hat mir drei Projekte gerettet. Unten ist eine Karte, der ich seit fünf Jahren folge.

1. Part, Granulum, Mark: Eine Matrjoschka auf der Festplatte

ClickHouse speichert eine Tabelle nicht als einzelne Datei. Es teilt Daten in Parts auf, innerhalb jedes Parts in Granula und navigiert mit Marks.

Festplattenstruktur der Tabelle bets:
/var/lib/clickhouse/data/betting/bets/
├── 202401_1_1_0/          # Part #1 (Januar 2024)
│   ├── user_id.bin        # Spalte user_id (Binärdaten)
│   ├── user_id.mrk        # Marks für user_id
│   ├── created_at.bin
│   ├── created_at.mrk
│   ├── amount.bin
│   ├── amount.mrk
│   └── ...
├── 202401_2_2_0/          # Part #2
└── 202402_3_3_0/          # Part #3 (Februar)

Part – die kleinste Einheit, die von MergeTree verwaltet wird. Jeder Part wird beim Einfügen erstellt und dann im Hintergrund mit benachbarten Parts zusammengeführt.

Granulum – ein Datenblock von index_granularity Zeilen (Standard 8192). ClickHouse liest Daten in ganzen Granula. Sie können keine einzelne Zeile lesen – nur ein ganzes Granulum.

Mark – ein Zeiger auf die Position eines Granulums in der .bin-Datei. Die .mrk-Datei enthält den Offset: wo das Granulum auf der Festplatte beginnt und seinen Offset.

Warum das wichtig ist: Wenn Sie SELECT amount FROM bets WHERE user_id = 123 ausführen, verwendet ClickHouse den dünnbesetzten Index, um zu bestimmen, welche Granula diese user_id enthalten könnten, und liest nur diese. Es öffnet nicht einmal die anderen Granula.

2. Zusammenführen von Parts: Warum ClickHouse nicht an einer Million kleiner Einfügungen zerbricht

Jedes INSERT erstellt einen neuen Part auf der Festplatte. Wenn Sie 100 Datensätze 10.000 Mal einfügen – haben Sie 10.000 Parts. Das ist eine Katastrophe: Eine Abfrage müsste 10.000 Dateien öffnen.

Wie ClickHouse den Tag rettet:

Der Hintergrund-Merge-Prozess klebt kleine Parts zu größeren zusammen. Zum Beispiel:

• 10 Parts à 1 GB → 1 Part à 10 GB

Parameter, die ich in der Produktion anpasse:

<merge_tree>
    <min_rows_for_wide_part>100000</min_rows_for_wide_part>
    <max_bytes_for_merge>100000000000</max_bytes_for_merge>  <!-- 100 GB -->
    <merge_with_ttl_timeout>3600</merge_with_ttl_timeout>
</merge_tree>

Profi-Tipp: Wenn Sie einen großen Insert (1 Mio.+ Zeilen) durchführen, wird der Part nicht mit anderen zusammengeführt, bis ein benachbarter Part erscheint. ClickHouse speichert Parts in aufsteigender Schlüsselreihenfolge, daher hilft INSERT ... ORDER BY.

Wo ich mir die Finger verbrannt habe: Wir streamten Wetten über Kafka mit 10-50 Datensätzen pro Sekunde. Nach einer Woche hatten wir 300.000 Parts. Abfragen wurden langsamer, weil jede Abfrage alle Dateien öffnete. Wir behoben es, indem wir min_rows_for_wide_part auf 500k erhöhten und max_insert_block_size auf 1M. Der Datenstrom musste in Kafka gepuffert werden, aber die Parts schrumpften auf 500.

3. PRIMARY KEY vs. ORDER BY: Der häufigste Anfängerfehler

In MySQL ist PRIMARY KEY ein eindeutiger Identifikator. In ClickHouse nicht ganz.

-- Ich sehe das ständig
CREATE TABLE bets (
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
) ENGINE = MergeTree()
PRIMARY KEY (user_id)      -- ← Fehler
ORDER BY (user_id);        -- ← auch ein Fehler

Die Wahrheit:

• ORDER BY bestimmt die physische Reihenfolge der Zeilen auf der Festplatte. Obligatorisch.
• PRIMARY KEY ist dasselbe wie ORDER BY, wenn nicht angegeben. Aber es kann ein PRÄFIX von ORDER BY sein.

Richtig:

ORDER BY (created_at, user_id)   -- Zeit zuerst, dann Benutzer
PRIMARY KEY (created_at)         -- Index nur auf Zeit

Was passiert: ClickHouse baut einen dünnbesetzten Index basierend auf ORDER BY auf. PRIMARY KEY sagt nur, welcher Teil von ORDER BY zum Filtern verwendet wird.

Reales Beispiel aus unserer Produktion:

-- Falsch (langsam)
ORDER BY (user_id, created_at)
-- Abfrage: finde Wetten der letzten Stunde. Index hilft nicht, wir scannen alles.

-- Richtig (schnell)
ORDER BY (created_at, user_id)
-- Abfrage: springe über Index zum benötigten Datum, filtere dann nach user_id innerhalb

4. Dünnbesetzter Index: Wie 8192 Zeilen zu einem Indexeintrag werden

ClickHouse erstellt KEINEN Index für jede Zeile. Es nimmt ein Granulum (8192 Zeilen) und schreibt in den Index:

• Minimalwert von ORDER BY in diesem Granulum
• Maximalwert von ORDER BY

Das ist alles. Kein B-Baum, keine Hashtabelle – nur ein einfaches Array von Min-Max-Paaren.

Wie eine Abfrage beschleunigt wird:

-- Finde Wetten für 5 Minuten
SELECT * FROM bets WHERE created_at BETWEEN '2024-03-15 14:00:00' AND '2024-03-15 14:05:00';

-- Der (dünnbesetzte) Index prüft jedes Granulum:
-- Granulum 1: min='2024-03-15 13:00:00' max='2024-03-15 14:00:00' → KEINE ÜBEREINSTIMMUNG (max < 14:05?)
-- Granulum 2: min='2024-03-15 14:00:00' max='2024-03-15 15:00:00' → ÜBEREINSTIMMUNG (min <= 14:05)
-- Granulum 3: min='2024-03-15 15:00:00' max='2024-03-15 16:00:00' → KEINE ÜBEREINSTIMMUNG (min > 14:05)

Warum es schnell ist: Der Index benötigt (Anzahl der Granula) * 16 Bytes. Für 1 Milliarde Zeilen sind das ~1,9 Millionen Granula → 30 MB Index. Der gesamte Index passt in den Arbeitsspeicher.

5. Partitionierung: Springe zum richtigen Monat

PARTITION BY ist die Regel, nach der ClickHouse Parts in verschiedene Verzeichnisse auf der Festplatte legt.

PARTITION BY toYYYYMM(created_at)  -- monatliche Partitionen

Auf der Festplatte:

/var/lib/clickhouse/data/betting/bets/
├── 202401/   # Januar 2024
├── 202402/   # Februar 2024
└── 202403/   # März 2024

Wie es Abfragen beschleunigt:

SELECT * FROM bets WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- ClickHouse geht direkt zum Ordner 202402/, öffnet nicht einmal andere Partitionen

Wann Partitionierung nicht hilft:

• Kleine Partitionen (täglich mit 100 Millionen Zeilen pro Tag → 365 Partitionen, jede 300 MB → viele Dateien)
• Filter nicht auf dem Partitionsschlüssel

Meine Wahl: toYYYYMM() für 10–100 Millionen Zeilen pro Monat, toYYYYMMDD() wenn 1+ Milliarden pro Tag (aber dann brauchen Sie einen Cluster).

6. .bin- und .mrk-Format: Wie Daten auf der Festplatte liegen

Ich habe einmal in ein Tabellenverzeichnis geschaut und gesehen:

$ ls -la /var/lib/clickhouse/data/betting/bets/202401_1_1_0/
-rw-r----- 1 clickhouse clickhouse 1.2G  user_id.bin
-rw-r----- 1 clickhouse clickhouse  12M  user_id.mrk
-rw-r----- 1 clickhouse clickhouse 900M  created_at.bin
-rw-r----- 1 clickhouse clickhouse  12M  created_at.mrk
-rw-r----- 1 clickhouse clickhouse 2.1G  amount.bin
-rw-r----- 1 clickhouse clickhouse  12M  amount.mrk

• .bin – eigentliche Spaltendaten, komprimiert mit LZ4 (oder ZSTD, wenn konfiguriert)
• .mrk – Marks: Position jedes Granulums in .bin

Wie es gelesen wird:

1. Abfrage möchte Spalte amount für user_id=123
2. Dünnbesetzter Index sagt: diese user_id könnte in Granula #45, #46, #47 sein
3. ClickHouse öffnet user_id.mrk, nimmt den Offset für Granulum #45
4. Geht zu user_id.bin an diesem Offset, liest 8192 Werte
5. Findet Zeilen mit der gewünschten user_id, merkt sich Zeilennummern
6. Berechnet anhand der Zeilennummern Positionen in amount.mrk und liest nur die benötigten Bytes aus amount.bin

Fazit: Physisch werden Daten nur für die benötigten Spalten und nur für die benötigten Granula gelesen. Alles andere sind Metadaten.

7. Korrektes ORDER BY am Beispiel einer Wetttabelle

Schlechtes ORDER BY (habe ich gemacht):

CREATE TABLE betting.bets_wrong
(
    user_id UInt64,
    created_at DateTime64(3),
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- Index zuerst nach Benutzer

Problem: 90 % der Abfragen in unserem Projekt sind „zeige Wetten der letzten Stunde“ (Filter nach Zeit). Der Index hilft nicht, weil sich user_id schneller ändert als die Zeit. ClickHouse scannt alle Partitionen.

Korrektes ORDER BY:

CREATE TABLE betting.bets_correct
(
    user_id UInt64,
    created_at DateTime64(3),
    amount Decimal(18,2),
    sport LowCardinality(String),
    outcome Enum8('win'=1,'loss'=2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);   -- Index zuerst nach Zeit

Jetzt:

• Abfrage nach Datumsbereich: springt sofort zu den richtigen Granula
• Innerhalb eines Datums können Sie nach user_id filtern
• Optional können Sie einen SECONDARY INDEX hinzufügen (aber das ist eine andere Geschichte)

8. EXPLAIN indexes = 1: Sehen Sie, wie viele Granula tatsächlich gelesen werden

Das nützlichste Debugging-Werkzeug:

EXPLAIN indexes = 1
SELECT user_id, sum(amount)
FROM betting.bets
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'
  AND user_id = 100500
GROUP BY user_id;

Ausgabe:

Expression (Projection)
  Aggregating
    Expression
      ReadFromMergeTree (betting.bets)
        Indexes:
          Partition key:   partition_idx   (1/3 partitions, 1 read)
          Primary key:     created_at      (42/500 granules, 42 read)
          MinMax:          created_at      (0 skipped, 1 read)

Was wir sehen: Von 500 Granula in der Partition wurden nur 42 gelesen. Filtergrad – 8 %. Ohne das korrekte ORDER BY wären es 500 von 500.

9. Befehle zur Verwaltung von Partitionen

Alle Partitionen anzeigen:

SELECT 
    partition,
    name,
    rows,
    bytes_on_disk,
    modification_time
FROM system.parts
WHERE table = 'bets' AND active = 1;

Eine alte Partition löschen (schneller als DELETE):

ALTER TABLE betting.bets DROP PARTITION '202401';

Löscht sofort die Festplatte. DELETE FROM löscht Zeile für Zeile und führt dann Merge durch – Unterschied in Stunden.

Eine Partition trennen (ohne Daten zu löschen):

ALTER TABLE betting.bets DETACH PARTITION '202402';
-- Daten werden nach detached/ verschoben

Wieder anhängen:

ALTER TABLE betting.bets ATTACH PARTITION '202402';

Eine Partition in eine andere Tabelle kopieren (reales Beispiel):

ALTER TABLE betting.bets_archive REPLACE PARTITION '202401' FROM betting.bets;

10. OPTIMIZE TABLE – Wann nicht nötig (und wann plötzlich doch)

OPTIMIZE TABLE erzwingt ein manuelles Zusammenführen von Parts.

Schlechte Nachricht: Die meisten Artikel empfehlen, es regelmäßig auszuführen. Gute Nachricht: In 99 % der Fälle ist es nicht nötig. ClickHouse führt Merges automatisch im Hintergrund durch.

Wann ich OPTIMIZE tatsächlich verwendet habe:

• Nach dem Laden eines großen Blocks historischer Daten (100 Millionen Zeilen in einem Insert) – damit andere Partitionen nicht auf einen geplanten Merge warten
• Vor einem Backup, um die Anzahl der Dateien in der Tabelle zu reduzieren
• Testen – um die tatsächliche Größe nach der Komprimierung zu sehen

Wie man es sicher macht:

OPTIMIZE TABLE betting.bets PARTITION '202403' FINAL;

FINAL führt alle Parts in einen für diese Partition zusammen. Ohne FINAL – nur Parts, die bereits bereit sind.

Mein Rat: Fassen Sie OPTIMIZE nicht in automatisierten Skripten an. Hintergrund-Merges sind gut abgestimmt. Wenn Parts nicht zusammengeführt werden, überprüfen Sie max_bytes_to_merge und freien Speicherplatz.

Was kommt als Nächstes

MergeTree ist das Herz von ClickHouse. Jetzt wissen Sie, wie es schlägt. Nächster Artikel – über fortgeschrittene Indizierung: Skip-Indizes, materialisierte Spalten und Projections.

---

← Vorherige: ClickHouse: Vollständige Datentyp-Referenz für Wettanalysen (Woran ich mich verbrannt habe)
→ Nächste: Daten in ClickHouse laden: Wie ich aufhörte, Zeile für Zeile einzufügen und die Aufnahme um das 500-fache beschleunigte

— Editorial Team