Co może być wadą posiadania pojedynczej kolumny liczb całkowitych jako klucza podstawowego?

W ramach jednej aplikacji WWW, nad którą pracuję, wszystkie operacje na bazach danych są abstrakcyjne przy użyciu niektórych ogólnych repozytoriów zdefiniowanych w Entity Framework ORM.

Jednak, aby mieć prosty projekt dla ogólnych repozytoriów, wszystkie zaangażowane tabele muszą definiować unikalną liczbę całkowitą ( Int32w C #, intw SQL). Do tej pory zawsze było to PK na stole, a także IDENTITY.

Klucze obce są intensywnie używane i odnoszą się do tych liczb całkowitych. Są one wymagane zarówno dla spójności, jak i dla generowania właściwości nawigacyjnych przez ORM.

Warstwa aplikacji zazwyczaj wykonuje następujące operacje:

• wstępne ładowanie danych z tabeli (*) -SELECT * FROM table
• Aktualizacja -UPDATE table SET Col1 = Val1 WHERE Id = IdVal
• Usuń -DELETE FROM table WHERE Id = IdVal
• Wstaw -INSERT INTO table (cols) VALUES (...)

Rzadsze operacje:

• Wstawianie zbiorcze - BULK INSERT ... into tablepo którym następuje (*) wszystkie ładowanie danych (aby pobrać wygenerowane identyfikatory)
• Usuwanie zbiorcze - jest to normalna operacja usuwania, ale „nieporęczna” z perspektywy ORM:DELETE FROM table where OtherThanIdCol = SomeValue
• Aktualizacja zbiorcza - jest to normalna operacja aktualizacji, ale „nieporęczna” z punktu widzenia ORM:UPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue

* wszystkie małe tabele są buforowane na poziomie aplikacji i prawie wszystkie SELECTsnie osiągną bazy danych. Typowy wzór to obciążenie początkowe i wiele INSERTs, UPDATEs i DELETEs.

W oparciu o bieżące użycie aplikacji istnieje bardzo mała szansa na osiągnięcie 100 milionów rekordów w dowolnej tabeli.

Pytanie: Z punktu widzenia DBA, czy istnieją znaczące problemy, na które mogę natknąć się z powodu tego ograniczenia projektowania tabeli?

[EDYTOWAĆ]

Po przeczytaniu odpowiedzi (dziękuję za świetne opinie) i odnośników do artykułów, czuję, że muszę dodać więcej szczegółów:

1. Bieżąca specyfika aplikacji - nie wspomniałem o bieżącej aplikacji internetowej, ponieważ chcę zrozumieć, czy model może być ponownie użyty również w innych aplikacjach. Jednak moim szczególnym przypadkiem jest aplikacja, która wyodrębnia wiele metadanych z DWH. Dane źródłowe są dość niechlujne (zdenormalizowane w dziwny sposób, mają pewne niespójności, w wielu przypadkach nie mają naturalnego identyfikatora itp.), A moja aplikacja generuje wyraźnie oddzielone byty. Wyświetlanych jest także wiele wygenerowanych identyfikatorów ( IDENTITY), dzięki czemu użytkownik może użyć ich jako kluczy biznesowych. Oprócz masowego refaktoryzacji kodu wyklucza to użycie identyfikatorów GUID .

2. „nie powinny być jedynym sposobem jednoznacznego zidentyfikowania rzędu” (Aaron Bertrand ♦) - to bardzo dobra rada. Wszystkie moje tabele definiują także WYJĄTKOWE OGRANICZENIE, aby upewnić się, że duplikaty biznesowe nie są dozwolone.

3. Projektowanie oparte na aplikacji frontonu vs. projektowanie oparte na bazie danych - wybór projektu wynika z tych czynników

   1. Ograniczenia struktury jednostki - dozwolone są wiele kolumn PK, ale ich wartości nie można aktualizować

   2. Ograniczenia niestandardowe - posiadanie jednego klucza liczby całkowitej znacznie upraszcza struktury danych i kod inny niż SQL. Np .: wszystkie listy wartości mają klucz liczby całkowitej i wyświetlane wartości. Co ważniejsze, gwarantuje, że każda tabela oznaczona do buforowania będzie mogła umieścić na Unique int key -> valuemapie.

4. Złożone zapytania dotyczące wyboru - prawie nigdy tak się nie stanie, ponieważ wszystkie małe tabele (<20-30 000 rekordów) są buforowane na poziomie aplikacji. To sprawia, że ​​życie jest trochę trudniejsze podczas pisania kodu aplikacji (trudniej napisać LINQ), ale baza danych jest znacznie ładniejsza:

   1. Widoki list - nie będą generować żadnych SELECTzapytań przy ładowaniu (wszystko jest buforowane) lub zapytań, które wyglądają tak:

      SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)

      Wszystkie pozostałe wymagane wartości są pobierane przez wyszukiwanie pamięci podręcznej (O (1)), więc nie będą generowane żadne złożone zapytania.

   2. Edytuj widoki - wygeneruje SELECTtakie instrukcje:

      SELECT allcolumns FROM BigTable WHERE PKId = value1

(wszystkie filtry i wartości są ints)

Poza dodatkowym miejscem na dysku (a tym samym zużyciem pamięci i we / wy) dodanie kolumny TOŻSAMOŚCI nie jest niczym złym, nawet do tabel, które jej nie potrzebują (przykład tabeli, która nie potrzebuje kolumny TOŻSAMOŚCI) jest prostą tabelą połączeń, taką jak mapowanie użytkownika na jego / jej uprawnienia).

Odradzam ślepe dodawanie ich do każdego stołu w blogu z 2010 roku:

• Złe nawyki, by kopać: umieszczanie kolumny TOŻSAMOŚCI na każdym stole

Ale klucze zastępcze mają ważne przypadki użycia - po prostu uważaj, aby nie zakładać, że gwarantują one wyjątkowość (dlatego czasami są dodawane - nie powinny być jedynym sposobem jednoznacznej identyfikacji wiersza). Jeśli potrzebujesz użyć struktury ORM, a Twoja struktura ORM wymaga jednokolumnowych kluczy całkowitych, nawet w przypadkach, gdy twój prawdziwy klucz nie jest liczbą całkowitą, ani nie jest pojedynczą kolumną, albo nie, upewnij się, że zdefiniowałeś unikalne ograniczenia / indeksy także dla twoich prawdziwych kluczy.

Z mojego doświadczenia wynika, że ​​głównym i przytłaczającym powodem używania osobnego identyfikatora dla każdej tabeli jest:

W prawie każdym przypadku mój klient złożył przysięgę krwi w fazie poczęcia, że ​​pewne zewnętrzne „naturalne” pole XYZBLARGH_IDpozostanie na zawsze wyjątkowe i nigdy nie zmieni się dla danego podmiotu i nigdy nie będzie ponownie użyte, w końcu pojawiły się przypadki, w których Właściwości klucza podstawowego zostały uszkodzone. To po prostu nie działa w ten sposób.

Następnie, z punktu widzenia DBA, rzeczy, które powodują, że DB jest wolny lub wzdęty, z pewnością nie są 4 bajtami (lub czymkolwiek) na wiersz, ale rzeczy takie jak złe lub brakujące indeksy, zapomniane reorganizacje tabel / indeksów, złe parametry dostrajania pamięci RAM / przestrzeni tabel , zaniedbując użycie zmiennych powiązań i tak dalej. Ci, może spowolnić PB czynników 10, 100, 10000, ... Nie dodatkowej kolumny ID.

Tak więc, nawet jeśli nie były techniczne, mierzalne minusem posiadania dodatkowego 32 bit na rząd, że nie jest to kwestia, czy można zoptymalizować identyfikator daleko, ale czy identyfikator będzie niezbędna w pewnym momencie, który będzie bardziej prawdopodobnie niż nie. I nie zamierzam liczyć na wszystkie „miękkie” korzyści wynikające ze stanowiska programistycznego (takiego jak przykład ORM lub fakt, że ułatwia to programistom, gdy wszystkie identyfikatory według projektu mają ten sam typ danych itd.) .

Uwaga: pamiętaj, że nie potrzebujesz osobnego identyfikatora dla n:mtabel asocjacyjnych, ponieważ dla takich tabel identyfikatory powiązanych jednostek powinny tworzyć klucz podstawowy. Kontrprzykład byłby dziwnym n:mskojarzeniem, które pozwala na wiele skojarzeń między tymi samymi dwoma bytami z jakiegokolwiek dziwnego powodu - one potrzebowałyby wtedy własnej kolumny identyfikatora, aby utworzyć PK. Tam są biblioteki ORM, które nie mogą obsługiwać PKs wielu kolumn chociaż, więc to byłby powód do pobłażliwości programistów, jeśli mają pracować z takiej biblioteki.

Jeśli niezmiennie dodasz bezsensowną dodatkową kolumnę do każdej tabeli i odniesiesz się tylko do tych kolumn jako kluczy obcych, prawie nieuchronnie sprawisz, że baza danych będzie bardziej złożona i trudna w użyciu. W efekcie usuniesz dane będące przedmiotem zainteresowania użytkowników z atrybutów klucza obcego i zmuszając użytkownika / aplikację do wykonania dodatkowego sprzężenia w celu pobrania tych samych informacji. Zapytania stają się bardziej złożone, zadanie optymalizatora staje się trudniejsze, a wydajność może się pogorszyć.

W twoich tabelach będzie mniej miejsca „rzeczywistych” danych niż w innym przypadku. Baza danych będzie zatem trudniejsza do zrozumienia i weryfikacji. Może być również trudne lub niemożliwe narzucenie pewnych przydatnych ograniczeń (gdzie ograniczenia obejmowałyby wiele atrybutów, które nie są już w tej samej tabeli).

Sugeruję, abyś ostrożniej wybrał klucze i uczynił je liczbami całkowitymi tylko wtedy, gdy masz ku temu dobre powody. Oprzyj projekty baz danych na dobrej analizie, integralności danych, praktyczności i weryfikowalnych wynikach, zamiast opierać się na regułach dogmatycznych.

Z mojego doświadczenia z różnymi bazami danych, klucz podstawowy Integer jest zawsze lepszy niż aplikacje, które nie mają zdefiniowanych kluczy. Lub które mają klucze, które łączą pół tuzina kolumn varchar na niewygodne sposoby, które nie są logiczne ... (westchnienie)

Widziałem aplikacje, które przestawiły się z całkowitych PK na GUID. Powodem tego było to, że w niektórych przypadkach istniała potrzeba scalenia danych z wielu źródłowych baz danych. Programiści zmienili wszystkie klucze na GUID, aby scalenia mogły się odbyć bez obawy o kolizje danych, nawet na tabelach, które nie były częścią scalenia (na wypadek gdyby tabele te stały się częścią przyszłego scalenia).

Powiedziałbym, że liczba całkowita PK nie ugryzie Cię, chyba że planujesz scalić dane z oddzielnych źródeł lub możesz mieć dane, które wykraczają poza limity wielkości całkowitych - to cała zabawa i gry, dopóki nie zabraknie miejsca na wstawki .

Powiem jednak, że to może mieć sens, aby ustawić indeksu klastrowego w kolumnie innego niż PK, jeśli stół będzie częściej tamtędy pytani. Ale jest to wyjątkowy przypadek, zwłaszcza jeśli większość aktualizacji i wyborów opiera się na wartościach PK.

Odłożenie na bok:

• Wojny religijne (Google surogat kontra klucz naturalny)
• Osobny problem dotyczący tego, jakie indeksy klastrowe należy zdefiniować w tabelach
• Wykonalność buforowania wszystkich danych

Pod warunkiem, że używasz zbiorczego usuwania / aktualizacji w stosownych przypadkach i masz indeksy do obsługi takich operacji, nie sądzę, abyś miał kłopoty z powodu stosowanego standardu PK.
Możliwe, że jeśli później EF będzie generować zapytania z łączeniami itp., Nie będą one tak wydajne, jak w przypadku repozytorium opartego na kluczach naturalnych, ale nie wiem wystarczająco dużo o tym obszarze, aby powiedzieć to na pewno.

Masz kilka czynników, które pomogą ci poprowadzić,

1. Definicja i specyfikacja

   Jeśli coś jest zdefiniowane jako wyjątkowe przez zadanie lub prawa fizyki, marnujesz swój czas na klucz zastępczy.

2. Wyjątkowość.

   Aby zachować zdrowie psychiczne, połączenia i funkcje bazy danych wyższego poziomu, potrzebujesz: (a) unikalnej kolumny, (b) unikalnej serii kolumn

   Wszystkie wystarczająco znormalizowane schematy (1NF) zapewniają jeden z poniższych. Jeśli nie, zawsze powinieneś je stworzyć. Jeśli masz listę osób ustawionych na ochotniczą niedzielę, która zawiera nazwisko i imię, będziesz chciał wiedzieć, kiedy masz dwóch Joe Bobs.

3. Wdrożenie i optymalizacja.

   Int jest zwykle małą formą danych, która jest szybka do porównania i równości. Porównaj to z łańcuchem Unicode, którego sortowanie może zależeć od ustawień regionalnych (lokalizacja i język). Przechowywanie 4242 w ciągu ASCII / UTF8 to 4 bajty. Przechowując go jako liczbę całkowitą mieści się w 2 bajtach.

Jeśli chodzi o wady, masz kilka czynników.

1. Zamieszanie i dwuznaczność.

   1. Wpis na blogu Aarona Bertranda dobrze to podsumowuje. Nie jest to samo dokumentowanie posiadania OrderID według specyfikacji i zadania, a następnie narzucanie „ OrderID ” poprzez implementację bazy danych. Czasami trzeba to wyjaśnić lub stworzyć konwencję, ale może to wprowadzić zamieszanie.

2. Przestrzeń.

   Liczby całkowite nadal dodają spację do wiersza. A jeśli ich nie używasz, nie ma sensu.

3. Grupowanie.

   Możesz zamówić swoje dane tylko w jeden sposób. Jeśli narzucisz klucz zastępczy, który nie jest potrzebny, czy klastrujesz w ten sposób, czy w sposób naturalny?