Cómo optimizar consultas SQL: guía práctica paso a paso

Objetivo: identificar y aplicar técnicas concretas para acelerar consultas SQL en bases de datos relacionales (PostgreSQL / MySQL / SQL Server). Incluye flujo de trabajo, ejemplos antes/después, y scripts para reproducir pruebas.

Estructura del proyecto (scripts)

sql-optimizacion/
├─ data/
│  ├─ load_data.sql        -- scripts de carga masiva
│  └─ sample_data.csv
├─ queries/
│  ├─ original.sql         -- queries problemáticas
│  └─ optimized.sql        -- versiones optimizadas
├─ tools/
│  ├─ explain_postgres.sh  -- wrapper para EXPLAIN ANALYZE
│  └─ benchmark.sh         -- scripts de benchmark (pgbench/ab-like)
└─ README.md

Por qué: separar datos, queries y herramientas facilita comparar resultados y automatizar pruebas.

Paso 0 — Preparación y métricas

• Mide latencia y recursos: tiempo total, CPU, I/O, buffers leídos/escritos.
• Usa EXPLAIN ANALYZE (Postgres), EXPLAIN FORMAT=JSON (MySQL/PG), o SHOWPLAN (SQL Server).
• Prueba con datos realistas: la cardinalidad importa mucho.

Paso 1 — Analiza el plan de ejecución

Ejemplo en PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = true AND o.created_at > now() - interval '30 days';

Qué buscar:

• Seq Scan vs Index Scan: secuencial sobre tabla grande es señal de falta de índice.
• Nested Loop con tablas grandes — cuidado, puede explotar en tiempo.
• Filtrado tardío: WHERE aplicado después de JOIN implica más trabajo.
• Estimated rows vs actual rows — mala cardinalidad sugiere estadísticas desactualizadas o predicciones erróneas.

Paso 2 — Índices: el arma más poderosa (y peligrosa)

Reglas prácticas:

• Indexa columnas usadas en WHERE, JOIN y ORDER BY.
• Prefiere índices compuestos si la consulta filtra por varias columnas en combinación: CREATE INDEX ON table(col1, col2).
• Evita índices innecesarios: cada índice penaliza INSERT/UPDATE/DELETE.
• Considera índices parciales (Postgres) para valores frecuentes: CREATE INDEX ON orders(created_at) WHERE status = 'paid'.
• Covering indexes (incluyendo columnas en el índice) evitan vuelta a tabla: en MySQL/MariaDB usa INDEX(col1, col2, col3).

Ejemplo: crear índice compuesto en Postgres

-- Antes: lenta por filtro en two columns
CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at DESC);

Por qué DESC en índice: si haces ORDER BY created_at DESC, el índice puede suministrar filas ya ordenadas.

Paso 3 — Reescribe la consulta

Patrones de reescritura con impacto:

• Evita SELECT *: trae solo columnas necesarias.
• Reemplaza IN por EXISTS si la subconsulta está correlacionada y devuelve muchas filas.
• Usa JOIN correcto: si necesitas sólo existencia, EXISTS suele ser más eficiente que JOIN + DISTINCT.
• Evita funciones en columnas filtradas: WHERE LOWER(name) = 'x' impide uso de índice; mejor usar expresión indexada o normalizar datos.

Ejemplo antes/después:

-- Malo
SELECT * FROM products WHERE LOWER(sku) = LOWER('ABC123');

-- Mejor (Postgres): crear índice de expresión
CREATE INDEX idx_products_sku_lower ON products (LOWER(sku));
SELECT * FROM products WHERE LOWER(sku) = 'abc123';

Paso 4 — JOINs: orden y tipo

Consejos:

• Filtra tablas grandes lo antes posible (push down predicates).
• Si puedes reducir una tabla antes del JOIN (subconsulta/CTE/temporal), hazlo.
• Comprende tamaños relativos: Nested Loop con pequeño << grande está bien; con tablas similares, Hash Join o Merge Join son mejores.

Ejemplo: filtrar antes de join

-- Menos eficiente: join completo luego filtra
SELECT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > now() - interval '30 days';

-- Mejor: prefiltrar orders
WITH recent_orders AS (
  SELECT user_id FROM orders WHERE created_at > now() - interval '30 days'
)
SELECT u.id, u.name
FROM users u
JOIN recent_orders r ON r.user_id = u.id;

Paso 5 — CTEs vs Tablas temporales

En Postgres, los CTEs (WITH) son por defecto materializados (hasta Postgres 12). Eso puede ser bueno o malo:

• Si el subresultado se reutiliza, materialización puede ayudar.
• Si el optimizador podría reescribir la consulta, materialización impide optimizaciones; usa subquery inline o FROM (...) si necesitas inline expansion.

Para tareas intermedias pesadas y reutilizables, considera tablas temporales con índices:

CREATE TEMP TABLE tmp_recent_orders AS
SELECT * FROM orders WHERE created_at > now() - interval '30 days';
CREATE INDEX ON tmp_recent_orders (user_id);
-- luego joins eficientes

Paso 6 — Paginación y LIMIT/OFFSET

OFFSET es caro en offsets altos: la base aún escanea N filas. Mejores alternativas:

• Keyset pagination (seek method): usar WHERE (col < last_col) ORDER BY col DESC LIMIT 20.
• Mantener cursores o estado del cliente para navegación continua.

-- Keyset pagination
SELECT id, created_at FROM events
WHERE created_at < '2026-09-01T00:00:00'
ORDER BY created_at DESC
LIMIT 50;

Paso 7 — Estadísticas, cardinalidad y parámetros

• Actualiza estadísticas: ANALYZE (Postgres), RUNSTATS (DB2), UPDATE STATISTICS (SQL Server).
• Parameter sniffing: si el plan se genera con parámetros no representativos, podrías necesitar OPTION (RECOMPILE) o plan guides (SQL Server) o use_plan_cache (MySQL).
• Monitorea discrepancia entre rows estimadas y reales; corrige con mejores estadísticas o histograms.

Paso 8 — Mantenimiento de índices y VACUUM

Si usas PostgreSQL:

-- Mantén bloat bajo
VACUUM (VERBOSE, ANALYZE);
REINDEX TABLE my_table;  -- cuando índices muy fragmentados
-- Ajustes: autovacuum tunning (scale_factor / threshold)

En MySQL, usa OPTIMIZE TABLE y monitorea fragmentación.

Paso 9 — Monitorización y profiling

• Postgres: pg_stat_statements, pg_stat_activity, pg_buffercache.
• MySQL: Performance Schema, slow_query_log.
• Repite pruebas bajo carga real para detectar contención y locks.

Paso 10 — Cache, materialización y denormalización

Si la consulta es inherentemente costosa y se ejecuta con frecuencia:

• Materialized views (Postgres) para resultados actualizados periódicamente.
• Cache en la capa de aplicación (Redis) con invalidación correcta.
• Denormalizar para lecturas intensas: columnas precalculadas o tablas de reporting.

Ejemplo práctico: mejora de una consulta real

Escenario: tabla orders (10M filas) y users (2M filas). Consulta lenta por JOIN + filtro y orden:

-- original.sql
SELECT u.id, u.email, SUM(o.amount) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.created_at > '2026-01-01'
GROUP BY u.id, u.email
ORDER BY total DESC
LIMIT 100;

Problemas detectados:

• Seq Scan en orders por falta de índice en (status, created_at, user_id).
• GROUP BY obliga a agregar sobre muchas filas.

Optimización aplicada:

-- optimized.sql
CREATE INDEX CONCURRENTLY idx_orders_status_created_user ON orders (status, created_at, user_id);

-- usar agregación con pre-agrupación (reduce trabajo del join)
WITH paid_recent AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'paid' AND created_at > '2026-01-01'
  GROUP BY user_id
)
SELECT u.id, u.email, p.total
FROM paid_recent p
JOIN users u ON u.id = p.user_id
ORDER BY p.total DESC
LIMIT 100;

Resultados esperados: el índice permite escaneo indexado por status+created_at; la agregación previa reduce cantidad de filas para el JOIN, y el ORDER BY opera sobre la agregación final (mucho menos datos).

Checklist rápido antes de desplegar cambios

• Comparar EXPLAIN ANALYZE antes y después.
• Medir latencia p99, CPU y I/O.
• Revisar impacto en escrituras por nuevos índices.
• Plan de rollback: scripts DROP INDEX con nombre conocido o revertir cambios en migraciones.

Advertencia: no indexar todo por impulso; cada índice tiene coste en escritura y espacio.

Siguiente paso recomendado: automatiza pruebas de rendimiento (benchmarking) y guarda EXPLAINs históricos. Como consejo avanzado: integra pg_stat_statements + un pequeño script que detecte consultas con alto tiempo total y alta frecuencia, y prioriza optimizarlas; si una consulta es crítica y no mejora con índices, considera reestructurar el modelo de datos o materializar resultados.