Consultas Lentas: Análisis y Optimización - Guía Completa de Rendimiento

Introducción

El rendimiento de las consultas de base de datos es un factor crítico en la capacidad de respuesta de las aplicaciones, la satisfacción del usuario y la eficiencia general del sistema. Las consultas lentas pueden impactar significativamente el rendimiento de las aplicaciones, llevando a una mala experiencia del usuario, aumento del consumo de recursos del servidor y posibles fallos del sistema bajo alta carga. Una sola consulta mal optimizada puede llevar toda una aplicación de rodillas, afectando a miles de usuarios y costando a las organizaciones ingresos y reputación sustanciales.

La optimización de consultas es tanto un arte como una ciencia, requiriendo un profundo entendimiento de los componentes internos de la base de datos, estrategias de indexación, planes de ejecución de consultas y patrones de acceso a datos. Aunque los sistemas de bases de datos modernos como MySQL, PostgreSQL y MariaDB tienen optimizadores de consultas sofisticados, estos dependen de estadísticas precisas, indexación apropiada y consultas bien estructuradas para generar planes de ejecución eficientes. Incluso con optimización automática, entender cómo identificar, analizar y corregir consultas lentas sigue siendo una habilidad esencial para desarrolladores, DBAs e ingenieros DevOps.

Esta guía completa cubre todo, desde identificar consultas lentas usando varias herramientas de monitoreo hasta entender planes de ejecución de consultas, implementar estrategias efectivas de indexación y aplicar técnicas avanzadas de optimización. Ya sea que estés solucionando un problema de rendimiento en producción o optimizando proactivamente tu base de datos, esta guía proporciona el conocimiento y las técnicas prácticas necesarias para mejorar significativamente el rendimiento de las consultas y mantener un sistema de base de datos responsivo y escalable.

Requisitos Previos

Antes de adentrarte en la optimización de consultas, asegúrate de tener:

Requisitos del Sistema

  • MySQL 5.7+, MySQL 8.0+, PostgreSQL 12+, o MariaDB 10.3+
  • Acceso al servidor de base de datos con recursos suficientes
  • Privilegios administrativos en la base de datos
  • Capacidad de modificar archivos de configuración de la base de datos

Acceso a la Base de Datos

  • Credenciales root o administrativas de base de datos
  • Capacidad de habilitar registro de consultas lentas
  • Permiso para crear y modificar índices
  • Acceso a performance schema o catálogos del sistema

Requisitos de Conocimiento

  • Comprensión sólida de sintaxis SQL
  • Conocimiento básico de conceptos de indexación de bases de datos
  • Familiaridad con arquitectura de bases de datos
  • Entendimiento de conceptos de ejecución de consultas
  • Habilidades básicas de monitoreo de rendimiento

Herramientas y Acceso

  • Cliente de línea de comandos de base de datos (mysql, psql)
  • Acceso a archivos de configuración de base de datos
  • Permisos de lectura de archivos de log
  • Opcional: Herramientas de análisis de consultas (pt-query-digest, pgBadger)

Entendiendo el Rendimiento de Consultas

Ciclo de Vida de Ejecución de Consultas

Entender cómo las bases de datos ejecutan consultas es crucial para la optimización:

  1. Análisis: La sintaxis de la consulta es validada y analizada en una estructura interna
  2. Optimización: El optimizador de consultas genera el plan de ejecución
  3. Ejecución: El motor de base de datos ejecuta el plan
  4. Retorno de Resultados: Los resultados filtrados y formateados son retornados al cliente

Cuellos de Botella Comunes de Rendimiento

Índices Faltantes: Causa más común de consultas lentas. Los escaneos de tabla completa leen tablas enteras en lugar de usar índices para localizar datos rápidamente.

Índices Ineficientes: Tipo de índice incorrecto, orden de columnas o índices no usados que ralentizan las escrituras sin mejorar las lecturas.

Conjuntos de Resultados Grandes: Retornar millones de filas cuando solo se necesitan cientos.

Joins Complejos: Múltiples joins de tablas sin índices apropiados u orden de join ineficiente.

Estructura de Consulta Subóptima: Usar subconsultas en lugar de joins, o cláusulas WHERE ineficientes.

Bloqueos de Tabla: Consultas de larga duración manteniendo bloqueos y bloqueando otras operaciones.

Restricciones de Recursos: Memoria insuficiente, E/S de disco lenta o limitaciones de CPU.

Diseño de Esquema Pobre: Problemas de desnormalización, tipos de datos incorrectos o claves foráneas faltantes.

Métricas Clave de Rendimiento

Tiempo de Ejecución de Consulta: Tiempo total desde envío de consulta hasta retorno de resultado.

Filas Examinadas: Número de filas que la base de datos tuvo que escanear para producir resultados.

Filas Retornadas: Número de filas realmente retornadas al cliente.

Uso de Índice: Si se usaron índices y cuáles.

Tiempo de Espera de Bloqueo: Tiempo gastado esperando que se liberen bloqueos.

Creación de Tabla Temporal: Si se crearon tablas temporales en memoria o en disco.

Identificar Consultas Lentas

Método 1: Habilitar Log de Consultas Lentas de MySQL

Configurar MySQL para registrar consultas lentas:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Agregar o modificar:

[mysqld]
# Habilitar log de consultas lentas
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

# Registrar consultas que toman más de 2 segundos
long_query_time = 2

# Registrar consultas que no usan índices
log_queries_not_using_indexes = 1

# Limitar registro de consultas que no usan índices
log_throttle_queries_not_using_indexes = 10

# Registrar sentencias administrativas lentas
log_slow_admin_statements = 1

# Registrar sentencias de réplica lentas
log_slow_slave_statements = 1

# Número mínimo de filas examinadas para registrar
min_examined_row_limit = 1000

Reiniciar MySQL:

sudo systemctl restart mysql

Habilitar en tiempo de ejecución (sin reinicio):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Ver log de consultas lentas:

sudo tail -f /var/log/mysql/mysql-slow.log

Método 2: Habilitar Registro de Consultas Lentas en PostgreSQL

Configurar PostgreSQL para registrar consultas lentas:

sudo nano /etc/postgresql/14/main/postgresql.conf

Agregar o modificar:

# Registrar consultas más lentas que 1000ms (1 segundo)
log_min_duration_statement = 1000

# Registrar más detalles de consultas
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'none'
log_duration = off

# Registrar planes de consulta para consultas lentas
auto_explain.log_min_duration = 1000
shared_preload_libraries = 'auto_explain'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on

Recargar configuración:

sudo systemctl reload postgresql

O habilitar en tiempo de ejecución:

ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();

Ver logs de PostgreSQL:

sudo tail -f /var/log/postgresql/postgresql-14-main.log

Método 3: Performance Schema de Consultas (MySQL 8.0+)

Usar Performance Schema para identificar consultas lentas:

-- Habilitar Performance Schema (usualmente habilitado por defecto en MySQL 8.0+)
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';

-- Encontrar consultas más lentas por tiempo total
SELECT
    DIGEST_TEXT as query,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT / 1000000000000 as avg_time_sec,
    SUM_TIMER_WAIT / 1000000000000 as total_time_sec,
    MAX_TIMER_WAIT / 1000000000000 as max_time_sec,
    SUM_ROWS_EXAMINED as rows_examined,
    SUM_ROWS_SENT as rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- Encontrar consultas con más filas examinadas
SELECT
    DIGEST_TEXT as query,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT / 1000000000000 as avg_time_sec,
    SUM_ROWS_EXAMINED as rows_examined,
    SUM_ROWS_SENT as rows_sent,
    SUM_ROWS_EXAMINED / SUM_ROWS_SENT as examined_sent_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_ROWS_SENT > 0
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 20;

-- Encontrar consultas con escaneos de tabla completa
SELECT
    DIGEST_TEXT as query,
    COUNT_STAR as exec_count,
    SUM_NO_INDEX_USED as full_scans,
    SUM_NO_GOOD_INDEX_USED as bad_index
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 20;

Método 4: Consultar pg_stat_statements (PostgreSQL)

Instalar y usar pg_stat_statements:

-- Instalar extensión
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Ver consultas más lentas
SELECT
    query,
    calls,
    total_exec_time / 1000 as total_time_sec,
    mean_exec_time / 1000 as avg_time_sec,
    max_exec_time / 1000 as max_time_sec,
    rows,
    100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Encontrar consultas con bajo ratio de cache hit
SELECT
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS cache_hit_ratio
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY cache_hit_ratio ASC
LIMIT 20;

-- Reiniciar estadísticas
SELECT pg_stat_statements_reset();

Método 5: Usar pt-query-digest (Percona Toolkit)

Analizar log de consultas lentas de MySQL:

# Instalar Percona Toolkit
sudo apt-get install percona-toolkit

# Analizar log de consultas lentas
pt-query-digest /var/log/mysql/mysql-slow.log > analisis_consultas_lentas.txt

# Analizar con opciones específicas
pt-query-digest \
    --limit 95%:20 \
    --filter '$event->{fingerprint} =~ m/^select/i' \
    /var/log/mysql/mysql-slow.log

# Analizar consultas desde Performance Schema
pt-query-digest \
    --source performance_schema:dbi:mysql \
    --limit 20

Ver reporte de análisis:

less analisis_consultas_lentas.txt

Método 6: Usar pgBadger (PostgreSQL)

Analizar archivos de log de PostgreSQL:

# Instalar pgBadger
sudo apt-get install pgbadger

# Analizar archivo de log
pgbadger /var/log/postgresql/postgresql-14-main.log -o reporte.html

# Analizar con opciones específicas
pgbadger \
    --prefix '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' \
    --outfile reporte.html \
    /var/log/postgresql/postgresql-14-main.log

# Abrir reporte en navegador
xdg-open reporte.html

Analizar Planes de Ejecución de Consultas

Análisis EXPLAIN de MySQL

Usar EXPLAIN para entender la ejecución de consultas:

-- EXPLAIN básico
EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 12345;

-- EXPLAIN extendido con más detalles
EXPLAIN FORMAT=JSON SELECT * FROM pedidos WHERE cliente_id = 12345;

-- Analizar estadísticas de ejecución reales (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 12345;

-- Formato de árbol visual (MySQL 8.0.16+)
EXPLAIN FORMAT=TREE SELECT * FROM pedidos WHERE cliente_id = 12345;

Entender salida de EXPLAIN:

EXPLAIN SELECT p.*, c.nombre
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
WHERE p.estado = 'pendiente'
  AND p.fecha_creacion > '2024-01-01'
ORDER BY p.fecha_creacion DESC
LIMIT 100;

Columnas clave en salida de EXPLAIN:

  • id: Identificador de consulta, números más altos ejecutan primero
  • select_type: Tipo de SELECT (SIMPLE, SUBQUERY, UNION, etc.)
  • table: Tabla siendo accedida
  • type: Tipo de join (system, const, eq_ref, ref, range, index, ALL)
    • ALL = Escaneo de tabla completa (peor)
    • index = Escaneo de índice completo
    • range = Escaneo de rango de índice
    • ref = Búsqueda de índice no único
    • eq_ref = Búsqueda de índice único
    • const = Coincidencia de fila única (mejor)
  • possible_keys: Índices que podrían usarse
  • key: Índice realmente usado
  • key_len: Longitud del índice usado
  • ref: Columnas comparadas con el índice
  • rows: Filas estimadas a examinar
  • Extra: Información adicional (Using where, Using index, Using temporary, Using filesort)

Análisis EXPLAIN de PostgreSQL

Usar EXPLAIN en PostgreSQL:

-- EXPLAIN básico
EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 12345;

-- EXPLAIN con estadísticas de ejecución
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 12345;

-- Información detallada de uso de buffer
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pedidos WHERE cliente_id = 12345;

-- Salida detallada con información completa
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS, TIMING)
SELECT * FROM pedidos WHERE cliente_id = 12345;

Entender salida de EXPLAIN de PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS) SELECT p.*, c.nombre
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
WHERE p.estado = 'pendiente'
  AND p.fecha_creacion > '2024-01-01'
ORDER BY p.fecha_creacion DESC
LIMIT 100;

Tipos de nodos clave:

  • Seq Scan: Escaneo secuencial (escaneo de tabla completa) - lento para tablas grandes
  • Index Scan: Escaneo de índice con búsqueda en tabla
  • Index Only Scan: El índice contiene todas las columnas necesarias (más rápido)
  • Bitmap Index Scan: Escanear índice y construir bitmap de filas coincidentes
  • Nested Loop: Join iterando tabla interna para cada fila externa
  • Hash Join: Construir tabla hash y probar para coincidencias
  • Merge Join: Ordenar ambas tablas y fusionar
  • Sort: Operación de ordenamiento explícita
  • Aggregate: Agrupación y agregación

Estrategias de Indexación para Rendimiento

Entendiendo Tipos de Índices

Índice B-Tree (Predeterminado): Mejor para consultas de igualdad y rango. Funciona para >, <, =, BETWEEN, IN, LIKE 'prefijo%'.

-- MySQL
CREATE INDEX idx_cliente_email ON clientes(email);

-- PostgreSQL
CREATE INDEX idx_cliente_email ON clientes(email);

Índice Hash: Solo para comparaciones de igualdad. No soportado en MySQL InnoDB, uso limitado en PostgreSQL.

-- Solo PostgreSQL
CREATE INDEX idx_cliente_email_hash ON clientes USING HASH (email);

Índice de Texto Completo: Para operaciones de búsqueda de texto completo.

-- MySQL
CREATE FULLTEXT INDEX idx_producto_descripcion ON productos(descripcion);

-- PostgreSQL (usando GIN)
CREATE INDEX idx_producto_descripcion ON productos USING GIN (to_tsvector('spanish', descripcion));

Índice Espacial: Para datos geográficos.

-- MySQL
CREATE SPATIAL INDEX idx_ubicacion ON tiendas(ubicacion);

-- PostgreSQL (requiere PostGIS)
CREATE INDEX idx_ubicacion ON tiendas USING GIST (ubicacion);

Crear Índices Efectivos

Índices de columna única:

-- Índice para condiciones WHERE frecuentes
CREATE INDEX idx_pedidos_estado ON pedidos(estado);

-- Índice para condiciones JOIN
CREATE INDEX idx_pedidos_cliente_id ON pedidos(cliente_id);

-- Índice para columnas ORDER BY
CREATE INDEX idx_pedidos_fecha_creacion ON pedidos(fecha_creacion);

Índices compuestos (multi-columna):

-- Índice para consultas filtrando en múltiples columnas
CREATE INDEX idx_pedidos_estado_fecha ON pedidos(estado, fecha_creacion);

-- El orden de columnas del índice importa - la más selectiva primero
CREATE INDEX idx_usuarios_pais_ciudad ON usuarios(pais, ciudad, fecha_registro);

-- El índice de cobertura incluye todas las columnas de la consulta
CREATE INDEX idx_pedidos_cobertura ON pedidos(cliente_id, estado, monto_total, fecha_creacion);

Índices parciales (PostgreSQL):

-- Indexar solo pedidos activos
CREATE INDEX idx_pedidos_activos ON pedidos(fecha_creacion)
WHERE estado IN ('pendiente', 'procesando');

-- Indexar solo datos recientes
CREATE INDEX idx_pedidos_recientes ON pedidos(cliente_id)
WHERE fecha_creacion > '2024-01-01';

Mejores Prácticas de Índices

Crear índices para:

  • Columnas de clave foránea usadas en JOINs
  • Columnas en cláusulas WHERE
  • Columnas en cláusulas ORDER BY
  • Columnas en cláusulas GROUP BY
  • Columnas en operaciones DISTINCT

Ordenamiento de columnas de índice (para índices compuestos):

  1. Condiciones de igualdad primero (=)
  2. Condiciones de rango segundo (>, <, BETWEEN)
  3. Condiciones de ordenamiento al final (ORDER BY)
-- Buen índice compuesto para esta consulta
-- WHERE estado = 'activo' AND fecha_creacion > '2024-01-01' ORDER BY fecha_actualizacion
CREATE INDEX idx_optimizado ON pedidos(estado, fecha_creacion, fecha_actualizacion);

Optimización de Consultas Paso a Paso

Paso 1: Identificar la Consulta Lenta

Usar log de consultas lentas o herramientas de monitoreo:

-- MySQL: Verificar consultas lentas
SELECT
    DIGEST_TEXT as query,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT / 1000000000000 as avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- PostgreSQL: Verificar consultas lentas
SELECT query, calls, mean_exec_time / 1000 as avg_time_sec
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Paso 2: Analizar el Plan de Ejecución de Consulta

-- Ejemplo de consulta lenta
EXPLAIN ANALYZE
SELECT p.*, c.nombre, c.email, pr.nombre as nombre_producto
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
JOIN items_pedido ip ON p.id = ip.pedido_id
JOIN productos pr ON ip.producto_id = pr.id
WHERE p.estado = 'pendiente'
  AND p.fecha_creacion > NOW() - INTERVAL 30 DAY
  AND c.pais = 'ES'
ORDER BY p.fecha_creacion DESC
LIMIT 100;

Buscar:

  • Escaneos de tabla completa (type: ALL o Seq Scan)
  • Altos conteos de filas examinadas
  • Falta de uso de índices
  • Creación de tabla temporal
  • Operaciones filesort

Paso 3: Crear Índices Necesarios

Basado en análisis de EXPLAIN:

-- Índice para filtrado de pedidos
CREATE INDEX idx_pedidos_estado_fecha ON pedidos(estado, fecha_creacion);

-- Índice para filtro de país de cliente
CREATE INDEX idx_clientes_pais ON clientes(pais);

-- Índices para columnas JOIN (si no están presentes)
CREATE INDEX idx_pedidos_cliente_id ON pedidos(cliente_id);
CREATE INDEX idx_items_pedido_pedido_id ON items_pedido(pedido_id);
CREATE INDEX idx_items_pedido_producto_id ON items_pedido(producto_id);

Paso 4: Reescribir Consultas Ineficientes

Consulta lenta original:

-- Lenta: Subconsulta ejecutada para cada fila
SELECT *
FROM pedidos
WHERE cliente_id IN (
    SELECT id FROM clientes WHERE pais = 'ES'
)
AND estado = 'pendiente';

Versión optimizada:

-- Rápida: JOIN con índices apropiados
SELECT p.*
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
WHERE c.pais = 'ES'
  AND p.estado = 'pendiente';

Otro ejemplo - evitar funciones en columnas indexadas:

-- Lenta: La función previene uso de índice
SELECT * FROM pedidos
WHERE DATE(fecha_creacion) = '2024-01-15';

-- Rápida: La consulta de rango usa índice
SELECT * FROM pedidos
WHERE fecha_creacion >= '2024-01-15'
  AND fecha_creacion < '2024-01-16';

Paso 5: Optimizar Operaciones JOIN

Asegurar que columnas JOIN están indexadas:

-- Verificar índices en columnas JOIN
SHOW INDEX FROM pedidos WHERE Column_name = 'cliente_id';
SHOW INDEX FROM clientes WHERE Column_name = 'id';

-- Crear índices faltantes
CREATE INDEX idx_pedidos_cliente_id ON pedidos(cliente_id);

Usar tipos de JOIN apropiados:

-- INNER JOIN cuando necesitas filas coincidentes de ambas tablas
SELECT p.*, c.nombre
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id;

-- LEFT JOIN solo cuando necesitas todas las filas de la tabla izquierda
SELECT c.nombre, COUNT(p.id) as cuenta_pedidos
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id
GROUP BY c.id, c.nombre;

Paso 6: Limitar Conjuntos de Resultados

Siempre usar LIMIT cuando sea apropiado:

-- Agregar LIMIT para prevenir retornar millones de filas
SELECT * FROM pedidos
WHERE estado = 'pendiente'
ORDER BY fecha_creacion DESC
LIMIT 100;

-- Usar paginación para conjuntos de resultados grandes
SELECT * FROM pedidos
WHERE estado = 'pendiente'
ORDER BY fecha_creacion DESC
LIMIT 100 OFFSET 0;  -- Primera página

Paso 7: Usar Índices de Cobertura

Crear índices que incluyan todas las columnas de la consulta:

-- La consulta necesita id, cliente_id, estado, monto_total
CREATE INDEX idx_pedidos_cobertura ON pedidos(
    estado,
    fecha_creacion,
    cliente_id,
    monto_total
);

-- Ahora esta consulta puede usar escaneo solo de índice
SELECT cliente_id, estado, monto_total
FROM pedidos
WHERE estado = 'pendiente'
  AND fecha_creacion > '2024-01-01';

Paso 8: Optimizar Agregaciones

Usar indexación apropiada para GROUP BY:

-- Agregar índice para columnas GROUP BY
CREATE INDEX idx_pedidos_cliente_estado ON pedidos(cliente_id, estado);

-- Ahora esta consulta es mucho más rápida
SELECT cliente_id, estado, COUNT(*) as cuenta_pedidos
FROM pedidos
GROUP BY cliente_id, estado;

Usar tablas resumen para agregaciones complejas:

-- Crear tabla resumen
CREATE TABLE estadisticas_pedidos_diarias (
    fecha_estadistica DATE PRIMARY KEY,
    total_pedidos INT,
    monto_total DECIMAL(15,2),
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Actualizar resumen diariamente
INSERT INTO estadisticas_pedidos_diarias (fecha_estadistica, total_pedidos, monto_total)
SELECT DATE(fecha_creacion), COUNT(*), SUM(monto_total)
FROM pedidos
WHERE DATE(fecha_creacion) = CURDATE()
GROUP BY DATE(fecha_creacion)
ON DUPLICATE KEY UPDATE
    total_pedidos = VALUES(total_pedidos),
    monto_total = VALUES(monto_total),
    fecha_actualizacion = CURRENT_TIMESTAMP;

Paso 9: Evitar SELECT *

Seleccionar solo columnas necesarias:

-- Lenta: Retorna todas las columnas
SELECT * FROM pedidos WHERE cliente_id = 12345;

-- Rápida: Retorna solo columnas necesarias
SELECT id, numero_pedido, estado, monto_total, fecha_creacion
FROM pedidos
WHERE cliente_id = 12345;

Paso 10: Actualizar Estadísticas

Mantener estadísticas actualizadas para el optimizador:

-- MySQL: Analizar tablas
ANALYZE TABLE pedidos, clientes, productos;

-- PostgreSQL: Analizar tablas
ANALYZE pedidos;
ANALYZE clientes;
ANALYZE productos;

-- O analizar base de datos completa
ANALYZE;

Técnicas de Optimización Avanzadas

Caché de Consultas

Caché de consultas de MySQL (obsoleto en MySQL 8.0):

-- Verificar si el caché de consultas está habilitado (MySQL 5.7)
SHOW VARIABLES LIKE 'query_cache%';

-- Para MySQL 8.0+, usar caché a nivel de aplicación (Redis, Memcached)

Sentencias preparadas de PostgreSQL:

-- Preparar sentencia para reutilización
PREPARE busqueda_pedido (int) AS
    SELECT * FROM pedidos WHERE id = $1;

-- Ejecutar sentencia preparada
EXECUTE busqueda_pedido(12345);

-- Liberar cuando termine
DEALLOCATE busqueda_pedido;

Particionamiento de Tablas Grandes

Particionamiento de tablas MySQL:

-- Particionar pedidos por rango de fecha
CREATE TABLE pedidos (
    id INT NOT NULL AUTO_INCREMENT,
    cliente_id INT,
    estado VARCHAR(20),
    monto_total DECIMAL(10,2),
    fecha_creacion DATETIME,
    PRIMARY KEY (id, fecha_creacion)
)
PARTITION BY RANGE (YEAR(fecha_creacion)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_futuro VALUES LESS THAN MAXVALUE
);

Particionamiento declarativo de PostgreSQL:

-- Crear tabla particionada
CREATE TABLE pedidos (
    id SERIAL,
    cliente_id INT,
    estado VARCHAR(20),
    monto_total DECIMAL(10,2),
    fecha_creacion TIMESTAMP
) PARTITION BY RANGE (fecha_creacion);

-- Crear particiones
CREATE TABLE pedidos_2024_q1 PARTITION OF pedidos
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE pedidos_2024_q2 PARTITION OF pedidos
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Crear índices en particiones
CREATE INDEX idx_pedidos_2024_q1_cliente ON pedidos_2024_q1(cliente_id);
CREATE INDEX idx_pedidos_2024_q2_cliente ON pedidos_2024_q2(cliente_id);

Vistas Materializadas

Vistas materializadas de PostgreSQL:

-- Crear vista materializada para agregación compleja
CREATE MATERIALIZED VIEW resumen_pedidos_cliente AS
SELECT
    c.id,
    c.nombre,
    c.email,
    COUNT(p.id) as total_pedidos,
    SUM(p.monto_total) as total_gastado,
    MAX(p.fecha_creacion) as fecha_ultimo_pedido
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id
GROUP BY c.id, c.nombre, c.email;

-- Crear índice en vista materializada
CREATE INDEX idx_resumen_cliente_id ON resumen_pedidos_cliente(id);

-- Consultar vista materializada (muy rápido)
SELECT * FROM resumen_pedidos_cliente WHERE id = 12345;

-- Refrescar vista materializada
REFRESH MATERIALIZED VIEW resumen_pedidos_cliente;

-- Refrescar sin bloquear (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY resumen_pedidos_cliente;

Hints de Optimización de Consultas

Hints de optimizador MySQL (8.0+):

-- Forzar uso de índice
SELECT /*+ INDEX(pedidos idx_pedidos_estado) */
    * FROM pedidos
WHERE estado = 'pendiente';

-- Forzar orden de JOIN
SELECT /*+ JOIN_ORDER(p, c) */
    p.*, c.nombre
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id;

-- Usar algoritmo JOIN específico
SELECT /*+ HASH_JOIN(p, c) */
    p.*, c.nombre
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id;

Monitoreo y Mantenimiento

Crear Script de Monitoreo de Rendimiento

Script de monitoreo MySQL:

#!/bin/bash
# Monitor de Rendimiento MySQL

mysql -e "
SELECT 'Consultas Lentas' as Metrica, variable_value as Valor
FROM performance_schema.global_status WHERE variable_name = 'Slow_queries'
UNION ALL
SELECT 'Preguntas', variable_value
FROM performance_schema.global_status WHERE variable_name = 'Questions'
UNION ALL
SELECT 'Hilos Conectados', variable_value
FROM performance_schema.global_status WHERE variable_name = 'Threads_connected'
UNION ALL
SELECT 'Escaneos de Tabla', variable_value
FROM performance_schema.global_status WHERE variable_name = 'Select_scan';

SELECT
    SUBSTRING(DIGEST_TEXT, 1, 80) as consulta,
    COUNT_STAR as ejecuciones,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) as avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 5;
"

Script de monitoreo PostgreSQL:

#!/bin/bash
# Monitor de Rendimiento PostgreSQL

sudo -u postgres psql << 'EOF'
\echo '=== Estadísticas de Base de Datos ==='
SELECT
    'Conexiones Activas' as metrica,
    COUNT(*) as valor
FROM pg_stat_activity
WHERE state = 'active'
UNION ALL
SELECT
    'Ratio Hit de Caché',
    ROUND(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit + blks_read), 0), 2)
FROM pg_stat_database;

\echo ''
\echo '=== Consultas Más Lentas ==='
SELECT
    SUBSTRING(query, 1, 60) as consulta,
    calls,
    ROUND(mean_exec_time::numeric, 2) as avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 5;
EOF

Tareas de Mantenimiento Regular

Programar mantenimiento regular de base de datos:

# Script de mantenimiento MySQL
#!/bin/bash
echo "Iniciando mantenimiento MySQL: $(date)"

# Optimizar tablas
mysql -e "OPTIMIZE TABLE pedidos, clientes, productos;"

# Actualizar estadísticas
mysql -e "ANALYZE TABLE pedidos, clientes, productos;"

# Verificar índices no usados
mysql << 'EOF'
SELECT
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'performance_schema');
EOF

echo "Mantenimiento MySQL completado: $(date)"

# Script de mantenimiento PostgreSQL
#!/bin/bash
echo "Iniciando mantenimiento PostgreSQL: $(date)"

# Vacuum y analyze
sudo -u postgres psql << 'EOF'
VACUUM ANALYZE pedidos;
VACUUM ANALYZE clientes;
VACUUM ANALYZE productos;

-- Verificar tablas infladas
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as tamaño
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Verificar índices no usados
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey';
EOF

echo "Mantenimiento PostgreSQL completado: $(date)"

Solución de Problemas

Alto Uso de CPU

Problema: Base de datos consumiendo CPU excesiva

Solución: Identificar consultas intensivas de CPU:

-- MySQL
SELECT
    DIGEST_TEXT as consulta,
    COUNT_STAR as cuenta_ejec,
    SUM_TIMER_WAIT / 1000000000000 as total_tiempo_seg
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- PostgreSQL
SELECT query, calls, total_exec_time / 1000 as total_seg
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Rendimiento Lento de JOIN

Problema: Consultas con JOINs son muy lentas

Solución: Verificar y crear índices en columnas JOIN:

-- Verificar que existen índices
SHOW INDEX FROM pedidos WHERE Column_name = 'cliente_id';

-- Crear índices faltantes
CREATE INDEX idx_pedidos_cliente_id ON pedidos(cliente_id);
CREATE INDEX idx_items_pedido_pedido_id ON items_pedido(pedido_id);

-- Usar EXPLAIN para verificar uso de índice
EXPLAIN SELECT p.*, c.nombre
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id;

Contención de Bloqueos de Tabla

Problema: Consultas esperando bloqueos de tabla

Solución: Identificar y resolver bloqueos:

-- MySQL: Mostrar tablas bloqueadas
SHOW OPEN TABLES WHERE In_use > 0;

-- Mostrar processlist para consultas bloqueantes
SHOW FULL PROCESSLIST;

-- Matar consulta bloqueante
KILL <process_id>;

-- PostgreSQL: Mostrar bloqueos
SELECT * FROM pg_locks WHERE NOT granted;

-- Encontrar consultas bloqueantes
SELECT
    blocked_activity.pid AS pid_bloqueado,
    blocked_activity.query AS consulta_bloqueada,
    blocking_activity.pid AS pid_bloqueante,
    blocking_activity.query AS consulta_bloqueante
FROM pg_stat_activity AS blocked_activity
JOIN pg_stat_activity AS blocking_activity
    ON blocking_activity.pid = ANY(pg_blocking_pids(blocked_activity.pid));

Detección de Índices Faltantes

Problema: Inseguro de qué índices crear

Solución: Identificar índices faltantes:

-- MySQL: Consultas no usando índices
SELECT
    DIGEST_TEXT,
    SUM_NO_INDEX_USED,
    SUM_NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC;

-- PostgreSQL: Escaneos secuenciales en tablas grandes
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan as lectura_seq_promedio
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

Resumen de Mejores Prácticas

1. Indexar Estratégicamente

  • Crear índices en claves foráneas y columnas JOIN
  • Indexar columnas en cláusulas WHERE, ORDER BY y GROUP BY
  • Usar índices compuestos para consultas multi-columna
  • No sobre-indexar - cada índice tiene sobrecarga de escritura

2. Escribir Consultas Eficientes

  • Seleccionar solo columnas necesarias (evitar SELECT *)
  • Usar LIMIT para restringir conjuntos de resultados
  • Evitar funciones en columnas indexadas en WHERE
  • Usar JOINs en lugar de subconsultas cuando sea posible

3. Monitorear Continuamente

  • Habilitar y revisar logs de consultas lentas
  • Usar Performance Schema / pg_stat_statements
  • Rastrear tendencias de tiempo de ejecución de consultas
  • Configurar alertas para degradación de rendimiento

4. Mantenimiento Regular

  • Actualizar estadísticas de tabla semanalmente
  • Optimizar/vacuum tablas mensualmente
  • Revisar y remover índices no usados
  • Archivar datos antiguos para mantener tablas manejables

5. Probar Antes de Producción

  • Probar todas las optimizaciones en entorno de staging
  • Usar volúmenes de datos similares a producción
  • Hacer benchmark de mejoras de rendimiento
  • Planear procedimientos de rollback

6. Documentar Cambios

  • Registrar todas las creaciones de índices
  • Documentar optimizaciones de consultas
  • Mantener métricas de rendimiento base
  • Rastrear cambios de esquema

Conclusión

La optimización de consultas es un proceso continuo que requiere monitoreo, análisis y refinamiento constantes. Siguiendo esta guía completa, has aprendido cómo identificar consultas lentas usando varias herramientas de monitoreo, analizar planes de ejecución de consultas para entender cuellos de botella de rendimiento, crear índices efectivos que mejoran dramáticamente el rendimiento de consultas, reescribir consultas para mejor eficiencia, implementar técnicas avanzadas de optimización y mantener rendimiento óptimo de base de datos en el tiempo.

La clave para mantener bases de datos de alto rendimiento es establecer un enfoque sistemático para optimización de consultas, monitorear regularmente el rendimiento de consultas y uso de recursos, identificar y abordar proactivamente cuellos de botella antes de que impacten a los usuarios, mantener estadísticas actualizadas e índices bien diseñados y revisar y optimizar continuamente conforme los volúmenes de datos y patrones de acceso cambian.

Recuerda que la optimización prematura puede ser contraproducente. Enfócate en optimizar consultas que tengan el mayor impacto en la experiencia del usuario o recursos del sistema. Usa datos de rendimiento reales para guiar esfuerzos de optimización, prueba todos los cambios exhaustivamente antes de desplegar a producción, documenta tus optimizaciones y su impacto y mantén un balance entre rendimiento de lectura y escritura.

A medida que tu base de datos crece y evoluciona, revisa tus estrategias de optimización regularmente. Nuevos patrones de datos pueden requerir diferentes índices, volúmenes de datos en crecimiento pueden necesitar particionamiento, patrones de acceso cambiantes pueden hacer obsoletas optimizaciones existentes y nuevas características de base de datos pueden ofrecer mejores soluciones. Mantente informado sobre mejores prácticas de bases de datos y nuevas características, continúa midiendo y monitoreando rendimiento y mantén una cultura de conciencia de rendimiento en todo tu equipo de desarrollo. Con la base construida desde esta guía, estás bien equipado para mantener sistemas de base de datos rápidos y eficientes que escalan con las necesidades de tu aplicación.