Systemy zarzdzania bazami danych 6 Optymalizacja zapyta Orygina

Systemy zarządzania bazami danych 6. Optymalizacja zapytań Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 1

Optymalizacja zapytań --> Generowanie i wybór planu Zapytanie Generuj Oczyść Plany x x Oszacuj koszty Koszty Wybierz Minimum Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 2

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 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 3

Szczegóły implementacyjne • Algorytmy złączania • Zarządzanie pamięcią • Przetwarzanie równoległe Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 4

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 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 5

Szacowanie wymaga parametrów B(R) f(R) M HT(i) LB(i) = liczba bloków z krotkami relacji R = liczba krotek R w jednym bloku = liczba dostępnych ramek pamięci = liczba poziomów indeksu i = liczba bloków z liśćmi indeksu i • Jakich operacji są to koszty? Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 6

Indeks pogrupowany • Indeks, w którym pozycje indeksu są w takim samym fizycznym porządku jak rekordy A ind. na A 10 15 17 19 35 37 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 7

Różne znaczenia pogrupowania • Grono (cluster) R 1 R 2 S 1 S 2 R 3 R 4 S 3 S 4 …. . • Relacja pogrupowana (clustered) R 1 R 2 R 3 R 4 R 5 R 7 R 8 …. . • Indeks pogrupowany (clustering) Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 8

R 1 ⋈ R 2 po wspólnym atrybucie C T(R 1) = 10. 000 T(R 2) = 5. 000 S(R 1) = S(R 2) = 1/10 bloku Dostępna pamięć = 101 bloków Miara: liczba operacji we/wy (ignorując zapis wyniku – dlaczego? ) Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 9

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) Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 10

Możliwości • Transformacje: R 1 ⋈ R 2, R 2 ⋈ R 1 – 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) – Haszowane (hash join) Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 11

Złączenie iteracyjne for each r R 1 do for each s R 2 do if r. C = s. C then output <r, s> Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 12

Przez scalanie (przykład) i R 1{i}. C R 2{j}. C j 1 2 3 4 5 10 20 20 30 40 5 20 20 30 30 50 52 1 2 3 4 5 6 7 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 13

Przez scalanie (1) Jeśli R 1 i R 2 nie posortowane po C, posortuj je (2) i 1; j 1; while (i T(R 1)) (j T(R 2)) do if R 1{ i }. C = R 2{ j }. C then output. Tuples else if R 1{ i }. C > R 2{ j }. C then j j+1 else if R 1{ i }. C < R 2{ j }. C then i i+1 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 14

Procedura output. Tuples while (R 1{ i }. C = R 2{ j }. C) (i T(R 1)) do [ jj j; while (R 1{ i }. C = R 2{ jj }. C) (jj T(R 2)) do [ output <R 1{ i }, R 2{ jj }>; jj jj+1 ] i i+1 ] Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 15

Iteracyjne z indeksem for each r R 1 do [ X index (R 2, C, r. C) for each s X do output <r, s> ] Zakładamy istnienie indeksu na R 2. C X index(R, A, W) wówczas: X = zbiór krotek R o atrybucie A równym W Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 16

Haszowane – Funkcja haszująca h, przeciwdziedzina 0 k – Kubełki dla R 1: G 0, G 1, . . . Gk – Kubełki dla R 2: H 0, H 1, . . . Hk (1) Rozrzuć krotki R 1 do kubełków G (hasz na C) (2) Rozrzuć krotki R 2 do kubełków H (hasz na C) (3) Dla każdego i = 0, 1, 2, . . . , k dopasuj krotki z kubełków Gi i Hi Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 17

Prosty przykład: parzyste/nieparzyste R 1 R 2 Kubełki 248 4 12 8 14 2 5 Parzyste: 4 4 R 1 R 2 3 12 Nieparzyste: 3 5 9 5 3 13 11 5 3 8 13 9 8 11 14 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 18

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? Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 19

Złączenie iteracyjne R 1 ⋈ R 2 ak as M • Relacje nieciągłe T(R 1) = 10. 000 T(R 2) = 5. 000 S(R 1) = S(R 2) =1/10 bloku M = 101 bloków ra Koszt: dla każdej krotki R 1: [Odczyt krotki + odczyt całej R 2] Łącznie =10. 000 [1+5000]=50. 010. 000 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 20

Czy da się lepiej? • Użyjmy naszych umysłów • Żeby wykorzystać dostępną pamięć (1) Wczytaj 100 bloków R 1 (2) Wczytaj całą R 2 (używając 1 bloku) i złącz (3) Powtarzaj do wyczerpania R 1 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 21

Koszty? Am • Dla każdego fragmentu R 1: Odczyt fragmentu: 1000 Odczyt R 2: 5000 6000 st ne ia Łącznie = 10. 000 x 6000 = 60. 000 1. 000 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 22

Czy można jeszcze lepiej? Odwróć kolejność: R 2 ⋈ R 1 Łącznie = 5000 x (1000 + 10. 000) = 1000 5 x 11. 000 = 55. 000 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 23

Złączenie iteracyjne R 1 ⋈ R 2 • Relacje ciągłe Koszt • Dla każdego fragmentu R 2: Odczyt fragmentu: 100 Odczyt R 1: 1000 1100 Łącznie= 5 fragmentów x 1100 = 5500 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 24

Złączenie przez scalanie • R 1, R 2 posortowane po C; ciągłe Pamięć R 1 R 2 R 1 …. . R 2 Łączny koszt: Odczyt R 1 + Odczyt R 2 = 1000 + 500 = 1. 500 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 25

Złączenie przez scalanie • R 1, R 2 nieposortowane, ale ciągłe • Trzeba je posortować • Jak? Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 26

Sortowanie przez scalanie (i) Dla każdego 100 -blokowego fragmentu R: - Przeczytaj fragment - Posortuj go w pamięci - Zapisz go na dysk . . . R 1 R 2 posortowane fragmenty Pamięć Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 27

(ii) Przeczytaj fragmenty, scal i wypisz Oryginał: Hector Garcia-Molina . . . Posortowany plik Pamięć 6. Optymalizacja zapytań posortowane fragmenty 28

Koszt sortowania • Każda krotka jest odczytywana, zapisywana • Koszt sortowania R 1: 4 x 1000 = 4. 000 • Koszt sortowania R 2: 4 x 500 = 2. 000 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 29

Sortowanie przez scalanie R 1, R 2 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! Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 30

Ale: R 1 = 10. 000 bloków ciągłe R 2 = 5. 000 bloków nieposortowane Iteracyjnie: 5000 x (100+10. 000) = 50 x 10. 100 = 505. 000 Przez scalanie: 5(10. 000+5. 000) = 75. 000 Złączenie przez scalanie wygrywa! Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 31

Pamięć potrzebna do sortowania Np. Załóżmy że mamy 10 bloków R 1 . . . 10 Oryginał: Hector Garcia-Molina 100 fragmentów do złączenia potrzebujemy 100 bloków! 6. Optymalizacja zapytań 32

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 lub k 2 x or k x Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 33

W naszym przykładzie • R 1 ma 1000 bloków, k 31. 62 • R 2 ma 500 bloków, k 22. 36 • Potrzeba co najmniej 32 ramek Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 34

Czy da się jeszcze poprawić scalanie? • Czy naprawdę potrzebujemy całkowicie posortowanych relacji? R 1 Złączenie R 2 Posortowane ciągi Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 35

Koszt poprawionego algorytmu Odczyt R 1 + Zapis posortowanych frag. R 1 + Odczyt R 2 + Zapis posortowanych frag. R 2 + scalenie = 2000 + 1500 = 4500 • Jakie są wymagania względem pamięci? Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 36

Iteracyjne z indeksem • Załóżmy istnienie indeksu na R 1. C (dwupoziomowego) • Załóżmy, że R 2 jest ciągła, nieposortowana • Załóżmy, że indeks na R 1. C mieści się w pamięci Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 37

Koszt iteracyjnego z indeksem Odczyt R 2: 500 dla każdej krotki R 2: - Wyszukiwanie w indeksie (za darmo) - Odczyt każdej krotki R 1 wskazanej przez indeks to koszt 1 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 38

Ile jest pasujących krotek? (a) Jeśli R 1. C to klucz, R 2. C to klucz obcy, to spodziewana liczba = 1 (b) Jeśli V(R 1, C) = 5000, T(R 1) = 10. 000 (z założeniem rozkładu równomiernego) spodziewana liczba = 10. 000/5. 000 = 2 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 39

Ile jest pasujących krotek? (c) Jeśli DOM(R 1, C)=1. 000 T(R 1) = 10. 000 gdy rozkład równomierny w dziedzinie Spodziewana = 10, 000 = 1 liczba 1. 000 100 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 40

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 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 41

Gdy indeks nie mieści się pamięci? • Załóżmy, że indeks na R 1. 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 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 42

Koszt łączny (z wyszukiwaniem) = 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 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 43

Dotychczas nie-ciągłe ⋈ Iteracyjne R 2 R 1 55000 (najlepszy) Scalanie _______ Sort+Scalanie _______ Z indeksem na R 1. C _______ Z indeksem na R 2. C _______ Iteracyjne R 2 R 1 5500 Scalanie 1500 Sort+Scalanie 7500 4500 Z indeksem na R 1. C 5500 3050 550 Z indeksem na R 2. C ____ ⋈ Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 44

Haszowane • R 1, R 2 ciągłe (nie-posortowane) Użyj 100 kubełków Odczytaj R 1, haszuj + zapisz kubełki . . . R 1 100 10 bloków Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 45

->Tak samo dla R 2 ->Przeczytaj kubełek R 1; zbuduj w pamięci tab. hasz. ->Przeczytaj odpowiedni kubełek R 2 + złącz haszem R 2 . . . R 1 Pamięć ->Powtórz dla wszystkich kubełków Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 46

Koszt Kubełkowanie Odczyt R 1 + zapis Odczyt R 2 + zapis Scalanie: Odczyt R 1, R 2 Koszt łączny = 3 x [1000+500] = 4500 Uwaga: to jest tylko oszacowanie, ponieważ kubełki mogą się różnić wielkością i trzeba je było „zaokrąglić” do wielkości bloku Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 47

Wymaganie pamięciowe Rozmiar kubełka dla R 1 = (x/k) k = liczba ramek pamięci x = liczba bloków R 1 Więc? (x/k) < k k > x Oryginał: Hector Garcia-Molina potrzeba k+1 ramek 6. Optymalizacja zapytań 48

Sztuczka: trzymaj niektóre kubełki w RAM Np. , k’=33 kubełki R 1 = 31 bloków trzymaj 2 w RAM pamięć wej. G 0 G 1 31 . . . R 1 Użycie pamięci: G 0 31 ramek G 1 31 ramek Wyjście 33 -2 ramek Odczyt R 1 1 Łącznie 94 ramek Zostało jeszcze 6 ramek! 33 -2=31 Hybrydowe złączenie haszowane Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 49

Następnie: Kubełkowanie R 2 – Kubełki R 2 =500/33= 16 bloków – Dwa z kubełków R 2 od razu łączone z G 0, G 1 pamięć G 1 16 Oryginał: Hector Garcia-Molina Kubełki R 1 31 33 -2=31 6. Optymalizacja zapytań . . . G 0 Kubełki R 2 . . . R 2 wej. 33 -2=31 50

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ęć jedna ramka R 1 Oryginał: Hector Garcia-Molina 16 Kubełki R 1 31 33 -2=31 6. Optymalizacja zapytań . . . Gi Kubełki R 2 . . . wynik wyj. cały kubełek R 2 33 -2=31 51

Koszt hybrydowego haszowanego • Kubełkowanie R 1 = 1000+31 31=1961 • Kubełkowanie R 2, 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 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 52

Ile kubełków trzymać w pamięci? pamięć R 1 wej. pamięć G 0 G 1 Oryginał: Hector Garcia-Molina R 1 czy 6. Optymalizacja zapytań wej. G 0 ? 53

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 Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 54

• Założenia: – 100 par <wartość, wskaźnik> w bloku – Spodziewana liczba krotek wynikowych = 100 • Zbuduj tablicę haszującą dla R 2 w pamięci 5000 krotek 5000/100 = 50 bloków • Odczytaj R 1 i scalaj • Odczytaj ~ 100 krotek R 2 Łączny koszt = Oryginał: Hector Garcia-Molina Odczyt R 2: Odczyt R 1: Pobierz krotki: 6. Optymalizacja zapytań 500 100 1600 55

ciągłe Dotychczas Iteracyjne Scalanie Sort+scalanie Indeks na R 1. C Indeks na R 2. C Zbuduj indeks na R 1. C Zbuduj indeks na R 2. C Haszowane sztuczka, R 1 prow. sztuczka, R 2 prow. Haszowane, wskaźniki Oryginał: Hector Garcia-Molina 5500 1500 7500 5500 550 _____ 4500+ 4414 _____ 1600 6. Optymalizacja zapytań 56

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ń nierównościowych (np. , R 1. C > R 2. C) • Jeśli relacje już posortowane, użyj scalania • Jeśli są indeksy, mogą być użyteczne (zależy to od selektywności – spodziewanego rozmiaru wyniku) Oryginał: Hector Garcia-Molina 6. Optymalizacja zapytań 57