Con MySQL, cuando se eliminan registros de una tabla, el espacio no se reasigna automáticamente. Queda como espacio vacío y cuando se realizan nuevas inserciones se va aprovechando.
El problema de esto es que si en una tabla se realizan muchas operaciones de DELETE, el espacio físico de la tabla va quedando cada vez más fragmentado y el rendimiento se reduce.
En los motores MyISAM e InnoDB de MySQL, disponemos del comando OPTIMIZE TABLE para poder realizar sobre cualquier tabla una optimización que, entre otras cosas, realiza una defragmentación automática de la tabla.
Es muy recomendable utilizar este comando regularmente sobretodo sobre las tablas que reciben más sentencias de eliminación de registros.
Como precaución, tener en cuenta que durante su ejecución, como es lógico, la tabla queda bloqueda. Hay que acordarse cuando lo vayamos a utilizar con tablas grandes y con mucho movimiento.
La sintaxis es supersimple:
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;
Para realizar una optimización de tablas fragmentadas se pueden seleccionar las que tengan espacio libre, seguramente a consecuencia de sentencias de DELETE:
SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND
Data_free > 0
He encontrado este sencillo script que utiliza esta sentencia para defragmentar las tablas que tengan 'huecos'.
#!/bin/bash
# Get a list of all fragmented tables
FRAGMENTED_TABLES="$( mysql -e 'use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME \
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND \
Data_free > 0' | grep -v "^+" | sed "s,\t,.," )"
for fragment in $FRAGMENTED_TABLES; do
database="$( echo $fragment | cut -d. -f1 )"
table="$( echo $fragment | cut -d. -f2 )"
[ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] && mysql -e "USE $database;\
OPTIMIZE TABLE $table;" > /dev/null 2>&1
done
Enlazo también un buen artículo que he encontrado sobre fragmentación de datos en bases de datos, con ejemplos de PostgreSQL, pero con conceptos aplicables a la mayoría de BBDD relacionales:
