Operacje Table/Index Scan oraz Index Seek

Niejednokrotnie byłem świadkiem sytuacji gdy specjaliści IT mniej lub bardziej związani z bazami danych na pytanie o optymalizację zapytań często odpowiadali w stylu “trzeba zrobić tak, żeby był seek”. Stwierdzenie to w niektórych aspektach jest oczywiście prawdziwe, ale generalizowanie w tej sytuacji jest nie tylko niewłaściwe, ale według mnie  nieakceptowalne. Temat jest nieco bardziej złożony niż niektórym może się wydawać. Ponadto można się zastanowić jaki byłby cel implementacji operatora Scan skoro jest oni taki zły? Na te pytania nie ma jednoznacznej odpowiedzi i w związku z tym postanowiłem napisać parę słów na temat Seek, Scan oraz pojęcia zwanego Tipping Point – zapraszam do lektury!

Zacznijmy od pierwszej operacji z jaką spotykamy się na co dzień tj. Scan. Operacja ta polega na tym, że skanujemy cały obiekt bez jakiejkolwiek filtracji. Scan w SQL Server przybiera dwie formy tj. Index Scan lub też Table Scan (istnieje również operator Remote Scan dla serwerów zlinkowanych aby się z nimi zapoznać polecam mój artykuł, który znajdziecie tutaj). Table Scan pojawia się wtedy gdy mamy do czynienia ze stertą (tabelą bez indeksu zgrupowanego), Index Scan działa analogicznie tylko operacje zamiast na stercie to wykonywane są na strukturze b-drzewa. W przypadku gdy widzimy na planie Table Scan  powinniśmy zadać sobie pytanie czy to właściwe zachowanie, że nasza tabela nie ma zdefiniowanego klucza? Odpowiedź jak większość rzeczy w świecie baz danych jest niejednoznaczna ponieważ obie sytuacje są odpowiednie w określonych sytuacjach.

Zobaczmy Table Scan w akcji – na sam początek stwórzmy tabelę tymczasową będącą kopią tabeli City z tabeli Bazy WideWorldImportersDW:

SELECT
	*
INTO
	#City
FROM
	[Dimension].[City]
GO

Następnie wyświetlmy sobie plan wykonania dla zapytania odpytującego nowo utworzoną tabelę.

SELECT
	*
FROM
	#City

Jak widać powyższy plan jest bardzo prosty i nie ma innego sposobu wykonania podanego przez nas zapytania jak przeskanowanie całej tabeli #City. W przypadku gdy istnieje tylko jedna droga do wykonania określonego zapytania to taki plan wykonania nazywamy planem trywialnym. Dzięki temu, że plan jest trywialny to optymalizator nie musi go poddawać zasobochłonnej optymalizacji kosztowej. O tym czy dany plan jest trywialny czy też nie możemy dowiedzieć się z właściwości OptimizationLevel.

Stwórzmy sobie teraz nową tabelę tymczasową wraz z indeksem zgrupowanym – aby zobaczyć jak w akcji wygląda Index Scan.

SELECT
    *
INTO
    #City_Clustered
FROM
    [Dimension].[City]
GO

CREATE CLUSTERED INDEX CIX_City
ON
#City
(
[City Key]
)

W tym przypadku odpytujemy indeks zgrupowany  co również zostało odnotowane na graficznym planie poprzez słowo Clustered znajdujące się w nawiasie. Jeśli chodzi o kwestie wydajnościowe to pomiędzy użyciem operatora Clustered Index Scan, a Table Scan nie ma większej różnicy wydajnościowej! Bardzo wyraźnie widać ten fakt poniżej gdzie koszt obu zapytań jest równy:

DBCC DROPCLEANBUFFERS
GO

SELECT * FROM #City
SELECT * FROM #City_Clustered

Same operatory działają bardzo podobnie natomiast jest pewna przewaga tabeli z indeksem nad stertą- chodzi mianowicie o to, że dane wychodzące z takiego indeksu są posortowane. Fakt ten może być wykorzystany przez optymalizator, który mając do dyspozycji posortowany zestaw danych może wykorzystać wydajny operator złączenia  (jak np. Merge Join) czy też agregacji, którego schemat działania wymaga właśnie posortowanych danych.

SELECT 
	* 
FROM Fact.[Order] AS F
JOIN Dimension.City AS C
ON
	C.[City Key]=f.[City Key]

Jak już wspomniałem indeks zgrupowany sam w sobie zawiera dane posortowane dlatego też nie było potrzeby jawnego sortowania z wykorzystaniem operatora Sort. W przypadku braku indeksu na tabeli to optymalizator musiałby albo dane posortować operatorem sort, bądź też zdecydował by się użyć innego, mniej wydajnego operatora złączenia. Oba przypadki możemy zobaczyć na poniższym planie wykonania.

Co w przypadku indeksów niezgrupowanych? Czy je również możemy skanować? Oczywiście, że tak! Stwórzmy sobie  taki obiekt na naszej tymczasowej tabeli ze zdefiniowanym wcześniej indeksem zgrupowanym.

CREATE NONCLUSTERED INDEX NCIX_City
ON
#City_Clustered
(
[WWI City ID],
[City]
)

Następnie standardowo odpytajmy naszą tabelę:

SELECT 
[City Key],
[WWI City ID],
[City] 
FROM #City_Clustered

Ponieważ nasze zapytanie pobierało dwie kolumny wchodzące w skład klucza indeksu niezgrupowanego oraz klucza indeksu zgrupowanego, (który jak wiemy również jest zawarty w indeksach niezgrupowanych) użyty został stworzony przez nas indeks nieklastrowy. Jaka jest przewaga planu operatora skanowania indeksu klastrowanego nad planem z operatorem skanowania indeksu nieklastrowanego? Oczywiście indeks nieklastrowany jest podzbiorem indeksu klastrowanego, a więc zajmuje mniej stron danych przez co nasze zapytanie będzie szybsze. Jest to dowód na to dlaczego nie powinniśmy używać “SELECT *” – ponieważ oprócz pobrania wszystkich, niekoniecznie potrzebnych, wierszy jest to równoznaczne z ignorowaniem wszystkich indeksów nieklastrowanych na tabeli.

W porządku, mamy już podstawowe informacje na temat operatorów Scan – co natomiast z operacją Seek? Tutaj jest nieco prościej gdyż nie ma czegoś takiego jak Seek na stercie –  operacja ta może wystąpić jedynie na indeksach b-drzewa – czyli tradycyjnych indeksach clustered i nonclustered (mała dygresja – nie bez przyczyny napisałem indeksach b-drzewa gdyż indeksy kolumnowe  nie posiadają operatora Seek, a jedynie Scan – wynika to z trybu ich działania i wewnętrznej architektury, ale to temat na zupełnie inny post). Aby optymalizator użył Index Seek musimy mieć warunek filtrujący w klauzuli WHERE po kluczu indeksu – przykładowe zapytanie tego typu wraz z planem przedstawiłem poniżej:

SELECT 
	[City Key],
	[WWI City ID]
FROM
#City_Clustered
WHERE  [City Key]=1

To co jest ważne w takim zapytaniu to tzw. Seek predicate, który definiuje po jakiej kolumnie operacja seek na indeksie została wykonana.

Bardzo często tak się zdarza, że warunek w WHERE nie filtruje tabeli tak, że pozostaje jeden wiersz. Czy możliwe jest kilkukrotne wyszukanie Seek w ramach jednego planu wykonania? Jest to możliwe – przekonamy się o tym wykonując poniższe zapytanie.

SELECT 
	[City Key],
	[WWI City ID]
FROM
	#City_Clustered
WHERE  
	[City Key]<10

Jak widać Seek został nadal wybrany przez optymalizator do pobrania tych 10 wierszy. Co ciekawe możecie sobie wyobrazić, że operator Seek pojawia się nawet wtedy gdy w warunku zamiast 10 wpiszemy 23963, natomiast gdy wpiszemy 23964 to wtedy użyty już zostanie operator Scan. Ten magiczny punkt nazywamy  Tipping Point, poniżej tego punktu optymalizator użyje Seek powyżej Scan. Tipping point to nic innego jak punkt gdzie liczba zwracanych wierszy nie jest selektywna na tyle aby optymalizator wybrał index seek. Niejednokrotnie słyszeliście, iż SQL Server wyposażony jest w optymalizator kosztowy i to właśnie koszt zawsze jest wyznacznikiem w planie zapytania tego czy szybciej i wydajniej będzie pobierać dane skanując całą tabelę czy może wywołując operacje Seek. Sam Tipping point jest inny dla każdej tabeli i zależy w dużej mierze od statystyk – przyjrzymy się bliżej temu terminowi omawiając Bookmark lookup w planach zapytań, który pojawi się już niebawem.