Czy unikalność indeksów w SQL ma znaczenie?

Dziś o temacie, który w moim mniemaniu nie jest do końca znany chodzi mianowicie o tradycyjne indeksy zgrupowane, które nie są unikalne. W zdecydowanej większości przypadków indeksy klastrowane wiążą się z założeniem klucza głównego na tabeli (ang. Primary Key) lub chociażby ograniczenia unikalności (ang. Unique constraint). Czym zatem różnią się indeksy unikalne od nieunikalnych? Czy ma to w ogóle jakieś znaczenie? Sprawdźmy to!

Do celów demonstracyjnych wykorzystamy dwie tabele o tej samej strukturze, które można stworzyć w dowolnej bazie danych (w moim przypadku będzie to AdventureWorksDW2014):

DROP TABLE IF EXISTS UniqueTable
GO
DROP TABLE IF EXISTS NonUniqueTable
GO

CREATE TABLE UniqueTable
(
ID INT PRIMARY KEY,
DateColumn datetime DEFAULT GETDATE(),
TextColumn CHAR(7900) DEFAULT 'A'
)
GO

CREATE TABLE NonUniqueTable
(
ID INT,
DateColumn datetime DEFAULT GETDATE(),
TextColumn CHAR(7900) DEFAULT 'A'
)
GO

Długość wiersza jest na tyle szeroka, że na jednej stronie zmieści się jeden wiersz. Pierwsza tabela o nazwie UniqueTable jest, jak sama nazwa wskazuje, tabelą unikalną po przez to, że zdefiniowaliśmy klucz główny tabeli. Jeśli nie wskażemy inaczej klucz główny na tabeli powoduje tworzenie unikalnego indeksu na tabeli – możemy ten fakt sprawdzić używając np. sp_help:

sp_help 'dbo.UniqueTable'

W przypadku naszej nieunikalnej tabeli (dbo.NonUniqueTable) indeks stworzymy sobie jawnie:

CREATE CLUSTERED INDEX IX_TEST
ON NonUniqueTable(ID)

Tworząc indeks w ten sposób możemy być pewni, że nie wymusza on unikalności – jeśli byśmy chcieli wymusić takową składnia polecenia CREATE wyglądała by następująco:

CREATE UNIQUE CLUSTERED INDEX IX_TEST ON NonUniqueTable(ID)

Wstawmy do obu tabel po tysiąc wierszy – do tabeli unikalnej wstawiamy “metodą chałupniczą” kolejne wartości. Dla tabeli nienukalnej wystarczy wstawienie wartości 1:

INSERT INTO UniqueTable
(ID)
SELECT ISNULL(MAX(ID),0)+1 FROM UniqueTable
GO 1000

INSERT INTO NonUniqueTable
(ID)
SELECT 1
GO 1000

Sprawdźmy jak wyglądają zaalokowane strony – do tego celu wykorzystamy funkcję sys.dm_db_database_page_allocations:

SELECT F.page_type,
       F.page_type_desc,
       F.page_level,
       F.next_page_file_id,
	  F.allocated_page_file_id,
       F.allocated_page_page_id,
       F.page_level
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.UniqueTable'), 1, NULL, 'DETAILED') AS F
where F.is_allocated=1
order by F.page_level

Rezultat powyższego zapytania wygląda następująco:

Zaalokowano na potrzeby tabeli zgodnie z przewidywaniami 1000 stron danych, dodatkowo widzimy stronę Index Allocation Map oraz strony związane ze strukturą b-drzewa indeksu. Zaznaczyłem jedną ze stron ze względu na fakt, iż tą właśnie stronę podejrzymy wykorzystując DBCC PAGE (flaga  3604 została włączona aby otrzymać rezultat działania DBCC PAGE):

DBCC TRACEON(3604)
DBCC PAGE(AdventureWorksDW2014,1,9089,3)

Z powyższego zrzutu możemy wywnioskować jak wygląda struktura indeksu zgrupowanego. Interesująca może być dla nas wartość w kolumnie Row Size wynosząca 11 bajtów. Na tę liczbę składa się 1 bajt statusowy + 4 bajty na klucz indeksu (wybraliśmy kolumnę z INT która tyle właśnie “waży”) + 4 bajty na identyfikator strony (PageID) + 2 bajty na identyfikator pliku (FileID).

Przejdźmy teraz do analizy strony indeksu nieunikalnego i sprawdźmy czy się ona czymś różni. Będziemy postępować w analogiczny sposób – najpierw znajdźmy wybraną stronę indeksu:

SELECT F.page_type,
       F.page_type_desc,
       F.page_level,
       F.next_page_file_id,
	  F.allocated_page_file_id,
       F.allocated_page_page_id,
       F.page_level
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.NonUniqueTable'), 1, NULL, 'DETAILED') AS F
where F.is_allocated=1
order by F.page_level

Pierwsze co może nam się rzucić w oczy w tym przypadku to fakt, że z tą tabelą związane jest dwie strony więcej – są to strony struktur indeksu. Dlaczego tak się stało? Wykonajmy DBCC PAGE na wybranej stronie:

Tutaj sprawa jest nieco ciekawsza niż w poprzednim przykładzie. Pojawiła się nowa kolumna o nazwie UNIQUIFIER, która została wewnętrznie utworzona aby nadać unikalność kluczowi indeksu! Dokładnie tak! Indeks mimo, że nieunikalny to wewnętrznie każdy wiersz w jakiś sposób musi zostać jednoznacznie znaleziony i właśnie po to stworzony został uniquifier. Działa on w bardzo prosty sposób to znaczy jeżeli mamy N powtarzających się kluczy indeksów to dostają one kolejne numery 0,1,2,3…n. Powoduje to pewien narzut na struktury indeksu gdyż liczby te są przechowywane jako liczby 4 bajtowe. W momencie gdy wiersz zawiera unikalny klucz to uniquifier miał wartość 0:

INSERT INTO NonUniqueTable
(ID)
VALUES (2),(3),(4)
GO

Warto w tym miejscu zaznaczyć, że uniquefier dodaje narzut 4 bajtów z takim wyjątkiem, że pierwsza wartość unikalna takiego narzutu nie posiada co możecie zauważyć nawet na rozmiarze wiersza dla wartości unikalnych 2,3,4 (dodatkowo widać, że narzut z uniquefier wynosi 4 ale całkowicie rozmiar wiersza powiększa się o 8 bajtów w wewnętrznej strukturze strony danych).

Mamy więc narzut 4 bajtów – ale czy to wszystko co zostało dodane do klucza indeksu? Po zadaniu tego pytania możecie się spodziewać, że nie. Ogólnie rzecz biorąc do nieunikalnego klucza indeksu dorzucone zostaną następujące informacje:

• dodatkowe 8 bajtów jeśli wiersz naszego indeksu klastrowanego nie ma typów o zmiennej długości
• dodatkowe 6 bajtów jeśli wiersz naszego indeksu klastrowanego ma typy o zmiennej długości

Dla wartości unikalnych klucza indeksu dorzucone zostanie:

• 0 bajtów jeśli wiersz nie ma kolumn o typach o zmiennej długości lub wszystkie mają null
• 2 bajty jeśli wiersz ma przynajmniej jedną niepustą kolumnę o zmiennej długości

Skąd zatem na powyższym zrzucie stron mamy wielkość wiersza 22? 11 bajtów, które obliczyliśmy poprzednio + mapa bitowa 1 bajt +8 bajtów narzutu przez uniquefier i pozostałe struktury ponieważ brak kolumn o zmiennej długości.

Dodatkowo w przypadku odpytywania kolumny na której istnieje taki indeks unikalny operacja SELECT DISTINCT nie wywoła operacji SORT z eliminacją duplikatów bo SQL wie z metadanych, że takowych tam nie ma. Takie samo zapytanie na kluczu nieunikalnym spowoduje wykorzystanie na planie operacji SORT co będzie zdecydowanie zwolni nasze zapytanie.

Jak widzicie po powyższym przykładzie to czy indeks jest unikalny czy też nie ma znaczenie i w niektórych przypadkach może mieć ogólny wpływ na nasze zapytania. Polecam zawsze kierować się zdrowym rozsądkiem i jeżeli z biznesowego punktu widzenia kolumna jest unikalna to założyć indeks unikalny. Pozdrawiam!