Guía completa de SQL: optimización y buenas prácticas para consultas rápidas

Esta guía práctica está dirigida a desarrolladores y DBAs que quieren diagnosticar y acelerar consultas SQL en PostgreSQL y MySQL. Va al grano: cómo entender planes de ejecución, índices efectivos, reescritura de consultas y reglas prácticas para evitar cuellos de botella.

1. Diagnóstico: reproduce, mide y entiende

• Siempre reproduce la carga en un entorno controlado (staging) con datos representativos.
• Usa EXPLAIN/EXPLAIN ANALYZE para ver planificación y ejecución.
• Mide CPU, I/O, tiempos de espera de locks y estadísticas de buffers.

Comandos útiles (Postgres):

-- planificación sin ejecutar
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- plan y ejecución con tiempos y buffers
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE customer_id = 42;

MySQL (InnoDB):

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;
SHOW STATUS LIKE 'Handler%'; -- para conteos de lecturas

2. Índices: cómo y cuándo

• Usa índices B-tree para igualdad y rangos; Hash solo si tu motor lo justifica.
• El orden importa en índices compuestos: index(colA, colB) sirve colA=... y colA+colB, no colB solo.
• Índices covering (incluir columnas) evitan accesos a la fila (index-only scan).
• Evita índices en columnas con muy baja cardinalidad (booleanos) salvo para consultas altamente selectivas.

Ejemplos (Postgres):

-- índice simple
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- índice compuesto (colA primero)
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- índice que cubre y evita heap access
CREATE INDEX idx_orders_customer_status_total ON orders(customer_id, status, total_amount);

3. Estadísticas y mantenimiento

• Postgres: RUN ANALYZE/ VACUUM (o autovacuum) para estadísticas actualizadas.
• Si las estadísticas están desactualizadas, el planner puede elegir planes pobres.
• En tablas grandes, considera ANALYZE VERBOSE para ver la muestra.

-- actualizar estadísticas (Postgres)
ANALYZE orders;
VACUUM (VERBOSE, ANALYZE) orders;

4. Reescritura de consultas: patrones que funcionan

• Evita SELECT *; pide solo columnas necesarias para reducir I/O.
• Prefiere JOINs bien indexados a subconsultas anidadas en muchas situaciones; pero usa EXISTS para pruebas de existencia.
• Evita OFFSET grande; usa paginación por cursor (WHERE id < last_id ORDER BY id DESC LIMIT n).
• CTEs en Postgres pueden materializarse: si quieres que se inlinee, en PG 12+ puedes usar la opción "WITH ... AS (SELECT ... )" y el planner decidirá; en versiones anteriores CTE = materialized.

-- malas prácticas
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE active = false);

-- mejor: JOIN con índice en customers(id) o EXISTS
SELECT o.*
FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.active = false);

5. Joins y cardinalidad

El orden lógico de las tablas en el SQL no siempre coincide con el orden de ejecución; depende del planner. Aún así:

• Asegúrate de índices en columnas usadas en joins y filtros.
• Para mezclas grandes (> 1M filas), considera dividir la operación o pre-agregar.

6. Ventanas y agregaciones

Las funciones de ventana son potentes pero costosas. Si solo necesitas agregación simple, agrupa primero y luego haz joins.

-- preferible: agregación previa
SELECT o.customer_id, agg.total
FROM (
  SELECT customer_id, SUM(total_amount) as total
  FROM orders
  GROUP BY customer_id
) agg
JOIN customers c ON c.id = agg.customer_id
WHERE agg.total > 1000;

7. Particionamiento y shard

• Particiona tablas muy grandes por rango (fecha) o por lista (país), para reducir scans.
• Partition pruning permite que el planner ignore particiones irrelevantes si el filtro es sargable (por ejemplo, WHERE created_at > '2025-01-01').

8. Operaciones de escritura y concurrencia

• Batch inserts/updates en chunks (por ejemplo, 1000 filas) en lugar de operaciones row-by-row.
• En Postgres, evita long-running transactions que bloqueen autovacuum y acumulen bloat.
• Usa índices parciales si solo una porción de la tabla se consulta frecuentemente (e.g., WHERE active = true).

-- índice parcial (Postgres)
CREATE INDEX idx_active_orders ON orders(customer_id) WHERE active = true;

9. Evita anti-patrones comunes

• Funciones en columnas del WHERE que impiden el uso de índices (por ejemplo, WHERE LOWER(name) = 'ana' sin índice funcional).
• Usar LIKE '%foo' causa full scan; prefiera búsquedas con prefijo o índices textuales (GIN/GIN_TRGM).
• Dependencia excesiva en OR en filtros: a veces es mejor UNION de queries con índices específicos.

-- índice funcional para LOWER
CREATE INDEX idx_users_lower_name ON users (LOWER(name));

-- trigram para búsquedas con %foo%
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

10. Checklist rápido de optimización

1. Reproduce y mide con EXPLAIN (ANALYZE).
2. Comprueba estadísticas y actualízalas (ANALYZE).
3. Evita SELECT *; selecciona solo columnas necesarias.
4. Asegúrate de índices apropiados (y revisa su uso con pg_stat_user_indexes).
5. Reescribe queries: subqueries → JOIN/EXISTS cuando convenga.
6. Considera índices compuestos y covering indexes.
7. Evita OFFSET grande; usa cursors/paginación por clave.
8. Usa particionamiento para tablas enormes.
9. Revisa contención/locks y long-running transactions.
10. Automatiza monitoreo: slow query log, pg_stat_statements o Performance Schema.

Ejemplo práctico: de lento a rápido (Postgres)

Escenario: tabla orders con 50M filas, consulta lenta:

-- consulta original
SELECT id, customer_id, status, total_amount, created_at
FROM orders
WHERE status = 'completed'
AND created_at > '2026-01-01';

Plan inicial muestra sequential scan. Pasos:

1. Verificar cardinalidad y distribución (SELECT count(*) WHERE ...).
2. Crear índice compuesto que cubra filtro y orden: (status, created_at) y si posible incluir total_amount para index-only scan.

CREATE INDEX idx_orders_status_created_at_total ON orders(status, created_at, total_amount);
ANALYZE orders;

-- re-ejecutar EXPLAIN ANALYZE y comprobar index-only scan
EXPLAIN (ANALYZE, BUFFERS) SELECT id, customer_id, status, total_amount, created_at
FROM orders
WHERE status = 'completed'
AND created_at > '2026-01-01';

Resultado esperado: planner usa el índice y reduce I/O en disco; tiempos mejoran significantemente.

Herramientas y métricas

• Postgres: pg_stat_statements, EXPLAIN, pg_top, pgbadger
• MySQL: Performance Schema, EXPLAIN FORMAT=JSON, pt-query-digest
• Monitoreo: latencia de queries, I/O por segundo, cache hit ratio, locks/waits

Si necesitas un plan de acción inmediato: captura el EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON si tu motor lo soporta), pega la consulta y los índices actuales; con eso puedo sugerir cambios concretos.

Consejo avanzado: cuando optimices, mide gains por cambio (A/B). A veces la mejor mejora no es otro índice, sino un cambio en la arquitectura: agregados precomputados, colas para procesamiento asíncrono o cache externo (Redis) para resultados calientes.

Si quieres, pega aquí un EXPLAIN real y lo analizamos juntos.