Réponse.

La suppression de dizaines de millions de lignes est l'une des opérations les plus typiques et les plus dangereuses, en particulier dans les bases de données très sollicitées. Historiquement, beaucoup écrivaient simplement DELETE FROM, ce qui entraînait des blocages de tableau et un débordement du journal des transactions. Le principal problème : la transaction devient trop volumineuse, les processus de service ralentissent, et les conséquences d'un rollback peuvent être difficiles à prévoir.

Solution — mettre en œuvre une suppression "par lots" (batch), en traitant un petit nombre de lignes dans une boucle avec de courtes transactions, afin de minimiser les blocages et l'impact sur le système :

Exemple de code (SQL Server) :

WHILE 1=1
BEGIN
   DELETE TOP (10000)
   FROM YourHugeTable
   WHERE CreatedAt < DATEADD(year,-2,GETDATE());

   IF @@ROWCOUNT = 0 BREAK;
   WAITFOR DELAY '00:00:01'; -- petite pause pour réduire la charge
END

Caractéristiques clés :

Minimise la taille des blocages et l'enregistrement dans le journal des transactions.
Le traitement se fait en petites portions : le système reste réactif.
Peut être combiné avec l'affichage de la progression ou une logique de surveillance externe.

Questions pièges.

Si l'on utilise TRUNCATE au lieu de DELETE, est-ce toujours plus rapide et plus sûr ?

Non. TRUNCATE est beaucoup plus rapide, mais :

TRUNCATE ne peut pas être utilisé si une clé étrangère fait référence à la table.
TRUNCATE ne déclenche pas les triggers.
TRUNCATE supprime complètement toutes les lignes, et non pas selon une condition.

Dans un DELETE de masse, est-il important d'utiliser des index sur le champ de filtre ?

Oui, la présence d'un index approprié sur la colonne de filtre (par exemple, CreatedAt) accélère la recherche des lignes à supprimer et réduit la charge sur la table. Sans index, la requête touchera toute la table, même si un petit nombre de lignes est supprimé à chaque fois.

CREATE INDEX idx_createdat ON YourHugeTable(CreatedAt);

Que se passe-t-il si plusieurs threads effectuent des suppressions de masse en même temps ?

Cela entraînera une concurrence pour les blocages : des escalades de blocage surviendront, le temps d'attente augmentera et le risque de deadlock grandira. La suppression de masse d'une seule table doit être effectuée par un seul processus, ou avec une division de plage très soigneusement planifiée.

Erreurs communes et anti-patterns

Suppression de masse dans une seule transaction (bloque la table, déborde le journal des transactions).
Absence de vérification de la progression et de contrôle du temps d'exécution.
Absence d'index — la table entière est scannée à chaque fois.

Exemple de la vie réelle

Cas négatif

Le DBA a décidé de nettoyer une table de 60 millions de lignes avec une requête unique DELETE FROM Log WHERE dt < '2021-01-01'. Le serveur a presque "gelé", d'autres processus ont commencé à attendre l'exécution, et le fichier journal a considérablement augmenté, entraînant une longue récupération.

Avantages :

Facile à mettre en œuvre.

Inconvénients :

Pertes de performance importantes sur l'ensemble du serveur, risque de perte de données en cas d'accident, longue récupération.

Cas positif

La suppression a été divisée en paquets de 10 000 lignes, le processus est contrôlé, avec une pause après chaque portion. Le serveur fonctionne de manière stable, les autres tâches sont effectuées, l'administrateur surveille la progression.