Twarda i szybka reguła dołączania kolumn w indeksie

Czy jest jakaś trudna i szybka reguła decydująca o tym, jakie kolumny i w jakiej kolejności powinny być umieszczone w indeksie bez klastrów? Właśnie czytałam 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 zasugerował, aby indeks był taki: 

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 sprawia, że plakat decyduje się na dodanie kolumny LastName. Dlaczego nie inne kolumny? i jak zdecydować, w jakiej kolejności powinniśmy trzymać tam kolumny?

Author: Community, 2011-05-31
Source

4 answers

Sugestia indeksu przez marc_s jest błędna. Dodałem komentarz. (I to też moja odpowiedź została przyjęta!)

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 = kluczowe kolumny = używane do ograniczania i przetwarzania wierszy
    Gdzie, Dołącz, zamów według, Grupuj według itd
  • NonKeyColList = Non-key columns = używane w SELECT i agregacji(np. SUM (col)) po zaznaczeniu/ograniczeniu
 48
Author: gbn,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-31 13:08:06

JNK i gbn udzieliły świetnych odpowiedzi, ale warto również rozważyć szerszą perspektywę - nie skupiając się tylko na jednym zapytaniu. Chociaż to konkretne zapytanie może skorzystać z indeksu (#1): 

Employee(DepartmentID) INCLUDE (Lastname, EmployeeID)

Ten indeks w ogóle nie pomaga, jeśli zapytanie nieznacznie się zmieni, np.: 

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

To wymagałoby indeksu (#2): 

Employee(DepartmentID, LastName) INCLUDE (EmployeeID)
Wyobraź sobie, że miałeś 1000 pracowników w wydziale 5. Używając indeksu # 1, aby znaleźć wszystkie Kowale, musisz przeszukać wszystkie 1000 wierszy w Dział 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 # 2 jest więc bardziej przydatny w obsłudze szerszego zakresu zapytań - ale koszt jest bardziej nadęty klucz indeksu, który sprawi, że strony bez liści indeksu będą większe. Każdy system będzie inny, więc nie ma tu zasady.

Na marginesie warto zwrócić uwagę, że jeśli EmployeeID był kluczem do klastrowania w tej tabeli - zakładając, że indeks klastrowy - nie musisz dołączać EmployeeID - jest on obecny we wszystkich indeksach nieklastrowych, co oznacza, że indeks #2 może być po prostu 

Employee(DepartmentID, LastName)
 19
Author: ,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-31 13:33:35

Nie wiem, 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, dla Twojego przykładu, jedynym polem używanym przez indeks jest DepartmentID, ponieważ znajduje się w klauzuli WHERE.

Pozostałe pola muszą być łatwo dostępne stamtąd. Wybierz na podstawie DepartmentID, a następnie INCLUDE ma te pola w węźle liścia indeksu.

Nie chcesz używać swojego inne przykłady, ponieważ nie będą działać dla tego indeksu.

Pomyśl o indeksie jak o książce telefonicznej. Większość książek telefonicznych jest zamawiana według nazwiska, imienia, środkowego inicjału. Jeśli znasz czyjeś imię, ale nie nazwisko, książka telefoniczna Ci nie pomoże, ponieważ nie możesz wyszukać imienia na podstawie kolejności indeksu książki telefonicznej.

Pola INCLUDE są takie jak numer telefonu, adres itp. pozostałe informacje dla każdego wpisu w książka.

EDIT:

Do dalszego wyjaśnienia dlaczego nie używać: 

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

Ten indeks jest przydatny tylko wtedy, gdy masz EmployeeID lub oba EmployeeID i LastName w klauzuli WHERE. Jest to prawie przeciwieństwo tego, czego potrzebujesz do tego zapytania.

 7
Author: JNK,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-31 13:08:30

Myślę, że nadal możesz używać indeksu (employee_id, department_id), ale będziesz musiał dołączyć "dummy" wiersz w frazie where, jak: "employee_id = employee_id)

  • posiadanie indeksu on (employee_id, departemnent_id),
  • konieczność wyszukiwania / ograniczania tylko na department_id
  • wiedząc, że nie użyje indeksu, ponieważ zła kolejność (lub rzeczy już się zmieniły, a następujący "trick" nie jest już potrzebny. Jestem"starym"?).

  • Użycie stara sztuczka?

    Select * from Employee emp
    gdzie emp.employee_id = emp.employee_id
    i emp.department_id = 5

(więc nie skupiam się tutaj na części include w Lastname, ale na tak / lub nie używanym kluczu.)

Pozdrawiam,

Miguell

 0
Author: Miguel Leeuwe,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-05-27 21:46:08