• Artykuł

          

Encyklopedia SQL - Indeksowanie tabel, indeks klastrowy i nieklastrowy  

Udostępnij na: Facebook

Autor: Paweł Wilkosz

Opublikowano: 2012-02-07

Kontynuując cykl publikacji Encyklopedia SQL chciałbym zaprezentować jedno z ważniejszych zagadnień w procesie optymalizacji oraz dostrajania bazy danych w zakresie szybkości wykonywania operacji – indeksowanie.

Przed wykonaniem zadań zapoznaj się z następującym materiałem:

Po wykonaniu zadań będziesz wiedział:

  • co to są indeksy i jaką rolę pełnią w bazie danych,
  • jaka jest różnica pomiędzy indeksem klastrowym i nieklastrowym.

Indeksacja

W większości publikacjach, dotyczących baz danych, indeksy porównuje się ze strukturą książki telefonicznej, a dokładniej mówiąc, samej formy organizacji spisu abonentów (alfabetyczna, podzielona tematycznie itp.). To niby trywialne zestawienie idealnie obrazuje, w jaki sposób indeksy działają oraz jaka jest ich architektura w systemie do zarządzania bazami danych. Brnąc przez kolejne przykłady, w poprzednich częściach Encyklopedii SQL, czytelnik nie spotkał się bezpośrednio z zagadnieniem indeksacji kolumn. Można więc zaryzykować twierdzenie, iż mechanizm ten praktycznie nie jest potrzebny w codziennej pracy z magazynem danych. Niestety śmiałość tej tezy nie odnosi się do wewnętrznych mechanizmów serwera SQL, który wykorzystuje indeksy, aby poradzić sobie z mnogością informacji podczas selekcji, sortowania, wyszukiwania itp. Działanie indeksów jest przezroczyste dla użytkownika końcowego, ale kluczowe dla SQL Server. Dlatego, bez wątpienia, zapoznaj się z tą techniką optymalizacji.

Indeks klastrowy

W środowisku SQL Server rozróżnia się dwa typy indeksów: klastrowy (clustered) i nieklastrowy (nonclustered), często nazywanymi również grupującymi i niegrupującymi. SQL Server 2008 definiuje również dwa dodatkowe indeksy: PrimaryXML oraz Spatial, lecz ze względu na złożoność tematyki, nie będą one omawianie w niniejszej publikacji. Clustered Index swoją strukturą najbardziej przypomina książkę telefoniczną z ułożonymi alfabetycznie wpisami. Indeks ten nie jest odrębnym obiektem składowanym na dysku twardym. Jego podstawowym zadaniem jest narzucanie tabelom, w jaki sposób mają zapisywać wiersze na kolejnych stronach. Istotną informacją jest fakt, iż po utworzeniu indeksu grupującego, struktura stron tabeli podlega reorganizacji wg wskazań indeksu. Oczywiście w obrębie jednej tabeli może znajdować się tylko jeden indeks klastrowy, gdyż jak wskazuje logika, nie można uporządkować danych na kilka sposobów (np. książka telefoniczna jest posegregowana wg jednego klucza – spis alfabetyczny). Dobrą praktyką jest wskazywanie klucza głównego danej tabeli jako indeksu klastrowanego.

Aby utworzyć indeks klastrowy w SQL Server, z poziomu języka T-SQL, należy wykonać następujące zapytanie:

CREATE UNIQUE CLUSTERED INDEX nazwa_indeksu
ON nazwa_tabeli(kolumna)

W SQL Server Management Studio w strukturze Object Browser->Database->Indexes powinny znajdować się wartości wszystkich utworzonych indeksów. Na Rys. 1 został przedstawiony indeks klastrowy o nazwie PK_Employee_EmployeeID w tabeli HumanResources.Employee, bazy danych AdventureWorks, założony na kolumnie EmployeeID, będącym notabene kluczem głównym omawianej tablicy.

Rys. 1. Indeks klastrowy tablicy HumanResources.Employee, bazy danych AdventureWorks.

Indeks nieklastrowy

Indeks nieklastrowy (niegrupujący), w przeciwieństwie do Clustered Index, zapisywany jest na zupełnie oddzielonych od tabeli stronach. Tego typu technikę można porównać do indeksu słów kluczowych, znajdujących się na końcu każdej książki. W SQL Server indeks nieklastrowy wskazuje na dokładnie jeden wiersz w tabeli (co stanowi różnicę pomiędzy książkowym rozwiązaniem). Ponieważ Nonclustered index nie wpływa na organizację stron tabeli, dla klażdej z nich można wykorzystać maksymalnie 249 indeksów.

Poniższe polecenie tworzy indeks nieklastrowy w T-SQL:

CREATE NONCLUSTERED INDEX nazwa_indeksu
ON nazwa_tabeli(kolumna)

Przykładowo indeks nieklastrowy (AK_Department_Name), tabeli HumanResources.Department, bazy AdventureWorks został zaprezentowany na Rys. 2.

Rys. 2. Indeks nieklastrowy tabeli HumanResources.Department.

Indeks kompozytowy

W celu zwiększenia optymalizacji operacji na bazie danych, kolumny, do których programiści odwołują się najczęściej (np. imię i nazwisko, bądź adres zamieszkania i miasto), można pogrupować w ramach pojedynczego indeksu kompozytowego (Composite Index). Ważne jest, aby wszystkie kolumny, wchodzące w skład indeksu kompozytowego, znajdowały się w obrębie tej samej tabeli. Aby utworzyć indeks kompozytowy na kolumnach: FirstName, LastName, tabeli Person.Contact, bazy danych AdventureWorks, należy wykonać następujące zapytanie:

CREATE INDEX Composite_FirstName_LastName
ON Person.Contact
(
  FirstName,
  LastName
)

Na jakie kolumny warto zakładać indeksy

Dobre praktyki projektowania baz danych wskazują, iż:

  1. Indeksy powinno zakładać się na kolumnach rzadko modyfikowanych.
  2. Największą optymalizację uzyska się, zakładając indeks na kolumnach wykorzystywanych do warunków złączeniowych, jako kluczy do wyszukiwania danych bądź sortowania zbioru wynikowego.
  3. Kolumny typu PRIMARY/FOREIGN KEY powinny zawierać indeksy: klastrowy i nieklastrowy.
Informacja

Zapamiętaj, że:

  1. SQL Server wyróżnia następujące typy indeksów: klastrowy, nieklastrowy, PrimaryXML, Spatial oraz dodatkowo: indeks kompozytowy.
  2. CLUSTERED INDEX może być zakładany na maksymalnie jednej kolumnie.
  3. NONCLUSTERED INDEX może być zakładany na wielu kolumnach.
  4. Nie można utworzyć indeksu dla kolumn typu: bit, text, image.

Podsumowanie

Z niniejszej publikacji dowiedziałeś się, w jaki sposób wykorzystać indeksy do wprowadzenia wstępnej optymalizacji pracy serwera SQL.

W kolejnej publikacji nauczysz się, w jaki sposób efektywnie pracować z procedurami składowanymi.

Dodatkowo zobacz: