Optimización Avanzada de Consultas en MySQL

Traza del Optimizador

-- Activar traza del optimizador
SET SESSION optimizer_trace = 'enabled=on';

SELECT * FROM pedidos o
JOIN clientes c ON o.cliente_id = c.id
WHERE o.estado = 'pendiente' AND c.pais = 'MX';

SELECT * FROM information_schema.OPTIMIZER_TRACE\G

SET SESSION optimizer_trace = 'enabled=off';

Estrategias de Índices

-- Índice parcial con columna virtual
ALTER TABLE usuarios
    ADD COLUMN email_lower VARCHAR(255) GENERATED ALWAYS AS (LOWER(email)) VIRTUAL,
    ADD INDEX idx_email_lower (email_lower);

-- Índice multivalor en arrays JSON (MySQL 8.0.17+)
CREATE TABLE eventos (
    id     INT PRIMARY KEY,
    etiquetas JSON
);

CREATE INDEX idx_etiquetas ON eventos ((CAST(etiquetas AS CHAR(100) ARRAY)));

SELECT * FROM eventos WHERE 'mysql' MEMBER OF (etiquetas);

Funciones de Ventana para Analítica

SELECT
    fecha_pedido,
    monto,
    SUM(monto) OVER (
        PARTITION BY cliente_id
        ORDER BY fecha_pedido
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_acumulado,
    RANK() OVER (PARTITION BY cliente_id ORDER BY monto DESC) AS ranking_monto,
    LAG(monto, 1) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido) AS monto_anterior
FROM pedidos;

CTEs Recursivas

WITH RECURSIVE arbol_categorias AS (
    SELECT id, nombre, padre_id, 1 AS profundidad
    FROM categorias
    WHERE padre_id IS NULL

    UNION ALL

    SELECT c.id, c.nombre, c.padre_id, ac.profundidad + 1
    FROM categorias c
    JOIN arbol_categorias ac ON c.padre_id = ac.id
    WHERE ac.profundidad < 10
)
SELECT * FROM arbol_categorias ORDER BY profundidad, nombre;

Sugerencias al Optimizador

-- Forzar un índice específico
SELECT /*+ INDEX(o idx_pedidos_estado_fecha) */ *
FROM pedidos o
WHERE estado = 'pendiente';

-- Definir orden de JOIN
SELECT /*+ JOIN_ORDER(c, o, oi) */ *
FROM clientes c
JOIN pedidos o ON c.id = o.cliente_id
JOIN detalle_pedido oi ON o.id = oi.pedido_id;

Errores Comunes que Rompen Índices

-- ❌ Función sobre columna indexada
SELECT * FROM usuarios WHERE YEAR(creado_en) = 2024;

-- ✅ Usar predicado de rango
SELECT * FROM usuarios
WHERE creado_en >= '2024-01-01' AND creado_en < '2025-01-01';

-- ❌ Wildcard al inicio
SELECT * FROM productos WHERE nombre LIKE '%tablet%';

-- ✅ Índice de texto completo
ALTER TABLE productos ADD FULLTEXT INDEX ft_nombre (nombre, descripcion);
SELECT * FROM productos WHERE MATCH(nombre, descripcion) AGAINST('tablet');