Czy indeks w kolumnie tożsamości powinien być nieklastrowany?

Czy dla tabeli z kolumną tożsamości należy utworzyć klastrowany lub nieklastrowany PK / indeks unikalny dla kolumny tożsamości?

Powodem jest to, że dla zapytań zostaną utworzone inne indeksy. Kwerenda, która używa indeksu nieklastrowanego (na stercie) i zwraca kolumny, które nie są objęte indeksem, użyje mniej logicznych operacji we / wy (LIO), ponieważ nie ma dodatkowych kroków wyszukiwania indeksu klastrowego b?

create table T (
  Id int identity(1,1) primary key, -- clustered or non-clustered? (surrogate key, may be used to join another table)
  A .... -- A, B, C have mixed data type of int, date, varchar, float, money, ....
  B ....
  C ....
  ....)

create index ix_A on T (A)
create index ix_..... -- Many indexes can be created for queries

-- Common query is query on A, B, C, ....
select A, B 
from T 
where A between @a and @a+5 -- This query will have less LIO if the PK is non-clustered (seek)

select A, B, C
from T 
where B between @a and @a+5 

....

Klastrowane PK w kolumnie tożsamości jest dobre, ponieważ:

1. Zwiększa się monotonnie, więc podczas wstawiania nie dochodzi do podziału strony. Mówi się, że wstawianie zbiorcze może być tak szybkie, jak w tabeli stosu (nieklastrowanego)

2. Jest wąski

Czy jednak zapytania w pytaniu będą szybsze bez ustawiania ich w klastrze?

** Aktualizacja: ** Co jeśli Idjest FK innych tabel i zostanie dołączony w niektórych zapytaniach?

Domyślnie PK jest zgrupowane i w większości przypadków jest to w porządku. Które pytanie należy jednak zadać:

• czy moje PK powinno być grupowane?
• które kolumny będą najlepszym kluczem do mojego indeksu klastrowanego?

PK i indeks klastrowy to dwie różnice:

• PK jest ograniczeniem. PK służy do jednoznacznej identyfikacji wierszy, ale nie ma pojęcia przechowywania. Jednak domyślnie (w SSMS) jest wymuszany przez unikalny indeks klastrowany, jeśli indeks klastrowany nie jest jeszcze obecny.
• Indeksy klastrowe to specjalny rodzaj indeksu, który przechowuje dane wierszy na poziomie liścia, co oznacza, że ​​zawsze obejmuje. Wszystkie kolumny, bez względu na to, czy są częścią klucza, czy nie, są przechowywane na poziomie liścia. Nie musi być unikalny, w którym to przypadku do klucza klastrowanego dodawany jest unikalizator (4 bajty).

Teraz mamy 2 pytania:

• Jak chcę jednoznacznie zidentyfikować wiersze w mojej tabeli (PK)
• Jak chcę go przechowywać na poziomie liścia indeksu (Indeks klastrowany)

To zależy od:

• projektujesz swój model danych
• przeszukujesz swoje dane i piszesz swoje zapytania
• wstawiasz lub aktualizujesz swoje dane
• ...

Po pierwsze, czy potrzebujesz indeks klastrowany? W przypadku wstawiania zbiorczego bardziej wydajne jest przechowywanie nieuporządkowanych danych w HEAP (w porównaniu do danych uporządkowanych w klastrze). Wykorzystuje RID (identyfikator wiersza, 8 bajtów), aby jednoznacznie identyfikować wiersze i przechowywać je na stronach.

Indeks klastrowy nie powinien być wartością losową. Dane na poziomie liścia zostaną zapisane i uporządkowane według klucza indeksu. Dlatego powinien stale rosnąć, aby uniknąć fragmentacji lub podziału strony. Jeśli PK nie może tego osiągnąć, powinieneś rozważyć inny klucz jako kandydata klastrowego. Indeks klastrowy na identycznych kolumnach, sekwencyjnym identyfikatorze GUID, a nawet czymś takim jak data wstawienia jest w porządku z sekwencyjnego punktu widzenia, ponieważ wszystkie wiersze zostaną dodane do ostatniej strony liścia. Z drugiej strony, chociaż unikalny identyfikator może być przydatny do potrzeb Twojej firmy jako PK, nie należy ich grupować (są one losowo uporządkowane / generowane).

Jeśli po analizie danych i zapytań okaże się, że najczęściej używasz tego samego indeksu, aby uzyskać dane, zanim zaczniesz wyszukiwanie klucza w klastrowanym PK, możesz uznać go za indeks klastrowany, chociaż może nie jednoznacznie identyfikować twoich danych.

Klastrowany klucz indeksu składa się ze wszystkich kolumn, które chcesz indeksować. Kolumna unikatowa (4 bajty) jest dodawana, jeśli nie ma na niej żadnego unikalnego ograniczenia (wartość przyrostowa dla duplikatów, w przeciwnym razie null). Ten klucz indeksu zostanie następnie zapisany raz dla każdego wiersza na poziomie liści wszystkich indeksów nieklastrowanych. Niektóre z nich będą również przechowywane kilka razy na poziomach pośrednich (gałąź) między korzeniem a poziomem liści drzewa indeksu (B-drzewo). Jeśli klucz jest zbyt duży, cały indeks nieklastrowany będzie większy, będzie wymagał więcej miejsca i więcej IO, procesora, pamięci, ... Jeśli masz PK na nazwisko + data urodzenia + kraj, jest bardzo prawdopodobne, że ten klucz nie jest dobrym kandydatem. Jest za duży na indeks klastrowany. Uniqueidentifier używający NEWSEQUENTIALID () zwykle nie jest uważany za wąski klucz (16 bajtów), chociaż jest sekwencyjny.

Następnie, gdy nauczysz się, jak jednoznacznie identyfikować wiersze w tabeli, możesz dodać PK. Jeśli uważasz, że nie użyjesz go w zapytaniu, nie twórz go w klastrze. Nadal możesz utworzyć kolejny indeks nieklastrowany, jeśli będziesz musiał go kiedyś wyszukać. Zauważ, że PK automatycznie utworzy unikalny indeks.

Indeksy nieklastrowane zawsze będą zawierać klucz klastrowany. Jeśli jednak indeksowane kolumny (+ kolumny klucza) pokrywają się, w indeksie klastrowym nie będzie żadnego wyszukiwania klucza. Nie zapomnij, że możesz także dodać Uwzględnij i Gdzie do indeksu nieklastrowanego. (używaj rozważnie)

Indeks klastrowy powinien być unikalny i możliwie wąski Indeks klastrowy nie powinien zmieniać się w czasie i powinien być wprowadzany przyrostowo.

Czas napisać trochę kodu SQL, który utworzy indeksy i ograniczenia tabelaryczne, klastrowe i nieklastrowane.

To wszystko teoretyczne, ponieważ nie znamy twojego modelu danych i używanych typów danych (A i B).

W przypadku tabeli z kluczem podstawowym (PK) w kolumnie tożsamości będzie ona domyślnie klastrowana. Czy może być lepiej niż nieklastrowany?

Jeśli pytasz, czy domyślny klucz podstawowy w kolumnie tożsamości (w szczególności) powinien być nieklastrowany, powiedziałbym, że nie. Większość tabel korzysta z indeksowania klastrowego, więc ustawienie klastrowania jako domyślnego dla ograniczenia klucza podstawowego jest prawdopodobnie ogólnie pomocne, szczególnie dla nowych użytkowników SQL Server.

Podobnie jak w przypadku prawie każdej opcji, zawsze istnieją różne okoliczności, w których jedna ma być preferowana w stosunku do drugiej, ale doświadczony DBA powinien być świadomy domyślnej wartości i być w stanie zastąpić ją w razie potrzeby. Zobacz także powiązane pytania i odpowiedzi. Kiedy klucz podstawowy należy zadeklarować jako nieklastrowany? .

Czy zapytania w pytaniu będą szybsze bez ustawiania ich w klastrze?

Tak, ale z zastrzeżeniami.

Wyszukiwanie RID jest rzeczywiście bardziej wydajne niż wyszukiwanie klucza. Nawet jeśli wszystkie wymagane strony są w pamięci (najprawdopodobniej dla wyższych poziomów indeksu), nawigacja po klastrze b-drzewa wiąże się z kosztami procesora. W związku z tym SQL Server może zwykle wykonywać znacznie więcej wyszukiwań RID niż wyszukiwań kluczy na jednostkę czasu procesora.

Ostrzeżenia

Powyższe często nie jest decydującym czynnikiem przy podejmowaniu decyzji, czy ułożyć strukturę tabeli w stos, czy nie. Musiałoby być niepraktyczne unikanie wyszukiwań (przy użyciu indeksów pokrywających), a liczba wyszukiwań musiałaby być wystarczająco duża, aby mieć mierzalny (i ważny) wpływ na wydajność, biorąc pod uwagę środowisko sprzętowe i obciążenie pracą.

W tej odpowiedzi nie jest praktyczne uwzględnienie wszystkich aspektów debaty o stercie vs indeksie klastrowym, ale powiem, że istnieje stosunkowo niewiele dobrych powodów, aby preferować strukturę tabeli jako stosu w ogóle. Dla mnie wybór rodzaju projektu zaproponowanego w pytaniu wymagałby bardzo starannej analizy przed wdrożeniem i musiałby osiągnąć wysoki poziom. Ogólne argumenty dotyczące „skalowalności” nie byłyby wystarczające.

Jeśli chodzi o aktualizację pytania o przyłączenia, ocena wpływu utraty indeksu klastrowego na plany wykonania stanowiłaby część wyżej wspomnianej analizy. Jeśli używane są sprzężenia z zagnieżdżonymi pętlami, bardzo wygodnie jest mieć indeks klastrowany na kluczu łączenia, ponieważ wszystkie kolumny z wiersza są natychmiast dostępne bez wyszukiwania.

Z własnego doświadczenia wynika, że ​​posiadanie unikatowych indeksów klastrowych w kolumnach tożsamości jest bardzo często korzystne, biorąc pod uwagę wszystkie rzeczy. Uważam, że stosy są problematyczne z punktu widzenia zarządzania pamięcią masową. Powinienem również wspomnieć, że niektóre funkcje programu SQL Server wymagają do działania unikalnego indeksu klastrowego.

W rzeczywistości nie trzeba tworzyć indeksu klastrowanego ani klucza podstawowego, ponieważ indeksy unikatowe i indeksy nieunikalne mogą obsłużyć pracę. SQL Server obsługuje Indeks klastrowy od co najmniej wersji 1.1, ale klucz podstawowy był tylko „koncepcją”, którą programiści wymuszali, definiując unikalny indeks.

Wydaje się jednak, że zarówno klucze podstawowe, jak i indeksy klastrowe są wartościowymi pojęciami w większości baz danych.

Spójrzmy na dokumentację SQL Server, aby zobaczyć częściowe opisy niektórych opcji indeksowania, jak pokazano poniżej.

Indeks klastrowy: https://msdn.microsoft.com/en-us/library/ms190457.aspx

• Indeksy klastrowe sortują i przechowują wiersze danych w tabeli lub widoku na podstawie ich kluczowych wartości. Są to kolumny zawarte w definicji indeksu.
• Może być tylko jeden indeks klastrowany na tabelę

Klucz podstawowy: https://msdn.microsoft.com/en-us/library/ms190457.aspx

• Tabela może zawierać tylko jedno ograniczenie KLUCZ PODSTAWOWY.

• Wszystkie kolumny zdefiniowane w ramach ograniczenia klucza podstawowego muszą być zdefiniowane jako NOT NULL.

• Klucz podstawowy można utworzyć jako indeks klastrowany (domyślny, jeśli nie ma indeksu klastrowanego) lub indeks nieklastrowany.

Unikalny indeks: https://msdn.microsoft.com/en-us/library/ms187019.aspx

• Podczas tworzenia ograniczenia UNIQUE tworzony jest unikalny indeks nieklastrowany, aby domyślnie wymuszać ograniczenie UNIQUE.

• Możesz określić UNIQUE indeks klastrowany, jeśli indeks klastrowany nie istnieje jeszcze dla tabeli.

Oznacza to, że twoje pytanie dotyczące indeksów klastrowych i kluczy podstawowych dotyczy w rzeczywistości niektórych z następujących problemów. Pamiętaj, że nie każda tabela korzysta z tego samego planu indeksowania.

Kiedy skorzystam z oddzielenia klucza podstawowego od indeksu klastrowego?

Być może, gdy indeks klastrowy jest szeroki (na przykład 5 kolumn informacji tekstowych, ale klucz podstawowy jest mały (INT lub BIGINT), jak się wydaje.

• Szeroki indeks klastrowy umożliwia szybkie wybranie wierszy z indeksu dla podzbioru zapytań, które zapewniają szeregowe odpowiedzi z indeksu klastrowanego (zwanego również tabelą ). Na przykład 5-kolumnowy indeks klastrowy obsługuje skanowanie kolumn C1, C2, C3, C4, C5 lub C1, C2, C3, C4 i tak dalej aż do C1.
• Uwaga: Jeśli wiersze były duże, może to dać pewne korzyści szybkościowe przy wyborze szeregowego zestawu wierszy, zwłaszcza jeśli inne kolumny w tabeli są regularnie uwzględniane w zestawie wyników.
• W takim przypadku można użyć klucza głównego dla integralności referencyjnej, aby podać potrzebną wartość jako klucz obcy, aby ograniczyć wiersze w innych tabelach. PK jest mały, a zatem FK jest małym trafieniem w rozmiar tabeli (tabel), do której istnieją odniesienia.
• Należy jednak pamiętać, że każdy indeks utworzony w tabeli z indeksem klastrowanym będzie zawierać wszystkie kolumny klastrów w innych indeksach tworzonych w tej tabeli. Szeroki indeks klastrowany powiększyłby rozmiar wszystkich indeksów nieklastrowanych w tej tabeli.

Czy sam klucz podstawowy powinien być indeks klastrowany?

• Jeśli masz mały klucz podstawowy (INT lub BIGINT) i jest to Indeks klastrowany, narzut związany z kolumnami klastra jest stosunkowo niewielki. Chociaż klastrowany klucz podstawowy w tym przypadku będzie również istniał w każdym indeksie w tej tabeli, jest to cena niższa niż w przypadku omawianego powyżej klastra szerokiego.

• Ten indeks klastrowany klucza podstawowego zwykle nie zapewnia bezpośrednio łatwej ścieżki do seryjnego wybierania wielu wierszy.

• Teraz, gdy utworzyłeś klastrowany klucz podstawowy, co z tymi innymi kolumnami, które kiedyś planowałeś włączyć do indeksu klastrowanego ?

• Utwórz indeks Unique (lub Non-Unique) zgodnie z potrzebą, aby zindeksować szerokie kryteria wyszukiwania w kolumnach C1, C2, C3, C4, C5. Wartości w tym indeksie „imitacji klastrowej” mogą służyć jako szybsza ścieżka wyszukiwania dla tych 5 kolumn. Jeśli istnieje nieindeksowana kolumna lub dwie, które są również regularnie wybierane, można je uwzględnić w indeksie za pomocą INCLUDE (Doctor_Name, Diagnosis_Synopsis).

Chociaż uważam, że proste Indeksy klastrowe i klucze podstawowe są użyteczne, istnieje kilka dobrych powodów, aby zastanowić się, czy użyć ich w tabeli, czy w bazie danych.

Czy w ogóle potrzebujesz indeksu klastrowanego?

• Jeśli utworzysz indeksy (indeksy unikalne i indeksy nieunikalne) i zdefiniujesz klucz podstawowy bez narzutu bycia indeksem klastrowanym, może się okazać, że węższe indeksy zapewnią ci to, czego potrzebujesz do swoich zapytań.

• Istnieje kilka przydatnych zachowań w indeksach klastrowych i kluczach podstawowych, ale pamiętaj, że tak naprawdę indeksy są najważniejsze. Zaprojektuj strategię indeksowania, aby uwzględnić realia Twojej aplikacji. Być może OneBigTablepotrzeba innej strategii indeksowania niż w przypadku większości tabel.

• Bez indeksu klastrowego dane będą przechowywane jako kupa z identyfikatorem wiersza (RID), który wcale nie jest dobrym mechanizmem wyszukiwania. Ale, jak wspomniano wcześniej, możesz tworzyć unikalne i nieunikalne indeksy do obsługi swoich zapytań.

Co teraz zabiera Cię do rozważenia Sterty:

Sterty i indeksy: https://msdn.microsoft.com/en-us/library/hh213609.aspx

• Gdy tabela jest przechowywana jako kupa, poszczególne wiersze są identyfikowane przez odniesienie do identyfikatora wiersza (RID) składającego się z numeru pliku, numeru strony danych i miejsca na stronie. Identyfikator wiersza jest małą i wydajną strukturą. (Ale to nie jest indeks .)
• Czasami architekci danych używają stosów, gdy dane są zawsze dostępne za pośrednictwem indeksów nieklastrowanych, a RID jest mniejszy niż klastrowany klucz indeksu .

Ale jeśli masz również kilka „gorących punktów” w dużym zbiorze danych, możesz także spojrzeć na inny typ indeksu:

Filtrowany indeks: https://msdn.microsoft.com/en-us/library/cc280372.aspx

• Dobrze zaprojektowany filtrowany indeks poprawia wydajność zapytań i jakość planu wykonania, ponieważ jest mniejszy niż indeks nieklastrowany pełnej tabeli i ma przefiltrowane statystyki. Filtrowane statystyki są dokładniejsze niż statystyki z pełną tabelą, ponieważ obejmują tylko wiersze w przefiltrowanym indeksie .

• Filtrowane indeksy mają wiele ograniczeń, które są przedstawione w linku do indeksowanych filtrów.

Jeśli jednak zastanawiasz się nad możliwością pominięcia kluczy podstawowych i indeksów klastrowych, możesz przeczytać post Markusa Winanda, do którego link znajduje się poniżej. Przedstawia swoje powody, z niektórymi przykładami kodu, sugerując, że czasem dobrym pomysłem może być rezygnacja z korzystania z tych funkcji.

http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key

Ale wszystko w końcu wraca do zrozumienia twojej aplikacji i zaprojektowania kodu, tabel, indeksów itp., Aby pasowały do ​​wykonywanej pracy.

Kilka punktów do rozważenia.

Podczas gdy indeks (klastrowany lub nie) o monotonicznie rosnącej wartości oszczędza podział strony podczas wstawiania masy, tworzy nowy gorący punkt na końcu indeksu. Chociaż może to nie być problem z pojedynczą wstawką zbiorczą jednego wątku, zdecydowanie zwiększy rywalizację o aplikację wielowątkową wstawiającą nowe krotki z dużą szybkością, ponieważ wątki będą stale konkurowały o dostęp do ostatniej strony indeksu.

Grupowanie tabeli w oparciu o surogat (tożsamość) PK jest rzadko korzystne. Taki klucz podstawowy służy głównie do uzyskiwania dostępu do pojedynczych krotek pojedynczo lub do skanowania całego indeksu w poszukiwaniu sprzężeń. W obu przypadkach nie ma znaczenia, czy indeks jest klastrowany, czy nie (z wyjątkiem złączeń scalających, ale jak często?)

Myślę, że najbardziej skorzystasz z indeksu klastrowego, który obejmuje zapytania z prośbą o skanowanie zakresu klucza i dodatkowe predykaty odnoszące się do innych kolumn.