Índices Avanzados: Clustered, Covering, Parciales y Funcionales
En el diseño físico de bases de datos, los índices son las estructuras auxiliares más determinantes para acelerar las lecturas. Sin embargo, un índice B-Tree básico no siempre es la solución óptima.
Comprender la diferencia entre índices agrupados (Clustered), índices de cobertura (Covering), índices parciales e índices basados en funciones permite reducir los tiempos de respuesta de segundos a fracciones de milisegundo y ahorrar gigabytes de almacenamiento.
1. Índice Agrupado (Clustered Index) vs. No Agrupado (Non-Clustered) #
1ESQUEMA DE TABLA HEAP (POSTGRESQL / ORACLE) TABLA CON ÍNDICE AGRUPADO (MYSQL INNODB / SQL SERVER) 2┌────────────────────────────────────────┐ ┌────────────────────────────────────────┐ 3│ [ Índice B-Tree ] ──puntero──► [ Fila ]│ │ [ Hojas del Árbol B-Tree del Índice ] │ 4│ (Almacena clave y puntero a bloque) │ │ (Las hojas contienen los DATOS REALES) │ 5└────────────────────────────────────────┘ └────────────────────────────────────────┘
- Índice Agrupado (Clustered Index / IOT):
- Los registros físicos de la tabla están ordenados y almacenados directamente dentro de las páginas hoja del propio índice B-Tree.
- Solo puede existir un único índice agrupado por tabla (típicamente asignado a la Clave Primaria).
- Ventaja: Las lecturas por clave primaria son instantáneas porque no requieren un salto adicional a la tabla base.
- Índice No Agrupado (Non-Clustered / Heap):
- Estructura separada que almacena la clave indexada y un puntero físico (TID / RowID) al bloque donde reside la fila completa.
2. Índice de Cobertura (Covering Index y Cláusula INCLUDE) #
Cuando una consulta solicita columnas que no están en el índice, el motor debe realizar un costoso acceso a disco denominado Búsqueda en la tabla (Heap Lookup / Key Lookup).
Un Índice de Cobertura incluye todas las columnas necesarias en la propia estructura del índice para que la consulta se resuelva íntegramente desde la memoria caché del índice (Index Only Scan):
3. Índices Parciales (Filtrados) #
En tablas con millones de registros, indexar columnas donde el 95% de las filas no son relevantes (por ejemplo, pedidos completados frente a los pocos pendientes) desperdicia RAM y ralentiza las inserciones.
Un Índice Parcial indexa únicamente las filas que cumplen un predicado WHERE:
1-- PostgreSQL / SQLite / SQL Server (Filtered Index) 2CREATE INDEX idx_pedidos_pendientes 3ON pedidos (fecha) 4WHERE estado = 'pendiente';
- Ahorro: Si solo el 2% de los pedidos están pendientes, el índice ocupará un 98% menos de espacio y se mantendrá permanentemente en memoria RAM.
4. Índices sobre Expresiones y Funciones #
Por defecto, si una consulta aplica una función sobre una columna (ej. WHERE LOWER(email) = 'ana@mail.com'), el optimizador no puede usar un índice estándar sobre email y se ve obligado a realizar un escaneo completo (Seq Scan).
Un Índice Funcional precalcula el resultado de la función:
Resumen del tema
Conceptos clave #
- Índice Agrupado (Clustered): organiza físicamente las filas de la tabla dentro de las hojas del árbol B-Tree del índice (máxima velocidad en lecturas por PK).
- Índice de Cobertura (
INCLUDE): contiene todas las columnas solicitadas en la consulta, permitiendo unIndex Only Scansin acceder a la tabla base. - Índices Parciales: indexan solo un subconjunto de filas mediante un filtro
WHERE, reduciendo drásticamente el consumo de memoria. - Índices Funcionales: indexan el resultado de expresiones (
LOWER(), operaciones matemáticas, campos JSON) para habilitar búsquedas indexadas sobre transformaciones.
Qué debes recordar #
Usa INCLUDE para evitar búsquedas lentas en la tabla (Heap Lookups) e índices parciales para optimizar estados infrecuentes como tareas pendientes o registros no eliminados.