JOIN, avec un large choix d’algorithmes de jointure. Pour maximiser les performances, nous recommandons de suivre les conseils d’optimisation des jointures présentés dans ce guide.
- Pour des performances optimales, essayez de réduire le nombre de
JOINdans les requêtes, en particulier pour les workloads analytiques en temps réel où des performances de l’ordre de la milliseconde sont requises. Visez un maximum de 3 à 4 jointures par requête. Nous détaillons plusieurs approches pour minimiser les jointures dans la section sur la modélisation des données, notamment la dénormalisation, les Dictionaries et les vues matérialisées. - À partir de ClickHouse 24.12, le planner de requêtes réordonne automatiquement les jointures entre deux tables afin de placer la plus petite à droite pour des performances optimales. Dans la version 25.9, cette optimisation a été étendue à l’ordre des jointures dans les requêtes qui joignent trois tables ou plus.
- Si votre requête nécessite une jointure directe, c’est-à-dire un
LEFT ANY JOIN, comme illustré ci-dessous, nous vous recommandons d’utiliser des Dictionaries lorsque c’est possible.
- Pour les jointures internes, il est souvent plus efficace de les écrire sous forme de sous-requêtes avec la clause
IN. Considérez les requêtes suivantes, qui sont fonctionnellement équivalentes. Toutes deux calculent le nombre depostsqui ne mentionnent pas ClickHouse dans la question, mais le mentionnent dans lescomments.
ANY INNER JOIN plutôt qu’une simple jointure INNER, car nous ne voulons pas de produit cartésien, c.-à-d. que nous voulons une seule correspondance pour chaque publication.
Cette jointure peut être réécrite à l’aide d’une sous-requête, ce qui améliore considérablement les performances :
JOIN. Prenons l’exemple suivant, où nous voulons calculer le nombre de votes positifs pour les posts liés à Java depuis 2020.
Une requête naïve, avec la plus grande table à gauche, s’exécute en 56 s :
INNER JOIN dans une sous-requête, comme indiqué précédemment, tout en conservant le filtre dans la requête externe comme dans la sous-requête.
Choisir un algorithme de JOIN
Ces algorithmes déterminent la façon dont une requête avec jointure est planifiée et exécutée. Par défaut, ClickHouse utilise l’algorithme de jointure directe ou le hash join selon le type de jointure utilisé, la strictness et le moteur des tables jointes. ClickHouse peut également être configuré pour choisir l’algorithme de jointure de manière adaptative et en changer dynamiquement à l’exécution, selon la disponibilité et l’utilisation des ressources : lorsque
join_algorithm=auto, ClickHouse essaie d’abord le hash join et, si la limite mémoire de cet algorithme est dépassée, bascule à la volée vers le partial merge join. Vous pouvez voir quel algorithme a été choisi dans les logs de trace. ClickHouse vous permet aussi de spécifier directement l’algorithme de jointure souhaité via le paramètre join_algorithm.
Les types de JOIN pris en charge par chaque algorithme de jointure sont indiqués ci-dessous et doivent être pris en compte avant toute optimisation :
Vous trouverez ici une description complète de chaque algorithme de
JOIN, avec ses avantages, ses inconvénients et ses propriétés en matière de scaling.
Le choix de l’algorithme de jointure approprié dépend de votre objectif : optimiser la mémoire ou les performances.
Optimiser les performances de JOIN
-
(1) Si les données de la table de droite peuvent être préchargées dans une structure de données clé-valeur en mémoire à faible latence, par exemple un dictionnaire, si la clé de jointure correspond à l’attribut clé du stockage clé-valeur sous-jacent, et si la sémantique
LEFT ANY JOINconvient, alors le direct join peut être utilisé et constitue l’approche la plus rapide. - (2) Si l’ordre physique des lignes de votre table correspond à l’ordre de tri de la clé de jointure, alors cela dépend. Dans ce cas, le full sorting merge join saute l’étape de tri, ce qui réduit considérablement l’utilisation de la mémoire et peut, selon la taille des données et la distribution des valeurs des colonnes de clé de jointure, offrir des temps d’exécution plus rapides que certains algorithmes de hash join.
- (3) Si la table de droite tient en mémoire, même avec le surcoût de mémoire supplémentaire du parallel hash join, alors cet algorithme, ou le hash join, peut être plus rapide. Cela dépend de la taille des données, des types de données et de la distribution des valeurs des colonnes de clé de jointure.
- (4) Si la table de droite ne tient pas en mémoire, alors là encore, cela dépend. ClickHouse propose trois algorithmes de jointure non contraints par la mémoire. Tous les trois écrivent temporairement les données sur disque. Le full sorting merge join et le partial merge join nécessitent un tri préalable des données. Le grace hash join, lui, construit des tables de hachage à partir des données. Selon le volume de données, les types de données et la distribution des valeurs des colonnes de clé de jointure, il existe des scénarios dans lesquels construire des tables de hachage à partir des données est plus rapide que trier les données. Et inversement.
Optimisation de l’utilisation mémoire
- (1) Si l’ordre physique des lignes de votre table correspond à l’ordre de tri de la clé de jointure, alors l’utilisation mémoire du full sorting merge join est minimale. Avec en plus de bonnes performances de jointure, puisque la phase de tri est désactivée.
- (2) Le grace hash join peut être réglé pour une très faible utilisation mémoire en configurant un grand nombre de buckets, au détriment de la vitesse de jointure. Le partial merge join utilise volontairement peu de mémoire principale. Le full sorting merge join avec tri externe activé utilise généralement plus de mémoire que le partial merge join (en supposant que l’ordre des lignes ne corresponde pas à l’ordre de tri de la clé), avec l’avantage d’un temps d’exécution nettement meilleur.