Solución de problemas de rendimiento de consultas de las bases de datos Aurora MySQL
MySQL proporciona control del optimizador de consultas
El rendimiento de las consultas depende de muchos factores, como el plan de ejecución, el esquema y tamaño de las tablas, las estadísticas, los recursos, los índices y la configuración de los parámetros. El ajuste de las consultas requiere identificar cuellos de botella y optimizar la ruta de ejecución.
-
Busque el plan de ejecución de la consulta y compruebe si la consulta utiliza los índices adecuados. Para optimizar la consulta, puede utilizar
EXPLAINy revisar los detalles de cada plan. -
Aurora MySQL versión 3 (compatible con MySQL 8.0 Community Edition) utiliza una instrucción
EXPLAIN ANALYZE. La instrucciónEXPLAIN ANALYZEes una herramienta de elaboración de perfiles que indica en dónde MySQL dedica tiempo a su consulta y por qué. ConEXPLAIN ANALYZE, Aurora MySQL planifica, prepara y ejecuta la consulta mientras cuenta las filas y mide el tiempo empleado en varios puntos del plan de ejecución. Cuando se completa la consulta,EXPLAIN ANALYZEimprime el plan y sus medidas en lugar del resultado de la consulta. -
Mantenga las estadísticas del esquema actualizadas mediante la instrucción
ANALYZE. A veces, el optimizador de consultas puede elegir planes de ejecución inadecuados debido a que las estadísticas están desactualizadas. Esto puede provocar un rendimiento deficiente de una consulta debido a que las estimaciones de cardinalidad de las tablas y los índices no son exactas. La columnalast_updatede la tabla innodb_table_statsmuestra la última vez que se actualizaron las estadísticas del esquema, lo que es un buen indicador de que están “estancadas”. -
Pueden producirse otros problemas, como un sesgo en la distribución de los datos, que no se tienen en cuenta para determinar la cardinalidad de la tabla. Para obtener más información, consulte Estimating ANALYZE TABLE complexity for InnoDB tables
y Histogram statistics in MySQL en la documentación de MySQL.
Descripción del tiempo que emplean las consultas
Estas son formas de determinar el tiempo que emplean las consultas:
- Elaboración de perfiles
-
La elaboración de perfiles está deshabilitada de forma predeterminada. Habilite la elaboración de perfiles y, a continuación, ejecute la consulta lenta y revise su perfil.
SET profiling = 1;Run your query.SHOW PROFILE;-
Identifique la etapa en la que pasa más tiempo. De acuerdo con General thread states
, en la documentación de MySQL, leer y procesar filas de una instrucción SELECTdeterminada suele ser el estado que tarda más tiempo en ejecutarse a lo largo de la vida útil de una consulta. Puede usar la instrucciónEXPLAINpara saber cómo MySQL ejecuta esta consulta. -
Revise el registro de consultas lentas para evaluar
rows_examinedyrows_sentpara asegurarse de que la carga de trabajo es similar en cada entorno. Para obtener más información, consulte Registro de bases de datos Aurora MySQL. -
Ejecute el siguiente comando para las tablas que forman parte de la consulta identificada:
SHOW TABLE STATUS\G; -
Obtenga la siguiente salida antes y después de ejecutar la consulta en cada entorno:
SHOW GLOBAL STATUS; -
Ejecute los siguientes comandos en cada entorno para comprobar si hay alguna otra consulta o sesión que influya en el rendimiento de esta consulta de ejemplo.
SHOW FULL PROCESSLIST; SHOW ENGINE INNODB STATUS\G;A veces, cuando los recursos del servidor están ocupados, esto afecta a todas las demás operaciones del servidor, incluidas las consultas. También puede obtener información periódicamente cuando se ejecutan consultas o configurar un trabajo de
cronpara obtener información a intervalos útiles.
-
- Performance Schema
-
Performance Schema proporciona información útil sobre el rendimiento del tiempo de ejecución del servidor y, al mismo tiempo, tiene un impacto mínimo en ese rendimiento. Es diferente de
information_schema, que proporciona información de esquema sobre la instancia de base de datos. Para obtener más información, consulte Descripción general de Performance Schema para Información de rendimiento en Aurora MySQL. - Seguimiento del optimizador de consultas
-
Para entender por qué se eligió un plan de consultas en particular para su ejecución
, puede configurar optimizer_tracepara acceder al optimizador de consultas de MySQL.Ejecute un seguimiento del optimizador para ver información exhaustiva sobre todas las rutas disponibles para el optimizador y su elección.
SET SESSION OPTIMIZER_TRACE="enabled=on"; SET optimizer_trace_offset=-5, optimizer_trace_limit=5; -- Run your query. SELECT * FROM table WHERE x = 1 AND y = 'A'; -- After the query completes: SELECT * FROM information_schema.OPTIMIZER_TRACE; SET SESSION OPTIMIZER_TRACE="enabled=off";
Revisión de la configuración del optimizador de consultas
Aurora MySQL versión 3 (compatible con MySQL 8.0 Community Edition) tiene muchos cambios relacionados con el optimizador en comparación con Aurora MySQL versión 2 (compatible con MySQL 5.7 Community Edition). Si tiene algunos valores personalizados para optimizer_switch, le recomendamos que revise las diferencias entre los valores predeterminados y establezca los valores de optimizer_switch que mejor se adapten a su carga de trabajo. También le recomendamos que pruebe las opciones disponibles para la versión 3 de Aurora MySQL para examinar el rendimiento de las consultas.
nota
La versión 3 de Aurora MySQL utiliza el valor predeterminado de 20 de la comunidad para el parámetro innodb_stats_persistent_sample_pages
Puede utilizar el siguiente comando para mostrar los valores de optimizer_switch:
SELECT @@optimizer_switch\G;
En la tabla siguiente, se muestran los valores predeterminados de optimizer_switch en Aurora MySQL versiones 2 y 3.
| Opción | Aurora MySQL versión 2 | Aurora MySQL versión 3 |
|---|---|---|
batched_key_access |
off | off |
block_nested_loop |
on | on |
condition_fanout_filter |
on | on |
derived_condition_pushdown |
– | on |
derived_merge |
on | on |
duplicateweedout |
on | on |
engine_condition_pushdown |
on | on |
firstmatch |
on | on |
hash_join |
off | on |
hash_join_cost_based |
on | – |
hypergraph_optimizer |
– | off |
index_condition_pushdown |
on | on |
index_merge |
on | on |
index_merge_intersection |
on | on |
index_merge_sort_union |
on | on |
index_merge_union |
on | on |
loosescan |
on | on |
materialization |
on | on |
mrr |
on | on |
mrr_cost_based |
on | on |
prefer_ordering_index |
on | on |
semijoin |
on | on |
skip_scan |
– | on |
subquery_materialization_cost_based |
on | on |
subquery_to_derived |
– | off |
use_index_extensions |
on | on |
use_invisible_indexes |
– | off |
Para obtener más información, consulte Switchable optimizations (MySQL 5.7)
