Odpowiedź.

Archiwizacja danych to jedno z najważniejszych zadań w intensywnie używanych systemach OLTP z dużymi tabelami. Historycznie pierwsze próby realizacji tego były bardzo proste: dane były usuwane lub kopiowane do oddzielnych tabel ręcznie za pomocą skryptów lub aplikacji. Później pojawiły się bardziej systematyczne podejścia, które uwzględniają integralność transakcyjną i minimalny wpływ na główną pracę bazy danych.

Problem polega nie tylko na fizycznym przenoszeniu informacji, ale także na utrzymaniu spójności, minimalizacji blokad i zapewnieniu wysokiej wydajności. Błędy podczas archiwizacji mogą prowadzić do utraty danych, zablokowania użytkowników lub znaczącego wzrostu obciążenia.

Rozwiązaniem jest wykorzystanie operacji wsadowych z kontrolą transakcji, a także utworzenie specjalnych tabel archiwalnych o identycznej strukturze lub automatyzacja przez harmonogramowanie zadań i procedury.

Przykład kodu:

-- Przenosimy po 5000 rekordów starszych niż rok do tabeli archiwalnej
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);

Kluczowe cechy:

Przenoszenie starych danych w partiach w celu zmniejszenia obciążenia.
Zgoda operacji wstawiania i usuwania za pomocą transakcji.
Planowanie automatyzacji przez harmonogramowanie zadań i procedury.

Pytania z podtekstem.

Jakie ryzyko istnieje przy masowym DELETE starych rekordów i jak tego uniknąć?

Masowy DELETE może prowadzić do eskalacji blokad i spowolnienia działania całej bazy. Można tego uniknąć, wykonując usunięcie w małych partiach wewnątrz pętli lub przy użyciu LIMIT/TOP, jeśli system zarządzania bazą danych to wspiera.

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

Czy można użyć TRUNCATE do usunięcia archiwizowanych danych?

TRUNCATE usuwa wszystkie wiersze w tabeli i nie nadaje się do warunkowego czyszczenia określonych wierszy. Nie wywołuje wyzwalaczy, nie obsługuje WHERE i jest używane tylko do całkowitego czyszczenia, a nie wybiórczej archiwizacji.

Jak zapewnić, że przenoszenie zakończyło się poprawnie, jeśli usunięcie i wstawienie odbywa się w różnych transakcjach?

Lepiej wykonać przeniesienie w jednej transakcji: najpierw kopiujemy do archiwum, następnie usuwamy z głównej bazy. W przeciwnym razie można uzyskać niespójność w przypadku awarii między operacjami.

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

Typowe błędy i anti-wzorce

Usuwanie ogromnych zbiorów danych jednorazowym zapytaniem, powodując blokady.
Archiwizacja bez upewnienia się, że wszystkie wiersze zostały rzeczywiście przeniesione.
Użycie TRUNCATE zamiast DELETE — prowadzi do utraty wszystkich danych w tabeli.

Przykład z życia

Negatywny przypadek

Inżynier uruchamia skrypt na milionie rekordów DELETE FROM logs WHERE event_date < '2022-01-01' w czasie pracy.

Zalety:

Skrypt prosty

Wady:

Blokuje całą tabelę, użytkownicy nie mogą pracować, proces trwa godziny, nie można cofnąć bez kopii zapasowej.

Pozytywny przypadek

Zaplanuj przeniesienie po 5000 wierszy przez procedurę składowaną i zadanie według harmonogramu w nocy, z logowaniem sukcesu każdej partii.