PolarDB:Auto plan cache

Contexte

Le choix d'un plan d'exécution dépend de nombreux facteurs, tels que les statistiques, l'ordre des jointures et les transformations de requêtes. Le temps d'optimisation varie selon les instructions. Pour certaines instructions SQL, ce temps représente une part importante du temps d'exécution total. Si ces instructions s'exécutent fréquemment, un temps d'optimisation élevé accroît la charge du système. La mise en cache et la réutilisation des plans d'exécution réduisent le temps d'optimisation à chaque exécution. Cela améliore les performances des requêtes, diminue la charge de la base de données et augmente le débit.

Pour de nombreuses autres requêtes, le temps d'optimisation est faible, mais le temps d'exécution dépend fortement du plan choisi. Différentes valeurs de paramètres dans une instruction SQL peuvent correspondre à différents plans d'exécution optimaux. Dans certains scénarios, MySQL récupère les données réelles depuis le moteur en fonction des valeurs de paramètres afin de poursuivre l'optimisation.

L'utilisation d'un plan d'exécution fixe pour ces requêtes n'améliore pas nécessairement le temps de réponse ni ne réduit la charge système. Cela peut même entraîner une régression des performances.

PolarDB for MySQL propose la fonctionnalité auto plan cache pour améliorer les performances des instructions SQL dont le temps d'optimisation est long, réduire la charge système et éviter les régressions liées à l'usage de plans d'exécution fixes. Cette fonctionnalité comporte trois modes : AUTO, DEMAND et ENFORCE. Définissez le paramètre loose_plan_cache_type sur l'un de ces modes pour stocker les plans d'exécution dans le cache de plans. Cela réduit le temps d'optimisation et améliore les performances des requêtes. Un plan d'exécution mis en cache s'invalide automatiquement si les statistiques d'une table référencée changent ou si une opération DDL (Data Definition Language) est effectuée sur cette table.

Prérequis

Paramètres

Configurez les paramètres du tableau suivant dans la console PolarDB. Pour plus d'informations, consultez Définir les paramètres de cluster et de nœud.

Description des interfaces

• dbms_sql.add_plan_cache(schema, query) : met en cache le plan d'exécution d'une instruction SQL spécifiée.

  Lorsque le paramètre loose_plan_cache_type est défini sur DEMAND, utilisez cette procédure stockée intégrée pour mettre en cache le plan d'exécution d'une instruction SQL spécifique. Exemple :

  CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");

  Après l'exécution de cette instruction, le plan d'exécution est mis en cache pour toute instruction SQL correspondant au modèle SELECT * FROM t_for_plan WHERE c1 > ? AND c1 < ?.

• dbms_sql.display_plan_cache_table() : affiche les informations relatives aux tables référencées dans le cache de plans actuel. Exemple :

  CALL dbms_sql.display_plan_cache_table()\G

  Résultat retourné :

  *************************** 1. row ***************************
   SCHEMA_NAME: test
    TABLE_NAME: t_for_plan
     REF_COUNT: 1
       VERSION: 0
  VERSION_TIME: 2023-03-10 17:21:35.605264

  Le tableau suivant décrit les champs retournés.

  • SCHEMA_NAME : nom du schéma contenant la table référencée.

  • TABLE_NAME : nom de la table référencée.

  • REF_COUNT : nombre de références à cette table dans le cache de plans.

  • VERSION : version de la table référencée dans le cache de plans.

  • VERSION_TIME : date et heure auxquelles la version actuelle de la table a été référencée.

• dbms_sql.delete_sharing_by_rowid(row_id) : supprime le plan d'exécution d'une instruction SQL spécifiée.

  Le paramètre row_id correspond à l'ID de ligne du plan d'exécution stocké dans la table mysql.sql_sharing.

  Example

  1. Exécutez la commande suivante pour consulter les informations sur les plans d'exécution présents dans le cache.

     SELECT Id, Schema_name, Type, Digest_text FROM mysql.sql_sharing WHERE Type = 'PLAN_CACHE'\G

     Résultat retourné :

     *************************** 1. row ***************************
              Id: 1
     Schema_name: test
            Type: PLAN_CACHE
     Digest_text: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?

     Le résultat de la requête indique que la valeur de row_id est 1.

  2. Supprimez le plan d'exécution obtenu lors de la requête précédente.

     CALL dbms_sql.delete_sharing_by_rowid(1);

Récupérer les informations du cache de plans

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

Performances des requêtes