Indeksowanie identyfikatora GUID PK w programie SQL Server 2012

Moi programiści skonfigurowali swoją aplikację do używania GUID jako PK dla prawie wszystkich swoich tabel, a domyślnie SQL Server ustawił indeks klastrowy na tych PK.

System jest stosunkowo młody, a nasze największe tabele mają nieco ponad milion wierszy, ale przyglądamy się naszemu indeksowaniu i chcemy być w stanie szybko skalować, ponieważ może to być potrzebne w najbliższej przyszłości.

Tak więc moją pierwszą skłonnością było przeniesienie indeksu klastrowego do utworzonego pola, które jest dużą reprezentacją DateTime. Jednak jedynym sposobem, w jaki mogę uczynić CX unikalnym, byłoby dołączenie kolumny GUID do tego CX, ale zamówienie najpierw utworzone.

Czy spowodowałoby to, że klucz klastrowania byłby zbyt szeroki i czy poprawiłby wydajność zapisu? Odczyty są również ważne, ale zapisy są prawdopodobnie większym problemem w tym momencie.

Główne problemy z identyfikatorami GUID, zwłaszcza niesekwencyjnymi, to:

• Rozmiar klucza (16 bajtów vs. 4 bajty dla INT): Oznacza to, że przechowujesz 4-krotność ilości danych w kluczu wraz z dodatkową przestrzenią na dowolne indeksy, jeśli jest to indeks klastrowany.
• Fragmentacja indeksu: Defragmentacja niesekwencyjnej kolumny GUID jest praktycznie niemożliwa ze względu na całkowicie losowy charakter kluczowych wartości.

Co to oznacza dla twojej sytuacji? Wszystko sprowadza się do twojego projektu. Jeśli w twoim systemie chodzi po prostu o zapisywanie i nie martwisz się o odzyskiwanie danych, to podejście nakreślone przez Thomasa K jest dokładne. Należy jednak pamiętać, że realizując tę ​​strategię, powstaje wiele potencjalnych problemów związanych z odczytywaniem i przechowywaniem tych danych. Jak zauważa Jon Seigel , będziesz również zajmować więcej miejsca i zasadniczo mieć wzdęcia pamięci.

Podstawowym pytaniem wokół GUID jest to, jak konieczne są. Deweloperzy je lubią, ponieważ zapewniają globalną wyjątkowość, ale rzadko zdarza się, że taka unikalność jest konieczna. Weź jednak pod uwagę, że jeśli maksymalna liczba wartości jest mniejsza niż 2 147 483 647 (maksymalna wartość 4-bajtowej liczby całkowitej ze znakiem), prawdopodobnie nie używasz odpowiedniego typu danych dla swojego klucza. Nawet przy użyciu BIGINT (8 bajtów), twoja maksymalna wartość to 9 223 372,036,854,775,807. Zazwyczaj wystarcza to dla dowolnej nieglobalnej bazy danych (i wielu globalnych), jeśli potrzebujesz pewnej wartości auto-przyrostowej dla unikalnego klucza.

Wreszcie, o ile używasz sterty w porównaniu z indeksem klastrowym, jeśli czysto zapisujesz dane, sterty byłyby najbardziej wydajne, ponieważ minimalizujesz narzuty na wstawki. Jednak stosy w programie SQL Server są wyjątkowo nieefektywne w pobieraniu danych. Z mojego doświadczenia wynika, że ​​indeks klastrowany jest zawsze pożądany, jeśli masz okazję go zadeklarować. Zauważyłem, że dodanie do tabeli indeksu klastrowego (4 miliardy + rekordy) poprawia ogólną wydajność selekcji sześciokrotnie.

Dodatkowe informacje:

• GUIDY jako PODSTAWOWE KLUCZY i / lub klucz grupowania - Kimberly L. Tripp
• Miejsce na dysku jest tanie ... - Kimberly L. Tripp (czapka Zane'a )
• Debata GUID vs INT - Microsoft SQL Server Wskazówki i porady

Nie ma nic złego w GUID jako kluczach i klastrach w systemie OLTP (chyba że masz dużo indeksów na stole, które cierpią z powodu zwiększonego rozmiaru klastra). W rzeczywistości są one znacznie bardziej skalowalne niż kolumny TOŻSAMOŚCI.

Powszechnie uważa się, że GUID są wielkim problemem w SQL Server - w dużej mierze jest to po prostu zły. W rzeczywistości GUID może być znacznie bardziej skalowalny na urządzeniach z więcej niż około 8 rdzeniami:

• http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/
• http://www.microsoft.com/en-gb/download/details.aspx?id=26665 (Patrz sekcja na ostatniej stronie / końcowa)
• http://dangerousdba.blogspot.co.uk/2011/10/day-sequences-saved-world.html

Przykro mi, ale twoi programiści mają rację. Martw się o inne rzeczy, zanim zaczniesz martwić się o GUID.

No i na koniec: dlaczego chcesz indeks klastrów w pierwszej kolejności? Jeśli Twoim problemem jest system OLTP z wieloma małymi indeksami, prawdopodobnie lepiej będzie z kupą.

Zastanówmy się teraz, co fragmentacja (która wprowadzi GUID) ma wpływ na twoje odczyty. Istnieją trzy główne problemy z fragmentacją:

1. Strona dzieli koszt dysku we / wy
2. Połowa pełnych stron nie jest tak wydajna pod względem pamięci, jak pełne strony
3. Powoduje to, że strony są przechowywane w porządku, co zmniejsza prawdopodobieństwo sekwencyjnego We / Wy

Ponieważ Twoje pytanie dotyczy skalowalności, którą możemy zdefiniować jako „Dodanie dodatkowego sprzętu powoduje, że system działa szybciej”, są to najmniejsze problemy. Aby kolejno rozwiązać każdy z nich

Ad 1) Jeśli chcesz skalować, możesz sobie pozwolić na zakup I / O. Nawet tani dysk SSD Samsung / Intel 512 GB (za kilka USD / GB) zapewni Ci ponad 100 000 IOPS. Nie zużyjesz tego w najbliższym czasie w systemie 2-gniazdowym. A jeśli na to wpadniesz, kup jeszcze jeden i gotowe

Ad 2) Jeśli usuniesz w tabeli, i tak będziesz mieć do połowy pełne strony. A nawet jeśli nie, pamięć jest tania i dla wszystkich oprócz największych systemów OLTP - gorące dane powinny się tam zmieścić. Próba upakowania większej ilości danych na stronach jest suboptymalizowana, gdy szukasz skali.

Ad 3) Tabela zbudowana z często podzielonych stron, mocno pofragmentowanych danych wykonuje losowe operacje we / wy z dokładnie taką samą prędkością, jak sekwencyjnie wypełnione tabele

Jeśli chodzi o dołączanie, istnieją dwa główne typy złączeń, które najprawdopodobniej zobaczysz w OLTP, takie jak obciążenie: mieszanie i pętla. Przyjrzyjmy się kolejno:

Łączenie mieszające: łączenie mieszające zakłada, że ​​mały stolik jest skanowany i zwykle szukany jest większy. Małe tabele najprawdopodobniej zapadną w pamięć, więc nie dotyczy Ciebie we / wy. Dotknęliśmy już faktu, że szukanie ma taki sam koszt w indeksie rozdrobnionym jak w indeksie niepodzielonym

Dołącz do pętli: poszukiwany będzie stolik zewnętrzny. Taki sam koszt

Być może trwa również wiele złych operacji skanowania tabeli - ale GUID znów nie jest twoim problemem, właściwe indeksowanie.

Możliwe, że trwają pewne legalne skany zakresu (szczególnie przy łączeniu z kluczami obcymi). W tym przypadku fragmentowane dane są mniej „spakowane” w porównaniu do danych niepofragmentowanych. Zastanówmy się jednak, jakie połączenia prawdopodobnie zobaczysz w dobrze zindeksowanych danych 3NF:

1. Sprzężenie z tabeli zawierającej odwołanie do klucza obcego do klucza podstawowego tabeli, do której się odwołuje

2. Odwrotnie

Ad 1) W tym przypadku wybierasz się do pojedynczego wyszukiwania do klucza podstawowego - dołączając n do 1. Fragmentacja lub nie, ten sam koszt (jedno wyszukiwanie)

Ad 2) W takim przypadku łączysz się z tym samym kluczem, ale możesz pobrać więcej niż jeden wiersz (szukanie zakresu). Łączenie w tym przypadku wynosi od 1 do n. Jednak w szukanej tabeli zagranicznej szukasz klucza SAME, który równie dobrze może znajdować się na tej samej stronie w indeksie pofragmentowanym, jak w indeksie niepodzielonym.

Zastanów się przez chwilę nad tymi kluczami obcymi. Nawet jeśli „idealnie” sekwencyjnie położyłeś nasze klucze podstawowe - wszystko, co wskazuje na ten klucz, nadal nie będzie sekwencyjne.

Oczywiście możesz działać na maszynie wirtualnej w jakiejś sieci SAN w jakimś banku, który jest tani, jeśli chodzi o pieniądze i ma wysoki proces. Wtedy wszystkie te rady zostaną utracone. Ale jeśli to jest twój świat, skalowalność prawdopodobnie nie jest tym, czego szukasz - szukasz wydajności i wysokiej prędkości / kosztów - które są dwiema różnymi rzeczami.

Thomas: niektóre z twoich punktów są całkowicie sensowne i zgadzam się z nimi wszystkimi. Jeśli korzystasz z dysków SSD, saldo tego, co optymalizujesz, zmienia się. Losowy vs sekwencyjny to nie to samo co dysk wirujący.

W szczególności zgadzam się, że przyjęcie czystego widoku DB jest okropnie złe. Dokonywanie aplikacja powolny i unscalable poprawić tylko wydajność DB może być zupełnie błędne.

Dużym problemem związanym z TOŻSAMOŚCIĄ (lub sekwencją, lub czymkolwiek wygenerowanym w DB) jest to, że jest strasznie powolny, ponieważ wymaga utworzenia bazy danych w obie strony, a to automatycznie tworzy wąskie gardło w DB, wymusza to, że aplikacje muszą wykonać połączenie DB, aby rozpocząć korzystanie z klucza. Utworzenie GUID rozwiązuje ten problem, używając aplikacji do utworzenia klucza, gwarantuje to, że jest unikatowy na całym świecie (z definicji), a warstwy aplikacji mogą w ten sposób używać go do przekazywania rekordu PRZED wywołaniem podróży w obie strony DB.

Ale zwykle używam alternatywy dla GUID. Moją osobistą preferencją dla typu danych jest tutaj unikatowy na całym świecie BIGINT generowany przez aplikację. Jak się to robi? W najbardziej trywialnym przykładzie dodajesz niewielką, BARDZO lekką funkcję do swojej aplikacji, aby mieszać identyfikator GUID. Zakładając, że funkcja skrótu jest szybka i stosunkowo szybka (zobacz CityHash z Google na przykład: http://google-opensource.blogspot.in/2011/04/introducing-cityhash.html - upewnij się, że wykonałeś wszystkie kroki kompilacji poprawnie, lub wariant FNV1a http://tools.ietf.org/html/draft-eastlake-fnv-03 dla prostego kodu) daje to korzyść z wygenerowanych przez aplikację unikalnych identyfikatorów i 64-bitowej wartości klucza, z którą procesory lepiej współpracują .

Istnieją inne sposoby generowania BIGINTÓW, a w obu tych algach istnieje szansa na zderzenia skrótów - czytaj i podejmuj świadome decyzje.