Optimización de Rendimiento en MySQL

Índices y Orden de Columnas

-- Crear índice compuesto (la columna más selectiva primero)
CREATE INDEX idx_pedidos_fecha_cliente ON pedidos(creado_en, cliente_id, total);

-- Índice de cobertura: la consulta usa solo el índice
SELECT cliente_id, SUM(total)
FROM pedidos
WHERE creado_en >= '2024-01-01'
GROUP BY cliente_id;

-- Eliminar índices duplicados o sin uso
DROP INDEX idx_viejo ON pedidos;

EXPLAIN y Análisis de Consultas

EXPLAIN FORMAT=JSON
SELECT o.id, c.nombre, SUM(oi.cantidad * oi.precio)
FROM pedidos o
JOIN clientes c ON o.cliente_id = c.id
JOIN detalle_pedido oi ON o.id = oi.pedido_id
WHERE o.creado_en >= '2024-01-01'
GROUP BY o.id, c.nombre;

Valores clave de EXPLAIN:

Registro de Consultas Lentas

[mysqld]
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow.log
long_query_time         = 0.5
log_queries_not_using_indexes = 1

# Analizar el log con mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Buffer Pool de InnoDB

[mysqld]
# 70-80% de la RAM en servidores dedicados
innodb_buffer_pool_size     = 8G
innodb_buffer_pool_instances = 8

-- Verificar hit ratio (debe ser > 99%)
SELECT
    FORMAT((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2) AS hit_ratio
FROM (
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

Consultas del Performance Schema

-- Top 10 consultas más lentas
SELECT
    DIGEST_TEXT,
    COUNT_STAR            AS ejecuciones,
    AVG_TIMER_WAIT/1e9    AS avg_ms,
    MAX_TIMER_WAIT/1e9    AS max_ms,
    SUM_ROWS_EXAMINED     AS filas_examinadas
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Índices sin uso
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 = 'myapp';

Mantenimiento de Tablas

-- Actualizar estadísticas del optimizador
ANALYZE TABLE pedidos;
ANALYZE TABLE productos;

-- Reconstruir tabla y recuperar espacio
OPTIMIZE TABLE pedidos;

-- Verificar integridad
CHECK TABLE pedidos;

Pool de Conexiones

[mysqld]
max_connections       = 500
thread_cache_size     = 50
wait_timeout          = 600
interactive_timeout   = 600