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:

CREATE NONCLUSTERED INDEX nazwa ON obiekt
(
	klucze
)
WHERE warunki

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:

CREATE NONCLUSTERED INDEX [NCI_Filtered10] ON [Sales].[InvoiceLines]
(
	PackageTypeID ASC,
	StockItemID ASC,
	[InvoiceID] ASC
)
INCLUDE ( 	[Quantity],
	[TaxRate],
	[TaxAmount]) 
WHERE PackageTypeID=10

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:

CREATE STATISTICS [MyStats] ON 
[Sales].[InvoiceLines](PackageTypeID, StockItemID, [InvoiceID]) 
WHERE PackageType = 10;
GO

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:

SELECT SIL.InvoiceID,
       SIL.Quantity,
       SIL.TaxRate,
       SIL.TaxAmount,
       WSI.[StockItemName],
       WSI.[Barcode],
       WSI.[UnitPrice]
FROM [Warehouse].[StockItems] AS WSI
     JOIN [Sales].[InvoiceLines] AS SIL ON SIL.StockItemID = WSI.StockItemID
WHERE SIL.PackageTypeID = 10; 

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:

CREATE NONCLUSTERED INDEX [NCI_NonFiltered] ON [Sales].[InvoiceLines]
(
	PackageTypeID ASC,
	StockItemID ASC,
	[InvoiceID] ASC
)
INCLUDE ( 	[Quantity],
	[TaxRate],
	[TaxAmount]) 

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:

CREATE PROC dbo.GetInvoiceData @PackageTypeID INT
AS
SELECT SIL.InvoiceID,
       SIL.Quantity,
       SIL.TaxRate,
       SIL.TaxAmount,
       WSI.[StockItemName],
       WSI.[Barcode],
       WSI.[UnitPrice]
FROM [Warehouse].[StockItems] AS WSI
     JOIN [Sales].[InvoiceLines] AS SIL ON SIL.StockItemID = WSI.StockItemID
WHERE SIL.PackageTypeID = @PackageTypeID;  

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

EXEC dbo.GetInvoiceData 10

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:

ALTER PROC dbo.GetInvoiceData @PackageTypeID INT WITH RECOMPILE
AS
SELECT SIL.InvoiceID,
       SIL.Quantity,
       SIL.TaxRate,
       SIL.TaxAmount,
       WSI.[StockItemName],
       WSI.[Barcode],
       WSI.[UnitPrice]
FROM [Warehouse].[StockItems] AS WSI
     JOIN [Sales].[InvoiceLines] AS SIL 
	ON SIL.StockItemID = WSI.StockItemID 
WHERE SIL.PackageTypeID = @PackageTypeID
;  

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:

ALTER PROC dbo.GetInvoiceData @PackageTypeID INT WITH RECOMPILE
AS
SELECT SIL.InvoiceID,
       SIL.Quantity,
       SIL.TaxRate,
       SIL.TaxAmount,
       WSI.[StockItemName],
       WSI.[Barcode],
       WSI.[UnitPrice]
FROM [Warehouse].[StockItems] AS WSI
     JOIN [Sales].[InvoiceLines] AS SIL  WITH (INDEX (NCI_Filtered10)) 
	ON SIL.StockItemID = WSI.StockItemID 
WHERE SIL.PackageTypeID = @PackageTypeID

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

EXEC dbo.GetInvoiceData @PackageTypeId=10

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

Msg 8622, Level 16, State 1, Procedure GetInvoiceData, Line 3 [Batch Start Line 132]
Query processor could not produce a query plan because of the hints defined in this query. 
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

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:

ALTER PROC dbo.GetInvoiceData @PackageTypeID INT WITH RECOMPILE
AS
DECLARE @Query VARCHAR(4000)=
'SELECT SIL.InvoiceID,
       SIL.Quantity,
       SIL.TaxRate,
       SIL.TaxAmount,
       WSI.[StockItemName],
       WSI.[Barcode],
       WSI.[UnitPrice]
FROM [Warehouse].[StockItems] AS WSI
     JOIN [Sales].[InvoiceLines] AS SIL 
	ON SIL.StockItemID = WSI.StockItemID 
WHERE SIL.PackageTypeID ='+ CAST(@PackageTypeID AS VARCHAR(5)) 
EXEC(@Query);  

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:

ALTER PROC dbo.GetInvoiceData @PackageTypeID INT 
AS
IF @PackageTypeID=10
SELECT SIL.InvoiceID,
       SIL.Quantity,
       SIL.TaxRate,
       SIL.TaxAmount,
       WSI.[StockItemName],
       WSI.[Barcode],
       WSI.[UnitPrice]
FROM [Warehouse].[StockItems] AS WSI
     JOIN [Sales].[InvoiceLines] AS SIL  
	ON SIL.StockItemID = WSI.StockItemID 
WHERE SIL.PackageTypeID = 10
ELSE
SELECT SIL.InvoiceID,
       SIL.Quantity,
       SIL.TaxRate,
       SIL.TaxAmount,
       WSI.[StockItemName],
       WSI.[Barcode],
       WSI.[UnitPrice]
FROM [Warehouse].[StockItems] AS WSI
     JOIN [Sales].[InvoiceLines] AS SIL  
	ON SIL.StockItemID = WSI.StockItemID 
WHERE SIL.PackageTypeID <> 10

 

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.