Podział flagi na stół

Projektuję tabelę pozycji, która (potencjalnie) będzie zawierać dziesiątki milionów rekordów. Niektóre elementy nie będą dostępne, dopóki nie zostaną „zatwierdzone” przez administratora. Przez „use” rozumiem, że do takich pozycji nie będzie odwoływać się żadna inna tabela, dopóki nie zostaną „zatwierdzone”. Do 50% produktów może być „niezatwierdzonych” w dowolnym momencie. Zapisy mogą zostać „zatwierdzone”, ale nie odwrotnie.

Rozważam dwie opcje projektowania:

• trochę flaga
• osobna tabela „niezatwierdzonych” przedmiotów - po zatwierdzeniu element jest przenoszony do „zwykłej” tabeli (odnowienie ID przedmiotu nie stanowi problemu)

Myślę, że druga opcja jest znacznie lepsza. Flaga bitowa zajmuje tylko bajt na wiersz, więc nie stanowi to problemu. Ale jeśli mamy milion zatwierdzonych i milion niezatwierdzonych rekordów w tej samej tabeli - czas skanowania wydłuża się dla operacji z zatwierdzonymi rekordami.

Pytanie brzmi: czy powinienem rozważyć pierwszą opcję (flaga bitowa)? Czy ma jakieś zalety w opisywanej sytuacji?

Możesz to zrobić na dwa sposoby z widokami podzielonymi na partycje .

Tworzysz tabelę bazową dla każdego statusu, wymuszoną przez ograniczenia, z wzajemnie wykluczającymi się wartościami. Następnie zobacz, które UNIE razem tworzą podstawowe tabele. Do widoku lub każdej tabeli bazowej można bezpośrednio odwoływać się. Jeśli status wiersza jest aktualizowany przez widok, DBMS usunie go z jednej tabeli podstawowej i wstawi do tego odpowiadającego nowemu statusowi. Każda tabela podstawowa może być indeksowana niezależnie zgodnie z jej wzorcem użytkowania. Optymalizator rozpozna odniesienia do pojedynczej odpowiedniej tabeli podstawowej, jeśli to możliwe.

Korzyści to
a) płytsze indeksy. Wykonaj matematykę na temat rozwijania indeksu. Przy tej skali i podziale między wartościami statusu możliwe jest, że indeksy będą miały taką samą głębokość w tabelach podziału, jak w tabeli łączonej.
b) kod aplikacji nie musi się zmieniać. Dane nadal pojawiają się jako ciągła całość.
c) przyszłe nowe wartości statusu można uwzględnić, dodając nową tabelę podstawową z ograniczeniami i ponownie tworząc widok.

Kosztem jest cały ten ruch danych; dwie strony i powiązane indeksy są zapisywane dla każdej aktualizacji statusu. Dużo IO do załatwienia. Taki ruch spowoduje również fragmentację.

tabela pozycji, która będzie (potencjalnie) zawierać dziesiątki milionów rekordów.

To właściwie nie tyle, biorąc pod uwagę to, co SQL Server może skutecznie obsługiwać. Oczywiście pamiętam jedno z moich wcześniejszych zadań, w którym jedna z największych tabel (system z pojedynczą instancją) miała 2 miliony wierszy i była to największa ilość, z jaką kiedykolwiek miałem do czynienia. Następnie następne zadanie obejmowało 17 instancji produkcyjnych z niektórymi tabelami zawierającymi setki milionów wierszy, a wszystkie zostały zagregowane w hurtownię danych z wieloma tabelami faktów mającymi ponad 1 miliard wierszy. Nie zrozum mnie źle, nie szydzę z dziesiątek milionów wierszy, po prostu podkreślam, że przy dobrym modelu danych i właściwym indeksowaniu (i konserwacji indeksów) SQL Server może wiele poradzić .

Do 50% produktów może być „niezatwierdzonych” w dowolnym momencie.

Hmm To nie brzmi dobrze. Wskaźnik „zatwierdzania” wpisów będzie o połowę mniejszy niż liczba nowych wpisów? Na każde 2 nowe wpisy tylko 1 zostanie „zatwierdzony”? W twoim przykładzie 2 milionów wierszy i 1 miliona dla „zatwierdzonych” i „niezatwierdzonych”, kilka lat później z kolejnymi 10 milionami wpisów, oczekujesz 6 milionów dla „zatwierdzonych” i „niezatwierdzonych”? Czy może 1 milion „niezatwierdzonych” pozostanie nieco stały, tak że przy 10 milionach nowych wpisów będzie 11 milionów „zatwierdzonych”, a 1 milion „niezatwierdzonych”?

Zapisy mogą zostać „zatwierdzone”, ale nie odwrotnie.

To prawda dzisiaj , ale z czasem wszystko się zmienia, dlatego zawsze istnieje możliwość, że firma zdecyduje się na „niezatwierdzenie”, a może jakiś inny status, np. „Zarchiwizowany” itp.

Spójrzmy więc na opcje:

Flaga (a może nawet TINYINT„status”)

• Nieco wolniej w przypadku zapytań o każdy status
• Bardziej elastyczna w czasie / łatwa do wprowadzenia zmiany, takiej jak trzeci stan (np. „Zarchiwizowane”), z nową wartością statusu wyszukiwania. Brak nowej tabeli (koniecznie), trochę nowego kodu, tylko część kodu zaktualizowana.
• Mniej pracy (tj. Kodu, testowania itp.) I mniej miejsca na błędy podczas aktualizacji pojedynczej TINYINTkolumny
• Mniej skomplikowane = niższe koszty utrzymania w czasie, krótszy czas szkolenia dla nowych pracowników
• (ewentualnie) Mniejszy wpływ na Dziennik transakcji, gdy aktualizowana jest jedna tabela
• Potrzebujesz tylko tabeli odnośników dla „RecordStatus” i FK między dwiema tabelami.

Dwie oddzielne tabele (jedna dla „zatwierdzonych”, jedna dla „niezatwierdzonych”)

• Nieco szybciej dla zapytań o każdy status
• Mniej elastyczne w czasie / trudniejsze do wprowadzenia zmiany, takiej jak stan trzeci (np. „Zarchiwizowane”); nowy stan wymagałby najprawdopodobniej innej tabeli oraz zdecydowanie nowego i zaktualizowanego kodu.
• Więcej pracy (tj. Kodu, testowania itp.) I więcej miejsca na błędy w przenoszeniu rekordów z tabeli „Niezatwierdzone” do tabeli „Zatwierdzone”
• Bardziej skomplikowane = wyższe koszty utrzymania w czasie, dłuższy czas szkolenia dla nowych pracowników
• (ewentualnie) Większy wpływ na dziennik transakcji, ponieważ jedna tabela jest usuwana, a druga wstawiana
• Nie musisz się martwić o „ odnowienie identyfikatora produktu ”: niezatwierdzona tabela ma kolumnę identyfikatora, która jest IDENTITYkolumną, a tabela zatwierdzona ma kolumnę identyfikatora, która nie jest IDENTITY(ponieważ nie jest tam potrzebna). W związku z tym wartości ID pozostają spójne, gdy rekord przemieszcza się między tabelami.

Osobiście pochyliłbym się w kierunku pojedynczego stołu z StatusIDkolumną na początek. Korzystanie z dwóch tabel wydaje się nadmiernie skomplikowaną, przedwczesną optymalizacją. Tego rodzaju optymalizację można omówić, jeśli / kiedy liczba rekordów wynosi kilkaset milionów, a indeksowanie nie zapewnia żadnego wzrostu wydajności.