El primer paso es dar prioridad a las consultas que se quieren optimizar:

1. Identifica las preguntas de la base de datos que necesitan el mejor tiempo de respuesta 
2. Identifica las preguntas frecuentes de las bases de datos

Estas dos categorías se deben optimizar 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 confirman o se restauran. Transacción es la función que proporciona atomicidad, coherencia, aislamiento y durabilidad (ACID) para MySQL. 

En InnoDB, el motor de almacenamiento de MySQL, todas las actividades de usuario se producen en una transacción. El modo de confirmación automática está habilitado de forma predeterminada, lo que significa que cada declaración SQL forma una sola transacción. Para hacer una transacción con varias declaraciones cuando la confirmación automática está habilitada, empieza la transacción explícitamente con START_TRANSACTION o START_TRANSACTION y termina con START_TRANSACTION o START_TRANSACTION. Si se inhabilita el modo de confirmación automática, la sesión siempre tendrá una transacción abierta hasta que una sentencia COMMIT o COMMIT la termine y empiece una nueva. 

Las prácticas recomendadas en el caso de las transacciones sugieren que sean lo más cortas posible. Esto se debe a que las transacciones largas tienen varios inconvenientes, tal como se describe más adelante en este artículo.

1. Contenciones de bloqueo prolongadas, que provocan consultas más lentas y posibles errores de consulta

• El bloqueo de nivel de fila InnoDB se retiene durante la transacción
• Posiblemente aumente la espera bloqueo, los tiempos de espera de bloqueo y los interbloqueos, que hacen que la consulta sea más lenta o que falle por completo

2. El rendimiento del servidor ha empeorado debido a la gran cantidad de registros de cancelación de modificaciones 

• Debido al control de simultaneidad multiversión (MVCC) de InnoDB, las versiones antiguas de las filas cambiadas se almacenan en registros de cancelación de modificaciones para obtener una lectura y una restauración coherentes. Con el nivel de aislamiento de lectura repetible predeterminado, los registros de cancelación de modificaciones no se purgarán hasta que se completen las transacciones iniciadas antes. Por lo tanto, las transacciones de larga duración acumulan registros de cancelación de modificaciones. Esto se puede observar y monitorizar a través de la lista de historial de la función de línea de comandos SHOW ENGINE INNODB STATUS.
• Cuando la lista del historial supera los millones, el rendimiento del servidor se ve afectado negativamente por la contención de exclusión mutua en los segmentos de restauración, el mayor volumen de lectura de los registros de cancelación de modificaciones y el mayor tiempo transcurrido para recorrer lista vinculada de registros de cancelación de modificaciones. También crea más trabajo para los hilos de purga.

3. Aumento del uso del disco

• Aumento de los registros de cancelación de modificaciones que están almacenados en disco, ya sea en el espacio de tabla del sistema o en el espacio de tablas de cancelación de modificaciones

4. Tiempo de cierre prolongado

• Durante el cierre normal, se restaurarán las transacciones en curso. El tiempo de restauración suele ser mayor que el tiempo que se tarda en llegar al punto. Por tanto, el cierre del servidor podría tardar mucho tiempo en restaurar la versión.

5. Tiempo prolongado de recuperación tras fallos

• Durante la recuperación tras fallos, InnoDB repite las transacciones del último punto de control y deshace las transacciones no confirmadas. Una transacción larga provocaría que la duración del paso correspondiente fuera mayor.

• Consultas SELECT
• No tienen bloqueos de filas
• Esto podría provocar que se acumularan registros
• Consulta la sección de optimización de consultas más abajo
• Consultas de tipo UPDATE/INSERT/DELETE
• La consulta por lotes tiene mejor rendimiento que muchos cambios en una sola fila
• Dividir y limitar el tiempo de ejecución de lotes a un par de segundos

• Piensa en separar con SELECCIONAR consultas
• Si hay lógica de aplicación entre las consultas de la base de datos, plantéate dividir la transacción
• Calcula cuántos bloqueos de fila se aplicarían a cada declaración
• Evalúa el orden de ejecución para minimizar el bloqueo de filas
• Busca oportunidades para reducir el tamaño de la transacción

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

Te recomendamos que registres las consultas de la base de datos y el tiempo de ejecución de las consultas. El almacenamiento de registros de la aplicación facilita la evaluación de la eficacia y la eficiencia de las consultas en el contexto de su negocio. Por ejemplo, los usuarios pueden registrar el tiempo de respuesta de cada consulta o el tiempo de respuesta de ciertas funciones. También es una forma sencilla de obtener el tiempo total de ejecución de las transacciones con varias declaraciones. 

Además, el tiempo de respuesta a las consultas que se mide a partir del registro en la aplicación es de extremo a extremo, incluido el tiempo de red. Esta función complementa el tiempo de ejecución de las consultas registrado desde la base de datos y facilita identificar si el problema está relacionado con la red o con la base de datos.

Información valiosa sobre las consultas de Cloud SQL de MySQL

La herramienta de información útil sobre las consultas de Cloud SQL permite capturar, monitorizar y diagnosticar consultas.

Con la información útil sobre las consultas, puedes encontrar fácilmente las principales consultas según el tiempo de ejecución y la frecuencia de ejecución.

La herramienta tiene opciones de filtrado, como el intervalo de tiempo, la base de datos, la cuenta de usuario y la dirección de cliente. Tiene gráficos para mostrar el uso de CPU y un desglose de las esperas de E/S y de bloqueo. En la tabla "Consultas y etiquetas principales" se muestran las consultas populares por tiempo de ejecución con las consultas normalizadas. Además del tiempo de ejecución, incluye estadísticas sobre la media de filas analizadas y la media de filas devueltas, que ofrecen información valiosa sobre la eficiencia de las consultas.

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

Usar el esquema de rendimiento

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

Cuando performance_schema=ON, los instrumentos de la declaración de consulta están habilitados de forma predeterminada. La tabla sys.statement_analysis proporciona estadísticas agregadas para las consultas normalizadas. Responde a preguntas como las siguientes: 

• ¿Qué consultas buscan en la tabla completa?
• full_scan/exec_count: para determinar si las consultas suelen hacer un análisis de tabla completa que, a menudo, es ineficaz 
• ¿Qué consultas se ejecutan con lentitud?
• avg_latency: tiempo medio de ejecución de las consultas.
• ¿Qué consultas son ineficaces?
• rows_examined_avg/rows_examined_avg: corresponde a las consultas de lectura. La proporción ideal es 1. Cuanto mayor sea la proporción, menos eficiente será la consulta. 
• rows_examined_avg/rows_examined_avg: corresponde a las consultas de escritura. La proporción ideal es 1. Cuanto mayor sea la proporción, menos eficiente será la consulta. 
• ¿Qué consultas usan tablas temporales y tienen que convertirse en tablas temporales de disco? 
• tmp_disk_tables/tmp_disk_tables: comprueba si el valor de tmp_disk_tables/tmp_disk_tables es suficiente.
• ¿Qué consultas usan el tipo de archivo?
• rows_sorted/rows_sorted, rows_sorted/rows_sorted: para identificar consultas con gran cantidad de ordenación y puede usar un campo rows_sorted más grande.

Si utilizas MySQL Workbench, tendrás informes de esquema de rendimiento basados en la vista sys. El informe cuenta con una sección sobre declaraciones SQL de alto coste en la que se proporciona información valiosa sobre el rendimiento de las consultas.

Usa un registro lento y herramientas

El registro lento captura todas las consultas que se ejecutan durante más tiempo que el campo 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 hacen que sea un candidato preferido para analizar las consultas de la base de datos que usan el registro general.

Te recomendamos que tengas habilitado el registro lento. Normalmente, long_query_time se debe mantener en un umbral razonable para capturar las consultas que quieras ver y optimizar. 

log_output=FILE

slow_query_log=ACTIVADO

long_query_time=2

De vez en cuando, es recomendable configurar long_query_time=0 para capturar todas las consultas durante un breve periodo y obtener un resumen del volumen y el rendimiento de las consultas. 

Hay herramientas, como mysqldumpslow y pt-query-digest, que extraerían firmas de la consulta y generarían un informe para mostrar estadísticas de consulta.   

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

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

El comando EXPLAIN proporciona el plan de ejecución de consultas y, desde la versión 8.0.18, el comando EXPLAIN ANALYZE ejecuta una instrucción y produce resultados EXPLAIN junto con el tiempo desde la ejecución. 

La información útil sobre las consultas de MySQL ofrece acceso práctico al plan EXPLAIN.

¿Qué buscamos en los resultados?

• El campo "rows" muestra el número de filas que se van a leer 
• Las E/S es la parte que más tiempo lleva. Si una consulta necesita leer una gran cantidad de datos, es probable que sea lenta. Para hacerte una idea general, multiplica las filas "rows" entre las tablas unidas. En el ejemplo anterior, es 858 * 23523. Leer 23523 filas de t2 en cada una de las 858 filas de t1, no parece óptimo. Teniendo esto en cuenta, la optimización sería reducir la cantidad de acceso a datos de t2 para cada iteración.
• El campo "type" describe el tipo de unión de la tabla
• El tipo "índice" significa que el índice se ha analizado. Si el índice cumple todos los datos necesarios de la tabla, en el campo Extra se mostrará "Utiliza el índice". 
• El tipo "range" significa que no solo se utiliza un índice, sino que también se proporciona una condición de intervalo para limitar el análisis de datos. 
• En tablas posteriores en orden de unión, el tipo "eq_ref" significa que se lee una fila de esta tabla para cada combinación de filas de las tablas anteriores, que es lo más eficiente. 
• El tipo "ref" significa que la coincidencia del índice es 1:m en lugar de 1:1. Se leerán más de una fila de esta tabla por cada combinación de filas de las tablas anteriores. 
• El tipo que se debe evitar es "TODO". Significa que se realiza un análisis completo de la tabla en cada combinación de filas de las tablas anteriores. 
• El campo "key" muestra el índice que se está utilizando. 
• La selección de los índices que se van a usar se basa en la cardinalidad del índice, que puede estar obsoleta. Por tanto, es importante comprobar que se utiliza 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 utiliza. Por ejemplo, si un índice tiene (col1, col2, col3) y la condición de consulta es "col1 = n y col2, como "%string%", solo se usa "col1" para filtrar el índice. Si la consulta se pudiera cambiar a "col1 = n y col2 como "string%", se utilizarían ambas (col1, col2) para el filtrado de índices. Este pequeño cambio podría suponer una diferencia considerable en el rendimiento de las consultas. 
• El campo "Extra" contiene información adicional sobre el plan de consultas 
• "Using temporary" significa que se crea una tabla temporal interna que puede generar una tabla temporal en el disco
• “Using filesort” significa que la ordenación no ha podido aprovechar ningún índice y que requiere un búfer de ordenación y archivos de disco temporales 
• "Uses index" hace referencia a todos los datos necesarios de esta tabla. no es necesario leer las filas de datos

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

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

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

Una vez que comprendes el plan de ejecución de consultas, hay varias formas de influir y optimizarlo. 

• Añadir o actualizar la definición del índice
• Para filtrar mejor, reduce el acceso a los datos
• Para ordenar, no uses filesort
• Actualizar las estadísticas del índice si está desactivada
• ANALYZE TABLE <tbl>;
• A continuación, vuelve a comprobar la información sobre el plan EXPLAIN
• Usar sugerencia del índice
• Para sugerir o forzar que se use un índice determinado para filtrar, agrupar ou ordenar por/agrupar por 
• Usa STRAIGHT_JOIN para definir el orden de unión de la tabla
• Usar sugerencias de Optimizador

Para optimizar la configuración del servidor en determinadas consultas, te recomendamos que uses variables 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 ajustar las consultas, hablamos de tres aspectos: 

• Toma decisiones fundamentadas al crear las consultas. Estas decisiones son el factor que impulsa el rendimiento de las consultas, del rendimiento global del servidor y de su cumplimiento. 
• Monitoriza los datos de ejecución de las consultas tanto en la aplicación como en la base de datos. El almacenamiento de registros de aplicaciones es importante. Podría configurarse en función de los intereses empresariales y reflejar las operaciones comerciales. 
• Por último, hay varias herramientas que te ayudarán a comprender el plan de ejecución de consultas, los costes asociados a distintos pasos y formas de optimizar las consultas.