Imagem por NicoElNino / Shutterstock
Capturando suas SQL statements no MySQL
O artigo anterior, Capturando SQL statements em execução, trata de um componente importante da análise de performance de aplicações que usam um banco de dados relacional (RDBMS). Para apoiar a análise de qualquer SQL statement no MySQL, também é importante reunir metadados adicionais que ofereçam o contexto necessário. Isso inclui as estruturas das tabelas envolvidas, estatísticas sobre os dados e a estrutura das tabelas, e o tempo de cada etapa na preparação e execução da SQL statement. Essas informações também são coletadas em momentos diferentes: antes, durante e depois da execução da SQL statement.
Coleta de informações adicionais sobre a statement
No MySQL, as informações de apoio a seguir ajudam na validação, na avaliação e no tuning das SQL statements da sua aplicação. Esses dados podem ser coletados com as ferramentas padrão do MySQL.
- Linhas retornadas (após a execução)
- Tempo de execução (após a execução)
- Query Execution Plan (QEP) (antes da execução)
- Estrutura da tabela (antes da execução)
- Cardinalidade dos índices (antes da execução)
- Custo de execução da query (antes da execução)
- Avaliação do query optimizer (antes da execução)
- Valores de status (antes, durante e após a execução)
- Profiling da query (após a execução)
- Processlist (durante a execução)
Salvo indicação em contrário, o cliente de linha de comando mysql é usado nos exemplos de saída a seguir.
OBSERVAÇÃO: os exemplos de SQL apresentados aqui são compatíveis com MySQL 5.7 e MySQL 8.0. Devido ao end-of-life (EOL) da edição community do MySQL 5.7, todos os exemplos de saída e as referências de documentação utilizam a versão atual do MySQL 8.0. Algumas saídas mostradas neste artigo podem diferir do que é produzido no MySQL 5.7.
1\. Linhas retornadas
Linhas retornadas no cliente MySQL
Com o cliente mysql, você executa uma query SQL e o número de linhas retornadas aparece em uma mensagem de status logo após o resultado. No entanto, esse texto precisa ser parseado junto com todo o conteúdo capturado.
mysql> SELECT ...
10 rows in set ...
Linhas retornadas pelo slow query log
O slow query log informa o número de linhas retornadas pelo valor `Rows_sent` para cada SQL statement registrada.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
O artigo Capturando SQL statements em execução descreve em detalhes como configurar, usar e revisar o slow query log do MySQL.
Linhas retornadas pelo performance/sys schema
Você também consegue obter informações sobre rows_sent nos schemas PERFORMANCE_SCHEMA e sys; entretanto, geralmente são resultados agregados. O exemplo a seguir mostra um exec_count=1 e, portanto, um valor preciso em rows_sent.
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 de execução da query
Tempo de execução pelo cliente MySQL
Da mesma forma que mostra o número de linhas retornadas, o cliente mysql também exibe o tempo total de execução, com granularidade de 10 milissegundos.
mysql> SELECT ...
10 rows in set (0.28 sec)
Tempo de execução via profiling
Com profiling habilitado, dá para obter o tempo total de execução de uma SQL statement com alta precisão.
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;
OBSERVAÇÃO: se você executar outras SELECTs, talvez seja preciso ajustar o valor de query_id para que ele corresponda à query cujo tempo de execução você quer medir.
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 |
+----------------+
O uso de profiling será detalhado mais adiante.
Tempo de execução pelo slow query log
Quando as queries são gravadas no slow query log, o valor Query_time traz o tempo de execução da SQL statement com alta precisão.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
3\. Query Execution Plan (QEP)
A statement EXPLAIN gera o QEP, que traz informações valiosas sobre como o MySQL vai executar a SQL statement. O QEP mostra, em ordem, cada etapa da execução da query e fornece informações adicionais, como índices avaliados, partições consideradas, o tipo de joins, estimativas de linhas e outros detalhes na coluna 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)
A statement EXPLAIN sempre gera 1 warning, que é a statement reescrita para a execução interna. Se houver mais de 1 warning, vale revisar todos antes de seguir.
> 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
Há também o formato JSON, mais recente, como alternativa ao layout de tabela tradicional (FORMAT=TRADITIONAL), que ainda traz dados adicionais em cost_info.
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"\
]\
}\
}\
]
}
}
}
}
Para mais informações, consulte o MySQL 8.0 Reference Manual.
Mais adiante, há um exemplo da opção EXPLAIN ANALYZE, que também demonstra o layout FORMAT=TREE.
4\. Estrutura da tabela
Estrutura da tabela pelo cliente mysql (1)
Para cada tabela em uma SQL statement, vale reunir informações sobre as colunas e as constraints. Para isso, use a sintaxe 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)
OBSERVAÇÃO: a vantagem dessa sintaxe é que dá para recriar a tabela a partir da saída.
Para mais informações, consulte o MySQL 8.0 Reference Manual.
Estrutura da tabela pelo cliente mysql (2)
Você também pode descrever uma tabela com a SQL statement DESC. A desvantagem é que não dá para recriar a tabela a partir dessa saída, e também não é possível identificar corretamente as colunas e posições dos índices.
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)
Para mais informações, consulte o MySQL 8.0 Reference Manual.
Estrutura da tabela pelo cliente mysqldump
Pode ser útil obter a definição de todas as tabelas de um determinado schema — ou apenas de uma única tabela.
$ mysqldump --no-data --compact --set-gtid-purged=OFF \
-h${INSTANCE_ENDPOINT} -u${USER} -p${DBA_PASSWD} \
${DB_NAME} airport
O resultado é idêntico ao da statement SHOW CREATE TABLE. Você pode ignorar as linhas de comentário extras que vêm junto com a estrutura da tabela.
/*!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 */;
Para mais informações, consulte o MySQL 8.0 Reference Manual.
Estrutura da tabela pelo Information Schema
Também é possível reproduzir uma sintaxe parecida com a do DESC usando as tabelas do INFORMATION_SCHEMA. Este não é um exemplo completo, já que não há motivo prático para usá-lo quando existem outras opções. Uma SQL statement completa precisaria contemplar diferentes formatos de tipos de dados, nullability, conjuntos de caracteres, collations e todas as constraints.
> 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\. Cardinalidade dos índices
A estrutura da tabela traz informações sobre os índices definidos, mas o QEP é influenciado por estatísticas das colunas indexadas. Uma forma de obter uma aproximação é olhar a cardinalidade de cada coluna de um índice usando a statement 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)
OBSERVAÇÃO: quanto maior o número na coluna de cardinalidade, melhor para uma SELECT statement que precisa localizar linhas específicas.
Você também pode usar a palavra-chave EXTENDED para obter mais informações, mas isso não é prático para o usuário 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)
Para mais informações, consulte o MySQL 8.0 Reference Manual.
6\. Custo de execução da query
O comando EXPLAIN ANALYZE traz informações detalhadas de custo para cada etapa da execução da query. Isso é muito útil para identificar quais etapas são as mais caras em recursos, como tempo de execução e número de linhas processadas.
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)
Para mais informações, consulte o MySQL 8.0 Reference Manual.
7\. Avaliação do query optimizer
Antes de executar uma query SQL no MySQL, ela primeiro é parseada. Em seguida, o sistema verifica se o usuário em execução tem os privilégios necessários sobre as tabelas e colunas. Se o cache de queries estiver habilitado, a query é comparada com correspondências existentes e, se houver uma válida, os resultados são retornados. Em seguida, o query optimizer determina o melhor caminho para executar a query, fazendo várias avaliações e considerando os recursos do storage engine da tabela. O optimizer trace permite reunir informações detalhadas sobre as etapas avaliadas pelo otimizador e sobre as estatísticas usadas para tomar uma decisão baseada em custo.
OBSERVAÇÃO: essas informações são altamente técnicas e devem ser coletadas apenas para análise especializada por profissionais altamente qualificados.
> 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>"\
}\
}\
]\
}\
}\
]\
}\
```\
OBSERVAÇÃO: a saída JSON acima está truncada, pois ocupa várias páginas. A versão completa está disponível [aqui](https://gist.github.com/ronaldbradford/14f3e253342da7c7ce64756976b690f7).\
Para mais informações, consulte o [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimizer-trace-table.html).\
8\. Valores de status do MySQL\
Internamente, o MySQL tem mais de 300 métricas que podem ser analisadas em granularidade GLOBAL ou SESSION. Usar a variável de status correta permite validar ações internas executadas durante a query em diferentes estágios — uso de índice, qualificação por where, ordenação e agrupamento de dados. No exemplo simples a seguir, em que uma tabela temporária interna foi usada, saber que ela foi gravada em disco é importante para avaliar se isso pode ser ajustado de várias formas para reduzir o tempo de execução.
OBSERVAÇÃO: essas informações são altamente técnicas e devem ser coletadas apenas para análise especializada por profissionais altamente qualificados, que saibam quais métricas usar e quais descartar. O próprio ato de coletar informações também impacta algumas métricas.\
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 |\
+-------------------------+-------+\
```\
Para mais informações, consulte o [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/show-status.html).\
9\. Profiling de query\
Com profiling habilitado para uma query, dá para revisar cada etapa interna e cruzar essas informações com o código-fonte do MySQL. Na saída a seguir, é possível ver que a maior parte do tempo de execução foi consumida em uma única etapa — a linha 14, por exemplo, levou 0,234060 segundos. A maioria das queries segue uma distribuição de tempo parecida; já queries mais complexas mostram etapas adicionais.
OBSERVAÇÃO: essas informações são altamente técnicas e devem ser coletadas apenas para análise especializada por profissionais altamente qualificados. Nenhum detalhe adicional dessa análise é descrito aqui.\
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)\
```\
Para mais informações, consulte o [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html).\
10\. Processlist\
Durante a execução de uma SQL statement, a processlist é útil para visualizar o State e o Time de uma query mais demorada. Ela também ajuda a observar o impacto da statement em outras statements concorrentes — por exemplo, situações de locking. Veja em Capturando SQL statements em execução como obter a processlist por diferentes métodos.\
Atenção à precisão dos valores\
Muitas das ferramentas de análise descritas aqui produzem resultados que variam conforme a situação. Alguns valores devem permanecer precisos a cada execução — o número de linhas retornadas ou a estrutura da tabela, por exemplo.
Outros tendem a se manter praticamente consistentes em execuções repetidas: o QEP, por exemplo, costuma trazer o mesmo número de etapas, mas pode haver diferença em uma ou outra coluna. Um QEP também pode mudar dependendo dos valores constantes usados na SQL statement, da versão do MySQL ou de mudanças na configuração.
Já outros raramente serão idênticos: o tempo de execução de alta precisão, o custo da query ou as etapas individuais do profiling devem ser tratados pela média, com um percentil adequado para identificar execuções similares.\
Conclusão\
Existem vários métodos nativos das ferramentas do cliente MySQL para coletar informações de apoio durante a execução de SQL statements. Esses dados são essenciais para analisar e otimizar SQL statements.
Este artigo dá continuidade a Capturando SQL statements em execução, que detalha as diferentes técnicas para capturar SQL statements completas.\
Apêndice\
Dados de exemplo utilizados\
Para este artigo, o dataset de aeroportos utilizado está disponível em https://github.com/ronaldbradford/data/tree/main/mysql-data/airport\
SQL de exemplo utilizado\
Em cada método de coleta foi usada a seguinte SQL statement de exemplo.\
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;\
```\
### Esta SQL statement retorna um conjunto de resultados como:\
```\
+--------------+----------------+-------+\
| 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)\
```\
OBSERVAÇÃO: as contagens reais de aeroportos e a ordem dos países podem variar conforme a data em que você obteve o dataset.\
Uma observação sobre as crases (\`)\
Em muitos exemplos, você vai notar o uso de crases (`) ao redor dos nomes de objetos. Isso é um artefato do suporte a palavras reservadas, que precisam ser delimitadas por crases. As ferramentas, porém, não aplicam essa sintaxe somente quando é necessário: elas a usam em todos os objetos de tabela e coluna, o que torna a leitura mais complexa.\