Cómo optimizar consultas SQL: guía práctica de rendimiento para desarrolladores

Esta guía compacta te lleva por los pasos prácticos para detectar, analizar y optimizar consultas SQL comunes. Incluye ejemplos ejecutables (PostgreSQL / MySQL), explicación de planes de ejecución y decisiones por qué se toman cada cambio.

Checklist rápida

• Reproduce el problema con datos representativos
• Captura el plan de ejecución (EXPLAIN / EXPLAIN ANALYZE)
• Identifica operaciones costosas: seq scan, nested loop sobre grandes tablas, sorts y materializaciones
• Aplica índices, reescribe consultas o introduce agregaciones/precomputaciones
• Mide antes y después con métricas de latencia y throughput

Ejemplo: esquema y consulta problemática

Usaremos un esquema típico de ecommerce: customers, orders, order_items, products.

-- sql/schema.sql
CREATE TABLE customers (id BIGSERIAL PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE products (id BIGSERIAL PRIMARY KEY, sku TEXT, price NUMERIC);
CREATE TABLE orders (id BIGSERIAL PRIMARY KEY, customer_id BIGINT REFERENCES customers(id), created_at TIMESTAMP);
CREATE TABLE order_items (id BIGSERIAL PRIMARY KEY, order_id BIGINT REFERENCES orders(id), product_id BIGINT REFERENCES products(id), qty INT);

Consulta lenta típica: obtener clientes con gasto total y filtrar por país y fecha.

-- sql/queries/bad_queries.sql
SELECT c.id, c.name, SUM(p.price * oi.qty) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE c.country = 'ES' AND o.created_at >= '2025-01-01'
GROUP BY c.id, c.name
ORDER BY total DESC
LIMIT 10;

Primer paso: obtener y leer el plan de ejecución

PostgreSQL:

EXPLAIN ANALYZE
SELECT ...;

MySQL:

EXPLAIN FORMAT=JSON
SELECT ...;

Qué buscar:

• Seq Scan / Full Table Scan en tablas grandes
• Nested Loop que multiplica filas (indicador de joins sin índices)
• Sort que consume memoria o provoca disk-spill
• Estimation vs actual: mucha diferencia sugiere estadísticas desactualizadas o cardinalities erróneas

Optimización 1: índices adecuados (la gana más común)

Para nuestra consulta, índices útiles:

CREATE INDEX idx_orders_customer_created_at ON orders(customer_id, created_at);
CREATE INDEX idx_order_items_order_product ON order_items(order_id, product_id);
CREATE INDEX idx_customers_country ON customers(country);

Por qué: el indice compuesto en orders permite filtrar por customer_id y created_at de forma eficiente para el join/where. Un índice en order_items evita escaneo completo al buscar items por order_id.

Optimización 2: cobertura y columnas incluidas

Si la base lo permite (PostgreSQL soporta INCLUDE a partir de v11):

CREATE INDEX idx_orders_customer_created_at_included ON orders(customer_id, created_at) INCLUDE (id);

Esto puede evitar fetch adicional si el plan puede resolver el join/aggregation usando solo el índice.

Optimización 3: reescritura de la consulta

Evita multiplicaciones innecesarias; agrega agregación temprana con subconsulta o CTE materializado:

-- agrupar order_items por order antes del join
WITH items_sum AS (
  SELECT order_id, SUM(qty * p.price) AS order_total
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY order_id
)
SELECT c.id, c.name, SUM(isum.order_total) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN items_sum isum ON isum.order_id = o.id
WHERE c.country = 'ES' AND o.created_at >= '2025-01-01'
GROUP BY c.id, c.name
ORDER BY total DESC
LIMIT 10;

Por qué: reduces el volumen de filas que se propagan hacia las fases de join/aggregation principales, especialmente si order_items es grande.

Optimización 4: selecciona sólo columnas necesarias (no SELECT *)

Traer columnas extras obliga a lecturas adicionales y hace los índices menos efectivos. Selecciona explícitamente sólo lo que necesitas.

Optimización 5: paginación eficiente (keyset pagination)

Evita OFFSET en paginación de grandes desplazamientos. Usa keyset pagination:

SELECT id, name, total
FROM leaderboard
WHERE (total, id) < ( :last_total, :last_id )
ORDER BY total DESC, id DESC
LIMIT 50;

Estadísticas, VACUUM/ANALYZE y parámetros

• PostgreSQL: RUN ANALYZE para actualizar estadísticas si cardinalities extrañas
• MySQL: ANALYZE TABLE
• Configurar work_mem / sort_buffer_size para evitar disk-spill en ordenamientos

Consideraciones avanzadas

• Partitioning: si orders y order_items crecen por fecha, particiona por rango de fecha para reducir scans
• Materialized views: si el cálculo es caro y los datos no requieren real-time, precompute totals periódicamente
• Parameter sniffing: en engines con execution-plan caching, prueba con RECOMPILE o hints si el plan se vuelve subóptimo con distintos parámetros
• Locking y concurrencia: índices mal diseñados pueden provocar hotspots en escrituras

Métrica y benchmarking

Mide latencia y throughput reales con:

• pgbench / sysbench
• Mediciones de 95/99 percentil, no solo promedio
• Pruebas A/B de antes/después en un entorno representativo

Estrategia de trabajo recomendada (pasos concretos)

1. Reproducir: ejecutar la consulta con datos reales/representativos
2. EXPLAIN ANALYZE: identificar scans caros y nested loops
3. Actualizar estadísticas
4. Agregar índice pequeño y medir (uno por uno)
5. Reescribir consulta (CTE/aggregación temprana / eliminar SELECT *)
6. Considerar particionado o materialización si es recurrente y costoso

Estructura recomendada de archivos para el trabajo

sql/
  schema.sql            -- DDL de tablas e índices
  data_gen.sql          -- scripts para poblar datos de prueba
  queries/
    bad_queries.sql
    optimized_queries.sql
  explain/
    explain_before.txt
    explain_after.txt
  bench/
    pgbench_setup.sql
    run_bench.sh

Errores comunes a evitar

• Crear índices indiscriminadamente (memoria/tiempo de mantenimiento)
• No actualizar estadísticas después de cargas masivas
• Depender de ORDER BY + OFFSET para paginación en tablas grandes
• No medir: asumir que un cambio mejora sin pruebas

Ejemplo completo: antes / después

Antes: plan muestra Seq Scan en order_items y nested loop que multiplica filas.

Acciones aplicadas: índice orders(customer_id, created_at), index order_items(order_id), reescritura con CTE agregando order_total.

Resultado esperado: reducción en tiempo de ejecución por eliminar scans completos y reducir filas intermedias; menor uso de I/O.

Recursos y comandos útiles

• EXPLAIN / EXPLAIN ANALYZE (Postgres)
• EXPLAIN FORMAT=JSON (MySQL)
• pg_stat_statements para queries más costosas
• pg_repack, pt-online-schema-change para mantenimiento sin downtime

Consejo avanzado: automatiza benchmarks de cada cambio (scripts que comparen EXPLAIN ANALYZE y latencias) y mantén versiones de índices en control de cambios para poder revertir rápidamente si un índice ayuda en lectura pero penaliza escrituras.

Advertencia: siempre prueba en staging con datos representativos; algunas optimizaciones (p. ej. índices muy selectivos) pueden comportarse distinto en producción.

Siguiente paso sugerido: ejecuta los ejemplos en tu DB local, captura EXPLAIN antes/después y guarda los outputs en explain/ para discutir con el equipo.