Guía completa de rendimiento en SQL para desarrolladores: índices, planes y optimización

Esta guía te da reglas prácticas, ejemplos ejecutables y técnicas para diagnosticar y optimizar consultas SQL en bases relacionales (PostgreSQL/MySQL). Incluye: índices, SARGability, planes de ejecución, estadísticas, particionamiento y anti-patrones comunes.

Índice

• Fundamentos de índices
• SARGability y reescritura de consultas
• Índices compuestos, covering y funcionales
• Analizar planes: EXPLAIN / EXPLAIN ANALYZE
• Estadísticas, VACUUM/ANALYZE y mantenimiento
• Particionamiento y materialized views
• Herramientas de diagnóstico
• Errores comunes y cómo solucionarlos
• Ejemplos prácticos (Postgres)

1) Fundamentos de índices

Un índice reduce la cantidad de páginas/leídas necesarias para localizar filas. No todos los filtros se benefician de índices: un índice es efectivo cuando filtras por columnas con selectividad suficiente y cuando la operación puede usar la estructura del índice.

Tipos comunes

• B-tree: predeterminado, perfecto para igualdad y rangos.
• Hash: igualdad pura (menos usado fuera de casos muy concretos).
• GIN/GIST: para arrays, JSONB, búsquedas de texto o datos geométricos.
• Índices parciales y funcionales: índices sobre condiciones o funciones específicas.

2) SARGability: por qué importa

SARGable = Search ARGument able. Las expresiones SARGables permiten usar índices. Evita aplicar funciones sobre la columna en el WHERE si quieres aprovechar índices.

Ejemplos

-- NO SARGABLE
SELECT * FROM users WHERE LOWER(name) = 'ana';

-- SARGABLE: crear un índice funcional
CREATE INDEX idx_users_lower_name ON users (LOWER(name));
SELECT * FROM users WHERE LOWER(name) = 'ana';

-- Mejor: guardar normalized_name y filtrar directamente

Siempre piensa: ¿puede el motor transformar la condición en búsqueda por rango/igualdad en el índice?

3) Índices compuestos y covering

Orden importa en índices compuestos (col1, col2). Un índice (a,b) puede usarse para filtros sobre a y a+b, pero no para b solo.

-- Buen uso
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

-- Usos que aprovechan el índice:
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

-- Covering index: incluye columnas para evitar acceder a la tabla
CREATE INDEX idx_orders_cover ON orders (customer_id, created_at) INCLUDE (total_amount);
-- En Postgres: usar INCLUDE(...), en MySQL usar índice compuesto o index-only scan si posible

Un covering index (índice que contiene todas las columnas necesarias) permite index-only scans y reduce I/O.

4) EXPLAIN y EXPLAIN ANALYZE: interpretar planes

EXPLAIN muestra el plan estimado; EXPLAIN ANALYZE ejecuta y muestra tiempos reales. Aprende a leer:

• Seq Scan vs Index Scan
• Costes (estimado) y rows (estimados vs reales)
• Nested Loop, Hash Join, Merge Join

Ejemplo práctico (Postgres)

-- Crear tabla de ejemplo
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT,
  name TEXT,
  created_at TIMESTAMP
);

-- Poblar (ejemplo simple)
INSERT INTO users (email, name, created_at)
SELECT md5(random()::text)||'@example.com',
       substring(md5(random()::text),1,10),
       NOW() - (random()*365 || ' days')::interval
FROM generate_series(1,200000) g;

-- Intentar una consulta
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'some@example.com';

Si EXPLAIN ANALYZE muestra Seq Scan y la tabla es grande, probablemente falta un índice en email:

CREATE INDEX idx_users_email ON users (email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'some@example.com';

Compara estimated rows vs actual rows: grandes discrepancias significan estadísticas desactualizadas o patrones no representados.

5) Estadísticas y mantenimiento

El optimizador confía en estadísticas. Ejecuta ANALYZE (o VACUUM ANALYZE en Postgres) tras cargas masivas. En MySQL usa ANALYZE TABLE y revisa innodb_stats_persistent.

• Postgres: VACUUM, ANALYZE, pg_stat_user_tables, ajustar default_statistics_target si necesitas más precisión.
• MySQL: ANALYZE TABLE, revisar information_schema, activar slow_query_log para capturar queries lentas.

6) Particionamiento y materialized views

Para tablas extremadamente grandes, particionar reduce escaneo. Usa particionamiento por rango/fecha para consultas por rango temporal.

-- Postgres: particionamiento por rango sencillo
CREATE TABLE events (
  id BIGSERIAL,
  event_date DATE,
  payload JSONB
) PARTITION BY RANGE (event_date);

CREATE TABLE events_2025 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Materialized views son útiles para cargas complejas que no cambian con alta frecuencia. Recuerda refrescarlas periódicamente.

7) Herramientas de diagnóstico

• Postgres: pg_stat_activity, pg_stat_statements, auto_explain
• MySQL: performance_schema, slow query log, EXPLAIN FORMAT=JSON
• Monitoreo: Datadog, Prometheus exporters para bases de datos, pganalyze

8) Errores comunes y soluciones rápidas

• Crear muchos índices: ralentiza writes. Regla: crear índices para consultas críticas que se ejecutan frecuentemente.
• Usar SELECT * en tablas grandes: especifica columnas para reducir I/O y favorecer index-only scans.
• Funciones en WHERE sin índice funcional: crea índices funcionales o normaliza datos.
• JOINs sin índices en claves de unión: asegúrate de índices en columnas usadas en ON.
• Dependencia de orden en índices compuestos: revisa el orden de columnas según filtros y ORDER BY.
• Estadísticas desactualizadas tras bulk loads: ejecutar ANALYZE.

9) Ejemplo completo: detectar y optimizar una consulta lenta (Postgres)

Estructura de carpetas sugerida para un repo con scripts:

sql-performance-guide/
├─ data/                -- dumps y muestras
├─ scripts/
│  ├─ load_data.sql
│  ├─ create_indexes.sql
│  └─ explain_queries.sql
└─ outputs/
   └─ explain_analyze_results.txt

Ejemplo de flujo:

-- scripts/load_data.sql: crear tabla y popular
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT,
  status TEXT,
  total NUMERIC,
  created_at TIMESTAMP
);

-- Insertar 5M filas (ejemplo conceptual)
-- En Postgres usar COPY o INSERT ... SELECT generate_series(...)

-- scripts/create_indexes.sql
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);
CREATE INDEX idx_orders_status ON orders (status);

-- scripts/explain_queries.sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT customer_id, SUM(total) as total_spent
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 100;

Si al ejecutar EXPLAIN ANALYZE ves un Nested Loop con mucha lectura: considera

• Ajustar índices (agregar columnas en índice para cubrir GROUP BY)
• Usar agregaciones precomputadas (materialized view)
• Reescribir query para hacer filtrado antes de join/aggregate

Reescritura de ejemplo

-- Reescribir para reducir filas antes de agrupar
WITH recent_orders AS (
  SELECT customer_id, total
  FROM orders
  WHERE created_at >= now() - interval '30 days'
)
SELECT customer_id, SUM(total) FROM recent_orders GROUP BY customer_id
ORDER BY SUM(total) DESC LIMIT 100;

-- Si el CTE es inlining en tu versión de Postgres y causa doble trabajo, 
-- convierte a subquery o materialized view:
CREATE MATERIALIZED VIEW mv_recent_order_totals AS
SELECT customer_id, SUM(total) as total_spent
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY customer_id;

-- Refrescar periódicamente según necesidad
REFRESH MATERIALIZED VIEW mv_recent_order_totals;

10) Buenas prácticas rápidas

• Prueba con datos reales o escala los datos de prueba; resultados sobre 100 filas son engañosos.
• Mide antes y después: EXPLAIN ANALYZE es tu amigo.
• Documenta índices y razón de existencia para evitar acumulación innecesaria.
• Evita optimizaciones prematuras; perfila y optimiza los hotspots reales.

Advertencia técnica: crear índices indiscriminadamente degradará escrituras y aumentará complejidad de mantenimiento. Equilibra lectura/escritura y monitorea overhead.

Siguiente paso sugerido: elige una consulta lenta en tu base real, crea un script que capture EXPLAIN ANALYZE antes y después de cambios (índices/reescritura/estadísticas) y automatiza comparaciones. Si quieres, envíame el EXPLAIN ANALYZE y te ayudo a interpretarlo y proponer cambios avanzados.