ORDER BY et PRIMARY KEY dans ClickHouse : comment bien choisir son index
1. Pourquoi ORDER BY est la chose la plus importante que vous spécifierez dans une table
Dans les bases de données classiques (PostgreSQL, MySQL), il existe deux concepts : un index clusterisé (la clé primaire qui ordonne physiquement les données sur le disque) et des index secondaires (des B-trees séparés). Vous pouvez ajouter ou supprimer un index à tout moment sans recréer la table.
Dans ClickHouse, c'est différent. Ici, il n'y a qu'un seul ordre physique des données sur le disque — celui que vous avez spécifié dans ORDER BY. Et vous ne pouvez pas le changer sans recréer la table. Point final. C'est comme couler du béton et réaliser que vous avez mal placé les armatures. Vous devez tout casser et recommencer.
Pourquoi une telle rigidité ? Parce que ClickHouse stocke les données dans un format columnar, fortement compressé. Pour changer l'ordre des lignes, vous devriez réécrire toutes les colonnes de zéro. Personne ne veut attendre des heures ou des jours pour réorganiser une table de plusieurs téraoctets.
Par conséquent, choisir ORDER BY est une décision stratégique. Vous devez prédire quelles requêtes seront les plus fréquentes et concevoir la clé pour qu'elles s'exécutent à la vitesse de l'éclair. Une erreur coûtera cher.
Analogie concrète : Imaginez que vous êtes bibliothécaire et que vous devez ranger tous les livres sur les étagères dans un ordre spécifique. Vous pouvez choisir un ordre, par exemple par genre, et à l'intérieur de celui-ci par nom d'auteur. Vous pourrez alors trouver rapidement les livres si vous cherchez selon ces critères. Mais si vous décidez que l'ordre par date de publication serait plus pratique, vous devrez déplacer tous les livres des étagères à nouveau. Pendant des heures.
2. PRIMARY KEY ⊆ ORDER BY — Une règle rare
Dans ClickHouse, vous avez deux paramètres :
ORDER BY— définit l'ordre physique des lignes sur le disque (obligatoire).PRIMARY KEY— définit l'index (optionnel).
Et il y a une règle stricte : les colonnes listées dans PRIMARY KEY doivent être les premières colonnes de ORDER BY. En d'autres termes, PRIMARY KEY est un préfixe de ORDER BY.
-- ✅ Correct : PRIMARY KEY est les deux premières colonnes de ORDER BY
CREATE TABLE bets
(
user_id UInt64,
created_at DateTime,
amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at, amount) -- ordre complet
PRIMARY KEY (user_id, created_at); -- préfixe : les deux premières-- ❌ Erreur : PRIMARY KEY n'est pas un préfixe
ORDER BY (user_id, created_at, amount)
PRIMARY KEY (created_at, user_id); -- ordre différent — ClickHouse lèvera une erreur-- ⚠️ Vous pouvez omettre PRIMARY KEY complètement
-- Alors il correspond automatiquement à 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)Alors pourquoi avez-vous besoin de PRIMARY KEY si ce n'est qu'un préfixe ? Voici pourquoi : l'index ClickHouse (index sparse) est construit uniquement sur les colonnes de PRIMARY KEY. Si vous spécifiez un PRIMARY KEY plus court que ORDER BY, vous économisez de la mémoire sur l'index, mais l'ordre des lignes reste complet (par toutes les colonnes de ORDER BY). C'est utile lorsque les colonnes affectant l'ordre physique ne sont pas nécessaires dans l'index.
Exemple : Dans ORDER BY (user_id, created_at, amount) — les lignes sont d'abord par user_id, puis par created_at, puis par amount. Mais vous n'avez pas besoin de rechercher par amount, donc PRIMARY KEY (user_id, created_at) est plus court, l'index est plus petit, et la disposition physique aide à la compression (les montants identiques sont stockés ensemble).
3. Index sparse : une entrée pour 8192 lignes (granule)
L'index dans ClickHouse est appelé un index sparse. Il ne stocke pas un pointeur vers chaque ligne comme un B-tree dans PostgreSQL. Au lieu de cela, il stocke une entrée pour chaque groupe de 8192 lignes (ce groupe s'appelle un granule).
À quoi cela ressemble en interne :
| Granule (lignes 1–8192) | Valeur de PRIMARY KEY pour la première ligne du 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 |
| ... | ... |
Comment ClickHouse recherche les données :
- Vous avez une requête
WHERE user_id = 100 AND created_at >= '2025-01-01'. - ClickHouse regarde l'index sparse et voit les granules.
- Il trouve que
user_id=100apparaît dans les granules 1, 2, peut-être 3 et suivants. - Mais il ne sait pas exactement où dans un granule se trouve la ligne souhaitée — car l'index pointe seulement vers le début du granule.
- Par conséquent, ClickHouse lit tous les granules qui peuvent contenir les lignes nécessaires (parfois plus que nécessaire — cela s'appelle filtrage par index).
Analogie : Un index sparse est comme une table des matières dans un livre où chaque chapitre fait 100 pages. La table des matières dit : « Le chapitre 3 commence à la page 201. » Si vous avez besoin d'une phrase spécifique à la page 210, vous devez quand même lire les pages 201–300 en entier car vous ne connaissez pas l'emplacement exact. Dans PostgreSQL, un index B-tree vous donnerait la page 210.
Pourquoi est-ce rapide dans ClickHouse ? Parce que :
- ClickHouse lit les colonnes sélectivement — si WHERE a besoin de
user_idet SELECT a besoin deamount, il lit seulement ces deux colonnes. - Les données à l'intérieur d'un granule sont compressées, et lire 8192 lignes à la fois est très efficace (volume minimum ~64 Ko, la taille du granule est configurable via
index_granularity). - Pour les requêtes analytiques (qui lisent des millions de lignes), une telle granularité est acceptable.
4. Règle de cardinalité : d'abord faible, puis élevée
La cardinalité est le nombre de valeurs uniques dans une colonne. Par exemple :
sport_id(type de sport : football, hockey, tennis) — cardinalité 20 (faible)market_id(marché de pari : résultat, total, handicap) — cardinalité 1000 (moyenne)created_at(temps à la seconde) — cardinalité des milliards (élevée)
La règle d'or de ClickHouse : dans ORDER BY, les colonnes avec une faible cardinalité doivent venir avant les colonnes avec une cardinalité élevée.
Pourquoi ? Parce que l'index sparse sera plus efficace pour éliminer les granules.
Mauvaise clé : ORDER BY (created_at, sport_id)
- Les données sont d'abord triées par temps.
sport_idpour les lignes adjacentes va sauter partout : football, hockey, tennis, puis football à nouveau... - La requête
WHERE sport_id = 1force ClickHouse à lire tous les granules carsport_id=1est dispersé dans toute la table.
Bonne clé : ORDER BY (sport_id, created_at)
- D'abord, toutes les lignes de football (
sport_id=1) triées par temps. Ensuite toutes les lignes de hockey (sport_id=2) — compact. - La requête
WHERE sport_id = 1élimine tous les granules non liés au football au niveau de l'index. ClickHouse lit seulement les granules avecsport_id=1.
Analogie : Imaginez trier un jeu de cartes. Si vous triez d'abord par couleur (faible cardinalité — 4 valeurs) puis par rang (élevée — 13 valeurs), tous les piques seront ensemble. Si vous faites l'inverse — d'abord par rang, alors les as de toutes les couleurs sont dispersés dans le jeu. Trouver tous les piques devient difficile.
5. Exemple pour les paris : comment choisir le bon ORDER BY
Comparons deux options pour une table de paris chez un bookmaker.
Option A (mauvaise) : ORDER BY (created_at, sport_id)
CREATE TABLE bets_bad
(
sport_id UInt8, -- 1 = football, 2 = hockey, 3 = tennis
market_id UInt32, -- ID du marché de pari
user_id UInt64,
amount Decimal(18,2),
created_at DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, sport_id, market_id);Performance des requêtes typiques :
-- Requête : tous les paris football de la dernière heure
SELECT sum(amount) FROM bets_bad
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;
-- EXPLAIN montrera : lecture de presque tous les granules car sport_id=1 est dispersé dans le tempsL'index (created_at, sport_id) aide peu car sport_id est la deuxième colonne. ClickHouse peut utiliser le préfixe created_at, mais ensuite le filtrage sur sport_id doit être fait au niveau du granule, en lisant des données supplémentaires.
Option B (bonne) : 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);Mêmes requêtes :
-- Requête : paris football de la dernière heure
SELECT sum(amount) FROM bets_good
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;
-- EXPLAIN montrera : lecture seulement des granules où sport_id = 1, significativement moinsPourquoi c'est mieux : ClickHouse peut immédiatement trouver les blocs avec sport_id = 1 via l'index, et dans ces blocs, les données sont triées par market_id et created_at. Le filtre temporel created_at >= ... est appliqué au niveau du granule dans ces blocs.
6. Égalité vs plage : lequel est le plus efficace
Pour les colonnes dans ORDER BY, il existe une hiérarchie d'efficacité :
- Égalité (
=) — le plus efficace. Si vous cherchez une valeur exacte, ClickHouse peut sauter des blocs entiers de granules. - Inégalité (
>=,<=,BETWEEN) — moins efficace, mais peut fonctionner si c'est la dernière colonne de la clé. LIKEou autres fonctions — souvent n'utilisent pas l'index du tout (sauf si elles sont transformées en une plage).
Règle : Dans ORDER BY, les colonnes avec des conditions d'égalité doivent venir avant les colonnes avec des conditions de plage.
Exemple pour la clé (user_id, created_at) :
-- ✅ Excellent : user_id = égalité (première colonne), created_at >= plage (deuxième)
SELECT * FROM bets WHERE user_id = 123 AND created_at >= '2025-06-01';
-- ❌ Mauvais : created_at plage (première colonne), user_id = égalité (deuxième)
-- L'index ne peut éliminer que par created_at, mais user_id doit être filtré dans les granules
SELECT * FROM bets WHERE created_at >= '2025-06-01' AND user_id = 123;Pourquoi ? Parce que les données sont physiquement triées par (user_id, created_at). Tous les enregistrements pour un user_id sont stockés de manière compacte, et à l'intérieur, triés par temps. Si vous cherchez par plage de temps, c'est facile. Mais si vous cherchez d'abord par temps, les enregistrements pour un user_id sont dispersés dans toute la table — ils ne peuvent pas être éliminés par l'index.
Analogie : Imaginez un annuaire téléphonique trié d'abord par nom de famille, puis par prénom. Trouver « tous les Dupont » est facile (le nom de famille est la première colonne). Trouver « tous ceux nés après 1990 » nécessite de lire l'annuaire entier.
7. Clé composite UInt8+UInt32+DateTime vs seulement DateTime
Parfois, on se dit : « Pourquoi ne pas simplement utiliser ORDER BY created_at — simple et clair ? » Analysons avec un exemple de jeu.
Requêtes dont un tableau de bord a réellement besoin :
- Paris d'un utilisateur spécifique la semaine dernière :
WHERE user_id = 123 AND created_at >= today() - 7 - Statistiques par sport pour un jour :
WHERE sport_id = 1 AND created_at = yesterday() - Agrégation par marché pour une heure :
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;Problèmes :
- Les requêtes par
user_idseront lentes — il faudra tout scanner. - Les requêtes par
sport_id— même histoire.
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);Maintenant :
- Requête
WHERE user_id = 123 AND created_at >= ...— excellent (utilise le préfixeuser_id). - Requête
WHERE sport_id = 1 AND created_at = ...— médiocre, carsport_idn'est pas la première colonne. ClickHouse ne peut pas éliminer parsport_iddans l'index.
Compromis : Choisissez le modèle de filtre le plus fréquent et placez ses colonnes au début de ORDER BY. Si vous cherchez le plus souvent par user_id, mettez user_id en premier. Si plus souvent par brand_id, mettez celui-ci en premier.
Règle empirique : ORDER BY devrait avoir au moins 2 à 4 colonnes. Une seule colonne est rarement optimale.
8. Comment vérifier l'efficacité de la clé avec EXPLAIN
ClickHouse fournit des outils puissants pour analyser comment l'index est utilisé.
EXPLAIN indexes = 1
-- Activer la sortie des informations d'utilisation de l'index
EXPLAIN indexes = 1
SELECT sum(amount) FROM bets
WHERE user_id = 123 AND created_at >= '2025-06-01';Le résultat montrera quelque chose comme :
Expression
...
ReadFromMergeTree
Indexes:
PrimaryKey
Condition: (user_id = 123) AND (created_at >= '2025-06-01')
Used keys: (user_id, created_at)
Granules: 15 / 1280Que signifient les nombres : 15 / 1280 — sur 1280 granules dans la table, seulement 15 ont été lus. Excellent résultat. Si cela montre 1200 / 1280, l'index a à peine aidé.
system.query_log
La table système query_log stocke les statistiques pour chaque requête. Les colonnes les plus utiles pour l'analyse de l'index :
-- Trouver les requêtes lentes et voir combien de lignes elles lisent
SELECT
query,
read_rows, -- combien de lignes lues
result_rows, -- combien de lignes retournées
read_rows / result_rows AS efficiency, -- plus proche de 1 est meilleur
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;Comment interpréter :
read_rows / result_rows≈ 1..10 — l'index fonctionne bienread_rows / result_rows> 1000 — vous lisez des milliers de lignes pour une — mauvais indexread_rowsproche du nombre total de lignes dans la table — scan complet
columns_read depuis system.query_log
SELECT
query,
read_rows,
written_rows,
result_rows,
columns_read, -- liste des colonnes qui ont été lues
columns_written
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
LIMIT 10;Si dans columns_read vous voyez des colonnes qui ne sont ni dans SELECT ni dans WHERE, ClickHouse lit des données supplémentaires (probablement à cause d'un mauvais ORDER BY).
9. Modèles pour l'industrie du jeu
Modèle 1 : Requêtes par un joueur spécifique
Si la requête la plus fréquente est « afficher l'historique des paris d'un utilisateur », la clé (user_id, created_at) est idéale.
CREATE TABLE bets_by_user
(
user_id UInt64,
created_at DateTime,
sport_id UInt8,
amount Decimal(18,2)
)
ORDER BY (user_id, created_at); -- Tous les paris d'un utilisateur sont compacts et par tempsLa requête WHERE user_id = 123 AND created_at BETWEEN ... lira seulement les granules de cet utilisateur, qui sont peu nombreux.
Modèle 2 : Plateforme multi-marques
Vous avez plusieurs marques (casino_A, casino_B), et les requêtes incluent presque toujours brand_id. Alors :
CREATE TABLE bets_multi_brand
(
brand_id UInt8, -- Faible cardinalité (5 marques)
user_id UInt64,
created_at DateTime,
amount Decimal(18,2)
)
ORDER BY (brand_id, created_at);La requête WHERE brand_id = 1 AND created_at >= ... élimine toutes les données des autres marques au niveau de l'index.
Modèle 3 : Tableau de bord par sport
Si les rapports regroupent par sport_id (football, hockey) et filtrent par temps :
CREATE TABLE bets_by_sport
(
sport_id UInt8,
created_at DateTime,
user_id UInt64,
amount Decimal(18,2)
)
ORDER BY (sport_id, created_at);Il n'y a pas de clé universelle. Vous devez choisir un ou deux modèles de requêtes les plus fréquents et optimiser pour eux. Les autres requêtes seront plus lentes — c'est un compromis inévitable.
10. Modifier ORDER BY après la création de la table — Impossible
C'est la connaissance la plus triste mais la plus importante. Vous ne pouvez pas changer ORDER BY ou PRIMARY KEY sur une table existante avec des commandes comme ALTER.
-- ❌ Rien de tel n'existe
ALTER TABLE bets MODIFY ORDER BY (new_column, created_at); -- ERREUR !Pourquoi ? Parce que l'ordre physique des lignes est déjà déterminé. Pour le changer, vous devez recréer la table.
Que faire si vous réalisez que vous avez fait une erreur ?
Méthode 1 : Créer une nouvelle table, migrer les données, renommer
-- 1. Créer une nouvelle table avec le bon ORDER BY
CREATE TABLE bets_new
(
user_id UInt64,
created_at DateTime,
amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at); -- nouvelle clé
-- 2. Migrer les données (peut être asynchrone si la table est grande)
INSERT INTO bets_new SELECT * FROM bets;
-- 3. Échanger les tables (opération atomique)
RENAME TABLE bets TO bets_old, bets_new TO bets;
-- 4. Vérifier que tout fonctionne, puis supprimer l'ancienne table
DROP TABLE bets_old;Méthode 2 : Utiliser une vue matérialisée (si vous pouvez stocker les données dans deux ordres simultanément)
-- Garder l'ancienne table pour certaines requêtes
-- Créer une vue matérialisée avec un ORDER BY différent pour d'autres requêtes
CREATE MATERIALIZED VIEW bets_by_sport_mv
ENGINE = MergeTree() ORDER BY (sport_id, created_at)
AS SELECT * FROM bets; -- les données seront dupliquéesMéthode 3 : L'accepter et vivre avec une mauvaise clé (parfois il est moins coûteux d'augmenter les ressources que de migrer des téraoctets)
Conseil : Avant de créer une table avec de grandes données (milliards de lignes), testez toujours ORDER BY sur un échantillon. Créez une copie avec 10 millions de lignes, exécutez EXPLAIN indexes=1, testez différentes requêtes. Cela vous évitera des semaines de douleur plus tard.
Prochaines étapes
Maintenant vous comprenez que ORDER BY dans ClickHouse n'est pas seulement un tri, mais un index stratégique. Prochains sujets :
- Comment configurer index_granularity — changer la taille du granule de 8192 à une autre valeur (presque jamais nécessaire).
- Partitionnement vs ORDER BY — quand les partitions aident et quand l'index fait le travail.
- Index skip (index bloom filter) — index secondaires pour les colonnes non incluses dans ORDER BY.
- Analyse des requêtes lentes via system.query_log — profilage approfondi.
En résumé : La formule pour l'ORDER BY idéal dans ClickHouse : les colonnes avec une faible cardinalité et des conditions d'égalité en premier ; puis les colonnes avec une cardinalité élevée et des plages. N'essayez pas de tout couvrir — choisissez les requêtes les plus fréquentes et ignorez le reste. Et n'oubliez jamais EXPLAIN indexes=1 — le meilleur ami d'un développeur ClickHouse.
← Précédente: Partitionnement dans ClickHouse : Comment gérer les données au niveau des dossiers
→ Suivante: TTL dans ClickHouse : Gestion Automatique du Cycle de Vie des Données
— Editorial Team
Aucun commentaire pour le moment.