W rozdziale 12. zostały omówione rozszerzenia programowe SQL Servera i Transact-SQL, jak również transakcje. Transakcje pozwalają na logiczne grupowanie modyfikacji dokonywanych w bazie danych. Wymagane jest, aby te logiczne grupy były w całości zatwierdzone lub w całości wycofane jako pojedyncze wykonywane jednostki. Zostało również omówione blokowanie wykorzystane w ochronie relacyjnej bazy danych. Blokowanie pozwala stworzyć wrażenie, że baza danych wygląda jak system pojedynczego użytkownika.
Niniejszy rozdział omawia indeksy. Indeksy dostarczają zbioru logicznych wskaźników do danych, podobnie jak indeksy w książkach, które pomagają w znalezieniu potrzebnych wyrażeń. Chociaż wszystkie omówione wcześniej wyrażenia (SELECT, INSERT, UPDATE, DELETE) działały bez indeksów, na ogół działają szybciej z indeksami.
Niniejszy rozdział rozpoczyna się omówieniem przydatności indeksów, a następnie przedstawieniem podstaw działania indeksów o strukturze drzewa B+ (typ indeksu implementowany przez SQL Server). Następnie zostanie omówiona składnia polecenia CREATE INDEX. Przedstawionych zostanie kilka opcji i problemów związanych z wydajnością w kontekście indeksów. Kolejnym zagadnieniem będą indeksy na widokach i sytuacje, w których indeksy są bardzo potrzebne. Następnie podane zostaną niektóre z poleceń DBCC, używane z indeksami, jak również inne zagadnienia związane z pielęgnacją indeksów. Ostatnim zagadnieniem będzie przegląd możliwości indeksowania pełnotekstowego SQL Servera 2000.
Można wprowadzać indeksy z wielu przyczyn, ale najbardziej zwyczajna przyczyna była już wymieniana — szybkość. Bez stosowania indeksów SQL Server korzysta z danych czytając każdą stronę danych w każdej z tablicy, które zostały określone w poleceniu SQL. Takie skanowanie tablicy (czytanie każdej strony danych), może być świetną metodą pobierania danych. Przykładowo, jeżeli tablica jest mała lub gdy sięga się do dużej części tablicy, skanowanie tablicy może być najlepszym sposobem dostępu do danych. Jednak, bardzo często dostęp do danych jest znacznie szybszy z indeksowaniem. Indeksowanie może również przyspieszyć złączenia tablic.
Inną przyczyną tworzenia indeksu jest wymuszenie unikalności. Dwa identyczne wiersze w tablicy nie warunkują błędu. Jednak, nie jest to metoda przechowywania danych, którą chce wykorzystywać większość ludzi. Można sobie wyobrazić system śledzący klientów. Jeżeli nie można jednoznacznie określić klientów, można mieć problemy z ich utrzymaniem, gdy rachunki dla nich będą nieprawidłowe. Istnieje kilka opcji do unikalnej identyfikacji klientów. Można nadać im numery, używać łącznie ich imion i dat urodzenia, używać numerów ich kart kredytowych lub używać innych wartości lub zbiorów wartości. Bez względu na wybór, sposobem poinformowania SQL Server o wyborze jest zastosowanie unikalnego indeksu. W rozdziale 14 zostanie omówiony inny sposób wymuszenia unikalności — więzy unikalne — ale nawet wtedy SQL Server będzie nadal tworzyć unikalny indeks jako narzucony mechanizm.
Na ogół indeks składa się ze zbioru stron zwanego drzewem B+. Drzewo B+ wygląda podobnie jak na pokazano na rysunku 13.1
Rysunek
Brak rysunku
13.1. Drzewo B+.
Jak zostało wcześniej powiedziane, indeks pomaga w szybkim znalezieniu danych. Aby znaleźć odpowiedni wiersz danych, należy przeszukiwać B+ drzewo aż do znalezienia wiersza a następnie przesuwać się w strukturze drzewa do indywidualnego wiersza danych. Rozpoczyna się od strony głównej. Wskaźnik do strony głównej jest umieszczony w tablicy systemowej sysindexes (w kolumnie zwanej root dla indeksów niezgrupowanych). Strona główna zawiera wpisy indeksu (dane z indeksowanej kolumny lub kolumn), oraz wskaźniki do każdej strony poniżej strony głównej. Każdy indeks może mieć jeden lub więcej poziomów pośrednich. Każdy wpis posiada wartość indeksu i wskaźnik do następnej strony poniżej.
Na stronach liścia (najniższy poziom w drzewie), to co się tam znajduje zależy od tego, czy tablica ma indeks zgrupowany. Logicznie mówiąc, można znaleźć wpis dla każdego wiersza w tablicy, który został indeksowany, jak również wskaźnik do strony danych i numeru wiersza, który ma aktualny wiersz danych. Jeżeli tablica ma również indeks zgrupowany, wszelkie niezgrupowane indeksy zawierają raczej wartości klucza z indeksu zgrupowanego niż informacje o stronie danych i numerze wiersza. Indeksy zgrupowane i niezgrupowane zostaną wytłumaczone później, ale teraz należy pomyśleć o indeksach zgrupowanych jako o sposobie do wcześniejszego posortowania bieżących danych. Tablice bez indeksów zgrupowanych nazywane są stosem (heaps), a indeksy niezgrupowane są osobnymi strukturami indeksów, które nie sortują bezpośrednio danych.
Dane są przechowywane w stronach zwanych stronami danych. Każda strona ma rozmiar 8,192 bajtów z nagłówkiem o wielkości 96 bajtów. Czyli, każda strona ma 8,096 bajtów dostępnych na przechowywanie danych. Każda strona na taką samą strukturę podstawową.
Rysunek 13.2 pokazuje przykład jak może wyglądać indeks. Ten indeks jest założony na kolumnę imię (first_name).
13.2. Przykładowy indeks B+.
Każdy poziom indeksu jest listą podwójnie łączoną. Każda strona ma informacje o stronie, która jest logicznie przed nią i po niej. Na poziomie głównym i poziomach pośrednich indeksu, każda wartość indeksu jest pierwszą wartością na stronie, na kolejnym niższym poziomie. Poziom liścia indeksu zawiera jeden wpis dla każdego wiersza w tablicy. Warto zauważyć, że indeks jest sortowany w oparciu o kolumnę (lub kolumny), która została wybrana jako klucz indeksu. Kolejność ta nie zmienia fizycznej kolejności sortowania danych.
Kiedy dane w tablicy są modyfikowane, modyfikowany jest również każdy indeks w tablicy. SQL Server gwarantuje spójność pomiędzy danymi w tablicach a ich indeksami. Jest to dobre, gdy chce się mieć doskonałą integralność danych. Jednak, oznacza to również, że operacje INSERT, UPDATE i DELETE, które powinny być szybkie, mogą mieć trochę więcej pracy i mogą działać nieznacznie wolniej. Dodawanie nowego wiersza przy pomocy polecenia INSERT, zajmuje normalnie dwie operacje wejścia/wyjścia (I/O) — jedną dla danych, jedną dla dziennika. Jeżeli w danej tablicy są dwa indeksy, dodawanie wiersza wymaga co najmniej dwóch kolejnych I/O, a być może więcej. Należy odpowiednio wyważyć swoje potrzeby związane z modyfikacją danych a realizacją szybszych zapytań.
Jak korzystać z indeksu? Na rysunku 13.2. należy znaleźć wiersz danych. Przyjmijmy, że indeks jest założony na kolumnę imię (first_name). Aby znaleźć wiersz z imieniem Donald, rozpoczyna się od strony głównej (korzeń drzewa). Ponieważ Donald jest „niżej” niż John, należy przejść do wpisu Andy na stronie 31. Na stronie 31 okazuje się, że Donald jest „wyżej” niż David ale „niżej” niż Hank, czyli następuje przejście do strony 22. Na stronie 22, można przeczytać, że wpis dla imienia Donald wskazuje na stronę 1 (która jest stroną danych ponieważ jest to najniższy poziom indeksu —liść). W SQL Serverze, można również znaleźć numer wiersza, który zawiera imię Donald, ale nie został przedstawiony na rysunku dla zachowania przejrzystości. Teraz pozostaje przeczytanie informacji na temat Donalda z odpowiedniego wiersza ze strony 1. SQL Server używa indeksów do wyszukiwania danych dokładnie w ten sam sposób.
Dla indeksów dostępnych jest kilka opcji. Zanim utworzy się własny indeks należy zrozumieć poszczególne możliwości aby dokonać wyboru odpowiedniego indeksu.
Istnieją dwie opcje fizycznego przechowywania indeksów. Pierwszy typ jest znany jako zgrupowany. Indeks zgrupowany fizycznie sortuje dane. Inaczej niż w przypadku posiadania całkowicie odrębnej struktury (takiej jak opisana wcześniej), poziom liścia indeksu w tym przypadku to dane. Dostęp do danych przy pomocy indeksu zgrupowanego jest prawie zawsze szybszy niż używanie indeksu niezgrupowanego, ponieważ dodatkowe przeglądanie strony danych/wiersza z poziomu liścia indeksu nie jest potrzebne.
Ponieważ dane są fizycznie sortowane w kolejności klucza indeksu, można mieć tylko jeden indeks zgrupowany w tablicy (nie ma sensu trzymać wielu kopii danych). Ponieważ dostępny jest jedynie jeden indeks zgrupowany, należy go wybrać szczególnie starannie. Wybór takiego indeksu może być dość złożony, ale niektóre główne wskazówki zostaną tutaj przedstawione.
Z indeksami zgrupowanymi związana jest jedna zasadnicza kwestia — wolna przestrzeń. Utworzenie indeksu zgrupowanego wymaga, aby było dostępne przynajmniej 120% rozmiaru tablicy jako chwilowy obszar roboczy. Musi istnieć wolna przestrzeń w bazie danych, w której jest tworzony indeks. Aby utworzyć indeks, SQL Server kopiuje tablicę, sortuje kopię w kolejności wartości indeksu (w kolejności rosnącej), buduje struktury indeksu (stronę główna i wszelkie potrzebne strony pośrednie), a następnie usuwa oryginalną tablicę. Po zakończeniu operacji, indeks zgrupowany zajmuje jedynie około 5% więcej przestrzeni niż tablica. Narzut (strony nie zawierające danych) w indeksie tego typu jest relatywnie mały, ale zależy od rozmiaru wartości, które są indeksowane.
Narzut ten może nie wygląda niepokojąco ponieważ wolna przestrzeń jest potrzebna jedynie podczas tworzenia indeksu, ale jej wielkość może być trudna do oszacowania. W przypadku bazy danych o wielkości 500 MB, gdy jedna tablica ma 100MB i będzie potrzeba stworzenia indeksu zgrupowanego dla tej tablicy, będzie potrzeba co najmniej 120MB nieużywanej przestrzeni w bazie danych.
Inną, równie ważną sprawą w SQL Serverze 2000 jest to, że wartości klucza (kolumna indeksowana), które zostały wybrane do indeksu zgrupowanego, są „przenoszone” do niezgrupowanych indeksów. Dlatego, jeżeli zostanie wybrany duży indeks zgrupowany — przykładowo, Char(30) — nie tylko więcej czasu zajmuje przeszukanie tego indeksu ale wszystkie inne indeksy niezgrupowane muszą również posiadać wartość Char(30) klucza tego indeksu zgrupowanego w każdym z wierszy w indeksach niezgrupowanych. Jest to znaczący narzut, dlatego należy utrzymywać klucze indeksu zgrupowanego tak małe, jak to możliwe. Należy się również upewnić, czy klucz zgrupowany, który został wybrany, nie jest często uaktualniany, ponieważ wszystkie indeksy niezgrupowane muszą być uaktualnione gdy zmienią się wartości indeksu zgrupowanego.
Inną sprawą, której należy być świadomym, to kolejność w jakiej tworzy się indeksy. Ponieważ klucz indeksu zgrupowanego jest częścią kluczowych wartości dla każdego indeksu niezgrupowanego, każdy z tych indeksów musi zostać odbudowany. Dlatego należy zawsze tworzyć najpierw indeksy zgrupowane.
Rysunek 13.3 pokazuje przykład indeksu zgrupowanego. Dane są sortowane w kolejności klucza indeksu (ponownie jest to imię), a dane na najniższym poziomie indeksu (strona danych) są sortowane wg imienia.
13.3. Przykład indeksu zgrupowanego.
Indeks niezgrupowany jest ogólnie taki sam jak standardowy indeks B+ drzewa. Każdy z indeksów ma stronę główną, jedną lub więcej stron poziomów pośrednich oraz poziom liścia, zawierający jeden wiersz dla jednego wiersza z tablicy. Indeksy niezgrupowane wymagają w sumie więcej przestrzeni niż indeksy zgrupowane, ale zajmują znacznie mniej przestrzeni podczas procesu ich tworzenia.
Na pojedynczej tablicy można mieć do 249 indeksów niezgrupowanych. Kolejność, w jakiej są tworzone, nie jest istotna. Indeks niezgrupowany nie zmienia kolejności danych, tak jak to robi indeks zgrupowany. Wiersze na poziomie liścia indeksu są posortowane w kolejności kolumn wybranych jako część indeksu. Każdy wiersz zawiera wskaźnik do kombinacji, numer strony/numer wiersza danych w tablicy (jeżeli nie istnieje indeks zgrupowany) lub wartość klucza indeksu zgrupowanego, (jeżeli tablica posiada taki indeks). Przykład indeksu niezgrupowanego został przedstawiony na rysunku 13.2.
Unikalność determinuje, czy wartości powtarzające się są dozwolone w indeksie. Przykładowo, we wcześniej prezentowanym indeksie dotyczącym imion, żadne dwie osoby nie mogły by mieć takich samych imion jeśli indeks byłby unikalny. Indeksy SQL Servera domyślnie nie są unikalne, co oznacza, że dozwolone jest duplikowanie wartości.
Jeżeli pozwalają na to dane, utworzenie unikalnego indeksu może znacznie poprawić wydajności podczas używania indeksu. Kiedy szukana wartość zostaje znaleziona, nie ma potrzeby wykonywania kolejnych wyszukiwań (ponieważ wartości się nie powtarzają).
Indeksy zgrupowane są szczególnie dobrymi kandydatami na unikalne indeksy ponieważ, SQL Server zawsze wewnętrznie wymusza unikalność indeksów zgrupowanych. Jeżeli nie zostanie utworzony unikalny indeks zgrupowany, SQL Server generuje dodatkową ukrytą wartość klucza, aby wymusić unikalność indeksu. Dlatego nie warto, aby SQL Server generował dodatkowy klucz skoro można skorzystać z unikalnego klucza tego indeksu.
Wiele indeksów obejmuje tylko jedną kolumnę, jednak można łatwo utworzyć indeks na wielu kolumnach. Indeksy wielokolumnowe mogą być całkiem pożyteczne, ponieważ pozwalają zredukować ilość indeksów używanych przez SQL Server i zapewnić lepszą wydajność. Jeżeli jakieś kolumny występują często razem w zapytaniach, są one doskonałymi kandydatami do indeksu złożonego (inna nazwa indeksu wielokolumnowego). Indeksy złożone mogą być zgrupowane lub niezgrupowane oraz zawierać od 2 do 16 kolumn i mogą mieć wielkość do 900 bajtów.
Problemem tutaj może być to, że jeżeli utworzy się zbyt duży indeks, nie będzie on użyteczny, ponieważ może się okazać, że przejrzenie bezpośrednio tablicy zajmuje mniej czasu niż używanie indeksu. Indeksowanie dotyczy niestety wiele zagadnień i rzadko prezentuje jeden oczywisty wybór dla wielu aplikacji.
We wszystkich wcześniejszych wersjach SQL Servera, indeksy były zawsze przyjmowane jako rosnące (naturalny sposób myślenia o indeksach). Czyli, sortując alfabet, A będzie na początku, a Z na końcu. Jednak, czasem może być potrzebna lista posortowana w malejącej kolejności (od Z do A). SQL Server 2000 w pełni obsługuje indeksy malejące.
Rysunek 13.4 pokazuje indeks niezgrupowany dotyczący imienia (ten sam indeks jak na rysunku 13.2), ale indeks jest teraz przechowywany w porządku malejącym. Wiersz można znaleźć dokładnie w ten sam sposób, zaczynając nawigację od strony głównej. Zaletą tej metody jest to, że jeżeli zapytanie ma zwrócić dane w kolejności malejącej, mogą być one zwrócone bez dodatkowego sortowania.
13.4. Indeks niezgrupowany, malejący.
SQL Server do budowy indeksów implementuje drzewa B+. Aby utworzyć indeks należy zastosować polecenie CREATE INDEX:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [owner.][table_name | view_name]
(column_name [ASC|DESC] [,...n]
[WITH
[PAD_INDEX][[,] FILLFACTOR = x]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
[[,] SORT_IN_TEMPDB]]
[ON filegroup]
Znaczenie składni:
· UNIQUE określa, że dla tego indeksu nie są dozwolone wartości duplikowane. Unikalność nie jest domyślnie wybraną wartością. Są dozwolone powtarzające się wpisy indeksu.
· CLUSTERED określa, ze dane będą fizycznie sortowane i staną się najniższym poziomem indeksu (liść). Wartości indeksu zgrupowanego, muszą być unikalne. W przypadku tworzenia indeksu zgrupowanego UNIQUE nie ma żadnego problemu. Jednak, jeżeli jest tworzony nie unikalny indeks zgrupowany, do każdego klucza tego indeksu jest dodawany 4-bajtowy „unifikator”, gwarantujący unikalność.
· NONCLUSTERED określa, że będzie tworzony normalny indeks B+, jako zupełnie osobny obiekt. Jest to domyślny typ indeksu.
· index_name jest unikalną nazwą SQL Servera dla tego obiektu.
· table_name jest nazwą tablicy, przechowującej kolumny, które mają być indeksowane.
· view_name jest nazwą widoku zawierającego kolumny do indeksowania.
· column_name jest nazwą kolumny (lub kolumn), która ma być indeksowana. Można utworzyć indeks dla 16 kolumn o rozmiarze do 900 bajtów. Kolumny nie mogą być typu text, image, bit lub ntext. Nową rzeczą w SQL Server 2000 jest to, że kolumna może być kolumną wyliczeniową.
· ASC|DSC oznacza, czy kolumna jest posortowana rosnąco (ASC) lub w kolejności malejącej (DESC) — od Z do A. Wartością domyślną jest sortowanie rosnąco, co jest spójne z wcześniejszymi wersjami, które nie miały tej opcji.
· filegroup jest nazwą grupy plików na który indeks powinien być założony. Jeżeli parametr filegroup nie został określony, indeks jest tworzony na domyślnej grupie plików.
Przykładowo, następujący kod tworzy tablicę zwaną myauthors w bazie danych pubs, a następnie kopiuje wszystkie dane z tablicy authors. Następnie kod tworzy indeks na kolumnie au_id tablicy myauthors. Powstały indeks zgrupowany wymusza unikalność.
USE PUBS
-- CREATE THE TABLE
CREATE TABLE dbo.myauthors (
au_id id NOT NULL,
au_lname varchar (40) NOT NULL ,
au_fname varchar (20) NOT NULL ,
phone char (12) NOT NULL ,
address varchar (40) NULL ,
city varchar (20) NULL ,
state char (2) NULL ,
zip char (5) NULL ,
contract bit NOT NULL)
-- Copy the data from the authors table into myauthors
INSERT myauthors select * from authors
-- create the unique clustered index on the myauthors table
Create unique clustered index myauind on myauthors (au_id)
Następujący kod tworzy nie unikalny indeks niezgrupowany, malejący na kolumnie au_fname tej samej tablicy:
Use pubs
Create index mynamindex on myauthors (au_fname DESC)
Warto zauważyć, że to polecenie Transact-SQL jest identyczne jak poprzednie w zakresie wykonywanych działań i może być bardziej oczywiste w formie:
Create nonclustered index mynameindex on myauthors (au_fname)
Opcja fillfactor określa, na ile powinna być wypełniona każda strona na poziomie liścia indeksu. Domyślnie stopień wypełnienia jest ustawiony na 0. Ponieważ fillfactor jest parametrem konfiguracyjnym, należy upewnić się, że nie będzie on zmieniany. Można sprawdzić to z SQL Server Enterprise Managera lub przy pomocy procedury sp_configure bez żadnych parametrów. Wynik powinien wyglądać podobnie do przedstawionego:
EXEC sp_configure
GO
name minimum maximum config_value run_value
------------------------ ------- ------- ------------ ---------
allow updates 0 1 0 0
default language 0 9999 0 0
fill factor (%) 0 100 0 0
language in cache 3 100 3 3
max async IO 1 255 32 32
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
nested triggers 0 1 1 1
network packet size (B) 4096 65536 4096 4096
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote proc trans 0 1 0 0
show advanced options 0 1 0 0
user options 0 4095 0 0
(14 row(s) affected) ...
Należy znaleźć w zbiorze wyników nazwę fill factor i sprawdzić czy wartości kolumny config_value i run_value dla tego parametru ustawione są na 0.
Jeżeli w poleceniu CREATE TABLE nie zostanie określony parametr fillfactor, używana jest domyślna wartość (na ogół 0). Wartość 0 oznacza, ze strony na „najniższym” poziomie indeksu są prawie pełne ale strony innych poziomów (poziomy pośrednie i poziom główny) nadal mają miejsce na co najmniej dwa wiersze. Jeżeli wartość fillfactor wynosi 100, wszystkie strony liśca są zapełnione w 100 procentach, bez miejsca na dodatkowe wiersze. Z kolei, strony główna i pośrednie ciągle posiadają miejsce na dwa dodatkowe wiersze. Każda inna wartość oznacza procentowy stopień zapełnienia wierszami każdej ze stron liścia. SQL Server zaokrągla procent do rozmiaru najbliższego wiersza, czyli rzadko można uzyskać faktyczny stan procentowy, ale wynik jest najlepszy, jaki SQL Server może podać.
Jeżeli zostanie utworzony indeks zgrupowany o wartości fillfactor wynoszącej 50, każda strona będzie w 50 procentach zapełniona. Na rysunku 13.5 widać, że strona liścia indeksu zgrupowanego jest zapełniona jedynie w połowie. Przykładowy kod wygląda następująco:...
kuma85