Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra. # Résolution des problèmes de performance des requêtes pour les bases de données Aurora MySQL MySQL permet de [contrôler l’optimiseur de requêtes](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html) par le biais de variables système qui influent sur le mode d’évaluation des plans de requêtes, les optimisations remplaçables, les indices d’optimiseur et d’index, ainsi que le modèle de coût de l’optimiseur. Ces points de données peuvent être utiles non seulement pour comparer différents environnements MySQL, mais également pour comparer les plans d’exécution de requêtes précédents avec les plans d’exécution actuels, et pour comprendre l’exécution globale d’une requête MySQL à tout moment. Les performances des requêtes dépendent de nombreux facteurs, notamment le plan d’exécution, le schéma et la taille de la table, les statistiques, les ressources, les index et la configuration des paramètres. L’ajustement des requêtes nécessite d’identifier les goulots d’étranglement et d’optimiser le chemin d’exécution. + Identifiez le plan d’exécution de la requête et vérifiez si cette dernière utilise les index appropriés. Vous pouvez optimiser votre requête en utilisant `EXPLAIN` et en examinant les détails de chaque plan. + Aurora MySQL version 3 (compatible avec MySQL 8.0 Community Edition) utilise une instruction `EXPLAIN ANALYZE`. L’instruction `EXPLAIN ANALYZE` est un outil de profilage qui indique où MySQL consacre du temps à votre requête, et pourquoi. Avec `EXPLAIN ANALYZE`, Aurora MySQL planifie, prépare et exécute la requête tout en comptant les lignes et en mesurant le temps passé à différents moments du plan d’exécution. Lorsque la requête est terminée, `EXPLAIN ANALYZE` imprime le plan et ses mesures au lieu du résultat de la requête. + Mettez à jour les statistiques de votre schéma à l’aide de l’instruction `ANALYZE`. L’optimiseur de requêtes peut parfois choisir des plans d’exécution inappropriés en raison de statistiques obsolètes. Cela peut nuire aux performances d’une requête en raison d’estimations de cardinalité inexactes à la fois pour les tables et les index. La colonne `last_update` de la table [innodb\$1table\$1stats](https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html#innodb-persistent-stats-tables) indique la dernière fois que les statistiques de votre schéma ont été mises à jour, ce qui est un bon indicateur d’« obsolescence ». + D’autres problèmes peuvent survenir, tels que le biais de distribution des données, lesquels ne seront pas pris en compte pour la cardinalité des tables. Pour plus d’informations, consultez [Estimation de la complexité d’ANALYZE TABLE pour les tables InnoDB](https://dev.mysql.com/doc/refman/8.0/en/innodb-analyze-table-complexity.html) et [Statistiques d’histogramme dans MySQL](https://dev.mysql.com/blog-archive/histogram-statistics-in-mysql/) dans la documentation MySQL. ## Comprendre le temps passé par les requêtes Les éléments suivants permettent de déterminer le temps passé par les requêtes : + [Profilage](https://dev.mysql.com/doc/refman/8.0/en/show-profile.html) + [Schéma de performance](https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html) + [Optimiseur de requête](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html) **Profilage** Par défaut, le profilage est désactivé. Activez le profilage, puis exécutez la requête lente et vérifiez son profil. ``` SET profiling = 1; Run your query. SHOW PROFILE; ``` 1. Identifiez l’étape où la requête passe le plus de temps. Selon [États généraux des threads](https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html) dans la documentation MySQL, la lecture et le traitement des lignes d’une instruction `SELECT` est souvent l’état le plus long au cours de la durée de vie d’une requête donnée. Vous pouvez utiliser cette instruction `EXPLAIN` pour comprendre comment MySQL exécute cette requête. 1. Consultez le journal des requêtes lentes pour évaluer `rows_examined` et `rows_sent` afin de vous assurer que la charge de travail est similaire dans chaque environnement. Pour plus d’informations, consultez [Journalisation pour les bases de données Aurora MySQL](aurora-mysql-troubleshooting-logging.md). 1. Exécutez la commande suivante pour les tables faisant partie de la requête identifiée : ``` SHOW TABLE STATUS\G; ``` 1. Capturez les sorties suivantes avant et après l’exécution de la requête sur chaque environnement : ``` SHOW GLOBAL STATUS; ``` 1. Exécutez les commandes suivantes sur chaque environnement pour voir si d'autres éléments query/session influencent les performances de cet exemple de requête. ``` SHOW FULL PROCESSLIST; SHOW ENGINE INNODB STATUS\G; ``` Parfois, lorsque les ressources du serveur sont occupées, cela a un impact sur toutes les autres opérations du serveur, y compris les requêtes. Vous pouvez également capturer des informations périodiquement lorsque des requêtes sont exécutées, ou configurer une tâche `cron` pour capturer des informations à des intervalles utiles. **Schéma de performance** Le schéma de performance fournit des informations utiles sur les performances d’exécution du serveur, tout en ayant un impact minimal sur ces performances. Il diffère de l’`information_schema`, qui fournit des informations de schéma sur l’instance de base de données. Pour plus d’informations, consultez [Présentation du schéma de performance pour Performance Insights sur Aurora MySQL](USER_PerfInsights.EnableMySQL.md). **Traçabilité de l’optimiseur de requête** Pour comprendre pourquoi un [plan de requête particulier a été choisi pour être exécuté](https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html), vous pouvez configurer `optimizer_trace` pour accéder à l’optimiseur de requêtes MySQL. Exécutez une opération optimizer\$1trace pour afficher des informations détaillées sur tous les chemins disponibles pour l’optimiseur et sur son choix. ``` SET SESSION OPTIMIZER_TRACE="enabled=on"; SET optimizer_trace_offset=-5, optimizer_trace_limit=5; -- Run your query. SELECT * FROM table WHERE x = 1 AND y = 'A'; -- After the query completes: SELECT * FROM information_schema.OPTIMIZER_TRACE; SET SESSION OPTIMIZER_TRACE="enabled=off"; ``` ## Examen des paramètres de l’optimiseur de requêtes Aurora MySQL version 3 (compatible avec MySQL 8.0 Community Edition) inclut de nombreuses modifications liées à l’optimiseur par rapport à Aurora MySQL version 2 (compatible avec MySQL 5.7 Community Edition). Si vous avez des valeurs personnalisées pour `optimizer_switch`, nous vous recommandons de vérifier les différences entre les valeurs par défaut et de définir les valeurs `optimizer_switch` les mieux adaptées à votre charge de travail. Nous vous recommandons également de tester les options disponibles pour Aurora MySQL version 3 afin d’examiner les performances de vos requêtes. **Note** Aurora MySQL version 3 utilise la valeur par défaut 20 pour le paramètre [innodb\$1stats\$1persistent\$1sample\$1pages](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages). Vous pouvez utiliser la commande suivante pour afficher les valeurs `optimizer_switch` : ``` SELECT @@optimizer_switch\G; ``` Le tableau suivant affiche les valeurs `optimizer_switch` par défaut pour Aurora MySQL versions 2 et 3. | Paramètre | Aurora MySQL version 2 | Aurora MySQL version 3 | | --- | --- | --- | | batched\$1key\$1access | off | off | | block\$1nested\$1loop | on | on | | condition\$1fanout\$1filter | on | on | | derived\$1condition\$1pushdown | – | on | | derived\$1merge | on | on | | duplicateweedout | on | on | | engine\$1condition\$1pushdown | on | on | | firstmatch | on | on | | hash\$1join | off | on | | hash\$1join\$1cost\$1based | on | – | | hypergraph\$1optimizer | – | off | | index\$1condition\$1pushdown | on | on | | index\$1merge | on | on | | index\$1merge\$1intersection | on | on | | index\$1merge\$1sort\$1union | on | on | | index\$1merge\$1union | on | on | | loosescan | on | on | | materialization | on | on | | mrr | on | on | | mrr\$1cost\$1based | on | on | | prefer\$1ordering\$1index | on | on | | semijoin | on | on | | skip\$1scan | – | on | | subquery\$1materialization\$1cost\$1based | on | on | | subquery\$1to\$1derived | – | off | | use\$1index\$1extensions | on | on | | use\$1invisible\$1indexes | – | off | Pour plus d’informations, consultez [Optimisations remplaçables (MySQL 5.7](https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html)) et [Optimisations remplaçables (MySQL 8.0)](https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html) dans la documentation MySQL.