El primer paso es priorizar las consultas que se optimizarán:

1. Identifica las preguntas de la base de datos que necesitan el mejor tiempo de respuesta
2. Identifica las preguntas de la base de datos que se ejecutan con frecuencia

Estas dos categorías deben optimizarse durante el diseño del esquema de la base de datos.

Una transacción es una unidad lógica en la que todas las declaraciones contenidas se confirmarían por completo o se revertirían. Las transacciones son las características que proporcionan atomicidad, coherencia, aislamiento y durabilidad (ACID, por sus siglas en inglés) para MySQL.

En InnoDB, el motor de almacenamiento para MySQL, todas las actividades del usuario se producen dentro de una transacción. De forma predeterminada, el modo de confirmación automática está habilitado, lo que significa que cada instrucción de SQL forma una sola transacción por sí misma. Para realizar una transacción de varias declaraciones cuando la confirmación automática está habilitada, inicia la transacción de forma explícita con START_TRANSACTION o START_TRANSACTION y finalízala con START_TRANSACTION o START_TRANSACTION. Si el modo de confirmación automática está inhabilitado, la sesión siempre tiene una transacción abierta hasta que un COMMIT o COMMIT la finaliza y comienza una nueva.

Se recomienda que las transacciones sean lo más cortas posible. Esto se debe a que las transacciones largas tienen varias desventajas, como se detalla más adelante en este artículo.

1. Contenciones de bloqueo prolongadas, que causan consultas más lentas y posibles fallas de las consultas

• El bloqueo de nivel de fila de InnoDB se mantiene durante la transacción
• Es posible que aumente los tiempos de espera y los tiempos de espera de bloqueo, y los interbloqueos que hacen que las consultas sean más lentas o fallen por completo

2. Rendimiento degradado del servidor debido a la gran cantidad de registros de deshacer 

• Debido al control de simultaneidad de varias versiones (MVCC) de InnoDB, las versiones anteriores de las filas modificadas se almacenan en los registros de deshacer para una lectura y una reversión coherentes. Con el nivel predeterminado de aislamiento de lectura repetible, los registros de deshacer no se borrarán definitivamente hasta que las transacciones comiencen antes de completarse. Por lo tanto, una transacción de larga duración acumula registros de deshacer. Esto se puede observar y supervisar a través de la lista del historial en la función de línea de comandos SHOW ENGINE INNODB STATUS.
• Si la lista del historial supera los millones, el rendimiento del servidor se vería afectado de forma negativa debido a la contención de exclusión mutua en segmentos de reversión, el aumento del volumen para leer los registros de deshacer y el aumento del tiempo para recorrer la lista vinculada de los registros de deshacer. También crea más trabajo para borrar definitivamente subprocesos.

3. Aumento del uso del disco

• Aumento en los registros de deshacer que se almacenan en el disco, ya sea en el espacio de tabla del sistema o en el de deshacer

4. Prolongación del tiempo de cierre

• Durante el cierre normal, las transacciones en curso se revierten. El tiempo de reversión suele ser más largo que el tiempo necesario para llegar al punto. Por lo tanto, el cierre del servidor podría tardar un poco en revertirse.

5. Prolongación del tiempo de recuperación ante fallas

• Durante la recuperación ante fallas, InnoDB repite las transacciones del último punto de control y despliega las transacciones no confirmadas. Una transacción larga puede hacer que tarde más el paso correspondiente.

• Consultas SELECT
• No conservan los bloqueos de fila
• Pueden hacer que se acumulen los registros de deshacer
• Consulta la sección de optimización de consultas a continuación
• Consultas UPDATE/INSERT/DELETE
• La consulta por lotes funciona mejor que muchos cambios de una sola fila
• Divide el tiempo de ejecución del lote y limítalo a un par de segundos

• Considera separar las consultas SELECT
• Si hay lógica de aplicación entre las consultas de la base de datos, considera dividir la transacción
• Estima cuántos bloqueos de fila se retendrían para cada declaración
• Evalúa el orden de ejecución para minimizar el bloqueo de filas
• Busca oportunidades para reducir el tamaño de las transacciones

Las consultas se pueden capturar en la aplicación o en la base de datos.

Una práctica recomendada para el desarrollo es registrar las consultas de bases de datos y el tiempo de ejecución de las consultas. El registro en la aplicación facilita la evaluación de la eficacia y la eficiencia de las consultas en su contexto empresarial. Por ejemplo, los usuarios pueden registrar el tiempo de respuesta de cada consulta o el tiempo de respuesta de algunas funcionalidades. Esta también es una manera fácil de obtener el tiempo total de ejecución para las transacciones de varias declaraciones.

Además, el tiempo de respuesta de la consulta medido a partir del registro de la aplicación es una medición de extremo a extremo, incluido el tiempo de red. Esto complementa el tiempo de ejecución de la consulta registrado desde la base de datos y facilita identificar si el problema se relaciona con la red o con la base de datos.

Estadísticas de consultas de MySQL en Cloud SQL

La herramienta Estadísticas de consultas de Cloud SQL permite capturar, supervisar y diagnosticar consultas.

Las estadísticas de consultas facilitan la búsqueda de las consultas principales según el tiempo de ejecución y la frecuencia de ejecución.

La herramienta tiene opciones de filtrado, como intervalo de tiempo, base de datos, cuenta de usuario y dirección del cliente. Tiene gráficos que muestran el uso de la CPU, y un desglose de esperas de bloqueo y E/S. En la tabla “Consultas y etiquetas principales”, se enumeran las consultas principales por tiempo de ejecución y se normalizan las consultas. Además del tiempo de ejecución, incluye estadísticas sobre el “promedio de filas analizadas” y el “promedio de filas mostradas”, que brindan estadísticas sobre la eficiencia de las consultas.

Consulta la documentación para ver todo lo que ofrece y cómo habilitarla.

Usa performance_schema

En Cloud SQL para MySQL, la función performance_schema está habilitada de forma predeterminada en las versiones 8.0.26 y posteriores de MySQL con más de 15 GB de memoria. Para habilitarla o inhabilitarla, es necesario reiniciar la instancia.

Cuando performance_schema=ON, los instrumentos de declaración de consultas están habilitados de forma predeterminada. En la tabla sys.statement_analysis, se proporcionan estadísticas agregadas para consultas normalizadas. Estas responden preguntas como las siguientes: 

• ¿Qué consultas realizan un análisis completo de la tabla?
• full_scan/exec_count: para ver si las consultas hacen un análisis completo de la tabla frecuentemente, lo que suele ser ineficiente 
• ¿Qué consultas se ejecutan con lentitud?
• avg_latency: tiempo promedio de ejecución de una consulta
• ¿Qué consultas son ineficientes? 
• rows_examined_avg/rows_examined_avg: corresponde a las consultas de lectura La proporción ideal es 1. Cuanto mayor sea la proporción, más ineficiente será la consulta.
• rows_examined_avg/rows_examined_avg: corresponde a consultas de escritura La proporción ideal es 1. Cuanto mayor sea la proporción, más ineficiente será la consulta.
• ¿Qué consultas usan tablas temporales y deben convertirse en tablas temporales en el disco?
• tmp_disk_tables/tmp_disk_tables: permite ver si tmp_disk_tables/tmp_disk_tables es suficiente
• ¿Qué consultas utilizan ordenamiento de archivos? 
• rows_sorted/rows_sorted, rows_sorted/rows_sorted: permiten identificar las consultas que tienen un gran volumen de orden y podrían usar un valor mayor de rows_sorted

Si usas MySQL Workbench, tendrá informes de esquema de rendimiento basados en la vista del sistema. El informe tiene una sección sobre "instrucciones de SQL de alto costo" que proporciona estadísticas sobre el rendimiento de las consultas.

Usa el registro de consultas lentas + herramientas

El registro de consultas lentas captura todas las consultas que se ejecutan por un tiempo mayor que long_query_time. También registra el tiempo de ejecución de las consultas, el tiempo de bloqueo, las filas de datos examinadas y las filas de datos enviadas. Las estadísticas de ejecución adicionales lo convierten en un candidato preferido para analizar las consultas de base de datos en lugar de usar el registro general.

Se recomienda habilitar el registro de consultas lentas. Por lo general, el valor de long_query_time debe mantenerse en un umbral razonable para capturar las consultas que pretendes observar y optimizar.

log_output=FILE

slow_query_log=ON

long_query_time=2

De vez en cuando, es una buena idea configurar long_query_time=0 para capturar todas las consultas durante un período breve y obtener una descripción general del volumen y el rendimiento de las consultas.

Existen herramientas, como mysqldumpslow y pt-query-digest, que extraen las firmas de las consultas y generan un informe que muestra las estadísticas de consultas.

Existen otras herramientas de supervisión de terceros que generan informes sobre las estadísticas de consultas, como Percona Monitoring and Management, SolarWinds Database Performance Monitor (anteriormente VividCortex) y más.

Después de capturar las consultas en transacciones, el siguiente paso es optimizarlas.

El comando EXPLAIN proporciona el plan de ejecución de consultas y, a partir de la versión 8.0.18, el comando EXPLAIN ANALYZE ejecutará una declaración y producirá un resultado EXPLAIN junto con los tiempos de la ejecución.

Las estadísticas de consultas de MySQL proporcionan acceso práctico al plan EXPLAIN.

¿Qué buscamos en el resultado? 

• En el campo “rows” se muestra la cantidad de filas que se leerán.
• E/S es la parte que más tiempo consume. Si una consulta necesita leer una gran cantidad de datos, es probable que sea lenta. Para tener una idea general de eso, multiplica el valor de “rows” de las tablas unidas. En el ejemplo anterior, esto es 858 * 23,523. Leer 23,523 filas de t2 por cada una de las 858 filas de t1 no parece óptimo. Debido a esto, la optimización sería reducir la cantidad de acceso a los datos de t2 para cada iteración.
• El campo “type” describe el tipo de unión de tablas 
• El tipo “index” significa que el índice se analiza. Si el índice satisface todos los datos obligatorios de la tabla, el campo Extra mostrará “Uses index”. 
• Tipo “range” significa que no solo se usa un índice, sino también que se proporciona una condición de rango para limitar el análisis de datos. 
• Para las tablas posteriores en el orden de combinación, el tipo “eq_ref” significa que se lee una fila de esta tabla por cada combinación de filas de las tablas anteriores, que es lo más eficiente.
• Si el tipo es “ref”, significa que la coincidencia del índice es 1:m en lugar de 1:1. Se leerá más de una fila de esta tabla para cada combinación de filas de las tablas anteriores.
• El tipo que se debe evitar es “ALL”. Significa que se realiza un análisis completo de la tabla para cada combinación de filas de las tablas anteriores. 
• El campo “key” muestra el índice real que se usa.
• La selección del índice que se usa depende de la cardinalidad, que puede estar desactualizada. Por lo tanto, es importante verificar que se use el índice más selectivo.  
• El campo “key_len” indica la longitud de la clave en bytes.
• Con un índice de varias columnas, key_len sugiere la parte del índice que se usa. Por ejemplo, si un índice tiene (col1, col2, col3) y la condición de la consulta es “col1 = n and col2 like ‘%string%’”, solo se usará col1 para filtrar índices. Si la consulta se pudiera cambiar a “col1 = n and col2 like ‘string%’”, se usarían ambas (col1, col2) para filtrar índices. Este pequeño cambio podría marcar una diferencia drástica en el rendimiento de las consultas.
• El campo “Extra” contiene información adicional sobre el plan de consulta.
• “Using temporary” significa que se crea una tabla temporal interna que puede generar una tabla temporal en el disco
• “Using filesort” significa que la clasificación no pudo aprovechar ningún índice y necesita un búfer de orden y posiblemente archivos temporales del disco
• “Uses index” significa que todos los datos obligatorios de esta tabla están contenidos en el índice. No es necesario leer filas de datos

Las variables de estado de la sesión se pueden usar para obtener detalles de la ejecución de la consulta.

Primero, borra las variables de la sesión y, luego, ejecuta la consulta y examina los contadores. Por ejemplo, el estado Handler_* muestra el patrón de acceso a los datos y la cantidad de filas. El estado Created_* se mostraría si se crea una tabla temporal o una temporal en el disco. El campo Sort_* mostraría la cantidad de pases de combinación de ordenamiento y la cantidad de filas ordenadas. En la documentación, se explican más variables de sesión.

La instrucción SHOW PROFILE proporciona el tiempo de ejecución de la consulta por etapa de ejecución, que también podría ser información útil.

Una vez que se comprende el plan de ejecución de consultas, existen varias formas de influir en él y optimizarlo.

• Agrega o actualiza la definición de índices 
• Para lograr un mejor filtrado, usa menos acceso a los datos
• Para ordenar, evita usar filesort
• Actualiza las estadísticas del índice si la opción está desactivada
• ANALYZE TABLE <tbl>;
• Luego, vuelve a revisar el resultado del plan EXPLAIN
• Usa Index Hint
• A fin de sugerir o forzar el uso de un índice determinado para filtrar, usa unir u ordenar por / agrupar por
• Usa STRAIGHT_JOIN para definir el orden de unión de tablas 
• Usa Optimizer Hints

A fin de optimizar la configuración del servidor para ciertas consultas, se recomienda usar las variables a nivel de sesión en lugar de cambiar el valor global que afecta a todas las sesiones.

Los valores de sesión más utilizados son los siguientes:

En resumen, para el ajuste de consultas, hablamos de tres aspectos:

• Tomar decisiones conscientes cuando redactas las consultas. Estas decisiones son el factor principal para el rendimiento de las consultas, la capacidad de procesamiento general del servidor y el rendimiento del servidor.
• Haz un seguimiento de los datos de ejecución de la consulta tanto en la aplicación como en la base de datos. El registro en la aplicación es importante. Se puede configurar en función de los intereses de la empresa y reflejar las operaciones comerciales.
• Por último, existen varias herramientas que ayudan a comprender el plan de ejecución de consultas, los costos asociados con los diferentes pasos y las formas de optimizar las consultas.