Optimización de queries

Resumen

Este skill guía el proceso de identificar y optimizar queries lentas en bases de datos relacionales. La optimización de queries no consiste en añadir índices a ciegas, sino en entender cómo el motor de base de datos ejecuta una consulta y actuar sobre los cuellos de botella concretos.

El proceso parte de una query lenta identificada (por logs, APM o reporte del usuario), aplica herramientas de análisis como EXPLAIN y propone soluciones que se validan con benchmarks antes y después.

Proceso

1. Identificar la query lenta. Localizar la consulta problemática a partir de fuentes concretas:

   • Slow query log del motor de base de datos (MySQL: slow_query_log, PostgreSQL: log_min_duration_statement).
   • Herramientas de APM (Datadog, New Relic, Sentry Performance).
   • Reporte del usuario o del equipo de desarrollo.
   • Registrar el tiempo actual de ejecución como baseline para comparar después.

2. Ejecutar EXPLAIN o EXPLAIN ANALYZE. Obtener el plan de ejecución de la query:

   • PostgreSQL: EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) para ver tiempos reales y acceso a disco.
   • MySQL: EXPLAIN FORMAT=JSON o EXPLAIN ANALYZE (MySQL 8.0+) para detalle adicional.
   • Leer el plan de ejecución de dentro hacia fuera (las operaciones más internas se ejecutan primero).

3. Identificar los cuellos de botella. Buscar estos patrones en el plan de ejecución:

   • Seq Scan / Full Table Scan: la base de datos recorre toda la tabla porque no hay índice útil.
   • Nested Loop sin índice: joins que recorren la tabla interna completa por cada fila de la externa.
   • Sort sin índice: ordenaciones que se hacen en memoria o disco en lugar de aprovechar un índice.
   • Subconsultas correlacionadas: subconsultas que se ejecutan una vez por cada fila de la consulta externa.
   • Estimaciones incorrectas: filas estimadas muy diferentes de las reales, lo que indica estadísticas desactualizadas.

4. Proponer soluciones según el cuello de botella. Cada problema tiene soluciones específicas:

   • Full scan: crear índice en las columnas del WHERE, o índice compuesto si filtra por varias.
   • Join sin índice: asegurar que la columna de join tiene índice en la tabla interna.
   • Subconsulta correlacionada: reescribir como JOIN o usar CTE (Common Table Expression).
   • Ordenación costosa: crear índice que cubra tanto el filtro como el orden.
   • Selección de demasiadas columnas: usar SELECT explícito en lugar de SELECT * y considerar covering indexes.
   • Estadísticas obsoletas: ejecutar ANALYZE (PostgreSQL) o ANALYZE TABLE (MySQL).

5. Evaluar reescrituras de la query. A veces el problema no es el índice sino la estructura de la query:

   • Sustituir IN con subconsulta por EXISTS o JOIN.
   • Evitar funciones sobre columnas indexadas en el WHERE (rompe el uso del índice).
   • Usar paginación con cursor en lugar de OFFSET para conjuntos grandes.
   • Considerar vistas materializadas para agregaciones costosas que se consultan frecuentemente.

6. Hacer benchmark antes y después. Medir el impacto real del cambio:

   • Ejecutar la query original y la optimizada en las mismas condiciones.
   • Comparar tiempo de ejecución, filas examinadas y buffers/páginas leídas.
   • Verificar que la query optimizada devuelve exactamente los mismos resultados.
   • Ejecutar varias iteraciones para descartar variabilidad por caché.

7. Documentar el cambio. Registrar la optimización con: query original, plan de ejecución antes, cambio aplicado, plan de ejecución después y métricas de mejora. Este registro es valioso para detectar regresiones futuras.

Que NO hacer

• No añadir índices sin verificar con EXPLAIN que el motor los utiliza.
• No optimizar queries que se ejecutan pocas veces al día y tardan milisegundos; centrar el esfuerzo en las que generan carga real.
• No sacrificar la legibilidad de la query por una mejora marginal de rendimiento.
• No olvidar que cada índice añadido ralentiza las operaciones de escritura (INSERT, UPDATE, DELETE).
• No confiar en el tiempo de ejecución de una sola iteración como benchmark fiable.