Image par NicoElNino / Shutterstock
Capturer vos requêtes SQL avec MySQL
L'article précédent Capturing running SQL statements aborde un volet essentiel de l'analyse des performances applicatives lorsqu'on utilise une base de données relationnelle (SGBDR). Pour bien analyser une requête SQL avec MySQL, il faut également rassembler des métadonnées complémentaires qui apportent un contexte suffisant : la structure des tables concernées, des statistiques sur leurs données et leur structure, ainsi que le détail temporel des étapes de préparation et d'exécution de la requête. Ces informations se collectent à différents moments : avant, pendant et après l'exécution.
Collecte d'informations complémentaires sur les requêtes
Sous MySQL, les informations suivantes facilitent la validation, l'évaluation et l'optimisation des requêtes SQL applicatives. Elles peuvent être collectées avec les outils MySQL natifs.
- Lignes renvoyées (après exécution)
- Temps d'exécution (après exécution)
- Plan d'exécution de la requête (QEP) (avant exécution)
- Structure des tables (avant exécution)
- Cardinalité des index (avant exécution)
- Coût d'exécution de la requête (avant exécution)
- Évaluation par l'optimiseur de requêtes (avant exécution)
- Valeurs de statut (avant, pendant et après exécution)
- Profiling de la requête (après exécution)
- Processlist (pendant l'exécution)
Sauf mention contraire, le client en ligne de commande mysql est utilisé pour les exemples de sortie ci-dessous.
NOTE : les exemples SQL présentés ici sont compatibles avec MySQL 5.7 et MySQL 8.0. La version Community de MySQL 5.7 ayant atteint sa fin de vie (EOL), toutes les sorties d'exemple et références à la documentation s'appuient sur la version actuelle MySQL 8.0. Certaines sorties affichées dans cet article peuvent différer de celles obtenues avec MySQL 5.7.
1\. Lignes renvoyées
Lignes renvoyées avec le client MySQL
Avec le client mysql, vous pouvez exécuter une requête SQL : le nombre de lignes renvoyées apparaît dans une instruction de statut affichée après les résultats. Encore faut-il l'extraire parmi tout le contenu capturé.
mysql> SELECT ...
10 rows in set ...
Lignes renvoyées via le slow query log
Le slow query log indique le nombre de lignes renvoyées via la valeur `Rows_sent` pour chaque requête SQL journalisée.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
L'article Capturing running SQL statements détaille la configuration, l'utilisation et l'analyse du slow query log MySQL.
Lignes renvoyées via les schémas performance/sys
Vous pouvez obtenir les informations relatives à rows_sent depuis les schémas PERFORMANCE_SCHEMA et sys, mais il s'agit généralement de résultats agrégés. L'exemple suivant montre un exec_count=1, et donc une valeur rows_sent précise.
mysql> SELECT * FROM sys.x$statement_analysis WHERE query LIKE '%airport%'\G
query: SELECT `a` . `country_code` , `c` . `name` , COUNT ( * ) AS `cnt` FROM `airport` `a` INNER JOIN `country` `c` USING ( `country_code` ) GROUP BY `country_code` , NAME ORDER BY ? DESC LIMIT ?
db: airport
full_scan: *
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 477392952000
max_latency: 477392952000
avg_latency: 477392952000
lock_latency: 221000000
rows_sent: 10
rows_sent_avg: 10
rows_examined: 152960
rows_examined_avg: 152960
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 1
tmp_disk_tables: 0
rows_sorted: 10
sort_merge_passes: 0
digest: 616aa31e4f99b22a194c91ae9f1cfbfb95d6957621b55393b4a01603d8e1fd47
first_seen: 2024-01-18 19:23:42.595635
2\. Temps d'exécution de la requête
Temps d'exécution via le client MySQL
Comme pour le nombre de lignes renvoyées, le client mysql affiche le temps total d'exécution avec une granularité de 10 millisecondes.
mysql> SELECT ...
10 rows in set (0.28 sec)
Temps d'exécution via le profiling
L'activation du profiling permet d'obtenir un temps total d'exécution haute précision pour une requête SQL.
mysql > SET @@profiling=1;
mysql > SELECT ...
mysql > SET @@profiling=0;
mysql > SHOW PROFILES;
mysql > SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=1;
NOTE : si vous exécutez d'autres requêtes SELECT, la valeur query_id doit être ajustée pour cibler la requête dont vous souhaitez mesurer le temps d'exécution.
mysql > SHOW PROFILES;
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1| 0.23497150 | SELECT a.country_code, c.name, COUNT(*) AS cnt
FROM airport a
INNER JOIN country c USING (country_code)
GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10 |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=9;
+----------------+
| SUM(DURATION) |
+----------------+
| 0.2349715 |
+----------------+
Le profiling est décrit plus en détail dans une section ultérieure.
Temps d'exécution via le slow query log
Lorsqu'une requête est consignée dans le slow query log, vous pouvez obtenir un temps d'exécution haute précision via la valeur Query_time.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
3\. Plan d'exécution de la requête (QEP)
L'instruction EXPLAIN produit le QEP, qui contient des informations précieuses sur la manière dont MySQL exécutera la requête. Le QEP présente, dans l'ordre, les étapes d'exécution et fournit des indications complémentaires : index évalués, partitions considérées, type de jointure utilisée, estimation du nombre de lignes, ainsi que des éléments figurant dans la colonne Extra.
mysql> EXPLAIN SELECT ...
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
| 1 | SIMPLE | a | NULL | ALL | country_code | NULL | NULL | NULL | 86733 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 8 | airport.a.country_code | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+-------+----------+---------------------------------+
2 rows in set, 1 warning (0.04 sec)
L'instruction EXPLAIN produit toujours un avertissement, qui correspond à la requête réécrite pour l'exécution interne. Au-delà d'un avertissement, il convient de les examiner avant de poursuivre.
> SHOW WARNINGS;
Note (Code 1003): /* select#1 */ select `airport`.`a`.`country_code` AS `country_code`,`airport`.`c`.`name` AS `name`,count(0) AS `cnt` from `airport`.`airport` `a` join `airport`.`country` `c` where (`airport`.`c`.`country_code` = `airport`.`a`.`country_code`) group by `airport`.`a`.`country_code` order by count(0) desc limit 10
Il existe également un format JSON plus récent en complément du format tabulaire traditionnel (FORMAT=TRADITIONAL), qui ajoute des données cost_info supplémentaires.
mysql> EXPLAIN FORMAT=JSON SELECT ...
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "105264.60"
},
"ordering_operation": {
"using_filesort": true,
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [\
{\
"table": {\
"table_name": "a",\
"access_type": "ALL",\
"possible_keys": [\
"country_code"\
],\
"rows_examined_per_scan": 86733,\
"rows_produced_per_join": 86733,\
"filtered": "100.00",\
"cost_info": {\
"read_cost": "1185.00",\
"eval_cost": "8673.30",\
"prefix_cost": "9858.30",\
"data_read_per_join": "256M"\
},\
"used_columns": [\
"airport_id",\
"name",\
"country_code"\
]\
}\
},\
{\
"table": {\
"table_name": "c",\
"access_type": "eq_ref",\
"possible_keys": [\
"PRIMARY"\
],\
"key": "PRIMARY",\
"used_key_parts": [\
"country_code"\
],\
"key_length": "8",\
"ref": [\
"airport.a.country_code"\
],\
"rows_examined_per_scan": 1,\
"rows_produced_per_join": 86733,\
"filtered": "100.00",\
"cost_info": {\
"read_cost": "86733.00",\
"eval_cost": "8673.30",\
"prefix_cost": "105264.60",\
"data_read_per_join": "160M"\
},\
"used_columns": [\
"country_code",\
"name"\
]\
}\
}\
]
}
}
}
}
Pour en savoir plus, consultez le manuel de référence MySQL 8.0.
Voir, plus loin, un exemple de l'option EXPLAIN ANALYZE qui illustre également la disposition FORMAT=TREE.
4\. Structure des tables
Structure des tables via le client mysql (1)
Pour chaque table impliquée dans une requête SQL, il est important de recueillir les informations relatives à ses colonnes et à ses contraintes. Vous pouvez recourir à la syntaxe SHOW CREATE TABLE.
mysql> SHOW CREATE TABLE airport\G
*************************** 1. row ***************************
Table: airport
Create Table: CREATE TABLE `airport` (
`airport_id` int unsigned NOT NULL,
`ident` varchar(7) NOT NULL,
`type` varchar(20) NOT NULL,
`name` varchar(100) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`elevation_ft` smallint DEFAULT NULL,
`continent_code` char(2) NOT NULL,
`country_code` char(2) NOT NULL,
`region_code` char(7) NOT NULL,
`municipality` varchar(60) NOT NULL,
`scheduled_service` tinyint(1) NOT NULL,
`gps_code` char(4) DEFAULT NULL,
`iata_code` char(3) DEFAULT NULL,
`local_code` varchar(7) DEFAULT NULL,
`home_url` varchar(128) DEFAULT NULL,
`wikipedia_url` varchar(128) DEFAULT NULL,
`keywords` varchar(300) DEFAULT NULL,
PRIMARY KEY (`airport_id`),
KEY `name` (`name`),
KEY `country_code` (`country_code`),
KEY `region_code` (`region_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.03 sec)
NOTE : l'avantage de cette syntaxe est que la sortie permet de recréer la table à l'identique.
Pour en savoir plus, consultez le manuel de référence MySQL 8.0.
Structure des tables via le client mysql (2)
Vous pouvez également décrire une table avec l'instruction SQL DESC. L'inconvénient : sa sortie ne permet pas de recréer la table, ni de déterminer correctement les colonnes ni les positions des index.
mysql> DESC airport;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| airport_id | int unsigned | NO | PRI | NULL | |
| ident | varchar(7) | NO | | NULL | |
| type | varchar(20) | NO | | NULL | |
| name | varchar(100) | NO | MUL | NULL | |
| latitude | float | NO | | NULL | |
| longitude | float | NO | | NULL | |
| elevation_ft | smallint | YES | | NULL | |
| continent_code | char(2) | NO | | NULL | |
| country_code | char(2) | NO | MUL | NULL | |
| region_code | char(7) | NO | MUL | NULL | |
| municipality | varchar(60) | NO | | NULL | |
| scheduled_service | tinyint(1) | NO | | NULL | |
| gps_code | char(4) | YES | | NULL | |
| iata_code | char(3) | YES | | NULL | |
| local_code | varchar(7) | YES | | NULL | |
| home_url | varchar(128) | YES | | NULL | |
| wikipedia_url | varchar(128) | YES | | NULL | |
| keywords | varchar(300) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
Pour en savoir plus, consultez le manuel de référence MySQL 8.0.
Structure des tables via le client mysqldump
Il peut être utile de récupérer la définition de toutes les tables d'un schéma donné, ou bien d'obtenir la définition d'une seule table.
$ mysqldump --no-data --compact --set-gtid-purged=OFF \
-h${INSTANCE_ENDPOINT} -u${USER} -p${DBA_PASSWD} \
${DB_NAME} airport
La sortie obtenue est identique à celle de l'instruction SHOW CREATE TABLE. Les lignes de commentaires supplémentaires accompagnant la structure de la table peuvent être ignorées.
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `airport` (
`airport_id` int unsigned NOT NULL,
`ident` varchar(7) NOT NULL,
`type` varchar(20) NOT NULL,
`name` varchar(100) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`elevation_ft` smallint DEFAULT NULL,
`continent_code` char(2) NOT NULL,
`country_code` char(2) NOT NULL,
`region_code` char(7) NOT NULL,
`municipality` varchar(60) NOT NULL,
`scheduled_service` tinyint(1) NOT NULL,
`gps_code` char(4) DEFAULT NULL,
`iata_code` char(3) DEFAULT NULL,
`local_code` varchar(7) DEFAULT NULL,
`home_url` varchar(128) DEFAULT NULL,
`wikipedia_url` varchar(128) DEFAULT NULL,
`keywords` varchar(300) DEFAULT NULL,
PRIMARY KEY (`airport_id`),
KEY `name` (`name`),
KEY `country_code` (`country_code`),
KEY `region_code` (`region_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
Pour en savoir plus, consultez le manuel de référence MySQL 8.0.
Structure des tables via Information Schema
Vous pouvez aussi reproduire une syntaxe proche de DESC à l'aide des tables INFORMATION_SCHEMA. Cet exemple n'est pas exhaustif : il n'a pas vraiment d'intérêt pratique alors que d'autres options existent. Une requête SQL complète devrait gérer les différents formats de types de données, la nullabilité, les jeux de caractères, les collations et l'ensemble des contraintes.
> SELECT column_name, data_type, character_maximum_length, numeric_precision,
numeric_scale, datetime_precision, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'airport'
AND table_name = 'airport'
ORDER BY ordinal_position;
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | IS_NULLABLE | COLUMN_DEFAULT |
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
| airport_id | int | NULL | 10 | 0 | NULL | NO | NULL |
| ident | varchar | 7 | NULL | NULL | NULL | NO | NULL |
| type | varchar | 20 | NULL | NULL | NULL | NO | NULL |
| name | varchar | 100 | NULL | NULL | NULL | NO | NULL |
| latitude | float | NULL | 12 | NULL | NULL | NO | NULL |
| longitude | float | NULL | 12 | NULL | NULL | NO | NULL |
| elevation_ft | smallint | NULL | 5 | 0 | NULL | YES | NULL |
| continent_code | char | 2 | NULL | NULL | NULL | NO | NULL |
| country_code | char | 2 | NULL | NULL | NULL | NO | NULL |
| region_code | char | 7 | NULL | NULL | NULL | NO | NULL |
| municipality | varchar | 60 | NULL | NULL | NULL | NO | NULL |
| scheduled_service | tinyint | NULL | 3 | 0 | NULL | NO | NULL |
| gps_code | char | 4 | NULL | NULL | NULL | YES | NULL |
| iata_code | char | 3 | NULL | NULL | NULL | YES | NULL |
| local_code | varchar | 7 | NULL | NULL | NULL | YES | NULL |
| home_url | varchar | 128 | NULL | NULL | NULL | YES | NULL |
| wikipedia_url | varchar | 128 | NULL | NULL | NULL | YES | NULL |
| keywords | varchar | 300 | NULL | NULL | NULL | YES | NULL |
+-------------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+----------------+
5\. Cardinalité des index
La structure de la table renseigne sur les index définis, mais le QEP est influencé par les statistiques portant sur les colonnes indexées. Pour obtenir une approximation, on peut examiner la cardinalité de chaque colonne d'un index via l'instruction SHOW INDEXES.
> SHOW INDEXES FROM airport;
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| airport | 0 | PRIMARY | 1 | airport_id | A | 86733 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | name | 1 | name | A | 70534 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 1 | country_code | A | 214 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 1 | region_code | A | 3334 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
NOTE : plus la valeur de la colonne cardinality est élevée, mieux c'est pour une instruction SELECT qui cherche à cibler des lignes spécifiques.
Vous pouvez également utiliser le mot-clé EXTENDED pour obtenir des informations supplémentaires, mais cela n'a guère d'utilité pour un utilisateur final.
> SHOW EXTENDED INDEXES FROM airport;
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| airport | 0 | PRIMARY | 1 | airport_id | A | 86733 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 2 | DB_TRX_ID | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 3 | DB_ROLL_PTR | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 4 | ident | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 5 | type | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 6 | name | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 7 | latitude | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 8 | longitude | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 9 | elevation_ft | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 10 | continent_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 11 | country_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 12 | region_code | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 13 | municipality | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 14 | scheduled_service | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 15 | gps_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 16 | iata_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 17 | local_code | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 18 | home_url | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 19 | wikipedia_url | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 0 | PRIMARY | 20 | keywords | A | NULL | NULL | NULL | YES | BTREE | | | YES | NULL |
| airport | 1 | name | 1 | name | A | 70534 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | name | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 1 | country_code | A | 214 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | country_code | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 1 | region_code | A | 3334 | NULL | NULL | | BTREE | | | YES | NULL |
| airport | 1 | region_code | 2 | airport_id | A | NULL | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+--------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
26 rows in set (0.00 sec)
Pour en savoir plus, consultez le manuel de référence MySQL 8.0.
6\. Coût d'exécution de la requête
La commande EXPLAIN ANALYZE peut fournir des informations détaillées sur le coût de chaque étape d'exécution. Très utile pour repérer les étapes coûteuses en ressources, qu'il s'agisse du temps d'exécution ou du nombre de lignes traitées.
mysql > EXPLAIN ANALYZE SELECT ...
-> Limit: 10 row(s) (actual time=350.043..350.045 rows=10 loops=1)
-> Sort: cnt DESC, limit input to 10 row(s) per chunk (actual time=350.043..350.043 rows=10 loops=1)
-> Table scan on <temporary> (actual time=0.003..0.049 rows=245 loops=1)
-> Aggregate using temporary table (actual time=349.935..349.994 rows=245 loops=1)
-> Nested loop inner join (cost=112.60 rows=248) (actual time=0.083..257.689 rows=76475 loops=1)
-> Table scan on c (cost=25.80 rows=248) (actual time=0.051..0.225 rows=248 loops=1)
-> Index lookup on a using country_code (country_code=c.country_code) (cost=0.25 rows=1) (actual time=0.043..0.993 rows=308 loops=248)
Pour en savoir plus, consultez le manuel de référence MySQL 8.0.
7\. Évaluation par l'optimiseur de requêtes
Avant qu'une requête SQL ne soit exécutée par MySQL, elle est d'abord analysée syntaxiquement. La requête analysée est ensuite contrôlée pour vérifier que l'utilisateur dispose des privilèges requis sur les tables et colonnes concernées. Si le cache de requêtes est activé, une correspondance existante est recherchée et les résultats sont renvoyés en cas de correspondance valide. L'optimiseur de requêtes détermine ensuite le chemin d'exécution optimal à partir de diverses évaluations et en tenant compte des capacités du moteur de stockage. La trace de l'optimiseur permet de recueillir des informations détaillées sur les étapes évaluées par l'optimiseur ainsi que sur les statistiques utilisées pour prendre une décision basée sur le coût.
NOTE : ces informations sont très techniques et ne devraient être collectées qu'en vue d'une analyse spécialisée par des ingénieurs hautement qualifiés.
> SET SESSION optimizer_trace="enabled=on";
> SELECT ...
> SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{
"steps": [\
{\
"join_preparation": {\
...\
},\
{\
"join_optimization": {\
"select#": 1,\
"steps": [\
...\
},\
{\
"rows_estimation": [\
{\
"table": "`airport` `a`",\
"table_scan": {\
"rows": 86733,\
"cost": 296.25\
}\
},\
{\
"table": "`country` `c`",\
"table_scan": {\
"rows": 248,\
"cost": 1\
}\
}\
]\
},\
...\
},\
{\
"join_execution": {\
"select#": 1,\
"steps": [\
{\
"temp_table_aggregate": {\
"select#": 1,\
"steps": [\
{\
"creating_tmp_table": {\
"tmp_table_info": {\
"in_plan_at_position": 2,\
"columns": 3,\
"row_length": 218,\
"key_length": 210,\
"unique_constraint": false,\
"makes_grouped_rows": true,\
"cannot_insert_duplicates": false,\
"location": "TempTable"\
}\
}\
}\
]\
}\
},\
{\
"sorting_table": "<temporary>",\
"filesort_information": [\
{\
"direction": "desc",\
"expression": "`cnt`"\
}\
],\
"filesort_priority_queue_optimization": {\
"limit": 10,\
"chosen": true\
},\
"filesort_execution": [\
],\
"filesort_summary": {\
"memory_available": 262144,\
"key_size": 8,\
"row_size": 226,\
"max_rows_per_buffer": 11,\
"num_rows_estimate": 18446744073709551615,\
"num_rows_found": 245,\
"num_initial_chunks_spilled_to_disk": 0,\
"peak_memory_used": 2574,\
"sort_algorithm": "std::sort",\
"unpacked_addon_fields": "using_priority_queue",\
"sort_mode": "<fixed_sort_key, additional_fields>"\
}\
}\
]\
}\
}\
]\
}\
```\
NOTE : la sortie JSON ci-dessus est tronquée car elle s'étend sur plusieurs pages. Une version complète est disponible [ici](https://gist.github.com/ronaldbradford/14f3e253342da7c7ce64756976b690f7).\
Pour en savoir plus, consultez le [manuel de référence MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimizer-trace-table.html).\
8\. Valeurs de statut MySQL\
MySQL expose en interne plus de 300 métriques consultables avec une granularité GLOBAL ou SESSION. La bonne variable de statut permet de valider une action interne déclenchée par la requête à différentes étapes : utilisation des index, qualification du WHERE, tri et regroupement des données. Dans l'exemple simple ci-dessous, où une table temporaire interne a été utilisée, savoir qu'elle a été écrite sur disque s'avère précieux pour déterminer si l'on peut l'optimiser par divers moyens afin de réduire le temps d'exécution.
NOTE : ces informations sont très techniques et ne devraient être collectées qu'en vue d'une analyse spécialisée par des ingénieurs hautement qualifiés sachant quelle métrique retenir et laquelle écarter. Le simple fait de collecter ces informations influe d'ailleurs sur certaines métriques.\
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%tables';\
+-------------------------+-------+\
| Variable_name | Value |\
+-------------------------+-------+\
| Created_tmp_disk_tables | 0 |\
| Created_tmp_tables | 4 |\
+-------------------------+-------+\
mysql> SELECT ...\
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%tables';\
+-------------------------+-------+\
| Variable_name | Value |\
+-------------------------+-------+\
| Created_tmp_disk_tables | 1 |\
| Created_tmp_tables | 5 |\
+-------------------------+-------+\
```\
Pour en savoir plus, consultez le [manuel de référence MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/show-status.html).\
9\. Profiling de la requête\
Lorsque le profiling est activé pour une requête, vous pouvez examiner chacune de ses étapes internes et la mettre en regard du code source de MySQL. Dans la sortie ci-dessous, on voit que l'essentiel du temps d'exécution s'est concentré sur une seule étape : la ligne 14, à 0,234060 seconde. La plupart des requêtes suivront une distribution similaire du temps, mais les requêtes plus complexes feront apparaître des étapes supplémentaires.
NOTE : ces informations sont très techniques et ne devraient être collectées qu'en vue d'une analyse spécialisée par des ingénieurs hautement qualifiés. Aucun détail supplémentaire de cette analyse n'est décrit ici.\
mysql > SET @@profiling=1;\
mysql > SELECT ...\
mysql > SET @@profiling=0;\
mysql > SHOW PROFILES;\
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE query_id=1;\
```\
```\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
| 9 | 2 | starting | 0.000125 | 0.000130 | 0.000021 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |\
| 9 | 3 | Executing hook on transaction | 0.000010 | 0.000009 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1376 |\
| 9 | 4 | starting | 0.000011 | 0.000010 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1378 |\
| 9 | 5 | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 2303 |\
| 9 | 6 | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 2303 |\
| 9 | 7 | Opening tables | 0.000180 | 0.000156 | 0.000025 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | open_tables | sql_base.cc | 5803 |\
| 9 | 8 | init | 0.000008 | 0.000006 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 570 |\
| 9 | 9 | System lock | 0.000009 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 331 |\
| 9 | 10 | optimizing | 0.000012 | 0.000011 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 344 |\
| 9 | 11 | statistics | 0.000027 | 0.000023 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 663 |\
| 9 | 12 | preparing | 0.000017 | 0.000014 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 747 |\
| 9 | 13 | Creating tmp table | 0.000047 | 0.000041 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | create_intermediate_table | sql_executor.cc | 195 |\
| 9 | 14 | executing | 0.234060 | 0.202675 | 0.000000 | 65 | 3 | 0 | 0 | 0 | 0 | 0 | 1222 | 0 | ExecuteIteratorQuery | sql_union.cc | 1130 |\
| 9 | 15 | end | 0.000022 | 0.000020 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 603 |\
| 9 | 16 | query end | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command_internal | sql_parse.cc | 6372 |\
| 9 | 17 | waiting for handler commit | 0.000231 | 0.000231 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1707 |\
| 9 | 18 | closing tables | 0.000018 | 0.000021 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | mysql_execute_command_internal | sql_parse.cc | 6450 |\
| 9 | 19 | freeing items | 0.000018 | 0.000015 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | finish_dispatch_sql_command | sql_parse.cc | 6971 |\
| 9 | 20 | logging slow query | 0.000150 | 0.000150 | 0.000000 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 4 | 0 | log_slow_do | log.cc | 1784 |\
| 9 | 21 | cleaning up | 0.000010 | 0.000010 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | finish_dispatch_command | sql_parse.cc | 3409 |\
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+\
20 rows in set, 1 warning (0.00 sec)\
```\
Pour en savoir plus, consultez le [manuel de référence MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html).\
10\. Processlist\
Pendant l'exécution d'une requête SQL, la processlist permet d'observer les colonnes State et Time d'une requête au long cours. Elle aide aussi à mesurer l'impact d'une instruction sur les autres requêtes concurrentes, par exemple en cas de verrouillage. Consultez Capturing running SQL statements pour en savoir plus sur la collecte de la processlist via différentes méthodes.\
À propos de la précision des valeurs\
Beaucoup des outils d'analyse présentés ici produisent des résultats variables selon les situations. Certaines valeurs doivent rester précises à chaque exécution, par exemple le nombre de lignes renvoyées ou la structure de la table.
D'autres devraient rester quasiment identiques d'une exécution à l'autre : le QEP peut, par exemple, afficher le même nombre d'étapes, mais une colonne peut contenir des valeurs différentes. Le QEP peut également varier selon les constantes utilisées dans la requête SQL, la version de MySQL ou des modifications de configuration.
D'autres valeurs ne seront que rarement identiques : le temps d'exécution haute précision, le coût de la requête ou les étapes individuelles du profiling doivent être moyennés, et un percentile approprié doit être utilisé pour identifier des exécutions comparables.\
Conclusion\
Il existe plusieurs méthodes natives, à l'aide des outils clients MySQL, pour collecter les informations contextuelles lors de l'exécution de requêtes SQL. Ces données sont essentielles pour analyser et optimiser ces requêtes.
Cet article fait suite à Capturing running SQL statements, qui détaille les différentes techniques permettant de capturer l'intégralité des requêtes SQL exécutées.\
Annexe\
Jeu de données utilisé\
Pour cet article, le jeu de données airport employé est disponible à l'adresse https://github.com/ronaldbradford/data/tree/main/mysql-data/airport\
Requête SQL utilisée\
Pour chacune des méthodes de collecte, la requête SQL d'exemple suivante a été employée.\
SELECT a.country_code, c.name, COUNT(*) AS cnt\
FROM airport a\
INNER JOIN country c USING (country_code)\
GROUP BY country_code, name ORDER BY 3 DESC LIMIT 10;\
```\
### Cette requête SQL renvoie un ensemble de résultats du type :\
```\
+--------------+----------------+-------+\
| country_code | name | cnt |\
+--------------+----------------+-------+\
| US | United States | 30581 |\
| BR | Brazil | 6849 |\
| JP | Japan | 3430 |\
| CA | Canada | 3075 |\
| AU | Australia | 2576 |\
| MX | Mexico | 2288 |\
| RU | Russia | 1556 |\
| KR | South Korea | 1400 |\
| GB | United Kingdom | 1398 |\
| DE | Germany | 1042 |\
+--------------+----------------+-------+\
10 rows in set (0.47 sec)\
```\
NOTE : le décompte réel des aéroports et l'ordre des pays peuvent varier selon la date à laquelle vous avez récupéré le jeu de données.\
Une remarque sur les accents graves (\`)\
Dans de nombreux exemples, vous verrez l'ajout du caractère accent grave (`) autour des noms d'objets. C'est un héritage de la prise en charge des mots réservés, qui doivent obligatoirement être encadrés par des accents graves. Les outils n'appliquent toutefois pas cette syntaxe seulement quand elle s'avère nécessaire : ils l'ajoutent à tous les objets de type table et colonne, ce qui nuit à la lisibilité.\