Tipos de índices en MySQL

En MySQL, los índices se clasifican lógicamente en índices simples, índices primarios, índices únicos, índices de texto completo e índices compuestos. Este artículo se enfoca en los índices compuestos, que son ampliamente utilizados y tienen conceptos clave como el principio de coincidencia de prefijo izquierdo.

Estructura de B+Tree para índices

Un índice simple en una estructura B+Tree almacena un par clave-valor por nodo. Los índices compuestos, por otro lado, utilizan múltiples campos como clave. Por ejemplo, considere una tabla con campos grupo y rol que forman un índice compuesto.

Análisis de ordenamiento

En un índice compuesto (grupo, rol), el campo grupo está ordenado secuencialmente, mientras que rol no tiene un orden fijo en general. Sin embargo, cuando el valor de grupo es igual, los valores de rol se vuelven ordenados. Esto refleja el comportamiento de clasificación múltiple: primero por el primer campo, luego por el segundo en caso de empate.

Principio de coincidencia de prefijo izquierdo

Este prinicpio es fundamental para el uso eficiente de índices compuestos. Analicemos ejemplos de consultas SQL.

Consulta que cumple el principio

Considera la siguiente consulta:

SELECT * FROM usuarios WHERE grupo = 1 AND rol = 2;

En el B+Tree, el campo grupo está ordenado, permitiendo una búsqueda binaria para localizar grupo = 1. Luego, dentro de ese conjunto, rol está ordenado, por lo que se puede aplicar otra búsqueda binaria para rol = 2. Así, el índice se utiliza completamente.

Consulta que no cumple el principio

Ahora, considera:

SELECT * FROM usuarios WHERE rol = 2;

El orden de rol depende de que grupo esté definido. Sin un valor específico para grupo, rol no tiene un orden garantizado en el B+Tree, por lo que no se puede usar una búsqueda binaria. El índice no se aplica.

Fallo en consultas de rango

Para consultas con operadores de rango, como >, el principio se interrumpe. Ejemplo:

SELECT * FROM usuarios WHERE grupo > 1 AND rol = 2;

El campo grupo está ordeando, por lo que el índice puede localizar valores mayores que 1. Sin embargo, dentro de ese rango, los valores de grupo varían, lo que hace que rol pierda su orden. Por consiguiente, rol no beneficia del índice.

Fallo en consultas LIKE

Las consultas LIKE también están sujetas al principio de prefijo izquierdo. Considera ejemplos con el campo nombre:

• WHERE nombre LIKE 'a%': Prefijo a la derecha, coincide con el inicio del índice. A veces puede usar el índice.
• WHERE nombre LIKE '%a%': Prefijo en cualquier posición, generalmente no usa el índice.
• WHERE nombre LIKE '%a': Prefijo a la izquierda, no usa el índice.

La razón es que los índices en cadenas de texto se ordenan carácter a carácter desde el inicio. Solo coincidencias de prefijo izquierdo pueden aprovechar el orden.

Escenarios prácticos de creación de índices

Apliquemos estos conceptos a consultas comunes para decidir cómo crear índices compuestos.

Escenario 1: Consulta con múltiples igualdades

Para una consulta como:

SELECT * FROM empleados WHERE departamento = 'IT' AND nivel = 'Senior' AND antiguedad = 5;

Se debe crear un índice compeusto. La mejor práctica es ordenar los campos por su selectividad. Por ejemplo, si la selectividad de mayor a menor es nivel, departamento, antiguedad, entonces el índice debería ser (nivel, departamento, antiguedad). El optimizador de MySQL reorganizará los campos en la cláusula WHERE para usar el índice.

Escenario 2: Consulta con rango y igualdad

Para una consulta con rango:

SELECT * FROM ventas WHERE monto > 1000 AND region = 'Norte';

Si se crea un índice (region, monto), ambos campos se pueden aprovechar. En cambio, un índice (monto, region) solo usaría monto. El optimizador ajustará la ejecución.

Escenario 3: Consulta con IN y rango

Para consultas con IN:

SELECT * FROM productos WHERE categoria IN ('A', 'B') AND precio > 50;

El operador IN se trata como igualdad múltiple, por lo que un índice (categoria, precio) es eficiente.

Escenario 4: Consulta con ORDER BY

Para ordenar resultados:

SELECT * FROM pedidos WHERE estado = 'pendiente' ORDER BY fecha;

Un índice compuesto (estado, fecha) evita la clasificación adicional en la ejecución.