Wybór indeksu klastrowego - PK czy FK?

Mam tabelę programu SQL Server 2014, która wygląda następująco:

OrderId     int           not null IDENTITY --this is the primary key column
OrderDate   datetime2     not null
CustomerId  int           not null
Description nvarchar(255) null

Niektórzy członkowie mojego zespołu sugerowali, że indeks klastrowany powinien być włączony OrderId, ale myślę, że CustomerId+ OrderIdbyłby lepszym wyborem z następujących powodów:

• Prawie wszystkie zapytania będą wyglądały WHERE CustomerId = @param, nieOrderId
• CustomerIdjest kluczem obcym do Customertabeli, więc indeks klastrowany z CustomerIdpowinien przyspieszyć złączenia
• Chociaż CustomerIdnie jest unikalny, podanie dodatkowej OrderIdkolumny w indeksie zapewni unikalność (możemy użyć UNIQUEsłowa kluczowego podczas tworzenia indeksu klastrowego na tych 2 kolumnach, aby uniknąć narzutu związanego z brakiem unikatowości)
• Po wstawieniu danych CustomerIdi OrderIdnigdy się nie zmieniają, więc te wiersze nie będą się przesuwać po pierwszym zapisie.
• Dostęp do danych odbywa się za pośrednictwem ORM, który domyślnie żąda wszystkich kolumn, więc gdy pojawi się zapytanie oparte na CustomerIdindeksie, klastrowany indeks będzie w stanie dostarczyć wszystkie kolumny bez dodatkowej pracy.

Czy podejście CustomerIdi OrderIdwydaje się najlepszą opcją, biorąc pod uwagę powyższe? A może jest OrderIdlepszy, ponieważ jest to pojedyncza kolumna, która sama gwarantuje wyjątkowość?

Obecnie tabela ma indeks klastrowany OrderIdi indeks nieklastrowy włączony CustomerId, ale nie obejmuje, więc ponieważ używamy ORM i wymagane są wszystkie kolumny, odzyskanie ich jest dodatkową pracą. Więc w tym poście staram się rozważyć poprawę wydajności dzięki lepszemu CI.

Aktywność na naszym DB wynosi około 85% odczytów i 15% zapisów.

_{Odpowiedź wiki społeczności :}

Myślę, że złożony indeks klucza klastrowanego z CustomerID jako pierwszą kolumną będzie najlepszy, ponieważ znajduje się w WHEREklauzuli prawie wszystkich zapytań.

Może być więcej podziałów w porównaniu z kluczem przyrostowym (lub bardziej prawdopodobne, że gęstość strony nie będzie optymalna przez pewien czas, jeśli będziesz zarządzać współczynnikiem wypełnienia i utrzymywać go, aby uniknąć „złych” podziałów). Jednak ogólna poprawa wydajności zapytań klientów jest znaczna, ponieważ unika się kluczowego wyszukiwania.

OrderID lub OrderDate mogą być najlepsze dla drugiej kolumny, w zależności od najbardziej krytycznych zapytań.

Na przykład, jeśli klienci zobaczą chronologiczną listę ostatnich zamówień po zalogowaniu się na stronie internetowej, kolejność zamówienia powinna być następna, aby zoptymalizować ORDER BY OrderDate DESC.

Jeśli wybierzesz OrderID jako indeks klastrowany z indeksem nieklastrowanym na CustomerID , nadal będziesz otrzymywać podziały i fragmentację, tylko w indeksie klastrowym.

Jeśli ta tabela wymaga intensywnego zapisu (np. INSERTWystępuje o wiele więcej instrukcji niż SELECTinstrukcji przeciwko niej), nie będę się zgadzał z odpowiedzią wiki .

Wybranie CustomerID jako pierwszej kolumny złożonego klucza klastrowego spowoduje wygenerowanie wielu podziałów w połowie strony . Mamy nadzieję, że masz wielu istniejących klientów, a także zyskujesz wielu nowych klientów przez cały czas. Ponieważ klienci (miejmy nadzieję) składają wiele zamówień w miarę rozwoju firmy, takie podejście będzie wykazywać dużą liczbę podziałów na środku strony, które zabiją wydajność nie tylko podczas zapisu, ale także odczytu, ponieważ zarówno indeksy będą mocno rozdrobnione i prawdopodobnie zawierają większe ilości białej przestrzeni (co oznacza zmarnowane miejsce do przechowywania i pamięć).

Jeśli uważasz, że CustomerID powinien być wiodącą kolumną złożonego indeksu klastrowego, możesz zmniejszyć wpływ podziałów w połowie strony, dostosowując FILLFACTORwszystkie indeksy dla tej tabeli. Zmniejszy to liczbę podziałów w połowie strony, zwiększając rozmiar tabeli / indeksu. Jeśli chcesz pójść tą drogą, sugeruję przetestowanie z wartością 80 i zmniejszenie, jeśli analiza wykaże, że podziały w połowie strony wciąż zabijają wydajność.

Moja sugestia to użycie OrderId. OrderID powinien naturalnie być sekwencyjny i generować więcej podziałów strony końcowej, które są dobre i oczekiwane wraz ze wzrostem tabeli. Ponadto to podejście będzie działać lepiej w przypadku partycjonowania tabel, jeśli zdecydujesz się użyć kolumny OrderDate jako klucza partycji. Jeśli chodzi o zapytania, które stale korzystają z pola CustomerID, utwórz indeks nieklastrowany, aby obsłużyć te zapytania. Indeks ten musiałby zostać zdefiniowany za pomocą właściwego, FILLFACTORponieważ będzie cierpiał z powodu podziałów na środkowej stronie, o których wspomniałem powyżej, chociaż nie będą one ogólnie tak złe, w przeciwieństwie do tego, czy podziały występowałyby względem indeksu klastrowego.

Aktywność na naszym DB wynosi około 85% odczytów i 15% zapisów.

CustomerID+ OrderID(i określenie współczynnika wypełnienia umożliwiającego wzrost bez podziałów) jest prawdopodobnie lepsze, jeśli ta ocena jest prawdziwa. Upewnij się tylko , że ocena jest dokładna. Test testowy.