Tabela nagrobków a flaga usunięta w scenariuszach synchronizacji bazy danych i miękkiego usuwania

Muszę śledzić usunięte elementy na potrzeby synchronizacji klienta.

Zasadniczo, czy lepiej jest dodać tabelę nagrobków i wyzwalacz, który śledzi, kiedy wiersz został usunięty z bazy danych serwera - w zasadzie dodając nowy wiersz do tabeli nagrobków z danymi z usuniętego elementu - czy też zachować elementy w oryginalna tabela i oflaguj je jako usunięte, zwykle z kolumną typu bit, aby wskazać, że wiersz został usunięty, a inna kolumna do śledzenia, kiedy wystąpiło usunięcie?

Zasadniczo lepiej jest znać szczegółowe wymagania i nie podejmować decyzji projektowych w oparciu o to, co działa najlepiej w większości sytuacji. Każda może być lepsza. Oto kilka szczegółów do zebrania:

• Jak szybko muszą być usuwane?
• Jak szybko muszą być usuwane usunięcia?
• Jak często będą wyszukiwane usunięte dane i czy będą wyszukiwane dane, które nie zostały usunięte?
• Jak szybkie muszą być zapytania dotyczące usuniętych danych?
• Czy chcesz zachować również tylko usunięte elementy lub zmiany?
• Czy musisz utrzymywać mały stół / indeksy na stole podstawowym?
• Jakie technologie partycjonowania i / lub śledzenia zmian są dostępne na platformie bazy danych?
• Ile miejsca na dysku jest dostępne?
• Czy usuwanie nastąpi w locie czy w operacjach wsadowych?

Może powinieneś celowo połączyć te dwie metody. Dlaczego ???

Użyjmy tej tabeli (dialekt MySQL)

CREATE TABLE mydata
(
    id int not null auto_increment
    firstname varchar(16) not null,
    lastname varchar(16) not null,
    zipcode char(5) not null,
    ...
    deleted tinyint not null default 0
    KEY (deleted,id),
    KEY (deleted,lastname,firstname,id),
    KEY (deleted,zipcode,id),
    KEY (lastname,firstname),
    KEY (zipcode),
    PRIMARY KEY (id)
);

Pamiętaj, że z wyjątkiem KLUCZA PODSTAWOWEGO każdy tworzony indeks powinien być poprzedzony deletedflagą i kończyć się naid .

Stwórzmy stół z nagrobkami

CREATE TABLE mytomb SELECT id FROM mydata WHERE 1=2;
ALTER TABLE mytomb ADD PRIMARY KEY (id);

Jeśli twój stół ma już deletedflagę, możesz wypełnić tabelę Tommstone

INSERT INTO mytomb SELECT id FROM mydata WHERE deleted = 1;

OK, teraz dane i nagrobek są przygotowane. Jak przeprowadzasz usuwanie?

Załóżmy, że usuwasz każdą osobę z kodu pocztowego 07305. Uruchomiłbyś następujące:

INSERT IGNORE INTO mytomb SELECT id FROM mydata WHERE deleted=0 AND zipcode='07305';
UPDATE mydata SET deleted=1 WHERE deleted=0 AND zipcode='07305';

OK, tak czy inaczej, wygląda to na duże obciążenie.

Czy chcesz teraz zobaczyć wszystkie usunięte dane? Oto dwa różne sposoby:

• SELECT * FROM mydata WHERE deleted=1;
• SELECT B.* FROM mytomb A INNER JOIN mydata B USING (id);

Jeśli liczba identyfikatorów w mytomb jest większa niż 5% liczby wierszy moich danych, jest to pełne skanowanie tabeli. W przeciwnym razie skan indeksu z wyszukiwaniem każdego wiersza. Zwróć uwagę na wszelkie kryteria w tym zakresie. Wyszukaj wyjaśnienie planów.

Czy chcesz teraz zobaczyć każdą osobę w kodzie pocztowym 07304? Oto dwa różne sposoby:

• SELECT * FROM mydata WHERE deleted=1 AND zipcode='07304';
• SELECT A.* FROM mydata A LEFT JOIN mytomb B USING (id) WHERE B.id IS NULL AND A.zipcode='07304'

Co powiesz na masowe usuwanie? Oto dwa różne sposoby:

• DELETE FROM mydata WHERE deleted=1;
• DELETE B.* FROM mytomb A INNER JOIN mydata B USING (id); DELETE FROM mytomb;

WNIOSEK

Nie mówię teraz o zachowaniu obu metod. Wykonanie tego z czasem ujawnia, która metoda jest szybsza pod względem ogólnej operacyjności. Musisz zdecydować, które testy porównawcze do sprawdzania danych na żywo, sprawdzania usuniętych danych i masowego usuwania działają najlepiej dla Ciebie.