sobota, 16 sierpnia 2014

Indeks klastrowy - prześwietlenie cz 1

Tym postem chciałbym zapoczątkować całą serie dotyczącą indeksu klastrowego. Temat ten jest dość ciekawy oraz złożony i warto na niego spojrzeć od różnych stron.

Indeks klastrowy jest dość specyficzny w swojej naturze, jako że tylko jeden taki może być założony na tabeli. Wynika to z faktu, że liście tego indeksu zawierają dane i są tak naprawdę strukturą tabeli. Nie oznacza to jednak, że dane są fizycznie zapisane na dysku w kolejności klucza indeksu. SQL Server, może ale nie musi, zwrócić wyniku zapytania w kolejności klucza indeksu. Jest to jeden z mitów krążąych na jego temat.

Cykl wpisów poświęcony indeksowi klatrowemu rozpocznę właśnie od obalenia tej teorii.
Poniższy skrypt tworzy tabelę z dwoma kolumnami oraz wypełnią ją danymi. Pierwsza kolumna zawiera wartości od A-Z a druga od 1-26 w kolejności malejącej.
set nocount on
go
 
create table ClusteredIndexOrder (
    Col1 char(1) primary key clustered,
    Col2 int not null unique
)
 
go
 
insert into ClusteredIndexOrder (Col1, Col2)
select CHAR(ASCII('A') + number - 1) as col1, 27 - number as col2 from (
select distinct number from master.dbo.spt_values where number between 1 and 26
) numbers
go
Rezultatem jest taka oto struktura

Col1 Col2
---- -----
A    26
B    25
C    24
D    23
E    22
F    21
G    20
H    19
I    18
J    17
K    16
L    15
M    14
N    13
O    12
P    11
Q    10
R    9
S    8
T    7
U    6
V    5
W    4
X    3
Y    2
Z    1
 
Jeżeli prawdą byłoby, że dane są zwrócone w kolejności klucza indeksu klastrowego to poniższe zapytanie powinno zwrócić wynik jak powyżej.
 
select * from ClusteredIndexOrder
 
A co ono zwraca?
 
Col1 Col2
---- -----
Z    1
Y    2
X    3
W    4
V    5
U    6
T    7
S    8
R    9
Q    10
P    11
O    12
N    13
M    14
L    15
K    16
J    17
I    18
H    19
G    20
F    21
E    22
D    23
C    24
B    25
A    26
“Niespodziewanie” dane pojawiły się w odwrotnej kolejności.

Z pomocą rozwiązania zagadki przychodzi analiza planu wykonania.





Otóż to nie indeks klastrowy był w tym przypadku skanowany a unikalny założony na kolumnie Col2. Jako, że wszystkie kolumny klucza indeksu klastrowego znajdują się w liściach indeksu nieklastrowego i indeks ten w całości pokrywa to zapytanie optymalizator zapytań w pierwszej kolejności wybiera indeksy nieklastrowe. Przykład ten jest bardzo specyficzny. Większość tabel, która maja założony indeks klastrowy i dodatkowy indeks nieklastrowy będzie miała więcej kolumn. Indeks nieklastrowy będzie też zawierał mniej danych w związku z czym jeżeli takowy indeks pokrywa w całości zapytanie mniej operacji IO, wczytywania danych do pamięci i blokowania zasobów będzie wymagane.

Poniższe dwa zapytania zwrócą dane w kolejności klucza indeksu klastrowego.

select * from ClusteredIndexOrder where Col1 <> ''
select * from ClusteredIndexOrder with(index=1)

Plany zapytań w ich przypadku będą następujące






W pierwszym przypadku predykat na kolumnie indeksu klucza klastrowego wymusił operator seek natomiast w drugim przypadku scan był wywołany przez wymuszenie indeksu w zapytaniu.
Poniższe zapytanie zwróci wynik w kolejności klucza na indeksie unikalnym.

select * from ClusteredIndexOrder where Col2 <> 0
Wynika to z faktu, że zapytanie zawiera predykat na kolumnie klucza, co przeważnie skutkuje bardzie optymalnym planem zapytania wykorzystującym operator seek.


W jednym znastępnych wpisów pokaże jakie okoliczności muszą zajść by scan był mimowszystko preferowanym operatorem.

To co jest najważniejsze to to, że kolejność wyników może być zagwarantowana tylko poprzez klauzulę ORDER BY. W każdym innym przypadku wynik jest niedetermistyczny co pokaże w kilku kolejnych wpisach.