Mantenimiento de una base de datos

Desarrollo de software personalizado

hombre haciendo mantenimiento a una base de datos de mysqli

El mantenimiento de una base de datos es una tarea muy importante para cualquier software puesto que sobre los datos almacenados corre toda la arquitectura que se desarrolla.

Un Cron Job es una excelente alternativa para hacer mantenimiento automático a las bases de datos, en mi hosting por ejemplo, tengo configurado un archivo que cada 24 horas ejecuta ciertas tareas que garantizan la idoneidad de los datos. A continuación te sugiero algunas prácticas que permiten que la información se conserve de manera íntegra y que las consultas fluyan con velocidad óptima.

  1. Delete. Eliminar datos innecesarios. Esta alterativa no puede aplicarse a todas las tablas,pero con el tiempo es posible que algunas tengan información que definitivamente ya no necesitas, en ese caso es bueno hacer un borrado automático de los datos. Un ejemplo de esta situación puede ser:

delete from tabla where DATEDIFF(NOW(),fechacreacion) >90

Aquí supongo una tabla que tiene un campo (fechacreacion) de tipo DATETIME. Con la comparación que hay en el where estamos borrando todas las filas que tengan mas de 90 dias de antiguedad.

2. TRUNCATE. Limpiar la tabla. Un complemento para la sentencia delete, pero cuidado, no hay un where, esto significa que utilizas la sentencia todos los datos de la tabla se eliminarán, la ventaja de esta orden es que que reinicia los contadores autoincrementables. En este orden de ideas, podemos usarla acompañado de una consulta que cuente la existencia de datos en la tabla.

Una situación donde este mètodo tiene mucha relevancia es el caso donde se utiliza una tabla como auxiliar que tiene datos que se borran despues de cierta operaciòn con un Id SERIAL, que crece indiscrimadamente, en ese caso en nuestro cron podemos tener algo como esto

$rs= $con->query(«select count(id) from

facturasauxiliar»);$fila=mysqli_fetch_array($rs);

if($fila[0]<1){$con->query(«truncate table facturasauxiliar»);}

Cada que el Cron Job ejecute esta sentencia optimiza la tabla para que los autoincrementales no se suban mucho, y tenemos la seguridad de que solo lo hace cuando la tabla està vacía.

3. AUTO_INCREMENT = 0. Desafortunadamente cuando la tabla tiene relaciones hacia otras tablas, la sentencia TRUNCATE falla (incluso si la tabla está vacía), en ese caso nos ayudamos de AUTO_INCREMENT = 0 , con esto, logramos el mismo ojetivo por las malas. La sentencia quedaría así

$rs= $con->query(«select count(id) from facturasauxiliar»);

$fila=mysqli_fetch_array($rs);

if($fila[0]<1){$con->query(«alter table facturasauxiliar AUTO_INCREMENT = 0 «); }

Solamente la usamos cuando la tabla tiene relaciones que no permitenque funcione TRUNCATE

4. Vigila el tamaño de las tablas. Si usas phpmyadmin es fàcil mirar el tamaño de las tablas, ahì te dejo una imagen explicativa


Esto es muy importante porque cuando ese tamaño es grande phpmyadmin se enloquece y no es capaz de hacer, ni siquiera puede hacer un delete, En un ejemplo que hice,permití que una tabla se creciera a 250 MB, con un ciclo que introjudo un millón de filas en una tabla, y cada que trataba de hacer una consulta el servidor colapsaba. En últimas tocó destruir la tabla y no se pudo recuperar la información de ninguna manera.

OPTIMIZE. Cuando se hace un delete sobre una tabla el espacio queda vacío, y luego es utilizado con los nuevos datos que van ingresando, pero en una tabla que tiene mucho movimiento, se va generando el problema de la fragmentaciòn que no es otra cosa que muchos huecos vacios en la tabla que ya no se van a usar. ¿Que hacer para optimizar ese espacio?, Optimizar la tabla, la sentencia es sencilla

OPTIMIZE TABLE nombredelatabla;

Comando para desfragmentar una tabla en myslqi.

Hasta aquì, todo muy bien, pero….¿Y si tengo muhcas tablas?, ¿Las optimizo todas?. La verdad es que solo vamos a optimizar las que son necesarias, (aquellas que tienen espacios vacíos), ¿Y cuales son?. En esta consulta te dejo la magia

SELECT TABLE_SCHEMA,TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA NOT IN («information_schema»,»mysql») AND Data_free > 0

Consulta para averigual cuales son las tablas que necesitan ser desfragmentadas

El detalle con este método es que necesitas utilizar la base de datos information_schema y aunque esto no es un problema, si tienes muchas bases de datos necesitarás afinar un poco la consulta.

Bueno, si tienes algún aporte, o necesitas orientación adicional no dudes en consultarme

También puedo ayudarte a normalizar tus bases de datos .

 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *