Imagen de NicoElNino / Shutterstock
Cómo capturar tus sentencias SQL con MySQL
El artículo anterior, Capturando sentencias SQL en ejecución, aborda un componente clave del análisis de rendimiento de aplicaciones cuando se usa una base de datos relacional (RDBMS). Para apoyar el análisis de cualquier sentencia SQL en MySQL, también es importante reunir metainformación adicional que aporte el contexto necesario. Esto incluye la estructura de las tablas utilizadas, las estadísticas sobre los datos y la estructura de la tabla, y los tiempos de cada paso en la preparación y ejecución de la sentencia SQL. La información se recopila además en distintos momentos: antes, durante y después de la ejecución de la sentencia SQL.
Recopilación de información adicional sobre la sentencia
En MySQL, la siguiente información de apoyo ayuda a validar, evaluar y afinar las sentencias SQL de tu aplicación. Esta información se puede recopilar usando las herramientas predeterminadas de MySQL.
- Filas devueltas (después de la ejecución)
- Tiempo de ejecución (después de la ejecución)
- Plan de ejecución de la consulta (QEP) (antes de la ejecución)
- Estructura de la tabla (antes de la ejecución)
- Cardinalidad del índice (antes de la ejecución)
- Costo de ejecución de la consulta (antes de la ejecución)
- Evaluación del optimizador de consultas (antes de la ejecución)
- Valores de estado (antes, durante y después de la ejecución)
- Profiling de la consulta (después de la ejecución)
- Processlist (durante la ejecución)
Salvo que se indique lo contrario, en los siguientes ejemplos se utiliza el cliente de línea de comandos mysql.
NOTA: Los ejemplos de SQL que se muestran aquí son compatibles tanto con MySQL 5.7 como con MySQL 8.0. Debido al fin de vida útil (EOL) de la edición community de MySQL 5.7, todas las salidas y referencias a la documentación corresponden a la versión actual MySQL 8.0. Algunas salidas mostradas en este artículo pueden diferir de las que produce MySQL 5.7.
1\. Filas devueltas
Filas devueltas con el cliente MySQL
Con el cliente mysql puedes ejecutar una consulta SQL y el número de filas devueltas se incluye en una sentencia de estado tras los resultados. Sin embargo, esto debe parsearse junto con todo el contenido capturado.
mysql> SELECT ...
10 rows in set ...
Filas devueltas a través del slow query log
El slow query log proporciona las filas devueltas mediante el valor `Rows_sent` para cada sentencia SQL registrada.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
El artículo Capturando sentencias SQL en ejecución describe en detalle cómo configurar, usar y revisar el slow query log de MySQL.
Filas devueltas a través de performance/sys schema
Puedes obtener información sobre rows_sent desde los esquemas PERFORMANCE_SCHEMA y sys, aunque por lo general se trata de resultados agregados. El siguiente ejemplo muestra un exec_count=1 y, por lo tanto, una respuesta precisa de 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\. Tiempo de ejecución de la consulta
Tiempo de ejecución desde el cliente MySQL
Igual que el cliente mysql indica el número de filas devueltas, también muestra el tiempo total de ejecución con una granularidad de 10 milisegundos.
mysql> SELECT ...
10 rows in set (0.28 sec)
Tiempo de ejecución mediante profiling
Con el profiling puedes obtener un tiempo total de ejecución de alta precisión para una sentencia 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;
NOTA: Si ejecutas sentencias SELECT adicionales, puede que tengas que modificar el valor de query_id para que coincida con la consulta cuyo tiempo de ejecución quieres 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 |
+----------------+
El uso del profiling se describe con más detalle más adelante.
Tiempo de ejecución a través del slow query log
Cuando las consultas se escriben en el slow query log, puedes obtener un tiempo de ejecución de alta precisión de la sentencia SQL mediante el valor Query_time.
# Query_time: 0.473775 Lock_time: 0.000751 Rows_sent: 10 Rows_examined: 152960
3\. Plan de ejecución de la consulta (QEP)
La sentencia EXPLAIN produce el QEP, que contiene información valiosa sobre cómo MySQL ejecutará la sentencia SQL. El QEP muestra en orden los pasos individuales para ejecutar la consulta y aporta información adicional como los índices evaluados, las particiones consideradas, el tipo de joins utilizados, las estimaciones de filas e información extra que aparece en la columna 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)
La sentencia EXPLAIN siempre genera 1 advertencia, que es la sentencia reescrita para su ejecución interna. Si aparece más de 1 advertencia, conviene revisarlas antes de continuar.
> 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
También está el formato JSON, más reciente que el formato tradicional de tabla (FORMAT=TRADITIONAL), que además genera datos adicionales de 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 más información, consulta el Manual de Referencia de MySQL 8.0.
Más adelante encontrarás un ejemplo de la opción EXPLAIN ANALYZE, que también muestra el formato FORMAT=TREE.
4\. Estructura de la tabla
Estructura de la tabla desde el cliente mysql (1)
Para cada tabla incluida en una sentencia SQL es importante reunir información sobre sus columnas y restricciones. Puedes usar la sintaxis 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: La ventaja de esta sintaxis es que puedes recrear la tabla a partir de la salida.
Para más información, consulta el Manual de Referencia de MySQL 8.0.
Estructura de la tabla desde el cliente mysql (2)
También puedes describir una tabla con la sentencia SQL DESC. La desventaja es que con esta salida no puedes recrear la tabla ni determinar correctamente las columnas y posiciones de los í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 más información, consulta el Manual de Referencia de MySQL 8.0.
Estructura de la tabla con el cliente mysqldump
Puede resultarte útil obtener la definición de todas las tablas de un esquema dado, o bien la definición de una sola tabla.
$ mysqldump --no-data --compact --set-gtid-purged=OFF \
-h${INSTANCE_ENDPOINT} -u${USER} -p${DBA_PASSWD} \
${DB_NAME} airport
Esto produce una salida idéntica a la de la sentencia SHOW CREATE TABLE. Puedes ignorar las líneas de comentarios adicionales que acompañan a la estructura de la tabla.
/*!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 más información, consulta el Manual de Referencia de MySQL 8.0.
Estructura de la tabla mediante Information Schema
También puedes reproducir una sintaxis similar a DESC usando las tablas INFORMATION_SCHEMA. Este no es un ejemplo completo, ya que no aporta un fin práctico cuando existen otras opciones. La sentencia SQL completa tendría que añadir soporte para distintos formatos de tipos de datos, nullability, conjuntos de caracteres, collations y todas las restricciones.
> 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\. Cardinalidad del índice
La estructura de la tabla aporta información sobre los índices definidos, pero el QEP se ve influido por las estadísticas de las columnas indexadas. Una técnica para obtener una aproximación es revisar la cardinalidad de cada columna de un índice con la sentencia 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: Cuanto mayor sea el número en la columna de cardinalidad, mejor para una sentencia SELECT que busca filas específicas.
También puedes usar la palabra clave EXTENDED para obtener información adicional, aunque no resulta práctica para un usuario 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 más información, consulta el Manual de Referencia de MySQL 8.0.
6\. Costo de ejecución de la consulta
El comando EXPLAIN ANALYZE aporta información detallada de costo para cada paso de la ejecución de la consulta. Esto resulta muy útil para identificar qué pasos son más costosos en cuanto a recursos, incluyendo el tiempo de ejecución y las filas procesadas.
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 más información, consulta el Manual de Referencia de MySQL 8.0.
7\. Evaluación del optimizador de consultas
Antes de ejecutarse en MySQL, una consulta SQL primero se parsea. Luego se valida que el usuario que la ejecuta tenga los privilegios correspondientes sobre las tablas y columnas. Si el cacheo de consultas está habilitado, se comprueba si existe una coincidencia previa y, de haberla, se devuelven los resultados. Después, el optimizador de consultas determina la ruta ideal para ejecutar la consulta a partir de diversas evaluaciones y considerando las capacidades del motor de almacenamiento de la tabla. El optimizer trace te permite recopilar información detallada sobre los pasos que evaluó el optimizador y las estadísticas que utilizó para tomar una decisión basada en costo.
NOTA: Esta información es altamente técnica y solo debería recopilarse para evaluaciones especializadas a cargo de personas con un alto nivel de conocimientos.
> 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: La salida JSON anterior está truncada, ya que ocupa varias páginas. Puedes ver la versión completa [aquí](https://gist.github.com/ronaldbradford/14f3e253342da7c7ce64756976b690f7).\
Para más información, consulta el [Manual de Referencia de MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimizer-trace-table.html).\
8\. Valores de estado de MySQL\
MySQL cuenta internamente con más de 300 métricas que pueden revisarse con granularidad GLOBAL o SESSION. Usar la variable de estado adecuada permite validar las acciones internas que se realizan durante la consulta en sus distintas etapas, incluyendo el uso de índices, las condiciones de filtrado, el ordenamiento y la agrupación de los datos. En el siguiente ejemplo, en el que se utilizó una tabla temporal interna, saber que se escribió en disco es importante para evaluar si se puede ajustar por diversos medios y reducir así el tiempo de ejecución.
NOTA: Esta información es altamente técnica y solo debería recopilarse para evaluaciones especializadas a cargo de personas con un alto nivel de conocimientos, capaces de saber qué métrica usar y cuál descartar. El propio acto de recopilar información también afecta a algunas 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 más información, consulta el [Manual de Referencia de MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/show-status.html).\
9\. Profiling de la consulta\
Cuando el profiling está habilitado para una consulta, puedes revisar cada paso interno y compararlo con el código fuente de MySQL. En la siguiente salida puedes ver que la mayor parte del tiempo de ejecución correspondió a un único paso; por ejemplo, la línea 14 fue de 0,234060 segundos. La mayoría de las consultas siguen una distribución de tiempos similar, aunque las consultas más complejas mostrarán pasos adicionales.
NOTA: Esta información es altamente técnica y solo debería recopilarse para evaluaciones especializadas a cargo de personas con un alto nivel de conocimientos. Aquí no se describen detalles adicionales de este análisis.\
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 más información, consulta el [Manual de Referencia de MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html).\
10\. Processlist\
Durante la ejecución de una sentencia SQL, el processlist resulta útil para conocer el State y el Time de una consulta de larga duración. También permite ver el impacto de la sentencia sobre otras sentencias concurrentes, por ejemplo en cuanto a bloqueos. Consulta Capturando sentencias SQL en ejecución para obtener información detallada sobre cómo recopilar el processlist mediante varios métodos.\
Advertencia sobre la precisión de los valores\
Muchas de las herramientas de análisis aquí descritas arrojan resultados variables según la situación. Algunos valores deben mantenerse precisos en cada ejecución, por ejemplo el número de filas devueltas o la estructura de la tabla.
Otras salidas deberían mantenerse casi consistentes en ejecuciones repetidas; por ejemplo, el QEP puede incluir el mismo número de pasos, pero una columna puede contener valores distintos. Un QEP puede cambiar según las constantes utilizadas en la sentencia SQL, según la versión de MySQL o por cambios en la configuración de MySQL.
Otros valores rara vez serán idénticos: por ejemplo, el tiempo de ejecución de alta precisión, el costo de la consulta o los pasos individuales del profiling deberían promediarse y conviene usar un valor de percentil apropiado para identificar ejecuciones similares.\
Conclusión\
Existen múltiples métodos nativos que utilizan las herramientas de cliente de MySQL para recopilar información de apoyo al ejecutar sentencias SQL. Esta información es clave a la hora de analizar y optimizar las sentencias SQL.
Este artículo da continuidad a Capturando sentencias SQL en ejecución, donde se detallan las distintas técnicas que se pueden usar para capturar sentencias SQL completas.\
Apéndice\
Datos de ejemplo utilizados\
Para este artículo, el dataset de aeropuertos utilizado está disponible en https://github.com/ronaldbradford/data/tree/main/mysql-data/airport\
SQL de ejemplo utilizado\
Para cada método de recopilación se usó la siguiente sentencia SQL de ejemplo.\
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 sentencia SQL devuelve un 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)\
```\
NOTA: Los conteos reales de aeropuertos y el orden de los países pueden variar según el momento en que hayas obtenido el dataset.\
Una nota sobre las comillas invertidas (\`)\
En muchos ejemplos verás que se añade el carácter de comilla invertida (`) alrededor de los nombres de objetos. Es una herencia de la capacidad de admitir el uso de palabras reservadas, las cuales deben encerrarse entre comillas invertidas. Sin embargo, las herramientas no agregan esta sintaxis adicional solo cuando es necesario, sino que la incluyen en todos los objetos de tablas y columnas, lo que dificulta la lectura.\