Dlaczego utworzenie tego nowego indeksu tak bardzo poprawiło wydajność, gdy istniejący indeks zawierał wszystkie kolumny w nowym indeksie?

Mam tabele Log i LogItem; Piszę zapytanie, aby pobrać dane z obu. Istnieją tysiące Logsi każdy Logmoże mieć do 125LogItems

Zapytanie jest skomplikowane, więc pomijam je (jeśli ktoś uważa, że to ważne, prawdopodobnie mogę je opublikować), ale kiedy uruchomiłem plan SSMS Estimated Query, powiedział mi, że nowy indeks nieklastrowany poprawi wydajność nawet o 100% .

Existing Index: Non-clustered
Key Colums (LogItem): ParentLogID, DateModified, Name, DatabaseModified

Query Plan Recommendation
CREATE NONCLUSTERED INDEX [LogReportIndex]
ON [dbo].[LogItem] ([ParentLogID],[DatabaseModified])

Dla zabawy stworzyłem ten nowy indeks i uruchomiłem zapytanie. Ku mojemu zdziwieniu, moje zapytanie działa teraz około 1 sekundy, zanim było ponad 10 sekund.

Zakładałem, że mój istniejący indeks obejmowałby to nowe zapytanie, więc moje pytanie brzmi: dlaczego utworzenie nowego indeksu dla jedynych kolumn użytych w moim nowym zapytaniu poprawiło wydajność? Czy powinienem mieć indeks dla każdej unikalnej kombinacji kolumn używanych w moich whereklauzulach?

Uwaga: Nie sądzę, że dzieje się tak, ponieważ SQL Server buforuje moje wyniki, uruchomiłem zapytanie około 25-30 razy, zanim utworzyłem indeks, i konsekwentnie zajęło to 10-15 sekund, po indeksie jest teraz konsekwentnie ~ 1 lub mniej.

sql-server index

— Nate
źródło

Zanim stworzył dodatkowy indeks nieklastrowany, co zrobił rzeczywistego wykonania planu show dla użycia indeksu?

— Thomas Stringer,

Co to jest poprawiona wydajność o 100%?

@Shark Dobre pytanie, nie jestem pewien. To jest moja pierwsza sytuacja debugowania wydajności. Na pewno to wykorzystam. Powiedział tylko „brakujący indeks” i powiedział, które pola.

@JeffO Oto, co powiedział SSMS: „Procesor zapytań szacuje, że wdrożenie następującego indeksu może poprawić koszt zapytania o 100%”.

Odpowiedzi:

Ważna jest kolejność kolumn w indeksie. Jeśli filtrowanie wymaga kolumny 1 i 4 z indeksu, indeks nie pomoże. Jest to przydatne tylko podczas filtrowania według pierwszych N kolejnych kolumn.

Wynika to z faktu, że indeks jest drzewem. Nie można skutecznie wybrać wszystkich węzłów drzewa column3 = something, ponieważ, ponieważ są one rozproszone po wszystkich innych miejscach, należących do różnych wartości column1i column2. Ale jeśli wiesz column1i column2tak, znalezienie odpowiedniej gałęzi na drzewie nie jest żadnym problemem.

— GSerg
źródło

Czy byłoby zatem bezpiecznie założyć (ogólnie), że potrzebuję jednego indeksu na zestaw klauzul „gdzie”, które trafią do tej tabeli?

Kiedyś zrobiłem ogromne przyspieszenie czyjeś zapytania, tylko upewniając się, że używa indeksu w odpowiedniej kolejności.

@Nate Zasadniczo tak. Niektóre wheres mogą się nakładać, więc możesz mieć indeks, który ładnie obejmuje kilka wheres; lub możesz zignorować część whereklauzuli, ponieważ indeksowanie w określonej kolumnie i tak nie pomoże (niska selektywność); ale ogólnie tak.

@Nate Nie chcesz mieć więcej indeksów niż to konieczne. Każdy indeks, który SQL musi utrzymywać, dodaje własny narzut. Jeśli możesz zmienić kolejność klauzul WHERE, tak aby pasowały do pierwszych N kolumn w istniejącym indeksie, powinno to być bardzo bliskie bez dodawania dodatkowych indeksów.

— Ten Chuck Guy

@ChuckBlumreich Kolejność kolumn w whereklauzulach nie jest ważna. Serwer zawsze zorganizuje je tak, aby jak najlepiej wykorzystać istniejące indeksy. To tylko kwestia posiadania indeksu zawierającego wszystkie wymagane wherekolumny jako pierwsze kolumny.

Krawędź natarcia indeksu jest to, co się liczy.

Dopóki twoje zapytanie jest „objęte” wiodącą krawędzią indeksu, będzie wydajne. Indeksy baz danych są zazwyczaj implementowane jako B-Drzewa, a struktura B-Drzewa nakazuje, aby wyszukiwanie odbywało się w określonej kolejności, dlatego kolejność pól w indeksie złożonym ma znaczenie.

Jeśli masz „dziury”, np. Jeśli wyszukujesz ParentLogIDi DatabaseModifiedindeks jest włączony {ParentLogID, DateModified, Name, DatabaseModified}, ale tylko {ParentLogID}część indeksu można efektywnie wykorzystać.

(UWAGA: Niektóre DBMS mogą wykorzystać tę {DatabaseModified}część poprzez „pominięcie skanowania”, ale nawet jeśli DBMS to robi, jest to znacznie mniej wydajne niż zwykły dostęp do indeksu) .

— Branko Dimitrijevic
źródło

Więc jeśli mam Columns (a, b, c, d, e, f)i większość zapytań jest ... WHERE A IN(...) AND B = 3moim indeksem Index(a,b,c,d), to jest dobry, ale to nie pomaga, jeśli tak, ... WHERE A IN (...) AND D = 5dlatego właśnie mój nowy indeks, który utworzyłem, Index(a,d)poprawił tak bardzo wydajność, prawda?

@Nate - poprawne. Pomyśl o tym jak o książce telefonicznej. Jeśli znasz tylko czyjeś imię, nie można go znaleźć bez przejrzenia całej książki, ponieważ jest on zorganizowany w Lastname, Firstname

— JNK