Guía completa de SQL para desarrolladores: rendimiento, consultas avanzadas y buenas prácticas

Esta guía reúne técnicas prácticas y explicaciones para escribir consultas SQL eficientes, entender planes de ejecución, diseñar índices efectivos y evitar errores comunes. Ejemplos orientados a PostgreSQL (donde haya diferencias clave se indicará), pero los conceptos valen para la mayoría de RDBMS.

Índice

• Fundamentos rápidos (SELECT, JOINs, filtros)
• CTE vs subconsultas, y cuándo usar cada una
• Funciones ventana (window functions) con ejemplos prácticos
• Índices: tipos, cuándo y cómo crearlos
• Leer y comprender EXPLAIN/EXPLAIN ANALYZE
• Transacciones, aislamiento y bloqueos
• Antipatrones de rendimiento y cómo resolverlos
• Checklist de optimización y herramientas
• Ejemplo completo: optimización paso a paso

1. Fundamentos rápidos

Buena práctica: escribe primero la consulta correcta y legible; luego perfílala. Evita SELECT * en producción.

-- Evita usar SELECT *; enumera columnas para claridad y performance
SELECT id, nombre, created_at
FROM usuarios
WHERE activo = true
ORDER BY created_at DESC
LIMIT 100;

JOINs: usa el JOIN adecuado (INNER, LEFT, RIGHT, FULL) según semántica; filtra con condiciones en ON cuando correspondan al emparejamiento y en WHERE para condiciones posteriores al JOIN.

-- Buen patrón: condiciones de emparejamiento en ON
SELECT o.id, u.email
FROM ordenes o
JOIN usuarios u ON u.id = o.usuario_id AND u.eliminado = false
WHERE o.estado = 'pagada';

2. CTE vs Subconsulta

CTEs (WITH) son ideales para legibilidad y para materializar resultados lógicamente; sin embargo, en algunos motores (o versiones antiguas) pueden forzar materialización y penalizar performance. Subconsultas inline a veces permiten mejores planes de ejecución.

-- CTE legible
WITH totales AS (
  SELECT usuario_id, SUM(monto) total
  FROM pagos
  GROUP BY usuario_id
)
SELECT u.id, u.nombre, t.total
FROM usuarios u
LEFT JOIN totales t ON t.usuario_id = u.id;

-- Si el CTE se materializa y hay millones de filas, considera refactorizar a subconsulta o temp table

3. Funciones ventana

Potentes para ranking, agregados por partición, cálculos acumulativos.

-- Ranking de ventas por usuario
SELECT usuario_id, monto, 
  RANK() OVER (PARTITION BY usuario_id ORDER BY monto DESC) as rank_por_usuario
FROM ventas;

-- Suma acumulada por fecha
SELECT fecha, total_diario,
  SUM(total_diario) OVER (ORDER BY fecha) as suma_acumulada
FROM resumen_diario_ventas;

4. Índices: tipos y estrategia

Tipos comunes: B-tree (por defecto), Hash, GIN/GIST (para búsquedas textuales o arrays), BRIN (para datos muy ordenados como timestamps). Crear índices sin estrategia puede degradar INSERT/UPDATE. Balancea lectura vs escritura.

• Índice en columnas usadas en WHERE, JOIN y ORDER BY.
• Índice compuesto: orden importa; empezar por la columna más discriminante.
• Evita índices en columnas con baja cardinalidad (booleanos), salvo casos específicos.

-- Índice compuesto para filtro y orden
CREATE INDEX idx_ordenes_usuario_estado_fecha
ON ordenes (usuario_id, estado, created_at DESC);

-- GIN para búsqueda de texto con to_tsvector (Postgres)
CREATE INDEX idx_productos_search ON productos USING gin(to_tsvector('spanish', nombre || ' ' || descripcion));

5. Leer EXPLAIN y EXPLAIN ANALYZE

EXPLAIN muestra plan; EXPLAIN ANALYZE ejecuta la consulta y muestra tiempos reales. Observa:

• Seq Scan vs Index Scan
• Costes estimados vs reales (muy diferentes indica estadísticas desactualizadas)
• Filtrado selectivo: filas estimadas vs reales
• Operaciones de sort/aggregate que pueden requerir temp files

-- Ejemplo en Postgres
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.id
FROM usuarios u
JOIN ordenes o ON o.usuario_id = u.id
WHERE o.created_at > now() - interval '30 days';

Si ves SEQ SCAN en tablas grandes donde esperas index scan, revisa si el índice existe o si las estadísticas necesitan VACUUM/ANALYZE.

6. Transacciones, aislamiento y bloqueos

Entender el nivel de aislamiento es vital para consistencia y concurrencia. Niveles comunes: Read Uncommitted, Read Committed (por defecto en Postgres), Repeatable Read, Serializable.

• Usa transacciones cortas: abrir transacción largo tiempo aumenta contención y tamaño de WAL/undo.
• Evita SELECT dentro de transacción larga si no se necesita consistencia con writes.
• Para operaciones batch, realiza commits periódicos.

BEGIN;
UPDATE cuentas SET balance = balance - 100 WHERE id = 1;
UPDATE cuentas SET balance = balance + 100 WHERE id = 2;
COMMIT;

Evita deadlocks ordenando acceso a tablas/filas de forma consistente entre transacciones.

7. Antipatrones de rendimiento

• SELECT * sobre tablas grandes
• Realizar N+1 queries desde la aplicación en lugar de JOINs o JOIN+aggregation
• Funciones en columnas del WHERE (e.g., WHERE LOWER(name) = 'x') impiden uso de índices — usa índices funcionales
• Indices excesivos que penalizan escrituras
• Ordenar sin LIMIT en tablas grandes; ORDER BY puede forzar sort costoso

-- Antipatrón: función en columna
SELECT * FROM usuarios WHERE LOWER(email) = 'a@b.com';

-- Mejor: índice funcional
CREATE INDEX idx_usuario_email_lower ON usuarios (LOWER(email));

8. Checklist de optimización y herramientas

• ¿La consulta devuelve columnas innecesarias?
• ¿Hay índices adecuados para WHERE, JOIN y ORDER BY?
• ¿Las estadísticas están actualizadas? (ANALYZE / autovacuum)
• Ejecuta EXPLAIN ANALYZE en staging con datos representativos
• Monitoriza con pg_stat_statements, slow query log, o equivalente

Herramientas útiles: pg_stat_statements, pgbadger, autovacuum logs, EXPLAIN visualizers, y APMs (Datadog, NewRelic).

9. Ejemplo completo: optimización paso a paso

Escenario: consulta lenta que muestra usuarios con su último pedido y total de pedidos en 30 días.

-- Versión inicial (lentísima en tablas grandes)
SELECT u.id, u.nombre, o_ultimo.created_at as ultimo_pedido, COUNT(o.id) as total_30d
FROM usuarios u
LEFT JOIN ordenes o ON o.usuario_id = u.id
LEFT JOIN ordenes o_ultimo ON o_ultimo.usuario_id = u.id
  AND o_ultimo.created_at = (
    SELECT MAX(created_at) FROM ordenes WHERE usuario_id = u.id
  )
WHERE o.created_at > now() - interval '30 days'
GROUP BY u.id, u.nombre, o_ultimo.created_at;

Problemas detectados: subconsulta por fila (MAX) y JOIN redundantes. Pasos:

-- 1) Usar CTE/ventana para calcular último pedido y total por usuario (más eficiente)
WITH ordenes_30d AS (
  SELECT usuario_id, created_at
  FROM ordenes
  WHERE created_at > now() - interval '30 days'
),
resumen AS (
  SELECT usuario_id,
    COUNT(*) AS total_30d,
    MAX(created_at) AS ultimo_pedido
  FROM ordenes_30d
  GROUP BY usuario_id
)
SELECT u.id, u.nombre, r.ultimo_pedido, COALESCE(r.total_30d, 0) as total_30d
FROM usuarios u
LEFT JOIN resumen r ON r.usuario_id = u.id;

-- 2) Asegúrate de tener índices en ordenes(created_at) y ordenes(usuario_id, created_at)
CREATE INDEX IF NOT EXISTS idx_ordenes_created_at ON ordenes (created_at);
CREATE INDEX IF NOT EXISTS idx_ordenes_usuario_created_at ON ordenes (usuario_id, created_at DESC);

Después de estos cambios, prueba con EXPLAIN ANALYZE. Si el CTE se materializa y cuesta demasiado, considera una tabla temporal o materialized view actualizada periódicamente.

Consejos prácticos y errores comunes

• Mide antes de optimizar: el problema más frecuente es optimizar lo equivocado.
• Evita suposiciones basadas en datos de desarrollo; siempre prueba con dataset representativo.
• Documenta los motivos de cada índice (comentarios en la DB o en repositorio) para evitar índices huérfanos.
• Si cambia el patrón de consultas, revisa índices: lo que fue útil puede volverse un peso.

Advertencia: optimizar sin pruebas puede empeorar la latencia o la carga. Si ves discrepancias entre costos estimados y reales, actualiza estadísticas y revisa histogramas de cardinalidad.

Paso siguiente recomendado: habilita y consulta las métricas de consultas lentas (slow query log o pg_stat_statements), agrupa por fingerprint de consulta y prioriza optimizaciones por impacto (tiempo total consumido por todas las ejecuciones).

Pista avanzada: cuando trabajes con ORM, inspecciona SQL generado; a menudo el gran problema está en múltiples queries desde el ORM (N+1). Considera materialized views o CQRS para cargas analíticas intensas.