Indeksy w SQL Server

Czym jest indeks w bazie danych wie z pewnością każdy deweloper. Jednak już o samych strategiach i nowych ich możliwościach rzadko się piszę. W tym artykule opiszę rodzaje indeksów w bazie MS SQL Server, strategiach ich nakładania, a także przedstawię przykłady uzyskanych wyników przy zastosowaniu różnych indeksów i konsekwencjach ich braków.

Indeks to obok normalizacji jeden z najważniejszych tematów w bazach danych. Indeks to nic innego jak struktura danych ułatwiająca wyszukiwanie danych. Wyobraźmy sobie sytuacje w której mamy plik z zapisanymi 3000 wierszy danych. Aby znaleźć interesujące nas informacje średnio będziemy potrzebować przejrzeć połowę zawartości tego pliku - czyli dokonamy około 1500 odczytów. Dzięki zastosowaniu indeksu jesteśmy w stanie zmniejszyć ten wskaźnik do 4 odczytów. Zysku takiego dokonuje się poprzez zastosowanie drzewiastej struktury danych B-drzewa oraz B+-drzewa (w dziale literatura na końcu tego artykułu znajdują się linki opisujące dokładnie te struktury danych).
Przyjrzyjmy się różnym typom indeksów obecnych w MS SQL Server.

Nonclustered:

Indeks ten, można sobie wyobrazić jako skorowidz w książce. Jeżeli weźmiemy do ręki katalog narzędzi w którym mamy 600 stron, a chcemy znaleźć konkretne narzędzie np. młotek, to szkoda w takim wypadku przeglądać kartka po kartce w poszukiwaniu informacji. Łatwiej zaglądnąć do skorowidza i tam w szybki sposób zlokalizować stronę na której znajduje się opis szukanego przez nas narzędzia.
Tak więc:
- dane fizycznie nie są ułożone zgodnie z logiczną kolejnością danych reprezentowaną przez ten indeks
- stosowany jest w klauzulach typu: JOIN, WHERE, ORDER BY
- dobre dla tabel często modyfikowanych
- dla SQL Servera 2005 możemy utworzyć 249 takich indeksów w tabeli, dla 2008 - 999
- wszędzie tam, gdzie mamy niewielką selekcję danych z tabeli 0 - 5% wszystkich wierszy z docelowej tabeli

Clustered

W przeciwieństwie do poprzednio omawianego indektu, ten indeks definiuje sposób w jaki dane są ułożone na dysku.
Podsumowując:
- tylko jeden na tabelę
- determinuje kolejność składowania danych w pliku
- jeżeli nie nałożymy go jawnie, jego rolę będzie pełnił PRIMARY KEY
- szczególnie widać jego zalety podczas pobierania przedziałów danych

Nonclustered indeks wraz z dodatkowymi kolumnami

Aby rozwinąć możliwości indeksów nonclustered, można zawrzeć w ich definicji. Dzięki temu można "pokryć" większą ilość zapytań co zmniejszy ilość odczytów z tabeli (ponieważ szukane informacje będą już zawarte w indeksie). Dodatkowo indeks ten może zawierać kolumny które nie są w normalnych okolicznościach dostępne dla indeksów typu nonclustered (np. varchar(max)).
Podsumowując:
- indeks nonclustered wraz z dodatkowymi kolumnami pozwala na dołączenie dodatkowych kolumn
- indeks taki może dodatkowo zawierać dane które w normalny sposób nie mogłyby zostać zaindeksowane
- zmniejsza ilość odczytów i dostępów do tabeli
- maksymalna ilość kolumn dołączony to 1023

Filtered indeks

Pozwala indeksować dane na podstawie wyspecyfikowanego warunku. Dzięki temu pokrywa tylko część danych.
Najważniejsze fakty dotyczące tego indeksu:
- typ nonclustered wraz z klauzulą WHERE
- mniejsza ilość zajmowanego miejsca
- zmniejsza narzut podczas tworzenia statystyk
- zmniejsza narzut podczas dodawania nowych danych, ponieważ indeks tworzony jest tylko dla danych wyspecyfiowanych w klauzuli WHERE
- zmniejsza czas podczas odbudowy tabeli.
Prosty przykład zastosowania. Mamy tabelę użytkowników. Jeżeli zastosujemy filtered indeks na kolumnie aktywność z warunkiem aktywny (WHERE aktywny='A') wtedy dane dodawane z aktywnością 'N' (nieaktywny) są nieindeksowane co zmniejsza wartość wielkości pliku indeksu jak i przyspiesza dodawanie takich rekordów.

Indeksowane widoki

Z pewnością, czasem rzadziej czasem częściej przychodzi nam stosować widoki. Widoki ułatwiają przede wszystkim korzystanie z wielokrotnie wykorzystywanych zapytań, posiadających skomplikowaną logikę. Na takim widoku możemy stworzyć indeks. Co dodatkowo może on objąć kolumny z różnych widoków.
Można by tu znaleźć podobieństwo do Filtered index, jednak różnic jest więcej niż podobieństw. Bardzo dobre porównanie przedstawił w swoim artykule Kanasz Robert. Przedstawię w skrócie najważniejsze punkty:

Fill factor

Fill factor to modyfikator pozwalający na określenie czy SQL Server ma pozostawiać wolne miejsce w ramkach. Po co takie wolne miejsce pozostawiać albo nie pozostawiać? Otóż w przypadku kiedy do tabeli wstawiane są nowe dane silnik bazy danych musi wygospodarować miejsce aby je ulokować w odpowiedniej kolejności. W najgorszym przypadku musi przenieść znaczną część danych na następne rami co odbija się na wydajności. Dobierając odpowiednio Fill factor, możemy w zależności od sytuacji określić w jaki sposób ma być zagospodarowane miejsce w ramce. Fill factor przyjmuje wartości od 0 - 100%. 100% w tym przypadku oznacza, że nie pozostawiamy żadnego dodatkowego miejsca. Ustawienie takie jest dobre dla tabel, które będą tylko odczytywane - czyli tabele tylko do odczytu. W przypadku mniejszej wartości zostanie zostawione więcej miejsca, co ograniczy potrzebę przenoszenia informacji między ramkami.
Strategie nadawania:
- tabela w których jest mało operacji zmian danych - parametr na 90 - 100
- dane będą często zmieniane - 60 - 80

Czas na podsumowanie i parę rad podczas tworzenia indeksów:

- podczas tworzenia indeksów, starajmy się je budować na jednej kolumnie lub tylu ile jest faktycznie potrzebne. Co to oznacza? Otóż im krótszy jest indeks tym szybciej silnik bazy danych sprawdzi go, a także ilość zajmowanego miejsca będzie najmniejsza.
- w każdej tabeli powinniśmy nakładać clustered index na kolumnie wykorzystywanej najczęściej w operacjach wyszukiwania
- nakładając clustered index starajmy się go nakładać na kolumnę z dużą selektywnością (mała ilość duplikatów kluczy)
- unikaj nakładania clustered index na kolumny, które często podlegają operacji uaktualniania. Oprócz samego uaktualnienia indeksu na kolumnie której jest nałożony, muszą zostać także uaktualnione wszystkie indeksy nonclustered odwołujące się do tego indeksu.
- unikajmy i usuwajmy zbędne czy też powtarzające się indeksy
- indeksy nonclustered powinniśmy (o ile to jest możliwe) tworzyć na osobnym dysku, zapewniając tym samym poprawienie wydajności
- tworząc złożony indeks (z kilku kolumn) najbardziej selektywną kolumnę wprowadzamy od lewej strony indeksu

Przejdźmy teraz do części praktycznej omawianego artykułu.

Dla przykładu stworzyłem nową bazę z tabelą Person o następującej strukturze:

Code:

CREATE TABLE [dbo].[Person]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[BirthDate] [datetime] NULL,
	[Activ] [char](1) NULL,
        CONSTRAINT [PK_Person] PRIMARY KEY
)

Do tabeli załadowałem 1 mln wierszy danych testowych:

Mając dane możemy rozpocząć nasze testy.

1. Wyszukiwanie i wyświetlenie wartości całego wiersza:

Jak widać dla zapytania Estimated Subree Cost jest równy 5,7. Jest to bardzo duży wynik i naszym zadaniem jest zmniejszenie go jak najbardziej się da. Na kolumnie firstName nie ma indeksu tak więc tabela musiała zostać przejrzana w całości.

2. Wyszukiwanie i wyświetlenie wartości całego wiersza - nałożony indeks nonclustered
Założymy na kolumnie firstName indeks typu nonclustered:

CREATE INDEX IX_Person_FistName
ON Person (FirstName);

Po tej operacji wykonamy ponownie nasze zapytanie:

Jak widać koszt spadł diametralnie.

3. Wyszukiwanie i wyświetlenie wartości kolumny na której jest nałożony nonclustered index

Jeżeli nasze zapytanie przyjmie postać:
SELECT FirstName
FROM Person
WHERE FirstName='Ślipienko'

wtedy koszt zapytania będzie wynosić:

4. Dołożenie do indeksu nonclustered dodatkowych kolumn

Aby uniknąć dodatkowego odwołania do tabeli, można do indeksu dołożyć dodatkowe kolumny:
CREATE INDEX IX_Person_FistName
ON Person (FirstName)
INCLUDE(LastName,City)

Dzięki dodaniu dodatkowych kolumn do indeksu, nie jest potrzebne odwołanie się do tabeli po dane.


5. Wykorzystanie Filtered index:
W tabeli mamy użytkowników aktywnych i nieaktywnych. Nasz klient w większości przypadków będzie chciał oglądać użytkowników aktywnych. Zobaczmy proste zapytanie wyświetlające użytkowników aktywnych:
SELECT FirstName,LastName,City
FROM Person
WHERE Activ='A'

Jak widać koszt zapytania jest bardzo duży. W tym przypadku, wiemy jakich danych oczekuje użytkownik. Skorzystamy więc z filtered index:
CREATE INDEX IX_Person_FistName
ON Person (FirstName)
INCLUDE (LastName,City)
WHERE Activ='A'

Po wykonaniu zapytania otrzymujemy wynik:

Wynik znacząco się poprawił.



Podsumowując otrzymane wyniki można stwierdzić, że stosując dobrze dobrane indeksy można uzyskać znaczące przyspieszenie wyszukiwania danych. W większości systemów na 10 wyszukań przypada 5 insertów. Tak więc koszt utrzymywania indeksów jest niewielki w porównaniu z przyspieszeniem wydobywania danych.



Literatura
http://en.wikipedia.org/wiki/B-tree
http://en.wikipedia.org/wiki/B%2B_tree
http://msdn.microsoft.com/en-us/library/dd171921%28v=sql.100%29.aspx
http://www.codeproject.com/KB/database/DB_Prerformance_Tuning_1.aspx
http://msdn.microsoft.com/en-us/library/aa933139%28v=sql.80%29.aspx