Kiedy stosować indeks klustrowany, a kiedy nieklustrowany (Clustered Index vc Nonclustered Index)

W kluczu clustered w poziomie liści znajdują się wszystkie dane rekordu (poza danymi typu nvarchar, binary itp.) Takie podejście ma jedną wielką zaletę. Kiedy zapytanie ma wyszukać rekord, to kiedy już znajdzie ten rekord, to ma „pod ręką” wszystkie potrzebne dane. Na dodatek indeks clustered, jeżeli jeszcze się nie pofragmentował na dysku, ma rekordy ułożone na dysku w fizycznej kolejności zgodnej z logiczną kolejnością wynikającą z indeksu.
Zobacz taki przykład:
Najpierw przygotowanie do przykładu:

USE AdventureWorks

SET STATISTICS IO ON

(Ta opcja powoduje, że będize widać ile odczytów musi wykonać Database Engine w celu realizacji odczytu.)
Teraz zażyczę sobie wsyświetlenia wszystkich informacji o osobach mieszkających pod określonym kodem pocztowym

SELECT * FROM Person.Address

WHERE PostalCode=’92173′

Jest jedna taka osoba, ale na moim komputerze trzeba było przeczytać 218 bloków, czyli prawie całą tabelę, która liczy sobie 280 bloków!

Gdybym poprosił o dane pracownika nr 1052 to wystarczą 2 odczyty:

SELECT * FROM Person.Address

WHERE AddressId=1052

Dlaczego tak mało? Bo AddressId to podstawa indeksu clustered. Szybko znalazłem rekord, a jak go już znalazłem, to wszystkie dane były pod ręką!
Pięknie widać to jeżeli włączyć do tego plan wykonania.

Zobacz jak można poprawić działanie pierwszego zapytania. Może przydałby się indeks. Ale jaki? Clustered już nie, bo w danej tabeli nie można mieć dwóch indeksów klustrowanych, bo niby jak można by poukładać na dysku w fizycznej kolejności rekordy w dwóch różnych logicznych kolejnościach!? No więc z braku innych możliwości decydujemy się na indeks nonclustered:

CREATE NONCLUSTERED INDEX IX_Person_Address_PostalCode

ON Person.Address(PostalCode)

Powtarzamy zapytanie i… o rety tylko 4 odczyty!

SELECT * FROM Person.Address

WHERE PostalCode=’92173′

Dlaczego? Zobacz do planu wykonania:

2 odczyty zostały poświęcone na wyszukiwanie w indeksie nonclustered (operacja INDEX SEEK), a kiedy rekord się znalazł, to trzeba było poszukać dodatkowych danych tego rekordu, które znajdowały się w indeksie clustered. Stąd kolejne 2 odczyty potrzebne na wykonanie operacji KEY LOOKUP. Szkoda, że jak już znalazłem rekord, to nie miałem wszystkiego co potrzebne żeby zwrócić dane, tylko musiałem jeszcze czegoś doszukać…

Na dodatek, czasami może się okazać, że zapytanie wygenerowane wobec klucza nonclustered byłoby na tyle kosztowne (przez czytanie i indeksu i potem tabeli), że optymalizator zdecyduje się po prostu zrobić skan tabeli. Zobacz – chociaż jest indeks nonclustered po postalcode, to optymalizator i tak skanuje całą tabelę dla poniższego przykładu:

SELECT * FROM Person.Address

WHERE PostalCode=’98168′

Dlaczego? Bo zwraca dużo rekordów (215)wobec 19641 rekordów całej tabeli. Skanowanie to koszt 280 odczytów.

Oczywiście można zmusić serwer do użycia indeksu za pomocą  tzw. table hint INDEX”

SELECT * FROM Person.Address

WITH (INDEX(IX_Person_Address_PostalCode))

WHERE PostalCode=’98168′

Ale ta ‘poprawka’ spowodowała, że mamy do wykonania 454 odczyty! Dlaczego? Właśnie przez KEY LOOKUP, czyli doszukiwanie brakujących danych, które trzeba dobrać z indeksu clustered. To właśnie dlatego Microsoft nie zaleca stosowania table hint na wyrost.

Podsumowując:

Jedyną wadą indeksu klustrowanego jest to, że zajmuje na dysku więcej niż indeks nieklustrowany. Reszta to zalety.

Tak to już jest. Pracując z bazami danych wiesz, że użytkownicy będą szukać wg różnych kryteriów.  Odgadujesz, co będą robić najczęściej. To znalezione kryterium najczęściej staje się kluczem klustrowanym, a pozostałe, żeby chociaż trochę poprawić wydajność pracy stają się indeksami nieklustrowanymi.

Jeżeli tabela ma kolumnę IDENTITY, to bywa, że staje się ona kluczem klustrowanym. Dlaczego? Bo jak będziesz dopisywał nowe rekordy, to będą one otrzymywać numer ID generowany przez IDENTITY i będą umieszczane na końcu tabeli. W ten sposób tabela się nie pofragmentuje! To może być decydujący argument do wyboru indeksu klustrowanego (i klucza podstawowego) dla tabel często modyfikowanych (OLTP). Pozostałe klucze stają się wtedy z konieczności nieklustrowanymi.

Jak walczyć z opisanymi tu problemami?
Pisać w liście select, co cię interesuje, a nie wpisywać *
Dodać do indeksu tzw kolumny dołączane za pomocą klauzuli INCLUDES.

Po przykładach można po sobie posprzątać:

DROP INDEX IX_Person_Address_PostalCode ON Person.Address

Więcej na ten temat znajdziesz również w książce Training Kit 70-432 w rozdziale dotyczącym Tuning Advisora.

Zapraszam też do przeczytania innych artykułów na mobilo/mobilo24

Zapraszam też na autoryzowane szkolenie SQL Opole/Katowice/Warszawa
Mam nadzieję, że chociaż trochę pomogłem…