Índices y rendimiento
Mapa conceptual
Qué problema resuelve
Sin índice, filtrar por una columna obliga a mirar fila por fila (barrido completo). Con millones de filas eso es lentísimo. Un índice mantiene una estructura ordenada sobre la columna que permite localizar valores con búsquedas binarias, leyendo poquísimas páginas.
El árbol B
El índice relacional típico es un árbol B balanceado: nodos ordenados, cada uno con muchos hijos, que apuntan hacia abajo, terminando en hojas que enlazan con las filas. Su altura crece logarítmicamente, así que localizar un valor cuesta pocas lecturas incluso en tablas enormes.
Búsqueda por igualdad y por rango
El árbol ordenado sirve tanto para localizar un valor exacto como para recorrer un rango (entre, mayor que) de forma contigua. Esta doble capacidad hace del árbol B el caballo de batalla de los índices.
Índice hash
Un índice hash ofrece acceso directo por igualdad en tiempo constante, ideal para claves que se buscan exactas, pero no sirve para rangos ni para ordenar. Cada motor ofrece distintos tipos de índice para distintos patrones.
Índices compuestos y orden de columnas
- Un índice sobre (a, b) acelera filtros por a, o por a y b juntos, pero no por b sola.
- El orden de las columnas importa: la primera es la que debe filtrarse con frecuencia.
- Coincide el orden del índice con el del ORDER BY para evitar una ordenación extra.
- Una columna de rango tras la de igualdad puede dejar de usar bien el índice.
Índice cubriente
Si el índice incluye todas las columnas que la consulta lee, el motor responde sin tocar la tabla: es un índice cubriente. Ese salto ahorra lecturas aleatorias y explica por qué conviene listar columnas en lugar de usar SELECT con asterisco.
El coste de indexar
Cada escritura (insert, update, delete) debe actualizar todos los índices de la tabla. Más índices significan lecturas rápidas pero escrituras lentas y más espacio. Un índice que nadie usa paga ese coste sin beneficio.
Cuándo NO ayuda un índice
- En tablas pequeñas, donde un barrido completo ya es instantáneo.
- En columnas muy poco selectivas (unos pocos valores distintos), donde el filtro no reduce casi nada.
- Cuando el motor decide, acertadamente, ignorar el índice por el coste de saltar a la tabla.
Decisión del optimizador
El motor estima cuántas filas devolverá y decide si usar el índice o barrer. Un filtro que abarca el 30 por ciento de la tabla suele ir mejor barrido que saltando por índice. Tener estadísticas frescas es clave para esa decisión.
Índices y JOIN
Unir dos tablas por una columna indexada permite buscar las filas coincidentes en lugar de cruzar todas. Sin índice en la columna de JOIN, la unión puede degradarse a un producto costoso. Indexar claves foráneas es una de las mejoras más rentables.
Fragmentación y mantenimiento
Con el tiempo, inserciones y borrados fragmentan los índices: quedan páginas semillenas y el recorrido encarece. Reconstruir o reorganizar índices periódicamente devuelve su eficiencia. El mantenimiento es parte del ciclo de vida.
Índices parciales y expresiones
Se puede indexar solo un subconjunto (índice parcial para filas con estado activo) o una expresión (el año extraído de una fecha). Cuando el patrón de consulta es fijo, estos índices apuntados dan el rendimiento con menos coste de mantenimiento.
Plan de lectura de un índice
- El optimizador estima el coste de cada ruta posible.
- Con índice: desciende el árbol hasta el rango buscado.
- Recorre las hojas en orden, obteniendo punteros a filas.
- Si no es cubriente, va a la tabla por los datos que faltan.
- Devuelve el resultado; si hay orden pedido y el índice lo da, ahorra ordenar.
| Situación | Índice ayuda | Por qué |
|---|---|---|
| buscar por clave exacta | sí | descenso logarítmico al valor |
| rango ordenado | sí | hojas enlazadas contiguas |
| columna con dos valores | no | baja selectividad, barrido basta |
| muchas escrituras, pocas lecturas | cuidado | mantener índice cuesta |
Tienes una consulta que filtra por la columna b de un índice compuesto sobre (a, b). ¿Se usará el índice de forma eficiente?
Sobrecarga de índices
Indexar «por si acaso» es un error caro: ralentiza escrituras, ocupa disco y confunde al optimizador. La regla es diseñar índices para patrones de consulta reales y medir. Menos índices, bien elegidos, superan a muchos al azar.
Verificar el uso
La mayoría de motores ofrecen vistas sobre qué índices se usan y cuáles no. Revisarlas permite retirar los que nadie toca y detectar consultas que deberían indexar y no lo hacen. El mantenimiento se guía por datos de uso, no por intuición.
Errores frecuentes
- Crear un índice en cada columna «por si acaso».
- Ignorar el orden de columnas en un índice compuesto.
- Esperar que un índice ayude en una columna con muy pocos valores distintos.
Cuántos más índices tenga una tabla, más rápidas serán todas las operaciones.
Índices en almacenes de datos
En entornos analíticos, donde se lee mucho y se carga por lotes, se toleran más índices y se usan estructuras distintas (columnares, de bitácora). El diseño óptimo depende del perfil de acceso: transaccional o analítico.
Vocabulario de índices
Ordena el diagnóstico para decidir un índice:
Consulta
Selectividad
Estructura
Medicion
Use The Index, Luke
Cómo piensan los índices y cómo escribir consultas que los aprovechen.
Una consulta filtra por estado y ordena por fecha sobre una tabla de millones de filas. Propón un índice compuesto y justifica el orden de sus columnas.
Documentación PostgreSQL
Referencia oficial de SQL y bases de datos.