Optimización de Rendimiento SQL

Introducción

Las consultas lentas son una de las causas más comunes de problemas de rendimiento en aplicaciones. A continuación: técnicas prácticas para identificar, diagnosticar y corregir problemas de rendimiento SQL en PostgreSQL, MySQL y SQL Server.

Encontrando Consultas Lentas

PostgreSQL

-- Extensión pg_stat_statements
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Consultas lentas activas
SELECT pid, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '1 second'
ORDER BY duration DESC;

MySQL

-- Slow query log (habilitar en my.cnf)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Performance Schema
SELECT sql_text, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

Entendiendo los EXPLAIN Plans

El plan EXPLAIN revela cómo la base de datos ejecuta tu consulta.

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;

Operaciones Clave del Plan

Operación	Significado	Impacto en Rendimiento
Seq Scan	Escaneo de tabla (lee cada fila)	Lento en tablas grandes; necesita índice
Index Scan	Lee índice, luego busca filas coincidentes	Rápido para consultas selectivas
Index Only Scan	Lee solo el índice, sin acceso a tabla	El más rápido; requiere índice cubriente
Bitmap Heap Scan	Construye bitmap desde índice, busca filas en lotes	Bueno para selectividad moderada
Nested Loop	Para cada fila externa, escanea tabla interna	Bien para conjuntos externos pequeños
Hash Join	Construye tabla hash interna, sonda con externa	Bueno para joins grandes
Merge Join	Ordena ambas entradas, las mezcla	Bueno para datos pre-ordenados

Estrategias de Indexación

Índices B-Tree (Por Defecto)

Mejores para igualdad y rangos:

-- Índice de columna simple
CREATE INDEX idx_users_email ON users(email);

-- Índice compuesto (el orden de columnas importa)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Bueno para: WHERE user_id = ? AND status = ?
-- Malo para:  WHERE status = ? (user_id no es líder)

Orden de Columnas en Índice

Coloca columnas en esta prioridad:

1. Filtros de igualdad (=)
2. Filtros de rango (>, <, BETWEEN, LIKE 'prefijo%')
3. Columnas usadas en ORDER BY
4. Columnas usadas en SELECT (para índices cubrientes)

Índices Cubrientes

Un índice que contiene todas las columnas necesarias para la consulta, evitando búsquedas en tabla:

CREATE INDEX idx_orders_covering
ON orders(user_id, status, created_at, total)
INCLUDE (id);

-- La consulta puede satisfacerse completamente desde el índice
SELECT id, created_at, total
FROM orders
WHERE user_id = 123 AND status = 'shipped';

Índices Parciales

Indexa solo un subconjunto de filas, reduciendo tamaño y costo de mantenimiento:

-- Indexar solo usuarios activos
CREATE INDEX idx_users_active_email
ON users(email)
WHERE is_active = true;

Técnicas de Reescritura de Consultas

1. Evita SELECT *

-- Malo
SELECT * FROM orders WHERE user_id = 123;

-- Bueno: obtén solo las columnas necesarias
SELECT id, status, total FROM orders WHERE user_id = 123;

2. Usa EXISTS en lugar de IN para Subconsultas

-- Malo: materializa resultado completo de subconsulta
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- Bueno: se detiene en la primera coincidencia
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.amount > 1000
);

3. Evita Funciones en Columnas Indexadas

-- Malo: la función impide uso del índice
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';

-- Bueno: consulta de rango usa el índice
SELECT * FROM orders
WHERE created_at >= '2024-01-15'
  AND created_at < '2024-01-16';

4. Prefiere JOINs sobre Subconsultas Correlacionadas

-- Malo: subconsulta correlacionada se ejecuta una vez por fila
SELECT name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- Bueno: JOIN es más eficiente
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

5. Actualizaciones en Batch en lugar de Una por Una

-- Malo: N+1 actualizaciones
UPDATE orders SET status = 'shipped' WHERE id = 1;
UPDATE orders SET status = 'shipped' WHERE id = 2;
...

-- Bueno: UPDATE único con WHERE IN o JOIN
UPDATE orders
SET status = 'shipped'
WHERE id IN (1, 2, 3, ...);

Anti-Patrones Comunes

Anti-Patrón	Problema	Solución
Consultas N+1	Una consulta por fila	Usa JOIN o WHERE IN. Consulta indexación de base de datos.
Sin LIMIT	Obtener millones de filas	Agrega LIMIT y paginación
Conversiones implícitas	Función en columna impide uso de índice	Castea la constante, no la columna
SELECT DISTINCT para arreglar duplicados	Oculta un problema de join	Arregla el join o el esquema
Contar todas las filas	SELECT COUNT(*) en tablas enormes	Usa conteos aproximados o triggers
Sin pool de conexiones	Sobrecarga de conexión domina	Usa pool de conexiones.

Lo que funciona

• Indexa claves foráneas automáticamente — los joins dependen de ellas
• Monitorea logs de consultas lentas semanalmente y atiende los principales ofensores
• Analiza tablas regularmente — ANALYZE actualiza estadísticas para el planificador
• Evita sobre-indexación — cada índice ralentiza escrituras y consume espacio
• Usa tipos de datos apropiados — INTEGER es más rápido que VARCHAR para IDs
• Particiona tablas grandes por fecha o rango cuando exceden 10M filas

Temas Avanzados

Escenario: Optimizacion de Dashboard E-commerce

-- Problema: Query del dashboard tarda 45 segundos
-- Tabla: orders (50M filas), order_items (200M filas)

-- Query original:
EXPLAIN ANALYZE
SELECT c.name, SUM(oi.line_total) AS revenue,
       COUNT(DISTINCT o.id) AS orders
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed'
GROUP BY c.name
ORDER BY revenue DESC LIMIT 20;

-- Plan: 2 seq scans + hash joins + sort, 45s

-- Fix 1: Indices compuestos
CREATE INDEX idx_orders_date_status ON orders(created_at, status)
  WHERE status = 'completed';
CREATE INDEX idx_order_items_order ON order_items(order_id);
-- Plan: 2 index scans + merge join, 8s

-- Fix 2: Materialized view para pre-agregacion
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT DATE(o.created_at) AS sale_date,
       c.name AS customer,
       SUM(oi.line_total) AS revenue,
       COUNT(DISTINCT o.id) AS orders
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY DATE(o.created_at), c.name;

CREATE UNIQUE INDEX idx_mv_daily ON mv_daily_sales(sale_date, customer);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

-- Dashboard ahora usa MV:
SELECT customer, SUM(revenue) AS total, SUM(orders) AS order_count
FROM mv_daily_sales
WHERE sale_date >= '2026-01-01'
GROUP BY customer
ORDER BY total DESC LIMIT 20;
-- Plan: seq scan MV (1M filas) + aggregate, 200ms

-- Fix 3: Connection pooling (PgBouncer)
  [databases]
  production = host=10.0.0.1 port=5432 dbname=app
  [pgbouncer]
  pool_mode = transaction
  max_client_conn = 1000
  default_pool_size = 25
  -- Reduce overhead de conexion de 50ms a 2ms

-- Fix 4: Particionar orders por mes
CREATE TABLE orders (
    id BIGSERIAL, customer_id BIGINT, status VARCHAR(20),
    created_at TIMESTAMPTZ NOT NULL, total DECIMAL(10,2)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- Query solo escanea particion de enero, no 50M filas

Resultados:
  | Optimizacion | Antes | Despues |
  |--------------|-------|---------|
  | Sin optimizar | 45s | - |
  | + Indices | 45s | 8s |
  | + Materialized view | 8s | 200ms |
  | + Connection pool | 200ms + 50ms | 200ms + 2ms |
  | + Particionado | MV refresh 60s | MV refresh 15s |

Lecciones:
  - Indices raramente resuelven queries analiticas complejas
  - Materialized views pre-computan agregaciones costosas
  - Connection pooling elimina overhead por request
  - Particionado reduce scope de scan para queries temporales
  - Siempre mide con EXPLAIN ANALYZE antes y despues

Cuando deberia usar query hints?

Evita query hints en PostgreSQL (no existen). En MySQL, USE INDEX o FORCE INDEX pueden ayudar cuando el planificador se equivoca. Pero los hints son parches: la solucion real es actualizar estadisticas (ANALYZE), crear mejores indices o reestructurar la query. Los hints se vuelven obsoletos cuando los datos cambian.