Reguła twarda i szybka dla uwzględnienia kolumn w indeksie

Czy istnieje jakaś twarda i szybka reguła, która decyduje o tym, w których kolumnach i jakiej kolejności należy je umieścić Uwzględnione w indeksie nieklastrowanym. Właśnie czytałem ten post https://stackoverflow.com/questions/1307990/why-use-the-include-clause-when-creating-an-index i znalazłem to dla następującego zapytania:

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

Plakat sugeruje, aby zrobić indeks w ten sposób:

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

oto moje pytanie, dlaczego nie możemy zrobić takiego indeksu

CREATE NONCLUSTERED INDEX NC_EmpDep 
      ON Employee( EmployeeID, DepartmentID, LastName)

lub

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

i co powoduje, że plakat decyduje się na uwzględnienie kolumny LastName. Dlaczego nie inne kolumny? i jak zdecydować, w jakiej kolejności powinniśmy przechowywać tam kolumny?

Ta sugestia indeksu autorstwa marc_s jest błędna. Dodałem komentarz. (I to była też moja odpowiedź zaakceptowana!)

Indeks dla tego zapytania to

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

Indeks jest zazwyczaj

CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)

Gdzie:

• KeyColList = Kolumny kluczowe = używane do ograniczania wierszy i przetwarzania
  GDZIE, DOŁĄCZ, ORDER BY, GROUP BY itp.
• NonKeyColList = Kolumny niekluczowe = używane w SELECT i agregacji (np. SUM (col)) po selekcji / ograniczeniu

JNK i gbn dały świetne odpowiedzi, ale warto również wziąć pod uwagę duży obraz - nie skupiając się tylko na jednym zapytaniu. Chociaż to konkretne zapytanie może skorzystać z indeksu (nr 1):

Employee(DepartmentID) INCLUDE (Lastname, EmployeeID)

Ten indeks w ogóle nie pomaga, jeśli zapytanie zmienia się nieznacznie, na przykład:

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5 AND LastName = 'Smith'

Wymagałoby to indeksu (# 2):

Employee(DepartmentID, LastName) INCLUDE (EmployeeID)

Wyobraź sobie, że miałeś 1000 pracowników w dziale 5. Korzystając z indeksu nr 1, aby znaleźć wszystkich Smithów, musisz przeszukać wszystkie 1000 wierszy w dziale 5, ponieważ zawarte kolumny nie są częścią klucza. Korzystając z indeksu # 2, możesz szukać bezpośrednio w dziale 5, LastName Smith.

Indeks nr 2 jest zatem bardziej przydatny w obsłudze szerszego zakresu zapytań - ale kosztem jest bardziej rozdęty klucz indeksu, który zwiększy strony nie-liściowe indeksu. Każdy system będzie inny, więc nie ma tu żadnej praktycznej zasady.

Na marginesie warto zauważyć, że jeśli identyfikator pracownika był kluczem do klastrowania dla tej tabeli - zakładając indeks klastrowany - to nie musisz uwzględniać identyfikatora pracownika - jest on obecny we wszystkich indeksach nieklastrowanych, co oznacza, że ​​indeks nr 2 mógłby po prostu być

Employee(DepartmentID, LastName)

Nie jestem pewien, skąd masz ten pierwszy. Dla mnie do tego zapytania użyłbym:

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

Nie ma „twardej i szybkiej reguły” dla prawie wszystkiego w SQL.

Ale, na przykład, jedynym polem, którego będzie używał indeks, jest DepartmentIDto, że znajduje się w WHEREklauzuli.

Inne pola muszą być łatwo dostępne stamtąd. Wybierasz na podstawie DepartmentIDwtedy INCLUDEma te pola w węźle liścia indeksu.

Nie chcesz używać innych przykładów, ponieważ nie będą one działać dla tego indeksu.

Pomyśl o indeksie jak o książce telefonicznej. Większość książek telefonicznych jest uporządkowana według nazwiska, imienia i drugiej litery. Jeśli znasz czyjeś imię, ale nie nazwisko, książka telefoniczna nic ci nie da, ponieważ nie możesz wyszukiwać imienia na podstawie kolejności indeksu książki telefonicznej.

Te INCLUDEpola są jak numer telefonu, adres itp inne informacje dla każdego wpisu w książce.

EDYTOWAĆ:

Aby wyjaśnić, dlaczego nie używać:

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

Indeks ten jest przydatna tylko jeśli masz albo EmployeeIDczy OBA EmployeeID oraz LastNamew WHEREklauzuli. To w zasadzie OPPOSITE tego, czego potrzebujesz do tego zapytania.

Wydaje mi się, że nadal możesz używać indeksu (identyfikator_użytkownika, identyfikator_działu), ale w frazie „musisz” wstawić wiersz „fikcyjny”, na przykład: „identyfikator_użytkownika = identyfikator_użytkownika”

• posiadający indeks na (identyfikator_użytkownika, identyfikator_oddziału),
• konieczność wyszukiwania / ograniczania tylko dla ID_ departamentu
• wiedząc, że nie użyje indeksu, ponieważ zła kolejność (lub rzeczy się zmieniły, a następująca „sztuczka” nie jest już potrzebna. Jestem „stara”?) .

• Używać „starej” sztuczki?

  wybierz * z Employee emp
  gdzie emp.employee_id = emp.employee_id
  i emp.department_id = 5
  

(Więc nie skupiam się na włączeniu tutaj Lastname, ale na tak / lub nieużywanie klucza.)

Z poważaniem,

Miguell