How to manually adjust the join order using hints - AnalyticDB

Cette rubrique explique comment ajuster manuellement l'ordre des jointures à l'aide d'indications (hints).

Présentation de la fonctionnalité

AnalyticDB for MySQL prend en charge les requêtes de jointure complexes et ajuste automatiquement l'ordre des jointures par défaut. Toutefois, les instructions de requête et les conditions de filtrage des tables peuvent évoluer. Si les caractéristiques des données sont complexes, l'ajustement automatique peut ne pas sélectionner l'ordre de jointure optimal. Un ordre de jointure sous-optimal génère de grands ensembles de résultats intermédiaires et une utilisation élevée de la mémoire, ce qui dégrade les performances des requêtes.

Pour résoudre ces problèmes, AnalyticDB for MySQL vous permet d'utiliser l'indication /*+ reorder_joins*/ pour activer ou désactiver l'ajustement de l'ordre des jointures :

/*+ reorder_joins=true*/ : Active l'ajustement automatique de l'ordre des jointures. AnalyticDB for MySQL active cette fonctionnalité par défaut ; il n'est donc pas nécessaire de spécifier cette indication lors de l'exécution des requêtes SQL.
/*+ reorder_joins=false*/ : Désactive l'ajustement automatique de l'ordre des jointures. Cela vous permet d'ajuster manuellement l'ordre des jointures en fonction des caractéristiques des données de la requête. La requête est ensuite exécutée selon l'ordre de jointure spécifié dans l'instruction SQL.

Remarque

/*+ reorder_joins*/ est une indication au niveau de la session. Elle affecte uniquement l'instruction de requête SQL dans laquelle elle est spécifiée.

Méthode d'ajustement

Avant l'ajustement
- Instruction de requête
  L'instruction Query10 originale est la suivante :
  Remarque
  
  Cette rubrique utilise Query10 de TPC-H comme exemple pour illustrer la méthode et l'effet de l'ajustement manuel de l'ordre des jointures. Pour plus d'informations sur TPC-H, consultez TPC-H.
  
  La fonctionnalité d'ajustement automatique de l'ordre des jointures de AnalyticDB for MySQL est activée par défaut. L'indication /*+ reorder_joins=false*/ est utilisée dans l'instruction de requête suivante pour simuler un scénario avec un ordre de jointure sous-optimal.
```
SELECT   c_custkey,
         c_name,
         Sum(l_extendedprice * (1 - l_discount)) AS revenue,
         c_acctbal,
         n_name,
         c_address,
         c_phone,
         c_comment
FROM     customer c,
         orders o,
         lineitem l,
         nation n
WHERE    c_custkey = o_custkey
AND      l_orderkey = o_orderkey
AND      o_orderdate >= date '1993-10-01'
AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
AND      l_returnflag = 'R'
AND      c_nationkey = n_nationkey
GROUP BY c_custkey,
         c_name,
         c_acctbal,
         c_phone,
         n_name,
         c_address,
         c_comment
ORDER BY revenue DESC
LIMIT    20;
```
- Ordre des jointures
  Sur la base de l'instruction SQL précédente, l'ordre des jointures est le suivant :
```
customer JOIN orders JOIN lineitem JOIN nation;
```
- Résultats de la requête
  Les résultats intermédiaires de chaque jointure dans le plan d'exécution sont les suivants :
  
  Remarque
  Pour plus d'informations sur la consultation d'un plan d'exécution, consultez Analyze a query using an execution plan.
  1. La table customer est jointe à la table orders. La jointure produit 57 069 lignes dans l'ensemble de résultats intermédiaires tmp1.
  2. L'ensemble de résultats intermédiaires tmp1 est joint à la table lineitem via une jointure INNER (Hash) sur la condition o_orderkey = l_orderkey. Cette opération prend 4,88 ms (21,67 %) et produit 114 705 lignes dans le nouvel ensemble de résultats intermédiaires tmp2.
  3. L'ensemble de résultats intermédiaires tmp2 est joint à la table nation. La jointure produit 114 705 lignes en tant que résultat final. Le nœud de jointure est de type INNER (Hash). La condition de jointure est left.c_nationkey = right.n_nationkey. L'opération prend 5,71 ms (12,07 %).
  Le nombre cumulé de lignes produites par les trois jointures est de : 57 069 + 114 705 + 114 705 = 286 479.
Après l'ajustement
- Instruction de requête
  Ajoutez l'indication /*+ reorder_joins=false*/ à l'instruction SQL pour désactiver la fonctionnalité d'ajustement automatique de l'ordre des jointures de AnalyticDB for MySQL, puis ajustez manuellement l'ordre des jointures. L'instruction SQL avec l'ordre ajusté est la suivante :
```
/*reorder_joins=false*/
SELECT   c_custkey,
         c_name,
         Sum(l_extendedprice * (1 - l_discount)) AS revenue,
         c_acctbal,
         n_name,
         c_address,
         c_phone,
         c_comment
FROM     customer c,
         orders o,
         nation n,
         lineitem l
WHERE    c_custkey = o_custkey
AND      c_nationkey = n_nationkey
AND      l_orderkey = o_orderkey
AND      o_orderdate >= date '1993-10-01'
AND      o_orderdate <  date '1993-10-01' + INTERVAL '3' month
AND      l_returnflag = 'R'
GROUP BY c_custkey,
         c_name,
         c_acctbal,
         c_phone,
         n_name,
         c_address,
         c_comment
ORDER BY revenue DESC
LIMIT    20;
```
- Ordre des jointures
  Sur la base de l'instruction SQL précédente, l'ordre des jointures est le suivant :
```
customer JOIN orders JOIN nation JOIN lineitem
```
- Résultats de la requête
  Les résultats intermédiaires de chaque jointure dans le plan d'exécution sont les suivants :
  
  Remarque
  Pour plus d'informations sur la consultation d'un plan d'exécution, consultez Analyze a query using an execution plan.
  1. La table customer est jointe à la table orders. La jointure produit 57 069 lignes dans l'ensemble de résultats intermédiaires tmp1. Le nœud de jointure est de type INNER (Hash). La condition de jointure est c_custkey = o_custkey. L'opération prend 4,75 ms (36,7 %).
  2. L'ensemble de résultats intermédiaires tmp1 est joint à la table nation. La jointure produit 57 069 lignes dans l'ensemble de résultats intermédiaires tmp2. Le nœud de jointure est de type INNER (Hash). La condition de jointure est left.c_nationkey = right.n_nationkey. L'opération prend 5,63 ms (25,61 %).
  3. L'ensemble de résultats intermédiaires tmp2 est joint à la table lineitem via une jointure INNER (Hash) sur la condition left.o_orderkey = right.l_orderkey. La jointure produit 114 705 lignes en tant que résultat final et prend 5,29 ms (9,81 % du temps total).
  Le nombre cumulé de lignes produites par les trois jointures est de : 57 069 + 57 069 + 114 705 = 228 843.
  
  Cela représente une réduction de 20 % du nombre de lignes produites par rapport aux 286 479 lignes obtenues avant l'ajustement. Cette comparaison montre que différents ordres de jointure peuvent affecter la taille des ensembles de résultats intermédiaires. Si vous constatez que l'ordre des jointures dans AnalyticDB for MySQL n'est pas optimal, ajustez-le manuellement pour améliorer les performances des requêtes.