Un SELECT lento que antes se completaba en 20 milisegundos de repente empieza a quedarse colgado durante 12 segundos. La base de datos pasó de 50 mil filas a 5 millones, y cada consulta se convirtió en una lotería. ¿Le suena familiar?

Los índices son algo de lo que todo el mundo ha oído hablar pero pocos configuran de forma deliberada. Usted añadió un par, las cosas parecieron ir más rápido y siguió adelante. Luego, seis meses después, INSERT es más lento que SELECT sin índice, porque cada inserción reconstruye cinco árboles B innecesarios.

Analicemos cómo funcionan realmente los índices, qué tipos existen y, lo más importante, qué reglas seguir al construirlos para no provocar el caos en producción.

💡 Resumen rápido:

• Comprenda la mecánica de los índices de árbol B, hash y compuestos, porque elegir el tipo correcto es imposible sin este conocimiento
• Domine 7 prácticas clave: desde indexar claves foráneas hasta eliminar índices no utilizados
• Aprenda a leer EXPLAIN y a distinguir un índice útil de uno inútil

Qué es un índice de base de datos

Un índice de base de datos es una estructura independiente que almacena valores ordenados de una o más columnas de la tabla junto con punteros a las filas. Cuando usted ejecuta SELECT ... WHERE category_id = 42, el servidor sin índice recorre la tabla fila por fila (recorrido secuencial completo). Con un índice encuentra los registros necesarios en O(log n), como buscar un nombre en una guía telefónica.

Un índice se crea con CREATE INDEX:

1
-- Regular index
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Unique index
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Pero el precio de las lecturas más rápidas son las escrituras más lentas. Cada INSERT, UPDATE y DELETE debe actualizar no solo la tabla, sino todos los índices asociados. Tres índices en una tabla con un millón de filas y las inserciones masivas se ralentizan en un orden de magnitud. El equilibrio entre velocidad de lectura y velocidad de escritura es la cuestión central del diseño de índices.

Un desglose detallado del tema por Hussein Nasser: 497 mil suscriptores, un enfoque de ingeniería sin relleno. Usando PostgreSQL como ejemplo, muestra la mecánica interna de los índices, por qué un CREATE INDEX acelera una consulta 100 veces mientras que otro no hace nada.

Tipos de índices: cuándo usar cada uno

La elección del tipo de índice determina la eficiencia con la que la base de datos procesa su consulta. Los distintos SGBD los implementan de manera diferente, pero los principios son universales.

Árbol B (árbol balanceado)

El tipo predeterminado en la mayoría de los SGBD relacionales. PostgreSQL, MySQL, Oracle y SQL Server usan el árbol B como índice por defecto. Almacena las claves en orden, admite operaciones de comparación, rangos (BETWEEN), búsqueda por prefijo (LIKE 'prefix%') y ordenación. Es la mejor opción para la gran mayoría de los casos.

Índice hash

Funciona solo para comparaciones de igualdad exacta =. Ultrarrápido en consultas puntuales, pero inútil para rangos y ordenación. En PostgreSQL, los índices hash son aptos para producción desde la versión 10; en MySQL no están disponibles en InnoDB (solo en MEMORY).

Índice agrupado (clustered)

Define el orden físico de las filas en disco. En MySQL InnoDB, la clave primaria siempre es agrupada: las filas se almacenan en el orden de PRIMARY KEY. En SQL Server, hay un índice agrupado por tabla. Elegir la clave agrupada correcta (monótonamente creciente: BIGSERIAL, AUTO_INCREMENT o UNIQUEIDENTIFIER con NEWSEQUENTIALID()) proporciona ganancias en consultas de rango y evita la fragmentación de páginas.

Índice compuesto

Un índice sobre dos o más columnas. Es crítico para consultas que filtran y ordenan por múltiples columnas:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

El orden de las columnas importa: coloque primero la columna con mayor selectividad usada en WHERE. Principio del prefijo más a la izquierda: un índice (A, B, C) funciona para WHERE A y WHERE A AND B, pero no para WHERE B AND C.

Índice cubriente (covering)

Contiene todas las columnas que la consulta necesita, tanto para filtrar como para la salida. La base de datos obtiene todo del índice sin tocar la tabla. En PostgreSQL esto se hace con columnas INCLUDE; en MySQL InnoDB, la cobertura implícita ocurre a través del índice agrupado.

7 Prácticas clave de indexación

1. Indexar las columnas usadas en WHERE

La primera regla: toda columna usada regularmente en WHERE, JOIN ... ON y HAVING debe estar indexada. Estas son las operaciones que más se benefician de los índices.

Antes de crear un índice, verifique la selectividad de la columna. Si la columna status solo tiene tres valores ('new', 'processing', 'done') y hay 2 millones de filas, un índice sobre status es casi inútil: el planificador elegirá un recorrido secuencial completo como la opción más barata. Un índice tiene sentido cuando el número de valores únicos es lo suficientemente grande en relación con el tamaño de la tabla.

2. Indexar las columnas usadas para ordenación

ORDER BY sin índice implica filesort (MySQL) u ordenación explícita (PostgreSQL): el servidor recoge todas las filas y las ordena en memoria (o en disco si work_mem/sort_buffer_size es demasiado pequeño). Un índice sobre las mismas columnas que ORDER BY sale gratis, ya que los datos ya están ordenados en el árbol B.

1
-- No index on created_at: filesort on millions of rows
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Index solves the problem
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Indexar las columnas usadas en GROUP BY y agregaciones

Agrupar sin índice requiere un recorrido completo y construir una tabla hash. Un índice sobre las columnas de GROUP BY convierte la operación en una agregación por flujo: las filas ya están agrupadas en orden de clave.

4. Indexar todas las claves foráneas

Una clave foránea no indexada es una bomba de tiempo. DELETE FROM users WHERE id = 5 con una FOREIGN KEY (user_id) REFERENCES users(id) en la tabla orders pero sin índice en user_id significa un recorrido secuencial completo de orders por cada eliminación. Todos los SGBD populares requieren un índice en la clave foránea o lo crean implícitamente (MySQL InnoDB lo hace automáticamente, PostgreSQL no).

5. Indexar columnas únicas y claves primarias

La clave primaria se indexa automáticamente (a menudo como índice agrupado). Un UNIQUE INDEX explícito protege contra duplicados y también acelera las búsquedas. Cualquier columna con una restricción de unicidad de negocio (por ejemplo email, slug o external_id) debe tener un índice único, tanto por integridad como por rendimiento.

6. Usar el índice agrupado de forma deliberada

Para tablas grandes (decenas de millones de filas), la clave agrupada correcta es crítica. Una buena opción es un valor monótonamente creciente: AUTO_INCREMENT, BIGSERIAL o UUID v7. Un UUID aleatorio como clave agrupada causa fragmentación de páginas: cada inserción cae en un punto aleatorio del árbol B, dividiendo páginas llenas en dos medio llenas.

7. Eliminar los índices no utilizados

Un índice que ninguna consulta usa es una pérdida pura. Ralentiza las escrituras, ocupa espacio en disco y memoria del buffer pool, y confunde al planificador de consultas. En PostgreSQL, la tabla del sistema pg_stat_user_indexes proporciona una lista de índices no utilizados:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

En MySQL, hay información similar disponible en sys.schema_unused_indexes (a partir de la versión 5.7). Programe una auditoría mensual y elimine los índices que no se hayan usado ni una sola vez durante el período del informe.

Cómo verificar que sus índices están funcionando

Una vez que haya creado un índice, verifique que realmente se esté utilizando. El comando EXPLAIN (o EXPLAIN ANALYZE) muestra el plan de ejecución de la consulta y el uso real del índice:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

En la salida, busque Index Scan o Index Only Scan (PostgreSQL) / Using index (MySQL). Si ve Seq Scan (PostgreSQL) o Using where; Using filesort (MySQL), el índice no se está utilizando. Posibles razones: baja selectividad, tipo de índice incorrecto, orden de columnas no coincidente en un índice compuesto o estadísticas desactualizadas (ANALYZE table_name;).

Monitoree las métricas regularmente: pg_stat_user_indexes.idx_scan en PostgreSQL, sys.schema_index_statistics en MySQL. Un índice con cero escaneos durante un mes es candidato a eliminación.

⁉️🤔 Preguntas frecuentes

¿Cuántos índices debe tener una tabla?

Idealmente, de 2 a 6 índices por tabla de uso activo. Menos de dos casi con seguridad significa que algunas consultas son subóptimas. Más de seis, y debería verificar cuidadosamente si todos son realmente necesarios: cada índice adicional ralentiza las escrituras. Para tablas de consulta (pocas escrituras, lecturas frecuentes) se justifican más índices. Para tablas operativas de alto tráfico (mucho INSERT/UPDATE) mantenga el número al mínimo.

¿En qué es mejor un índice compuesto que varios índices de una sola columna?

Un índice compuesto (A, B) es UNA estructura. El servidor lo recorre una vez. Tres índices separados (A), (B) y (C) para WHERE A=1 AND B=2 obligan al servidor a elegir un índice (y filtrar el resto) o a realizar un escaneo de índice de mapa de bits (fusionando mapas de bits). Un índice compuesto es casi siempre más eficiente, siempre que el orden de las columnas coincida con sus consultas.

¿Cuándo perjudica un índice en lugar de ayudar?

Tres escenarios típicos. Primero: la tabla es pequeña (hasta unos pocos miles de filas), y un recorrido secuencial completo es más rápido que leer el índice más buscar las filas. Segundo: un índice sobre una columna con baja selectividad (is_deleted, status con tres valores). Tercero: inserciones masivas durante ETL/importación, donde los índices se reconstruyen en cada lote. Elimínelos antes de cargar y recréelos después.

¿Se deben indexar las columnas usadas en JOIN?

Absolutamente. Cada JOIN sin índice en la columna de unión de la tabla externa es un bucle anidado con recorrido completo. Para LEFT JOIN orders ON users.id = orders.user_id, un índice en orders.user_id convierte el bucle anidado en una búsqueda por índice. Indexe siempre las columnas por las que une.

¿Árbol B o hash: cuál elegir para búsquedas exactas?

Para =, hash es más rápido: una búsqueda hash toma tiempo constante, mientras que el árbol B recorre el árbol en un número logarítmico de pasos. Pero hash no admite rangos, ordenación ni UNIQUE. En la práctica, el árbol B cubre la gran mayoría de los escenarios; hash es una herramienta de nicho para búsquedas puntuales por clave en sistemas de alta carga (sesiones, cachés). En PostgreSQL, los índices hash son aptos para producción desde la versión 10 y ocupan menos espacio que el árbol B.

¿Debería indexar "por si acaso"?

No. Cada índice es una compensación. Acelera las lecturas a costa de escrituras más lentas y espacio adicional en disco. No indexe "por si acaso"; indexe para consultas específicas que realmente se ejecutan en su aplicación. Perfile las consultas lentas (pg_stat_statements, slow_query_log), añada índices de forma quirúrgica y verifique EXPLAIN antes y después.

La conclusión principal es simple: los índices son una herramienta, no un fin en sí mismos. Un índice compuesto bien diseñado puede reemplazar tres índices de una sola columna y ahorrar gigabytes de espacio en disco. Y un índice no utilizado en una tabla con muchas escrituras puede ralentizar toda la aplicación.

Si quiere profundizar, comience con la guía oficial de diseño de índices de SQL Server y la documentación de PostgreSQL sobre tipos de índices. Y si se encuentra con una consulta que los índices no pueden arreglar, el problema puede estar en el propio modelo de datos.