W SQL Server magazyn zorientowany na wiersze zarówno indeksy klastrowe, jak i nieklastrowane są zorganizowane jako drzewa B.
( Źródło obrazu )
Kluczową różnicą między indeksami klastrowymi a indeksami nieklastrowanymi jest to, że poziom liścia indeksu klastrowanego to tabela. Ma to dwie konsekwencje.
- Wiersze na stronach liścia indeksu klastrowanego zawsze zawierają coś dla każdej (nierzadkiej) kolumny w tabeli (albo wartość, albo wskaźnik do wartości rzeczywistej).
- Indeks klastrowy jest podstawową kopią tabeli.
Indeksy nieklastrowane mogą również wykonać punkt 1, używając INCLUDEklauzuli (od SQL Server 2005), aby jawnie dołączyć wszystkie niekluczowe kolumny, ale są to drugorzędne reprezentacje i zawsze istnieje kolejna kopia danych (sama tabela).
CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)
CREATE UNIQUE CLUSTERED INDEX ci ON T(A,B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A,B) INCLUDE (C,D)
Dwa powyższe indeksy będą prawie identyczne. Za pomocą stron indeksu wyższego poziomu zawierających wartości dla kluczowych kolumn A,Bi stron zawierających liśćA,B,C,D
Może istnieć tylko jeden indeks klastrowany na tabelę, ponieważ same wiersze danych można sortować tylko w jednej kolejności.
Powyższy cytat z książek SQL Server online powoduje wiele zamieszania
Moim zdaniem byłoby to o wiele lepiej sformułowane jako.
Może istnieć tylko jeden indeks klastrowany na tabelę, ponieważ wiersze klastrowego indeksu na poziomie liścia są wierszami tabeli.
Cytat z książek w Internecie nie jest niepoprawny, ale należy wyjaśnić, że „sortowanie” zarówno wskaźników niesklastrowanych, jak i klastrowanych jest logiczne, a nie fizyczne. Jeśli czytasz strony na poziomie liścia, postępując zgodnie z połączoną listą i czytasz wiersze na stronie w kolejności tablic szczelin, to odczytujesz rzędy indeksu w posortowanej kolejności, ale fizycznie strony nie mogą być sortowane. Powszechnie uważa się, że przy indeksie klastrowym wiersze są zawsze fizycznie przechowywane na dysku w tej samej kolejności, w jakiej klucz indeksu jest fałszywy.
To byłoby absurdalne wdrożenie. Na przykład, jeśli wiersz zostanie wstawiony na środku tabeli 4 GB, SQL Server nie musi kopiować 2 GB danych w pliku, aby zrobić miejsce dla nowo wstawionego wiersza.
Zamiast tego następuje podział strony. Każda strona na poziomie liści zarówno indeksów klastrowych, jak i nieklastrowanych ma adres ( File:Page) następnej i poprzedniej strony w logicznej kolejności kluczy. Strony te nie muszą być ciągłe ani w kolejności kluczowej.
np. może być połączony łańcuch stron 1:2000 <-> 1:157 <-> 1:7053
Kiedy dochodzi do podziału strony, nowa strona jest przydzielana z dowolnego miejsca w grupie plików (z zakresu mieszanego, w przypadku małych tabel lub niepustego zakresu jednolitego należącego do tego obiektu lub nowo przydzielonego zakresu jednolitego). Może to nawet nie znajdować się w tym samym pliku, jeśli grupa plików zawiera więcej niż jeden.
Stopień, w jakim logiczna kolejność i ciągłość różni się od wyidealizowanej wersji fizycznej, to stopień logicznej fragmentacji.
W nowo utworzonej bazie danych z jednym plikiem uruchomiłem następujące.
CREATE TABLE T
(
X TINYINT NOT NULL,
Y CHAR(3000) NULL
);
CREATE CLUSTERED INDEX ix
ON T(X);
GO
--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
@X AS INT
SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100
ORDER BY CRYPT_GEN_RANDOM(4)
OPEN @C1;
FETCH NEXT FROM @C1 INTO @X;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO T (X)
VALUES (@X);
FETCH NEXT FROM @C1 INTO @X;
END
Następnie sprawdź układ strony za pomocą
SELECT page_id,
X,
geometry::Point(page_id, X, 0).STBuffer(1)
FROM T
CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER BY page_id
Wyniki były wszędzie. Pierwszy rząd w kolejności klawiszy (o wartości 1 - podświetlonej strzałką poniżej) znajdował się na prawie ostatniej stronie fizycznej.
Fragmentację można zmniejszyć lub usunąć poprzez przebudowę lub reorganizację indeksu w celu zwiększenia korelacji między porządkiem logicznym a porządkiem fizycznym.
Po bieganiu
ALTER INDEX ix ON T REBUILD;
Mam następujące
Jeśli tabela nie ma indeksu klastrowego, nazywana jest stertą.
Indeksy nieklastrowane można budować na stercie lub indeksie klastrowym. Zawsze zawierają lokalizator wierszy z powrotem do tabeli podstawowej. W przypadku sterty jest to fizyczny identyfikator wiersza (rid) i składa się z trzech komponentów (Plik: Strona: Slot). W przypadku indeksu klastrowanego lokalizator wierszy jest logiczny (klastrowany klucz indeksu).
W tym drugim przypadku, jeśli indeks nieklastrowany już naturalnie zawiera kolumny klucza CI jako kolumny klucza NCI lub INCLUDEkolumny -d, wówczas nic nie jest dodawane. W przeciwnym razie brakujące kolumny klucza CI zostaną po cichu dodane do NCI.
SQL Server zawsze zapewnia, że kolumny kluczy są unikalne dla obu typów indeksu. Mechanizm, w którym jest to wymuszane dla indeksów niezadeklarowanych jako unikalne, różni się jednak między tymi dwoma typami indeksów.
Indeksy klastrowe są uniquifierdodawane do wszystkich wierszy z kluczowymi wartościami, które duplikują istniejący wiersz. To tylko rosnąca liczba całkowita.
W przypadku indeksów nieklastrowanych niezadeklarowanych jako unikalny SQL Server dyskretnie dodaje lokalizator wierszy do nieklastrowanego klucza indeksu. Dotyczy to wszystkich wierszy, a nie tylko tych, które są faktycznie duplikatami.
Nomenklatura klastrowa vs. nieklastrowana jest również używana do indeksów magazynu kolumn. W artykule Rozszerzenia SQL Server Stores Kolumna stanach
Chociaż dane magazynu kolumn nie są tak naprawdę „klastrowane” na żadnym kluczu, zdecydowaliśmy się zachować tradycyjną konwencję SQL Server dotyczącą odwoływania się do indeksu podstawowego jako indeksu klastrowego.
