Dlaczego warto używać klauzuli INCLUDE podczas tworzenia indeksu?

Możesz użyć INCLUDE, aby dodać jedną lub więcej kolumn do poziomu liścia indeksu bezklastrycznego, jeśli w ten sposób możesz "pokryć" swoje zapytania.

Wyobraź sobie, że musisz zapytać o identyfikator pracownika, identyfikator działu i nazwisko.

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5

Jeśli zdarzy ci się mieć nieklastrowany indeks na (EmployeeID, DepartmentID), gdy znajdziesz pracowników dla danego działu, musisz teraz wykonać "bookmark lookup", aby uzyskać rzeczywisty pełny rekord pracownika, tylko aby uzyskać kolumnę lastname. Że może być dość kosztowne pod względem wydajności, jeśli znajdziesz dużo pracowników.

Jeśli umieściłeś tę ostatnią nazwę w swoim indeksie:

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(EmployeeID, DepartmentID)
  INCLUDE (Lastname)

Wtedy wszystkie potrzebne informacje są dostępne na poziomie liścia indeksu bezklasowego. Po prostu szukając w indeksie bezklasowym i znajdując swoich pracowników dla danego działu, masz wszystkie niezbędne informacje, a Wyszukiwanie zakładek dla każdego pracownika znalezionego w indeksie nie jest już konieczne -- > oszczędzasz dużo czas.

Oczywiście, nie można włączyć każdej kolumny do każdego indeksu nieklastrycznego - ale jeśli masz zapytania, w których brakuje tylko jednej lub dwóch kolumn do "pokrycia" (i które są często używane), bardzo pomocne może być włączenie tych do odpowiedniego indeksu nieklastrycznego.

Podstawowe kolumny indeksu są sortowane, ale zawarte w nich kolumny nie są sortowane. Oszczędza to zasoby w utrzymaniu indeksu, jednocześnie umożliwiając dostarczanie danych w dołączonych kolumnach w celu pokrycia zapytania. Tak więc, jeśli chcesz pokryć zapytania, możesz umieścić kryteria wyszukiwania, aby zlokalizować wiersze w posortowanych kolumnach indeksu, ale następnie "dołącz" dodatkowe, niesortowane kolumny z danymi nie przeszukującymi. Zdecydowanie pomaga w zmniejszeniu ilości sortowania i fragmentacji w indeksie konserwacja.

W tej dyskusji brakuje ważnego punktu: pytanie nie brzmi, czy "non-Key-columns" lepiej zawierać jako index - columns lub jako included -columns.

Pytanie brzmi, jak kosztowne jest stosowanie mechanizmu include do dołączania kolumn, które nie są naprawdę potrzebne w indeksie? (zazwyczaj nie są częścią klauzul where -, ale często są uwzględniane w selekcjach). Więc twój dylemat jest zawsze:

1. użyj indeksu na id1, id2 ... idN samodzielnie lub
2. użyj indeksu na id1, id2 ... idN Plus include col1, col2 ... colN

Gdzie: id1, id2 ... idN to kolumny często używane w ograniczeniach i col1, col2 ... colN to kolumny często wybierane, ale zazwyczaj nie używane w ograniczeniach

(opcja włączenia wszystkich tych kolumn jako części indeksu-key jest po prostu zawsze głupia ( chyba że są one również używane w ograniczeniach) - ponieważ utrzymanie indeksu zawsze byłoby droższe, ponieważ indeks musi być aktualizowany i posortowane nawet wtedy, gdy "klucze" nie uległy zmianie).

Więc użyj opcji 1 Czy 2?

Odpowiedź: jeśli twoja tabela jest rzadko aktualizowana - najczęściej wstawiana do/usuwana z - to stosunkowo niedrogie jest użycie mechanizmu include-do dołączania niektórych "gorących kolumn" (które są często używane w selekcjach - ale nie często używane w ograniczeniach), ponieważ wstawianie/usuwanie wymaga aktualizacji / sortowania indeksu i dlatego niewiele dodatkowych kosztów jest związane z przechowywaniem kilku dodatkowych kolumn podczas aktualizacji indeksu. Narzut to dodatkowa pamięć i procesor używane do przechowywania nadmiarowych informacji na indeksie.

Jeśli kolumny, które uważasz za dołączone-kolumny są często aktualizowane (bez klucza index - - kolumny są aktualizowane) - lub - jeśli jest ich tak wiele, że indeks staje się Bliski kopii Twojej tabeli-użyj opcji 1, sugeruję! Również jeśli dodanie pewnych include-column(s) okaże się bez różnicy w wydajności - możesz pominąć pomysł na dodanie ich:) sprawdź czy są przydatne!

Średnia liczba wierszy dla tych samych wartości w kluczach (id1, id2 ... idN) może mieć również pewne znaczenie.

Zauważ, że jeśli kolumna-która jest dodawana jako included - kolumna indeksu - jest używana w ograniczeniu : tak długo, jak indeks jako taki może być użyty (na podstawie ograniczenia względem indeksu-Klucz -columns) - wtedy SQL Server dopasowuje ograniczenie do indeksu (Leaf-node-values) zamiast iść drogą wokół samego stołu.

Powody, dla których (w tym dane w poziomie liścia indeksu) zostały ładnie wyjaśnione. Powodem, dla którego dajesz dwa wstrząsy o tym, jest to, że podczas uruchamiania zapytania, jeśli nie masz dodatkowych kolumn zawartych (nowa funkcja w SQL 2005) SQL Server musi przejść do indeksu klastrów, aby uzyskać dodatkowe kolumny, które zajmuje więcej czasu i dodaje więcej obciążenia do usługi SQL Server, dyski i pamięć (bufor cache być specyficzne) jak nowe strony danych są ładowane do pamięci, potencjalnie wypychając z bufora inne, częściej potrzebne dane.

Dodatkowym rozważeniem, którego nie widziałem w udzielonych odpowiedziach, jest to, że zawarte kolumny mogą być typami danych, które nie są dozwolone jako kolumny klucza indeksowego, takie jak varchar(max).

Pozwala to na włączenie takich kolumn do indeksu pokrycia. Ostatnio musiałem to zrobić, aby dostarczyć zapytanie wygenerowane przez nHibernate, które miało wiele kolumn w SELECT, z użytecznym indeksem.

Istnieje ograniczenie całkowitego rozmiaru wszystkich kolumn wpisanych do definicji indeksu. To powiedziawszy, nigdy nie musiałem tworzyć tak szerokiego indeksu. Dla mnie większą zaletą jest fakt, że możesz pokryć więcej zapytań jednym indeksem, który zawiera kolumny, ponieważ nie muszą być one zdefiniowane w żadnej konkretnej kolejności. Pomyśl o is jako indeksie w indeksie. Przykładem może być StoreID (gdzie StoreID to niska selektywność, co oznacza, że każdy sklep jest związany z wieloma klientów), a następnie dane demograficzne klienta (LastName, FirstName, DOB): Jeśli po prostu wstawisz kolumny w tej kolejności (StoreID, LastName, FirstName, DOB), możesz efektywnie wyszukiwać tylko klientów, dla których znasz StoreID i LastName.

Z drugiej strony, zdefiniowanie indeksu na StoreID i włączenie kolumn LastName, FirstName, DOB pozwoli Ci w istocie wykonać dwa predykaty seek-index Na StoreID, a następnie wyszukać predykat na dowolnej z dołączonych kolumn. To pozwoli Ci obejmuje wszystkie możliwe permutacje Wyszukiwania, o ile zaczyna się od StoreID.