Guía definitiva: Índices y rendimiento en SQL para desarrolladores

Los índices son la herramienta más poderosa —y a la vez la más mal entendida— para optimizar consultas SQL. Esta guía te ofrece reglas prácticas, ejemplos con código y cómo diagnosticar problemas reales usando EXPLAIN/ANALYZE, estadísticas y mantenimiento.

1. ¿Por qué importan los índices?

Un índice reduce el trabajo que debe hacer el motor de base de datos para localizar filas. Sin embargo, los índices tienen coste: ocupan espacio, ralentizan INSERT/UPDATE/DELETE y requieren mantenimiento. La meta es maximizar beneficio en lecturas críticas sin penalizar excesivamente las escrituras.

2. Tipos de índices y cuándo usarlos

• B-tree (predeterminado): ideal para igualdad y rango (<, <=, >, BETWEEN). Útil para ORDER BY y GROUP BY en columnas indexadas.
• Hash: igualdad pura en algunos motores; en PostgreSQL son raros en producción.
• BRIN: para tablas gigantes con correlación física (por ejemplo, timestamps append-only).
• GiST / GIN: índices para tipos geométricos, arrays, JSONB, full-text search; GIN para consultas de contención/elemento en arrays/JSONB.
• Function/Expression: indexa una expresión (ej. LOWER(col)). Muy útil para búsquedas case-insensitive.
• Partial: cubre solo filas que cumplen una condición; reduce tamaño y acelera consultas sobre ese subconjunto.

3. Ejemplos prácticos (PostgreSQL y MySQL)

Creemos una tabla de ejemplo y comparemos planes.

-- PostgreSQL: crear tabla y poblar (simulado)
CREATE TABLE orders (
  id serial PRIMARY KEY,
  user_id int NOT NULL,
  status text NOT NULL,
  amount numeric(10,2),
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Insertar muchos registros (script externo o COPY)

Consulta común:

SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Sin índice, PostgreSQL hará un seq scan. Mejoras:

-- Índice compuesto (orden de columnas importa)
CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);

-- Alternativa: índice covering en PostgreSQL (index-only scan) si solo necesitas columnas del índice
CREATE INDEX idx_orders_covering ON orders (user_id, status, created_at DESC) INCLUDE (amount);

MySQL (InnoDB) equivalente:

CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);

4. EXPLAIN y EXPLAIN ANALYZE: cómo leer planes

Siempre verifica con datos reales. EXPLAIN da plan estimado; EXPLAIN ANALYZE ejecuta y mide realmente.

-- PostgreSQL ejemplo
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

-- Salida (simplificada):
-- Limit  (cost=0.29..10.00 rows=20) (actual time=0.10..0.50 rows=20 loops=1)
--   -> Index Scan using idx_orders_user_status_created on orders  (cost=0.29..1000.00 rows=2000) (actual time=0.10..0.45 rows=20 loops=1)

Interpretación rápida: si ves Seq Scan y esperas pocos rows, añade o ajusta índice. Si ves Index Scan (o Index Only Scan), estás bien.

5. Reglas prácticas y errores comunes

• No indexar todo: cada índice penaliza escrituras.
• Orden de columnas en índices compuestos importa: coloca columnas con mayor cardinalidad y que aparezcan en WHERE al principio.
• Evita funciones implícitas y tipos incompatibles (casting) en columnas indexadas; preprocesa o usa expression indexes.
• Ojo con LIKE '%abc': no usa índice B-tree. Usa trigram/Gin si necesitas búsquedas con wildcards.
• Evita selects SELECT * cuando buscas pocas columnas: permite index-only scans.
• Muchos índices pequeños vs uno grande: evalúa consultas reales y las rutas más críticas.

6. Índices especiales y ejemplos útiles

-- Partial index (Postgres): útil cuando una condición se repite mucho
CREATE INDEX idx_orders_paid_recent ON orders (user_id, created_at DESC)
WHERE status = 'paid';

-- Expression index (case-insensitive search)
CREATE INDEX idx_orders_lower_status ON orders (lower(status));
-- Consulta: WHERE lower(status) = 'paid'

-- GIN index para JSONB
CREATE INDEX idx_orders_meta_gin ON orders USING gin (metadata jsonb_path_ops);

7. Mantenimiento: estadísticas y limpieza

Herramientas clave en PostgreSQL:

• VACUUM (FULL) y VACUUM regular para limpiar tuples obsoletas; VACUUM FULL reescribe la tabla (bloqueante).
• ANALYZE actualiza estadísticas; imprescindible después de cargas masivas.
• REINDEX cuando un índice está corrupto o muy fragmentado.
• pg_stat_user_indexes y pg_stat_all_tables para ver uso y hit ratios.

-- Ver uso de índices en Postgres
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

En MySQL: ANALYZE TABLE, OPTIMIZE TABLE y revisar INFORMATION_SCHEMA.STATISTICS.

8. Cómo medir coste/beneficio

1. Identifica consultas críticas (latencia y frecuencia).
2. Ejecuta EXPLAIN (ANALYZE, BUFFERS) antes/después.
3. Mide impacto en escrituras: TPS y latencia de INSERT/UPDATE.
4. Monitorea espacio en disco y crecimiento de índices.

9. Diagnóstico de problemas comunes

Casos y soluciones rápidas:

• Consulta lenta tras insert masivo: ejecutar ANALYZE para actualizar estadísticas.
• Índice no usado: revisar orden de columnas, operadores, tipos, o forzar con hints para pruebas.
• Gran cantidad de índices sin uso: eliminar índices con idx_scan = 0 y medir impacto.
• Index bloat: usar REINDEX o VACUUM FULL si necesario.

10. Buenas prácticas resumidas

• Diseña índices basados en consultas reales (pistas: logs, APM).
• Prioriza índices para queries de lectura crítica y reportes.
• Usa índices compuestos con intención: WHERE + ORDER BY + LIMIT.
• Monitorea y elimina índices no usados.
• Automatiza mantenimiento: VACUUM/ANALYZE/OPTIMIZE en horarios de baja carga.

Recursos y siguientes lecturas

• Documentación oficial de PostgreSQL: planificación de consultas e índices.
• Documentación de MySQL sobre índices y optimización.
• Herramientas: pg_stat_statements, pg_repack, EXPLAIN visualizers.

Consejo avanzado: en entornos de staging, crea índices hipotéticos (o usa herramientas como hypopg) para medir impacto sin materializar índices en producción. También prueba SET LOCAL enable_seqscan = off para forzar planner a usar índice y comparar tiempos —solo para pruebas controladas.

Siguiente paso: identifica las 5 consultas más costosas de tu sistema, ejecuta EXPLAIN ANALYZE con y sin índice propuesto, y decide eliminando o creando índices basados en datos reales.

Advertencia: no automatices la creación de índices sin revisar impacto en escrituras y espacio. El balance entre lectura y escritura es contextual; mide siempre antes de aplicar en producción.