Systemy zarządzania bazami danych

Systemy zarządzania bazami danych

6. Optymalizacja zapytań

--> Generowanie i wybór planu Zapytanie

Generuj Plany

Oczyść x x Oszacuj koszty

Koszty Wybierz

Optymalizacja zapytań

Wybierz Minimum

Generując plany weź pod uwagę:

• Przekształcenia zapytania w algebrze relacji (np. porządek złączeń)

• Użycie istniejących indeksów

• Zbudowanie nowych indeksów

• Sortowanie na użytek zapytania

Szczegóły implementacyjne

• Algorytmy złączania

• Zarządzanie pamięcią

• Przetwarzanie równoległe

Szacowanie kosztów we/wy

• Liczba bloków dyskowych, które trzeba przeczytać (i/lub zapisać) aby

zrealizować plan zapytania

– Zapisy potrzebne np. do realizacji

wielofazowego sortowania zewnętrznego

Szacowanie wymaga parametrów

B(R) = liczba bloków z krotkami relacji R f(R) = liczba krotek R w jednym bloku M = liczba dostępnych ramek pamięci HT(i) = liczba poziomów indeksu i

LB(i) = liczba bloków z liśćmi indeksu i

• Jakich operacji są to koszty?

Indeks pogrupowany

• Indeks, w którym pozycje indeksu są w takim samym fizycznym porządku jak rekordy

na Aind.

10 15 17 19 35 A

Różne znaczenia pogrupowania

• Grono (cluster)

…..

• Relacja pogrupowana (clustered)

…..

• Indeks pogrupowany (clustering)

R1 R2 S1 S2 R3 R4 S3 S4

R1 R2 R3 R4 R5 R5 R7 R8

R1 ⋈ ^R2 po wspólnym atrybucie C

T(R1) = 10.000 T(R2) = 5.000

S(R1) = S(R2) = 1/10 bloku

Dostępna pamięć = 101 bloków Miara: liczba operacji we/wy

Ostrożnie

• To może nie być najlepszy sposób szacowania:

– Ignorujemy koszt przetwarzania przez procesor

– Ignorujemy synchronizację

– Ignorujemy możliwości równoległej pracy procesora i dysku (np. DMA)

Możliwości

• Transformacje: R1 ⋈ R2, R2 ⋈ R1

– Która relacja prowadząca?

– Jaka kolejność złączeń?

• Algorytmy złączenia:

– Iteracyjne (nested loops)

– Przez scalanie (sort-merge join)

– Iteracyjne z indeksem (index nested loops)

Złączenie iteracyjne

for each r  R1 do

for each s  R2 do

if r.C = s.C then output <r,s>

Przez scalanie (przykład)

i R1{i}.C R2{j}.C j

1 10 5 1 2 20 20 2 3 20 20 3 4 30 30 4 5 40 30 5 50 6 52 7

Przez scalanie

(1) Jeśli R1 i R2 nie posortowane po C, posortuj je (2) i  1; j  1;

while (i  T(R1))  (j  T(R2)) do

if R1{ i }.C = R2{ j }.C then outputTuples else if R1{ i }.C > R2{ j }.C then j  j+1 else if R1{ i }.C < R2{ j }.C then i  i+1

Procedura outputTuples

while (R1{ i }.C = R2{ j }.C)  (i  T(R1)) do [ jj  j;

while (R1{ i }.C = R2{ jj }.C)  (jj  T(R2)) do [ output <R1{ i }, R2{ jj }>;

jj  jj+1 ] i  i+1 ]

for each r  R1 do

[ X  index (R2, C, r.C) for each s  ^{X do}

output <r,s> ]

Zakładamy istnienie indeksu

na R2.C

X  index(R, A, W)

wówczas: X = zbiór krotek R o atrybucie A równym W

Iteracyjne z indeksem

– Funkcja haszująca h, przeciwdziedzina 0  k – Kubełki dla R1: G0, G1, ... Gk

– Kubełki dla R2: H0, H1, ... Hk

(1) Rozrzuć krotki R1 do kubełków G (hasz na C) (2) Rozrzuć krotki R2 do kubełków H (hasz na C) (3) Dla każdego i = 0, 1, 2,..., k

dopasuj krotki z kubełków G_i i H_i

Haszowane

Prosty przykład: parzyste/nieparzyste

R1 R2 Kubełki

2 5 Parzyste:

4 4 R1 R2

3 12 Nieparzyste:

5 3

8 13

9 8 11

2 4 8 4 12 8 14

3 5 9 5 3 13 11

Czynniki wpływające na wydajność

• Czy krotki relacji są trzymane razem fizycznie (ciągłe)?

• Czy relacje są posortowane po atrybucie złączenia?

• Czy są dostępne indeksy?

• Jak selektywne jest zapytanie?

Złączenie iteracyjne R1 ⋈ ^R2

• Relacje nieciągłe

T(R1) = 10.000 T(R2) = 5.000 S(R1) = S(R2) =1/10 bloku

M = 101 bloków Koszt: dla każdej krotki R1:

[Odczyt krotki + odczyt całej R2]

Łącznie =10.000 [1+5000]=50.010.000

Ma sak ra

Czy da się lepiej?

• Użyjmy naszych umysłów

• Żeby wykorzystać dostępną pamięć

(1) Wczytaj 100 bloków R1

(2) Wczytaj całą R2 (używając 1 bloku) i złącz (3) Powtarzaj do wyczerpania R1

• Dla każdego fragmentu R1:

Odczyt fragmentu: 1000 Odczyt R2: 5000

6000

Łącznie = 10.000 x 6000 = 60.000 1.000

Koszty?

Am nes tia

Czy można jeszcze lepiej?

 Odwróć kolejność: R2 ⋈ ^R1

Łącznie = 5000 x (1000 + 10.000) = 1000

5 x 11.000 = 55.000

• Relacje ciągłe

Złączenie iteracyjne R1 ⋈ ^R2

Koszt

• Dla każdego fragmentu R2:

Odczyt fragmentu: 100 Odczyt R1: 1000 1100

Łącznie= 5 fragmentów x 1100 = 5500

Złączenie przez scalanie

• R1, R2 posortowane po C; ciągłe

Pamięć

R1 R2

…..

…..

R1

R2

Łączny koszt: Odczyt R1 + Odczyt R2

• R1, R2 nieposortowane, ale ciągłe

• Trzeba je posortować

• Jak?

Złączenie przez scalanie

Sortowanie przez scalanie

(i) Dla każdego 100-blokowego fragmentu R:

- Przeczytaj fragment - Posortuj go w pamięci - Zapisz go na dysk

posortowane fragmenty

R1

R2 ...

(ii) Przeczytaj fragmenty, scal i wypisz

Posortowany

plik Pamięć

posortowane fragmenty

...

...

Koszt sortowania

• Każda krotka jest odczytywana, zapisywana odczytywana, zapisywana

• Koszt sortowania R1: 4 x 1000 = 4.000

• Koszt sortowania R2: 4 x 500 = 2.000

Sortowanie przez scalanie

R1,R2 ciągłe ale nieposortowane

Łączny koszt = sortowanie + scalanie = 6000 + 1500 = 7500

Ale: Koszt złączenia iteracyjnego = 5.500

Złączenie przez scalanie się nie opłaca!

Ale: R1 = 10.000 bloków ciągłe

R2 = 5.000 bloków nieposortowane Iteracyjnie: 5000 x (100+10.000) = 50x10.100

100

= 505.000

Przez scalanie: 5(10.000+5.000) = 75.000 Złączenie przez scalanie wygrywa!

Pamięć potrzebna do sortowania

Np. Załóżmy że mamy 10 bloków ¹⁰

...

100 fragmentów  do złączenia potrzebujemy 100 bloków!

R1

W ogólności

• Liczba ramek pamięci: k

• Wielkość relacji: x bloków

• Liczba fragmentów = (x/k)

• Wielkość fragmentu = k

L. fragmentów < L. ramek przy scalaniu więc... (x/k)  k

W naszym przykładzie

• R1 ma 1000 bloków, k  31.62

• R2 ma 500 bloków, k  22.36

• Potrzeba co najmniej 32 ramek

Czy da się jeszcze poprawić scalanie?

• Czy naprawdę potrzebujemy całkowicie posortowanych relacji?

R1

R2

Złączenie

Posortowane

Koszt poprawionego algorytmu

Odczyt R1 + Zapis posortowanych frag. R1 + Odczyt R2 + Zapis posortowanych frag. R2 + scalenie

= 2000 + 1000 + 1500 = 4500

• Jakie są wymagania względem pamięci?

Iteracyjne z indeksem

• Załóżmy istnienie indeksu na R1.C (dwupoziomowego)

• Załóżmy, że R2 jest ciągła, nieposortowana

• Załóżmy, że indeks na R1.C mieści się w pamięci

Odczyt R2: 500

dla każdej krotki R2:

- Wyszukiwanie w indeksie (za darmo) - Odczyt każdej krotki R1 wskazanej przez indeks to koszt 1

Koszt iteracyjnego z indeksem

Ile jest pasujących krotek?

(a) Jeśli R1.C to klucz, R2.C to klucz obcy, to spodziewana liczba = 1

(b) Jeśli V(R1,C) = 5000, T(R1) = 10.000 (z założeniem rozkładu równomiernego) spodziewana liczba = 10.000/5.000 = 2

(c) Jeśli DOM(R1, C)=1.000.000 T(R1) = 10.000

gdy rozkład równomierny w dziedzinie Spodziewana = 10,000 = 1

liczba 1.000.000 100

Ile jest pasujących krotek?

Koszt iteracyjnego z indeksem

(a) Łącznie = 500+5000(1)1 = 5,500 (b) Łącznie = 500+5000(2)1 = 10,500 (c) Łącznie = 500+5000(1/100)1=550

Gdy indeks nie mieści się pamięci?

• Załóżmy, że indeks na R1.C ma 201 bloków

• Trzymaj korzeń + 99 liści w pamięci

• Średni koszt każdego wyszukiwania to:

E = (0)99 + (1)101  0,5 200 200

= 500+5000 [Wyszukiwanie + pobierz rekordy]

= 500+5000 [0,5+2]

= 500+12.500 = 13.000 (przypadek b) Przypadek (c)

= 500+5000[0,5  1 + (1/100)  1]

= 500+2500+50 = 3050

Koszt łączny (z wyszukiwaniem)

Dotychczas

Iteracyjne R2

⋈

Sort+Scalanie _______

Z indeksem na R1.C _______

Z indeksem na R2.C _______

Iteracyjne R2

⋈

Scalanie 1500

Sort+Scalanie 7500  4500

Z indeksem na R1.C 5500  3050  550 Z indeksem na R2.C ________

ciągłenie-ciągłe

• R1, R2 ciągłe (nie-posortowane)

 Użyj 100 kubełków

 Odczytaj R1, haszuj + zapisz kubełki R1 ^

Haszowane

... ... ¹⁰⁰

->Tak samo dla R2

->Przeczytaj kubełek R1; zbuduj w pamięci tab.hasz.

->Przeczytaj odpowiedni kubełek R2 + złącz haszem

R1

R2

...

R1

Pamięć

...

->Powtórz dla wszystkich kubełków

Koszt

Kubełkowanie Odczyt R1 + zapis Odczyt R2 + zapis Scalanie: Odczyt R1, R2

Koszt łączny = 3 x [1000+500] = 4500

Uwaga: to jest tylko oszacowanie, ponieważ

Wymaganie pamięciowe

Rozmiar kubełka dla R1 = (x/k)

k = liczba ramek pamięci x = liczba bloków R1

Więc? (x/k) < k

k > x potrzeba k+1 ramek

Sztuczka: trzymaj niektóre kubełki w RAM

Np., k’=33 kubełki R1 = 31 bloków trzymaj 2 w RAM

pamięć G⁰ G1

wej.

...

31

33-2=31

R1

Użycie pamięci:

G0 31 ramek

G1 31 ramek

Wyjście 33-2 ramek Odczyt R1 1

Łącznie 94 ramek Zostało jeszcze 6 ramek!

Następnie: Kubełkowanie R2

– Kubełki R2 =500/33= 16 bloków

– Dwa z kubełków R2 od razu łączone z G0,G1

pamięć G⁰ G1

wej.

...

16

33-2=31

R2

...

31

33-2=31

Kubełki R2 Kubełki R1

Ostatecznie: Scal pozostałe kubełki

– Dla każdej pary kubełków:

• Wczytaj jeden z nich w całości do pamięci

• Scal z drugim z nich pamięć

Gⁱ wyj.

...

16

33-2=31

wynik

...

31

33-2=31

Kubełki R2 Kubełki R1

cały kubełek R2

jedna ramka R1

Koszt hybrydowego haszowanego

• Kubełkowanie R1 = 1000+3131=1961

• Kubełkowanie R2, zapisujemy tylko 31 kubełków, więc 500+3116=996

• Scalanie kubełków (2 już z głowy):

odczyty 3131+3116=1457

• Łącznie = 1961+996+1457 = 4414

Ile kubełków trzymać w pamięci?

pamięć G⁰ G1

R1 wej.

pamięć

G⁰ R1 wej.

czy ?

Kolejna sztuczka dla haszowanego

• Do kubełków zapisuj tylko pary

<wartość,wskaźnik>

= Utworzenie indeksu haszowanego w locie

• Dopiero gdy znajdzie się para

pasujących krotek, trzeba ściągnąć z dysku krotki

• Założenia:

– 100 par <wartość,wskaźnik> w bloku

– Spodziewana liczba krotek wynikowych = 100

• Zbuduj tablicę haszującą dla R2 w pamięci 5000 krotek  5000/100 = 50 bloków

• Odczytaj R1 i scalaj

• Odczytaj ~ 100 krotek R2

Łączny koszt = Odczyt R2: 500 Odczyt R1: 1000 Pobierz krotki: 100

Dotychczas

Iteracyjne 5500

Scalanie 1500

Sort+scalanie 7500

Indeks na R1.C 5500  550 Indeks na R2.C _____

Zbuduj indeks na R1.C _____

Zbuduj indeks na R2.C _____

Haszowane 4500+

sztuczka,R1 prow. 4414 sztuczka,R2 prow. _____

Haszowane, wskaźniki 1600

ciągłe

Podsumowanie

• Iteracyjne dobre dla „małych” relacji (w porównaniu z wielkością pamięci)

• Dla złączenia równościowego, gdy relacje nie są posortowane i nie ma indeksów,

haszowane zwykle najlepsze

• Przez scalanie dobre dla złączeń nie- równościowych (np., R1.C > R2.C)

• Jeśli relacje już posortowane, użyj scalania

• Jeśli są indeksy, mogą być użyteczne (zależy to od selektywności – spodziewanego