Optymalizacja zapytań (cd)

Indeksowanie: BB-drzewa
Fizyczna struktura bazy danych
Indeksy
Tadeusz Pankowski
Optymalizacja
www.put.poznan.pl/~tadeusz.pankowski
T. Pankowski/A. Stachowiak
1
Fizyczna struktura bazy danych
2
Fizyczna struktura bazy danych (c.d.)
• Techniki używane do przechowywania dużej ilości
ustrukturalizowanych danych na dysku mają wpływ na:
projektowanie baz, administrowanie oraz implementację
systemów SZBD
• Proces fizycznego projektowania bazy danych związany jest
z wyborem, spośród opcji oferowanych przez SZBD, takiej
techniki organizacji danych, która będzie najlepiej
odpowiadała wymaganiom aplikacji
• Potrzebne dane muszą zostać jak najsprawniej
zlokalizowane na dysku, skopiowane do pamięci głównej,
a następnie, po przetworzeniu, z powrotem zapisane na
dysku
T. Pankowski/A. Stachowiak
T. Pankowski/A. Stachowiak
3
Trwałe dane w bazie danych są przechowywane w pamięci zewnętrznej z
trzech powodów:
ze względu na rozmiar bazy danych,
odporność pamięci zewnętrznej na awarie,
koszt jednostkowy.
Pamięć zewnętrzna ma organizację plikową, oznacza to, że jednostką
alokacji na dysku jest plik.
T. Pankowski/A. Stachowiak
4
Fizyczna struktura bazy danych (c.d.)
Fizyczna struktura bazy danych (c.d.)
• Dane przechowywane na dysku są zorganizowane w pliki rekordów.
Każdy rekord składa się z pól przechowujących wartości.
• Organizacja pliku określa sposób uporządkowania rekordów w pliku
przechowywanym na dysku. Wybór właściwej organizacji zależy od
sposobu użytkowania danego pliku i jest zadaniem administratora BD.
• Podstawowe metody organizacji plików:
• plik stertowy (heap file) – dopisuje nowe rekordy na końcu pliku
• plik posortowany (sorted file) – zachowuje uporządkowanie
rekordów według wartości określonego pola
• plik mieszający (hash file) – wykorzystuje funkcję mieszającą
stosowaną względem określonego pola w celu określenia miejsca
umieszczenia rekordu na dysku
• Pomocnicze struktury dostępu: B-drzewa, indeksy
T. Pankowski/A. Stachowiak
• Rekordy mogą mieć stałą lub zmienną długość.
• Stała długość oznacza, że rekord zawsze zajmuje tyle samo
miejsca na dysku, niezależnie od rzeczywistych rozmiarów
przechowywanych w nim danych.
• Rekordy o zmiennej długości przyjmują taki rozmiar jaki
faktycznie przyjmują przechowywane w nich dane.
• Na poziomie dyskowym, rekordy są przechowywane w blokach
dyskowych (strony, page). Rozmiar tych bloków jest
określany przez system operacyjny (zwykle 0.5KB-8KB).
• Rekordy pliku muszą być przydzielane do bloków dyskowych,
ponieważ blok jest jednostką przesyłania danych między
dyskiem a pamięcią
5
Fizyczna struktura bazy danych (c.d.)
6
Fizyczna struktura bazy danych (c.d.)
• Organizacja segmentowana (spanned):
• pozwala przechowywać część rekordu w jednym bloku,
a resztę w drugim. Wskaźnik znajdujący się na końcu
pierwszego bloku wskazuje na blok, w którym znajduje
się reszta rekordu, jeżeli nie jest to następny blok w
kolejności
• Organizacja niesegmentowana (unspanned):
• Alokacja ciągła:
• bloki pliku są alokowane na kolejnych blokach dysku; odczyt pliku jest
bardzo szybki, ale utrudnia rozszerzanie pliku
• Alokacja łączona:
• każdy blok pliku zawiera wskaźnik na następny blok pliku
• Alokacja klastrów:
• kombinacja dwóch poprzednich rozwiązań; kolejne bloki łączone są w
klastry i zawierają wskaźnik na następny klaster
• nie zezwala, aby rozmiar rekordu był większy niż
rozmiar bloku
T. Pankowski/A. Stachowiak
T. Pankowski/A. Stachowiak
• Alokacja indeksowa:
• bloki indeksu zawierają wskaźnik na faktyczne bloki pliku
7
T. Pankowski/A. Stachowiak
8
Fizyczna struktura bazy danych (c.d.)
Fizyczna struktura bazy danych (c.d.)
• Metoda organizacji pliku odnosi się do rozmieszczenia
danych pliku w ramach rekordów, bloków i struktur dostępu.
• W celu wyszukania rekordu na dysku jeden lub większa liczba
bloków zostaje skopiowana do buforów pamięci głównej.
Następnie w buforach wyszukiwany jest potrzebny rekord lub
rekordy. Jeżeli adres bloku zawierającego rekord nie jest
znany, przeszukiwanie musi być liniowe. Każdy blok pliku
zostaje skopiowany do bufora i przeszukiwany do momentu
znalezienia rekordu lub przeszukania wszystkich bloków.
• Celem poprawnej organizacji plików jest możliwość
lokalizowania bloku zawierającego potrzebny rekord w jak
najmniejszej liczbie operacji przesyłania bloków.
9
T. Pankowski/A. Stachowiak
Fizyczna struktura bazy danych (c.d.)
• SQL Server zapisuje dane (rekordy, rows) w 8 KB (=8192B) ciągłych
obszarach przestrzeni nazywanych stronami (ang. Page).
• Strony łączone są w większe struktury nazywane zakresami (ang. Extent).
Każdy zakres składa się z 8 stron, czyli zawiera 64 KB danych. Tabele i
indeksy zapisywane są w zakresach, przy czym małe obiekty mogą dzielić
ten sam zakres. Poszczególne wiersze tabel oraz dane indeksów wraz z
metadanymi przechowywane są na stronach (ogranicza to ich rozmiar).
T. Pankowski/A. Stachowiak
11
• Metoda dostępu to grupa operacji (znajdź,
odczytaj, zmodyfikuj, wstaw, zamknij i inne), które
można stosować względem pliku; niektóre metody
dostępu mogą być stosowane tylko dla określonej
metody organizacji plików
• Techniki metod dostępu:
• szeregowanie,
• mieszanie (hash-owanie),
• indeksowanie
T. Pankowski/A. Stachowiak
10
Fizyczna struktura bazy danych (c.d.)
• Maksymalna liczba danych zapisanych na
pojedynczej stronie wynosi 8060 bajtów
• Pojedynczy wiersz nie może zostać zapisany
na kilku stronach. Wynika z tego, że wielkość
pojedynczego wiersza nie może przekroczyć
8060 bajtów
T. Pankowski/A. Stachowiak
12
Fizyczna struktura bazy danych (c.d.)
Fizyczna struktura bazy danych (c.d.)
CREATE DATABASE nazwa
[ON [PRIMARY]
[<plik> [,…n]]
[, <grupa_plików> [,…n]]
]
[LOG ON {<plik> [,…n]}]
[COLLATE porządek]
[FOR LOAD | FOR ATTACH]
• Baza danych zapisana jest przynajmniej w dwóch
plikach:
• W głównym pliku .mdf (główny plik danych), w którym
zapisane są informacje o strukturze bazy oraz wszystkie
wiersze poszczególnych tabel i indeksy.
• W pliku .ldf (plik rejestru transakcji), w którym
zapisany jest dziennik transakcyjny bazy danych
• Można (a nawet należy) zapisywać bazę w większej
liczbie plików – dane mogą zostać podzielone między
dowolną liczbę plików .ndf (pliki dodatkowe)
gdzie:
<plik> ::=
([NAME = nazwa_logiczna,]
FILENAME = 'nazwa_fizyczna'
[, SIZE = wielkość]
[, MAXSIZE = {maksymalna_wielkość | UNLIMITED}]
[, FILEGROWTH = przyrost]) [,…n]
<grupa_plików> ::=
FILEGROUP nazwa <plik> [,…n]
T. Pankowski/A. Stachowiak
13
14
Fizyczna struktura bazy danych (c.d.)
Fizyczna struktura bazy danych (c.d.)
• Usuwanie bazy danych
DROP DATABASE nazwaBD [, nazwaBD,...]
• Rozbudowa bazy danych
ALTER DATABASE nazwaBD (...)
• sp_dboption – procedura pozwala wyświetlić lub zmienić
ustawienia konfiguracyjne bazy danych
• sp_databases – lista wszystkich baz danych na serwerze
• sp_helpdb [nazwa_bazy] – informacja o wszystkich bazach
na bieżącym serwerze [informacja o konkretnej bazie]
CREATE DATABASE wydawnictwo
ON PRIMARY
(NAME = wydawnictwo_dane1,
FILENAME = 'C:\wydawnictwo\wydawnictwo_dane1.mdf',
SIZE = 5MB,
MAXSIZE = 20MB,
FILEGROWTH = 20%),
(NAME = wydawnictwo_dane2,
FILENAME = 'C:\wydawnictwo\wydawnictwo_dane2.ndf',
SIZE = 1MB,
MAXSIZE = 20MB,
FILEGROWTH = 20%)
LOG ON
(NAME = wydawnictwo_dziennik,
FILENAME = 'C:\wydawnictwo\wydawnictwo_dziennik.ldf')
T. Pankowski/A. Stachowiak
T. Pankowski/A. Stachowiak
15
T. Pankowski/A. Stachowiak
16
Indeksy
Indeksy (c.d.)
• Indeks jest pomocniczą strukturą używaną w celu
przyspieszenia dostępu do żądanych rekordów pliku.
Konstruowany jest w oparciu o pole indeksujące.
• Do konstrukcji indeksu można użyć dowolnego pola,
jak również dla jednego pliku można stworzyć wiele
indeksów.
• Najczęściej używane rodzaje indeksów:
• jednopoziomowe, bazujące na plikach uporządkowanych
• wielopoziomowe, bazujące na strukturach drzewiastych
T. Pankowski/A. Stachowiak
17
Indeksy (c.d.)
T. Pankowski/A. Stachowiak
18
Indeksy (c.d.)
• Zalety: optymalizacja zapytań - jeżeli istnieją powiązane
z tabelą indeksy, znalezienie żądanych danych sprowadza się
do znalezienia w indeksie (który z reguły jest obiektem
wielokrotnie mniejszym niż tabela) wierszy tabeli
spełniających podane kryteria i odczytania wyłącznie tych
stron, na których zostały zapisane znalezione wiersze;
• Wady: indeksy mogą spowalniać operacje wstawiania,
usuwania i modyfikacji danych
T. Pankowski/A. Stachowiak
• Powodem tworzenia indeksów jest poprawa wydajności bazy
danych.
• Indeksy, podobnie jak statystyki, nie wpływają na
wynik zapytania, a jedynie na plan i koszt jego
wykonania.
• Jeżeli nie istnieją indeksy, wyszukanie pojedynczego wiersza
tabeli wiąże się z koniecznością odczytania wszystkich stron, na
których tabela została zapisana
19
• Strukturę indeksu definiuje się zazwyczaj dla jednego pola
pliku, zwanym polem (atrybutem) indeksującym
• Wartości w indeksie są uporządkowane, dzięki czemu można na
nich wykonywać wyszukiwanie binarne (przez połowienie)
• Rodzaje indeksów uporządkowanych:
• indeks główny, określany na polu klucza (unikatowym), według
którego plik jest fizycznie porządkowany
• indeks klastrowania (grupujący), dla pól porządkujących, ale nie
koniecznie unikatowych
• indeks drugorzędny, określony dla pola nieporządkującego
T. Pankowski/A. Stachowiak
20
Indeksy (c.d.)
Indeks główny
• Indeks zagęszczony (dense index) zawiera wpis dla
każdej wartości klucza wyszukiwania znajdującej się w
pliku danych (a więc dla każdego rekordu) (np. indeks
drugorzędny)
• Indeks rzadki, niezagęszczony (sparse index, nondense)
– posiada wpisy tylko dla niektórych wartości
wyszukiwania (np. indeks główny)
T. Pankowski/A. Stachowiak
21
Indeks główny – przykład
Plik indeksu
Ala
Danuta
Helena
...
Plik danych
• Dla każdego bloku danych przypada jeden wpis (rekord)
indeksu
• Rekord, którego wartość klucza wynosi K, gdzie
K(i)≤K<K(i+1) znajduje się zatem w bloku, którego adresem
jest P(i), (rekordy są fizycznie uporządkowane ze względu na
wartość pola klucza)
T. Pankowski/A. Stachowiak
22
Załóżmy, że mamy plik uporządkowany względem pola klucza liczący r = 30 000 rekordów,
przechowywany na dysku o rozmiarze bloku 1024 bajty. Rekordy pliku mają stały rozmiar
R=100 bajtów (i są niesegmentowane). Ile dostępów do bloku wymaga wyszukanie binarne?
Rozwiązanie:
W bloku mamy 1024/100 = 10 rekordów.
Liczba wymaganych bloków wynosi zatem 30 000/10 = 3000.
Przeszukanie binarne wymaga około log23000=12 operacji dostępu do bloku.
Załóżmy teraz, że pole klucza ma długość V=9 bajtów. Na tym polu utworzono indeks główny,
wskaźnik na blok ma 6 bajtów. Jaki zysk otrzymamy stosując indeks?
Rozmiar każdego wpisu indeksu wynosi 9+6=15, zatem w jednym bloku mieści się 1024/15=68
wpisów. Całkowita liczba wpisów indeksu jest równa liczbie bloków pliku z danymi, a więc 3000.
Potrzebujemy zatem 3000/68=45 bloków. Przeszukiwanie binarne na pliku indeksu wymaga
zatem log245=6 dostępów do bloków. Plus 1 dostęp do bloku danych, a więc 7 operacji dostępu.
Ala
Beata
Celina
Czesława
Danuta
Dorota
Franciszka
Grażyna
Helena
Irena
Joanna
Katarzyna
T. Pankowski/A. Stachowiak
• wartość pola klucza - K(i)
• wskaźnik na blok dyskowy zawierający rekord o tej wartości pola
klucza - P(i)
Indeks główny – zadanie 1
Imię
(pole klucza głównego)
Wartość klucza Wskaźnik P(i)
głównego K(i) na blok
• Indeks główny to plik uporządkowany z rekordami o stałej
długości posiadającymi dwa pola zawierające:
23
T. Pankowski/A. Stachowiak
24
Indeks grupujący
Indeks grupujący – przykład
Plik indeksu
• Jeżeli rekordy pliku są fizycznie posortowane według
pola nie będącego polem klucza (a więc takiego, dla
którego wartości mogą się powtarzać), to pole to
określa się mianem pola klastrowania i można
utworzyć na nim indeks grupujący (klastrowania).
• Indeks ten również składa się z dwóch pól:
Numer
(pole klastrowania)
1
1
1
2
1
3
...
1
2
2
3
Wskaźnik na blok, indeksu
zgrupowanego, zawiera adres bloku
danych, w którym znajduje się
pierwszy rekord danych z wartością
atrybutu indeksowego równą
wartości pola grupowania
25
Indeks drugorzędny
3
3
3
3
T. Pankowski/A. Stachowiak
26
Indeks drugorzędny – przykład
Plik indeksu
Plik danych
Numer
(pole klucza drugorzędnego)
• Indeks drugorzędny (wtórny) jest dodatkowym
mechanizmem, i tworzony jest na polu, które nie
porządkuje danych, i które może mieć zarówno wartości
unikalne jak i powtarzające się
• Sam indeks posiada wartości uporządkowane o dwóch
polach:
Wartość pola
indeksu K(i)
1
2
3
4
5
• wartość pola indeksującego
• wskaźnik na rekord lub na blok (różne wersje)
T. Pankowski/A. Stachowiak
1
Wartość pola Wskaźnik P(i)
klastrowania K(i)na blok
• wartość pola klastrowania (po jednym wpisie dla każdej
odrębnej wartości)
• wskaźnik na pierwszy blok w pliku danych zawierający
rekord o danej wartości pola klastrowania
T. Pankowski/A. Stachowiak
Plik danych
6
7
8
9
10
27
Wskaźnik P(i)
na blok
11
2
3
12
5
4
10
6
Każdy rekord
pliku danych
posiada swój
odpowiednik w
rekordzie
indeksu. Stąd,
indeks wtórny
jest indeksem
gęstym.
7
1
8
9
T. Pankowski/A. Stachowiak
28
Indeks drugorzędny – zadanie 2
Indeks drugorzędny dla pola o
powtarzających się wartościach – przykład
Plik indeksu
Rozważmy przykład jak w zadaniu 1.
Plik posiada 3000 bloków o rozmiarze 1024, gdzie wpisano 30 000 rekordów o rozmiarze 100
bajtów każdy.
Ponieważ pole, po którym wyszukujemy nie jest uporządkowane, aby znaleźć żądaną wartość
należy przeprowadzić wyszukiwanie liniowe, a więc wykonać średnio 3000/2 = 1500 operacji
dostępu do bloków.
Załóżmy, że skonstruowaliśmy indeks drugorzędny.
Podobnie jak w zadaniu 1 każdy wpis ma rozmiar 9+6=15 bajtów, a zatem w jednym bloku
znajduje się 1024/15=68 wpisów.
Całkowita liczba wpisów w indeksie jest równa liczbie rekordów w pliku danych (wartości pola są
unikalne), a więc wynosi 30 000. Indeks musi zatem zajmować 30 000/68=442 bloki.
Przeszukanie binarne takiego pliku wymaga log2442=9 operacji dostępu do bloku, plus 1
operacja dostępu do bloku danych, a więc w sumie 10 operacji, co jest znacznym
usprawnieniem.
Wartość pola
indeksu K(i)
Wskaźnik P(i)
na blok
Blok wskaźników
na rekordy
Plik danych
Numer
(pole indeksowania)
1
1
1
2
2
5
3
4
2
5
3
2
5
3
4
1
4
T. Pankowski/A. Stachowiak
29
Indeksy wielopoziomowe
30
Indeks wielopoziomowy – zadanie 3
• Indeks wielopoziomowy powstaje na bazie uporządkowanego pliku
indeksu o odrębnych wartościach dla każdego K(i), który teraz określany
jest jako zerowy (lub podstawowy) poziom indeksu. Dla niego tworzony
jest indeks główny, który staje się pierwszym poziomem indeksu.
Proces ten jest powtarzany do momentu, aż wszystkie wpisy indeksu na
pewnym poziomie t zmieszczą się w jednym bloku. Ten poziom określa
się jako szczytowy.
• Wysokość t = logf0r, gdzie r jest liczbą wpisów na poziomie
podstawowym, a f0 – liczbą wpisów na jeden blok.
Wyszukiwanie w takim pliku jest szybsze niż wyszukiwanie binarne, jeżeli
f0>2.
T. Pankowski/A. Stachowiak
T. Pankowski/A. Stachowiak
31
Załóżmy, że zagęszczony indeks drugorzędny z zadania 2 został zamieniony
na indeks wielopoziomowy.
Obliczyliśmy współczynnik blokowy pliku f0= 68 wpisów na blok i stanowi on
jednocześnie obciążenie wyjściowe dla indeksu wielopoziomowego. Liczba
bloków na poziomie podstawowym wynosiła r=442. Liczba bloków poziomu
pierwszego wyniesie zatem 442/68=7 bloków, a na poziomie drugim: 7/68=1
blok. Zatem t=log68442= 2.
W celu uzyskania dostępu do żądanego rekordu poprzez przeszukanie
indeksu wielopoziomowego musimy uzyskać dostęp do jednego bloku na
każdym poziomie oraz jednego bloku w pliku danych, a więc 3+1=4 operacje
dostępu do bloków.
T. Pankowski/A. Stachowiak
32
Drzewa
Drzewa wyszukiwania
Drzewo – oznacza w teorii grafów graf, który jest acykliczny i spójny.
korzeń
Węzeł
wewnętrzny
Poziom 0
0
1
2
4
5
Poziom 1
3
6
7
• Drzewo wyszukiwania stanowi specjalny rodzaj drzewa używanego w celu
sterowania procesem wyszukiwania rekordu. Indeksy wielopoziomowe
można postrzegać jako odmianę drzew wyszukiwania.
• Drzewo wyszukiwania rzędu p jest takim drzewem, że każdy
wierzchołek zawiera co najwyżej p-1 wartości wyszukiwania oraz p
wskaźników na poddrzewo:
<P1, K1, P2, K2, ..., Pq-1, Kq-1, Pq>, gdzie q ≤ p.
• Każde Pi oznacza wskaźnik na poddrzewo (lub wsk.pusty), a każde Ki jest
wartością wyszukiwania z uporządkowanego zbioru wartości (zakłada się,
że wartości są unikatowe)
8
Poziom 2
liść
poddrzewo
Drzewo
T. Pankowski/A. Stachowiak
33
Drzewa wyszukiwania (c.d.)
X < K1
K1
...
Ki-1
Pi
Ki
...
Kq-1
Ki-1 < X < Ki
T. Pankowski/A. Stachowiak
Pq
34
B-drzewa
• w każdym wierzchołku K1 < K2 < ... < Kq-1
• dla wszystkich wartości X w poddrzewie, na które wskazuje
wskaźnik Pi zachodzi: Ki-1 < X < Ki
P1
T. Pankowski/A. Stachowiak
...
Kq-1 < X
35
• B-drzewo (perfectly balanced multiway tree) jest drzewem wyszukiwania o
dodatkowych ograniczeniach, które zapewniają, że jest ono zawsze w
pełni zrównoważone oraz stopień wypełnienia nie jest nigdy zbyt mały.
(Komplikuje to oczywiście algorytmy wstawiania i usuwania, ale
przyspiesza wyszukiwanie)
• Stanowi ono podstawę implementacji indeksów w SZBD.
• Drzewo skierowane T nazywamy B-drzewem klasy t(h, m), jeśli h = 0
(drzewo puste) lub
· Wszystkie drogi od korzenia do liści są długości h,
· Każdy wierzchołek z wyjątkiem korzenia ma, co najmniej m kluczy
(elementów) i m+1 synów,
· Każdy wierzchołek ma, co najwyżej 2m kluczy (i 2m+1 synów),
· Korzeń ma, co najmniej jeden klucz.
T. Pankowski/A. Stachowiak
36
B-drzewa (c.d.)
B-drzewa (c.d.)
• Struktura strony (wierzchołka) w B-drzewie:
<P0, <K1, Pr1>, P1, <K2, Pr2>, ... , <Kq, Prq>, Pq>
gdzie Ki jest wartością wyszukiwania, Pi jest wskaźnikiem poddrzewa, a Pri
jest wskaźnikiem danych, 1≤
≤ q ≤ 2m dla korzenia, i m ≤ q ≤ 2m dla
wierzchołków i liści.
W każdym wierzchołku K1 < ... < Kq, Pi - wskaźnik na wierzchołek będący
synem lub NIL, <Ki, Pri> indeks.
Ki-1 | Pi-1 | Ki | Pi | Ki+1
Ki-1 < X < Ki
Ki < X < Ki+1
schemat uporządkowania
kluczy.
T. Pankowski/A. Stachowiak
37
B-drzewa (c.d.)
Operacje na indeksie zorganizowanym według
struktury B-drzewa
Dołączanie
• metoda podziału
• metoda kompensacji
Chcemy dołączyć element indeksu o kluczu X, tak aby nie
naruszyć struktury B-drzewa. Dołączanie poprzedzone jest
procedurą SZUKAJ, w wyniku której, albo znajdziemy
wierzchołek zawierający klucz X (koniec) albo znajdziemy
adres wierzchołka (liścia) do którego należy dołączyć klucz
X. Jeśli wierzchołek ma mniej niż 2m elementów to
dołączamy nowy klucz. Jeśli wierzchołek ma 2m
elementów to następuje tzw. kolizja (przepełnienie,
nadmiar), którą rozwiązujemy albo metodą podziału
albo metodą kompensacji.
T. Pankowski/A. Stachowiak 38
B-drzewa (c.d.)
Likwidacja nadmiaru
a) metoda podziału 2m+1 elementów dzielimy na trzy części.
Elementy 1, ... , m umieszczamy w wierzchołku 1
Element m+1 przenosimy do strony ojca
Elementy m+2, ... , 2m+1 umieszczamy w wierzchołku 2 (rys.1)
Przykład 1. m = 2
b) metoda kompensacji: można ją stosować jeżeli sąsiednia
strona zawiera j<2m elementów.
Stan wyjściowy – rys 1, j=2<2m, obliczamy i=entier((2m+j+3)/2) =
((4+2+3)/2) = 4.
Elementy 1, 2, ..., i-1
umieszczamy w wierzchołku1,
Element i
przenosimy do strony ojca,
Elementy i+1, ..., 2m+j+2 umieszczamy w wierzchołku 2 (rys.2).
Rys.2
Jeżeli w stronie ojca nastąpi przepełnienie to algorytm
powtarzamy – może wzrosnąć wysokość drzewa.
39
T. Pankowski/A. Stachowiak
40
B-drzewa (c.d.)
B-drzewa (c.d.)
Usuwanie • metoda łączenia
• metoda kompensacji
Chcemy usunąć element o kluczu X. Usuwanie podobnie jak
dołączanie poprzedzone jest algorytmem wyszukiwania. Procedura
SZUKAJ powinna zakończyć się powodzeniem i zwrócić adres (s)
wierzchołka (strony) zawierającej klucz X.
•
Jeśli strona jest liściem to usuwamy indeks o kluczu X. Może
wówczas wystąpić niedomiar, który usuwamy metodą łączenia lub
kompensacji.
•
Jeżeli strona nie jest liściem to przeglądamy poddrzewo
wskazywane przez prawy wskaźnik stojący za kluczem X i szukamy
najmniejszego indeksu (L) - idziemy ścieżką wskazywaną przez P0,
aż dojdziemy do liścia (rys.3). Ten najmniejszy element wstawiamy w
miejsce (X, Pr) a następnie usuwamy go z liścia – może wystąpić
niedomiar.
T. Pankowski/A. Stachowiak
Rys.3
Likwidacja niedomiaru
a) metoda kompensacji: jeżeli dla każdej ze stron sąsiednich j+k ≥
2m to stosujemy metodę kompensacji analogicznie jak przy dołączaniu
(j, k – ilość elementów na sąsiednich stronach).
41
T. Pankowski/A. Stachowiak
B-drzewa (c.d.)
42
B-drzewa -przykład
b) metoda łączenia stosujemy ją jeśli strona s zawiera j<m
elementów, a jedna ze stron sąsiednich (s1) zawiera k elementów , przy
czym j+k<2m (rys.4).
Dane jest B-drzewo klasy t(h, 2) (rys.5):
a) wstaw obiekt o kluczu 32 (metodą podziału i metodą
kompensacji),
b)usuń obiekt o kluczu 46 (metodą łączenia i metodą kompensacji).
Rys.4
Rys.5
Na stronie s2 może wystąpić niedomiar, trzeba wówczas operację
łączenia powtórzyć, lub jeśli s2 jest korzeniem to przekazujemy go do
puli stron pustych (drzewo zmniejszy wysokość).
T. Pankowski/A. Stachowiak
43
T. Pankowski/A. Stachowiak
44
B+-drzewa
B-drzewa - zadania
1. Oblicz liczbę wierzchołków Wmin i Wmax B-drzewa klasy T(3, 2)
odpowiednio przy minimalnym i maksymalnym wypełnieniu drzewa.
2. Oblicz maksymalne (minimalne) zużycie pamięci konieczne na
zapamiętanie indeksu w postaci B-drzewa, przy następujących danych:
Struktura B+-drzewa jest taka sama jak B-drzewa (zrównoważone,
wielodrogowe drzewo wyszukiwań).
N = 100 000 - liczba rekordów w pliku głównym,
B = 1kB
- wielkość bloku,
P = 4B
- wielkość pola wskaźnikowego,
A = 8B
- wielkość pola adresowego,
X = 20B
- wielkość pola klucza indeksowania.
W większości komercyjnych systemów baz danych do tworzenia
indeksów wykorzystywana jest pewna modyfikacja B-drzew o
nazwie B+-drzewa.
Różnica polega na tym, że wszystkie indeksowane dane i
wskaźniki do rekordy danych przechowywane są w liściach.
Wierzchołki pośrednie służą tylko do wyszukiwania danych.
Jaka jest wówczas wysokość B-drzewa?
T. Pankowski/A. Stachowiak
45
B+-drzewa (c.d.)
•
•
•
•
5
7
3
1
3
5
6
7
Liście implementowane są zwykle jako lista.
8
8
46
Indeksy w SQL Server
W przypadku B+-drzewa wskaźniki danych są przechowywane tylko w liściach
Wierzchołki liści posiadają wpis (indeks ze wskaźnikiem do rekordu danych) dla
każdej wartości pola wyszukiwania
Niektóre wartości pola wyszukiwania są powtarzane w wierzchołkach wewnętrznych i
służą do wspomagania wyszukiwania
Koszt wyszukiwania – wysokość drzewa + 1.
wskaźnik
danych
T. Pankowski/A. Stachowiak
B+-drzewo dla
kluczy: 8, 5, 1,
7, 3, 12, 9, 6
(m=1)
9
1
2
T. Pankowski/A. Stachowiak
47
• SQL Server pozwala na tworzenie indeksów grupujących
(klastrujących, ang. Clustered) i niegrupujących (ang.
NonClustered)
• Indeksy tworzone są w oparciu o strukturę B+-drzewa
• SQL Server po utworzeniu indeksu grupującego reorganizuje
wszystkie strony tabeli, zapisując wiersze w kolejności
określonej przez indeks grupujący (krotki są fizycznie
posortowane według atrybutu indeksującego)
• Na danej tabeli może zatem istnieć tylko jeden indeks
grupujący
• Domyślnie na kluczu podstawowym jest tworzony indeks
grupujący
T. Pankowski/A. Stachowiak
48
Tworzenie indeksu
Indeksy w SQL Server (c.d.)
• Indeks niegrupujący nie wpływa w żaden
sposób na uporządkowanie stron tabeli
(uporządkowane są jedynie pozycje samego
indeksu). Można ich utworzyć 249.
• Indeksy niegrupujące używają indeksu grupującego
do pobierania żądanych danych z tabeli, dlatego
indeks grupujący należy utworzyć przed
utworzeniem indeksów niegrupujących.
T. Pankowski/A. Stachowiak
49
Tworzenie indeksu (c.d.)
•
•
•
•
•
•
PAD_INDEX oznacza utworzenie indeksu, którego wierzchołki wewnętrzne zostaną
zapisane na stronach niewypełnionych w 100%, dzięki czemu dodanie lub
modyfikacja danych nie będą oznaczały konieczności przebudowy tego indeksu.
Wykorzystywany w połączeniu z FILLFACTOR. Domyślnie SQL Server na każdej
stronie indeksu zostawi tyle wolnej przestrzeni, ile wystarcza do zapisania dwóch
dodatkowych wierszy indeksu,
FILLFACTOR określa procent wolnej przestrzeni na stronach przechowujących
wartości liści indeksu,
IGNORE_DUP_KEY (opcja dostępna tylko dla indeksów unikatowych) sprawi, że
próba wstawienia wartości już istniejącej w jednej z kolumn indeksu unikatowego
spowoduje wyświetlenie ostrzeżenia, a naruszająca warunki integralności instrukcja
zostanie zignorowana, ale transakcja będzie kontynuowana. Brak tego parametru
powoduje przy próbie wstawienia istniejących wartości wyświetlenie komunikatu
błędu i wycofanie całej transakcji,
DROP_EXISTING zastępuje istniejący indeks nowym o tej samej nazwie,
STATISTICS_NORECOMPUTE wyłącza automatyczne aktualizowanie statystyk dla
indeksu,
SORT_IN_TEMPDB powoduje, że wewnętrzne operacje sortowania danych indeksu
będą przeprowadzane w bazie tempdb. Jeżeli baza tempdb znajduje się na
osobnym, szybkim dysku, może to spowodować skrócenie czasu sortowania danych
51
T. Pankowski/A. Stachowiak
indeksu.
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX
indeks
ON {tabela (kolumna [ASC | DESC] [,…n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = współczynnik_wypełnienia]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
[[,] SORT_IN_TEMPDB]
]
[ON grupa_plików]
T. Pankowski/A. Stachowiak
50
Optymalizacja zapytań
• Zapytania przed skompilowaniem i wykonaniem są
optymalizowane przez wewnętrzny proces SQL Servera o nazwie
Query Optimizer.
• Jego zadaniem jest znalezienie najtańszego sposobu wykonania
instrukcji.
• Query Optimizer bazuje na liczbie operacji wejścia-wyjścia oraz
na liczbie obliczeń dokonanych przez procesor niezbędnych do
wykonania instrukcji.
• Query Optimizer szacuje koszt operacji wejścia-wyjścia
na podstawie:
• struktury tabel i związanych z nimi indeksów
• kosztu operacji złączenia
• istniejących lub tworzonych dynamicznie statystyk
opisujących dane źródłowe
• metainformacji opisujących fizyczną strukturę plików,
w których zapisane są żądane dane.
T. Pankowski/A. Stachowiak
52
Optymalizacja zapytań (c.d.)
Optymalizacja zapytań (c.d.)
Optymalizacja instrukcji języka T-SQL przebiega następująco:
• Sprawdzana jest poprawność syntaktyczna i semantyczna (parse),
instrukcja zostanie „podzielona” na znaczniki interpretowane przez SQL
Server.
• standaryzacja — zapisanie znaczników instrukcji Transact-SQL w
jednoznacznej postaci (np. ujednolicenie aliasów)
• optymalizacja — wybór jednego z przygotowanych planów wykonania. Na
tym etapie następuje analiza indeksów i statystyk oraz metod złączania
danych. Etap można podzielić na trzy fazy:
• Analiza zapytania — wybór metod wyszukiwania i łączenia danych
źródłowych
• Wybór indeksów
• Wybór metody złączania tabel
• Instrukcja wykonana według opracowanego optymalnego planu zostaje
skompilowana.
• Query Optimizer wybiera optymalny sposób pobrania wybranych
(wynikowych) danych. Na przykład: czy odwołać się do indeksu, czy
T. Pankowski/A. Stachowiak
53
odczytać tabelę
Optymalizacja zapytań (c.d.)
T. Pankowski/A. Stachowiak
54
Optymalizacja zapytań (c.d.)
Plan wykonania instrukcji oraz wartości pewnych statystyk
można poznać:
• odczytując zawartość tabeli systemowej SYSINDEXES (nie
jest zalecane)
• wyświetlając plan wykonania włączając opcje: SET
SHOWPLAN TEXT lub SET SHOWPLAN_ALL
• wyświetlając statystyki związane z czasem -SET
STATISTICS TIME ON
• wyświetlając statystyki związane liczbą operacji we/wy SET STATISTICS IO ON
• korzystając z przedstawienia graficznego, które zawiera
informacje o kolejności wykonywania instrukcji, logicznych
operatorach algebry zbiorów użytych podczas wykonywania
instrukcji oraz fizycznej implementacji tych operatorów
T. Pankowski/A. Stachowiak
• Skompilowana według optymalnego planu instrukcja języka
Transact-SQL zostaje zapisana w buforze procedury
• Ponowna kompilacja instrukcji jest przeprowadzana, jeżeli:
• Zmieniona została definicja obiektu, do którego instrukcja się
odwołuje (wykonano polecenie ALTER).
• Wymuszono aktualizacje statystyk, na podstawie których
przygotowany został plan wykonania instrukcji (wykonano
instrukcję UPDATE STATISTIC).
• Usunięto indeks wykorzystywany przez instrukcję (wykonano
instrukcję DROP INDEX).
• Z tabeli źródłowej usunięto lub dodano do niej dużą (względem
stanu w momencie optymalizacji instrukcji) liczbę wierszy.
• Wymuszono rekompilację instrukcji (wywołano procedurę
systemową sp_recompile).
55
Informacje związane z liczbą operacji we-wy
• scan count – liczba odwołań do tabeli źródłowej
• logical reads – liczba odczytanych z pamięci podręcznej
stron danych
• physical reads – liczba stron danych odczytanych z dysku
• read-ahead reads – liczba stron umieszczonych w pamięci
podręcznej
Współczynnik trafień=(logical reads - physical reads
)/ logical reads
physical reads<= logical reads
T. Pankowski/A. Stachowiak
56
Lista operatorów fizycznych
Optymalizacja zapytań (c.d.)
Plan wykonania może być przedstawiony w
postaci graficznej i zawiera informacje o:
• Krokach wykonania zadania i ich kolejności.
• Logicznych operatorach algebry relacji,
które były wykorzystane podczas wykonania
zadnia.
• Fizycznej implementacji tych operatorów
użytej do wykonania zadania
T. Pankowski/A. Stachowiak
57
Plan wykonania
T. Pankowski/A. Stachowiak
58
Plan wykonania (c.d.)
Ustawiając kursor na symbolu operatora fizycznego uzyskamy dodatkowe informacje
o danej operacji:
select nazwa from dzialy2
where nazwa = 'ALGORYTMY'
argumenty wywołania operacji (ang. Argument),
koszt wykonania operacji i jego szacunkowy udział w koszcie wykonania
instrukcji (ang. Cost),
koszt wykonania operacji i operacji przez nią wywołanych (ang. Subtree cost),
create index ix_nazwa
on dzialy2(nazwa)
liczbę wykonania operacji w ramach instrukcji (ang. Number of executes),
liczbę zwróconych przez operacje wierszy (ang. Row count),
select nazwa from dzialy2
where nazwa = 'ALGORYTMY'
szacunkową wielkość zwróconych przez operacje wierszy (ang. Estimated row
size),
szacunkowy koszt operacji we-wy (ang. I/O cost),
szacunkowy koszt wykorzystania zasobów procesora przez (ang. CPU cost).
T. Pankowski/A. Stachowiak
59
T. Pankowski/A. Stachowiak
60
Optymalizacja zapytań (c.d.)
Optymalizacja zapytań (c.d.)
Optymalna pod względem szybkości odczytu danych jest sytuacja, w której
wszystkie żądane dane (wyrażenia wymienione w instrukcji ) mogą zostać
odczytane z indeksu. Mówimy wtedy, że indeks zawiera zapytanie.
Szczególną grupę stanowią zapytania z operatorami z grupy SARG (ang.
Search ARGuments). Nazwa ta określa pewien specjalny podzbiór
argumentów wyszukiwania (wymienionych w klauzuli WHERE instrukcji
SELECT. Argumenty te charakteryzuje:
Obecność stałej, której wartość jest porównywana z polami wybranej
kolumny tabeli źródłowej.
Wyszukiwanie wartości równych wzorcowi, należących do zakresu
wyznaczonego przez wzorzec lub przez połączenie kilku argumentów SARG
za pomocą operatora koniunkcji.
T. Pankowski/A. Stachowiak
61
Optymalizacja zapytań (c.d.)
• Wykonanie zapytania zawierającego argument typu
SARG-( =, <, <=, >, >=, BETWEEN oraz, w
pewnych przypadkach, LIKE – np. gdy znak % jest na
końcu, pozwala to ograniczyć liczbę przeszukiwanych wierszy)
przebiega według następującego schematu:
• optymalizator sprawdza, czy istnieją przydatne do
wykonania zapytania indeksy
• jeżeli takie indeksy istnieją, rozpoczyna się wyszukiwanie
(za pomocą operatora >=) stron indeksu przechowujących
żądane dane
• wszystkie wartości spełniające zadane kryteria są
odczytywane, a jeśli jest to konieczne, z tabeli
odczytywane są pozostałe pola danego wiersza.
T. Pankowski/A. Stachowiak
62
Optymalizacja zapytań (c.d.)
• Optymalizacja złączeń tabel:
W przypadku użycia operatora <>, NOT, !=, !<, !>, NOT EXISTS ,
NOT IN czy NOT LIKE konieczne okazuje się sprawdzenie
wartości wszystkich wierszy tabeli źródłowej.
Chociaż nie oznacza to, że SQL Server nie potrafi skorzystać z
indeksów przy tworzeniu planu zapytania zawierającego wyżej
wymienione operatory, to należy dążyć do zastąpienia takich
operatorów argumentami SARG.
T. Pankowski/A. Stachowiak
• Jeżeli zapytanie zawiera klauzulę WHERE, optymalizator
może zadecydować o wybraniu wierszy spełniających
podane kryteria, zanim wykona operację złączenia. W
ten sposób wielokrotnie zmniejsza się liczba wierszy,
które będą łączone
• Metody złączeń:
• Hash Match – złączenie mieszające
• Nested Loop – złączenie pętli zagnieżdżonych
• Merge Join – złączenie sortująco-scalające
63
T. Pankowski/A. Stachowiak
64
Optymalizacja zapytań (c.d.)
Optymalizacja zapytań (c.d.)
Hash Match
• Tego typu połączenie przeprowadzane jest, jeżeli
optymalizator nie może znaleźć użytecznych dla
złączenia indeksów.
• Dane zawarte w tabelach źródłowych dzielone są
na grupy według wartości funkcji skrótu (funkcji
hash-ującej) obliczonej dla kolejnych wierszy, tzn.
dane, dla których wartość tej funkcji jest taka
sama trafiają do tej samej grupy. Wystarczy więc,
że SQL Server porówna dane z tych samych grup.
T. Pankowski/A. Stachowiak
select *
from dzialy2 d, pracownicy2 p
where p.id_dzialu = d.id_dzialu
65
Optymalizacja zapytań (c.d.)
T. Pankowski/A. Stachowiak
66
Optymalizacja zapytań (c.d.)
Nested Loop
• Złączenie tego typu polega na porównaniu przez SQL
Server każdego wiersza z wewnętrznej tabeli
złączenia (decyzja o tym, która tabela zostanie
uznana za wewnętrzną jest podejmowana przez
usługę Query Optimizer na podstawie liczby wierszy,
ich unikalności i ziarnistości) z kolejnymi wierszami
zewnętrznej tabeli złączenia.
• Stosowne jest kiedy w tabeli wewnętrznej na
kolumnie połączeniowej założony jest indeks.
• Jeżeli obie mają indeksy to mniejsza z tabel
preferowana jest jako tabela zewnętrzna
T. Pankowski/A. Stachowiak
Hash Match
67
Nested Loop
create index ix_id_dz
on dzialy2(id_dzialu)
select *
from dzialy2 d, pracownicy2 p
where p.id_dzialu = d.id_dzialu
tabela wewnętrzna
T. Pankowski/A. Stachowiak
68
Optymalizacja zapytań (c.d.)
Optymalizacja zapytań (c.d.)
Merge Join
• Złączenie poprzez łączenie (merge) wybierane jest
wtedy, gdy obie tabele są posortowane według
kolumny złączenia (utworzony jest na nich indeks
grupujący).
Ponadto przynajmniej jedna z tych kolumn
powinna zawierać wartości niepowtarzalne (indeks
zdefiniowany jest jako unikalny)
Nested Loop
create index ix_id_dz
on pracownicy2(id_dzialu)
select *
from dzialy2 d, pracownicy2 p
where p.id_dzialu = d.id_dzialu
tabela wewnętrzna
T. Pankowski/A. Stachowiak
69
Kiedy tworzyć indeksy
70
Statystyki
• tworzyć na kolumnach często używanych w
klauzulach WHERE, ORDER BY, GROUP BY
• tworzyć raczej na kolumnach typu liczbowego
• nie tworzyć za dużo indeksów, usuwać
nieużywane
• tworzyć na kolumnach i dużej selektywności (tzn.
np. nie na kolumnie ‘płeć’) i raczej nie tworzyć na
kolumnach typu IDENTITY
• tworzyć indeks grupujący na każdej tabeli
• tworzyć indeksy na kolumnach połączeniowych
T. Pankowski/A. Stachowiak
T. Pankowski/A. Stachowiak
71
• Statystyki przechowują (w kolumnie statblob typu image tabeli
sysindexes) określoną próbkę danych zapisanych w
poindeksowanych kolumnach. Informacja ta służy procesowi
optymalizatora do wyboru najlepszego planu wykonania
zapytania.
• SQL Server umożliwia również utworzenie statystyk dla kolumn
niepowiązanych z żadnym indeksem. Jeżeli włączona jest opcja
auto create statistics, SQL automatycznie utworzy statystyki
dla kolumn, do których nastąpiło odwołanie w klauzuli WHERE.
• Możliwe jest samodzielne utworzenie statystyki dla wybranych
kolumn (polecenie CREATE STATISTICS ), ręczna aktualizacja
wybranych statystyk (polecenie UPDATE STATISTICS), oraz
usunięcie nieużywanych statystyk (DROP STATISTICS )
T. Pankowski/A. Stachowiak
72
Pytania kontrolne
Pytania kontrolne (c.d.)
1. Indeksy: rodzaje indeksów, zastosowania, zalety i wady indeksów, kiedy
tworzymy indeksy.
2. B- drzewa: budowa, operacje wstawiania i usuwania elementów,
obliczenia (wysokość B-drzewa, ilość wierzchołków, itp.), (przykłady z
wykładu).
3. Strona B-drzewa ma wielkość 1KB, pole klucza ma długość 16B,
pole wskaźnika na blok ma długość 4B, pole wskaźnika na rekord ma
długość 8B. Ile indeksów mieści się na trzecim poziomie tego drzewa
przy minimalnym wypełnieniu.
3. Różnice między B-drzewem a B+-drzewem.
4. Cele i etapy optymalizacji zapytań, jakie elementy wpływają na koszt
wykonania zapytania.
ZADANIA:
1. Jaką wysokość musi mieć B-drzewo klasy t(h, 5), aby przy
maksymalnym wypełnieniu zaindeksować plik liczący 14 500 rekordów?
T. Pankowski/A. Stachowiak
2. Dany jest indeks zorganizowany jako B-drzewo klasy t(3,10). Jaka
jest maksymalna liczba rekordów bazy danych, którą można
zaindeksować przy użyciu tego B-drzewa?
73
Uwaga: trzeba rozpocząć od obliczenia współczynnika wypełnienia
(m) B-drzewa.
T. Pankowski/A. Stachowiak
74