Immagine di NicoElNino / Shutterstock
Catturare le query SQL con MySQL
L'articolo precedente Capturing running SQL statements tratta una componente fondamentale dell'analisi delle performance applicative quando si lavora con un database relazionale (RDBMS). Per supportare l'analisi di una query SQL con MySQL è altrettanto importante raccogliere ulteriori metainformazioni che ne forniscano il contesto adeguato. Si tratta della struttura delle tabelle coinvolte, delle statistiche sui dati e sulla struttura, oltre ai tempi delle varie fasi di preparazione ed esecuzione della query SQL. Le informazioni vengono inoltre raccolte in momenti diversi: prima, durante e dopo l'esecuzione della query.
Raccogliere informazioni aggiuntive sulle query
In MySQL, le seguenti informazioni di supporto agevolano la validazione, la valutazione e il tuning delle query SQL applicative. Si possono raccogliere usando gli strumenti standard di MySQL.
- Righe restituite (dopo l'esecuzione)
- Tempo di esecuzione (dopo l'esecuzione)
- Query Execution Plan (QEP) (prima dell'esecuzione)
- Struttura della tabella (prima dell'esecuzione)
- Cardinalità degli indici (prima dell'esecuzione)
- Costo di esecuzione della query (prima dell'esecuzione)
- Valutazione del query optimizer (prima dell'esecuzione)
- Valori di stato (prima, durante e dopo l'esecuzione)
- Profiling della query (dopo l'esecuzione)
- Processlist (durante l'esecuzione)
Salvo diversa indicazione, negli esempi che seguono viene utilizzato il client a riga di comando mysql.
NOTA: gli esempi SQL riportati sono compatibili sia con MySQL 5.7 sia con MySQL 8.0. Poiché la community edition di MySQL 5.7 ha raggiunto il fine vita (EOL), tutti gli output di esempio e i riferimenti alla documentazione si basano sulla versione attuale MySQL 8.0. Alcuni output mostrati in questo articolo possono differire da quelli ottenuti con MySQL 5.7.
1\. Righe restituite
Righe restituite con il client MySQL
Con il client mysql puoi eseguire una query SQL e il numero di righe restituite viene riportato in un'istruzione di stato dopo i risultati. Il dato però va estratto insieme a tutto il contenuto catturato.
mysql> SELECT ...
10 rows in set ...
Righe restituite tramite slow query log
Lo slow query log riporta le righe restituite tramite il valore `Rows_sent` per ogni query SQL registrata.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
L'articolo Capturing running SQL statements spiega in dettaglio come configurare, utilizzare e analizzare lo slow query log di MySQL.
Righe restituite tramite gli schemi performance/sys
Puoi ottenere informazioni su rows_sent dagli schemi PERFORMANCE_SCHEMA e sys; di norma, però, si tratta di risultati aggregati. L'esempio seguente mostra exec_count=1 e quindi un valore rows_sent accurato.
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\. Tempo di esecuzione della query
Tempo di esecuzione tramite il client MySQL
Così come indica il numero di righe restituite, il client mysql mostra anche il tempo totale di esecuzione, con una granularità di 10 millisecondi.
mysql> SELECT ...
10 rows in set (0.28 sec)
Tempo di esecuzione tramite profiling
Con il profiling puoi ottenere il tempo totale di esecuzione di una query SQL con elevata precisione.
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;
NOTA: se esegui altre istruzioni SELECT, potresti dover modificare il valore di query_id in modo che corrisponda alla query di cui vuoi conoscere il tempo di esecuzione.
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 |
+----------------+
L'uso del profiling è descritto in modo più approfondito in una sezione successiva.
Tempo di esecuzione tramite slow query log
Quando le query vengono registrate nello slow query log puoi conoscere il tempo di esecuzione della query SQL con elevata precisione tramite il valore Query_time.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
3\. Query Execution Plan (QEP)
L'istruzione EXPLAIN produce il QEP, che contiene informazioni preziose sul modo in cui MySQL eseguirà la query SQL. Il QEP mostra in sequenza i singoli passaggi di esecuzione della query e fornisce ulteriori dettagli, tra cui gli indici valutati, le partizioni considerate, il tipo di join utilizzati, la stima delle righe e altre informazioni nella colonna 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'istruzione EXPLAIN genera sempre 1 warning, che corrisponde alla query riscritta per l'esecuzione interna. Se i warning sono più di uno, conviene analizzarli prima di proseguire.
> 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
Esiste anche il formato JSON, più recente del layout tabellare tradizionale (FORMAT=TRADITIONAL), che restituisce inoltre dati cost_info aggiuntivi.
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"\
]\
}\
}\
]
}
}
}
}
Per approfondire consulta il MySQL 8.0 Reference Manual.
Più avanti trovi un esempio dell'opzione EXPLAIN ANALYZE, che illustra anche il layout FORMAT=TREE.
4\. Struttura della tabella
Struttura della tabella tramite il client mysql (1)
Per ogni tabella coinvolta in una query SQL è importante raccogliere informazioni su colonne e vincoli. Puoi usare la sintassi 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)
NOTA: il vantaggio di questa sintassi è che dall'output ottenuto puoi ricreare la tabella.
Per approfondire consulta il MySQL 8.0 Reference Manual.
Struttura della tabella tramite il client mysql (2)
Una tabella può essere descritta anche con l'istruzione SQL DESC. Lo svantaggio è che con questo output non puoi ricreare la tabella e non riesci a determinare correttamente colonne e posizioni degli indici.
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)
Per approfondire consulta il MySQL 8.0 Reference Manual.
Struttura della tabella tramite il client mysqldump
Può essere utile recuperare la definizione di tutte le tabelle di un determinato schema, oppure ottenere la definizione di una singola tabella.
$ mysqldump --no-data --compact --set-gtid-purged=OFF \
-h${INSTANCE_ENDPOINT} -u${USER} -p${DBA_PASSWD} \
${DB_NAME} airport
Il risultato è identico a quello dell'istruzione SHOW CREATE TABLE. Puoi ignorare le righe di commento aggiuntive che accompagnano la struttura della tabella.
/*!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 */;
Per approfondire consulta il MySQL 8.0 Reference Manual.
Struttura della tabella tramite Information Schema
È possibile riprodurre una sintassi simile a DESC anche tramite le tabelle INFORMATION_SCHEMA. L'esempio non è completo perché non ha utilità pratica, dato che esistono altre opzioni. Una query SQL completa dovrebbe gestire formati diversi dei tipi di dati, nullabilità, character set, collation e tutti i vincoli.
> 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à degli indici
La struttura della tabella fornisce informazioni sugli indici definiti, ma il QEP risente delle statistiche sulle colonne indicizzate. Una tecnica per ottenerne un'approssimazione è esaminare la cardinalità di ogni colonna di un indice con l'istruzione 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)
NOTA: più alto è il valore nella colonna cardinality, migliore è il comportamento di un'istruzione SELECT che cerca righe specifiche.
Puoi usare anche la parola chiave EXTENDED per ottenere informazioni aggiuntive, ma in pratica non è utile per un utente finale.
> 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)
Per approfondire consulta il MySQL 8.0 Reference Manual.
6\. Costo di esecuzione della query
Il comando EXPLAIN ANALYZE fornisce informazioni dettagliate sul costo di ogni passaggio di esecuzione della query. È molto utile per individuare i passaggi più onerosi in termini di risorse, ad esempio tempo di esecuzione e righe elaborate.
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)
Per approfondire consulta il MySQL 8.0 Reference Manual.
7\. Valutazione del query optimizer
Prima di essere eseguita in MySQL, una query SQL viene innanzitutto analizzata sintatticamente. Si verifica poi che l'utente che la esegue disponga dei privilegi necessari sulle tabelle e sulle colonne coinvolte. Se la cache delle query è abilitata, viene cercata una corrispondenza già presente e, in caso di match valido, vengono restituiti direttamente i risultati. A quel punto il query optimizer determina il percorso ideale per eseguire la query, valutando vari aspetti e tenendo conto delle caratteristiche dello storage engine delle tabelle. L'optimizer trace consente di raccogliere informazioni dettagliate sui passaggi valutati dall'optimizer e sulle statistiche utilizzate per prendere una decisione basata sui costi.
NOTA: queste informazioni sono molto tecniche e dovrebbero essere raccolte solo per valutazioni specialistiche, condotte da personale altamente qualificato.
> 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>"\
}\
}\
]\
}\
}\
]\
}\
```\
NOTA: l'output JSON sopra è troncato perché si estende per diverse pagine. Puoi trovare la versione completa [qui](https://gist.github.com/ronaldbradford/14f3e253342da7c7ce64756976b690f7).\
Per approfondire consulta il [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimizer-trace-table.html).\
8\. Valori di stato di MySQL\
MySQL gestisce internamente oltre 300 metriche, consultabili a livello GLOBAL o SESSION. Usando le variabili di stato corrette puoi validare le azioni interne svolte dalla query nelle varie fasi di esecuzione, tra cui l'utilizzo degli indici, la qualificazione delle clausole WHERE, l'ordinamento e il raggruppamento dei dati. Nel semplice esempio seguente, in cui è stata utilizzata una tabella temporanea interna, sapere che è stata scritta su disco è importante per capire se l'aspetto può essere ottimizzato in vari modi per ridurre il tempo di esecuzione.
NOTA: queste informazioni sono molto tecniche e dovrebbero essere raccolte solo per valutazioni specialistiche, condotte da personale altamente qualificato che sappia quali metriche utilizzare e quali scartare. Anche l'atto stesso di raccogliere informazioni incide su alcune metriche.\
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 |\
+-------------------------+-------+\
```\
Per approfondire consulta il [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/show-status.html).\
9\. Profiling delle query\
Quando il profiling è abilitato per una query puoi esaminare ogni passaggio interno e correlarlo al codice sorgente di MySQL. Nell'output seguente noterai che la maggior parte del tempo di esecuzione è concentrata in un singolo passaggio: la riga 14 ha richiesto 0,234060 secondi. La maggior parte delle query segue una distribuzione del tempo simile, mentre quelle più complesse mostrano passaggi aggiuntivi.
NOTA: queste informazioni sono molto tecniche e dovrebbero essere raccolte solo per valutazioni specialistiche, condotte da personale altamente qualificato. Non vengono qui forniti ulteriori dettagli su tale analisi.\
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)\
```\
Per approfondire consulta il [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html).\
10\. Processlist\
Durante l'esecuzione di una query SQL, la processlist è utile per osservare lo State e il Time di una query particolarmente lunga. Permette inoltre di valutarne l'impatto sulle istruzioni concorrenti, ad esempio in termini di locking. Consulta Capturing running SQL statements per i dettagli su come raccogliere la processlist con vari metodi.\
Avvertenza sulla precisione dei valori\
Molti degli strumenti di analisi descritti producono risultati variabili in situazioni differenti. Alcuni valori dovrebbero rimanere precisi a ogni esecuzione, ad esempio il numero di righe restituite o la struttura della tabella.
Altri output dovrebbero rimanere pressoché costanti tra esecuzioni ripetute: il QEP, ad esempio, può comprendere lo stesso numero di passaggi, ma una colonna può contenere valori diversi. Un QEP può cambiare a seconda dei valori costanti utilizzati nella query SQL, di una versione differente di MySQL o di modifiche alla configurazione di MySQL.
Altri valori risulteranno raramente identici: il tempo di esecuzione ad alta precisione, il costo della query o i singoli passaggi del profiling, ad esempio, andrebbero mediati e occorre utilizzare un valore percentile appropriato per identificare esecuzioni simili.\
Conclusioni\
Esistono diversi metodi nativi che, tramite gli strumenti del client MySQL, consentono di raccogliere informazioni di supporto durante l'esecuzione delle query SQL. Si tratta di informazioni fondamentali per analizzare e ottimizzare le query SQL.
Questo articolo fa seguito a Capturing running SQL statements, che illustra in dettaglio le diverse tecniche per catturare query SQL complete e utilizzabili.\
Appendice\
Dati di esempio utilizzati\
Per questo articolo è stato utilizzato il dataset airport, disponibile all'indirizzo https://github.com/ronaldbradford/data/tree/main/mysql-data/airport\
SQL di esempio utilizzato\
Per ciascun metodo di raccolta è stata utilizzata la seguente query SQL di esempio.\
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;\
```\
### Questa query SQL restituisce un set di risultati simile al seguente:\
```\
+--------------+----------------+-------+\
| 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)\
```\
NOTA: il numero effettivo di aeroporti e l'ordinamento dei Paesi possono variare a seconda di quando è stato scaricato il dataset.\
Una nota sui backquote (\`)\
In molti esempi noterai l'uso del carattere backquote (`) attorno ai nomi degli oggetti. È un retaggio della possibilità di utilizzare parole riservate, che vanno racchiuse tra backquote. Gli strumenti, però, non aggiungono questa sintassi solo quando è necessaria, ma per tutti gli oggetti tabella e colonna, complicando la leggibilità.\