Las malas prácticas en la formulación de tu consulta pueden traducirse en consultas lentas cuyo rendimiento no hace si no empeorar conforme la cantidad de datos que almacenas crece, por poner un ejemplo, lo que era una consulta de 200ms, se convierte en una de 4seg cuando la tabla registra cientos de miles de datos.
En este artículo voy a darte algunos tips que pueden ayudarte a reducir el tiempo de ejecución para tus consultas SQL, sobre todo si la escala de tu aplicación está en los cientos de miles o millones de registros en las tablas de la base de datos.
Muchas personas asumen que una consulta lenta es una señal de que es necesario introducir tecnologías adicionales a SQL, especialmente aquellas enfocadas en el manejo de Big Data. La realidad es que son pocos y muy específicos los casos en que la información supera las capacidades de un buen motor de base de datos, en la gran mayoría de los casos es más práctico y de mayor beneficioso solucionar el problema en SQL, revisar la estructura de tu información, entre otras cosas que cubriremos a continuación.
Medición
En el trabajo de optimización de rendimiento no hay nada más importante que la medición de los resultados, medir es el primer paso para mejorar el rendimiento de cualquier cosa. Sin medición no podemos decir si algún cambio produjo una mejora, o al contrario, empeoró los resultados; más importante aún, sin medición no podemos identificar los problemas antes de que se presenten frente a nuestros usuarios.
La regla 80/20
Nate Berkopec, en su libro The Complete Guide To Rails Performance hace mención del principio de Pareto, 80% of the output will come from 20% of the input, y cómo este mismo principio aplica al código, asumiendo que 80% de los beneficios de rendimiento están en optimizar una pequeña porción de todo nuestro código.
Esto quiere decir que en lugar de tratar de optimizar cada línea de código que nos topamos, primero debemos identificar los casos más graves de rendimiento y los que al modificarse o corregirse van a dar cuenta del mayor beneficio para la aplicación en general en velocidad y performance.
Sin medición, es imposible encontrar estos pequeños cambios que generarán el 80% de las mejoras, lo que puede significar que terminemos enfrascados en trabajo de micro optimizaciones, sin atacar los problemas más grandes de una aplicación, o en este caso, de una consulta.
Mediciones en el gestor
Medir el tiempo de ejecución de una consulta dependerá del gestor que uses, en mysql el tiempo de ejecución de una consulta aparece justo después de los resultados si usas la terminal. Adicionalmente puedes medir la duración de una consulta de la siguiente manera:
- Cambia el valor de la variable profilling, para que las consultas registren su tiempo de ejecución
mysql> set profiling=1;
- Ejecuta la consulta
mysql> SELECT * from tabla_gigante;
- Revisa el rendimiento con show profiles;
mysql> show profiles;
Mediciones en la aplicación
En muchas ocasiones, tus consultas SQL serán generadas por una librería en tu aplicación, si usas Rails será el ActiveRecord, en Laravel Eloquent, etc.
En estos casos, será tu elección de librerías la que te ayude a definir las herramientas para medir tus consultas, por ejemplo: el ActiveRecord de Rails imprime en log el tiempo de ejecución de tu consulta en modo de desarrollo.
Lo importante es configurar tu librería de conexión a la base de datos para que esta te pueda informar del tiempo de ejecución de tus consultas.
Herramientas independientes de tu stack
Así como cada framework y herramienta tiene librerías que te ayudarán a registrar tus mediciones, existen herramientas en la nube, que son agnósticas de la tecnología que uses, y que puedes configurar en tu aplicación para registrar información de tu base de datos.
Una de ellas, es Newrelic, una solución basada en la nube que configuras en cualquier framework o lenguaje: Rails, Django, Laravel, Node.js, Go, entre otros.
Newrelic ofrece una versión gratuita que te ayuda a visualizar qué tipo de consultas coonsumen más tiempo o se ejecutan más frecuentemente en tu aplicación. Además, puedes visualizar cuáles de tus rutas están demorando más en responder, para que puedas revisarlas en detalle.
Tips de optimización
El primer tip de optimización de una base de datos SQL, es invertir el tiempo en aprender SQL a detalle. En muchas ocasiones, un mismo resultado puede obtenerse con distintos tipos de consultas, en ocasiones, 3 consultas se pueden ejecutar en una sola.
Para que tú puedas analizar y concluir cuál es la mejor forma de obtener un resultado complejo, debes de conocer las distintas posibilidades y el impacto que tiene cada una de ellas. En ese sentido, te recomiendo ampliamente el Curso Profesional de Bases de Datos SQL, impartido aquí en CódigoFacilito.
Dicho esto, a continuación una serie de revisiones generales que puedes aplicar para muchos tipos de problemas de rendimiento en la ejecución de una consulta.
Índices
Los motores de bases de datos SQL pueden agregar índices a ciertas columnas que aceleran las queries.
Este proceso se actualiza en inserción por lo que hay un trade off al agregar un index, inserciones ligeramente más lentas y consultas más rápidas.
El uso correcto de índices puede acelerar consultas en tablas con muchos registros, pero sobre todo, el mal uso de ellos puedes traducirse en consultas muy lentas que afectan el rendimiento de la página
Una de las principales por las que una consulta se ejecuta muy lento, es que hagas operaciones sobre un campo que no tiene un index. En algunos casos observados en nuestra plataforma, agregar un índice a un campo usado para ordenamiento o filtrado de la información, ha resultado en consultas que toman la mitad del tiempo.
En otros escenarios los indexes están pero no se están usando, un claro ejemplo es el campo id que Rails usa para identificar de manera única cada registro en una tabla, por defecto este campo se indexa en la base de datos.
El campo id, además de identificar nuestros registros, también suele usarse para saber en qué orden fueron creados. Alternativamente, el campo created_at que guarda la fecha de creación también nos puede ayudar a ordenar los elementos según fueron agregados a la ttabla.
Considerando una tabla con aprox unos 3 millones de registros, estas dos consultas entregan el mismo resultado, este es un ejemplo visto en nuestra plataforma:
SELECT * from test ORDER BY created_at desc LIMIT 100;
SELECT * from test ORDER BY id desc LIMIT 100;
La única diferencia es que una tarda 0s y la otra casi 3s,
Al filtrar u ordenar, procura siempre hacerlo con el campo que tiene un index, y si no tiene, considera agregárselo.
Adicionalmente, algunos desarrolladores recomiendan mantener en revisión los indexes de una tabla para que sean eliminados en caso de que ya no sean utilizados, para acelerar las operaciones de inserción en la base de datos. Recueda que el uso de índices no es gratuito, tiene una repercusión en el tiempo de inserción, por lo que eliminar los indices no usados, puede ser una buena idea.
El problema de N+1 consultas
View post on imgur.com
El problema de N+1 queries describe los escenarios en los que por cada elemento de una lista con la que trabajamos, como en el ejemplo la lista de cursos se introduce un query nuevo, es solo una referencia, ya que en el mundo real es fácil introducir problemas de N+2, N+3 etc. donde cada elemento en la lista introduce múltiples queries.
En los escenarios donde las consultas son escritas manualmente, es prácticamente imposible encontrar uno de estos casos, normalmente los encontramos como resultado del mal uso de una librería para el manejo de la base de datos en nuestro código.
Para solucionar estos escenarios, la recomendación general es;hacer una carga anticipada de los registros usando alguno de los distintos mecanismos que nuestras librerías de base de datos ofrecen. Esto usualmente se traduce en el uso correcto de operaciones JOIN en nuestras consultas.
En este escenario, no importa si tus consultas tardan 10ms si estás ejecutando 100 de ellas. Reducir la cantidad de consultas que requiere obtener un resultado, también es optimización.
¡Importante! En algunos escenarios, una consulta individual que trae consigo todos los datos necesarios, puede ser más lenta que muchas consultas pequeñas, en algunos escenarios que hemos observado aquí en CódigoFacilito, hemos reducido la cantidad de consultas de una operación de cientos a un par, solo para ver cómo las nuevas consultas, aunque menos, tardan muchísimo en ejecutarse, de nuevo, es importante tener a la mano tus herramientas de medición, para no hacer modificaciones engañosas.
Campos caché
Aunque el concepto campos de caché acaba de ser inventado por mí al redactar este artículo, esta estrategia es muy común y dependiendo del escenario en el que funcionen estos campos, reciben nombres distintos, yo decidí agrupar estos escenarios bajo el nombre campos caché.
Caché de conteo
Considera un ejemplo como el de CódigoFacilito, donde tenemos una tabla cursos y una tabla adicional con los cursos que ha tomado cada estudiante, si bien la tabla cursos es relativamente pequeña con cientos de registros, la tabla de los cursos tomados por los alumnos tiene cientos de miles de registros.
Resulta que una de las consultas más comunes es saber cuántos cursos tiene cada alumno o cuáles son los cursos con más alumnos. Estos escenarios representan un problema de desafío porque para realizar el conteo hay que considerar el tamaño de cada tabla, en este ejemplo, la tabla con los cursos tomados por los alumnos es grande.
Para evitar que, en cada ocasión que queramos saber la cantidad de alumnos en un curso, consultemos la tabla grande, decidimos agregar una consulta a la tabla cursos, con el conteo de registros en la tabla grande:
| id | titulo | total_alumnos |
|---|---|---|
| 1 | Rails | 500 |
| 2 | Base de datos | 305 |
| 3 | JavaScript | 1000 |
Esta nueva columna se actualiza cada que un nuevo registro se actualiza en la tabla grande con los alumnos que toman cada curso. De esta manera, para consultar la cantidad de alumnos de un curso basta con realizar una consulta normal a una tabla relativamente pequeña.
El nombre caché para estos campos viene de el hecho de que éstos guardan información resultado de otras operaciones, en este caso un conteo, para que accedera a ésta información sea más rápido.
En estos escenarios se añaden operaciones de inserción, para hacer las operaciones de lectura más rápidas.
Otro tipo de campos caché
Casi cualquier operación costosa, llámense sumas, conteos, joins o promedios, pueden beneficiarse en velocidad con un campo caché.
Estos campos deben seleccionarse cuidadosamente, ya que los beneficios vienen con un costo en el momento de escritura.
Recientemente en un caso dentro de nuestra plataforma, reducimos el tiempo de ejecución de una consulta, colocando una relación con otra tabla que antes se calculaba vía JOINs.
Conclusión
Eston son solo algunos tips que puede seguir para optimizar tus consultas, hay muchos más que aplican en escenarios distintos a los que aquí hemos cubierto.
Para cada caso es importante: 1. Medir 2. Conocer formas alternas de llegar al mismo resultado
Recuerda que para sacar el mayor provecho del tiempo que inviertes en optimización, debes concentrarte en los problemas cuya solución traiga el mayor impacto en los resultados.
Si tienes algún otro tip, me encantaría leerlo en los comentarios.