Ćwiczenia > Indeksy w MS SQL Server 2000 > Wykorzystanie indeksów

5.3 Wykorzystanie indeksów



W poprzedniej lekcji zaprezentowaliśmy sposób, w jaki można testować działanie indeksów. Jednak musisz wiedzieć, że nie zawsze utworzony przez Ciebie indeks będzie wykorzystywany.

MS SQL Server posiada wbudowany mechanizm zwany Query Optimizer (optymalizator zapytań), który jest odpowiedzialny za optymalizację planu wykonania każdego zapytania. Zapytania bowiem mogą na ogół być wykonane na wiele sposobów. Szczególnie dotyczy to zapytań, które wykorzystują kolumny indeksowane. Okazuje się, że w przypadku poleceń SELECT optymalizator nie zawsze wybiera opcję z wykorzystaniem indeksu. Jakby tego było mało, okazuje się, że nie każde wykorzystanie indeksu jest tym, co wpływa na wydajność (oznacza to, że czasem nawet jeśli indeks zostanie wykorzystany, nie poprawi on wydajności zapytań).

W tej lekcji pokażemy Ci metodę, która pozwoli stwierdzić, czy w danym zapytaniu optymalizator wykorzystuje utworzony przez Ciebie indeks oraz czy indeks jest wykorzystywany w sposób taki, jak planowałeś. Dowiesz się, jakie zapytania SELECT najlepiej nadają się do pracy z indeksami.

Krok 1 - Utwórz tabelę do testowania zapytań

1. Uruchom program Query Analyzer.
2. Zaloguj się używając uwierzytelnienia systemu Windows.
3. W menu głównym programu wybierz File - Open.
4. W oknie Open Query File wybierz plik demo_5_3_1.sql.
5. Zaznacz i uruchom (klawisz F5) fragment kodu oznaczony w komentarzu jako (1).


-- (1) stwotrzmy tabele do cwiczenia 
USE Northwind
GO
 
IF EXISTS (
	SELECT name
	FROM sysobjects
	WHERE name='Employees_kopia')
DROP TABLE Employees_kopia
GO

SELECT EmployeeID, FirstName, LastName, BirthDate
INTO Employees_kopia
FROM Employees
GO


Wynikiem wykonania powyższego kodu jest ustawienie bazy danych Northwind jako bazy roboczej, sprawdzenie istnienia w bazie Northwind tabeli Employees_kopia i usunięcie jej, skopiowanie wybranych kolumn tabeli Employees do nowej tabeli Employees_kopia.

2. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (2). Ten kod już znasz z poprzednich lekcji - powoduje on wyświetlenie listy indeksów w tabeli Employees_kopia. Na razie otrzymasz informację, że dla tabeli nie utworzono żadnego indeksu. 3. W menu głównym programu Query Analyzer wybierz Query - Show Execution Plan. Oglądanie planu wykonania każdego zapytania pozwoli Ci zorientować się, kiedy serwer korzysta z indeksów.

Krok 2 - Wykonaj zapytania wyszukujące bez tworzenia indeksów

1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).


-- (3) wykonajmy proste wyszukiwanie po ID
-- oraz po nazwisku
SELECT * FROM Employees_kopia
WHERE EmployeeID = 5
GO

SELECT * FROM Employees_kopia
WHERE LastName = 'Dodsworth'
GO


Co będzie wynikiem wykonania powyższego kodu, wiesz już na pewno doskonale.

3. W programie Query Analyzer przełącz się z zakładki Editor na zakładkę Execution Plan. Zauważ, że dla obu zapytań przed chwilą wykonanych plan wykonania jest identyczny - wykonywane jest skanowanie tabeli (Table Scan), czyli przeszukanie wszystkich stron danych związanych z tabelą.



Rys. 5.3.1 Plan wykonania - skanowanie tabeli bez użycia indeksów


Aby z planu wykonania wyciągnąć więcej informacji, najeżdżaj kursorem myszy na poszczególne etapy planu wykonania. Pojawi się dość obszerny opis, którego objaśnienie możesz znaleźć w Books Online.


Możesz także wyświetlać plan wykonania w postaci tekstu wykonując przez wykonaniem kodu T-SQL następujący kod:

SET SHOWPLAN_TEXT ON
GO	
Po wykonaniu powyższego kodu wykonanie każdego zapytania nie zwróci wyników, ale wyświetlony zostanie plan wykonania w postaci tekstowej.


Krok 3 - Utwórz indeks grupowany w tabeli

1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (4).


-- (4) utworzmy indeks grupowany
CREATE CLUSTERED INDEX indeks_grupowany 
ON Employees_kopia(EmployeeID)
GO


Wynikiem wykonania powyższego kodu jest utworzenie indeksu grupowanego o nazwie indeks_grupowany na kolumnie EmployeeID w tabeli Employees_kopia.

2. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (2) (procedura sp_helpindex). Tym razem na liście pojawia się utworzony przed chwilą indeks grupowany.

Krok 4 - Wykonaj zapytania wyszukujące przy istniejącym indeksie grupowanym

1. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (3) (dwa zapytania SELECT).
2. W programie Query Analyzer przełącz się na zakładkę Execution Plan. Tym razem plan wykonania jest inny dla obu zapytań.

Dla zapytania, które używa w klauzuli WHERE kolumny EmployeeID plan wykonania wygląda jak na rysunku 5.3.2.



Rys. 5.3.2 Plan wykonania - przeszukiwanie indeksu grupowanego


Nastąpiło przeszukanie indeksu grupowanego (Clustered Index Seek) na kolumnie EmployeeID - czyli operacja jak najbardziej przez nas pożądana. Przeszukiwanie indeksu to operacja, która w sposób optymalny wykorzystuje indeks.

Dla zapytania, które używa w klauzuli WHERE kolumny LastName plan wykonania wygląda jak na rysunku 5.3.3.



Rys. 5.3.3 Plan wykonania - skanowanie indeksu grupowanego


Nastąpiło skanowanie indeksu grupowanego (Clustered Index Scan). Sytuacja ta jest odpowiednikiem skanowania tabeli w przypadku braku indeksu grupowanego. Nie powinno Cię jednak dziwić owo skanowanie, ponieważ zapytanie nie korzysta przy wyszukiwaniu z indeksowanej kolumny, zatem indeks nie może być przeszukany.

Zwróć uwagę na subtelną różnicę w graficznej reprezentacji przeszukania i skanowania indeksu (niebieska strzałka raz przechodzi przez rysunek indeksu - to oznacza skanowanie, a raz idzie w jedno z rozgałęzień indeksu - to oznacza przeszukanie indeksu).


Krok 5 - Utwórz indeks niegrupowany w tabeli

1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (5).


-- (5) utworzmy indeks niegrupowany
CREATE INDEX indeks_niegrupowany
ON Employees_kopia(LastName)
GO


Wynikiem wykonania powyższego kodu jest utworzenie indeksu grupowanego o nazwie indeks_niegrupowany na kolumnie LastName w tabeli Employees_kopia.

2. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (2) (procedura sp_helpindex). Tym razem na liście pojawia się utworzony przed chwilą indeks niegrupowany (oraz wcześniej utworzony indeks grupowany).

Krok 6 - Wykonaj zapytanie nie zawarte w indeksie niegrupowanym

1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (6).


-- (6) to zapytanie powinno korzystac z indeksu
SELECT * FROM Employees_kopia
WHERE LastName = 'Dodsworth'
GO


Komentarz do wyniku wykonania powyższego kodu jest zbędny. Natomiast zwróć uwagę na to, że nie indeks grupowany nie zawiera tego zapytania, tzn. w zapytaniu używamy innych kolumn, niż te wchodzące w skład indeksu (symbol *).

2. W programie Query Analyzer przełącz się na zakładkę Execution Plan. Plan wykonania dla tego zapytania jest nieco bardziej złożony (patrz rysunek 5.3.4).



Rys. 5.3.4 Plan wykonania - wykorzystanie dwóch indeksów (grupowanego i niegrupowanego)


Pierwszą operacją w tym planie wykonania jest przeszukanie indeksu niegrupowanego (ponieważ w klauzuli WHERE umieściliśmy właściwy warunek wyszukiwania). Nowością dla Ciebie jest drugi krok - czyli przekierowanie do zakładki (Bookmark Lookup) indeksu grupowanego (lub w przypadku jego braku bezpośrednio do tabeli) w celu uzyskania pozostałych kolumn (indeks niegrupowany nie zawiera wszystkich kolumn).

Gdy w tabeli istnieją dwa indeksy, grupowany i niegrupowany, to ostatni poziom (liście) indeksu niegrupowanego zawiera wskaźniki do korzenia indeksu grupowanego. Jeżeli zapytanie nie zawiera się w indeksie niegrupowanym i wykorzystuje w klauzuli WHERE kolumnę (kolumny) tego indeksu, to wykorzystywane są oba indeksy.


Krok 7 - Wykonaj zapytanie zawarte w indeksie niegrupowanym

1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (7).


-- (7) zapytanie zawarte w indeksie niegrupowanym
SELECT LastName FROM Employees_kopia
WHERE LastName LIKE '[A-F]%'
GO


Wyniku wykonania tego zapytania nie będziemy komentować, natomiast zauważ, że jest to zapytanie zawarte w indeksie niegrupowanym na kolumnie LastName (zapytanie nie korzysta z innych kolumn jako argumentów).

2. W programie Query Analyzer przełącz się na zakładkę Execution Plan. Plan wykonania tego zapytania przedstawiono na rysunku 5.3.5.



Rys. 5.3.5 Plan wykonania - przeszukanie indeksu niegrupowanego


Ponieważ indeks niegrupowany zawiera wykonane zapytanie, następiło przeszukanie tego indeksu - czyli operacja najszybsza z możliwych.

Tworzenie indeksów zawierających zapytania jest oczywiście sprawą wyboru najbardziej potrzebnych (najczęściej wykonywanych i trwających najdłużej) z punktu widzenia aplikacji bazodanowej zapytań. Jest to jednak kolejny dowód na to, by nie stosować symbolu * w zapytaniach SELECT (bo wówczas zwiększa się liczba kolumn, na których musisz utworzyć indeks, by zawierał on zapytanie).

Pamiętaj też, że rozwiązaniem nie jest utworzenie indeksu zawierającego wszystkie kolumny tabeli (choć wydaje się, że taki indeks zawiera wszystkie zapytania...). W takim przypadku utworzysz po prostu kopię tabeli i zamiast zyskać na wydajności, spowodujesz jej spadek.


Krok 8 - Wykonaj zapytanie nie przeszukujące indeksów

1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (8).


-- (8) przyklad zapytania nie dla indeksow
SELECT LastName FROM Employees_kopia
WHERE LastName LIKE '%r'
GO


Wynikiem wykonania tego kodu jest wyświetlenie wszystkich nazwisk kończących się na literę R.

2. W programie Query Analyzer przełącz się na zakładkę Execution Plan. Plan wykonania tego zapytania przedstawia rysunek 5.3.6.



Rys. 5.3.6 Plan wykonania - zapytanie nie wykorzystujące indeksów


Mimo, iż wydaje się, że zapytanie jest zawarte w indeksie niegrupowanym, okazuje się, że indeks ten nie jest w ogóle wykorzystany! Następuje tylko skanowanie indeksu grupowanego (Clustered Index Scan) - czyli właściwie skanowanie tabeli (tyle, że jej posortowanej wersji). Dlaczego? Dzieje się tak dlatego, że indeks musi mieć wzorzec do wykonania przyrównania typu "większe-równe" z wartościami w indeksie. Oznacza to, że w przypadku łańcuchów tekstowych indeks musi znać początek łańcucha (tu jest on dowolny).

Zapamiętaj, że użycie niektórych operatorów może spowodować sytuację jak powyżej.

Dopuszczalne operatory dla argumentów SARG (ang. Search ARGuments) to: =, <, >, <=, >=, BETWEEN oraz LIKE (w przypadku, gdy wzorzec nie zaczyna się od "dzikiej karty" - %, ang. wildcard). W przypadku użycia tylko tych operatorów w klauzuli WHERE umożliwiamy serwerowi wykorzystanie indeksów.

W przypadku pozostałych operatorów niezbędne jest przeszukanie całej tabeli. W miarę możliwości należy więc zastępować te operatory wymienionymi powyżej.


Ponadto okazuje się, że jeśli dokonujemy obliczeń matematycznych wewnątrz klauzuli WHERE, koniecznie należy dążyć do wyizolowania kolumny będącej argumentem wyszukiwania. Porównaj poniższe zapytania:

SELECT * FROM [Order Details]
WHERE UnitPrice > 2 * 50
GO

SELECT * FROM [Order Details]
WHERE UnitPrice / 2 > 50
GO
Niby wynikiem wykonania jest to samo, ale... Okazuje się, że lepsze jest pierwsze zapytanie, ponieważ serwer najpierw obliczy wartość po prawej stronie nierówności (2*50=100) i dokona przeszukania ewentualnego indeksu niegrupowanego na kolumnie UnitPrice. Natomiast w drugim zapytaniu serwer pobierze z tabeli z każdego wiersza wartość pola z kolumny UnitPrice - czyli dokona tak naprawdę skanowania tabeli...


Krok 9 - Obejrzyj statystyki indeksu

1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (9).


-- (9) wyswietlmy statystyki indeksu
DBCC SHOW_STATISTICS (Employees_kopia, indeks_niegrupowany)
GO


Wynikiem wykonania powyższego kodu jest wyświetlenie statystyk indeksu o nazwie indeks_niegrupowany utworzononego w tabeli Employees_kopia.



Rys. 5.3.7 Query Analyzer - statystyki indeksu


Statystyki odpowiadają na pytanie - jaka jest selektywność (ang. selectivity) rekordów. Pod pojęciem selektywność rozumiemy relację między liczbą wierszy zwracanych w zapytaniu, a całkowitą liczbą wierszy w tabeli. Im mniejszy jest iloraz [liczba wyszukanych wierszy]/[liczba całkowita wierszy], tym lepsza selektywność. Na podstawie selektywności serwer może decydować o wykorzystaniu indeksu. W naszym przykładzie można łatwo stwierdzić, że wiersze są unikalne pod względem zarówno wartości zapisanych w kolumnie LastName (nazwiska się nie powtarzają), jak i kombinacji kolumn LastName i EmployeeID. Co ciekawe tę unikalność możemy też wyczytać ze statystyk. Spójrz do statystyk i zauważ kolumnę All density (oznacza to, jaki średnio procent wszystkich wierszy stanowi pojedyncza wartość w kolumnie - tu 0.1(1), czyli 1/9 - co się zgadza, jako że mamy 9 wierszy i każdy jest inny, każdy stanowi 1/9 ogółu). Teraz mnożąc tę wartość przez całkowitą ilość wierszy uzyskamy średnią "powtarzalność" wierszy (tu 9 razy 1/9 daje 1, co oznacza, że każda wartość kolumny występuje dokładnie raz).

Selektywność jest bardzo ważną wielkością w przypadku stosowania indeksów. Pamiętaj, że im większa selektywność zapytania, tym większy wzrost wydajności możesz zanotować w przypadku użycia indeksów. Zajrzyj do lekcji poprzedniej i sprawdź selektywność zapytania tam wykorzystanego (3 wiersze z ponad 2000). To właśnie jest dobra selektywność.

Z selektywnością wiąże się stosowanie odpowiednich operatorów logicznych. Zauważ, że operator AND użyty w klauzuli WHERE ogranicza dodatkowo liczbę zwracanych rekordów (wszystkie warunki połączone tym operatorem muszą być spełnione jednocześnie). Natomiast użycie operatora OR może spowodować zmniejszenie selektywności zapytania (dowolny z warunków połączonych tym operatorem musi być spełniony).


Do dokumentacji projektu Prace dyplomowe dołącz rysunki lub wydruki ilustrujące plan wykonania kilku zapytań wyszukujących dane i korzystających z indeksów. Opisz te plany wykonania pod względem wykorzystania indeksów.


Przejdź dalej



Ćwiczenia > Indeksy w MS SQL Server 2000 > Wykorzystanie indeksów