Główne uwagi
Widzę jedną ważną zaletę dla stosów i jedną dla tabel klastrowych, a także trzecią uwagę, która może iść w obie strony.
Kupa oszczędza warstwę pośrednią. Indeksy zawierają identyfikatory wierszy wskazujące bezpośrednio (dobrze, nie tak naprawdę, ale tak bezpośrednio, jak to możliwe) na lokalizację dysku. Dlatego szukanie indeksu względem sterty powinno kosztować około połowy poszukiwania indeksu nieklastrowego względem tabeli klastrowanej.
Indeks klastrowany jest sortowany per se, dzięki (prawie) wolnemu indeksowi. Ponieważ indeks klastrowania jest odzwierciedlony w fizycznej kolejności danych, zajmuje on stosunkowo niewiele miejsca na samych danych, które oczywiście trzeba przechowywać. Ponieważ jest ono uporządkowane fizycznie, skanowanie zasięgu w stosunku do tego indeksu może wyszukiwać do punktu początkowego, a następnie bardzo skutecznie przesuwać się do punktu końcowego.
Wskaźniki na hałdach odnoszą się do RID, które są 64-bitowe. Jak wspomniano, nieklastrowe indeksy w tabeli klastrowej odnoszą się do klucza klastrowania, który może być mniejszy (32-bitowy INT), taki sam (64-bitowy BIGINT) lub większy (48-bitowy DATETIME2()plus 32-bitowy INT, lub 128-bitowy identyfikator GUID). Oczywiście szersze odniesienie powoduje, że indeksy są większe i droższe.
Wymagania dotyczące miejsca
Dzięki tym dwóm tabelom:
CREATE TABLE TmpClustered
(
ID1 INT NOT NULL,
ID2 INT NOT NULL
)
ALTER TABLE TmpClustered ADD CONSTRAINT PK_Tmp1 PRIMARY KEY CLUSTERED (ID1)
CREATE UNIQUE INDEX UQ_Tmp1 ON TmpClustered (ID2)
CREATE TABLE TmpNonClustered
(
ID1 INT NOT NULL,
ID2 INT NOT NULL
)
ALTER TABLE TmpNonClustered ADD CONSTRAINT PK_Tmp2 PRIMARY KEY NONCLUSTERED (ID1)
CREATE UNIQUE INDEX UQ_Tmp2 ON TmpNonClustered (ID2)
... każdy zawiera 8,7 mln rekordów, wymagane miejsce to 150 MB na dane dla obu; 120 MB dla indeksów tabeli klastrowej, 310 MB dla indeksów tabeli nieklastrowanej. Odzwierciedla to, że indeks klastrowy jest węższy niż RID i że indeks klastrowy jest przeważnie „darmowy”. Bez włączonych unikatowych indeksów ID2wymagana przestrzeń indeksu spada do 155 MB dla nieklastrowanej tabeli (połowa, jak można się spodziewać), ale tylko 150 KB dla klastrowanego PK - prawie nic.
Tak więc indeks nieklastrowy 32-bitowego pola w tabeli klastrowej z indeksem 32-bitowym (łącznie 64 bity, nominalnie) zajął 120 MB, podczas gdy indeks 32-bitowego pola w stercie z 64-bitowym RID (łącznie 96 bitów, nominalnie) zajął 155 MB, czyli nieco mniej niż 50% wzrost, na który naiwnie można się spodziewać przejścia z 64-bitowych na 96-bitowe klucze, ale oczywiście jest narzut, który zmniejsza efektywną różnicę wielkości.
Zapełnienie dwóch tabel i utworzenie ich indeksów zajęło tyle samo czasu dla każdej tabeli. Przeprowadzając proste testy obejmujące skanowanie lub wyszukiwanie, nie znalazłem istotnych różnic w wydajności między tabelami, które pasują do białej księgi Microsoft, którą gbn jest pomocne. Wspomniany artykuł wykazuje znaczącą różnicę w przypadku wysoce współbieżnego dostępu; Nie jestem pewien, dlaczego tak się dzieje, mam nadzieję, że ktoś z większym doświadczeniem niż ja z dużymi systemami OLTP może nam powiedzieć.
Dodanie ~ 40 bajtów losowych danych o zmiennej długości nie zmieniło znacząco tej równoważności. Zastąpienie INTs szerokimi identyfikatorami UUID również nie spowodowało (każda tabela została spowolniona w mniej więcej tym samym stopniu). Może się wahać, ale w większości przypadków , czy indeks jest dostępny jest ważniejsze niż to, co rodzaj.
Kawałki
Wykonywanie skanowania zakresu w stosunku do indeksu nieklastrowego - ponieważ tabela jest stertą lub indeks nie jest indeksem klastrowanym - obejmuje skanowanie indeksu, a następnie wyszukiwanie tabeli w odniesieniu do każdego działania. Może to być bardzo kosztowne, więc czasami taniej jest po prostu zeskanować stół. Możesz jednak obejść ten problem za pomocą indeksu obejmującego. Dotyczy to niezależnie od tego, czy masz klaster tabeli, czy nie.
Jak zauważył @gbn, nie ma prostego sposobu na zagęszczenie sterty. Jeśli jednak twoja tabela stopniowo się powiększa - co jest bardzo częstym przypadkiem - niewiele będzie marnotrawstwa, ponieważ przestrzeń zwolniona przez usuwanie zostanie wypełniona nowymi danymi.
Kilka dyskusji na temat sterty w porównaniu do tabeli klastrowej, które widziałem, stanowi ciekawy argument, że sterta bez indeksów jest gorsza od tabeli klastrowanej, ponieważ zawsze wymaga skanowania tabeli. Jest to z pewnością prawda, ale bardziej miarodajne porównanie to „duża dobrze indeksowana tabela klastrowa” w porównaniu z „dużą dobrze indeksowaną stertą”. Jeśli twój stół jest bardzo mały lub zawsze będziesz przeprowadzał skanowanie tabeli, to nie ma większego znaczenia, czy go zgrupujesz, czy nie.
Ponieważ każdy indeks w tabeli klastrowej odwołuje się do indeksu klastrowania, w rzeczywistości wszystkie indeksy pokrywają się. Kwerenda, która odwołuje się do kolumny indeksowanej i kolumn klastrowych, może wykonać skanowanie indeksu bez wyszukiwania tabel. Zasadniczo nie jest to cenne, jeśli indeks klastrowania jest kluczem syntetycznym, ale jeśli jest to klucz biznesowy, który i tak należy pobrać, jest to miła funkcja.
TL; DR
Jestem facetem od hurtowni danych, a nie ekspertem od OLTP. W przypadku tabel faktów prawie zawsze używam indeksu klastrowania w polu, który najprawdopodobniej wymaga skanowania zakresu, zazwyczaj pola daty. W przypadku tabel wymiarów klastruję na PK, więc jest on przewidziany dla połączeń scalania z tabelami faktów.
Istnieje kilka powodów, dla których warto stosować indeksy klastrowe, ale jeśli żaden z tych powodów nie ma zastosowania, koszty ogólne mogą nie być opłacalne. Podejrzewam, że wiele osób „zawsze tak robiliśmy” i „to po prostu najlepsza praktyka” za ludźmi używającymi indeksów klastrowych uniwersalnie. Spróbuj zarówno ze swoimi danymi i swoim obciążeniem i zobaczyć, co działa najlepiej.