Use the SQL optimization feature - Database Autonomy Service

Database Autonomy Service (DAS) propose une fonctionnalité d'optimisation SQL qui génère automatiquement des résultats de diagnostic, des suggestions d'optimisation et une estimation des gains de performance. Consultez ces résultats pour décider d'appliquer ou non les suggestions. Cette rubrique explique comment utiliser la fonctionnalité d'optimisation SQL.

Prérequis

Le moteur de base de données doit être l'un des suivants :
- RDS for MySQL
  
  Remarque
  Les éditions Basic et Cluster de RDS for MySQL ne sont pas prises en charge.
- MyBase MySQL
- PolarDB for MySQL
  
  Remarque
  Les clusters à nœud unique (anciennement instances autonomes) de PolarDB for MySQL ne sont pas pris en charge.
- PolarDB-X 2.0
  
  Remarque
  Les instances PolarDB-X 2.0 dont la version majeure est 5.4.13 et dont la version mineure se situe dans la plage [16415631,16504348] ne sont pas prises en charge. Pour afficher la version de votre instance PolarDB-X 2.0, consultez la rubrique Afficher et mettre à niveau la version d'une instance.
- MongoDB
L'instance cible est connectée à DAS. Pour plus d'informations, consultez la rubrique Connecter une instance à DAS.
L'état d'accès de l'instance cible est Normal Access.

Limitations

Il est impossible de diagnostiquer ou d'optimiser les instructions SQL portant sur des tables utilisant le moteur X-Engine.
Dans PolarDB-X, si vous exécutez une instruction SQL via une instruction préparée, le journal des requêtes lentes enregistre séparément le modèle de l'instruction (par exemple, select * from test where a = ? and b = ?) et les paramètres liés (par exemple, params: [1, 2]). Ce format n'étant pas directement exécutable en tant qu'instruction SQL valide, les fonctionnalités s'appuyant sur l'instruction originale pour l'analyse ou l'optimisation peuvent être limitées.

Créer une analyse SQL visuelle à partir des journaux des requêtes lentes

Cette fonctionnalité présente les flux d'exécution SQL complexes sous un format visuel intuitif. Son interface graphique affiche clairement le chemin d'exécution de la requête, l'efficacité des nœuds et les goulots d'étranglement potentiels, ce qui vous aide à identifier et résoudre rapidement les problèmes lors de l'optimisation SQL, des revues de code et des contrôles de santé.

Important

L'optimisation SQL sur la page Slow Log Analysis est prise en charge uniquement pour les instances RDS for MySQL 5.6, 5.7, and 8.0 et PolarDB for MySQL 5.6, 5.7, and 8.0.

Connectez-vous à la console DAS.
Dans le volet de navigation de gauche, cliquez sur Intelligent O&M Center > Instance Monitoring .
Recherchez l'instance cible et cliquez sur son ID pour ouvrir la page des détails de l'instance.
Dans le volet de navigation de gauche, cliquez sur Request Analysis > Slow Logs.
Sur la page Slow Log Analysis :
- Sous l'onglet Slow Log Statistics, recherchez le modèle SQL cible et cliquez sur Optimize dans la colonne Actions.
- Sous l'onglet Slow Log Details , repérez l'instruction SQL cible et cliquez sur Optimize dans la colonne Actions .
Dans la boîte de dialogue qui s'affiche, cliquez sur Create Plan .
Sélectionnez un type de nœud d'exécution.
- Standby node (par défaut) : nœud de secours de l'instance actuelle. Ce nœud est principalement utilisé pour l'analyse régulière des requêtes.
- Event node : nœud métier sur lequel l'instruction SQL a été réellement exécutée. Ce nœud est adapté au dépannage et à l'optimisation.
Cliquez sur Confirm Creation pour créer un Visual Execution Plan .

Plan d'exécution visuel

Important

Le plan d'exécution visuel reflète l'état d'exécution actuel, et non un historique.
La matrice visuelle s'adapte à la complexité de l'instruction SQL. Si le graphique contient beaucoup d'éléments, utilisez l'outil de zoom pour ajuster l'échelle d'affichage ou cliquez sur le bouton de réinitialisation pour revenir à la vue initiale.

Dans un plan d'exécution visuel, les flux d'exécution vont du bas vers le haut et de la gauche vers la droite. Le nœud situé en bas représente le point de départ de la requête. Les données remontent couche par couche jusqu'au renvoi du résultat final. Exemple :

Couleurs des nœuds

Nœud efficace (vert) : indique une méthode d'accès efficace, notamment : system , const , eq_ref , ref , ref_or_null et index_merge .
Nœud modérément efficace (jaune) : indique une méthode d'accès sous-optimale, notamment : fulltext , unique_subquery , index_subquery et range .
Nœud inefficace (rouge) : indique une méthode d'accès inefficace à optimiser en priorité, notamment : all et index .

Détails des nœuds

Haut : affiche le type de nœud, tel que TABLE_SCAN ou INDEX_SCAN .
Gauche : le coût, une métrique relative estimée par l'optimiseur en fonction de facteurs tels que le CPU, la mémoire et les E/S disque.
Droite : le nombre estimé de lignes renvoyées, qui indique le volume de traitement des données.

Suggestion d'optimisation

Priorisez l'optimisation des nœuds inefficaces marqués en rouge pour améliorer considérablement les performances des requêtes.

Glossaire

Terme	Nom courant	Description
QUERY_BLOCK	bloc de requête	Unité sémantique d'une instruction SQL. Chaque requête ou sous-requête indépendante constitue un bloc de requête, identifié par `select_id` dans la sortie EXPLAIN.
ATTACHED_SUBQUERIES	sous-requête attachée	Sous-requête attachée à une clause `WHERE` , `HAVING` ou `ON` à l'aide de prédicats tels que `EXISTS` , `IN` ou `ANY` . Ce type de sous-requête présente une dépendance logique avec la requête principale.
CORRELATED_SUBQUERY	sous-requête corrélée	Requête imbriquée qui fait référence à des colonnes d'une requête externe. Elle nécessite une liaison aux valeurs du contexte externe lors de l'exécution et peut entraîner une complexité en O(n²).
NON_CORRELATED_SUBQUERY	sous-requête non corrélée	Sous-requête autonome pouvant être exécutée indépendamment de la requête externe. L'optimiseur la précalcule généralement et la matérialise sous forme de constante.
MATERIALIZED_FROM_SUBQUERY	sous-requête matérialisée	Stratégie d'optimisation introduite dans MySQL 5.6+. Elle persiste le résultat de la sous-requête dans une table temporaire en mémoire et est souvent associée à une table dérivée au format `subqueryN` .
OPTIMIZED_AWAY_SUBQUERIES	sous-requête optimisée (supprimée)	Sous-requête entièrement supprimée après optimisation par réécriture de requête, telle qu'une propagation descendante de sous-requête constante. Elle n'apparaît pas dans le plan d'exécution final.
QUERY_SPECIFICATIONS	structure de spécification de requête	Unité sémantique complète décrivant les éléments syntaxiques d'une requête, y compris la liste SELECT, la clause FROM et les conditions de filtre.
SELECT_LIST_SUBQUERIES	sous-requête de liste de sélection	Sous-requête scalaire apparaissant dans les colonnes de projection (liste de champs SELECT). Elle doit renvoyer une seule valeur scalaire pour chaque itération.
INDEX_SCAN	balayage d'index	Modèle d'accès aux données utilisant un index B+ tree. Les balayages peuvent être directs ou inverses et peuvent impliquer des recherches dans la table.
TABLE_SCAN	balayage complet de la table	Méthode d'accès où l'optimiseur parcourt toutes les pages de l'index clusterisé. Cela se produit lorsqu'aucun index approprié n'est disponible ou lorsque la proportion de données à accéder dépasse un seuil. Les performances se dégradent à mesure que le volume de données augmente.
ORDERING_OPERATION	tri de l'ensemble de résultats	Opération ORDER BY qui trie l'ensemble de résultats à l'aide d'un algorithme de tri explicite, tel que filesort. Cette opération peut utiliser la mémoire ou des fichiers temporaires sur disque.
NESTED_LOOP	jointure par boucles imbriquées	Implémentation la plus basique d'un algorithme de jointure de tables. Pour chaque ligne de la table externe, elle parcourt les lignes correspondantes de la table interne. Cette méthode est efficace lorsque le prédicat de jointure offre un filtrage efficace.
DUPLICATES_REMOVAL	déduplication des résultats	Opération implémentant la sémantique DISTINCT. L'implémentation sous-jacente peut utiliser un index unique sur une table temporaire ou trier puis filtrer les résultats. Le coût dépend des caractéristiques de distribution des données.
WINDOWING_OPERATION	calcul de fonction de fenêtrage	Calcul de fonction analytique (tel que `ROW_NUMBER` ou `RANK` ) effectué sur une fenêtre de données définie par une clause OVER(). Cela peut nécessiter le tri de l'ensemble complet de données.
TABLE	référence à la table de base	Objet de stockage physique directement accessible dans le plan d'exécution. Il contient des informations telles que le nom de la table, l'alias et la méthode d'accès (par exemple, `const` , `system` ou `range` ).

Optimiser le SQL depuis la page Instance Sessions

Important

Les instances MySQL auto-gérées, MongoDB et RDS for PostgreSQL ne prennent pas en charge l'optimisation SQL sur la page Instance Sessions .

Connectez-vous à la console DAS.
Dans le volet de navigation de gauche, cliquez sur Intelligent O&M Center > Instance Monitoring .
Recherchez l'instance cible et cliquez sur son ID pour ouvrir la page des détails de l'instance.
Dans le volet de navigation de gauche, cliquez sur Instance Sessions .
Dans la zone Instance Sessions , sélectionnez la session à optimiser et cliquez sur Optimize .
Dans la boîte de dialogue SQL Diagnostic Optimization qui s'affiche, consultez les résultats du diagnostic SQL.

Si vous acceptez la suggestion, cliquez sur Copy en haut à droite et collez l'instruction SQL optimisée dans votre client de base de données ou dans DMS pour l'exécuter. Si vous n'acceptez pas la suggestion, cliquez sur Cancel pour fermer la boîte de dialogue.

Remarque
DAS diagnostique les instructions SQL en fonction de leur complexité, du volume de données des tables et de la charge de la base de données. Un diagnostic peut prendre plus de 20 secondes. Ensuite, le moteur fournit des résultats de diagnostic, des suggestions d'optimisation et une estimation des gains de performance que vous pouvez consulter et appliquer.

Une fois le diagnostic terminé, la page affiche l'SQL original, le plan d'exécution (incluant des colonnes telles que select_type, type et rows), les résultats du diagnostic (incluant les instructions DDL pour les suggestions d'index row-store et les détails des index), les avantages de l'optimisation (par exemple, « Amélioration attendue des performances : 0,55x » et « Gains d'optimisation faibles ») et les suggestions d'optimisation des instructions.

Afficher l'historique des diagnostics SQL

Connectez-vous à la console DAS.
Dans le volet de navigation de gauche, cliquez sur Intelligent O&M Center > Instance Monitoring .
Recherchez l'instance cible et cliquez sur son ID pour ouvrir la page des détails de l'instance.
Dans le volet de navigation de gauche, cliquez sur Request Diagnostic History . Ici, vous pouvez afficher l'historique des diagnostics SQL de l'instance, qui inclut des détails tels que le contenu SQL, l'état du diagnostic, l'heure et les résultats.