Indeksowanie: BIndeksowanie: B--drzewa drzewa Fizyczna...

Indeksowanie: BIndeksowanie: B--drzewadrzewa

1

Tadeusz Pankowskiwww.put.poznan.pl/~tadeusz.pankowski

T. Pankowski/A. Stachowiak

Fizyczna struktura bazy danych

2

Fizyczna struktura bazy danych

Indeksy

Optymalizacja


Fizyczna struktura bazy danychFizyczna struktura bazy danych

• 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

3

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 najsprawniejzlokalizowane na dysku, skopiowane do pamięci głównej, a następnie, po przetworzeniu, z powrotem zapisane na dysku


Fizyczna struktura bazy danych (c.d.)Fizyczna struktura bazy danych (c.d.)

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,

4

� odporność pamięci zewnętrznej na awarie,

� koszt jednostkowy.

Pamięć zewnętrzna ma organizację plikową, oznacza to, że jednostką alokacji na dysku jest plik.



• 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:

5

• 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



• 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

6

• 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ą



• 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

7

się reszta rekordu, jeżeli nie jest to następny blok w kolejności

• Organizacja niesegmentowana (unspanned):• nie zezwala, aby rozmiar rekordu był większy niż

rozmiar bloku



• 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

8

• 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

• Alokacja indeksowa:• bloki indeksu zawierają wskaźnik na faktyczne bloki pliku



• 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

9

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.



• 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

10

dostępu mogą być stosowane tylko dla określonej metody organizacji plików

• Techniki metod dostępu: • szeregowanie,

• mieszanie (hash-owanie),

• indeksowanie



• 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

11

ten sam zakres. Poszczególne wiersze tabel oraz dane indeksów wraz z metadanymi przechowywane są na stronach (ogranicza to ich rozmiar).



• Maksymalna liczba danych zapisanych na pojedynczej stronie wynosi 8060 bajtów

• Pojedynczy wiersz nie może zostać zapisany

12

• Pojedynczy wiersz nie może zostać zapisany na kilku stronach. Wynika z tego, że wielkość pojedynczego wiersza nie może przekroczyć 8060 bajtów



• 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.

13

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)


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]

14

[FOR LOAD | FOR ATTACH]

<plik> ::=([NAME = nazwa_logiczna,]FILENAME = 'nazwa_fizyczna'[, SIZE = wielko ść][, MAXSIZE = {maksymalna_wielko ść | UNLIMITED}][, FILEGROWTH = przyrost]) [,…n]

<grupa_plików> ::=FILEGROUP nazwa <plik> [,…n]

gdzie:



CREATE DATABASE wydawnictwoON PRIMARY(NAME = wydawnictwo_dane1,

FILENAME = 'C:\wydawnictwo\wydawnictwo_dane1.mdf',SIZE = 5MB,

15

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 ')



• Usuwanie bazy danych DROP DATABASE nazwaBD [, nazwaBD,...]

• Rozbudowa bazy danych ALTER DATABASE nazwaBD (...)

• sp_dboption – procedura pozwala wyświetlić lub zmienić

16

• 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]


IndeksyIndeksy

• 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,

17

• 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


Indeksy (c.d.)Indeksy (c.d.)

• 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

18

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



• 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

19

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



• 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:

20

• 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



• 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)

21

• Indeks rzadki, niezagęszczony (sparse index, nondense) – posiada wpisy tylko dla niektórych wartości wyszukiwania (np. indeks główny)


Indeks głównyIndeks główny

• Indeks główny to plik uporządkowany z rekordami o stałej długości posiadającymi dwa pola zawierające:• wartość pola klucza - K(i)

• wskaźnik na blok dyskowy zawierający rekord o tej wartości pola klucza - P(i)

22

klucza - P(i)

• 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)


Indeks główny Indeks główny –– przykładprzykład

Ala Celina

Beata

Ala

Plik indeksu Plik danych

Wartość klucza

głównego K(i)

Wskaźnik P(i)

na blok

Imię

(pole klucza głównego)

23

...

Helena

Danuta

AlaCzesława

Grażyna

Franciszka

Dorota

Danuta

Katarzyna

Joanna

Irena

Helena


Indeks główny Indeks główny –– zadanie 1zadanie 1

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.

24

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.


Indeks grupującyIndeks grupujący

• 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).

25

utworzyć na nim indeks grupujący (klastrowania).

• Indeks ten również składa się z dwóch pól:• 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


Indeks grupujący Indeks grupujący –– przykładprzykład

11

1

1

1


Wartość pola

klastrowania K(i)

Wskaźnik P(i)

na blok

Numer

(pole klastrowania)

26

...

3

2 1

3

2

2

1

3

3

3

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 grupowaniaT. Pankowski/A. Stachowiak

Indeks drugorzędnyIndeks drugorzędny

• 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ę

27

unikalne jak i powtarzające się

• Sam indeks posiada wartości uporządkowane o dwóch polach:• wartość pola indeksującego

• wskaźnik na rekord lub na blok (różne wersje)


Indeks drugorzędny Indeks drugorzędny –– przykładprzykład

2

112

3

2

11


Wartość pola

indeksu K(i)

Wskaźnik P(i)

na blok

Numer

(pole klucza drugorzędnego)Każdy rekord

pliku danych

posiada swój

odpowiednik w

rekordzie

indeksu. Stąd,

28

5

4

3

2

6

10

4

5

9

8

1

7

10

9

8

7

6

indeksu. Stąd,

indeks wtórny

jest indeksem

gęstym.


Indeks drugorzędny Indeks drugorzędny –– zadanie 2zadanie 2

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

29

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.


Indeks drugorzędny dla pola o Indeks drugorzędny dla pola o powtarzających się wartościach powtarzających się wartościach –– przykładprzykład

2

15

2

1

1


Wartość pola

indeksu K(i)

Wskaźnik P(i)

na blok

Numer

(pole indeksowania)

Blok wskaźników

na rekordy

30

5

4

3

2 5

5

2

3

2

4

1

4

3


Indeksy wielopoziomoweIndeksy wielopoziomowe

• 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

31

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.


Indeks wielopoziomowy Indeks wielopoziomowy –– zadanie 3zadanie 3

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

32

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.


DrzewaDrzewa

Drzewo – oznacza w teorii grafów graf, który jest acykliczny i spójny.

korzeńWęzeł

33

0

1 2 3

4 5 6 7 8

Poziom 0

Poziom 1

Poziom 2

poddrzewo

liść

korzeńWęzeł

wewnętrzny

DrzewoT. Pankowski/A. Stachowiak

Drzewa wyszukiwaniaDrzewa wyszukiwania

• 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

34

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)


Drzewa wyszukiwania (c.d.)Drzewa wyszukiwania (c.d.)

• 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

35

...PqKq-1...KiPiKi-1...K1P1

X < K1 Ki-1 < X < Ki Kq-1 < X


BB--drzewadrzewa

• 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.

36

• 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.


BB--drzewadrzewa (c.d.)(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 Prijest 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

37

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+1schemat uporządkowania

kluczy.


BB--drzewadrzewa (c.d.)(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

38

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

BB--drzewa (c.d.)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)

39

Elementy m+2, ... , 2m+1 umieszczamy w wierzchołku 2 (rys.1)

Przykład 1. m = 2

Jeżeli w stronie ojca nastąpi przepełnienie to algorytm

powtarzamy – może wzrosnąć wysokość drzewa.


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,

40

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



�� 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.

41

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.



Rys.3

42

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).T. Pankowski/A. Stachowiak


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).

Rys.4

43

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ść).

Rys.4


BB--drzewadrzewa --przykładprzykład

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).

44

Rys.5


BB--drzewa drzewa -- zadaniazadania

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:

45

zapamiętanie indeksu w postaci B-drzewa, przy następujących danych:

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.

Jaka jest wówczas wysokość B-drzewa?T. Pankowski/A. Stachowiak

BB++--drzewadrzewa

W większości komercyjnych systemów baz danych do tworzenia

indeksów wykorzystywana jest pewna modyfikacja B-drzew o

nazwie B+-drzewa.

46

Struktura B+-drzewa jest taka sama jak B-drzewa (zrównoważone,

wielodrogowe drzewo wyszukiwań).

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.


BB++--drzewa (c.d.)drzewa (c.d.)

• 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.

47

• Koszt wyszukiwania – wysokość drzewa + 1.

5

3 87

31 5 76 8 12

9

B+-drzewo dla

kluczy: 8, 5, 1,

7, 3, 12, 9, 6

(m=1)

wskaźnik

danych

Liście implementowane są zwykle jako lista. T. Pankowski/A. Stachowiak

Indeksy w SQL ServerIndeksy w SQL Server

• 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

48

• 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


Indeksy w SQL Server (c.d.)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.

49

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ć przedutworzeniem indeksów niegrupujących.


Tworzenie indeksuTworzenie indeksu

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX indeksON {tabela (kolumna [ASC | DESC] [,…n])[WITH

50

[WITH[PAD_INDEX][[,] FILLFACTOR = współczynnik_wypełnienia][[,] IGNORE_DUP_KEY][[,] DROP_EXISTING][[,] STATISTICS_NORECOMPUTE][[,] SORT_IN_TEMPDB]][ON grupa_plików]


Tworzenie indeksu (c.d.)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

51

• 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 indeksu. T. Pankowski/A. Stachowiak

Optymalizacja zapytań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

52

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

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)

53

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 odczytać tabelę 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).

54

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).



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

55

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



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

56

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



Plan wykonania może być przedstawiony w postaci graficznej i zawiera informacje o:

• Krokach wykonania zadania i ich kolejności.

57

• 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


Lista operatorów fizycznych

58T. Pankowski/A. Stachowiak

Plan wykonaniaPlan wykonania

select nazwa from dzialy2

where nazwa = 'ALGORYTMY'

59

create index ix_nazwa

on dzialy2(nazwa)

select nazwa from dzialy2

where nazwa = 'ALGORYTMY'


Plan wykonania (c.d.)Plan wykonania (c.d.)Ustawiając kursor na symbolu operatora fizycznego uzyskamy dodatkowe informacje

o danej operacji:

� argumenty wywołania operacji (ang. Argument),

� koszt wykonania operacji i jego szacunkowy udział w koszcie wykonania

instrukcji (ang. Cost),

60

� koszt wykonania operacji i operacji przez nią wywołanych (ang. Subtree cost),

� liczbę wykonania operacji w ramach instrukcji (ang. Number of executes),

� liczbę zwróconych przez operacje wierszy (ang. Row count),

� 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).



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

61

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.



• 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:

62

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.



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.

63

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.



• Optymalizacja złączeń tabel:• 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

64

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



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ą

65

• 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.



select *

from dzialy2 d, pracownicy2 p

where p.id_dzialu = d.id_dzialu

Hash Match

66




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,

67

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



Nested Loop

68

create index ix_id_dz

on dzialy2(id_dzialu)

select *



tabela wewnętrzna



Nested Loop

69

create index ix_id_dz

on pracownicy2(id_dzialu)

select *



tabela wewnętrzna



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).

70

grupujący). Ponadto przynajmniej jedna z tych kolumn powinna zawierać wartości niepowtarzalne (indeks zdefiniowany jest jako unikalny)


Kiedy tworzyć indeksyKiedy tworzyć indeksy

• 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

71

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


StatystykiStatystyki

• 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.

72

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 )


Pytania kontrolnePytania kontrolne

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).

73

wykładu).

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?


Pytania kontrolne Pytania kontrolne (c.d.)(c.d.)

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?

74

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.

Uwaga: trzeba rozpocząć od obliczenia współczynnika wypełnienia

(m) B-drzewa.


Indeksowanie: BIndeksowanie: B--drzewa drzewa Fizyczna...

Documents

Transcript of Indeksowanie: BIndeksowanie: B--drzewa drzewa Fizyczna...