środa, 3 września 2008

Optymalizacja cz.1 – Indeksy

Wstęp
Pierwszą część dotyczącą optymalizacji baz danych rozpocznę od indeksów. Indeks to uporządkowany zbiór danych. Przykładem indeksu jest książka telefoniczna, która zawiera numery telefonów uporządkowana wg nazwiska czy nazwy firmy.


Korzystając z odpowiednich indeksów można w znaczący sposób skrócić czas wykonywania zapytania do bazy danych. Nic jednak za darmo. Indeksy mają swój koszt i muszą one być odpowiednio przemyślane. Kosztem indeksu jest miejsce na dysku oraz czas związany z operacjami modyfikacji danych (INSERT, UPDATE, DELETE).

Tak jak każda baza danych jest inna tak też nie ma uniwersalnej reguły dotyczącej indeksów. Mając na uwadze fakt, że w przypadku dodania, usunięcia lub modyfikacji danych poza aktualizacją tabeli aktualizowane są także indeksy, byłoby nie rozsądnym dodawanie zbyt wielu indeksów w bazie danych, która jest często aktualizowana. Natomiast w przypadku bazy danych, która jest tylko odczytywana i służy do produkcji raportów większa liczba indeksów niewątpliwie będzie korzystna.

A jak to wygląda w praktyce...
Nie zanudzając teorią przyjdźmy do praktycznego zastosowania. We wszystkich przykładach wykorzystywać będę bazę danych Northwind.
Aby pokazać korzyści płynące z indeksów, usunąłem z tabel istniejące już wcześniej indeksy.
W przypadku braku jakiegokolwiek indeksu skanowana musi być cała tabela.

select orderid, customerid, orderdate from orders
where OrderDate > '19970101'


Powyższe zapytanie generuje następujący plan zapytania
Zapytanie to także wymagało 20 logicznych operacji odczytu.
Utwórzmy więc indeks, który pokryje wszystkie kolumny biorące udział w zapytaniu. W zależności od tego czy baza danych jest na SQL Server 2000 czy na SQL Server 2005 mamy do dyspozycji dwa typy indeksów. W przypadku SQL Server 2000, jeżeli byśmy chcieli utworzyć indeks, który pokryłby wszystkie kolumny biorące udział w zapytaniu, wszystkie te kolumny muszą być kluczami indeksu.
Poniższy kod utworzy nam pożądany indeks.
create index idx_ncl_od on orders(orderdate, orderid, customerid)
Wadą tego indeksu jest to, że w przypadku aktualizacji wszystkie klucze muszą być odpowiednio aktualizowane.
Znacznie bardziej optymalne rozwiązanie umożliwia nam SQL Server 2005, gdzie kluczem indeksu będzie kolumna, która używana jest jako predykat, natomiast pozostałe kolumny dodamy w klauzuli include. W przypadku modyfikacji danych tylko jedna kolumna będzie powodowała dodatkowy koszt. Naturalnie wielkość indeksu na dysku będzie taka sama w przypadku obu rozwiązań. Poniżej zapytanie, które utworzy nam indeks, który pokryje wszystkie kolumny w zapytaniu.
create index idx_ncl_in_od on orders(orderdate) include(orderid, customerid)

Wywołując ponownie nasze zapytanie otrzymujemy poniższy plan wykonania.

Jak widać w tym przypadku nie skanowaliśmy już całej tabeli, a tylko część indeksu.

W przypadku pierwszego zapytania bez indeksu koniecznych było 20 logicznych operacji odczytu. W drugim przypadku było ich tylko 5.
Z uwagi na fakt, że indeks ten nie będzie już nam potrzebny w kolejnych przykładach zostanie on teraz usunięty.
drop index idx_ncl_in_od on orders.
Dotychczas korzystaliśmy tylko z indeksu nieklastrowego. Indeks nieklastrowy charakteryzuje się tym, że posiada on dane tylko z tych kolumn, które są jego kluczami lub kolumn dołączonych za pomocą instrukcji include.
Kolejnym indeksem jaki założymy na naszej tabeli będzie indeks klastrowy. Indeks klastrowy poza danymi pochodzącymi z kolumny, która jest kluczem posiada wszystkie pozostałe dane z tabeli. Indeks klastrowy to tak na prawdę dane. Dane w takim indeksie będą posortowane wg. kolumny klucza.
Poniższe zapytanie utworzy nam indeks klastrowy oraz klucz podstawowy na kolumnie OrderID.
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED (OrderID)
Wykonując ponownie nasze zapytanie otrzymamy poniższy plan wykonania.
Jak widać w tym przypadku nie skanujemy już zawartości tabeli a indeks klastrowy, który zawiera wszystkie dane posortowane wg. kolumny OrderID. Przed założeniem tego typu indeksu należy się poważnie zastanowić, która z kolumn będzie jego kluczem. W tabeli możemy mieć tylko jeden indeks klastrowy. SQL Server pozwala nam ponadto utworzyć 249 indeksów nieklastrowych.

W przypadku gdy założymy na tabeli także indeks nieklastrowy, nieunikalny, klucz indeksu klastrowego będzie dodatkowym kluczem indeksu nieklastrowego. Niezależnie od tego czy czy indeks nieklastrowy jest unikalny czy nie, kolumna klucza klastrowego będzie także częścią tego indeksu. Ma to związek z faktem z tym, że w przypadku kiedy w tabeli znajduje się indeks klastrowy indeks nieklastrowy posiada do niego wskaźnik. W przypadku braku indeksu klastrowego indeks nieklastrowy zawiera wskaźnik do wiersza i pliku gdzie znajdują się dane.

Aby to zademonstrować założę teraz na tabeli indeks nieklastrowy, którego kluczem będzie kolumna OrderDate.

CREATE INDEX OrderDate ON Orders(OrderDate)

Wykonajmy teraz poniższe zapytanie i przeanalizujmy plan wykonania.

select orderid, orderdate from orders
where OrderDate > '19970101'

Mimo tego, że zakładając indeks nieklastrowy, jako klucz podaliśmy tylko kolumnę OrderDate nasze zapytanie nie wymagało skanowania całej tabeli, a raczej indeksu klastrowego. Zamiast tego wykonany został częściowy skan tabeli poprzedzony odnalezieniem pierwszego wiersza spełniającego predykat.

Dotychczasowe zapytania wykorzystywały tylko jeden z indeksów, choć tak na prawdę w ostatnim przypadku gdybyśmy nie mieli indeksu klastrowego optymalizator wybrałby skan całej tabeli, lub w przypadku wysokiej selektywności zapytania częściowy skan indeksu nieklastrowego w połączeniu z przeszukaniem wszystkich wskaźników do wierszy i plików zawierających dane.

W ten sposób doszedłem do selektywności zapytań. Czym więc jest selektywność zapytań i jak ona wpływa na wydajność? Selektywność jest to iloraz oczekiwanej liczby wierszy do wszystkich jakie znajdują się w tabeli. 

Wysoka selektywność oznacza relatywnie małą ilość wyników, natomiast niska selektywność dużą ilość wierszy w wyniku zapytania.

Poniższe zapytanie cechuje się wysoką selektywnością.

select orderid, customerid from orders
where OrderDate > '19980505'

Rezultatem tego zapytania będą tylko 4 wiersze, co stanowi 0,48% wszystkich wierszy w tabeli.

Przyjrzyjmy się teraz planowi wykonania.

Z uwagi na fakt, że nasz indeks nieklastrowy nie pokrywa wszystkich kolumn zapytania optymalizator zdecydował, najpierw o wyszukaniu rekordów spełniających nasz predykat, a później dla każdego ze znalezionych wierszy wykonał przeszukanie indeksu klastrowego w celu pobrania danych z kolumny CustomerID. Wyszukanie danych z kolumny CustomerID jest bardzo kosztowną operacją. Dla każdego ze znalezionych wierszy przeprowadzony musi być skan indeksu. Statystycznie indeks skanowany jest w 50%.

Zmienimy teraz nieznacznie zakres danych

select orderid, customerid from orders
where OrderDate > '19980504'

W wyniku tego zapytania otrzymamy 8 wierszy co stanowi 0,96% wszystkich wierszy w tabeli. Przyjrzyjmy się ponownie planowi wykonania zapytania.

Jak wspomniałem wcześniej przeszukanie całego indeksu klastrowego, lub też tabeli w przypadku jego braku, dla każdego wiersza jest bardzo kosztowną operacją. Dlatego też optymalizator zdecydował, że bardziej korzystne będzie zeskanowanie tylko raz całego indeksu klastrowego.
Na dowód tego wykonam kolejne zapytanie, które wykorzysta indeks nieklastrowy.

select orderid, customerid from orders with (index = 3)
where OrderDate > '19980504'

W wyniku tego zapytania otrzymamy poniższy, znajomy już nam plan.

W moim przypadku koszt tego zapytania to 0,023443, podczas gdy poprzedniego, w którym optymalizator sam wybrał najlepsze rozwiązanie to 0,0182691.

Zakończenie
Indeksy mogą w znacznym stopniu zoptymalizować zapytania do bazy danych. W powyższych zapytaniach nie było może to tak bardzo widoczne, gdyż głównie koncentrowałem się na tym by przedstawić jak działają indeksy i jak optymalizator je wykorzystuje. Temat indeksów poruszę ponownie w przyszłości, wtedy też o wiele bardziej widoczne będą korzyści z nich płynące a także koszt, czego w tym artykule nie poruszyłem.