OPTIMIZE TABLE

Cuando eliminas o actualizas muchas filas de una tabla, MySQL no siempre libera el espacio en disco de inmediato. Los huecos dejados por las filas eliminadas permanecen en el archivo de datos, fragmentando la tabla y desperdiciando almacenamiento. La sentencia OPTIMIZE TABLE reorganiza el almacenamiento físico de la tabla, recupera el espacio no utilizado y actualiza las estadísticas de índices.

Sintaxis

OPTIMIZE TABLE nombre_tabla [, nombre_tabla2, ...];

Puedes optimizar una o varias tablas en la misma sentencia. La operación reconstruye la tabla y sus índices, lo que puede tomar un tiempo considerable en tablas grandes.

Cuándo optimizar

La fragmentación de una tabla ocurre de manera gradual con las operaciones normales de escritura, pero hay situaciones donde se acumula de forma significativa.

Después de eliminar una gran cantidad de registros es el caso más claro. Si borras el 50% de las filas de una tabla con un millón de registros, el archivo de datos sigue ocupando prácticamente el mismo espacio que antes. OPTIMIZE TABLE compacta ese espacio.

Las actualizaciones de columnas de longitud variable (VARCHAR, TEXT, BLOB) también generan fragmentación. Si una fila almacenaba un texto de 100 caracteres y lo actualizas a 500, MySQL puede mover la fila a una nueva ubicación y dejar un hueco en la original.

-- Ejemplo: eliminar pedidos antiguos
DELETE FROM pedidos
WHERE fecha_pedido < '2023-01-01';
-- Query OK, 45000 rows affected
 
-- Recuperar el espacio
OPTIMIZE TABLE pedidos;

Ejemplo práctico

Veamos el proceso completo de verificar la fragmentación y optimizar:

-- Verificar el tamaño actual de la tabla
SELECT
    table_name AS tabla,
    ROUND(data_length / 1024 / 1024, 2) AS datos_mb,
    ROUND(data_free / 1024 / 1024, 2) AS espacio_libre_mb,
    ROUND(index_length / 1024 / 1024, 2) AS indices_mb
FROM information_schema.tables
WHERE table_schema = 'tienda_mysql'
AND table_name = 'pedidos';

La columna espacio_libre_mb indica el espacio fragmentado que se puede recuperar. En este caso, hay 4.80 MB que no están siendo utilizados dentro del archivo de datos.

OPTIMIZE TABLE pedidos;

-- Verificar después de optimizar
SELECT
    table_name AS tabla,
    ROUND(data_length / 1024 / 1024, 2) AS datos_mb,
    ROUND(data_free / 1024 / 1024, 2) AS espacio_libre_mb,
    ROUND(index_length / 1024 / 1024, 2) AS indices_mb
FROM information_schema.tables
WHERE table_schema = 'tienda_mysql'
AND table_name = 'pedidos';

El espacio libre se ha reducido a cero y el tamaño total de datos refleja solo las filas existentes. Incluso los índices se han compactado ligeramente.

Comportamiento en InnoDB

Para tablas InnoDB, OPTIMIZE TABLE no realiza una optimización in-place. Internamente, MySQL ejecuta un ALTER TABLE ... FORCE que recrea la tabla completa. El resultado que verás incluye una nota informativa:

OPTIMIZE TABLE productos;

La nota Table does not support optimize, doing recreate + analyze instead no es un error. Significa que MySQL ha recreado la tabla por completo y luego ha actualizado las estadísticas. El efecto final es el mismo: espacio recuperado y datos desfragmentados.

Este proceso de recreación implica que MySQL necesita espacio temporal en disco equivalente al tamaño de la tabla. Si tu disco está casi lleno, ten precaución al optimizar tablas grandes.

Comportamiento en MyISAM

En tablas MyISAM, OPTIMIZE TABLE realiza las siguientes acciones: repara la tabla si tiene filas eliminadas o partidas, ordena las filas del índice si no están ya ordenadas, actualiza las estadísticas de índices si están desactualizadas, y compacta el archivo de datos eliminando los huecos.

Para MyISAM, la operación es más directa y generalmente más rápida que el proceso de recreación que hace InnoDB.

Implicaciones de bloqueo

Durante la ejecución de OPTIMIZE TABLE, la tabla se bloquea completamente. Ni lecturas ni escrituras pueden acceder a la tabla hasta que la operación termine. Esto es importante para entornos de producción con tráfico constante.

Para tablas InnoDB, la recreación usa el algoritmo Online DDL cuando es posible, lo que permite algunas operaciones concurrentes. Sin embargo, es prudente ejecutar la optimización durante ventanas de mantenimiento.

-- Estimar cuánto tardará revisando el tamaño
SELECT
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS tamano_total_mb
FROM information_schema.tables
WHERE table_schema = 'tienda_mysql'
ORDER BY (data_length + index_length) DESC
LIMIT 5;

Tablas más grandes tardarán proporcionalmente más en optimizarse.

Optimizar múltiples tablas

Puedes optimizar varias tablas a la vez:

OPTIMIZE TABLE pedidos, detalle_pedidos, clientes;

MySQL procesa cada tabla secuencialmente. Si necesitas optimizar todas las tablas de una base de datos, considera usar la herramienta mysqlcheck desde la línea de comandos:

mysqlcheck -o tienda_mysql -u root -p

Encontrar tablas que necesitan optimización

Esta consulta identifica las tablas con mayor fragmentación en tu base de datos:

SELECT
    table_name AS tabla,
    engine AS motor,
    ROUND(data_length / 1024 / 1024, 2) AS datos_mb,
    ROUND(data_free / 1024 / 1024, 2) AS fragmentado_mb,
    ROUND(data_free / data_length * 100, 1) AS porcentaje_fragmentado
FROM information_schema.tables
WHERE table_schema = 'tienda_mysql'
AND data_length > 0
AND data_free > 0
ORDER BY data_free DESC;

Como regla general, si el porcentaje de fragmentación supera el 20-30%, vale la pena ejecutar OPTIMIZE TABLE.

Alternativa con ALTER TABLE

Si por alguna razón OPTIMIZE TABLE no funciona como esperas con InnoDB, puedes lograr el mismo efecto con ALTER TABLE:

ALTER TABLE pedidos ENGINE=InnoDB;

Esta sentencia recrea la tabla completa con el motor InnoDB, produciendo exactamente el mismo resultado que OPTIMIZE TABLE para tablas InnoDB. Es un truco útil en situaciones donde necesitas más control sobre el proceso.

Automatización

Para automatizar la optimización periódica, puedes crear un evento programado:

CREATE EVENT optimizar_tablas_mensuales
ON SCHEDULE EVERY 1 MONTH
STARTS '2026-03-01 04:00:00'
DO
BEGIN
    OPTIMIZE TABLE pedidos;
    OPTIMIZE TABLE detalle_pedidos;
    OPTIMIZE TABLE log_actividad;
END;

También puedes usar cron desde el sistema operativo:

# Agregar al crontab: optimizar el primer domingo de cada mes a las 4 AM
0 4 1-7 * 0 mysqlcheck -o tienda_mysql -u admin -p'contraseña' 2>&1 | logger -t mysql-optimize

Cuándo NO optimizar

No necesitas optimizar tablas que solo reciben inserciones y rara vez eliminan datos, como tablas de log o auditoría. Tampoco tiene sentido optimizar tablas pequeñas donde la fragmentación es insignificante. Ejecutar OPTIMIZE TABLE en una tabla sin fragmentación simplemente la recrea sin beneficio, consumiendo tiempo y recursos de E/S.

La sentencia OPTIMIZE TABLE es una herramienta de mantenimiento periódico que complementa a ANALYZE TABLE. Mientras ANALYZE TABLE actualiza las estadísticas, OPTIMIZE TABLE reorganiza los datos físicamente. En el siguiente artículo veremos mysqlcheck, una herramienta de línea de comandos que combina verificación, reparación y optimización en una sola utilidad.