Różnica wydajności między indeksem klastrowanym i nieklastrowanym

Czytałem Clusteredi Non Clustered Indexes.

Clustered Index- Zawiera strony danych. Oznacza to, że pełna informacja o wierszu będzie obecna w kolumnie Indeks klastrowany.

Non Clustered Index- Zawiera tylko informacje o Lokalizatorze wierszy w postaci kolumny Indeks klastrowany (jeśli jest dostępny) lub Identyfikator pliku + numer strony + łączna liczba wierszy na stronie. Oznacza to, że silnik zapytań musi wykonać dodatkowy krok w celu zlokalizowania rzeczywistych danych.

Zapytanie - Jak mogę sprawdzić różnicę wydajności z pomocą praktyczny przykład, jak wiemy, że tabela może mieć tylko jedną Clustered Indexi zapewnia sortingu Clustered Index Columni Non Clustered Indexnie zapewniają sortingi może obsługiwać 999 Non Clustered Indexesw SQL Server 2008do 249 w SQL Server 2005.

Bardzo dobre pytanie, ponieważ jest to tak ważna koncepcja. Jest to jednak duży temat, a to, co wam pokażę, jest uproszczeniem, dzięki czemu można zrozumieć podstawowe pojęcia.

Po pierwsze, gdy zobaczysz tabelę myśli indeksu klastrowego . W SQL Server, jeśli tabela nie zawiera indeksu klastrowanego, jest stertą. Utworzenie indeksu klastrowego w tabeli faktycznie przekształca tabelę w strukturę typu b-drzewo. Indeks klastrowany JEST twoją tabelą, nie jest on oddzielny od tabeli

Czy zastanawiałeś się kiedyś, dlaczego możesz mieć tylko jeden indeks klastrowany? Cóż, gdybyśmy mieli dwa indeksy klastrowe, potrzebowalibyśmy dwóch kopii tabeli. W końcu zawiera dane.

Spróbuję to wyjaśnić na prostym przykładzie.

UWAGA: W tym przykładzie utworzyłem tabelę i wypełniłem ją ponad 3 milionami losowych wpisów. Następnie uruchomiłem aktualne zapytania i wkleiłem tutaj plany wykonania.

To, co naprawdę musisz zrozumieć, to notacja O lub wydajność operacyjna . Załóżmy, że masz następującą tabelę.

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](100) NOT NULL,
[CustomerSurname] [varchar](100) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
  , IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON
  , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Mamy więc podstawową tabelę z kluczem klastrowym na CustomerID (klucz podstawowy jest klastrowany domyślnie). Zatem tabela jest uporządkowana / uporządkowana na podstawie klucza podstawowego CustomerID. Poziomy pośrednie będą zawierać wartości CustomerID. Strony danych będą zawierać cały wiersz, dlatego jest to wiersz tabeli.

Stworzymy również indeks nieklastrowany w polu CustomerName. Zrobi to następujący kod.

CREATE NONCLUSTERED INDEX [ix_Customer_CustomerName] ON [dbo].[Customer] 
 (
[CustomerName] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
  , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
  , DROP_EXISTING = OFF, ONLINE = OFF
  , ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Tak więc w tym indeksie na stronach danych / poziomie liści znajduje się wskaźnik do poziomów pośrednich w indeksie klastrowym. Indeks jest uporządkowany / uporządkowany wokół pola CustomerName. Zatem poziom pośredni zawiera wartości CustomerName, a poziom liścia będzie zawierał wskaźnik (te wartości wskaźnika są w rzeczywistości wartościami klucza podstawowego lub kolumny CustomerID).

Tak, jeśli wykonamy następujące zapytanie:

SELECT * FROM Customer WHERE CustomerID = 1 

SQL prawdopodobnie odczyta indeks klastrowany poprzez operację wyszukiwania. Operacja wyszukiwania to wyszukiwanie binarne, które jest znacznie wydajniejsze niż skanowanie, które jest wyszukiwaniem sekwencyjnym. Tak więc w naszym powyższym przykładzie indeks jest odczytywany i za pomocą wyszukiwania binarnego SQL może wyeliminować dane, które nie spełniają kryteriów, których szukamy. Zobacz zrzut ekranu dołączonego planu zapytań.

Tak więc liczba operacji lub notacja O dla operacji wyszukiwania jest następująca:

1. Przeszukuj binarnie indeks klastrowany, porównując szukaną wartość z wartościami na poziomie pośrednim.
2. Zwróć pasujące wartości (pamiętaj, ponieważ indeks klastrowany zawiera wszystkie dane, może zwrócić wszystkie kolumny z indeksu, ponieważ są to dane wierszy)

To dwie operacje. Jeśli jednak wykonamy następujące zapytanie:

SELECT * FROM Customer WHERE CustomerName ='John'

SQL będzie teraz używał nieklastrowanego indeksu w CustomerName do wyszukiwania. Ponieważ jednak jest to indeks nieklastrowany, nie zawiera wszystkich danych w wierszu.

Tak więc SQL przeprowadzi wyszukiwanie na poziomach pośrednich w celu znalezienia pasujących rekordów, a następnie wykona wyszukiwanie przy użyciu wartości zwróconych w celu ponownego wyszukiwania w indeksie klastrowym (czyli tabeli) w celu pobrania rzeczywistych danych. To brzmi myląco, wiem, ale czytam dalej i wszystko stanie się jasne.

Ponieważ nasz indeks nieklastrowany zawiera tylko pole CustomerName (wartości pól indeksowanych przechowywane w węzłach pośrednich) i wskaźnik do danych, które są CustomerID, indeks nie ma zapisu CustomerSAME. Nazwisko klienta należy pobrać z indeksu klastrowego lub tabeli.

Podczas uruchamiania tego zapytania otrzymuję następujący plan wykonania:

Na powyższym zrzucie ekranu zauważasz dwie ważne rzeczy

1. SQL mówi, że mam brakujący indeks (tekst w kolorze zielonym). SQL sugeruje utworzenie indeksu na CustomerName, który zawiera CustomerID i CustomerSAME.
2. Zobaczysz również, że 99% czasu zapytania jest poświęcane na wyszukiwanie klucza w indeksie klucza podstawowego / indeksie klastrowym.

Dlaczego SQL ponownie sugeruje indeks na CustomerName? Cóż, ponieważ indeks zawiera tylko identyfikator klienta, a nazwa klienta SQL nadal musi znaleźć nazwę klienta z tabeli / indeksów klastrowych.

Gdybyśmy utworzyli indeks i umieściliśmy kolumnę CustomerSename w indeksie, SQL byłby w stanie spełnić całe zapytanie, po prostu czytając indeks nieklastrowany. Właśnie dlatego SQL sugeruje zmianę mojego indeksu nieklastrowego.

Tutaj możesz zobaczyć dodatkową operację, jaką musi wykonać SQL, aby uzyskać kolumnę CustomerSname z klucza klastrowanego

Zatem liczba operacji jest następująca:

1. Przeszukuj binarnie indeks nieklastrowany, porównując szukaną wartość z wartościami na poziomie pośrednim
2. W przypadku pasujących węzłów przeczytaj węzeł poziomu liścia, który będzie zawierał wskaźnik danych w indeksie klastrowym (przy okazji węzły poziomu liścia będą zawierać wartości klucza podstawowego).
3. Dla każdej zwróconej wartości wykonaj odczyt indeksu klastrowego (tabeli), aby uzyskać wartości wierszy tutaj, odczytalibyśmy CustomerSAME.
4. Zwróć pasujące wiersze

To 4 operacje, aby uzyskać wartości. Dwukrotnie potrzebna liczba operacji w porównaniu do odczytu indeksu klastrowego. Pokaż, że indeks klastrowany jest najmocniejszym indeksem, ponieważ zawiera wszystkie dane.

Aby wyjaśnić ostatni punkt. Dlaczego mówię, że wskaźnik w indeksie nieklastrowanym jest wartością klucza podstawowego? Aby wykazać, że węzły poziomu liścia indeksu nieklastrowanego zawierają wartość klucza podstawowego, zmieniam zapytanie na:

SELECT CustomerID
FROM Customer
WHERE CustomerName='Jane'

W tym zapytaniu SQL może odczytać identyfikator klienta z indeksu nieklastrowanego. Nie trzeba wyszukiwać indeksu klastrowanego. Widać to po planie wykonania, który wygląda tak.

Zwróć uwagę na różnicę między tym zapytaniem a poprzednim zapytaniem. Nie ma wyszukiwania. SQL może znaleźć wszystkie dane w indeksie nieklastrowanym

Mamy nadzieję, że zrozumiesz, że indeks klastrowany jest tabelą, a indeksy nieklastrowe NIE zawierają wszystkich danych. Indeksowanie przyspieszy zaznaczenia, ponieważ można wyszukiwać binarnie, ale tylko indeksy klastrowe zawierają wszystkie dane. Dlatego wyszukiwanie indeksu nieklastrowego prawie zawsze spowoduje załadowanie wartości z indeksu klastrowanego. Te dodatkowe operacje powodują, że indeksy klastrowane są mniej wydajne niż indeks klastrowany.

Mam nadzieję, że to wszystko wyjaśni. Jeśli coś nie ma sensu, napisz komentarz, a ja postaram się wyjaśnić. Jest tu raczej późno, a mój mózg jest trochę płaski. Czas na czerwonego byka.

„Oznacza to, że silnik zapytań musi wykonać dodatkowy krok w celu zlokalizowania rzeczywistych danych”.

Niekoniecznie - jeśli indeks obejmuje dane zapytanie, nie trzeba podróżować do stron danych. Ponadto dzięki dołączonym kolumnom do indeksu nieklastrowego można dodać dodatkowe kolumny, aby pokrywały się bez zmiany wielkości klucza.

Tak więc ostateczna odpowiedź brzmi - to zależy (od znacznie większej ilości informacji, niż można naprawdę ująć w jednym pytaniu) - musisz zrozumieć wszystkie możliwości indeksów, a plan wykonania dla danego zapytania może odbiegać od twoich oczekiwań.

Mam ogólną zasadę, że tabela zawsze ma indeks klastrowany (i zwykle na identyfikatorze lub sekwencyjnym GUID), ale indeksy nieklastrowe są dodawane w celu zwiększenia wydajności. Ale zawsze są wyjątki - tabele sterty mają miejsce, szersze indeksy klastrowe mają miejsce. Pozornie zbędne indeksy, które są węższe i mieszczą więcej wierszy na stronie, mają swoje miejsce. itd itd.

I nie martwiłbym się limitami różnych dozwolonych indeksów - prawie na pewno nie wejdzie to w grę w wielu rzeczywistych przykładach.