Guía completa de índices en SQL para rendimiento y escalado

Los índices son la palanca más poderosa para acelerar consultas en bases de datos SQL, pero mal usados pueden penalizar inserciones/actualizaciones, ocupar espacio y confundir el optimizador. Esta guía explica tipos, diseño, diagnóstico y mantenimiento con ejemplos prácticos en PostgreSQL y MySQL. Ve al grano: crea índices pensados, mide y itera.

1. ¿Por qué y cuándo usar un índice?

• Mejoran búsquedas por clave, ordenación y agrupamiento cuando reducen el número de filas que el motor debe leer.
• No usar índice cuando la consulta devuelve gran parte de la tabla (selectivity baja).
• Coste: cada índice añade coste a INSERT/UPDATE/DELETE y consume espacio.

2. Tipos de índices (resumen práctico)

• B-tree (por defecto): excelente para igualdad y rangos. Usado para ORDER BY y GROUP BY.
• Hash: igualdad pura; limitado en algunos motores y no soporta ordenación.
• GIN/GiST (Postgres): índices para arrays, jsonb, texto (full-text) y búsquedas geométricas.
• BRIN (Postgres): índices extremadamente pequeños para columnas con correlación física (timestamps, ids crecientes).

3. Diseño de índices: reglas prácticas

1. Índice para las columnas usadas en WHERE: prioriza columnas con alta selectividad. Si la columna tiene pocos valores distintos (ej. booleano), el índice probablemente no ayude.
2. Orden en índices compuestos: el orden importa. Un índice (a, b) puede usarse para condiciones sobre a o (a y b), pero no para b solo.
3. Evita funciones no indexables: WHERE LOWER(name) = 'x' no usará index estándar; usa expression index: CREATE INDEX ON t (LOWER(name)).
4. Índices covering: incluye columnas usadas en SELECT para evitar acceder a la fila (index-only scan). Ej: CREATE INDEX idx ON t(col1, col2); SELECT col1,col2 FROM t WHERE col1=...;
5. Índices parciales: crean índice solo para filas que cumplen una condición (Postgres, MySQL 8.0 soporta índices invisibles/funcionales). Reducen tamaño y mantienen selectividad.

4. Comandos y ejemplos prácticos

Ejemplo: tabla de ventas

CREATE TABLE sales (
  id serial PRIMARY KEY,
  order_id bigint NOT NULL,
  customer_id int NOT NULL,
  created_at timestamptz NOT NULL,
  status text,
  total numeric
);

Consultas típicas:

SELECT * FROM sales WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

Índice recomendado:

-- PostgreSQL
CREATE INDEX idx_sales_customer_created ON sales (customer_id, created_at DESC);

-- MySQL (InnoDB): la ordenación DESC puede ser optimizada igualmente
CREATE INDEX idx_sales_customer_created ON sales (customer_id, created_at);

Por qué: el índice es selectivo por customer_id y ya contiene created_at para ordenar sin leer la tabla entera.

Expression index

-- Postgres: search case-insensitive
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
-- Usa: WHERE LOWER(email) = 'foo@example.com'

Partial index (Postgres)

CREATE INDEX idx_active_users ON users (last_login) WHERE active = true;

Útil si solo consultas usuarios activos y la proporción de activos es pequeña.

5. Diagnóstico: cómo saber si un índice se usa

• Postgres: EXPLAIN y EXPLAIN ANALYZE. Compara tiempos con y sin índice.
• MySQL: EXPLAIN SELECT ...; SHOW INDEX FROM table; SHOW STATUS;
• Busca index-only scans en Postgres: EXPLAIN muestra "Index Only Scan".

-- Postgres
EXPLAIN ANALYZE SELECT * FROM sales WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

-- MySQL
EXPLAIN SELECT * FROM sales WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

Interpretación rápida: si el plan muestra Seq Scan (sequential scan) en lugar de Index Scan, tu índice no está siendo usado — revisa estadísticas, tipos y funciones en la condición.

6. Problemas comunes y cómo solucionarlos

• Sobreindexado: demasiados índices ralentizan escrituras. Revisa índices duplicados o que no se usan (pg_stat_user_indexes para Postgres).
• Bloat: índices fragmentados ocupan espacio (Postgres: REINDEX, VACUUM FULL en casos extremos).
• Estadísticas desactualizadas: el optimizador toma malas decisiones. Ejecuta ANALYZE o autovacuum tuning.
• Tipo de dato mismatched: comparar bigint con int puede evitar el uso de índice por casts implícitos.
• Funciones y colaciones: usar funciones en columnas sin expression index pierde indexabilidad.

7. Mantenimiento y monitoreo

• Postgres: VACUUM (autovacuum), ANALYZE, REINDEX.
• MySQL (InnoDB): OPTIMIZE TABLE, innodb_stats_persistent y tools de monitoreo.
• Monitorea: uso de índices, tamaño, tiempos de consulta (pg_stat_statements, slow query log).

8. Estrategias avanzadas

• Usa índices BRIN para datos append-only con correlación física (ej. logs, series temporales) para ahorro masivo de espacio.
• Considera índices GIN para jsonb y full-text: CREATE INDEX idx ON t USING GIN (jsonb_column);
• Index-only scans: diseña consultas y esquemas para que las columnas necesarias estén en el índice.
• Evaluación costo-beneficio: mide latencia de lectura vs coste de escritura por cada índice nuevo.

9. Checklist práctico antes de crear un índice

1. ¿La condición WHERE (o JOIN/ORDER BY) se ejecuta frecuentemente?
2. ¿La columna tiene alta selectividad?
3. ¿La misma consulta se repite y representa un cuello de botella?
4. ¿Un índice parcial o expresión podría reducir tamaño y mejorar selectividad?
5. ¿Has medido con EXPLAIN ANALYZE antes y después?

10. Ejemplo completo: medición y rollback

-- 1) Medir
EXPLAIN ANALYZE SELECT * FROM sales WHERE customer_id=42 ORDER BY created_at DESC LIMIT 10;

-- 2) Crear índice
CREATE INDEX CONCURRENTLY idx_sales_customer_created ON sales (customer_id, created_at DESC);

-- 3) Volver a medir
EXPLAIN ANALYZE SELECT * FROM sales WHERE customer_id=42 ORDER BY created_at DESC LIMIT 10;

-- 4) Si el índice no ayuda o penaliza escrituras, eliminar
DROP INDEX CONCURRENTLY idx_sales_customer_created;

Nota: en Postgres usa CONCURRENTLY en producción para evitar bloqueo; en MySQL crea índices online con ALTER TABLE ... ALGORITHM=INPLACE cuando sea posible.

11. Buenas prácticas rápidas

• Prueba en un entorno de staging replicando carga real antes de aplicar en producción.
• Automatiza recolección de planes y tiempos (pg_stat_statements, slow query logs) y revisa semanalmente.
• Documenta por qué existe cada índice (comentarios en esquema o en repositorio IaC).

Consejo avanzado: crea pruebas de carga con datasets representativos y compara no solo latencia promedio sino latencia p99. Las optimizaciones que mejoran promedio pueden empeorar p99.

Advertencia: añadir índices sin medir puede convertir una mejora puntual en un problema de escalado de escrituras y almacenamiento. Siguiente paso: integra monitoreo de consultas y un pipeline de pruebas A/B para cambios de índices en producción.