Indeksy filtrowane – jak to działa

W tematyce baz danych podstawową techniką przyspieszania czy też zwiększania wydajności zapytań są indeksy. Jestem niemal pewny, że zdecydowana większość czytelników niniejszego bloga wie czym jest indeks bazodanowy i zdaje sobie sprawę jak go użyć. Każdy rozróżnia indeksy klastrowane i nieklastrowane, nowe indeksy kolumnowe itd. Natomiast zdecydowanie mniej osób zdaje sobie sprawę z istnienia tzw. indeksów filtrowanych, a jeszcze mniej je stosuje w swoich bazach. W ramach niniejszego artykułu powiemy sobie kilka słów na ich temat oraz wskażemy jak można je efektywnie wykorzystać.

Indeks filtrowany jest to nic innego jak tradycyjny indeks rowstore posiadający klauzulę WHERE. Czyli innymi słowy jest to indeks, który nie będzie zawierał wszystkich wartości kolumn wskazanych w swojej definicji. Tworzenie takiego obiektu jest bardzo proste i wygląda następująco:

W samej definicji nie ma nic zaskakującego – wskazujemy na jakiej tabeli chcemy widok oraz według jakich kluczy będzie on posortowany. Jeżeli istnieje taka potrzeba to możemy dodać opcjonalną sekcję INCLUDE oraz warunek filtrujący WHERE. To co jest warte odnotowania już na samym początku to fakt, że w warunku filtrującym możemy mieć jedynie proste wyrażenia – nie możemy używać operatora OR, kalkulacji czy też funkcji. Stwórzmy sobie zatem taki indeks na naszej bazie WideWorldImporters:

W tym indeksie będą się zawierały tylko te linie faktur których PackageTypeID jest równy 10. Jeśli tego nie zmieniliśmy to wraz z naszym indeksem stworzone zostały statystyki i tak – one również są filtrowane:

Statystyk zazwyczaj sami nie tworzymy (chyba, że chcemy stworzyć statystyki skorelowane ale o tym innym razem) ale możemy tego dokonać używając następującej składni:

W przypadku statystyk filtrowanych warto pamiętać o ich aktualizacji ze względu na fakt, iż automatyczna aktualizacja nastąpi w określonych przypadkach gdy zmieniona zostanie określone jako klucz indeksu kolumna – a nie warunek filtrujący. Z tego też powodu warto dodać do definicji kluczy indeksu warunek filtrujący bądź zaplanować właściwą strategię aktualizacji statystyk.

Idąc za ciosem napiszmy sobie zapytanie, które wprost z tego indeksu będzie korzystać – upewnijmy się przy tym, że nasz indeks będzie pokrywający w stosunku do tego zapytania:

Aby przekonać się czy nasz indeks został wykorzystany czy też nie wyświetlmy sobie plan zapytania:

Jak możecie zobaczyć mamy na powyższym planie bardzo prosty i zrozumiały sposób wykonania zapytania. Nasz indeks został wykorzystany poprzez operację Indeks Seek. Zapewne zastanawiacie się jaki jest zysk wydajnościowy w porównaniu do tradycyjnego indeksu niefiltrowanego – odpowiedź jest znana i brzmi “to zależy”. Zależy od bardzo wielu czynników ale przede wszystkim od tego w jak dużym stopniu nasz indeks filtrowany jest mniejszy w porównaniu do indeksu niefiltrowanego – bo jak wiemy im mniejszy obiekt tym szybciej można go pobrać do pamięci. Jednakże w celach poglądowych stwórzmy sobie indeks niefiltrowany i porównajmy koszty i czasy:

Wyniki porównania wyglądają następująco:

Kosztowo sytuacja była łatwa do przewidzenia i plan z indeksem filtrowanym jest dużo mniej kosztowny ze względu na mniejsze IO, które wygląda następująco:

W tym aspekcie porównawczym różnica jest bardzo mała i wynosi zaledwie dwie strony – pomijalnie mało. Jednakże pamiętajmy o tym, że w przypadku większej ilości danych czy chociażby większej ilości kolumn wewnątrz tego indeksu różnice mogą być zdecydowanie większe! Statystyki czasowe są niemal identyczne dlatego też pozwolę sobie je pominąć.

Przechodząc dalej pomyślmy, że znaleźliśmy kandydata na indeks filtrowany i chcemy go umieścić w naszej procedurze składowanej. Zróbmy to zatem i nasze powyższe zapytanie umieśćmy w ciele procedury a warunek filtrujący umieśćmy jako parametr:

Następnie spróbujmy wywołać naszą procedurę z wartością parametru równą wartości naszego warunku filtrującego w indeksie:

Plan tego zapytania wygląda następująco:

Zaraz zaraz… Nasz indeks został całkowicie zignorowany! Ponadto dostaliśmy ostrzeżenie:

Ostrzeżenie o niepasujących indeksach – wśród wskazań znalazły się dwa indeksy niefiltrowane – jeden stworzony powyżej oraz jeden stworzony przeze mnie w celach testowych. Tutaj może pojawić się pytanie jak to możliwe, że indeks nie pasuje skoro tak naprawdę pasuje? Zapewne części z was przyszła odpowiedź do głowy – chodzi o cachowanie planu zapytań. Jeżeli do cache trafiłby plan wykorzystujący indeks filtrowany to co by się stało jakby ktoś wywołał tę procedurę z wartością parametru wykraczającą poza indeks? Użytkownik nie otrzymałby wierszy? Zwrócony zostałby błąd? Nie dowiemy się tego gdyż taka konstrukcja nie jest dopuszczalna. Ale czy to oznacza, że nie mamy wyjścia i indeksy filtrowane będą tylko użyteczne dla zapytań ad-hoc?

Sprawdźmy kilka możliwych rozwiązań – pierwszym z nich będzie stworzenie procedury z przełącznikiem WITH RECOMPILE dzięki czemu będzie ona kompilowana przy każdym wywołaniu:

Niestety nie przyniosło to spodziewanych rezultatów i znów nasz plan został zignorowany:

Pierwsze koty za płoty! Załóżmy, że wiemy, iż nasza procedura będzie wywoływana tylko i wyłącznie z tą jedną wartością parametru i wymuśmy użycie naszego indeksu filtrowanego:

Powyższe zapytanie zakończyło się powodzeniem – spróbujmy zatem wywołać naszą procedurę:

Tutaj pojawia się problem bo dostajemy komunikat błędu:

Bardzo nie dobrze… Czy zatem nie ma sposobu na użycie indeksu filtrowanego w procedurach składowanych. Oczywiście, że jest!
I myślę, że taki o którym za pierwszym razem nie pomyśleliście, a jest nim dynamiczny SQL! Spróbujmy sobie stworzyć nasze zapytanie jako ciąg tekstowy, a następnie wywołać komendą EXEC:

Po wywołaniu procedury z parametrem równym 10 otrzymaliśmy plan wykorzystujący indeks filtrujący:

Gdy wywołamy procedurę z inną wartością to użyty zostanie oczywiście właściwy indeks ( na przykładzie używałem wartość parametru 1):

Rozwiązaniem zagadki jest dynamiczny SQL. Zdaje sobie sprawę z tego, że składanie zapytania jak wartości tekstowej może was odstraszać i oczywiście ma to swoje zalety i wady. Warto pamiętać jednak o tym, że potencjalne zyski mogą być bardzo duże. Jeżeli jest to mimo wszystko nieakceptowalne dla was podejście to rozwiązaniem jest wskazanie kompilacji na poziomie zapytania (OPTION RECOMPILE) lub zbudowanie logiki wykonania w warunku IF:

 

Jak możecie zauważyć indeks filtrujący daje bardzo ciekawe możliwości. Oprócz normalnego wzrostu wydajności z jego wykorzystaniem możemy również użyć go do implementacji unikalności określonego podzbioru danych (np. wtedy gdy kolumna przyjmuje wartości NULL ale jak już wartość została wstawiona to musi być unikalna – w takim przypadku sprawdzi się unikalny indeks filtrowany). Rozważcie zatem użycie warunku WHERE do swoich indeksów – odpowiednio użyty potrafi dać duże korzyści.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

2 Comments

  1. AvatarNorbert Kulski

    O indeksach nigdy za wiele 🙂
    Filtrowane mają potencjał ale mają też parę haczyków – mój “ulubiony” 😉 to zachowanie select/update/insert przy QUOTED_IDENTIFIER OFF

    Reply
    1. Adrian ChodkowskiAdrian Chodkowski (Post author)

      To prawda indeksy są tak szeroką tematyką, że można by o nich pisać i pisać 🙂
      Co do przypadku z QUOTED_IDENTIFIER i jego “zastosowaniach” to mam w dalekosiężnych planach napisać 🙂

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *