Suppression de millions de lignes dans MySQL

Question

J'ai récemment trouvé et corrigé un bug dans un site j'ai été travailler sur, qui a entraîné des millions de doublons de lignes de données dans un tableau qui sera assez grande, même sans eux (toujours en millions de dollars). Je peux facilement trouver ces lignes en double et vous pouvez exécuter une seule requête suppression de tous les tuer. Le problème est que d'essayer de supprimer ce nombre de lignes d'un seul coup verrouille en place de la table pour un long temps, que je voudrais éviter si possible. La seule façon que je peux voir pour se débarrasser de ces lignes, sans prise de site (par blocage de la table) sont:

1. Écrire un script qui va exécuter des milliers de petites supprimer des requêtes dans une boucle. Ce sera théoriquement obtenir autour de la table verrouillée question parce que d'autres requêtes seront en mesure de le faire dans la file d'attente et de fonctionner entre les supprime. Mais il reste toujours le pic de la charge sur la base de données tout à fait un peu et prendre du temps pour s'exécuter.
2. Renommer la table et de recréer la table existante (il va maintenant être vide). Puis faire mon nettoyage de la table renommée. Renommez la nouvelle table, nom de l'ancien arrière et fusionner les nouvelles lignes dans la table renommée. C'est la façon dont prend beaucoup plus d'étapes, mais devrait faire le travail avec un minimum d'interruption. La seule partie délicate est que la table en question est un tableau, donc une fois qu'il est renommé hors de la voie et le vide que l'on a mis à sa place tous les historiques des rapports d'aller loin jusqu'à ce que je l'ai remis en place. Plus le processus de fusion peut être un peu de douleur, car le type de données stockées. En général, c'est mon choix maintenant.

Je me demandais juste si quelqu'un d'autre a eu ce problème avant et, si oui, comment vous avez fait affaire avec lui sans prendre le site et, espérons-le, avec un minimum si une interruption pour les utilisateurs? Si je vais avec le numéro 2, ou d'un autre, similaire, l'approche, je peux programmer des trucs pour fonctionner tard dans la nuit et faire la fusion tôt le lendemain matin et de laisser les utilisateurs savent à l'avance, ce qui n'est pas une affaire énorme. Je suis à la recherche pour voir si quelqu'un a des idées pour un meilleur, ou le plus simple, pour faire le nettoyage.

Answer 1

 DELETE FROM `table`
WHERE (whatever criteria)
ORDER BY `id`
LIMIT 1000
 

Laver, rincer, répéter jusqu'à ce que zéro rangées soient touchées. Peut-être dans un script qui dort une ou trois secondes entre les itérations.

Answer 2

Je vous recommanderais également d'ajouter des contraintes à votre table pour vous assurer que cela ne vous arrive plus. Un million de lignes, à raison de 1 000 points par coup, nécessiteront 1 000 répétitions d’un script. Si le script s'exécute une fois toutes les 3,6 secondes, vous aurez terminé en une heure. Pas de soucis. Vos clients sont peu susceptibles de remarquer.

Answer 3

Je voudrais utiliser mk-archiveur de l'excellent Maatkit package d'utilitaires (un tas de scripts Perl pour la gestion MySQL) Maatkit est le Baron Schwartz, l'auteur de l'O'Reilly "Haute Performance MySQL" livre.

Le but est d'avoir un faible impact, avant uniquement travail à grignoter anciennes données de la table sans impact sur les requêtes OLTP beaucoup. Vous pouvez insérer les données dans un autre le tableau, qui n'a pas besoin d'être sur la même serveur. Vous pouvez aussi écrire sur un fichier dans un format adapté à la CHARGE DATA INFILE. Ou vous pouvez faire ni l'un ni dans auquel cas, c'est juste un différentiel SUPPRIMER.

Il est déjà construit pour l'archivage de vos indésirables les lignes en petits lots et en prime, il peut économiser de l'supprimé les lignes d'un fichier dans le cas où vous bousiller la requête qui sélectionne les lignes à supprimer.

Aucune installation n'est requise, il suffit de saisir http://www.maatkit.org/get/mk-archiver et exécuter perldoc sur elle (ou lire le site web) pour la documentation.

Answer 4

Faites-le par lots de, disons 2000 lignes à la fois. S'engager entre les deux. Un million de lignes, ce n'est pas beaucoup et ce sera rapide, à moins que vous n'ayez plusieurs index sur la table.