5.1 Wprowadzenie

Internet jest pełen dogmatów dotyczących tego, jak należy wybierać i używać kluczy w relacyjnych bazach danych. Czasami spory zamieniają się nawet w holivary: czy używać kluczy naturalnych czy sztucznych? Automatyczne zwiększanie liczb całkowitych lub identyfikatorów UUID?

Po przeczytaniu sześćdziesięciu czterech artykułów, przejrzeniu sekcji pięciu książek i zadaniu tony pytań na IRC i StackOverflow, ja (Joe „begriffs” Nelson, autor oryginalnego artykułu) wydaje się, że ułożyłem kawałki układanki w całość i może teraz pogodzić przeciwników. Wiele kluczowych sporów tak naprawdę wynika z niezrozumienia czyjegoś punktu widzenia.

Rozłóżmy problem na czynniki pierwsze i złóżmy go z powrotem. Najpierw zadajmy sobie pytanie – co to jest „klucz”?

Zapomnijmy na chwilę o kluczach podstawowych, interesuje nas bardziej ogólna idea. Klucz to kolumna (kolumna) lub kolumny, które nie mają zduplikowanych wartości w wierszach . Ponadto kolumny muszą być nieredukowalnie unikalne, tj. Żaden podzbiór kolumn nie jest tak unikalny.

Ale najpierw trochę teorii:

główny klucz

Główny kluczbezpośrednio używany do identyfikacji wierszy w tabeli. Musi spełniać następujące ograniczenia:

  • Klucz podstawowy musi być cały czas unikalny .
  • Musi być zawsze obecny w tabeli i mieć wartość.
  • Nie powinien często zmieniać swojej wartości. Idealnie nie powinno to w ogóle zmieniać wartości .

Zazwyczaj klucz podstawowy reprezentuje pojedynczą kolumnę tabeli, ale może to być również klucz złożony składający się z wielu kolumn.

Klucz złożony

Niestandardowy klucz- kombinacja atrybutów (kolumn), które jednoznacznie identyfikują każdy wiersz tabeli. Mogą to być wszystkie kolumny, kilka i jedna. W takim przypadku wiersze zawierające wartości tych atrybutów nie powinny się powtarzać.

Potencjalny klucz

Klucz kandydata- reprezentuje minimalny klucz złożony relacji (tabeli), czyli zestaw atrybutów, który spełnia szereg warunków:

  • Nieredukowalność : Nie można go zredukować, zawiera on minimalny możliwy zestaw atrybutów.
  • Unikalność : Musi mieć unikalne wartości niezależnie od zmiany wiersza.
  • Obecność wartości : nie może mieć wartości null, tzn. musi mieć wartość.

5.2 Ciekawy przypadek kluczy podstawowych

To, co właśnie nazwaliśmy „kluczami” w poprzedniej sekcji, jest powszechnie określane jako „klucze kandydujące”. Termin „kandydat” oznacza, że ​​wszystkie takie klucze konkurują o honorową rolę „klucza głównego” (klucza głównego), a pozostałym przypisuje się „klucze alternatywne” (klucze alternatywne).

Zajęło trochę czasu, zanim implementacje SQL przezwyciężyły niezgodność między kluczami a modelem relacyjnym, a najwcześniejsze bazy danych były nastawione na niskopoziomową koncepcję klucza podstawowego. Klucze podstawowe w takich bazach danych były wymagane do identyfikacji fizycznej lokalizacji wiersza na sekwencyjnych nośnikach pamięci. Oto jak wyjaśnia to Joe Celko:

Termin „klucz” oznaczał klucz sortowania plików, który był potrzebny do wykonywania jakichkolwiek operacji przetwarzania w sekwencyjnym systemie plików. Zestaw kart perforowanych odczytywano w jednej i tylko jednej kolejności; powrót był niemożliwy. Wczesne napędy taśmowe naśladowały to samo zachowanie i nie pozwalały na dostęp dwukierunkowy. Oznacza to, że oryginalny Sybase SQL Server wymagał „przewinięcia” tabeli do początku, aby odczytać poprzedni wiersz.

We współczesnym SQL nie trzeba skupiać się na fizycznej reprezentacji informacji, tabele modelują relacje, a wewnętrzna kolejność wierszy nie jest w ogóle istotna. Jednak nawet teraz serwer SQL domyślnie tworzy indeks klastrowy dla kluczy głównych i zgodnie ze starą tradycją fizycznie porządkuje kolejność wierszy.

W większości baz danych klucze podstawowe należą do przeszłości i zapewniają niewiele więcej niż odbicie lub fizyczną lokalizację. Na przykład w tabeli PostgreSQL zadeklarowanie klucza podstawowego automatycznie wymusza ograniczenie NOT NULLi definiuje domyślny klucz obcy. Ponadto preferowanymi kolumnami dla operatora są klucze podstawowe JOIN.

Klucz podstawowy nie zastępuje możliwości zadeklarowania innych kluczy. Jednocześnie, jeśli żaden klucz nie jest przypisany jako podstawowy, tabela nadal będzie działać poprawnie. W każdym razie piorun cię nie uderzy.

Java university

5.3 Znajdowanie kluczy naturalnych

Omówione powyżej klucze nazywane są „naturalnymi”, ponieważ są to właściwości modelowanego obiektu, które same w sobie są interesujące, nawet jeśli nikt nie chce z nich zrobić klucza.

Pierwszą rzeczą, o której należy pamiętać podczas sprawdzania tabeli pod kątem możliwych kluczy naturalnych, jest staranie się nie być zbyt mądrym. Użytkownik sqlvogel na StackExchange daje następującą radę:

Niektóre osoby mają trudności z wyborem „naturalnego” klucza, ponieważ wymyślają hipotetyczne sytuacje, w których dany klucz może nie być unikalny. Nie rozumieją samego sensu zadania. Znaczenie klucza polega na zdefiniowaniu reguły, że atrybuty powinny i zawsze będą unikalne w określonej tabeli w danym momencie. Tabela zawiera dane w określonym i zrozumiałym kontekście (w „obszarze tematycznym” lub „obszarze dyskursu”), a jedynym znaczeniem jest zastosowanie ograniczenia w tym konkretnym obszarze.

Praktyka pokazuje, że konieczne jest wprowadzenie kluczowego ograniczenia, gdy kolumna jest unikalna z dostępnymi wartościami i tak pozostanie w prawdopodobnych scenariuszach. A jeśli to konieczne, ograniczenie można usunąć (jeśli ci to przeszkadza, poniżej porozmawiamy o stabilności klucza).

Na przykład baza danych członków klubu hobbystycznego może mieć unikalność w dwóch kolumnach - first_name, last_name. Przy niewielkiej ilości danych duplikaty są mało prawdopodobne, a zanim pojawi się prawdziwy konflikt, całkiem rozsądne jest użycie takiego klucza.

W miarę powiększania się bazy danych i zwiększania ilości informacji wybór klucza naturalnego może stać się trudniejszy. Przechowywane przez nas dane są uproszczeniem rzeczywistości zewnętrznej i nie zawierają pewnych aspektów wyróżniających obiekty na świecie, takich jak ich współrzędne zmieniające się w czasie. Jeśli przedmiot nie ma żadnego kodu, jak odróżnić dwie puszki napoju lub dwa pudełka płatków owsianych po ich rozmieszczeniu przestrzennym, niewielkich różnicach w wadze lub opakowaniu?

Dlatego organy normalizacyjne tworzą i stosują znaki wyróżniające na produktach. Pojazdy są opatrzone numerem identyfikacyjnym pojazdu (VIN) , książki są drukowane z numerami ISBN , a opakowania żywności mają UPC . Możesz sprzeciwić się temu, że liczby te nie wydają się naturalne. Dlaczego więc nazywam je kluczami naturalnymi?

Naturalność lub sztuczność unikalnych właściwości w bazie danych zależy od świata zewnętrznego. Klucz, który był sztuczny, gdy był tworzony w organie normalizacyjnym lub agencji rządowej, staje się dla nas naturalny, ponieważ staje się standardem na całym świecie i / lub jest drukowany na przedmiotach.

Istnieje wiele branżowych, publicznych i międzynarodowych standardów dotyczących różnych przedmiotów, w tym walut, języków, instrumentów finansowych, chemikaliów i diagnoz medycznych. Oto niektóre wartości, które są często używane jako klucze naturalne:

  • kody krajów ISO 3166
  • kody językowe ISO 639
  • Kody walut zgodnie z ISO 4217
  • Symbole giełdowe ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • nazwy logowania
  • adresy e-mail
  • numery pokoi
  • sieciowy adres mac
  • szerokość i długość geograficzna dla punktów na powierzchni Ziemi

Zalecam deklarowanie kluczy, gdy tylko jest to możliwe i rozsądne, może nawet wiele kluczy na tabelę. Pamiętaj jednak, że wszystkie powyższe mogą mieć wyjątki.

  • Nie każdy ma adres e-mail, chociaż może to być dopuszczalne w niektórych warunkach bazy danych. Ponadto ludzie od czasu do czasu zmieniają swoje adresy e-mail. (Więcej o stabilności klucza później).
  • Symbole giełdowe ISIN zmieniają się od czasu do czasu, na przykład symbole GOOG i GOOGL nie opisują dokładnie reorganizacji firmy z Google na Alphabet. Czasami może pojawić się zamieszanie, jak w przypadku TWTR i TWTRQ, niektórzy inwestorzy omyłkowo kupili to drugie podczas IPO na Twitterze.
  • Numery ubezpieczenia społecznego są używane tylko przez obywateli USA, mają ograniczenia dotyczące prywatności i są ponownie wykorzystywane po śmierci. Ponadto po kradzieży dokumentów ludzie mogą uzyskać nowe numery. Wreszcie ten sam numer może identyfikować zarówno osobę, jak i identyfikator podatku dochodowego.
  • Kody pocztowe to kiepski wybór dla miast. Niektóre miasta mają wspólny indeks lub odwrotnie, w jednym mieście jest kilka indeksów.

5.4 Sztuczne klucze

Biorąc pod uwagę, że kluczem jest kolumna z unikalnymi wartościami w każdym wierszu, jednym ze sposobów na jego utworzenie jest oszukiwanie - w każdym wierszu można wpisać fikcyjne unikalne wartości. Są to sztuczne klucze: wymyślony kod używany do odwoływania się do danych lub obiektów.

Bardzo ważne jest, aby kod był generowany z samej bazy danych i nie był znany nikomu poza użytkownikami bazy. To właśnie odróżnia sztuczne klucze od standardowych kluczy naturalnych.

Podczas gdy klucze naturalne mają tę zaletę, że chronią przed zduplikowanymi lub niespójnymi wierszami w tabeli, klucze sztuczne są przydatne, ponieważ ułatwiają ludziom lub innym systemom odwoływanie się do wiersza oraz przyspieszają wyszukiwanie i łączenie, ponieważ nie używają porównania łańcuchowe (lub wielokolumnowe).klucze.

Surogaci

Sztuczne klucze są używane jako kotwice - bez względu na to, jak zmieniają się reguły i kolumny, jeden wiersz zawsze można zidentyfikować w ten sam sposób. Sztuczny klucz używany do tego celu nazywany jest „kluczem zastępczym” i wymaga szczególnej uwagi. Poniżej rozważymy surogaty.

Niezastępcze klucze sztuczne są przydatne do odwoływania się do wiersza spoza bazy danych. Sztuczny klucz krótko identyfikuje dane lub obiekt: może być podany jako adres URL, dołączony do faktury, podyktowany przez telefon, uzyskany z banku lub wydrukowany na tablicy rejestracyjnej. (Tablica rejestracyjna samochodu to dla nas naturalny klucz, ale zaprojektowany przez rząd jako sztuczny klucz).

Klucze syntetyczne należy dobierać z uwzględnieniem możliwych sposobów transmisji, aby zminimalizować literówki i błędy. Należy zauważyć, że klucz można wypowiedzieć, przeczytać wydrukowany, wysłać SMS-em, odczytać odręcznie, wpisać z klawiatury i osadzić w adresie URL. Ponadto niektóre sztuczne klucze, takie jak numery kart kredytowych, zawierają sumę kontrolną, dzięki czemu w przypadku wystąpienia pewnych błędów można je przynajmniej rozpoznać.

Przykłady:

  • W przypadku amerykańskich tablic rejestracyjnych istnieją zasady dotyczące używania niejednoznacznych znaków, takich jak O i 0.
  • Szpitale i apteki muszą zachować szczególną ostrożność, biorąc pod uwagę charakter pisma lekarzy.
  • Czy wysyłasz kod potwierdzający SMS-em? Nie wykraczaj poza zestaw znaków GSM 03.38.
  • W przeciwieństwie do Base64, który koduje dowolne dane bajtowe, Base32 używa ograniczonego zestawu znaków, który jest wygodny w użyciu i obsłudze na starszych systemach komputerowych.
  • Proquinty to czytelne, zapisywalne i wymawialne identyfikatory. Są to QUINT-uplety jednoznacznie rozumianych spółgłosek i samogłosek.

Pamiętaj, że gdy tylko przedstawisz światu swój sztuczny klucz, ludzie dziwnie zaczną zwracać na niego szczególną uwagę. Wystarczy spojrzeć na tablice rejestracyjne „złodziei” lub na system tworzenia czytelnych identyfikatorów, który stał się niesławnym automatycznym generatorem przekleństw.

Nawet jeśli ograniczymy się do klawiszy numerycznych, istnieją tabu, takie jak trzynaste piętro. Podczas gdy proquinty mają większą gęstość informacji na wypowiadaną sylabę, liczby są również dobre pod wieloma względami: w adresach URL, klawiaturach pinowych i odręcznych notatkach, o ile odbiorca wie, że kluczem są tylko liczby.

Należy jednak pamiętać, że nie należy stosować kolejności sekwencyjnej w publicznych kluczach numerycznych, ponieważ umożliwia to przeszukiwanie zasobów (/videos/1.mpeg, /videos/2.mpeg itd.), a także powoduje wyciek informacji o numerze dane. Nałóż siatkę Feistela na sekwencję liczb i zachowaj wyjątkowość, ukrywając kolejność liczb.

Jedynym argumentem przeciwko deklarowaniu dodatkowych kluczy jest to, że każdy nowy niesie ze sobą kolejny unikalny indeks i zwiększa koszt zapisu do tablicy. Oczywiście zależy to od tego, jak ważna jest dla Ciebie poprawność danych, ale najprawdopodobniej klucze nadal powinny być zadeklarowane.

Warto też zadeklarować kilka sztucznych kluczy, jeśli takie istnieją. Na przykład organizacja ma kandydatów do pracy (Aplikanci) i pracowników (Pracownicy). Każdy pracownik był kiedyś kandydatem i odnosi się do kandydatów za pomocą własnego identyfikatora, który powinien być również kluczem pracownika. Inny przykład, możesz ustawić identyfikator pracownika i nazwę logowania jako dwa klucze w Pracownicy.

5.5 Klucze zastępcze

Jak już wspomniano, ważny rodzaj sztucznego klucza nazywany jest „kluczem zastępczym”. Nie musi być zwięzły i przystępny jak inne sztuczne klucze, ale jest używany jako wewnętrzna etykieta, która zawsze identyfikuje ciąg. Jest używany w SQL, ale aplikacja nie ma do niego jawnego dostępu.

Jeśli znasz kolumny systemowe PostgreSQL, możesz myśleć o surogatach prawie jak o parametrze implementacji bazy danych (jak ctid), który jednak nigdy się nie zmienia. Wartość zastępcza jest wybierana raz na wiersz i nigdy później nie jest zmieniana.

Klucze zastępcze są świetne jako klucze obce, a ograniczenia kaskadowe muszą być określone, ON UPDATE RESTRICTaby dopasować niezmienność zastępczego.

Z drugiej strony klucze obce do kluczy współdzielonych publicznie powinny być oznaczone symbolem ON UPDATE CASCADE, aby zapewnić maksymalną elastyczność. Aktualizacja kaskadowa działa na tym samym poziomie izolacji, co otaczająca ją transakcja, więc nie martw się o problemy z współbieżnością — baza danych będzie działać, jeśli wybierzesz ścisły poziom izolacji.

Nie rób kluczy zastępczych „naturalnych”. Gdy pokażesz wartość klucza zastępczego użytkownikom końcowym lub, co gorsza, pozwolisz im pracować z tą wartością (szczególnie poprzez wyszukiwanie), skutecznie nadasz kluczowi wartość. Wtedy pokazany klucz z twojej bazy danych może stać się naturalnym kluczem w czyjejś bazie danych.

Zmuszanie systemów zewnętrznych do korzystania z innych sztucznych kluczy zaprojektowanych specjalnie do transmisji umożliwia nam zmianę tych kluczy w razie potrzeby w celu zaspokojenia zmieniających się potrzeb, przy jednoczesnym zachowaniu wewnętrznej integralności referencyjnej za pomocą surogatów.

Automatyczne zwiększanie INT/BIGINT

Najczęstszym zastosowaniem kluczy zastępczych jest automatycznie zwiększająca się kolumna „dużego numeru seryjnego” , znana również jako TOŻSAMOŚĆ . (W rzeczywistości PostgreSQL 10 obsługuje teraz konstrukcję IDENTITY, podobnie jak Oracle, patrz CREATE TABLE.)

Uważam jednak, że liczba całkowita z automatycznym zwiększaniem jest złym wyborem dla kluczy zastępczych. Ta opinia jest niepopularna, więc pozwólcie, że wyjaśnię.

Wady kluczy seryjnych:

  • Jeśli wszystkie sekwencje zaczynają się od 1 i stopniowo rosną, wiersze z różnych tabel będą miały te same wartości klucza. Ta opcja nie jest idealna, nadal lepiej jest używać rozłącznych zestawów kluczy w tabelach, aby np. zapytania nie mogły przypadkowo pomylić stałych JOINi zwrócić nieoczekiwanych wyników. (Alternatywnie, aby upewnić się, że nie ma przecięć, można skonstruować każdą sekwencję z wielokrotności różnych liczb pierwszych, ale byłoby to dość pracochłonne.)
  • Wezwanie nextval() do wygenerowania sekwencji w dzisiejszym rozproszonym SQL powoduje, że cały system nie skaluje się dobrze.
  • Konsumpcja danych z bazy danych, która również korzystała z kluczy sekwencyjnych, spowoduje konflikty, ponieważ wartości sekwencyjne nie będą unikalne w różnych systemach.
  • Z filozoficznego punktu widzenia sekwencyjny wzrost liczby jest związany ze starymi systemami, w których implikowana była kolejność linii. Jeśli chcesz teraz uporządkować wiersze, zrób to jawnie za pomocą kolumny znacznika czasu lub czegoś, co ma sens w twoich danych. W przeciwnym razie naruszana jest pierwsza postać normalna.
  • Słaby powód, ale te krótkie identyfikatory kuszą, by komuś powiedzieć.

UUID

Spójrzmy na inną opcję: użycie dużych liczb całkowitych (128-bitowych) generowanych według losowego wzorca. Algorytmy do generowania takich uniwersalnych unikalnych identyfikatorów (UUID) mają bardzo niskie prawdopodobieństwo dwukrotnego wybrania tej samej wartości, nawet jeśli działają na dwóch różnych procesorach w tym samym czasie.

W takim przypadku identyfikatory UUID wydają się naturalnym wyborem do użycia jako klucze zastępcze, prawda? Jeśli chcesz oznaczyć wiersze w unikalny sposób, nic nie przebije unikalnej etykiety!

Dlaczego więc nie wszyscy używają ich w PostgreSQL? Istnieje kilka wymyślonych powodów i jeden logiczny, który można obejść, i przedstawię testy porównawcze, aby zilustrować mój punkt widzenia.

Najpierw omówię daleko idące powody. Niektórzy uważają, że identyfikatory UUID to ciągi znaków, ponieważ są zapisywane w tradycyjnej notacji szesnastkowej z myślnikiem: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Rzeczywiście, niektóre bazy danych nie mają kompaktowego (128-bitowego) typu uuid, ale PostgreSQL ma rozmiar dwa bigint, tj. w porównaniu z ilością innych informacji w bazie danych, narzut jest znikomy.

UUID są również niesłusznie oskarżane o to, że są uciążliwe, ale kto je wymawia, wpisze lub przeczyta? Powiedzieliśmy, że pokazywanie sztucznych kluczy ma sens, ale nikt (z definicji) nie powinien widzieć zastępczego UUID. Możliwe, że UUID będzie obsługiwany przez programistę uruchamiającego polecenia SQL w psql w celu debugowania systemu, ale to wszystko. Deweloper może również odwoływać się do ciągów przy użyciu wygodniejszych kluczy, jeśli zostaną podane.

Prawdziwy problem z identyfikatorami UUID polega na tym, że wysoce losowe wartości prowadzą do wzmocnienia zapisu z powodu zapisywania całej strony w dzienniku zapisu z wyprzedzeniem (WAL) . Jednak spadek wydajności w rzeczywistości zależy od algorytmu generowania UUID.

Zmierzmy wzmocnienie zapisu . W rzeczywistości problem dotyczy starszych systemów plików. Gdy PostgreSQL zapisuje na dysk, zmienia „stronę” na dysku. Jeśli wyłączysz zasilanie komputera, większość systemów plików nadal zgłasza pomyślne zapisanie, zanim dane zostaną bezpiecznie zapisane na dysku. Jeśli PostgreSQL naiwnie uzna taką akcję za zakończoną, to baza danych zostanie uszkodzona podczas kolejnego startu systemu.

Ponieważ PostgreSQL nie może ufać większości systemów operacyjnych/systemów plików/konfiguracji dysków w zakresie zapewnienia ciągłości, baza danych zapisuje pełny stan zmienionej strony dysku w dzienniku zapisu z wyprzedzeniem, którego można użyć do odzyskania sprawności po ewentualnej awarii. Indeksowanie wysoce losowych wartości, takich jak identyfikatory UUID, zwykle obejmuje kilka różnych stron dysku i powoduje zapisanie pełnego rozmiaru strony (zwykle 4 lub 8 KB) w WAL dla każdego nowego wpisu. Jest to tak zwany zapis całostronicowy (ang. full-page write, FPW).

Niektóre algorytmy generowania UUID (takie jak „płatek śniegu” Twittera lub uuid_generate_v1() w rozszerzeniu uuid-ossp PostgreSQL) generują monotonicznie rosnące wartości na każdej maszynie. Takie podejście konsoliduje zapisy na mniejszej liczbie stron dysku i zmniejsza FPW.

5.6 Wnioski i zalecenia

Teraz, gdy poznaliśmy różne typy kluczy i ich zastosowania, chcę wymienić moje zalecenia dotyczące ich używania w bazach danych.

Dla każdego stołu:

  • Zdefiniuj i zadeklaruj wszystkie klucze naturalne.
  • Utwórz klucz zastępczy <table_name>_idtypu UUID z wartością domyślną uuid_generate_v1(). Możesz nawet oznaczyć go jako klucz podstawowy. Jeśli do tego identyfikatora dodasz nazwę tabeli, uprości to JOIN, tj. odbierać JOIN foo USING (bar_id)zamiast JOIN foo ON (foo.bar_id = bar.id). Nie przekazuj tego klucza klientom i nie udostępniaj go w ogóle poza bazą danych.
  • W przypadku tabel pośrednich, które przechodzą przez JOIN, zadeklaruj wszystkie kolumny klucza obcego jako pojedynczy złożony klucz podstawowy.
  • Opcjonalnie dodaj sztuczny klucz, którego można użyć w adresie URL lub innych wskazaniach odwołań do ciągu znaków. Użyj siatki Feistela lub pg_hashids , aby zamaskować automatycznie zwiększające się liczby całkowite.
  • Określ ograniczenie kaskadowe ON UPDATE RESTRICT, używając zastępczych identyfikatorów UUID jako kluczy obcych i sztucznych kluczy obcych ON UPDATE CASCADE. Wybierz naturalne klucze w oparciu o własną logikę.

Takie podejście zapewnia stabilność kluczy wewnętrznych, jednocześnie umożliwiając, a nawet chroniąc klucze naturalne. Ponadto widoczne sztuczne klucze nie przyczepiają się do niczego. Rozumiejąc wszystko poprawnie, nie można zawiesić się tylko na „kluczach podstawowych” i korzystać ze wszystkich możliwości korzystania z kluczy.