Quelle est la meilleure façon de supprimer les anciennes lignes de MySQL sur une base continue?

Je me retrouve à vouloir supprimer des lignes de plus de (x) jours sur une base continue dans de nombreuses applications. Quelle est la meilleure façon de le faire le plus efficacement possible sur une table à fort trafic?

Par exemple, si j’ai une table qui stocke les notifications et que je veux seulement les conserver pendant 7 jours. Ou des scores élevés que je ne veux garder que pendant 31 jours.

En ce moment, je garde une ligne stockant l’heure de publication et exécute un travail cron qui s’exécute une fois par heure et les supprime par incréments comme ceci:

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100

Je fais cela jusqu’à ce que mysql_affected_rows retourne 0.

J’avais l’habitude de tout faire en même temps, mais cela a provoqué un retard d’environ 30 secondes dans l’application, alors qu’INSERTS s’était accumulé. L’ajout de LIMIT a consortingbué à atténuer ce problème, mais je me demande s’il existe une meilleure façon de le faire.

Découvrez le partitionnement MySQL :

Les données qui perdent leur utilité peuvent souvent être facilement supprimées d’une table partitionnée en supprimant la ou les partitions contenant uniquement ces données. Inversement, le processus d’ajout de nouvelles données peut être grandement facilité en ajoutant une ou plusieurs nouvelles partitions pour stocker spécifiquement ces données.

Voir par exemple cet article pour obtenir des idées sur la façon de l’appliquer:

Utilisation du partitionnement et du planificateur d’événements pour élaguer les tables d’archives

Et celui-là:

Partitionnement par dates: comment faire rapidement

Essayez de créer un événement qui s’exécutera automatiquement sur la firebase database après l’intervalle de temps souhaité.

Voici un exemple: Si vous souhaitez supprimer des entrées datant de plus de 30 jours d’une table «tableName», ayant une entrée de colonne «datetime». Ensuite, la requête suivante s’exécute chaque jour, ce qui nécessite une action de nettoyage.

 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)

Nous devons append ON COMPLETION PRESERVE pour conserver l'événement après chaque course. Vous pouvez trouver plus d'informations ici: http://www.mysqltutorial.org/mysql-sortingggers/working-mysql-scheduled-event/

Au lieu d’exécuter la suppression uniquement sur la table, essayez de rassembler les clés correspondantes, puis effectuez une DELETE JOIN.

Compte tenu de la requête ci-dessus

 DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100 ;

Vous pouvez en laisser la LIMITE.

Disons que vous voulez supprimer des données de plus de 31 jours.

Calculons 31 jours en secondes (86400 X 31 = 2678400)

Commencez avec la collecte de clés
Ensuite, indexez les clés
Ensuite, effectuez DELETE JOIN
Enfin, déposez les clés rassemblées

Voici l'algorithme

 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 collecte des clés dure moins de 5 minutes, exécutez cette requête toutes les 5 minutes.

Essaie !!!

MISE À JOUR 2012-02-27 16:55 HAE

Voici quelque chose qui devrait accélérer la collecte de clés un peu plus. Ajoutez l'index suivant:

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

Cela permettra de mieux prendre en charge la sous-requête qui remplit la table delete_keys car elle fournit un index de recouvrement afin que les champs soient récupérés uniquement dans l'index.

MISE À JOUR 2012-02-27 16:59 EDT

Comme vous devez supprimer souvent, vous voudrez peut-être essayer tous les deux mois

 OPTIMIZE TABLE my_table;

Cela va défragmenter la table après toutes ces petites suppressions ennuyeuses toutes les 5 minutes pendant deux mois

Dans mon entreprise, nous avons une situation similaire. Nous avons une table qui contient des clés qui ont une expiration. Nous avons un cron qui fonctionne pour nettoyer cela:

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

Cela a fonctionné une fois par heure, mais nous avions des problèmes similaires à ce que vous rencontrez. Nous l'avons augmenté à une fois par minute. Puis 6 fois par minute. Installez un cron avec un script bash qui effectue essentiellement la requête, puis dort quelques secondes et se répète jusqu'à ce que la minute soit écasting.

La fréquence accrue a significativement diminué le nombre de lignes que nous supprimions. Ce qui a soulagé la controverse. C'est la voie que j'irais.

Cependant, si vous constatez que vous avez encore trop de lignes à supprimer, utilisez la limite et faites une pause entre elles. Par exemple, si vous avez 50 000 lignes à supprimer, effectuez un bloc de 10 Ko avec un sumil de 2 secondes entre eux. Cela aidera les requêtes à s'emstackr et permettra au serveur d'effectuer certaines opérations normales entre ces suppressions en bloc.

Vous pouvez envisager d’introduire une solution maître / esclave (réplication) dans votre conception. Si vous transférez tout le trafic de lecture vers l’esclave, vous ouvrez le maître pour gérer les activités CRUD «à la volée», qui sont ensuite répliquées sur l’esclave (votre serveur de lecture).

Et parce que vous supprimez autant d’enregistrements, vous pouvez envisager d’exécuter une optimisation sur la ou les tables à partir desquelles les lignes sont supprimées.

Terminé en utilisant cela pour ne laisser que 100 dernières lignes en place, donc un décalage significatif lors de l’exécution fréquente (chaque minute)

 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;