Cómo perfilar consultas SQL para un mejor rendimiento

En Servebolt, vivimos y respiramosel rendimiento .

El rendimiento de la base de datos no es una excepción.

Ejecutar una consulta ineficiente después de que un visitante del sitio web haga clic en un enlace degradará significativamente la experiencia del usuario .Tendrán que esperar a que se ejecute la duración completa de la consulta lenta, lo que puede demorar varios segundos, antes de que se lleve a cabo cualquier otra acción, como la representación de la página. Este tiempo de espera incluye no solo el tiempo necesario para que se ejecute la consulta, sino también cualquier tiempo adicional necesario para el procesamiento previo y posterior. Como resultado, una consulta mal diseñada puede ralentizar significativamente el rendimiento general de un sitio web, lo que resulta en una experiencia frustrante para el usuario.

El tiempo hasta el primer byte (TTFB) es una forma de medir cuánto tiempo se tarda en recibir el primer byte de datos después de que un usuario realiza una solicitud a un sitio web.También es una métrica clave utilizada por los motores de búsqueda para evaluar sitios. Cuando se activa una consulta lenta, afectará negativamente a TTFB. Cuanto más tarde en ejecutarse la consulta lenta, mayor será el TTFB, lo que dará como resultado un rendimiento general más lento del sitio web y una experiencia de usuario menos satisfactoria.

En esta guía, lo guiaremos a través de cómo perfilar consultas SQL, una parte crucial para mantener el rendimiento de las aplicaciones web que dependen de las respuestas de la base de datos. Este es un proceso que sienta las bases para poder comenzar a trabajar en la optimización de estas consultas para mejorar su rendimiento.

Descripción de la generación de perfiles de consultas SQL

A medida que desarrolla una aplicación web y comienza a funcionar a mayor escala, las consultas SQL que alguna vez se ejecutaron sin problemas pueden causar problemas de rendimiento. En términos generales, tiende a haber un número creciente de consultas que se ejecutan en una cantidad creciente de datos con un número creciente de solicitudes por segundo. Y cuando el rendimiento sufre, también lo hace la experiencia que tienen los usuarios cuando interactúan con su sitio, software o servicio.

La creación de perfiles de consultas es una forma de analizar las consultas de la base de datos, evaluar su rendimiento e identificar posibles problemas.

Al analizar e identificar estas consultas problemáticas, puede realizar mejoras específicas que pueden marcar una diferencia medible en el rendimiento de su base de datos. Esto, a su vez, permitirá una escalabilidad mejorada en el futuro, así como la satisfacción general del cliente, ya que las aplicaciones y los sitios tendrán una mayor capacidad de respuesta.

MariaDB (y MySQL) proporcionan varias herramientas y técnicas para la creación de perfiles de consultas, que trataremos en este artículo. Una vez identificadas las consultas lentas , el siguiente paso será optimizarlas. Este proceso incluye identificar la causa raíz del problema y realizar cambios en la estructura de las consultas para mejorar su eficiencia.

Cómo perfilar consultas SQL (7 métodos)

Comencemos por desglosar las diferentes herramientas y técnicas disponibles para identificar consultas lentas e ineficientes para que sepa dónde enfocar los esfuerzos de mejora:

1 – El comandoEXPLICAR EXTENDIDO

Una de las herramientas que se pueden usar para analizar sus consultas SQL es el comandoEXPLAIN .

Al ejecutar el comando EXPLAIN en una consulta, puede ver cómo se ejecuta la consulta, incluidos los índices que se utilizan y la cantidad de filas que se examinan.

EXPLAIN SELECT * FROM orders

JOIN customers ON orders.customer_id = customers.customer_id

WHERE customers.name = 'John Smith';

Cuando ejecuta el comando EXPLAINen una consulta, devuelve un conjunto de resultados con varias columnas, que incluyen:

• id: El identificador único de la consulta en el plan de ejecución
• select_type: el tipo de consulta, como SIMPLE o SUBQUERY
• table: La tabla que se está consultando
• type: el tipo de combinación utilizado, como JOIN o INDEX
• possible_keys: los índices que MariaDB o MySQL podrían haber usado para procesar la consulta
• clave: el índice que MariaDB o MySQL realmente usaron para procesar la consulta
• key_len: La longitud de la clave que se utilizó
• filas: el número de filas que MariaDB o MySQL estima que se examinarán para la consulta

Extra: contiene información adicional sobre la consulta, como si se realizó un escaneo completo de la tabla o si se usó una tabla temporal.

Al analizar la salida del comandoEXPLAIN, generalmente puede identificar posibles cuellos de botella en el rendimiento, como una indexación deficiente, tipos de combinación subóptimos o un gran número de filas examinadas.

Por ejemplo, si la columna de tipo muestra "TODO" en lugar de "índice",entonces la consulta está realizando un análisis completo de la tabla, lo que casi con certeza resultará en un rendimiento lento. Si la columna clave es NULL, entonces MySQL no está usando ningún índice, lo que también será lento. Si la columna de filas tiene un valor alto, significa que se están examinando muchas filas, lo que provoca una mayor degradación del rendimiento.

Preferimos usar la variación EXPLAIN EXTENDED para ayudar a proporcionar información adicional.

Nota: Si bien esto está en desuso en MySQL, todavía está disponible en MariaDB.

Al usar la opción EXTENDIDO, podrá ver información útil como la cantidad de filas examinadas, la cantidad de filas devueltas, información sobre el tipo de JOIN utilizado, el orden de las tablas escaneadas, los índices utilizados y cuánto tiempo. la consulta tardó en ejecutarse.

Así es como se ve el uso del comando EXPLAIN EXTENDED:

EXPLAIN EXTENDED SELECT * FROM your_table WHERE column_name = 'value';

En este ejemplo, el comando EXPLAIN mostrará una lista de pasos que tomará la base de datos para ejecutar la consulta, así como una lista de los recursos que usará.

Al usar este comando, podrá detectar más fácilmente los cuellos de botella en la consulta, lo que le permitirá realizar los cambios necesarios que ayudarán a aliviar eso y acelerar el rendimiento de la consulta.

Por ejemplo, el uso del comando EXPLAIN EXTENDED puede ayudar a identificar la necesidad de agregar índices, optimizar las condiciones de JOIN y limitar el número total de filas devueltas por la consulta.

También debe asegurarse de haber deshabilitado el almacenamiento en caché de consultas al realizar estas pruebas y optimizaciones para asegurarse de obtener resultados precisos. Para hacerlo, ejecute este comando primero cuando conecte su cliente.

SET SESSION query_cache_type=0;

Una vez que haya realizado estos cambios en su consulta, vuelva a probar su rendimiento para identificar la mejora que se ha logrado (si corresponde). Recuerde que, como con cualquier creación de perfiles y optimización de una consulta, el proceso es iterativo; espere usar el comando EXPLAIN EXTENDED, seguido de una prueba de rendimiento, varias veces.

2 – El comandoEXPLICAR ANALIZAR

Este comando se utiliza para analizar el plan de ejecución de una consulta y devolver métricas de rendimiento, como el tiempo real que tardó en ejecutarse la consulta y el número de filas que realmente examinó. Al analizar los resultados del comando EXPLAIN ANALYZE, puede identificar posibles cuellos de botella en la ejecución de la consulta, como la falta de índices o una gran cantidad de filas que deben examinarse.

3 – El registro de consultas lentas

Esta es una función integrada en MariaDB (y MySQL) que registra todas las consultas que tardan más de cierto tiempo en ejecutarse. El registro de consultas lentas se puede configurar para registrar consultas que toman más tiempo que un umbral específico, como un segundo.

En Servebolt, el registro de consultas lentas registra todas las consultas que tardan más de 1 segundo en ejecutarse. Esto se debe a que la mayoría de las consultas deberían ejecutarse en fracciones de segundo. En el contexto de una aplicación web, como un sitio que ejecuta WordPress, cargar una sola página requiere entre 10 y 100 consultas a la base de datos, todas las cuales deben ejecutarse secuencialmente antes de que la página pueda compilarse en HTML y devolverse al usuario.

La configuración actual de Servebolt Cloud mantiene registros de consultas lentos en un servidor de registro global. Si surge la necesidad, simplemente puede ponerse en contacto con nuestro equipo de soporte, y filtraremos el archivo para los registros relevantes y le proporcionaremos el resultado.

En sus propios entornos, puede habilitar el registro de consultas lentas agregando las siguientes líneas a su archivo de configuración de MariaDB o MySQL (my.cnf o my.ini):

log_slow_queries = /path/to/slow.log

long_query_time = 1

4 – Plan de explicación visual

Un plan de explicación visual proporciona una representación gráfica de la salida del comando EXPLAIN, lo que facilita la comprensión de la ejecución de una consulta y la detección de cualquier problema de rendimiento.

Nota: Los planes de explicación visual son útiles cuando se encuentra en el proceso de desarrollo de aplicaciones web.

En lugar de una salida de texto sin formato, muestra la ejecución de la consulta en una estructura de árbol , en la que cada nodo representa una tabla, un índice o una operación, y las conexiones entre ellos representan el orden de las operaciones.

Diferentes herramientas, como MySQL Workbench y EXPLAIN Analyzer, pueden generar planes de explicación visuales y ofrecer una interfaz interactiva para navegar por el plan de ejecución y examinar cada operación en detalle.

Por ejemplo, en MySQL Workbench, generar un plan explicativo visual es tan simple como ejecutar la consulta y hacer clic en el botón "Explicar plan " en la pestaña de resultados.Esto presenta una representación gráfica del plan de ejecución de consultas, junto con información detallada sobre cada operación. Esto le permite identificar cualquier problema de rendimiento y luego optimizar la consulta según sea necesario.

5 – El Sintonizador MySQL

MySQL Tuner es un script que comprueba el rendimiento y la configuración de un servidor de base de datos y proporciona recomendaciones para mejorar. Proporciona un resumen del estado actual del servidor, incluida información como el número total de consultas, el número de consultas lentas y el uso actual del grupo de búfer.

También se puede usar para verificar otras configuraciones, como la versión de la base de datos, el motor de almacenamiento en uso y la configuración de caché de consultas, y proporciona recomendaciones para optimizar estas configuraciones en función de la carga de trabajo actual.

Una de las principales diferencias con otras herramientas es que es una herramienta de línea de comandos que se puede ejecutar en el propio servidor o de forma remota, lo que facilita la automatización del proceso de supervisión y optimización del rendimiento de la base de datos.

Nota: Si su aplicación web (y su base de datos) ya están alojadas en Servebolt Cloud, nuestro equipo se especializa en esto y puede hacerlo mejor que cualquier recomendación que pueda brindar una herramienta.

6 – Perfiladores de consultas

Existen generadores de perfiles de consultas de terceros que se pueden usar para generar perfiles de consultas SQL, como MariaDB Enterprise Query Analyzer , Dataedo y Percona Toolkit . Los generadores de perfiles de consultas de terceros pueden proporcionar características y funciones adicionales en comparación con las herramientas integradas disponibles en MariaDB (o MySQL).

Nota: los generadores de perfiles de consultas son útiles cuando se encuentra en el proceso de desarrollo de aplicaciones web.

Por ejemplo, pueden ofrecer información más detallada sobre el rendimiento de las consultas, como los tiempos de ejecución y los tiempos de espera de bloqueo, y pueden proporcionar visualización de los datos de formas que no son posibles con las herramientas integradas.

Si las herramientas integradas son suficientes para sus necesidades, entonces no es necesario utilizar perfiladores de consultas de terceros. Sin embargo, si necesita información más detallada o funciones avanzadas, puede valer la pena considerar un generador de perfiles de terceros.

7 – Perfilado con herramientas de monitoreo

También hay una serie de herramientas de monitoreo, como Prometheus, Grafana y Nagios, que se pueden usar para generar perfiles de consultas y monitorear el rendimiento de sus bases de datos.

Prometheus es un sistema de monitoreo eficiente que puede recopilar, almacenar y consultar datos de métricas, lo que le permite obtener información valiosa en tiempo real.Se integra con MariaDB (y MySQL) para almacenar las métricas recopiladas y viene con Grafana para una visualización efectiva.

Grafana es una poderosa herramienta de análisis de código abierto que se puede usar para monitorear y visualizar datos recopilados de Prometheus.La configuración de paneles y alertas personalizados le permite controlar el rendimiento de su base de datos en tiempo real.

Nagios lo ayuda a vigilar la salud de su base de datos en todo momento.Se puede configurar para monitorear recursos clave como CPU, RAM y espacio en disco, al tiempo que realiza un seguimiento de otros servicios y dispositivos de red. Como es altamente configurable, es una gran herramienta para el monitoreo proactivo de consultas de bases de datos.

Con la ayuda de estas herramientas de monitoreo del servidor, puede realizar un seguimiento de los problemas de rendimiento y tomar medidas rápidamente, lo que le permite asegurarse de que su servidor de base de datos funcione sin problemas.

Técnicas comunes de optimización de consultas

Existen varias técnicas comunes de optimización de consultas que se pueden utilizar para mejorar el rendimiento de las consultas SQL:

1 – Indexación

Los índices son una forma de acelerar las consultas, particularmente aquellas que usan filtros(DÓNDE).El uso de índices da como resultado estructuras de datos en su motor de base de datos (MariaDB o MySQL) fuera de tablas específicas y apunta a los datos que está tratando de consultar. No entraremos en demasiados detalles en esta publicación, ya que el uso de índices para mejorar las consultas de la base de datos justifica un artículo propio, algo que planeamos cubrir en el futuro.

Por ejemplo, considere una tabla grande llamada "pedidos" que contiene millones de filas de datos, incluida información como el ID del pedido, el ID del cliente y la fecha del pedido. Si se ejecuta una consulta para recuperar todos los pedidos realizados por un cliente específico sin un índice en la columna de identificación del cliente, MariaDB tendría que escanear toda la tabla para ubicar los datos relevantes. Esto podría requerir mucho tiempo y recursos, especialmente para tablas grandes.

En términos generales, siempre que esté seguro de que ejecutará una consulta específica repetidamente y leerá cuestiones de rendimiento, crear un índice (o más de uno) puede ser el enfoque correcto para acelerar esa consulta.

En el contexto de WordPress, esto es muy común. Muchos complementos son creados por desarrolladores que (por conveniencia) usan tablas genéricas compartidas sin usar índices. Como resultado, también es un área en la que a menudo hay ganancias de rendimiento muy significativas.

Para ver los índices que existen en una tabla en particular,

Puede ver cualquier índice que exista en una tabla específica utilizando MOSTRAR ÍNDICE DESDE , como en el siguiente ejemplo para la tabla wp_postmeta:

MariaDB [db_name] > SHOW INDEX FROM wp_postmeta;

En un escenario, recientemente creamos dos índices para una tabla wp_postmeta:sb_postid_metakey y sb_postid_metakey_metaval.

Estos índices se agregaron en función de observar las principales consultas lentas y descubrir que todas eran relativamente similares por la característica de ser declaraciones SELECT que filtran usando WHERE además de muchas condiciones de comparación (AND/OR). Al ver esto, revisé los índices actuales de la tabla utilizada y ejecutéEXPLAIN EXTENDED en la consulta para validar aún más mi enfoque.

La consulta funcionaba principalmente y usaba la tabla wp_postmeta usandoJOIN.Según el orden en que esto sucedía, agregar estos índices permitiría que MariaDB (o MySQL) obtuviera su respuesta de los índices en lugar de escanear toda la tabla con todas sus filas.

CREATE INDEX sb_postid_metakey ON wp_postmeta (post_id, meta_key);

CREATE INDEX sb_postid_metakey_metaval ON wp_postmeta (post_id, meta_key, meta_value);

Esta es una combinación de "descubrir cosas" mediante el uso de las herramientas que tiene a su disposición (como se describe anteriormente), así como el conocimiento de los tipos de datos y el contenido de la base de datos. Esto de ninguna manera siempre funciona; incluso cuando lo hace, no siempre resulta en una mejora del rendimiento del 500 %. Tener un índice enorme puede terminar siendo más lento que escanear todas las filas, por lo que las consultas deben probarse antes y después de aplicar los índices para estar seguros.

Nota: cuando intente probar las velocidades de índice, querrá deshabilitar el almacenamiento en caché de consultas para la sesión, usando:

SET SESSION query_cache_type=0;

En este caso, antes de usar índices, la consulta tardaba 10.437 segundos en ejecutarse. Y después de crear los dos índices, la misma consulta tomó [# de segundos].

2 – Reducción del acceso a datos

Reducir el acceso a los datos , es decir, minimizar el número de filas y columnas a las que se debe acceder para ejecutar una consulta.Esto se puede lograr filtrando los datos que recupera la consulta, usando índices y particionando tablas grandes. Aunque no es algo que la mayoría de la gente necesite (o pueda) hacer, es un punto esencial a tener en cuenta al diseñar consultas de base de datos desde cero.

Por ejemplo, si una consulta de la base de datos está buscando datos sobre un usuario para fines de inicio de sesión, la consulta debe tener un LÍMITE 1, ya que claramente nunca se deben requerir más de los datos de un usuario.

Nota: Esto se relaciona más con el diseño de la base de datos que con la optimización.Si bien es importante para mantener el rendimiento, este esfuerzo es más relevante para los desarrolladores de complementos (en el contexto de WordPress) que para la mayoría de los usuarios finales.

Recuerde que antes de probar las velocidades después de realizar cualquier cambio en el acceso a los datos, debe asegurarse de haber deshabilitado el almacenamiento en caché de consultas ejecutando el siguiente comando:

SET SESSION query_cache_type=0;

3 – Uso de particiones de datos

Al particionar los datos en fragmentos más pequeños, las bases de datos se vuelven más eficientes y su administración requiere menos tiempo. Esta estrategia puede ayudar a reducir la cantidad de tiempo dedicado a los procesos de mantenimiento, como copias de seguridad y actualizaciones, así como a limitar la cantidad de datos que deben administrarse. En general, ayuda a mejorar el rendimiento y optimizar el uso de recursos.

Para particionar datos en una base de datos, puede seguir estos pasos:

1. Al seleccionar una tabla para particionar, asegúrese de elegir una que contenga una gran cantidad de datos y se beneficiaría de la división. Esto ayudará a optimizar su sistema y mejorar el rendimiento de las consultas.
2. Seleccionar el método de partición adecuado para su base de datos es crucial. Puede elegir entre partición de rango, lista, hash o clave, según la estructura de sus datos y las consultas que planea ejecutar. Asegúrese de elegir el que mejor se adapte a sus necesidades para optimizar el rendimiento y los resultados.
   
   1. La partición por rangos es la opción ideal cuando tiene datos que se pueden dividir en ciertos rangos.Por ejemplo, si tiene una tabla con datos de varios años, puede crear una partición de rango para organizarla mejor. Podría basarse en la fecha o el valor numérico de la columna en cuestión.
   2. La partición de listas es una técnica eficiente para manejar datos que se pueden segregar fácilmente en varios grupos según un parámetro particular.Por ejemplo, tienes una tabla con la información de los empleados categorizados por Departamento; esto requiere el uso de partición de lista.
   3. La partición hash es una estrategia eficaz para organizar los datos en clústeres de igual tamaño en función del valor hash de una columna específica.Esto permite una distribución uniforme de los datos en múltiples particiones, lo que lo convierte en una excelente opción para distribuir datos de manera eficiente.
   4. La partición clave es similar a la partición hash, pero la principal diferencia es que utiliza un valor de columna específico como base para dividir los datos en diferentes grupos.Esto lo convierte en una opción ideal para conjuntos de datos que se pueden dividir en grupos separados en función de un identificador único o clave natural.
3. Al crear una tabla particionada, puede dividir efectivamente la tabla original en otras más pequeñas. Esto se logra agregando una cláusula de partición en la declaración CREATE TABLE, donde especifica el método y las condiciones deseadas para la segmentación. Hacer esto puede ayudar a mejorar el rendimiento de las consultas y también hacer que la gestión de datos sea más eficiente.
4. Puede copiar rápidamente datos de la tabla original a la recién particionada usando la declaración INSERT INTO… SELECT. Esto completará fácilmente su tabla particionada con toda la información relevante.
5. Las aplicaciones ahora deben reconfigurarse para aprovechar la tabla particionada. Esto reemplazará la tabla original y hará que sus aplicaciones sean más eficientes.
6. Antes de ejecutar cualquier prueba para evaluar la mejora potencial del rendimiento, será esencial deshabilitar primero el almacenamiento en caché de consultas ejecutando el comando: SET SESSION query_cache_type=0;
7. Para asegurarse de que su tabla particionada funcione sin problemas, es importante vigilar de cerca su rendimiento. Si nota algún problema, podría ser útil ajustar las condiciones de partición o cambiar a otro método. El monitoreo regular de sus particiones lo ayudará a maximizar su potencial.

Nota importante sobre actualizaciones de secuencias de comandos y tablas particionadas

Si bien el particionamiento de las bases de datos puede marcar una diferencia positiva en la eficiencia, es importante tener en cuenta los posibles problemas causados ​​por la ejecución de secuencias de comandos de actualización para cambiar el esquema de la base de datos. Es esencial que se tengan en cuenta las tablas particionadas al crear secuencias de comandos para estas actualizaciones. Si las tablas particionadas no se tienen en cuenta en los scripts de actualización, podría haber problemas potenciales que casi con toda seguridad darán lugar a un mal funcionamiento del sitio.

Por ejemplo, si se crea una secuencia de comandos para agregar una nueva columna a una tabla particionada, es posible que solo altere una partición, creando inconsistencias y problemas dentro de los datos. Del mismo modo, si se crea un script de actualización para agregar un índice a una tabla particionada, solo puede generar el índice en una partición, lo que genera un rendimiento más lento y resultados inconsistentes.

Para evitar tales problemas, los scripts de actualización deben diseñarse para considerar las tablas particionadas. Esto podría implicar ejecutar el script en cada partición individualmente o revisar los scripts para que funcionen con tablas particionadas. También es importante realizar pruebas exhaustivas para asegurarse de que el proceso de actualización no genere problemas inesperados o pérdida de datos.

4 – Redis

Para los clientes de Servebolt, Redis es un complemento (pago) que puede ayudar con la optimización de consultas.

Redis (a veces conocido como servidor de diccionario remoto) es una solución de código abierto que almacena datos en la memoria y se puede usar para el almacenamiento en caché, una base de datos o incluso como intermediario de mensajes. Puede integrarse con una base de datos para mejorar el rendimiento, actuando como un intermediario eficiente entre la aplicación y la base de datos.

Funciona para mejorar el rendimiento y los tiempos de respuesta de las aplicaciones al reducir la carga en la base de datos. Esto se hace almacenando datos de uso frecuente en Redis en lugar de la base de datos para cada solicitud, lo que ahorra un tiempo considerable.

Al configurar correctamente el complemento, Redis se puede usar con una base de datos para optimizar la ejecución de consultas. Cuando los datos requeridos no están presentes en Redis, la aplicación los recuperará de la base de datos y los almacenará en Redis para uso futuro. Esto hace que la recuperación de datos sea mucho más rápida y eficiente.

Al usar este enfoque, la aplicación puede beneficiarse del rápido acceso en memoria de Redis y también almacenar y acceder a datos de la base de datos según sea necesario.

Recuerde que si está implementando Redis por primera vez, deberá deshabilitar el almacenamiento en caché de consultas antes de ejecutar cualquier prueba de rendimiento. Para hacer esto, use el comando:

SET SESSION query_cache_type=0;

Conclusión

El ecosistema de MariaDB y MySQL cuenta con una amplia gama de herramientas y métodos para facilitar la detección de cuellos de botella en las ejecuciones de consultas de bases de datos, lo que le permite mejorar el rendimiento de sus aplicaciones web.

Es probable que se produzcan ralentizaciones durante la ejecución de cualquier aplicación. Tratar de evitarlos es excelente, pero en última instancia, debe saber dónde buscar cuando comience a diagnosticar problemas de rendimiento. Dependiendo del tamaño y la naturaleza de las bases de datos que ejecute, este es un proceso iterativo que requiere monitoreo continuo, resolución de problemas y mejoras continuas para mantener el rendimiento de sus bases de datos a un alto nivel.