Indeks unikalny klastrowany

Indeks unikalny klastrowany to indeks fizycznie porządkujący dane tabeli, jednocześnie wymuszający unikalność wartości.

Budowa i działanie indeksu unikalnego klastrowanego

Indeks unikalny klastrowany łączy dwie właściwości. Po pierwsze, wymusza niepowtarzalność klucza indeksu, czyli zabrania zapisania dwóch wierszy z taką samą wartością lub takim samym zestawem wartości w kolumnach objętych indeksem. Po drugie, określa fizyczny sposób uporządkowania wierszy tabeli według klucza indeksu. W praktyce struktura danych tabeli jest związana z poziomem liściowym indeksu, dlatego tabela może mieć tylko jeden indeks klastrowany.

W SQL Server indeks klastrowany ma strukturę drzewa B+. Węzły pośrednie prowadzą do odpowiednich zakresów kluczy, a poziom liściowy zawiera dane wierszy tabeli, jeżeli tabela jest przechowywana jako indeks klastrowany. Przy wyszukiwaniu po kluczu silnik przechodzi od korzenia przez poziomy pośrednie do właściwego fragmentu liści. Unikalność jest kontrolowana podczas operacji zapisu, a nie dopiero przy późniejszym odczycie danych. Próba dodania lub zmiany wiersza tak, aby powstał duplikat klucza, kończy się błędem i operacja nie może zostać zatwierdzona.

Klucz indeksu, kolejność kolumn i ograniczenie UNIQUE

Indeks może obejmować jedną kolumnę albo kilka kolumn. Przy indeksie złożonym unikalność dotyczy całej kombinacji wartości w określonej kolejności, a nie każdej kolumny niezależnie. Przykładowo, indeks na kolumnach id_firmy i numer_umowy może dopuścić ten sam numer umowy dla różnych firm, ale nie dopuści dwóch wierszy z identyczną parą wartości. Kolejność kolumn wpływa również na użyteczność indeksu podczas wyszukiwania. Predykat filtrujący po pierwszej kolumnie klucza zwykle pozwala wykorzystać uporządkowanie drzewa skuteczniej niż predykat dotyczący wyłącznie dalszej kolumny.

W SQL Server unikalny indeks klastrowany może zostać utworzony bezpośrednio jako indeks albo zostać zbudowany w związku z ograniczeniem PRIMARY KEY lub UNIQUE. Ograniczenie opisuje regułę integralności danych, natomiast indeks jest strukturą wykorzystywaną do jej egzekwowania i do realizacji zapytań. Jeżeli tabela nie ma jeszcze indeksu klastrowanego, ograniczenie klucza głównego jest często tworzone jako klastrowane, ale nie jest to wymóg: można jawnie wybrać indeks nieklastrowany, jeśli projekt tabeli tego wymaga. Jedna tabela nadal może mieć tylko jeden indeks klastrowany, lecz może mieć wiele indeksów nieklastrowanych.

Warto odróżnić unikalność od samego sortowania wyników. Indeks przechowuje klucze w uporządkowanej strukturze, ale zapytanie nie powinno polegać na kolejności wierszy bez użycia klauzuli ORDER BY. Optymalizator może wykorzystać indeks do uniknięcia dodatkowego sortowania, jednak kolejność zwróconych rekordów jest gwarantowana dopiero wtedy, gdy wynika z jawnie określonego porządku zapytania.

Wpływ na zapisy, odczyty i rozmiar tabeli

Indeks unikalny klastrowany jest szczególnie przydatny, gdy zapytania często wyszukują rekordy po jego kluczu albo pobierają zakres wartości. Dzięki uporządkowaniu danych silnik może wykonać wyszukiwanie indeksowe zamiast przeglądać całą tabelę. Odczyt zakresu, na przykład rekordów należących do określonego przedziału identyfikatorów lub dat, może korzystać z kolejnych stron liściowych. Korzyść zależy jednak od selektywności warunku, rozkładu danych oraz planu wykonania wybranego przez optymalizator.

Klucz klastrowy wpływa na wszystkie indeksy nieklastrowane tabeli. W SQL Server indeksy nieklastrowane przechowują jako lokalizator wiersza klucz indeksu klastrowego, dlatego szeroki klucz złożony z wielu kolumn może zwiększyć rozmiar pozostałych indeksów. Z tego powodu klucz klastrowany powinien być możliwie wąski, stabilny i zwykle rosnący, o ile odpowiada rzeczywistym wzorcom dostępu do danych. Zmiana wartości klucza klastrowego może oznaczać konieczność przeniesienia rekordu w inne miejsce struktury, a także aktualizacji wartości używanej przez indeksy nieklastrowane.

Niepoprawnie dobrany klucz może powodować fragmentację stron i dodatkowe operacje wejścia-wyjścia. Szczególne znaczenie ma klucz, którego nowe wartości pojawiają się losowo w całym zakresie istniejących danych. Wstawienia mogą wtedy wymagać podziału stron, czyli utworzenia nowych stron i rozdzielenia między nie dotychczasowych wpisów. Przy projektowaniu indeksu analizuje się więc nie tylko wymóg unikalności, lecz także częstotliwość operacji INSERT, UPDATE i DELETE, rozmiar wierszy oraz typowe warunki zapytań.

Rozpoznawanie problemów z indeksem podczas diagnostyki

Diagnostyka indeksu unikalnego klastrowanego powinna rozpocząć się od sprawdzenia definicji tabeli, klucza, ograniczeń i indeksów nieklastrowanych. Należy ustalić, które kolumny tworzą klucz, w jakiej kolejności występują oraz czy aplikacja nie próbuje zapisać duplikatu. Błąd naruszenia unikalności nie oznacza automatycznie uszkodzenia indeksu. Częściej wskazuje na konflikt między regułą integralności a danymi wejściowymi, na przykład ponowione przetworzenie tego samego identyfikatora albo nieprawidłowo przygotowaną aktualizację.

W przypadku spowolnień analizuje się plany wykonania zapytań, statystyki, fragmentację oraz blokowanie. Indeks może być formalnie poprawny, a mimo to nie pasować do warunków używanych przez zapytania. Przydatne jest porównanie czasu wyszukiwania po kluczu z czasem operacji zakresowych i zapisów. Trzeba też sprawdzić, czy klucz nie jest nadmiernie szeroki oraz czy dodatkowe indeksy nie powodują nieproporcjonalnego kosztu podczas modyfikacji danych. Diagnostyka i ewentualna naprawa powinna uwzględniać zależności między tabelą, ograniczeniami integralności i wszystkimi strukturami indeksowymi, a nie tylko pojedynczy komunikat błędu.