Key/RID lookup – co to jest?

Jednym z najprostszych do rozwiązania problemów wydajnościowych w SQL Server są tzw. lookupy o którym chciałbym opowiedzieć w niniejszym artykule – zapraszam do lektury.

Jak wiecie optymalizator SQL Server może zdecydować się na użycie indeksu jeżeli tylko będzie to dla niego w odpowiednim stopniu optymalne. Sam indeks może być odpytywany operacją pełnego skanowania (Index Scan) lub też operacjami odszukania (Index Seek) – mechanizmy te opisałem tutaj. Napiszmy proste zapytanie, które przeszuka tabelę operatorem Index Seek (za przykład posłuży nam tabela DimCustomer z bazy AdventureWorksDW2014):

SELECT LastName FROM dbo.DimCustomer
where LastName='Carter'

Jak widać użyty został operator Index Seek na indeksie o nazwie IX_LastName. Przyjrzyjmy się bliżej temu indeksowi używając komendy sp_helpindex:

sp_helpindex 'dbo.DimCustomer'

IX_LastName jako klucz indeksu ma zdefiniowaną jedynie kolumnę LastName. Jak więc będzie wyglądał plan wykonania w momencie gdy nasze zapytanie dodatkowo będzie potrzebowało kolumny Customer Key?

SELECT CustomerKey,LastName FROM dbo.DimCustomer
where LastName='Carter'

Odpowiedź może być nieco myląca:

Ponownie został wykonany Index Seek – dlaczego tak się dzieje? Ponieważ pamiętamy o tym, że oprócz klucza indeks zawiera również wskaźnik do głównej tabeli ( w tym przypadku wskaźnikiem do indeksu klastrowanego którego kluczem jest CustomerKey). Zmodyfikujmy nasze zapytanie tak aby oprócz dwóch powyższych kolumn pobierało również kolumnę FirstName:

SELECT CustomerKey,FirstName,LastName FROM dbo.DimCustomer
where LastName='Carter'

Tym razem w planie pojawiły się dwa dodatkowe operatory Nested Loops oraz Key Lookup:

Nested Loops jest niczym innym jak algorytmem złączenia (o nim jak i o innych algorytmach złączenia powiemy sobie w ramach innego artykułu) – interesujący dla nas jest operator Key Lookup. Odpowiada on za pobranie z tabeli głównej (w tym wypadku indeksu klastrowanego) wartości kolumn, których indeks sam w sobie nie zawiera. Tak więc uproszczony algorytm działania wygląda tak:

1. Z indeksu IX_LastName pobrane zostały te wiersze, których wartość jest równa ciągowi znaków ‘Carter’ wraz ze wskaźnikami do tabeli głównej tj. CustomerKey
2. Dla każdego wiersza zwróconego przez Index Seek wyszukiwane (po kluczu) są pasujące rekordy w tabeli głównej
3. Z pasujących rekordów wybierana jest wartość dla kolumny LastName i zwracana do użytkownika

Koszt takiej operacji był na tyle mały, że optymalizator zdecydował się na niego – w innym przypadku gdy np. nasz warunek WHERE nie byłby dostatecznie selektywny przeskanowanie całej tabeli może być mniej kosztowne niż wyszukanie rekordów, a następnie łączenie się do tabeli źródłowej aby pobrać brakujące wartości. W tym przypadku punkt graniczny nazywany jest Tipping Point – można go obliczyć ręcznie na podstawie pewnego wzoru, który z pewnością opiszę w jednym z przyszłych postów. W poście opisującym Index Seek i Index Scan wspomniałem, iż jest to punkt wyboru pomiędzy Scan i Seek – tutaj możemy to doprecyzować, że jest to punkt pomiędzy Scan i Seek + Lookup. Przykładem gdy Tipping Point został przekroczony jest poniższe bezwarunkowe zapytanie:

SELECT CustomerKey,FirstName,LastName 
FROM dbo.DimCustomer

Jak widać w zapytaniach tego typu indeks został całkowicie zignorowany. Scenariusz ten jest niestety najgorszym z możliwych w naszym przypadku. Nie jest to problem gdy mamy małą tabelę – co jednak gdy mamy do czynienia z wielomilionową tabelą transakcji? No właśnie. W takim przypadku zarówno pełne skanowanie jak i lookup nie pomoże. Lekarstwem w tym przypadku jest indeks pokrywający czyli covering index. Jest to nic innego jak indeks, który zawiera wszystkie potrzebne w naszym zapytaniu kolumny. Tak więc stwórzmy sobie nowy indeks, który będzie spełniał cechy indeksu pokrywającego dla naszego zapytania:

CREATE NONCLUSTERED INDEX [IX_LastNameFirstName] ON [dbo].[DimCustomer]
(
	[LastName] ASC,
	[FirstName] ASC
)
GO

Następnie wykonajmy nasze zapytanie i spójrzmy na plan wykonania:

Jak widać nie było potrzeby “sięgania” do tabeli źródłowej. Stworzony przez nas indeks będzie bardzo efektywny w przypadku gdy wyszukujemy informacje po kolumnie LastName lub po LastName i FirstName. Jeżeli chcemy zawrzeć kolumnę w indeksie jednakże wiemy, że nie będzie ona używana w klauzuli WHERE to lepszym wyjściem będzie jej zawarcie na poziomie liści indeksu używając słowa kluczowego INCLUDE. Kolumny zawarte w indeksie przy użyciu INCLUDE nie będą zawarte na poziomach innych niż liście czyli nie będą częścią klucza – dzięki temu nasz indeks będzie mniejszy. Analogiczna do powyższego składnia ze słowem kluczowym INCLUDE została zawarta poniżej:

CREATE NONCLUSTERED INDEX [IX_LastNameFirstName] ON [dbo].[DimCustomer]
(
	[LastName] ASC
)
INCLUDE
(FirstName)
WITH (DROP_EXISTING=ON)
GO

Po wykonaniu zapytania naszym oczom powinien ukazać się plan jakiego oczekiwaliśmy:

Oprócz operatora Key Lookup możecie w swoich planach znaleźć również operator RID Lookup. Pełni on analogiczną funkcję z tym, że zamiast łączenia z indeksem zgrupowanym łączy się ze stertą tj. tabelą bez indeksu zgrupowanego.

Powyższy plan powinien dać nam do myślenia tj. powinniśmy się zastanowić czy występowanie sterty to coś czego na pewno potrzebujemy oraz czy nie powinniśmy stworzyć indeksu pokrywającego.

W przypadku występowania operacji lookup w naszym planie wykonania warto stworzyć indeks pokrywający aby przyspieszyć działanie całego zapytania. Dzięki temu możemy uniknąć niepotrzebnych operatorów tak jak wspomniany lookup czy też operator złączenia (Nested Loops). Warto podkreślić, że same operatory lookup nie są czymś z czym bezwzględnie trzeba walczyć – po prostu w bardzo wielu przypadkach można je wyeliminować odpowiednio dopasowanym indeksem. Ta prosta technika pozwala w znacznym stopniu przyspieszyć bardzo wiele zapytań – mam nadzieję, że okaże się dla was przydatna.