Ć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).
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ów1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3).
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
Krok 3 - Utwórz indeks grupowany w tabeli1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (4).
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 grupowanym1. 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.
Krok 5 - Utwórz indeks niegrupowany w tabeli1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (5).
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 niegrupowanym1. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (6).
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).
Krok 7 - Wykonaj zapytanie zawarte w indeksie niegrupowanym1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (7).
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.
Krok 8 - Wykonaj zapytanie nie przeszukujące indeksów1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (8).
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).
Krok 9 - Obejrzyj statystyki indeksu1. W programie Query Analyzer przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (9).
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).
Przejdź dalej ![]() |
|||||||||||||||||||||||||||||||
![]() Ćwiczenia > Indeksy w MS SQL Server 2000 > Wykorzystanie indeksów |