Optimización de Consultas SQL: Bases de Datos Relacionales
En el campo del desarrollo web orientado, la optimización en la experiencia de usuarios y clientes es primordial para darles facilidades empleando las aplicaciones.
Desde Pragma, siendo una organización que impulsa la transformación digital de las empresas, ofrecemos la mejora en el rendimiento en busca de un desempeño adecuado y óptimo del código empleado para las consultas a las bases de datos vinculadas a los servicios , lo que garantiza una mejor experiencia y, en ese orden de ideas, mayor satisfacción para el cliente.
Detrás de la interfaz con la que interactúa el usuario, se encuentra el desarrollo backend donde las consultas SQL cumplen la labor del medio por el cual leemos, escribimos y modificamos las bases de datos asociadas a nuestra aplicación. El evitar los efectos desfavorables, que vienen de la mano con una mala gestión en éstas, contribuye con la riqueza en la estructuración de nuestro código y, por consiguiente, con el funcionamiento óptimo de nuestra aplicación.
Algunos de los factores que pueden comprometer el comportamiento ideal en nuestras consultas pueden relacionarse con un mal diseño de la base de datos empleada o con una formulación inadecuada en las consultas en sí.
Estos factores pueden producir sobrecargas en los recursos asociados a la aplicación hasta desencadenar tiempos de respuesta inadecuados y mayores a los planificados, lo que genera un rendimiento deficiente proveniente del backend afectando todo el servicio, posiblemente, causando costos operativos adicionales a los previstos.
Para procurar que nuestra aplicación no sea afectada, existen diversas estrategias y técnicas estructurales, que, al ser aplicadas y comprendidas por el pragmático backero potenciarán de manera significativa el rendimiento del código velando por una aplicación adaptable y, por lo tanto, escalable. Como desarrolladores, debemos considerar que la optimización de las consultas SQL no consiste únicamente en una mejora en la velocidad al acceder a las bases de datos, sino en asegurar la integridad y la seguridad de los datos.
El buen manejo de las consultas SQL es una práctica esencial para el backero, quien se encuentra en la búsqueda constante de la minimización de impactos en el sistema, así, en este artículo vamos a profundizar en algunas de las técnicas y estrategias, con el fin de comprender el proceso para su implementación adecuada.
Relevancia de la eficiencia en el backend
Sabemos que el backend en una aplicación cumple con la función del motor, es decir, permite que el vehículo, que en esta analogía es el servicio, encienda y arranque de la manera que esperamos, así, como en Pragma somos los pilotos de nuestro destino, sabemos la importancia de planear correctamente las conexiones del motor con los piñones, cables y engranajes.
Cuando subimos en el vehículo de nuestro destino, como usuarios, buscamos una experiencia fluida, sin baches en el camino, tardando el menor tiempo posible en llegar a nuestro destino. De igual forma, esperamos que, al presionar el acelerador, aceleremos inmediatamente y, claramente, no esperamos frenar el momento de acelerar; esto corresponde a la experiencia del usuario proporcionada por el backend. Cada acción se debe efectuar como tenemos previsto, sin largos tiempos de respuesta y con facilidad.
En nuestro viaje, un elemento esencial para el vehículo es el combustible, sin este, ni siquiera avanzaríamos un par de centímetros en el camino. Actualmente existen diversos “combustibles” donde, como usuarios, preferimos un vehículo que consuma la menor cantidad posible de estos. Va de la mano del backero asegurarse de que los recursos para el funcionamiento de la aplicación sean empleados de manera eficiente, permitiendo al cliente obtener rentabilidad del sistema.
Por otro lado, el diseño y construcción adecuada de un motor, permite que este sea replicado a mejores modelos del vehículo a medida los pragmáticos vamos requiriendo mejores funcionalidades para facilitar nuestro viaje. Un backero debe garantizar que, a medida que más usuarios empleen el sistema e ingresa más información a este, la aplicación pueda adaptarse a las modificaciones sin afectar su rendimiento, y que esté preparada para la escalabilidad necesaria para satisfacer las necesidades del cliente.
Así, como pilotos de nuestro destino, siempre queremos dar con el mejor vehículo al que podamos acceder para nuestro camino. El backero tiene las habilidades para marcar una diferencia entre los sistemas y aplicaciones que como brindamos a nuestros clientes aportándole una ventaja significativa competitiva a la empresa para la que trabaja.
De este modo, entre mayor sea la eficiencia en el backend, mejor es la experiencia del usuario, más escalabilidad tienen los sistemas, se emplean menos costos operativos y, en nuestro caso, se mantiene a Pragma como la empresa de desarrollo web y mobile confiable que ya es.
Normalización de bases de datos
Al diseñar las bases de datos relacionales debemos minimizar repetición e inconsistencia en la información, esto lo podemos realizar mediante una organización adecuada que vele por la integridad de la base de datos. Para lograrlo, podemos recurrir a la normalización de las bases de datos, este proceso consta de una serie de normas estructurales para relacionar los datos de la tabla de manera óptima. Veamos un ejemplo con la siguiente tabla:
| ID | Nombre | Chapter | Cliente |
|----|--------------|----------|-----------|
| 1 | Pragmatico 0 | Calidad | Cliente 0 |
| 2 | Pragmatico 1 | Backend | Cliente 1 |
| 3 | Pragmatico 2 | Backend | Cliente 0 |
| 4 | Pragmatico 3 | Frontend | Cliente 2 |
| 5 | Pragmatico 4 | Calidad | Cliente 1 |En nuestra tabla tenemos la información de los pragmáticos, el chapter al que corresponden y el cliente o proyecto al que están asignados, podemos ver que hay datos que se repiten en las columnas de ‘Chapter’ y ‘Cliente’, es decir, que nuestra tabla no está normalizada; veamos algunas de las reglas de normalización para ajustar nuestra información:
- Primera Forma Normal: Esta regla establece que cada columna debe contener valores atómicos e indivisibles. En nuestra tabla, todas las columnas cumplen con la atomicidad habiendo un único valor en cada celda. Sin embargo, podemos dividir nuestra información en tablas separadas debido a la repetición en las columnas mencionadas anteriormente.
- Segunda Forma Normal: Esta regla elimina las dependencias parciales con el de que cada columna no clave dependa completamente de la clave primaria. En nuestro caso la columna ‘ID’ corresponde a la columna primavera y vemos que las columnas ‘Chapter’ y ‘Cliente’ no cumplen con esta regla debido a que varios pragmáticos pueden pertenecer al mismo chapter y al mismo cliente. Para que nuestra tabla sea normalizada, manejaremos dos tablas adicionales relacionándolas con claves particulares.
Así, nuestra tabla correspondiente a los chapters queda de la forma:
| ID | Enfoque |
|----|----------|
| 1 | Calidad |
| 2 | Backend |
| 3 | Frontend |Y nuestra tabla de los clientes:
| ID | Nombre |
|----|-----------|
| 1 | Cliente 0 |
| 2 | Cliente 1 |
| 3 | Cliente 2 |Finalmente, después de las normalizaciones siguiendo las reglas de la Primera Forma Normal y la Segunda Forma Normal, 1NF y 2NF respectivamente, nuestra tabla normalizada sería de la forma:
| ID | Nombre | Chapter | Cliente |
|----|--------------|---------|---------|
| 1 | Pragmatico 0 | 1 | 1 |
| 2 | Pragmatico 1 | 2 | 2 |
| 3 | Pragmatico 2 | 2 | 1 |
| 4 | Pragmatico 3 | 3 | 3 |
| 5 | Pragmatico 4 | 1 | 1 |Empleando las relaciones de las claves particulares ‘Chapter_ID’ y ‘Cliente_ID’. Para notación, existen reglas de las formas normales partiendo de la 1NF hasta la 5NF, siendo las restantes:
3. Tercera Forma Normal: Esta regla elimina las dependencias transitivas asegurándose que cada columna no clave dependa únicamente de la columna primaria.
4. Cuarta Forma Normal y Quinta Forma Normal: Son reglas específicas para evitar la redundancia de datos y dependencias multivaluadas.
Después de observar el funcionamiento de la normalización, es importante recordar que como desarrolladores debemos tener en cuenta que existen casos en los cuales realizar una desnormalización de la base de datos, como agregar datos repetidos controlados, puede llegar a contribuir favorablemente al rendimiento de las consultas teniendo en cuenta el objetivo principal de la normalización; simplificar la información y atribuir eficiencia en el diseño de la base de datos.
Identificación de consultas problemáticas
Teniendo en cuenta la importancia de la eficiencia al nivel del backend, veamos algunos métodos y herramientas para detectar consultas SQL que pueden estar generando ineficiencia en nuestro código.
La primera herramienta que debemos emplear para identificar las consultas potencialmente problemáticas en nuestro código no consta únicamente de una herramienta en sí, consta de una buena práctica del backero siendo esta la revisión del código fuente de la consulta SQL, con el fin de dar con consultas que, de manera innecesaria, realicen subconsultas costosas o recorran completamente una o más tablas. Para esta buena práctica nos podemos apoyar en herramientas de análisis de código como el confiable GitHub y la programación en pares.
Otra de las prácticas adquiridas según acumulamos experiencia consta de analizar el comportamiento de nuestra consulta verificando los logs, que nos proporcionan el registro generado por la consulta permitiéndonos tener una idea general del rendimiento de nuestra petición en la base de datos, dándonos información sobre tiempos de respuesta y posibles errores generados. Esto lo podemos realizar por medio de herramientas, como el gestor de logs de Splunk, que nos facilita la lectura de la información generado por un gran número de consultas mediante la monitorización y análisis de los datos.
Existen también herramientas elaboradas para el monitoreo específico de bases de datos, como Dynatrace, que nos proporciona información en tiempo real del rendimiento de la base de datos al momento de la consulta, incluyendo los tiempos de ejecución preciosos y la memoria empleada. También podemos emplear herramientas para realizar un análisis y seguimiento detallado a las consultas de manera independiente, como MySQL Query Profiler o la funcionalidad de PostgreSQL, pg_stat_statements.
Mediante estas herramientas, el backero adquiere la información necesaria para identificar áreas de mejora al momento de diseñar las consultas SQL. Una vez identificadas estas oportunidades de mejora, contamos con técnicas y estructuras asociadas a SQL que nos permiten optimizar las consultas.
Ahora, además de contar con herramientas para el monitoreo y lectura de nuestras consultas, una vez detectadas las que pueden estar generando sobrecargas en el sistema y tiempos de respuesta encima de lo esperados, podemos recurrir a la creación de estructuras, como los índices, en nuestra base de datos para mejorar la eficiencia y rendimiento de la recuperación de datos.
Ilustremos el funcionamiento de los índices con un ejemplo, tomemos una tabla llamada “pragmaticos”, la cual contiene la información de los empleados de Pragma sin indexar:
CREATE TABLE pragmaticos (
id INTEGER PRIMARY KEY,
firstName TEXT NOT NULL,
lastName TEXT NOT NULL,
email TEXT NOT NULL,
...
);Realicemos una búsqueda de un pragmático específico por apellido:
SELECT * FROM pragmaticos WHERE lastName = 'ejemplo';Esta consulta tendría que recorrer toda la tabla para dar con el registro del pragmático en particular, es decir. Recorre todas las filas de tabla y compara el valor en la columna ‘lastName’ teniendo una complejidad O(n) siendo n la cantidad de pragmáticos. Creemos ahora el índice en nuestra tabla:
CREATE INDEX idx_lastName ON pragmaticos (lastName);Al crear este índice en la columna ‘lastName’ la base de datos cuenta ahora con una estructura con una lista ordenada de los apellidos de los pragmáticos con un puntero a la ubicación de las filas que coinciden en la tabla ‘pragmaticos’, puntero al que recurrirá en las consultas compuestas con la cláusula ‘WHERE’, reduciendo la complejidad de la búsqueda a O(log n) siendo una búsqueda binaria eficiente en la mayoría de los casos.
Los índices no solo mejoran el rendimiento para las consultas de búsqueda, también pueden acelerar las consultar compuestas por las cláusulas ‘ORDER BY’ o ‘GROUP BY’, ilustremos este funcionamiento con otro ejemplo; tomemos la tabla ‘clientes’ donde se encuentran los clientes asociados a Pragma junto con los pragmáticos que hacer parte de sus proyectos:
CREATE TABLE clientes (
id INTEGER PRIMARY KEY,
cliente TEXT NOT NULL,
numPrag INTEGER NOT NULL
);Para organizar los clientes de la tabla de modo que el cliente con mayor número de pragmáticos en su proyecto se priorizado, sin contar con un índice en la columna ‘numPrag’, la base de datos debe realizar una ordenación de la información en la memoria asociada resultando en una mayor cantidad de recursos y de tiempo para ordenar la tabla, donde es inconveniente es proporcional a la cantidad de clientes registrados. Creemos ahora un índice en la columna ‘numPrag’:
CREATE INDEX idx_numPrag ON clientes (numPrag);Con este índice los datos el número de pragmáticos asociados a los clientes se pre ordenan gracias a la propia estructura y facilita el efectuar la consulta de ordenamiento:
SELECT * FROM clientes ORDER BY numPrag ASC;Una vez efectuada la cláusula ‘ORDER BY’, la base de datos emplea el índice ‘idx_numPrag’ para acceder a los datos ya ordenados, lo cual ahorra tiempo y memoria, es decir, recursos.
Otra manera de mejorar el rendimiento por medio de la creación de un índice se puede realizar por medio de la cláusula ‘JOIN’, donde, en el mismo orden de ideas, al realizar la búsqueda de los datos coincidentes en la consulta de unión, se evita recorrer y comparar todas las filas de las tablas a unir mejorando el tiempo de respuesta en la recuperación de los datos.
En resumen, el utilizar los índices de manera eficiente no solo mejorando los tiempos de respuesta de las consultas de búsqueda, sino también para las consultas de unión, agrupación y ordenación. Así, como desarrolladores, podemos optimizar el rendimiento de nuestros servicios y, por tanto, mejorar la experiencia de nuestros usuarios.
Realizar las optimizaciones detectadas
Hemos resaltado de la importancia de la detección de las oportunidades de mejora en nuestras consultas SQL, sin embargo, es nuestra labor principal siempre velar por el correcto funcionamiento de nuestro código, por ello, es importante comprender del impacto de nuestras modificaciones en el funcionamiento de la aplicación y en las consultas pre-existentes, apoyándonos en las métricas establecidas en la planeación asociadas, claramente, a tiempos de respuesta y recursos del sistema.
Así, al hacer nuestras modificaciones, lo ideal es realizarlas paulatinamente evitando un impacto masivo en el código, apoyándonos en nuestro sistema de control de versiones, Git en la mayoría de los casos, el cual nos permite identificar y corregir los problemas según surjan, reduciendo las interrupciones en la aplicación velando. Lo más recomendable es realizar estas pruebas íntegras en entornos de desarrollo contemplando escenarios críticos donde nuestras optimizaciones sean realmente puestas a prueba.
El desarrollador backend no es el encargado específicamente de las pruebas que contemplen casos de carga y estrés, sin embargo, es importante tener en cuenta que dichas pruebas, realizadas con herramientas como Apache JMeter, evalúan el sistema en condiciones de carga máxima identificando límites en el funcionamiento del sistema y verificando si las optimizaciones empleadas realmente son eficientes en estos escenarios a comparación de las respuestas previas, por lo cual, es primordial realizarlas.
Hay que resaltar que posterior a nuestras optimizaciones, nuestro trabajo no termina, debemos realizar un monitoreo continúo a nuestro código siendo posible que una re-evaluación de las optimizaciones sea necesaria.
