Optimización y configuración

El sitio de tu cliente iba perfecto... hasta que creció. Ahora el listado de pedidos tarda 8 segundos y el servidor se queda sin memoria en las horas pico. En esta lección aprenderás el método para optimizar MySQL: medir qué es lento (slow query log, EXPLAIN), arreglar las consultas e índices, y ajustar los parámetros de configuración que más impacto tienen.

Concepto

Optimizar es como ir al médico: primero diagnóstico, luego tratamiento. Cambiar parámetros al azar "porque lo leí en un foro" es automedicarse.

El orden de impacto, de mayor a menor:

1. Diseño del esquema y tipos de datos      ████████████████████  (lo más importante)
2. Consultas e índices                      ██████████████████
3. Configuración del servidor (my.cnf)      ████████
4. Hardware (más RAM, discos SSD/NVMe)      ██████

Un índice correcto puede hacer una consulta 1000 veces más rápida; ajustar un parámetro, quizá 20 %.

Paso 1: encontrar las consultas lentas

Slow query log

MySQL puede registrar todas las consultas que tarden más de cierto tiempo:

-- Activarlo en caliente (se pierde al reiniciar)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;                     -- segundos (acepta decimales: 0.5)
SET GLOBAL log_queries_not_using_indexes = 'ON';    -- opcional, genera mucho ruido
SHOW VARIABLES LIKE 'slow_query_log_file';

O de forma permanente en la configuración:

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

Una entrada del log:

# Time: 2024-07-15T18:22:41.123456Z
# User@Host: tienda_app[tienda_app] @ localhost []
# Query_time: 4.812  Lock_time: 0.000 Rows_sent: 20  Rows_examined: 2450011
SET timestamp=1721067761;
SELECT * FROM pedidos WHERE YEAR(fecha) = 2024 AND estado = 'pagado' ORDER BY fecha DESC LIMIT 20;

La pista clave: Rows_examined: 2,450,011 para devolver 20 filas. Falta un índice o la consulta impide usarlo.

Resumir el log

# Herramienta incluida con MySQL: agrupa consultas similares
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log   # top 10 por tiempo total

# Percona Toolkit (más detallado)
pt-query-digest /var/log/mysql/mysql-slow.log

Desde performance_schema / sys

Sin activar el log, MySQL 8 ya acumula estadísticas:

-- Top 10 consultas por tiempo total
SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

-- Tablas que se leen completas (sin índice)
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- Índices que nunca se usan (candidatos a eliminar)
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'tienda';

Paso 2: analizar con EXPLAIN

EXPLAIN
SELECT * FROM pedidos WHERE YEAR(fecha) = 2024 AND estado = 'pagado'
ORDER BY fecha DESC LIMIT 20;

id	select_type	table	type	possible_keys	key	rows	filtered	Extra
1	SIMPLE	pedidos	ALL	NULL	NULL	2450011	1.00	Using where; Using filesort

Diagnóstico: type = ALL (escaneo completo), key = NULL (sin índice), Using filesort (ordena en memoria/disco).

Arreglo

-- 1. No aplicar funciones a la columna: rango en su lugar
-- 2. Índice compuesto: igualdad primero (estado), luego rango/orden (fecha)
CREATE INDEX idx_pedidos_estado_fecha ON pedidos (estado, fecha);

EXPLAIN
SELECT * FROM pedidos
WHERE estado = 'pagado'
  AND fecha >= '2024-01-01' AND fecha < '2025-01-01'
ORDER BY fecha DESC
LIMIT 20;

id	select_type	table	type	possible_keys	key	rows	filtered	Extra
1	SIMPLE	pedidos	range	idx_pedidos_estado_fecha	idx_pedidos_estado_fecha	20	100.00	Using index condition; Backward index scan

De 2.4 millones de filas examinadas a 20. De 4.8 s a 2 ms.

EXPLAIN ANALYZE y FORMAT=TREE

-- Tiempos reales (ejecuta la consulta)
EXPLAIN ANALYZE
SELECT c.nombre, COUNT(*) FROM pedidos p JOIN clientes c ON c.id = p.cliente_id GROUP BY c.nombre;

-> Table scan on <temporary>  (actual time=0.9..0.9 rows=4 loops=1)
    -> Aggregate using temporary table  (actual time=0.9..0.9 rows=4 loops=1)
        -> Nested loop inner join  (cost=2.25 rows=5) (actual time=0.05..0.08 rows=5 loops=1)
            -> Table scan on p  (cost=0.75 rows=5) (actual time=0.03..0.04 rows=5 loops=1)
            -> Single-row index lookup on c using PRIMARY (id=p.cliente_id) (actual time=0.005..0.005 rows=1 loops=5)

Se lee de adentro hacia afuera. Busca nodos con rows o actual time desproporcionados. Repasa la teoría en Índices y rendimiento.

Paso 3: buenas prácticas de esquema y consultas

Práctica	Por qué
Tipos lo más pequeños posible (INT vs BIGINT, VARCHAR(100) vs TEXT)	Más filas por página en memoria
PK INT/BIGINT UNSIGNED AUTO_INCREMENT	InnoDB ordena por la PK
Índices en FK y columnas de WHERE / ORDER BY frecuentes	Evitan escaneos completos
Evitar SELECT *	Menos datos, permite índices cubrientes
Paginación por cursor en tablas grandes	OFFSET grande es lento
Insertar en lotes (INSERT ... VALUES (...), (...), ...)	1 viaje en lugar de 1000
Evitar el problema N+1 en el ORM	Menos consultas por página
Guardar archivos (imágenes, PDFs) fuera de la BD	La BD no es un disco duro
Archivar datos viejos (logs, pedidos de hace años)	Tablas más pequeñas, índices más rápidos

-- Mantener estadísticas al día tras cargas masivas
ANALYZE TABLE pedidos;

-- Recuperar espacio tras borrar muchas filas (reconstruye la tabla; hazlo en horario tranquilo)
OPTIMIZE TABLE logs;

Paso 4: configuración del servidor

¿Dónde está el archivo?

Sistema	Ruta típica
Ubuntu/Debian	/etc/mysql/mysql.conf.d/mysqld.cnf (o /etc/mysql/my.cnf)
CentOS/RHEL	/etc/my.cnf
Windows	C:\ProgramData\MySQL\MySQL Server 8.4\my.ini
XAMPP	C:\xampp\mysql\bin\my.ini
Docker	Monta tu archivo en /etc/mysql/conf.d/

# ¿Qué archivos lee mysqld y en qué orden?
mysqld --verbose --help | grep -A1 "Default options"

-- Ver el valor actual de cualquier variable
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT @@max_connections;

Los parámetros que más importan

[mysqld]
# ---------- Memoria ----------
# El más importante: caché de datos e índices de InnoDB.
# Servidor dedicado a MySQL: 50–70 % de la RAM. Compartido con web/PHP: 25–40 %.
innodb_buffer_pool_size = 2G

# Tamaño total del redo log (MySQL 8.0.30+). Más grande = escrituras más fluidas.
innodb_redo_log_capacity = 1G

# ---------- Conexiones ----------
max_connections = 200          # por defecto 151; cada conexión consume memoria
wait_timeout = 300             # cierra conexiones inactivas tras 5 min

# ---------- Durabilidad vs velocidad ----------
# 1 = máxima seguridad (escribe a disco en cada COMMIT). Déjalo en 1 en producción.
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT  # Linux: evita doble caché con el sistema operativo

# ---------- Tablas temporales ----------
tmp_table_size = 64M
max_heap_table_size = 64M

# ---------- Charset ----------
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

# ---------- Logs ----------
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_error = /var/log/mysql/error.log

# ---------- Seguridad ----------
bind-address = 127.0.0.1
local_infile = 0

sudo systemctl restart mysql
sudo tail -n 50 /var/log/mysql/error.log    # confirma que arrancó sin errores

Más no siempre es mejor

• Un innodb_buffer_pool_size mayor que la RAM disponible hace que el sistema use swap y todo se vuelva más lento (o que Linux mate a MySQL por falta de memoria).
• max_connections = 5000 no hace tu sitio más rápido: si hay tantas conexiones, el problema está en la app (conexiones que no se cierran, falta de pool).
• Desconfía de "configuraciones mágicas". Cambia un parámetro a la vez y mide.

Calcular el buffer pool

VPS de 4 GB con Nginx + PHP-FPM + MySQL:
  Sistema operativo     ~0.5 GB
  PHP-FPM (20 procesos) ~1.0 GB
  Nginx, cron, etc.     ~0.3 GB
  ───────────────────────────
  Disponible para MySQL ~2.0 GB → innodb_buffer_pool_size = 1G a 1.5G

Si tus datos + índices caben completos en el buffer pool, casi todas las lecturas salen de RAM:

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mb_totales
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

Docker: configuración propia

services:
  db:
    image: mysql:8.4
    volumes:
      - db_datos:/var/lib/mysql
      - ./mysql/mi.cnf:/etc/mysql/conf.d/mi.cnf:ro
volumes:
  db_datos:

Herramientas de diagnóstico

# MySQLTuner: analiza el servidor en marcha y sugiere ajustes
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass

# Ver en vivo qué está ejecutando MySQL
mysqladmin -u root -p processlist
mysqladmin -u root -p extended-status | grep -E "Threads_connected|Slow_queries|Questions"

-- Consultas en ejecución ahora mismo (y cuánto llevan)
SELECT id, user, db, time, state, LEFT(info, 80) AS consulta
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

-- Matar una consulta que se quedó colgada
KILL 1234;

Ejecuta MySQLTuner después de al menos 24 horas de uso real

Sus recomendaciones se basan en estadísticas acumuladas desde el último reinicio. Justo después de reiniciar, sus sugerencias no son confiables.

Caso real: el listado lento del panel

Síntoma: el panel de administración tarda 6 s en mostrar "pedidos recientes con nombre del cliente".

-- Consulta original (generada por el ORM, con N+1 resuelto)
SELECT p.*, c.nombre, c.email
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.estado IN ('pendiente', 'pagado')
ORDER BY p.creado_en DESC
LIMIT 50 OFFSET 0;

1. EXPLAIN muestra type: ALL en pedidos y Using filesort.
2. Se crea CREATE INDEX idx_pedidos_estado_creado ON pedidos (estado, creado_en);
3. EXPLAIN ahora muestra range sobre el índice; la consulta baja a 15 ms.
4. Se cambia SELECT p.* por las 6 columnas que realmente muestra la tabla del panel.
5. Para páginas profundas se cambia OFFSET por paginación con cursor (AND p.creado_en < :ultimo).

Ejercicios

1. Activa el slow query log con long_query_time = 0.5 y revisa dónde se guarda el archivo.
2. ¿Qué índice crearías para SELECT id, total FROM pedidos WHERE cliente_id = ? ORDER BY fecha DESC LIMIT 10;? ¿Cómo lo harías "cubriente"?
3. Tienes un VPS de 8 GB dedicado solo a MySQL. ¿Qué valor aproximado de innodb_buffer_pool_size usarías?
4. En SHOW PROCESSLIST ves 140 conexiones en estado Sleep con max_connections = 151. ¿Qué indica y qué harías?

Ver solución

-- 1
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SHOW VARIABLES LIKE 'slow_query_log_file';

2. CREATE INDEX idx_pedidos_cliente_fecha ON pedidos (cliente_id, fecha); Para que sea cubriente, incluye también total (el id ya va incluido por ser la PK): CREATE INDEX idx_pedidos_cliente_fecha_total ON pedidos (cliente_id, fecha, total); → EXPLAIN mostrará Using index.
3. Entre 5 y 6 GB (60–75 % de la RAM), dejando memoria para el sistema y las conexiones.
4. Que la aplicación abre conexiones y no las cierra (o tiene un pool demasiado grande). Pronto aparecerá Too many connections. Revisa el código (cerrar conexiones, usar un pool con límite razonable), baja wait_timeout para liberar las inactivas y solo como último recurso sube max_connections.

Resumen

• Optimiza en orden: esquema → consultas e índices → configuración → hardware.
• Encuentra lo lento con el slow query log, mysqldumpslow y sys.statement_analysis.
• Usa EXPLAIN / EXPLAIN ANALYZE: evita type: ALL, Using filesort y Rows_examined enormes.
• El parámetro clave es innodb_buffer_pool_size (50–70 % de la RAM si el servidor es dedicado).
• Cambia un parámetro a la vez, mide, y apóyate en MySQLTuner con datos de uso real.