Optymalizacja zapyta Proces przetwarzania i obliczania wyniku zapytania

Optymalizacja zapytań Proces przetwarzania i obliczania wyniku zapytania (wyrażenia algebry relacji) w SZBD

Elementy optymalizacji n n n Analiza zapytania i przekształcenie go do „lepszej” postaci. Oszacowanie kosztu różnych opcji wykonania zapytania: u informacje (statystyki) służące do szacowania kosztu; u metody wykonania selekcji; u metody złączeń; u metody eliminacji duplikatów i sortowania Analizowanie i modyfikowanie planu wykonania zapytania.

Przekształcanie wyrażeń algebry relacji SELECT K. prow FROM Student S, Ocena O, Kurs K WHERE S. indeks=O. indeks AND O. przed=K. przed AND O. ocena>=K. ocena. Kwal AND S. nazwisko="Abacki”

Przekształcanie wyrażeń algebry relacji S 1 = πindeks(σnazwisko="Abacki”(S)) n O 1 = πindeks, ocena, przed(Ocena) n K 1 = πprow, ocena. Kwal, przed(Kurs) n SO = πocena, przed(S 1 |><| O 1) n SOK = πprow(σocena>=ocena. Kwal (SO |><| K)) n

Przekształcanie wyrażeń algebry relacji n n n Wykonaj jak najwcześniej operacje selekcji (przemienność selekcji z innymi operacjami). Połącz iloczyn kartezjański z następującą po nim selekcją w złączenie (o ile to możliwe). Zastosuj łączność operacji złączenia tak, by wykonać złączenia w jak najbardziej ekonomicznej kolejności (algorytm dynamiczny wyznaczania optymalnej kolejności rozstawienia nawiasów). Wykonaj jak najwcześniej operacje rzutu. Wydziel wspólne podwyrażenia i obliczaj je tylko raz.

Statystyki i szacowanie kosztu n n n Statystyki dla relacji R: u n. Tuples(R) – liczba krotek relacji R, u b. Factor(R) – liczba krotek relacji mieszczących się w jednym bloku dyskowym, u n. Blocks(R) – liczba bloków, w których jest przechowywana relacja R. Statystyki dla atrybutu A relacji R: u n. Distinct. A(R) – liczba różnych wartości A w R, u min. A(R), max. A(R) – minimalna i maksymalna wartość A w R, u SCA(R) – selektywność A w R, czyli średnia liczba krotek spełniających warunek równości dla A. Statystyki dla indeksu I według atrybutu A: u n. Levels. A(I) - liczba poziomów I (jeśli jest drzewem), u n. Lf. Blocks. A(I) - liczba bloków-liści w drzewie.

Statystyki i szacowanie kosztu n Przyjmuje się SCA(R) = { 1 iff A klucz; n. Tuples(R)/n. Distinct. A(R) wpp } n n Dla innych warunków także można określić selektywność: u n. Tuples(R)* ((max. A(R)-c)/(max. A(R)-min. A(R))) dla warunku A>c u n. Tuples(R)* ((c-min. A(R))/(max. A(R)-min. A(R))) dla warunku A<c u n. Tuples(R)*n/n. Distinct. A(R) dla warunku A in {c 1, c 2, . . . , cn} u SCA(R)*SCB(R) dla warunku (A AND B) u SCA(R)+SCB(R)- SCA(R)*SCB(R) dla warunku (A OR B) W przypadku gdy w systemie znajdują się histogramy dla wartości atrybutu, powyższe szacowania mogą być dokładniejsze

Sposoby wykonania selekcji σw(A)(R), w(A) - warunek na A n n skanowanie całej relacji - n. Blocks(R), wybranie wszystkich krotek relacji za pomocą indeksu (np. dla relacji pamiętanej w klastrze)n. Tuples(R)+n. Levels. A(I) wykorzystanie indeksu grupującego dla A SCw(A)(R)/b. Factor(R)+n. Levels. A(I), wykorzystanie indeksu niegrupującego dla A SCw(A)(R)+n. Levels. A(I)

Wybór warunku do selekcji σF 1 AND. . . AND Fn(R), F 1, . . . , Fn - proste warunki Dla każdego Fi (1 <= i <= n) szacujemy koszt ci wykonania selekcji σFi. Wybieramy i, dla którego szacunkowy koszt był minimalny, i wybieramy (za pomocą indeksu lub bez) krotki spełniające warunek Fi, przy okazji sprawdzając, czy spełniają pozostałe warunki selekcji Fj (j<>i).

Wybór warunku do selekcji - przykład σA=2 AND B>950 AND C=5(R), dla R=ABCD n n R jest zapisana samodzielnie w n. Blocks(R)=1000 blokach dyskowych, ma 50000 krotek, po 50 w jednym bloku; koszt skanowania = 1000; R ma indeks niegrupujący dla A i n. Distinct. A(R)=10; koszt wyszukania wg A = 50000/10 = 5000; R ma indeks grupujący dla B i n. Distinct. B(R)=1000, min. B(R)=1, max. B(R)=1000; koszt wyszukania wg B = 50000*(50/1000)*(1/50) = 50; Dla C i D nie ma indeksów.

Obliczanie złączeń n Szacunkowy rozmiar złączenia: R |><| S, dla R = AB i S = BC wynosi: n. Distinct. B(? )* (n. Tuples(R)/n. Distinct. B(R)*n. Tuples(S)/n. Distinct. B(S)) = = n. Tuples(R)*n. Tuples(S)/n. Distinct. B(R), przy założeniu, że rozkład wartości B w R i S jest jednostajny.

Zagnieżdżone pętle po blokach for next M-2 blocks br 1, br 2, . . . , br. M-2 in R do for each block bs in S do for i=1, . . , M-1 return bri |><| bs; n Szacunkowy koszt czytania: n. Blocks(R) + (n. Blocks(R)/(M-2))*n. Blocks(S) zapisu wyniku (zawsze taki sam): n. Blocks(R)*n. Blocks(S)/n. Distinct. B(R)

Złączenia z wykorzystaniem indeksu: // 1. S ma indeks grupujący I wg. B for each t in R do search sx={s in S: s. B = t. B by I}; return sx |><| {t}; // n. Blocks(R)+ n. Tuples(R)*(n. Levels. B(S)+n. Blocks(S)/n. Distinct. B(S)) // 2. S ma ind. grup. (I 1), R ma ind. niegrup. I 1, I 2 wg. B for each value x in I 1 do search sx = {s in S: s. B = x by I 1}; search tx = {t in R: t. B = x by I 2}; return sx |><| tx; // n. Distinct. B(S)*(n. Levels. B(I 1)+n. Blocks(S)/n. Distinct. B(S)+ n. Levels. B(I 2)*n. Tuples(R)/n. Distinct. B(R))

Sort-Merge Join Sort(R wg B) // 2*n. Blocks(R)* (log. M-1(n. Blocks(R)/(M-1)+1) Sort(S wg B) // 2*Blocks(S)* (log. M-1(n. Blocks(S)/(M-1)+1) Merge(R, S wg B) // n. Blocks(R)+n. Blocks(S) n Sortowanie: u w pierwszym przebiegu sortujemy serie złożone z M-1 bloków; u potem log. M-1(n. Blocks(R)/(M-1) razy scalamy po M-1 uporządkowanych serii najpierw długości M-1, potem (M-1)2, potem (M-1)3 itd.

Hash-join // h - funkcja haszująca dla B przyjmująca wartości 1, . . . , M-1 Hash(R wg h(B)) into R 1, R 2, . . . , RM-1// 2*n. Blocks(R) Hash(S wg h(B)) into S 1, S 2, . . . , SM-1 // 2*n. Blocks(S) // h' - funkcja haszująca dla B niezależna od h przyjmująca także wartości 1, . . . , M-1 for i=1, . . . , M-1 do Hash(Ri wg h'(B)) into A 1, A 2, . . . , AM-1// n. Blocks(Ri)+M-1 Hash(Si wg h'(B)) into B 1, B 2, . . . , BM-1// n. Blocks(Si) for j=1, . . . , M-1 return Aj |><| Bj; // M-1 // razem koszt: 3*(n. Blocks(R)+n. Blocks(S))+(2. . 4)*M

Sortowanie, grupowanie i eliminacja powtórzeń n Operacje grupowania i eliminacji powtórzeń można wykonać poprzez sortowanie (M-1 krotny merge-sort, czyli multiway Merge. Sort) lub poprzez haszowanie połączone z sortowanie kubełków w pamięci.

Porównanie metod złączenia - przykład n n n P - pracownik (klucz: id) n. Tuples(P) = 6000 b. Factor(P) = 30 n. Blocks(P) = 200 n. Distinctid(P) = 6000 ma indeks niegrupujący po id wys. 3 n n n Z - zlecenie (zawiera id pracownika) n. Tuples(Z) = 100000 b. Factor(Z) = 50 n. Blocks(Z) = 2000 n. Distinctid(Z) = 16 M = 100 Pętle po blokach (P zewnętrzna): 200+(200/98)*2000=4281 Pętle po blokach (Z - zewnętrzna): 2000+(2000/98)*200=6081 Pętla z indeksem niegrupującym: 2000+2000*3=8000 Sort-Join: 2*200*(log 99(200/99)+1) + 2*2000*((log 99(2000/99)+1))+ 2000 2*200*2+2*2000*2+2000=(8800+2200)=11000 Hash-Join: 3*(200+2000)+3*100=6900

Statystyki w SZBD n n Statystyki tabel, atrybutów i indeksów są najczęściej aktualizowane: u co pewien czas lub u przy okazji operacji przeglądających relację (np. budowa indeksu) lub u na wyraźne życzenie użytkownika (np. polecenia z pakietu DBMS_STATS w Oracle). Oprócz podanych wcześniej, system może budować histogramy wartości atrybutów pozwalające trafnie oceniać koszt operacji nawet przy niejednostajnym rozkładzie wartości.

Plan wykonania EXPLAIN [ANALYZE] <zapytanie SQL> n n n kolejność i metody wykonywania złączeń (NESTED LOOPS, HASH-JOIN, SORT-JOIN, INDEX NESTED LOOPS), warunek selekcji i ewentualnie użyty dla niego indeks (np. INDEX SCAN USING <atrybut> ON <relacja> lub FULL SCAN) końcowe sortowanie, grupowanie lub haszowanie w celu uporządkowania lub pogrupowania wyniku. szacunkowy czas wykonania poszczególnych operacji (jeżeli użyto ANALYZE, to zapytanie jest wykonywane) szacunkowy rozmiar wyniku operacji

Wskazówki (hints) n n Specjalne komentarze zamieszczane przy zapytaniu wskazujące, jakiej metody obliczania ma użyć system. W komentarzu tym można zapisać: u jakiego optymalizatora ma użyć system (np. w Oracle można wybrać oparty na kosztach lub rankingu operacji), u jakiego indeksu użyć przy obliczaniu selekcji, u w jakiej kolejności wykonać złączenia, u jakiego algorytmu złączenia użyć. Np. SELECT /*+ INDEX(wg. Miasta)*/ nazwisko FROM Student WHERE miasto="Chełm"