Cómo controlar y reducir la fragmentación de tablas MySQL consultando information_schema

La fragmentación tiene lugar sobretodo en tablas donde hay mucho movimiento insert/delete. Este crece mucho cuando el volumen de datos de la tabla es muy variable en el tiempo: por ejemplo en tablas de control de transacciones, de logueos de usuarios, de tablas intermedias, etc. El primer sintoma de fragmentación sería lentitud en las consultas, principalmente perceptible en tablas con muchos registros..

La fragmentación tiene lugar sobretodo en tablas donde hay mucho movimiento insert/delete. Este crece mucho cuando el volumen de datos de la tabla es muy variable en el tiempo: por ejemplo en tablas de control de transacciones, de logueos de usuarios, de tablas intermedias, etc. El primer sintoma de fragmentación sería lentitud en las consultas, principalmente perceptible en tablas con muchos registros. Para conocer información al respecto podemos consultando la vista information_schema.tables donde podemos ver rápidamente el estado de las tablas y algunos datos interesantes de las mismas. 

El indice de fragmentación lo sacaremos de la relación entre el espacio liberado(data_free) por registros eliminados respecto al total de tabla(data_lenght), lo haremos porcentaje y ordenaremos de más a menos:

mysql> select table_schema, table_name,data_length, data_free, (data_free/data_length)*100 frag_percent , engine                        from information_schema.tables                        where table_schema not in ('information_schema', 'mysql') and data_free > 0                        order by frag_percent desc; 

+--------------+-------------------------+-------------+-----------+--------------+--------+
| table_schema | table_name              | data_length | data_free | frag_percent | engine |
+--------------+-------------------------+-------------+-----------+--------------+--------+
| db1          | alumnos_logueados       |   143484340 | 136095052 |      94.8501 | MyISAM |
| db1          | incidencias_puntualidad |   131917692 | 112362116 |      85.1759 | MyISAM |
| db1          | incidencias_asistencia  |     7901568 |   6265364 |      79.2927 | MyISAM |
| db1          | usuarios_logueados      |     1813192 |   1004284 |      55.3876 | MyISAM | 

Con los datos de esta consulta nos daremos cuenta de las tablas fragmentadas con espacio disponible a liberar. Para eliminar la fragmentación basta con ejecutar la sentencia OPTIMIZE TABLE tabla. La única pega es que al hacerlo nos bloquea la tabla por lo que debemos elegir un buen momento para hacerlo. La fragmentación tampoco es que nos tenga que quitar el sueño ya que la mejora en el 90% de los casos será imperceptible pero tampoco está de más controlarlo de vez en cuando. Incluso en nuevas bases de datos controlarlo nos ayuda a conocer las tablas con más movimiento.