Hace algunos meses, abordamos con el equipo una historia de usuario en donde nos informaban un lento rendimiento en una vista del sistema. Analizando la situación, verificamos que el problema provenía de una consulta que no tomaba el índice correcto para la petición que se estaba solicitando.
Esta circunstancia me motivó a escribir este artículo, en donde se analizará un caso simple de ejemplo, en el que se requiere crear un índice en una tabla para optimizar una consulta.
El objetivo no es definir qué son los índices o ahondar en sus características, sino analizar cómo enfrentar este tipo de problemas y qué estrategias podemos implementar para solucionarlos.
Para ello, vamos a revisar un ejemplo sencillo. Imagínense que tenemos una base de datos MySQL con la siguiente tabla users, donde la misma tiene un millón de registros y necesitamos filtrar todos los usuarios activos creados en los últimos 3 meses, que se hayan registrado mediante su celular y que esté ordenado por apellido y nombre.
Cuando el motor de base de datos ejecuta la consulta, verifica qué índice se ajusta mejor a la petición que estamos realizando. Si no existen índices en la tabla o ninguno se ajusta a nuestra consulta, se realizará un full table scan, que leerá cada uno de los registros aplicando los parámetros de búsqueda que hemos especificado. Esta operación tendrá muy mala performance, por lo que, es necesario analizar qué índice conviene crear para aumentar la velocidad de la consulta.
Algunos pensarán en la idea de crear índices por cada uno de los campos de la tabla, lo cual es un grave error. La creación masiva de índices en una tabla, conlleva efectos adversos:
- Cada índice ocupa espacio de almacenamiento en nuestra base de datos.
- La velocidad en las operaciones de insert, update y delete puede verse afectada, ya que los índices de la tabla deben ser actualizados, ante cada una de estas acciones.
Por lo tanto, la primera decisión que debemos tomar es si conviene crear un índice simple o uno compuesto. Los compuestos son útiles cuando tenemos campos que se consultan comúnmente juntos y ambos tienen un alto grado de cardinalidad (a mayor cantidad de valores únicos, mayor cardinalidad).
Si analizamos nuestra tabla y un posible modelo de negocio, podemos suponer que los campos created_at y active podrían aparecer juntos en varias peticiones. Pero si verificamos su cardinalidad, el campo active tiene una muy baja (solo 2 valores posibles toma este campo), lo que hace que la performance de crear un índice único por created_at y crear un índice compuesto por created_at y active sea similar.
Por ende, la mejor decisión para nuestro caso hipotético, es crear un índice simple por el campo created_at.
A continuación, dejo algunas screens de las pruebas realizadas:
Cantidad total de registros en la tabla
Plan de acción del motor de base de datos, donde se especifica que NO se va a utilizar ningún índice en la ejecución de la consulta.
La ejecución de la consulta tardó 1118 ms devolviendo 816 registros.
Creación de índice BTree por campo created_at
Plan de acción del motor de base de datos, donde se especifica que va a utilizar el índice users_created_at_IDX en la ejecución de la consulta.
La ejecución de la consulta tardó 74 ms devolviendo 816 registros.
Como podrán observar, aplicando el correcto índice, la consulta se ejecutó 15 veces más rápido. Esto nos da la pauta de la importancia de aplicar índices en nuestras tablas.
Para finalizar, les dejo algunas recomendaciones:
- No usar índices en tablas con poca cantidad de datos.
- Tener cuidado con la cantidad de índices que se utilizan en una tabla, ya que esto puede provocar una baja en el rendimiento de las operaciones de creación, modificación y eliminación.
- Elegir campos que tengan alta cardinalidad.
- En los índices compuestos, el orden en que se crean las columnas INFLUYE. Estos pueden hacer uso de todas sus columnas o pueden usar solo algunas, pero solo tomando la parte más a la izquierda. Por ejemplo, si tenemos un índice compuesto por las columnas A, B y C, este servirá como índice por: A, A-B y A-B-C.
- Si se utiliza un índice compuesto, tratar de elegir en primer lugar un valor con mayor cardinalidad.
