Cómo acelerar tus consultas SQL en 3 pasos (Guía práctica)

Dirigido a desarrolladores y DBAs: técnicas prácticas y comprobables para diagnosticar y acelerar consultas SQL, con ejemplos reales (PostgreSQL). Enfócate en medir, arreglar y estabilizar.

Paso 1 — Medir y diagnosticar (observabilidad)

Antes de tocar índices o reescribir consultas, mide. Sin datos, optimizar es adivinar.

• Recopila métricas: latencia por consulta, I/O, CPU, locks.
• Registra planes de ejecución: EXPLAIN (ANALYZE, BUFFERS) en PostgreSQL.
• Usa herramientas: pg_stat_statements, auto_explain, slow query log (MySQL), EXPLAIN FORMAT=JSON.

Ejemplo mínimo (Postgres):

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';

Cómo leer lo esencial del plan:

• "actual time" vs "cost" — usa actual time para rendimiento real.
• Rows vs estimated rows — grandes discrepancias indican estadísticas obsoletas o selectividad mal estimada.
• Buffers/IO — alto I/O muestra que falta un índice o la consulta escanea muchas páginas.

Paso 2 — Índices y modelado: arreglar la causa

La mayoría de las ganancias vienen de índices y de corregir cardinalidades.

Reglas prácticas de indexado

• Indexa columnas usadas en WHERE, JOIN y ORDER BY con alta selectividad.
• Prefiere índices compuestos cuando las consultas filtran por varias columnas en combinación. Orden importa: put the most selective/used column first.
• Usa índices parciales para condiciones frecuentes (p. ej. active = true).
• Considera índices de expresiones si filtras por funciones (LOWER(col), date_trunc(...)).
• Evita índices redundantes: demasiados índices ralentizan writes.

Ejemplos prácticos

Problema: el plan hace un seq scan en orders filtrando por created_at y luego join con users.

-- Crear índice compuesto para filtro y join
CREATE INDEX idx_orders_userid_createdat ON orders (user_id, created_at DESC);

-- Índice parcial para orders recientes
CREATE INDEX idx_orders_recent ON orders (user_id, created_at)
WHERE created_at > now() - interval '90 days';

-- Índice en users.active si muchas filas están inactivas
CREATE INDEX idx_users_active ON users (id) WHERE active = true;

Por qué: el índice compuesto permite index-only scans cuando la consulta solo necesita las columnas del índice; el índice parcial reduce tamaño si la condición es común.

Index-only scan y cover indexes

Si tu consulta sólo selecciona columnas incluidas en un índice, PostgreSQL puede servir la consulta desde el índice sin acceder a la tabla (index-only scan). Diseña índices que cubran consultas frecuentes.

Paso 3 — Reescribir consultas y estabilizar planes

Una buena estructura de query puede cambiar radicalmente el plan. Reescribe, prueba y compara con EXPLAIN ANALYZE.

Patrones de reescritura útiles

• Evita SELECT * — selecciona sólo columnas necesarias.
• EXISTS vs IN: para subconsultas con muchos resultados, EXISTS suele ser mejor.

  -- Preferir EXISTS
  SELECT u.id FROM users u
  WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100
  );
  

• JOIN vs subquery: a menudo los JOINs son más optimizables que subconsultas correlacionadas.
• Cuidado con CTEs (WITH): en PostgreSQL versiones < 12 las CTEs materializan por defecto; usa subqueries o inline CTEs en v12+ si quieres que el optimizador las inlinee.
• Ordena filtros para reducir filas pronto y minimizar trabajo de agregación y joins.

Ejemplo: reescritura y antes/después

-- Antes: subconsulta correlacionada lenta
SELECT u.id, u.name,
  (SELECT count(*) FROM orders o WHERE o.user_id = u.id) AS orders_count
FROM users u
WHERE u.signup_date > now() - interval '1 year';

-- Después: agrupar y join (mejor para grandes volúmenes)
SELECT u.id, u.name, COALESCE(o.orders_count,0) AS orders_count
FROM users u
LEFT JOIN (
  SELECT user_id, count(*) AS orders_count
  FROM orders
  WHERE created_at > now() - interval '1 year'
  GROUP BY user_id
) o ON o.user_id = u.id
WHERE u.signup_date > now() - interval '1 year';

Estabilizar planes y estadísticas

• Actualiza estadísticas: VACUUM ANALYZE (Postgres), ANALYZE (MySQL/PG).
• Si el optimizador elige un plan malo por valores atípicos, considera crear histograms, extended statistics (Postgres), o ajustar parametrized plans.
• Usa plan baselines / force plans con cuidado en sistemas que lo soportan (Oracle, SQL Server Query Store).

Otras optimizaciones y tácticas

• Batching: en writes/updates masivos evita una gran transacción; usa batches para reducir locks y wal.
• Particionado: para tablas muy grandes con consultas por rango (fecha), particionar puede reducir escaneos.
• Materialized views: cuando la agregación es costosa y la frescura tolera latencia, una vista materializada puede ayudar.
• Connection pooling y prepared statements: reducen overhead y permiten planes más estables.
• Monitoriza contención: bloqueos y filas hot-spot pueden hacer que consultas rápidas fallen bajo carga concurrente.

Checklist rápido (aplicar en orden)

1. Mide: EXPLAIN ANALYZE, métricas de latencia, pg_stat_statements.
2. Corrige estadísticas: VACUUM/ANALYZE y reindex si es necesario.
3. Revisa estimaciones (rows vs actual) — causa de muchos problemas.
4. Agrega índices apropiados (compuestos, parciales, de expresión).
5. Reescribe querys: evita SELECT *, usa EXISTS, agrupa antes de join si conviene.
6. Prueba con datos reales; compara EXPLAIN ANALYZE antes/después.

Advertencias y costos

• Cada índice penaliza los writes; mide impacto en inserts/updates.
• No optimices prematuramente: prioriza consultas que consumen más recursos.
• Ten cuidado al forzar planes: puede romper con cambios de datos.

Consejo avanzado: crea un entorno de pruebas con una réplica de producción (sampling de datos) y automatiza comparaciones de EXPLAIN ANALYZE mediante scripts. Empieza con las 10 consultas que más consumen recursos y aplica los pasos anteriores iterativamente.

Si quieres, puedo generar un checklist ejecutable (scripts SQL + comandos para Postgres) para auditar tu base de datos y proponer índices candidatos basados en pg_stat_statements, o revisar una consulta concreta que me pegues aquí.