Dans quels scénarios peut-on remplacer LEFT JOIN par RIGHT JOIN pour optimiser les performances des requêtes - AnalyticDB

LEFT JOIN est une méthode de jointure de tables courante. Lors d'une jointure par hachage, la table de droite sert à construire la table de hachage et l'ordre des tables gauche et droite d'un LEFT JOIN ne peut pas être modifié. Si la table de droite est volumineuse, l'exécution de la requête ralentit et consomme beaucoup de mémoire. Cette rubrique présente, à l'aide d'exemples concrets, les scénarios dans lesquels il est possible de remplacer un LEFT JOIN par un RIGHT JOIN.

Contexte

AnalyticDB for MySQL utilise par défaut des jointures par hachage pour joindre les tables. Dans une jointure par hachage, la table de droite sert à construire la table de hachage, ce qui consomme beaucoup de ressources. Contrairement aux jointures internes, les jointures externes (y compris LEFT JOIN et RIGHT JOIN) ne permettent pas d'échanger l'ordre des tables gauche et droite sans modifier la sémantique de la requête. Par conséquent, si la table de droite est volumineuse, l'exécution de la requête ralentit et consomme beaucoup de mémoire. Dans les cas extrêmes où la table de droite est très volumineuse, les performances du cluster se dégradent ou l'erreur Out of Memory Pool size pre cal se produit lors de l'exécution de la requête. Appliquez alors les méthodes d'optimisation présentées dans cette rubrique pour réduire la consommation de ressources.

Scénarios applicables

Remplacez un LEFT JOIN par un RIGHT JOIN en modifiant l'instruction SQL ou en ajoutant un hint. Dans le LEFT JOIN d'origine, la table de gauche devient la table de droite pour construire la table de hachage. Si la nouvelle table de droite est trop volumineuse, les performances restent dégradées. Nous vous recommandons donc d'appliquer cette optimisation lorsque la table de gauche du LEFT JOIN est petite et que la table de droite est grande.

La taille d'une table (petite ou grande) est relative et dépend de facteurs tels que les colonnes de jointure et les ressources du cluster. En pratique, utilisez EXPLAIN ANALYZE pour afficher les paramètres du plan d'exécution. Pour décider d'utiliser un RIGHT JOIN, observez l'évolution de paramètres tels que PeakMemory et WallTime.

Utilisation

Remplacez un LEFT JOIN par un RIGHT JOIN de l'une des deux manières suivantes :

Modifiez directement l'instruction SQL. Par exemple, remplacez a left join b on a.col1 = b.col2 par b right join a on a.col1 = b.col2.
Ajoutez un hint pour indiquer à l'optimiseur de remplacer un LEFT JOIN par un RIGHT JOIN en fonction de la consommation de ressources. L'optimiseur décide alors s'il convient d'effectuer ce remplacement en se basant sur les tailles estimées des tables gauche et droite. Utilisez l'une des méthodes suivantes :
- Pour les clusters de version V3.1.8 ou ultérieure, cette fonctionnalité est activée par défaut. Si elle est désactivée, ajoutez le hint suivant au début de l'instruction SQL pour l'activer manuellement : /*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/
- Pour les clusters d'une version antérieure à V3.1.8, cette fonctionnalité est désactivée par défaut. Ajoutez le hint suivant au début de l'instruction SQL pour l'activer : /*+LEFT_TO_RIGHT_ENABLED=true*/

Remarque

Pour vérifier la version mineure d'un cluster Data Lakehouse Edition, exécutez SELECT adb_version();. Pour mettre à jour la version mineure, consultez la rubrique Mettre à jour la version mineure d'un cluster.

Exemples

Dans l'exemple suivant, nation est une petite table contenant 25 lignes et customer est une grande table contenant 15 000 000 de lignes. Utilisez EXPLAIN ANALYZE pour afficher le plan d'exécution d'une requête contenant un LEFT JOIN.

explain analyze
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey

Le plan ci-dessous montre l'étape 2, qui effectue le calcul de la jointure. L'opérateur Left Join contient les informations suivantes :

PeakMemory: 515MB (93.68%), WallTime: 4.34s (43.05%) : la valeur PeakMemory représente jusqu'à 93,68 % du total, ce qui indique que le LEFT JOIN constitue le goulot d'étranglement des performances de la requête.
Left (probe) Input avg.: 0.52 rows; Right (build) Input avg.: 312500.00 rows : la table de droite est la grande table et la table de gauche est la petite table.

Dans ce scénario, remplacez le LEFT JOIN par un RIGHT JOIN pour optimiser la requête.

Fragment 2 [HASH]
    Output: 48 rows (432B), PeakMemory: 516MB, WallTime: 6.52us, Input: 15000025 rows (200.27MB); per task: avg.: 2500004.17 std.dev.: 2410891.74
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: ? (?)}
    │   Output: 96 rows (864B), PeakMemory: 96B (0.00%), WallTime: 88.21ms (0.88%)
    │   count_2 := count(*)
    └─ LEFT Join[(`n_nationkey` = `c_nationkey`)][$hashvalue, $hashvalue_0_4]
       │   Outputs: []
       │   Estimates: {rows: 15000000 (0B)}
       │   Output: 30000000 rows (200.27MB), PeakMemory: 515MB (93.68%), WallTime: 4.34s (43.05%)
       │   Left (probe) Input avg.: 0.52 rows, Input std.dev.: 379.96%
       │   Right (build) Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [n_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 25 rows (350B), PeakMemory: 64KB (0.01%), WallTime: 63.63us (0.00%)
       │      Input avg.: 0.52 rows, Input std.dev.: 379.96%
       └─ LocalExchange[HASH][$hashvalue_0_4] ("c_nationkey")
          │   Outputs: [c_nationkey:integer, $hashvalue_0_4:bigint]
          │   Estimates: {rows: 15000000 (57.22MB)}
          │   Output: 30000000 rows (400.54MB), PeakMemory: 10MB (1.84%), WallTime: 1.81s (17.93%)
          └─ RemoteSource[4]
                 Outputs: [c_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 15000000 rows (200.27MB), PeakMemory: 3MB (0.67%), WallTime: 191.32ms (1.90%)
                 Input avg.: 312500.00 rows, Input std.dev.: 380.00%

Remplacer LEFT JOIN par RIGHT JOIN en modifiant l'instruction SQL :

SELECT
  COUNT(*)
FROM
  customer t2
  right JOIN nation t1 ON t1.n_nationkey = t2.c_nationkey

Remplacer LEFT JOIN par RIGHT JOIN en ajoutant un hint :
- Pour les clusters de version V3.1.8 ou ultérieure, exécutez l'instruction suivante pour activer cette fonctionnalité :
```
/*+O_CBO_RULE_SWAP_OUTER_JOIN=true*/
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey
```
- Pour les clusters d'une version antérieure à V3.1.8, exécutez l'instruction suivante pour activer cette fonctionnalité :
```
/*+LEFT_TO_RIGHT_ENABLED=true*/
SELECT
  COUNT(*)
FROM
  nation t1
  left JOIN customer t2 ON t1.n_nationkey = t2.c_nationkey
```

Après avoir exécuté EXPLAIN ANALYZE sur l'une des requêtes précédentes, le plan d'exécution affiche un RIGHT Join au lieu d'un LEFT Join, ce qui indique que le hint prend effet. Après l'ajustement, la valeur PeakMemory est de 889 Ko (3,31 %), soit une diminution de 515 Mo à 889 Ko, et ne constitue plus un point chaud de performance.

Fragment 2 [HASH]
    Output: 96 rows (864B), PeakMemory: 12MB, WallTime: 4.27us, Input: 15000025 rows (200.27MB); per task: avg.: 2500004.17 std.dev.: 2410891.74
    Output layout: [count_0_2]
    Output partitioning: SINGLE []
    Aggregate(PARTIAL)
    │   Outputs: [count_0_2:bigint]
    │   Estimates: {rows: ? (?)}
    │   Output: 192 rows (1.69kB), PeakMemory: 456B (0.00%), WallTime: 5.31ms (0.08%)
    │   count_2 := count(*)
    └─ RIGHT Join[(`c_nationkey` = `n_nationkey`)][$hashvalue, $hashvalue_0_4]
       │   Outputs: []
       │   Estimates: {rows: 15000000 (0B)}
       │   Output: 15000025 rows (350B), PeakMemory: 889KB (3.31%), WallTime: 3.15s (48.66%)
       │   Left (probe) Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       │   Right (build) Input avg.: 0.52 rows, Input std.dev.: 379.96%
       │   Distribution: PARTITIONED
       ├─ RemoteSource[3]
       │      Outputs: [c_nationkey:integer, $hashvalue:bigint]
       │      Estimates: 
       │      Output: 15000000 rows (200.27MB), PeakMemory: 3MB (15.07%), WallTime: 634.81ms (9.81%)
       │      Input avg.: 312500.00 rows, Input std.dev.: 380.00%
       └─ LocalExchange[HASH][$hashvalue_0_4] ("n_nationkey")
          │   Outputs: [n_nationkey:integer, $hashvalue_0_4:bigint]
          │   Estimates: {rows: 25 (100B)}
          │   Output: 50 rows (700B), PeakMemory: 461KB (1.71%), WallTime: 942.37us (0.01%)
          └─ RemoteSource[4]
                 Outputs: [n_nationkey:integer, $hashvalue_0_5:bigint]
                 Estimates: 
                 Output: 25 rows (350B), PeakMemory: 64KB (0.24%), WallTime: 76.34us (0.00%)
                 Input avg.: 0.52 rows, Input std.dev.: 379.96%