Índices en bases de datos: cómo funcionan y cuándo crearlos

¿Por qué una consulta que respondía al instante cuando la tabla tenía mil filas empieza a tardar segundos cuando tiene dos millones? En la gran mayoría de los casos, la respuesta es la misma: la base de datos está leyendo la tabla completa, fila por fila, para encontrar unas pocas. Un índice bien elegido convierte esa búsqueda en un recorrido corto y directo.

Este artículo responde las preguntas que surgen cuando empiezas a trabajar con índices: qué son, cómo funcionan por dentro, cómo saber si una consulta los usa y, sobre todo, cuándo vale la pena crearlos y cuándo no. Los ejemplos usan PostgreSQL, pero los conceptos aplican casi igual a MySQL, MariaDB, SQL Server y SQLite.

¿Qué es un índice, en una frase?

Es una estructura de datos adicional, separada de la tabla, que mantiene los valores de una o varias columnas ordenados y apuntando a las filas donde están. Funciona como el índice alfabético al final de un libro técnico: en lugar de hojear las 600 páginas para encontrar "transacciones", vas al índice, encuentras la palabra en segundos y saltas a la página exacta.

La analogía también sirve para entender el costo. Cada vez que el autor agrega un párrafo que menciona "transacciones", alguien tiene que actualizar el índice del libro. En una base de datos pasa lo mismo: cada INSERT, UPDATE o DELETE sobre una columna indexada obliga a actualizar el índice.

¿Cómo funciona por dentro?

El tipo de índice por defecto en casi todos los motores es el árbol B (B-tree). Imagina una pirámide de páginas:

• La página raíz, arriba, contiene unos pocos valores que dividen el rango total. Por ejemplo: "menores que M van a la izquierda, mayores a la derecha".
• Las páginas intermedias repiten la idea con rangos cada vez más pequeños.
• Las páginas hoja, abajo, contienen todos los valores ordenados y, junto a cada uno, la ubicación de la fila en la tabla.

Como cada página puede guardar cientos de entradas, el árbol crece muy poco en altura. Una tabla con millones de filas suele necesitar apenas tres o cuatro niveles. Encontrar un valor implica leer una página por nivel en lugar de recorrer toda la tabla.

Y como las hojas están ordenadas y enlazadas entre sí, el árbol B sirve para algo más que igualdades: también resuelve rangos (BETWEEN, >, <), prefijos de texto (LIKE 'abc%') y ordenamientos (ORDER BY) sin necesidad de ordenar en memoria.

¿Cómo sé si mi consulta usa un índice?

Pregúntale a la base de datos. En PostgreSQL, EXPLAIN muestra el plan que piensa usar, y EXPLAIN ANALYZE además ejecuta la consulta y muestra los tiempos reales:

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 4821;

Sin índice en cliente_id, verás algo como Seq Scan on pedidos, es decir, un recorrido secuencial de toda la tabla, acompañado de una línea Rows Removed by Filter con un número enorme: todas las filas que leyó y descartó. Ahora crea el índice y repite:

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 4821;

El plan cambia a Index Scan o Bitmap Heap Scan usando idx_pedidos_cliente, y el tiempo de ejecución baja de forma notable. Cuánto baja depende del tamaño de la tabla y del hardware, así que mide en tu propio entorno en lugar de confiar en cifras genéricas.

Ten cuidado con EXPLAIN ANALYZE en sentencias que modifican datos: como ejecuta la consulta de verdad, un DELETE analizado borra las filas. Envuélvelo en una transacción con BEGIN y ROLLBACK si necesitas medirlo.

¿Qué columnas merecen un índice?

Busca estas señales en las consultas que tu aplicación ejecuta con frecuencia:

• Columnas en WHERE que filtran a pocas filas, como un email, un número de documento o un identificador externo.
• Columnas usadas en JOIN, en especial las claves foráneas. Ojo: PostgreSQL no crea índices automáticamente en las claves foráneas; MySQL con InnoDB sí lo hace. Si usas PostgreSQL y haces uniones o borrados en cascada por esas columnas, créalos tú.
• Columnas en ORDER BY combinadas con LIMIT, como "los 20 pedidos más recientes".
• Restricciones de unicidad. Una columna UNIQUE o una clave primaria ya tienen su índice, porque el motor lo necesita para verificar duplicados. No crees otro igual.

¿Cuándo un índice no ayuda o incluso estorba?

Tablas pequeñas. Si la tabla cabe en unas pocas páginas, leerla entera es más rápido que pasar por el índice. El planificador lo sabe y lo ignorará.

Columnas con poca selectividad. Un índice sobre una columna activo donde el 95 % de las filas vale true no sirve para buscar las activas: el motor tendría que visitar casi toda la tabla igual. Puede servir para buscar el 5 % restante, y para eso existe una opción mejor que verás más abajo.

Tablas con escritura intensa. Un registro de eventos que recibe miles de inserciones por segundo y casi nunca se consulta paga el costo de cada índice en cada escritura sin obtener beneficio.

Índices duplicados o solapados. Si ya tienes un índice sobre (cliente_id, creado_en), otro solo sobre cliente_id casi siempre sobra, por la regla del prefijo que se explica a continuación.

¿Importa el orden en un índice de varias columnas?

Muchísimo. Un índice compuesto sobre (cliente_id, creado_en) ordena primero por cliente y, dentro de cada cliente, por fecha. Como en una guía telefónica ordenada por apellido y luego por nombre, sirve para buscar por apellido, o por apellido y nombre, pero no para buscar solo por nombre.

La regla práctica: pon primero las columnas que se comparan por igualdad y después las que se usan en rangos u ordenamientos.

¿Por qué mi índice existe y la consulta no lo usa?

Estas son las causas más comunes, cada una con su solución:

Aplicas una función a la columna. WHERE lower(email) = 'ana@example.com' no puede usar un índice sobre email, porque el índice guarda los valores originales. Solución: un índice sobre la expresión.

CREATE INDEX idx_clientes_email_lower ON clientes (lower(email));

El comodín está al inicio. LIKE '%gomez' no puede aprovechar un árbol B, porque el orden alfabético empieza por la primera letra. Para búsquedas dentro del texto, PostgreSQL ofrece la extensión pg_trgm con índices GIN, o la búsqueda de texto completo.

Tipos que no coinciden. Comparar una columna de texto con un número obliga a convertir valores y puede anular el índice. Compara siempre con el mismo tipo.

Estadísticas desactualizadas. El planificador decide según estadísticas sobre la distribución de los datos. Tras una carga masiva, ejecuta ANALYZE nombre_tabla para actualizarlas.

Índices especializados que vale la pena conocer

Índices parciales

Indexan solo las filas que cumplen una condición. Son perfectos para el caso de la columna con poca selectividad:

CREATE INDEX idx_pedidos_pendientes ON pedidos (creado_en)
WHERE estado = 'pendiente';

Si solo el 2 % de los pedidos está pendiente, este índice es pequeño, rápido de mantener y responde justo a la consulta "pedidos pendientes más antiguos".

Índices que cubren la consulta

Con INCLUDE puedes agregar columnas que no forman parte de la búsqueda pero que la consulta necesita devolver. Así el motor puede responder solo con el índice, sin visitar la tabla:

CREATE INDEX idx_pedidos_cliente_total ON pedidos (cliente_id) INCLUDE (total);

Otros tipos además del árbol B

¿Cómo creo índices en producción sin bloquear la tabla?

Un CREATE INDEX normal impide escribir en la tabla mientras se construye. En una tabla grande eso puede durar minutos. PostgreSQL ofrece una alternativa:

CREATE INDEX CONCURRENTLY idx_pedidos_estado ON pedidos (estado);

Tarda más y no puede ejecutarse dentro de una transacción, pero permite que la aplicación siga escribiendo. Si falla a mitad de camino, deja un índice marcado como inválido que debes eliminar y volver a crear.

Para encontrar índices que nunca se usan y solo cuestan espacio y escrituras, consulta la vista de estadísticas:

SELECT relname AS tabla, indexrelname AS indice, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Un idx_scan en cero durante semanas es una buena pista, aunque antes de borrar confirma que el índice no respalda una restricción de unicidad y que no lo usa algún proceso mensual.

Lista de verificación antes de crear un índice

1. Identifica la consulta lenta concreta, con sus valores reales.
2. Ejecuta EXPLAIN ANALYZE y confirma que hay un recorrido secuencial que descarta muchas filas.
3. Revisa si ya existe un índice que la cubra o que puedas ampliar.
4. Diseña el índice pensando en el orden: igualdades primero, rangos después.
5. Considera si un índice parcial o de expresión resuelve mejor el caso.
6. Créalo con CONCURRENTLY si la tabla está en producción.
7. Repite EXPLAIN ANALYZE y compara.
8. Revisa en unas semanas que el índice se esté usando.

Si quieres conocer otros artículos parecidos a Índices en bases de datos: cómo funcionan y cuándo crearlos puedes visitar la categoría Programacion.