Saca más partido al rendimiento de MySQL: ajuste de consultas
Las bases de datos desempeñan un papel fundamental en el rendimiento de cualquier aplicación. Las bases de datos MySQL no son una excepción. Por este motivo, es importante conocer las numerosas formas en las que el ajuste, el diseño y la configuración de la base de datos pueden ayudarte a mejorar el rendimiento de tu aplicación. A continuación, se indican algunas formas de optimizar MySQL para conseguir un rendimiento óptimo.
Artículos relacionados
Descripción general
Ajuste de consultas
El ajuste del rendimiento de la base de datos empieza desde la aplicación. La forma en que la aplicación traduce los requisitos empresariales a las consultas de la base de datos supone una gran diferencia en la complejidad y la eficiencia de la aplicación. La medida real de rendimiento es la eficiencia con la que cada instancia de base de datos contribuye a las necesidades empresariales.
Diseño de esquema
La definición de entidades y relaciones en una base de datos relacional determina lo sencilla o compleja que puede ser una respuesta a una consulta de base de datos. También desempeña un papel importante el modo en que se definen la clave principal y los índices secundarios.
Configuración del servidor
La configuración del servidor se encarga de optimizar y maximizar la utilización de los recursos del sistema. Los recursos del sistema son los núcleos de las unidades de procesamiento centrales (CPU), la memoria de la máquina física o de la máquina virtual, el sistema de almacenamiento subyacente y la red.
Ajuste del servidor dinámico
Supervisión continua, optimización y ajuste del rendimiento para adaptar las cargas de trabajo de las bases de datos a la dinámica del mundo real.
Este artículo se centra en el ajuste de las consultas. El resto de los temas se tratarán en artículos posteriores.
A menudo, el proceso de ajuste de la consulta se inicia analizando la consulta de la base de datos. Una mejor forma sería empezar evaluando cómo de eficiente se ha convertido el requisito empresarial en consulta de base de datos. La interpretación y el procesamiento de una necesidad empresarial en una consulta determina lo pequeño o grande que sería el coste.
Consultas de diseño
El primer paso es dar prioridad a las consultas que se quieren optimizar:
- Identifica las preguntas de la base de datos que necesitan el mejor tiempo de respuesta
- 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.
Transacciones cortas
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 los usuarios se producen en una transacción. El modo de confirmación automática está habilitado de forma predeterminada, lo que significa que cada instrucción de 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 INICIAR y termina con CONFIRMAR o RESTAURAR. Si se inhabilita el modo de confirmación automática, la sesión siempre tendrá una transacción abierta hasta que una CONFIRMACIÓN o RESTAURACIÓN la termine y empiece una nueva.
Te recomendamos que sea lo más breve posible. Esto se debe a que las transacciones largas tienen varios inconvenientes, tal como se describe más adelante en este artículo.
Inconvenientes de las transacciones largas
- 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.
- El rendimiento del servidor ha empeorado debido a la gran cantidad de registros que se pueden deshacer
- Debido al control de simultaneidad multiversión de InnoDB (MVCC), las versiones antiguas de las filas cambiadas se almacenan en registros de deshacer para obtener una lectura y una restauración coherentes. Con el nivel de aislamiento de lectura repetible predeterminado, los registros de deshacer no se purgarán hasta que se completen las transacciones. Por lo tanto, las transacciones de larga duración acumulan registros de deshacer. Esto se puede observar y supervisar a través de la lista de historial de la función de línea de comandos MOSTRAR MOTOR INNODB ESTADO.
- Cuando la lista del historial supera los millones, el rendimiento del servidor se ve afectado negativamente por la contención de silencio en los segmentos de restauración, el mayor volumen de lectura de los registros de deshacer y el tiempo transcurrido para recorrer lista vinculada de registros de deshacer. También crea más trabajo para purgar los hilos.
- Aumento del uso del disco
- Aumento de los registros de deshacer que están almacenados en disco, ya sea en el espacio de tabla del sistema o en el espacio de tablas
- Tiempo de apagado 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, la restauración del servidor podría tardar mucho tiempo en restaurar la versión.
- Tiempo prolongado de recuperación por 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 alargaría el paso correspondiente.
Consideración de las transacciones de consulta única
- SELECCIONAR consultas
- 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 ACTUALIZAR/INSERTAR/BORRAR
- La consulta por lotes funciona mejor que muchos cambios en una sola fila
- Pausa y limita el tiempo de ejecución por lotes a un par de segundos
Consideración para transacciones con varios extractos
- 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
- Calcular cuántos bloqueos de fila se aplicarían a cada extracto
- Evalúa el orden de ejecución para minimizar el bloqueo de filas
- Busca oportunidades para reducir el tamaño de la transacción
Captura de consultas
Las consultas se pueden capturar en la aplicación o en la base de datos.
Aplicación
Te recomendamos que registres 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 efectividad y de 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 la identificación de si el problema está relacionado con la red o con la base de datos.
Lado de la base de datos
Información valiosa sobre las consultas de Cloud SQL de MySQL
La herramienta de estadísticas de consulta de Cloud SQL permite capturar, monitorizar y diagnosticar consultas.
Con la información valiosa 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 periodo, 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 principales 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 estadísticas 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_sent_avg: se trata de consultas de lectura. La relación ideal es 1. Cuanto mayor es la relación, más ineficaz es la consulta.
- rows_examined_avg/rows_affected_avg: se trata de consultas de escritura. La relación ideal es 1. Cuanto mayor es la relación, más ineficaz es la consulta.
- ¿Qué consultas usan tablas temporales y tienen que convertirse en tablas temporales de disco?
- tmp_disk_tables/tmp_tables: comprueba si el valor de tmp_table_size/max_heap_table_size es suficiente.
- ¿Qué consultas usan el tipo de archivo?
- rows_sorted/exec_count, sort_merge_passes/exec_count: para identificar consultas con gran cantidad de orden y puede usar un campo sort_buffer_size 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 de 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=ARCHIVO
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.
Optimizar consultas
Después de capturar las consultas de las transacciones, el siguiente paso es optimizarlas.
EXPLICACIÓN: qué buscar
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
Las estadísticas de la consulta de MySQL ofrecen acceso práctico al plan EXPLAIN.
¿Qué buscamos en los resultados?
- El campo "filas" muestra el número de filas que se van a leer
- El PI 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" entre las tablas unidas. En el ejemplo anterior, es 858 * 23523. Para leer 23523 filas de t2 en cada una de las 858 filas de t1, el sonido no es óptimo. Teniendo esto en cuenta, 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 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 mostrará "Utiliza el índice".
- El tipo "intervalo" 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 la 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 de concordancia que se debe evitar es "TODOS". Significa que se realiza un análisis completo de la tabla en cada combinación de filas de las tablas anteriores.
- El campo "clave" muestra el índice que se está utilizando.
- La selección de los índices que se va 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 'cadena%'", 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
- "Usar temporal" significa que se crea una tabla temporal interna que puede generar una tabla temporal en el disco
- El uso de 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
- "Usa el índice" hace referencia a todos los datos necesarios de esta tabla. no es necesario leer las filas de datos
Perfiles de consulta
Las variables de estado de la sesión se podían usar para obtener detalles de ejecución de la consulta.
Primero, borra las variables de sesión, 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 elemento Created_* se mostraría si se crea una tabla temporal o una tabla temporal en el disco. 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 instrucció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.
Optimizar plan de ejecución de consultas
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 archivos, no los ordenes
- Actualizar las estadísticas del índice si está desactivada
- ANALIZAR TABLA <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
Optimizar la ejecución de la sesión
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:
Resumen
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 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 registro de aplicaciones es importante. Podrían configurarse en función de los intereses empresariales y reflejar las operaciones comerciales.
- Por último, contamos con 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.
Productos y servicios relacionados
Google Cloud ofrece una base de datos MySQL gestionada que se adapta a las necesidades de tu negocio, desde la retirada de tu centro de datos on‑premise, la ejecución de aplicaciones de software como servicio o la migración de sistemas empresariales fundamentales.
