MergeTree dans ClickHouse : granules, parties et index sparse

MergeTree dans ClickHouse : comment le moteur découpe l'analyse en granules et fusionne les parties

Sept ans de douleur, trois pertes de production et une plaque architecturale

Quand j'ai entendu parler de MergeTree pour la première fois, j'ai pensé : "Encore un moteur avec un nom à la mode." Puis, en production, une table avec 500 millions de paris a ralenti des requêtes qui volaient auparavant. Nous avons regardé EXPLAIN et vu Read 250000 granules. À l'époque, je ne savais pas ce qu'était un granule.

Il s'est avéré que j'avais créé une table avec le mauvais ORDER BY. Chaque requête scannait 80 % de toutes les données, même si elle ne filtrait que sur une colonne.

MergeTree n'est pas qu'un moteur. C'est une architecture qui détermine comment vos données sont stockées sur le disque, comment elles sont compressées, et surtout — comment ClickHouse décide quels blocs lire et lesquels ignorer. Comprendre les rouages m'a sauvé trois projets. Voici une carte que je suis depuis cinq ans.

1. Partie, granule, marque : une poupée russe sur le disque

ClickHouse ne stocke pas une table dans un seul fichier. Il divise les données en parties, à l'intérieur de chaque partie en granules, et navigue à l'aide de marques.

Structure disque de la table bets :
/var/lib/clickhouse/data/betting/bets/
├── 202401_1_1_0/          # partie n°1 (janvier 2024)
│   ├── user_id.bin        # colonne user_id (données binaires)
│   ├── user_id.mrk        # marques pour user_id
│   ├── created_at.bin
│   ├── created_at.mrk
│   ├── amount.bin
│   ├── amount.mrk
│   └── ...
├── 202401_2_2_0/          # partie n°2
└── 202402_3_3_0/          # partie n°3 (février)

Partie — la plus petite unité gérée par MergeTree. Chaque partie est créée lors de l'insertion, puis fusionnée en arrière-plan avec les parties voisines.

Granule — un bloc de données de index_granularity lignes (par défaut 8192). ClickHouse lit les données par granules entiers. Vous ne pouvez pas lire une seule ligne — seulement un granule entier.

Marque — un pointeur vers la position d'un granule dans le fichier .bin. Le fichier .mrk contient le décalage : où le granule commence sur le disque et son décalage.

Pourquoi c'est important : quand vous exécutez SELECT amount FROM bets WHERE user_id = 123, ClickHouse utilise l'index sparse pour déterminer quels granules peuvent contenir cet user_id et ne lit que ceux-ci. Il n'ouvre même pas les autres granules.

2. Fusion des parties : pourquoi ClickHouse ne s'effondre pas avec un million de petites insertions

Chaque INSERT crée une nouvelle partie sur le disque. Si vous insérez 100 enregistrements 10 000 fois — vous aurez 10 000 parties. C'est une catastrophe : une requête devrait ouvrir 10 000 fichiers.

Comment ClickHouse sauve la mise :

Le processus de fusion en arrière-plan colle les petites parties en plus grandes. Par exemple :

• 10 parties de 1 Go chacune → 1 partie de 10 Go

Paramètres que je modifie en production :

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

Astuce : si vous faites une grosse insertion (1M+ lignes), la partie ne fusionnera pas avec d'autres jusqu'à l'apparition d'une partie voisine. ClickHouse stocke les parties dans l'ordre croissant de la clé, donc INSERT ... ORDER BY aide.

Où je me suis brûlé : nous diffusions des paris via Kafka à 10-50 enregistrements par seconde. Après une semaine, nous avions 300 000 parties. Les requêtes ralentissaient car chaque requête ouvrait tous les fichiers. Nous avons corrigé en augmentant min_rows_for_wide_part à 500k et max_insert_block_size à 1M. Le flux de données a dû être mis en mémoire tampon dans Kafka, mais les parties sont passées à 500.

3. PRIMARY KEY vs ORDER BY : l'erreur la plus courante chez les débutants

Dans MySQL, PRIMARY KEY est un identifiant unique. Dans ClickHouse, pas tout à fait.

-- Je vois ça tout le temps
CREATE TABLE bets (
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
) ENGINE = MergeTree()
PRIMARY KEY (user_id)      -- ← erreur
ORDER BY (user_id);        -- ← aussi une erreur

La vérité :

• ORDER BY détermine l'ordre physique des lignes sur le disque. Obligatoire.
• PRIMARY KEY est identique à ORDER BY s'il n'est pas spécifié. Mais il peut être un PRÉFIXE de ORDER BY.

Correct :

ORDER BY (created_at, user_id)   -- d'abord le temps, puis l'utilisateur
PRIMARY KEY (created_at)         -- index seulement sur le temps

Ce qui se passe : ClickHouse construit un index sparse basé sur ORDER BY. PRIMARY KEY indique seulement quelle partie de ORDER BY utiliser pour le filtrage.

Exemple réel de notre production :

-- Mauvais (lent)
ORDER BY (user_id, created_at)
-- Requête : trouver les paris de la dernière heure. L'index n'aide pas, on scanne tout.

-- Correct (rapide)
ORDER BY (created_at, user_id)
-- Requête : sauter à la date requise via l'index, puis filtrer par user_id à l'intérieur

4. Index sparse : comment 8192 lignes deviennent une entrée d'index

ClickHouse ne construit PAS un index pour chaque ligne. Il prend un granule (8192 lignes) et écrit dans l'index :

• valeur minimale de ORDER BY dans ce granule
• valeur maximale de ORDER BY

C'est tout. Pas un B-tree, pas une table de hachage — juste un simple tableau de paires min-max.

Comment une requête accélère :

-- Trouver les paris pour 5 minutes
SELECT * FROM bets WHERE created_at BETWEEN '2024-03-15 14:00:00' AND '2024-03-15 14:05:00';

-- L'index (sparse) vérifie chaque granule :
-- Granule 1 : min='2024-03-15 13:00:00' max='2024-03-15 14:00:00' → NE CORRESPOND PAS (max < 14:05 ?)
-- Granule 2 : min='2024-03-15 14:00:00' max='2024-03-15 15:00:00' → CORRESPOND (min <= 14:05)
-- Granule 3 : min='2024-03-15 15:00:00' max='2024-03-15 16:00:00' → NE CORRESPOND PAS (min > 14:05)

Pourquoi c'est rapide : l'index prend (nombre de granules) * 16 octets. Pour 1 milliard de lignes, cela fait ~1,9 million de granules → 30 Mo d'index. L'index entier tient en mémoire.

5. Partitionnement : sauter au bon mois

PARTITION BY est la règle selon laquelle ClickHouse place les parties dans différents répertoires sur le disque.

PARTITION BY toYYYYMM(created_at)  -- partitions mensuelles

Sur le disque :

/var/lib/clickhouse/data/betting/bets/
├── 202401/   # janvier 2024
├── 202402/   # février 2024
└── 202403/   # mars 2024

Comment cela accélère les requêtes :

SELECT * FROM bets WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- ClickHouse va directement dans le dossier 202402/, n'ouvre même pas les autres partitions

Quand le partitionnement n'aide pas :

• Petites partitions (par jour avec 100 millions de lignes par jour → 365 partitions, chacune 300 Mo → beaucoup de fichiers)
• Filtre pas sur la clé de partitionnement

Mon choix : toYYYYMM() pour 10–100 millions de lignes par mois, toYYYYMMDD() si 1+ milliard par jour (mais alors il faut un cluster).

6. Format .bin et .mrk : comment les données reposent sur le disque

Un jour, j'ai fouillé dans un répertoire de table et j'ai vu :

$ 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 — données réelles de la colonne, compressées avec LZ4 (ou ZSTD si configuré)
• .mrk — marques : position de chaque granule dans .bin

Comment c'est lu :

1. La requête veut la colonne amount pour user_id=123
2. L'index sparse dit : cet user_id pourrait être dans les granules #45, #46, #47
3. ClickHouse ouvre user_id.mrk, prend le décalage pour le granule #45
4. Va dans user_id.bin à ce décalage, lit 8192 valeurs
5. Trouve les lignes avec l'user_id souhaité, se souvient des numéros de ligne
6. En utilisant les numéros de ligne, calcule les positions dans amount.mrk et lit seulement les octets nécessaires depuis amount.bin

Conclusion : physiquement, les données sont lues uniquement pour les colonnes requises et uniquement pour les granules requis. Tout le reste est métadonnées.

7. Exemple de ORDER BY correct sur une table de paris

Mauvais ORDER BY (j'ai fait ça) :

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 d'abord par utilisateur

Problème : 90 % des requêtes dans notre projet sont "afficher les paris de la dernière heure" (filtre par temps). L'index n'aide pas car user_id change plus vite que le temps. ClickHouse scanne toutes les partitions.

ORDER BY correct :

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 d'abord par temps

Maintenant :

• Requête par plage de dates : saute instantanément aux bons granules
• Dans une date, vous pouvez filtrer par user_id
• Optionnellement, vous pouvez ajouter un SECONDARY INDEX (mais c'est une autre histoire)

8. EXPLAIN indexes = 1 : voir combien de granules sont réellement lus

L'outil de débogage le plus utile :

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;

Sortie :

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)

Ce que nous voyons : sur 500 granules dans la partition, seulement 42 ont été lus. Degré de filtrage — 8 %. Sans le bon ORDER BY, ce serait 500 sur 500.

9. Commandes pour gérer les partitions

Voir toutes les partitions :

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

Supprimer une ancienne partition (plus rapide que DELETE) :

ALTER TABLE betting.bets DROP PARTITION '202401';

Nettoie le disque instantanément. DELETE FROM supprime ligne par ligne, puis fusionne — différence en heures.

Détacher une partition (sans supprimer les données) :

ALTER TABLE betting.bets DETACH PARTITION '202402';
-- données déplacées dans detached/

La rattacher :

ALTER TABLE betting.bets ATTACH PARTITION '202402';

Copier une partition vers une autre table (cas réel) :

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

10. OPTIMIZE TABLE — quand ce n'est pas nécessaire (et quand ça l'est soudainement)

OPTIMIZE TABLE force une fusion manuelle des parties.

Mauvaise nouvelle : la plupart des articles recommandent de l'exécuter périodiquement. Bonne nouvelle : dans 99 % des cas, vous n'en avez pas besoin. ClickHouse fusionne en arrière-plan automatiquement.

Quand j'ai réellement utilisé OPTIMIZE :

• Après avoir chargé un gros bloc de données historiques (100 millions de lignes en une insertion) — pour que les autres partitions n'attendent pas une fusion planifiée
• Avant de faire une sauvegarde, pour réduire le nombre de fichiers dans la table
• Tests — pour voir la taille réelle après compression

Comment le faire en toute sécurité :

OPTIMIZE TABLE betting.bets PARTITION '202403' FINAL;

FINAL fusionne toutes les parties en une seule pour cette partition. Sans FINAL — seulement les parties déjà prêtes.

Mon conseil : ne touchez pas à OPTIMIZE dans les scripts automatisés. Les fusions en arrière-plan sont bien réglées. Si les parties ne fusionnent pas, vérifiez max_bytes_to_merge et l'espace disque libre.

Prochaine étape

MergeTree est le cœur de ClickHouse. Maintenant vous savez comment il bat. Prochain article — sur les index avancés : skip indexes, colonnes matérialisées et projections.

---

← Précédente: ClickHouse : Référence complète des types de données pour l'analyse des paris (ce qui m'a coûté cher)
→ Suivante: Chargement de données dans ClickHouse : comment j'ai arrêté d'insérer ligne par ligne et accéléré l'ingestion par 500

— Editorial Team