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

5.2 Tworzenie indeksów



Kiedy już wiemy, jak będziemy indeksować tabele, możemy przystąpić do tworzenia i testowania indeksów. Testować indeksy należy przed ich wdrożeniem do bazy produkcyjnej (należy potwierdzić ich przydatność i wykazać, że rzeczywiście wpływają pozytywnie na wydajność wyszukiwania).

Do zilustrowania procesu tworzenia i działania indeksu wykorzystamy bazę danych Northwind, która jest jedną z baz utworzonych automatycznie po instalacji MS SQL Server.

Krok 1 - Przygotuj tabelę z danymi do utworzenia przykładowego indeksu

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_2_1.sql.
5. Zaznacz i uruchom (klawisz F5) fragmenty kodu oznaczone w komentarzach jako (1) oraz (2).


-- (1) ustawmy Northwind jako baze robocza 
USE Northwind
GO

-- (2) skopiujmy tabele Order Details
SELECT * INTO od FROM [Order Details]
GO


Wynikiem działania powyższego kodu jest ustawienie bazy danych Northwind jako bazy roboczej oraz skopiowanie struktury i zawartości tabeli Order Details do nowej tabeli o nazwie od.

W nowo utworzonej tabeli znajduje się aktualnie ponad 2000 wierszy.

6. W programie Query Analyzer w menu głównym wybierz Tools - Customize....
7. W oknie Customize w pozycji odpowiadającej kombinacji klawiszy Ctrl+F1 wpisz sp_helpindex i kliknij przycisk OK. Dzięki temu będziesz mógł w prosty sposób wywoływać z klawiatury procedurę składowaną sp_helpindex, która wyświetla informacje o indeksach w wybranej tabeli.



Rys. 5.2.1 Query Analyzer - konfigurowanie skrótów klawiaturowych


Opanowanie skrótów klawiaturowych programu Query Analyzer oraz utworzenie własnych skrótów jest gwarancją naprawdę wydajnej pracy.


8. Podświetl w edytorze słowo od (nazwę nowej tabeli) i wciśnij Ctrl+F1. Powinieneś zobaczyć informację:

The object does not have any indexes.

9. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (3). Powinieneś uzyskać informację jak powyżej.


-- (3) zobaczmy liste indeksow w tabeli
EXEC sp_helpindex od
GO


Procedura systemowa sp_helpindex wyświetla informacje na temat indeksów w wybranej tabeli lub widoku.

10. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (4).


-- (4) wlaczmy pomiar ilosci skanowanych stron
SET statistics io on
GO


Wynikiem wykonania powyższego kodu jest włączenie wyświetlania statystku wejścia/wyjścia (skanowania stron danych) przy wykonywaniu poleceń T-SQL.

Dość często przydatne są także statystyki dotyczące czasu wykonania (w milisekundach). Aby włączyć tą opcję należy wykonać kod:

SET statistics time on
GO
Wyłączenie wyświetlania statystyk uzyskuje się przez uruchomienie kodu identycznego jak ten, który służy do uruchomienia - zamieniamy jedynie słowo on na off.

W naszym przypadku ilość danych nie jest zbyt duża, więc nie zaobserwujemy różnicy czasowej wykoywania zapytań. Dlatego używamy jedynie statystyk dotyczących liczby skanowanych stron. Przy większej ilości danych i w bardziej złożonych przypadkach należy do testowania indeksów używać także statystyk czasowych.

Opcje statystyk możesz ustawić także w menu głównym programu Query Analyzer wybierając Query - Current Connection Properties....


11. Zaznacz i uruchom (F5) fragment kodu oznaczony w komentarzu jako (5).


-- (5) zapytanie dla indeksu
SELECT * 
FROM od
WHERE UnitPrice > 100 AND Quantity < 5
GO


Wynikiem wykonania powyższego kodu jest wyświetlenie z tabeli od wszyskich wierszy, w których cena jednostki (ang. unitprice) produktu jest większa niż 100, zaś zamówiona ilość (ang. quantity) jest mniejsza niż 5.

OrderID     ProductID   UnitPrice    Quantity Discount                 
----------- ----------- ------------ -------- ---------
10541       38          263.5000     4        0.1
10828       38          263.5000     2        0.0
11006       29          123.7900     2        0.25

(3 row(s) affected)

Table 'od'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.
Zwróć uwagę na kilka szczegółów. Po pierwsze - w wyniku zapytania zwrócono 3 wiersze. W tabeli jest ich ponad 2000. Taka dysproporcja jest istotna. Dlaczego? Dowiesz się z następnej lekcji. Po drugie - zapamiętaj liczbę przeskanowanych stron (na czerwono). Porównasz ją z liczbą stron skanowanych po utworzeniu indeksu.

Krok 2 - Utwórz indeks

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


-- (6) utworzmy indeks na dwoch kolumnach
CREATE INDEX indeks_od ON od(UnitPrice, Quantity)
GO


Wynikiem wykonania powyższego kodu jest utworzenie w tabeli od indeksu niegrupowanego (jeśli po słowie CREATE nie ma słowa określającego typ indeksu, czyli CLUSTERED lub NONCLUSTERED, to indeks jest niegrupowany) na kolumnach UnitPrice oraz Quantity.

2. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (3) lub zaznacz w edytorze słowo od i wciśnij klawisze Ctrl+F1 (procedura sp_helpindex). Tym razem zobaczysz dane dotyczące świeżo utworzonego indeksu: jego nazwę, rodzaj oraz spis kolumn, na których został utworzony. Zauważ, że nasz indeks nie zawiera zapytania (tzn. nie jest utworzony na wszystkich kolumnach użytych w zapytaniu SELECT, jako że użyliśmy symbolu - używamy więc wszystkich kolumn tabeli).

Krok 3 - Przetestuj przydatność indeksu

1. Zaznacz i uruchom (F5) ponownie fragment kodu oznaczony w komentarzu jako (4) (polecenie SELECT).

Tym razem wynik będzie oczywiście taki sam, ale pojawi się różnica w statystykach:

OrderID     ProductID   UnitPrice    Quantity Discount                 
----------- ----------- ------------ -------- ---------
10541       38          263.5000     4        0.1
10828       38          263.5000     2        0.0
11006       29          123.7900     2        0.25

(3 row(s) affected)

Table 'od'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
Ilość skanowanych stron zmalała o połowę, mimo iż nie utworzyliśmy indeksu, który teoretycznie byłby optymalny dla tego zapytania.

Pamiętaj, że w bazach produkcyjnych nie należy uzywać zapytań SELECT z symbolem *. Użycie takich zapytań może mieć poważny (negatywny) wpływ na wydajność.


Oczywiście MS SQL Server posiada wiele narzędzi, które umożliwiają zarządzanie indeksami w bazach danych, np.:
- w programie Enterprise Manager w menu głównym Tools - Wizards... masz do dyspozycji Create Index Wizard - kreator do tworzenia indeksów krok po kroku,
- w programie Enterprise Manager po kliknięciu prawym przyciskiem myszy na tabeli wybierz Wszystkie zadania (lub All Tasks) - Manage Indexes..., a pokaże się okno zarządzania indeksami,
- w programie Query Analyzer w menu głównym Query - Index Tuning Wizard masz do dyspozycji kreator, którego zadaniem jest wybór optymalnej kombinacji indeksów (oraz propozycje zmian w istniejącej strukturze); kreator ten jest dość często używany w kombinacji z wynikami uzyskanymi przy użyciu programu Profiler (więcej możesz poczytać w Books Online oraz w literaturze).

Pamiętaj jednak, że tylko Ty wiesz, jakie zapytania będą używane w bazie danych. Nie zawsze więc zaproponowane przez SQL Server rozwiązania będą dla Twojej bazy danych odpowiednie.


Optymalnie byłoby, gdybyś dla projektu Prace dyplomowe przygotował jakiś przykład ilustrujący słuszność obranej strategii indeksowania (tzn. najpierw pomiary bez indeksu, potem z indeksem i porównanie). Zanim przystąpisz do realizacji tych testów koniecznie zajrzyj do lekcji następnej.


Przejdź dalej



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