Amb MySQL, quan s'eliminen registres d'una taula, l'espai no es reassignació automàticament. Queda com a espai buit que es realitzen noves insercions es va aprofitant.
El problema d'això és que si en una taula es realitzen moltes operacions de DELETE, l'espai físic de la taula va quedant cada vegada més fragmentat i el rendiment es redueix.
En els motors MyISAM i InnoDB de MySQL, disposem de la comanda OPTIMITZAR TABLE per poder realitzar sobre qualsevol taula una optimització que, entre altres coses, fa una defragmentació automàtica de la taula.
És molt recomanable utilitzar aquesta comanda regularment sobretot sobre les taules que reben més sentències d'eliminació de registres.
Com a precaució, tenir en compte que durant la seva execució, com és lògic, la taula queda bloquejada. Cal acordar quan ho anem a utilitzar amb taules grans i amb molt moviment.
La sintaxi és supersimple:
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;
Per realitzar una optimització de taules fragmentades es poden seleccionar les que tinguin espai lliure, segurament a conseqüència de sentències de DELETE:
SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND
Data_free > 0
En el post Defragmenting all fragmented MyISAM tables de Chistian's Blog he trobat aquest senzill script que utilitza aquesta sentència per compactar les taules que tinguin 'buits'.
#!/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
Enllaço també un bon article que he trobat sobre fragmentació de dades en bases de dades, amb exemples de PostgreSQL, però amb conceptes aplicables a la majoria de BBDD relacionals:
