PolarDB:Configurer la réorganisation des jointures pour l'optimiseur

Le langage SQL est déclaratif : il spécifie le résultat à renvoyer, mais pas la méthode de calcul. Pour toute instruction SQL donnée, plusieurs plans de requête valides peuvent produire le résultat correct. Par exemple :

SELECT * FROM t0, t1, t2, t3 WHERE t0.a = t1.a AND t1.a = t2.a AND t2.a = t3.a AND t3.b = t1.b;

Pour l'instruction SQL précédente, les deux plans de requête suivants peuvent renvoyer le résultat correct. Les plans A et B sont appelés equivalent query plans. L'optimiseur explore les plans de requête équivalents pour une instruction SQL en appliquant des transformations. Par exemple, t1 INNER JOIN t2 et t2 INNER JOIN t1 constituent une paire de plans de requête équivalents. L'optimiseur peut générer t2 INNER JOIN t1 à partir de t1 INNER JOIN t2. Ce type de transformation est appelé query transformation rule.

L'optimiseur de requête suit ce flux de travail :

1. L'optimiseur reçoit un plan de requête initial que la base de données génère en analysant une instruction SQL.

2. Il applique ensuite des règles de transformation de requête au plan initial pour générer des plans de requête équivalents.

3. À l'aide de statistiques et d'un modèle de coût, l'optimiseur sélectionne le plan dont le coût d'exécution estimé est le plus faible et le transmet au moteur d'exécution en tant que plan d'exécution final.

La fonctionnalité d'optimisation des requêtes s'appuie sur des statistiques pour effectuer l'estimation de la cardinalité et le calcul des coûts afin de déterminer le meilleur plan de requête. Dans IMCI, les statistiques de table incluent les éléments suivants :

• Un histogramme décrit la distribution des valeurs d'une colonne et sert principalement à estimer la sélectivité des plages de valeurs et des prédicats d'égalité sur une seule table.

• Le nombre de valeurs distinctes dans une colonne sert principalement à estimer le nombre de groupes dans une clause Group By et peut également aider à estimer la sélectivité des prédicats d'égalité.

• D'autres contraintes, telles que la présence d'un index unique ou d'une contrainte de clé étrangère sur la colonne.

L'optimiseur de requête calcule le coût de chaque opérateur d'un plan de requête en fonction de deux facteurs :

• Le nombre total de lignes traitées par l'opérateur, qui peut être estimé à partir des statistiques.

• La complexité algorithmique de chaque opérateur utilisé dans le plan de requête.

Votre cluster PolarDB doit satisfaire à l'une des exigences de version suivantes :

• PolarDB for MySQL 8.0.1 avec la révision 8.0.1.1.31 ou ultérieure.

• PolarDB for MySQL 8.0.2 avec la révision 8.0.2.2.12 ou ultérieure.

Vous pouvez consulter le numéro de version de votre cluster pour confirmer la version de votre cluster.

Les scénarios suivants peuvent entraîner des erreurs significatives dans l'estimation de la cardinalité, ce qui peut amener l'optimiseur à choisir un plan de requête sous-optimal. Vous pouvez utiliser la syntaxe HINT pour guider l'optimiseur vers un meilleur plan de requête.

• Une requête comportant un prédicat qui utilise un opérateur de comparaison sur différentes colonnes de la même table, tel que t1.c1>t1.c2.

• Une requête comportant un prédicat qui utilise un opérateur dont la sélectivité ne peut pas être estimée à l'aide de statistiques, tel que t1.c1 MOD 2=1 ou t1.c2 LIKE '%ABC%'.

• Une requête comportant un prédicat contenant une expression qui ne peut pas être calculée lors de l'optimisation, telle que t1.c1+t1.c3>100.

• Une requête où les colonnes impliquées dans un opérateur manquent des statistiques nécessaires pour estimer la sélectivité du prédicat, telle que SELECT a, SUM(b) FROM t1 HAVING SUM(b) > 10.

• Plusieurs prédicats sont connectés par un opérateur AND, tel que t1.c1>10 AND t1.c3<5.

• La requête comporte trop de niveaux d'imbrication.

• La requête joint trop de tables. Vous pouvez ajuster le nombre de jointures recherchées par l'optimiseur IMCI en modifiant le paramètre loose_imci_max_enum_join_pairs.

L'exemple suivant utilise TPC-H Q8, qui est une requête multi-table incluant des fonctions d'agrégation.

SELECT
  o_year,
  SUM(
    CASE
      WHEN nation = 'BRAZIL' THEN volume
      ELSE 0
    END
  ) / SUM(volume) AS mkt_share
FROM
  (
    SELECT
      EXTRACT(
        year
        FROM
          o_orderdate
      ) AS o_year,
      l_extendedprice * (1 - l_discount) AS volume,
      n2.n_name AS nation
    FROM
      lineitem,
      orders,
      part,
      supplier,
      customer,
      nation n1,
      nation n2,
      region
    WHERE
      p_partkey = l_partkey
      AND s_suppkey = l_suppkey
      AND l_orderkey = o_orderkey
      AND o_custkey = c_custkey
      AND c_nationkey = n1.n_nationkey
      AND n1.n_regionkey = r_regionkey
      AND r_name = 'AMERICA'
      AND s_nationkey = n2.n_nationkey
      AND o_orderdate BETWEEN DATE '1995-01-01'
      AND DATE '1996-12-31'
      AND p_type = 'ECONOMY ANODIZED STEEL'
  ) AS all_nations
GROUP By
  o_year
ORDER BY
  o_year;

• Le plan de requête avec l'optimisation IMCI désactivée est le suivant : Dans ce plan, de nombreuses jointures génèrent de grands ensembles de résultats intermédiaires, ce qui augmente la quantité de données et le coût de traitement pour les opérateurs suivants, entraînant une latence de requête plus longue. Sur une machine 32 cœurs avec un jeu de données TPC-H SF100, cette requête a pris 7 017 ms pour s'achever.

• Le plan de requête avec l'optimisation IMCI activée est le suivant : L'optimiseur IMCI réorganise les jointures, réduisant la taille de sortie de la plupart des opérateurs de jointure à quelques millions de lignes. Cela réduit efficacement le coût de traitement pour les opérateurs suivants. Sur la même machine 32 cœurs avec le jeu de données TPC-H SF100, cette requête a pris 1 900 ms pour s'achever, soit une réduction de 73 % du temps de requête.