¿Cuál es la mejor manera de eliminar filas antiguas de MySQL de forma continua?

Me parece que quiero eliminar filas anteriores a (x) los días de forma continua en muchas aplicaciones. ¿Cuál es la mejor manera de hacer esto de la manera más eficiente en una mesa de alto tráfico?

Por ejemplo, si tengo una mesa que almacena notificaciones y solo quiero guardarlas durante 7 días. O puntuaciones altas que solo deseo conservar durante 31 días.

En este momento, conservo una fila que almacena el tiempo de epoch publicado y ejecuto un trabajo cron que se ejecuta una vez por hora y los elimina en incrementos como este:

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100 

Lo hago hasta que mysql_affected_rows devuelva 0.

Solía ​​hacerlo todo de una vez pero eso causó que todo en la aplicación se bloqueara durante 30 segundos más o menos mientras los INSERTOS se acumulaban. Agregar el LIMIT funcionó para aliviar esto, pero me pregunto si hay una mejor manera de hacerlo.

Echa un vistazo a la partición de MySQL :

Los datos que pierden su utilidad a menudo se pueden eliminar fácilmente de una tabla particionada eliminando la partición (o particiones) que contiene solo esos datos. Por el contrario, el proceso de agregar nuevos datos puede, en algunos casos, verse facilitado al agregar una o más particiones nuevas para almacenar específicamente esos datos.

Ver, por ejemplo, esta publicación para obtener algunas ideas sobre cómo aplicarla:

Uso del particionamiento y el progtwigdor de eventos para podar tablas de archivos comprimidos

Y éste:

Particionamiento por fechas: cómo hacerlo rápido

Intente crear el evento que se ejecutará en la base de datos automáticamente después del intervalo de tiempo que desee.

Aquí hay un ejemplo: si desea eliminar entradas que tienen más de 30 días de antigüedad de alguna tabla ‘tableName’, con la entrada de columna ‘datetime’. Luego, la siguiente consulta se ejecuta todos los días, lo que hará la acción de limpieza requerida.

 CREATE EVENT AutoDeleteOldNotifications ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY ON COMPLETION PRESERVE DO DELETE LOW_PRIORITY FROM databaseName.tableName WHERE datetime < DATE_SUB(NOW(), INTERVAL 30 DAY) 

Necesitamos agregar ON COMPLETION PRESERVE para mantener el evento después de cada carrera. Puede encontrar más información aquí: http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

En lugar de ejecutar la eliminación solo contra la tabla, intente reunir las claves correspondientes primero y luego haga un BORRAR UNIR

Teniendo en cuenta la consulta de muestra anterior

 DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100 ; 

Puedes dejar el LIMIT fuera de él.

Supongamos que quiere eliminar datos de más de 31 días de antigüedad.

Vamos a calcular 31 días en segundos (86400 X 31 = 2678400)

  • Comience con la recolección de llaves
  • A continuación, indexe las claves
  • Luego, realice DELETE JOIN
  • Finalmente, suelta las llaves recolectadas

Aquí está el algoritmo

 CREATE TABLE delete_keys SELECT id FROM my_table WHERE 1=2; INSERT INTO delete_keys SELECT id FROM ( SELECT id FROM my_table WHERE time_stored < (UNIX_TIMESTAMP() - 2678400) ORDER BY time_stored ) A LIMIT 100; ALTER TABLE delete_keys ADD PRIMARY KEY (id); DELETE B.* FROM delete_keys INNER JOIN my_table B USING (id); DROP TABLE delete_keys; 

Si la recostackción de claves es inferior a 5 minutos, ejecute esta consulta cada 5 minutos.

Darle una oportunidad !!!

ACTUALIZACIÓN 2012-02-27 16:55 EDT

Aquí hay algo que debería acelerar la recolección de llaves un poco más. Agregue el siguiente índice:

 ALTER TABLE my_table ADD INDEX time_stored_id_ndx (time_stored,id); 

Esto respaldará mejor la subconsulta que rellena la tabla delete_keys porque proporciona un índice de cobertura para que los campos se recuperen solo del índice.

ACTUALIZACIÓN 2012-02-27 16:59 EDT

Dado que tiene que eliminar a menudo, es posible que desee probar esto cada dos meses

 OPTIMIZE TABLE my_table; 

Esto desfragmentará la tabla después de todas esas pequeñas eliminaciones molestas cada 5 minutos durante dos meses

En mi compañía, tenemos una situación similar. Tenemos una tabla que contiene claves que tienen una caducidad. Tenemos un cron que corre para limpiar eso:

 DELETE FROM t1 WHERE expiration < UNIXTIME(NOW()); 

Esto funcionó una vez por hora, pero estábamos teniendo problemas similares a lo que estás experimentando. Lo aumentamos a una vez por minuto. Luego 6 veces por minuto. Configura un cron con un script bash que básicamente hace la consulta, luego duerme por unos segundos y se repite hasta que el minuto se agota.

La frecuencia aumentada disminuyó significativamente la cantidad de filas que estábamos eliminando. Lo cual alivió la disputa. Esta es la ruta que iría.

Sin embargo, si encuentra que todavía tiene demasiadas filas para eliminar, use el límite y duerma entre ellas. Por ejemplo, si tiene 50k filas para eliminar, haga un bloque de 10k con un descanso de 2 segundos entre ellas. Esto ayudará a que las consultas se acumulen, y permitirá que el servidor realice algunas operaciones normales entre estas eliminaciones masivas.

Es posible que desee considerar la introducción de una solución maestro / esclavo (replicación) en su diseño. Si cambia todo el tráfico de lectura al esclavo, abre el maestro para manejar actividades CRUD “sobre la marcha”, que luego se replican al esclavo (su servidor de lectura).

Y debido a que está eliminando tantos registros, es posible que desee considerar ejecutar una optimización en la (s) tabla (s) desde donde se borran las filas.

Terminó usando esto para dejar solo 100 últimas filas en su lugar, por lo que el retraso significativo cuando se ejecuta con frecuencia (cada minuto)

 delete a from tbl a left join ( select ID from tbl order by id desc limit 100 ) b on a.ID = b.ID where b.ID is null;