Klucz główny INT i GUID - porównanie

Różnice pomiędzy kluczem INT i GUID

Wśród projektantów i programistów baz danych co jakiś czas wybucha dyskusja na temat wyższości kluczy całkowitych nad kluczami typu GUID (ang Globally Unique IDentifier) i odwrotnie. Co jest lepsze? Skoro pojawiają się dyskusje to można mieć wręcz pewność, że nie ma jednoznacznej odpowiedzi na to pytanie. Ja też nie dam jednoznacznej odpowiedzi. Zamiast tego zajmę się poruszanymi w dyskusjach wadami i zaletami obu rozwiązań.

Pierwsza różnica jest dość oczywista i dotyczy struktury obu typów: INT to liczba całkowita, GUID, a w zasadzie uniqueidentifier, jest globalnym identyfikatorem, teoretycznie niepowtarzalnymWiadomo, że GUID zajmuje 16 bajtów i istnieje jakiś algorytm generujący kolejne wartości. Nawet gdyby algorytm działał idealnie i każde generowanie dałoby inny rezultat, dawałoby to 2¹²⁸ różnych kombinacji. Jest to, matematycznie, górne ograniczenie liczby różnych wartości.. Drugą różnicą, niepodważalną, widoczną od samego początku, jest rozmiar obu typów. typ int zajmuje 4 bajty, typ uniqueidentifier zajmuje 16 bajtów. Można to pokazać wykonując następujące zapytania:

Otrzymamy następujące rezultaty:

Pokazane w tym podpunkcie różnice są widoczne od razu. Pozostałe mogą być konsekwencją rozmiaru danych lub wynikać z zupełnie innych właściwości obu typów.

Przygotowanie tabeli testowej

Różne zależności najlepiej pokazać na konkretnym przykładzie. Nie inaczej będzie tym razem. Stwórzmy sobie dwie tabele, jedna będzie miała klucz typu int i wartość typu uniqueidentifier, druga natomiast klucz typu uniqueidentifier i taką samą wartość. Żeby nie zajmować się danymi, wszystkie wartości będą wstawiane domyślnie. Każda tabela będzie miała po 500000 rekordów:

Przykład tego nie pokaże ale powiem od razu, że tabela z typem int tworzy się znacznie szybciej. Przede wszystkim jest mniejsza, ale to nie wszystko! O tym jednak za chwilę.

Wpływ typu na rozmiar indeksu

Sam rozmiar jednej z wielu kolumn tabeli może być czasami pomijalny. Jeżeli tabela ma kilkanaście kolumn i kilka z nich może przechowywać wartości tekstowe, wtedy takie dodatkowe 12 bajtów różnicy na typie uniqueidentifier nie jest szczególnie istotne. Chodzi jednak o to, że większość zapytań korzysta z klucza - czytaj głównego indeksu powiązanego z tym kluczem (zobacz też: Indeksy w SQL Server - podstawy teoretyczne). Ale to nie wszystko - indeks główny, powiązany z kluczem głównym, określa najczęściej porządek ułożenia wszystkich danych tabeli (indeks typu CLUSTERED). To dalej nie wszystko - skoro określa porządek wszystkich danych, jest też wskaźnikiem (ang. Key Lookup) wykorzystywanym we wszystkich pozostałych indeksach typu NONCLUSTERED. Jest zatem dołączany niejawnie do wszystkich innych indeksów zwiększając przy okazji także ich rozmiary.

Popatrzmy zatem na rozmiary indeksów istniejących w pokazanych tabelach:

Wykonując zapytanie otrzymamy następujący rezultat:

Rozmiar nie jest tak łatwy do wyliczenia ręcznie, bo wymaga znajomości budowy B-drzew wykorzystywanych wewnętrznie przez SQL Server. Nie zmienia to jednak faktu, że główny indeks tabeli z kluczem int jest dużo mniejszy, ponad dwa razy, od indeksu na typie uniqueidentifier. Większy indeks to więcej stron do przeskanowania, większy klucz indeksu to mniej kluczy znajdujących się na jednej stronie. To wszystko w konsekwencji daje nam zwiększoną liczbę odczytów wykonywanych przy różnych operacjach.

Fizyczne ułożenie danych

To, że z punktu widzenia użytkownika bazy wszystko jest jedną tabelą to zasługa implementacji. SQL Server, ale także inne silniki baz danych pozwalają korzystać z języka zapytań SQL - języka znormalizowanego, stanowiącego logiczną (nie fizyczną! )warstwę, ułożoną nad logiczną (nie fizyczną!) warstwą relacji i zbiorów. Te logiczne warstwy muszą jednak w którymś momencie sięgnąć do fizycznych urządzeń, prawdziwie zapisać dane na dysku. To tutaj dzieją się rzeczy ciekawe. Przyjrzyjmy się układowi stron rozważanych tabel:

Otrzymamy następujący rezultat:

Warto zwrócić uwagę na kilka rzeczy. Po pierwsze, liczba stron wymaganych do przeskanowania indeksu (Pages Scanned). Jest to liczba porównywalna z rozmiarem samego indeksu i wynika głownie z tego rozmiaru. Drugim parametrem jest liczba przeskanowanych obszarów (Extents Scanned). Obszar taki jest równoważny 8 stronom pamięci, każda po 8 kB - w sumie taki obszar zajmuje 64 kB. Wartość ta jest proporcjonalna do rozmiaru indeksu. Kolejna wartość określa liczbę skoków pomiędzy takimi obszarami (Extent Switches). Oczywiście im mniej takich skoków, tym lepiej. Widać tutaj, że dla tabeli z typem int tych skoków jest mało, a w przypadku typu uniqueidentifier bardzo dużo. Oznacza to, że strony z danymi są porozrzucane w różnych obszarach. Analizujmy jednak dalej. Kolejna opcja (Scan Density) określa stosunek minimalnej liczby przetwarzanych obszarów do rzeczywistej liczby przetwarzanych obszarów (obszar może być przetwarzany kilka razy podczas jednego skanowania). Wartość bliska 100% określa idealne uporządkowanie, wartości mniejsze określają stopień nieciągłości danych.

Ważnym zagadnieniem jest też fragmentacja danych. Wartości te ujęte są w dwóch kolejnych atrybutach. Fragmentacja stron (Logical Scan Fragmentation) określa stosunek liczby stron, które w rozważanych obszarach nie znajdują się zaraz po poprzedzającej je stronie. Im mniejszy współczynnik, tym lepiej. Gdy strony są obok siebie SQL Server może stosować operację wyprzedzonego odczytu (ang. read-ahead read) jeszcze bardziej optymalizując przetwarzanie zapytania. To dlatego też w przypadku defragmentacji zaleca się reorganizację lub defragmentację indeksu (REBUILD i REORGANIZE INDEX). Fragmentacja obszarów (Extent Scan Fragmentation) jest tym samym parametrem co fragmentacja stron, ale na poziomie obszarów. Jest to jedyny przypadek, w którym indeks na kolumnie uniqueidentifier ma lepsze parametry. Spowodowane jest to tym, że podczas operacji wstawiania indeks jest wielokrotnie przekształcany. Gdy rekord wstawiany jest w środek indeksu następują podziały stron, a czasami także podziały całych obszarów. Przy okazji tych podziałów można coś poukładać.

Ostatnie dwa parametry określają średnią liczbę wolnych bajtów na stronie pamięci (Avg. Bytes Free per Page) oraz średnią zajętość strony (Avg. Page Density (full)). Pierwszy parametr powinien być jak najmniejszy, a drugi jak największy (mniej stron do przetwarzania) - za wyjątkiem sytuacji, w których spodziewamy się częstego wstawiania do środka indeksu nowych wartościGdy wartości wstawiane są na koniec indeksu nie m żadnych podziałów. Gdy strona się zapełni, przydzielana jest nowa, pusta, w miarę możliwości w ramach tego samego obszaru.. Jednak nawet w takich przypadkach najrozsądniejsze okazuje się ustawienie wypełnienia strony bliskie 100%. Nie po to kupujemy duże dyski, żeby przechowywać puste obszary.

Unikanie fragmentacji typu uniqueidentifier

Duża fragmentacja indeksu na typie uniqueidentifier powstaje z konieczności przechowywania posortowanych danych. Wyobraźmy sobie, że mamy tablicę. Ponadto, pierwsze dziesięć elementów ułożone jest według alfabetu i zajmuje pierwsze dziesięć pozycji. Jak wstawić element do środka zachowując jednocześnie właściwy porządek? Trzeba zrobić miejsce na pozycji szóstej, a całą resztę, z większymi indeksami, przenieść o jeden dalej. Trudno wyobrazić sobie takie przenoszenie danych, gdy w bazie mamy gigabajty. Jeżeli takie przenosiny można zrealizować w ramach strony - wszystko jest w porządku. Gdy jest to niemożliwe - pojawiają się podziały stron. Na jednej stronie jest połowa elementów z tym jednym nowym, na innej, przeniesionej w wolne miejsce, druga połowa. Otrzymujemy dwie strony wypełnione w połowie. To raz. Gdy stron jest więcej, są one poukładane i dzielona strona (1A) ma obok siebie inną stronę (2), ta nowa ląduje w pierwsze wolne miejsce (1B). Strony przestają trzymać porządek. Z sekwencyjnego układu strona(1)-strona(2) powstaje układ strona(1A)-strona(2)-strona(1B). Dla liniowego skanowania lepszy jest układ strona(1A)-strona(1B)-strona(2). Dlatego lepiej wstawiać strony na końcu. Po pierwsze nie powstają dziury, a po drugie, strony nie są przestawiane. Typ uniqueidentifier jest z założenia losowy, niesekwencyjny. Aby jakoś obejść te ograniczenia SQL Server pozwala na zastosowanie sekwencyjnego globalnego identyfikatora:

Spróbujmy teraz pobrać parametry indeksu:

Tym razem otrzymamy następujący rezultat:

Co zauważamy? Po pierwsze, ze względu na mniejszą liczbę podziałów, zmniejszył się rozmiar indeksu w stronach. Zmniejszyła się znacząco liczba przełączeń pomiędzy obszarami, zmniejszyła się fragmentacja (fragmentacja obszarów jest niska, bo wstawianie rekordów nie było przerywane innymi operacjami - w poprzednich przykładach rekordy do dwóch tabel były wstawiane naprzemiennie, bo to bardziej przypomina rzeczywiste przypadki), zwiększyła się za to pożądana gęstość zapisu.

Implementacja NEWSEQUENTIALID

Funckja NEWSEQUENTIALID jest o tyle ciekawa, że z jednej strony pomaga ograniczyć podziały stron i fragmentację, z drugiej zaś przełamuje założenia globalnych identyfikatorów - nietrywialną unikatowość. Jak działa NEWSEQUENTIALID? Wystarczy popatrzeć na wartości klucza:

Spośród wielu wartości wybrałem dziesięć:

Wyraźnie widać, że sekwencyjny, globalny i unikalny identyfikator (pierwsza kolumna) zwiększa swoją wartość o 1 w każdym przebiegu. Nie ma tu losowości. Łatwo wskazać kolejną wartość, która będzie wynosiła A238FB3A-6241-E311-8BA8-84A6C8E5ED3A. Cała magia i tajemniczość sekwencyjnego, globalnego i unikalnego identyfikatora została rozwiązana.

Z techniczno-wydajnościowej strony to wszystko. Pozostałe różnice dotyczą wygody, nieco innej funkcjonalności, pewnych pozytywnych efektów ubocznych.

Zalety uniqueidentifier

Unikalność GUID na poziomie serwera

Zastosowanie typu uniqueidentifier może być podyktowane pewnymi rozwiązaniami biznesowymi. O ile typ int jest unikatowy w ramach tabeli, o tyle uniqueidentifier jest unikatowy na poziomie całego serwera. Pozwala to między innymi śledzić zmiany w bazie za pomocą jednego klucza. Wystarczy, że mamy pod ręką jeden GUID - na jego podstawie jesteśmy w stanie określić instancję, bazę danych i tabelę wpomnianego rekordu. Nie jest to łatwe, ale da się.

Możliwość stosowania jako części adresu URL

GUID jest na tyle losowy (zwykły, nie sekwencyjny), że może być z powodzeniem stosowany do adresów URL. Typ całkowity też może być w adresie umieszczony, ale, o ile nie jest losowy, zdradza sposób i liczbę przechowywanych rekordów. Napastnik w postaci bota widząc adres w postaci ../index?id=10 ma prawie pewność, że można wysłać żądanie ../index?id=9. W przypadku parametru typu GUID już takiej pewności mieć nie może. Z drugiej strony, jeszcze lepszym rozwiązaniem jest postać tekstowa adresu URL.

Ułatwione scalanie danych z różnych źródeł

Wstawiłem ten podpunkt zdając sobie sprawę z różnych interpretacji tego samego zdania. Klucz typu int z opcją IDENTITY jest trudny w przenoszeniu ze względu na automatyczną numerację. Wstawianie takich rekordów pozostających w relacji z innymi jest niekiedy bardzo trudnym zadaniem. Każdy kto już to kiedyś wykonywał wie o czym mówię. Należy powyłączać automatyczną numerację, zadbać o zgodność kluczy obcych. W przypadku kluczy typu GUID problem znika sam.

Z drugiej strony, scalanie danych wymaga często porównywania innych kolumn, które pełnią funkcję klucza naturalnego. Przypuśćmy, że dwóch agentów wpisało klienta XXX z numerem pesel 00000000000. Każdy z tych rekordów dostał jakiś GUID. Na czym polega w takim przypadku scalanie danych? Czy na tym, że w tabeli docelowej mamy dwa rekordy tego samego klienta? Czy może na tym, że to w rzeczywistości jedna osoba i rekord ma być jeden? Ja skłaniałbym się do tej drugiej opcji - scalania na bazie klucza naturalnego. GUID i liczba całkowita to klucze sztuczne.

Możliwość korzystania ze specyficznych funkcji

Wiele mechanizmów SQL Server wręcz wymaga stosowania klucza typu uniqueidentifier. Są to między innymi mechanizmy replikacji oraz FILESTREAM (więcej o FILESTREAM można znaleźć tutaj: FILESTREAM w Sql Server). Jeżeli zatem zamierzamy stosować rozwiązanie wymagający klucza typu GUID, nie mamy wyjścia. Trzeba użyć GUID.

Możliwość generowania GUID przez aplikacje klienckie.

Wartość całkowita z opcją IDENTITY generowana jest przez serwer. W pokazanym przykładzie GUID też jest generowany przez serwer. Nic nie stoi na przeszkodzie, aby ten GUID wygenerować na kliencie. Przecież jest on globalnie unikalny! Zapis w bazie wymaga informacji zwrotnej w postaci przydzielonej wartości identyfikatora. Bez tego nie będziemy wiedzieli, jaki klucz ma nasz nowy rekord. W wielu przypadkach taką informację można łatwo zwrócić, ale niektóre rozwiązania, na przykład wykorzystujące kolejkowanie operacji, uniemożliwiają jakiekolwiek gospodarowanie obiektem opóźniając sam proces zapisu. Dopóki klient nie dostanie informacji zwrotnej, dość ryzykowne jest wykonywanie jakiejkolwiek operacji na obiekcie. Narzędzia typu O/RM stosują często jako klucz typ int i na jego podstawie decydują o wykonywanej operacji. Jeżeli klucz ma wartość domyślną, 0, wykonywany jest zapis. Jeżeli klucz ma inna wartość, wykonywana jest operacja aktualizacji. Jeszcze inaczej: jeżeli zapisany rekord miałby po zapisie w kluczu wartość 0 i zechcielibyśmy go usunąć? Baza nie znajdzie rekordu, a sama operacja zakończy się w najlepszym wypadku niepowodzeniem. W najlepszym, bo w gorszym nie dostaniemy wyjątku, a rekord dalej będzie istniał. Użycie typu uniqueidentifier pozwala ominąć problem, bo identyfikator nadany jest przez klienta i tylko on wie o jego istnieniu. O ile zachowana jest kolejność operacji, wszystko będzie działał dobrze.

Wady uniqueidentifier

Zwiększony rozmiar kluczy

Problem rozmiarów kluczy opisany został we wcześniejszej części artykułu: rozmiar indeksu.

Problem z indeksami i ich fragmentacją

Problem, jako jeden z najpoważniejszych, także został opisany w oddzielnej sekcji: indeksy i fragmentacja.

Mało intuicyjne zapytania

Problem ten dotyczy wygody i czytelności tworzenia zapytań. Wystarczy popatrzeć na dwa poniższe zapytania i wszystko stanie się jasne:

Sam zapis to nie wszystko. Czytanie logów z takimi szeregami znaków, przekazywanie innym osobom odpowiedzialnym za bazy namiarów na podejrzane rekordy. To wszystko stwarza dużo większe problemy w przypadku typu uniqueidentifier.

Podsumowanie

Dyskusje na temat wad i zalet stosowania typu uniqueidentifier zamiast typów całkowitych będą się pewnie toczyły dalej. Ja, w miarę swoich możliwości, przedstawiłem te miejsca, w których należy szukać największych różnic oraz te miejsca, w których uniqueidentifier niesie ze sobą jakieś zagrożenia. Rozmiary tabeli z kluczem typu uniqueidentifier w pokazanym przykładzie są przejaskrawione - lepiej w ten sposób uwypuklić różnice. W rzeczywistości różnica nie będzie aż tak znacząca. Faktem jest jednak, że ta różnica będzie się ujawniała w wielu różnych miejscach - w rozmiarze tabel, indeksów, w pamięci podręcznej cache, w rozmiarach plików kopii zapasowych, w pamięci aplikacji klienckich. Bardziej istotny jest problem fragmentacji, bo narasta on wraz ze zwiększaniem się rozmiaru tabeli. Pamiętajmy też, że projekt bazy danych przenosi się również na inne warstwy - jeżeli w bazie będzie GUID, programiści aplikacji klienckich będą musieli ten GUID przyjąć. Łatwiej też znaleźć nieprawidłowy znak w liczbie całkowitej niż w znacznie dłuższym łańcuchu GUID. Tradycyjnie - wszystko zależy od rozwiązania, zastosowanych technologii, algorytmów, liczby przetwarzanych transakcji i osobistych preferencji.

Pojedynek int vs. uniqueidentifier uważam za nierozstrzygnięty. Zachęcam do dzielenia się ciekawymi rozwiązaniami wykorzystującymi GUID oraz, z drugiej strony, rozwiązaniami, w których nieprawidłowo użyty GUID wychodzi bokiem. Być może pominąłem jakiś ważny argument przechylający szalę? W takim przypadku również proszę o ślad w komentarzach.

Kategoria:IndeksyOptymalizacja SQLSQL Server