Antwort.

Die Archivierung von Daten ist eine der wichtigsten Aufgaben für stark belastete OLTP-Systeme mit großen Tabellen. Historisch gesehen waren die ersten Versuche, dies umzusetzen, extrem einfach: Daten wurden manuell über Skripte oder Anwendungen in separate Tabellen gelöscht oder kopiert. Später entstanden systematischere Ansätze, die die Transaktionsintegrität und die minimale Auswirkung auf die Hauptarbeit der Datenbank berücksichtigten.

Das Problem liegt hier nicht nur im physischen Transfer von Informationen, sondern auch in der Aufrechterhaltung der Konsistenz, der Minimierung von Sperren und der Gewährleistung einer hohen Leistung. Fehler bei der Archivierung können zu Datenverlust, Sperrung von Benutzern oder einem erheblichen Anstieg der Last führen.

Die Lösung besteht darin, Batch-Operationen mit Transaktionskontrolle zu verwenden und spezielle Archivtabellem mit identischer Struktur zu bilden oder die Automatisierung über einen Aufgabenplaner und Prozeduren vorzunehmen.

Beispielcode:

-- Übertragen von 5000 Datensätzen, die älter als ein Jahr sind, in die Archivtabelle
INSERT INTO archive_orders SELECT * FROM orders WHERE order_date < DATEADD(year, -1, GETDATE())
    AND id IN (SELECT TOP 5000 id FROM orders WHERE order_date < DATEADD(year, -1, GETDATE()) ORDER BY id);
DELETE FROM orders WHERE id IN (SELECT TOP 5000 id FROM orders WHERE order_date < DATEADD(year, -1, GETDATE()) ORDER BY id);

Wesentliche Merkmale:

Übertragung alter Daten in Batches zur Reduzierung der Last.
Abstimmung von Einfüge- und Löschvorgängen mithilfe von Transaktionen.
Planung der Automatisierung über Zeitpläne und Prozeduren.

Fangfragen.

Welches Risiko besteht bei einer massenhaften Löschung alter Datensätze und wie kann man dies vermeiden?

Eine massive Löschung kann zur Eskalation von Sperren führen und den Betrieb der gesamten Datenbank verlangsamen. Dies kann vermieden werden, indem die Löschungen in kleinen Portionen innerhalb einer Schleife oder mit LIMIT/TOP durchgeführt werden, wenn die DBMS dies unterstützt.

WHILE 1=1
BEGIN
  DELETE TOP (1000) FROM orders WHERE order_date < '2023-01-01';
  IF @@ROWCOUNT = 0 BREAK;
END

Kann man TRUNCATE verwenden, um archivierte Daten zu löschen?

TRUNCATE löscht alle Zeilen in der Tabelle und eignet sich nicht für die bedingte Löschung bestimmter Zeilen. Es löst keine Trigger aus, unterstützt kein WHERE und wird nur für die vollständige Reinigung und nicht für die selektive Archivierung verwendet.

Wie kann man sicherstellen, dass der Transfer korrekt erfolgt ist, wenn Löschung und Einfügung in verschiedenen Transaktionen durchgeführt werden?

Es ist besser, den Transfer der Zeilen in einer Transaktion durchzuführen: Zuerst in das Archiv kopieren, dann aus der Haupttabelle löschen. Andernfalls kann es zu Inkonsistenzen im Falle eines Fehlers zwischen den Operationen kommen.

BEGIN TRANSACTION
INSERT INTO archive_orders SELECT * FROM orders WHERE ...
DELETE FROM orders WHERE ...
COMMIT

Typische Fehler und Anti-Patterns

Löschung riesiger Datenmengen in einer einzigen Anfrage, die zu Sperren führt.
Archivierung ohne Überprüfung, dass alle Zeilen tatsächlich übertragen wurden.
Verwendung von TRUNCATE anstelle von DELETE — führt zum Verlust aller Daten der Tabelle.

Beispiel aus dem Leben

Negativer Fall

Ein Ingenieur führt während des Arbeitstags das Skript auf eine Million Datensätze DELETE FROM logs WHERE event_date < '2022-01-01' aus.

Vorteile:

Das Skript ist einfach

Nachteile:

Die gesamte Tabelle wird gesperrt, Benutzer können nicht arbeiten, der Prozess dauert Stunden, eine Rückgabe ist ohne Backup nicht möglich.

Positiver Fall

Geplanter Transfer von 5000 Zeilen über eine gespeicherte Prozedur und einen Zeitplan, mit Protokollierung des Erfolgs jeder Portion.