Optymalizacja zapytań

Proces przetwarzania i obliczania Proces przetwarzania i obliczania wyniku zapytania (wyrażenia algebry wyniku zapytania (wyrażenia algebry

relacji) w SZBDrelacji) w SZBD

Elementy optymalizacji

Analiza zapytania i przekształcenie go do „lepszej” Analiza zapytania i przekształcenie go do „lepszej” postaci.postaci.

Oszacowanie kosztu różnych opcji wykonania zapytania:Oszacowanie kosztu różnych opcji wykonania zapytania: informacje (statystyki) służące do szacowania kosztu;informacje (statystyki) służące do szacowania kosztu; metody wykonania selekcji;metody wykonania selekcji; metody złączeń;metody złączeń; metody eliminacji duplikatów i sortowaniametody eliminacji duplikatów i sortowania

Analizowanie i modyfikowanie planu wykonania Analizowanie i modyfikowanie planu wykonania zapytania.zapytania.

Przekształcanie wyrażeń algebry relacji

SELECT K.prow SELECT K.prow

FROM Student S, Ocena O, Kurs KFROM Student S, Ocena O, Kurs K

WHERE S.indeks=O.indeks ANDWHERE S.indeks=O.indeks AND

O.przed=K.przed AND O.przed=K.przed AND O.ocena>=K.ocenaKwal AND O.ocena>=K.ocenaKwal AND S.nazwisko="Abacki”S.nazwisko="Abacki”

Przekształcanie wyrażeń algebry relacji

S1 = S1 = ππindeksindeks((σσnazwisko="Abacki”nazwisko="Abacki”(S))(S))

O1 = O1 = ππindeks,ocena,przedindeks,ocena,przed(Ocena)(Ocena)

K1 = K1 = ππprow,ocenaKwal,przedprow,ocenaKwal,przed(Kurs)(Kurs)

SO = SO = ππocena,przedocena,przed(S1 |><| O1)(S1 |><| O1)

SOK = SOK = ππprowprow((σσocena>=ocenaKwalocena>=ocenaKwal (SO |><| K)) (SO |><| K))

Przekształcanie wyrażeń algebry relacji

Wykonaj jak najwcześniej operacje selekcji (przemienność Wykonaj jak najwcześniej operacje selekcji (przemienność selekcji z innymi operacjami).selekcji z innymi operacjami).

Połącz iloczyn kartezjański z następującą po nim selekcją Połącz iloczyn kartezjański z następującą po nim selekcją w złączenie (o ile to możliwe).w złączenie (o ile to możliwe).

Zastosuj łączność operacji złączenia tak, by wykonać Zastosuj łączność operacji złączenia tak, by wykonać złączenia w jak najbardziej ekonomicznej kolejności złączenia w jak najbardziej ekonomicznej kolejności (algorytm dynamiczny wyznaczania optymalnej kolejności (algorytm dynamiczny wyznaczania optymalnej kolejności rozstawienia nawiasów).rozstawienia nawiasów).

Wykonaj jak najwcześniej operacje rzutu.Wykonaj jak najwcześniej operacje rzutu. Wydziel wspólne podwyrażenia i obliczaj je tylko raz.Wydziel wspólne podwyrażenia i obliczaj je tylko raz.

Statystyki i szacowanie kosztu Statystyki dla relacji R:Statystyki dla relacji R:

nTuples(R) – liczba krotek relacji R,nTuples(R) – liczba krotek relacji R, bFactor(R) – liczba krotek relacji mieszczących się w jednym bloku bFactor(R) – liczba krotek relacji mieszczących się w jednym bloku

dyskowym,dyskowym, nBlocks(R) – liczba bloków, w których jest przechowywana relacja R.nBlocks(R) – liczba bloków, w których jest przechowywana relacja R.

Statystyki dla atrybutu A relacji R:Statystyki dla atrybutu A relacji R: nDistinctnDistinctAA(R) – liczba różnych wartości A w R,(R) – liczba różnych wartości A w R,

minminAA(R), max(R), maxAA(R) – minimalna i maksymalna wartość A w R,(R) – minimalna i maksymalna wartość A w R,

SCSCAA(R) – selektywność A w R, czyli średnia liczba krotek (R) – selektywność A w R, czyli średnia liczba krotek

spełniających warunek równości dla A.spełniających warunek równości dla A. Statystyki dla indeksu I według atrybutu A:Statystyki dla indeksu I według atrybutu A:

nLevelsnLevelsAA(I) - liczba poziomów I (jeśli jest drzewem),(I) - liczba poziomów I (jeśli jest drzewem),

nLfBlocksnLfBlocksAA(I) - liczba bloków-liści w drzewie. (I) - liczba bloków-liści w drzewie.

Statystyki i szacowanie kosztu

Przyjmuje się Przyjmuje się

SCSCAA(R) = { 1 iff A klucz; nTuples(R)/nDistinct(R) = { 1 iff A klucz; nTuples(R)/nDistinctAA(R) wpp (R) wpp

}} Dla innych warunków także można określić selektywność:Dla innych warunków także można określić selektywność:

nTuples(R)* ((maxnTuples(R)* ((maxAA(R)-c)/(max(R)-c)/(maxAA(R)-min(R)-minAA(R))) dla warunku A>c(R))) dla warunku A>c

nTuples(R)* ((c-minnTuples(R)* ((c-minAA(R))/(max(R))/(maxAA(R)-min(R)-minAA(R))) dla warunku A<c(R))) dla warunku A<c

nTuples(R)*n/nDistinctnTuples(R)*n/nDistinctAA(R)(R) dla warunku A in {c dla warunku A in {c11,c,c22,...,c,...,cnn}}

SCSCAA(R)*SC(R)*SCBB(R) (R) dla warunku (A dla warunku (A ANDAND B) B)

SCSCAA(R)+SC(R)+SCBB(R)- SC(R)- SCAA(R)*SC(R)*SCBB(R) (R) dla warunku (A dla warunku (A OROR B) B)

W przypadku gdy w systemie znajdują się histogramy dla wartości W przypadku gdy w systemie znajdują się histogramy dla wartości atrybutu, powyższe szacowania mogą być dokładniejszeatrybutu, powyższe szacowania mogą być dokładniejsze

Sposoby wykonania selekcji

σσw(A)w(A)(R), (R), w(A) - warunek na Aw(A) - warunek na A skanowanie całej relacji - skanowanie całej relacji - nBlocks(R)nBlocks(R),, wybranie wszystkich krotek relacji za pomocą indeksu wybranie wszystkich krotek relacji za pomocą indeksu

(np. dla relacji pamiętanej w klastrze)- (np. dla relacji pamiętanej w klastrze)- nTuples(R)nTuples(R)+nLevels+nLevelsAA(I)(I)

wykorzystanie indeksu grupującego dla A - wykorzystanie indeksu grupującego dla A - SCSCw(A)w(A)(R)/bFactor(R)+nLevels(R)/bFactor(R)+nLevelsAA(I)(I),,

wykorzystanie indeksu niegrupującego dla A - wykorzystanie indeksu niegrupującego dla A - SCSCw(A)w(A)

(R)+nLevels(R)+nLevelsAA(I)(I)

Wybór warunku do selekcji

σσF1 AND ... AND FnF1 AND ... AND Fn(R), F(R), F11,...,F,...,Fnn - proste warunki - proste warunki

Dla każdego FDla każdego Fii (1 <= i <= n) szacujemy koszt c (1 <= i <= n) szacujemy koszt cii

wykonania selekcji wykonania selekcji σσFiFi..

Wybieramy i, dla którego szacunkowy koszt był Wybieramy i, dla którego szacunkowy koszt był minimalny, i wybieramy (za pomocą indeksu minimalny, i wybieramy (za pomocą indeksu lub bez) krotki spełniające warunek Flub bez) krotki spełniające warunek F ii, , przy przy

okazji sprawdzając, czy spełniają pozostałe okazji sprawdzając, czy spełniają pozostałe warunki selekcji Fwarunki selekcji Fjj (j<>i). (j<>i).

Wybór warunku do selekcji - przykład

σσA=2 AND B>950 AND C=5A=2 AND B>950 AND C=5(R), dla R=ABCD(R), dla R=ABCD R jest zapisana samodzielnie w nBlocks(R)=1000 blokach R jest zapisana samodzielnie w nBlocks(R)=1000 blokach

dyskowych, ma 50000 krotek, po 50 w jednym bloku; dyskowych, ma 50000 krotek, po 50 w jednym bloku; koszt koszt skanowania = 1000;skanowania = 1000;

R ma indeks niegrupujący dla A i nDistinctR ma indeks niegrupujący dla A i nDistinctAA(R)=10; (R)=10; koszt koszt

wyszukania wg A = 50000/10 = 5000;wyszukania wg A = 50000/10 = 5000; R ma indeks grupujący dla B i nDistinctR ma indeks grupujący dla B i nDistinctBB(R)=1000, (R)=1000,

minminBB(R)=1, max(R)=1, maxBB(R)=1000; (R)=1000; koszt wyszukania wg B = koszt wyszukania wg B =

50000*(50/1000)*(1/50) = 50;50000*(50/1000)*(1/50) = 50; Dla C i D nie ma indeksów.Dla C i D nie ma indeksów.

Obliczanie złączeń

Szacunkowy rozmiar złączenia:Szacunkowy rozmiar złączenia:

R |><| S, dla R = AB i S = BCR |><| S, dla R = AB i S = BC

wynosi:wynosi:nDistinctnDistinctBB(?)*(?)*

(nTuples(R)/nDistinct(nTuples(R)/nDistinctBB(R)*nTuples(S)/nDistinct(R)*nTuples(S)/nDistinctBB(S)) = (S)) =

= nTuples(R)*nTuples(S)/nDistinct= nTuples(R)*nTuples(S)/nDistinctBB(R),(R),

przy założeniu, że rozkład wartości B w R i przy założeniu, że rozkład wartości B w R i S jest jednostajny.S jest jednostajny.

Zagnieżdżone pętle po blokach

for next M-2 blocks brfor next M-2 blocks br11,br,br22,...,br,...,brM-2M-2 in R do in R do

for each block bs in S do for each block bs in S do

for i=1,..,M-1 return brfor i=1,..,M-1 return br ii |><| bs; |><| bs;

Szacunkowy koszt czytania:Szacunkowy koszt czytania:nBlocks(R) + (nBlocks(R)/(M-2))*nBlocks(S)nBlocks(R) + (nBlocks(R)/(M-2))*nBlocks(S)

zapisu wyniku (zawsze taki sam): zapisu wyniku (zawsze taki sam): nBlocks(R)*nBlocks(S)/nDistinctnBlocks(R)*nBlocks(S)/nDistinctBB(R)(R)

Złączenia z wykorzystaniem indeksu:

// 1. S ma indeks grupujący I wg. B// 1. S ma indeks grupujący I wg. B

for each t in R dofor each t in R do

search sx={s in S: s.B = t.B by I};search sx={s in S: s.B = t.B by I};

return sx |><| {t}; return sx |><| {t};

// // nBlocks(R)+nBlocks(R)+

nTuples(R)*(nLevelsnTuples(R)*(nLevelsBB(S)+nBlocks(S)/nDistinct(S)+nBlocks(S)/nDistinctBB(S))(S))

// 2. S ma ind. grup.(I1), R ma ind. niegrup. I1, I2 wg. B// 2. S ma ind. grup.(I1), R ma ind. niegrup. I1, I2 wg. B

for each value x in I1 do for each value x in I1 do

search sx = {s in S: s.B = x by I1};search sx = {s in S: s.B = x by I1};

search tx = {t in R: t.B = x by I2};search tx = {t in R: t.B = x by I2};

return sx |><| tx; return sx |><| tx;

// // nDistinctnDistinctBB(S)*(nLevels(S)*(nLevelsBB(I1)+nBlocks(S)/nDistinct(I1)+nBlocks(S)/nDistinctBB(S)+(S)+

nLevelsnLevelsBB(I2)*nTuples(R)/nDistinct(I2)*nTuples(R)/nDistinctBB(R))(R))

Sort-Merge Join

Sort(R wg B) // Sort(R wg B) // 2*nBlocks(R)* (log2*nBlocks(R)* (logM-1M-1(nBlocks(R)/(M-1)+1)(nBlocks(R)/(M-1)+1)

Sort(S wg B) // Sort(S wg B) // 2*Blocks(S)* (log2*Blocks(S)* (logM-1M-1(nBlocks(S)/(M-1)+1)(nBlocks(S)/(M-1)+1)

Merge(R,S wg B) // Merge(R,S wg B) // nBlocks(R)+nBlocks(S)nBlocks(R)+nBlocks(S)

Sortowanie: Sortowanie: w pierwszym przebiegu sortujemy serie w pierwszym przebiegu sortujemy serie

złożone z M-1 bloków;złożone z M-1 bloków; potem potem loglogM-1M-1(nBlocks(R)/(M-1) (nBlocks(R)/(M-1) razy scalamy razy scalamy

po M-1 uporządkowanych serii najpierw po M-1 uporządkowanych serii najpierw długości M-1, potem (M-1)długości M-1, potem (M-1)22, potem (M-1), potem (M-1)33 itd. itd.

Hash-join

// h - funkcja haszująca dla B przyjmująca wartości 1,...,M-1// h - funkcja haszująca dla B przyjmująca wartości 1,...,M-1

Hash(R wg h(B)) into RHash(R wg h(B)) into R11,R,R22,...,R,...,RM-1M-1// // 2*nBlocks(R)2*nBlocks(R)

Hash(S wg h(B)) into SHash(S wg h(B)) into S11,S,S22,...,S,...,SM-1M-1 // // 2*nBlocks(S)2*nBlocks(S)

// h' - funkcja haszująca dla B niezależna od h przyjmująca także // h' - funkcja haszująca dla B niezależna od h przyjmująca także wartości 1,...,M-1wartości 1,...,M-1

for i=1,...,M-1 dofor i=1,...,M-1 do

Hash(RHash(Rii wg h'(B)) into A wg h'(B)) into A11,A,A22,...,A,...,AM-1M-1// // nBlocks(RnBlocks(Rii)+M-1)+M-1

Hash(SHash(Sii wg h'(B)) into B wg h'(B)) into B11,B,B22,...,B,...,BM-1M-1// // nBlocks(SnBlocks(Sii))

for j=1,...,M-1 return Aj |><| Bj; // for j=1,...,M-1 return Aj |><| Bj; // M-1M-1

// razem koszt: // razem koszt: 3*(nBlocks(R)+nBlocks(S))+(2..4)*M3*(nBlocks(R)+nBlocks(S))+(2..4)*M

Sortowanie, grupowanie i eliminacja powtórzeń Operacje grupowania i eliminacji Operacje grupowania i eliminacji

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

Porównanie metod złączenia - przykład

P - pracownik (klucz: id)P - pracownik (klucz: id) nTuples(P) = 6000nTuples(P) = 6000 bFactor(P) = 30bFactor(P) = 30 nBlocks(P) = 200nBlocks(P) = 200 nDistinctnDistinctidid(P) = 6000(P) = 6000

ma indeks niegrupujący po id ma indeks niegrupujący po id wys.3wys.3

Z - zlecenie (zawiera id Z - zlecenie (zawiera id pracownika)pracownika)

nTuples(Z) = 100000nTuples(Z) = 100000 bFactor(Z) = 50bFactor(Z) = 50 nBlocks(Z) = 2000nBlocks(Z) = 2000 nDistinctnDistinctidid(Z) = 16(Z) = 16

M = 100M = 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*(log99(200/99)+1) + 2*2000*((log99(2000/99)+1))+ 200+ 20002*200*2+2*2000*2+200+2000=(8800+2200)=11000

Hash-Join: 3*(200+2000)+3*100=6900

Statystyki w SZBD Statystyki tabel, atrybutów i indeksów są najczęściej Statystyki tabel, atrybutów i indeksów są najczęściej

aktualizowane:aktualizowane: co pewien czas lubco pewien czas lub przy okazji operacji przeglądających relację (np. przy okazji operacji przeglądających relację (np.

budowa indeksu) lubbudowa indeksu) lub na wyraźne życzenie użytkownika (np. polecenia z na wyraźne życzenie użytkownika (np. polecenia z

pakietu DBMS_STATS w Oracle).pakietu DBMS_STATS w Oracle). Oprócz podanych wcześniej, system może budować Oprócz podanych wcześniej, system może budować

histogramy wartości atrybutów pozwalające trafnie histogramy wartości atrybutów pozwalające trafnie oceniać koszt operacji nawet przy niejednostajnym oceniać koszt operacji nawet przy niejednostajnym rozkładzie wartości.rozkładzie wartości.

Plan wykonania

EXPLAIN [ANALYZE] <zapytanie SQL>EXPLAIN [ANALYZE] <zapytanie SQL> kolejność i metody wykonywania złączeń (NESTED kolejność i metody wykonywania złączeń (NESTED

LOOPS, HASH-JOIN, SORT-JOIN, INDEX NESTED LOOPS, HASH-JOIN, SORT-JOIN, INDEX NESTED LOOPS),LOOPS),

warunek selekcji i ewentualnie użyty dla niego indeks (np. warunek selekcji i ewentualnie użyty dla niego indeks (np. INDEX SCAN USING <atrybut> ON <relacja> lub FULL INDEX SCAN USING <atrybut> ON <relacja> lub FULL SCAN)SCAN)

końcowe sortowanie, grupowanie lub haszowanie w celu końcowe sortowanie, grupowanie lub haszowanie w celu uporządkowania lub pogrupowania wyniku.uporządkowania lub pogrupowania wyniku.

szacunkowy czas wykonania poszczególnych operacji szacunkowy czas wykonania poszczególnych operacji (jeżeli użyto ANALYZE, to zapytanie jest wykonywane)(jeżeli użyto ANALYZE, to zapytanie jest wykonywane)

szacunkowy rozmiar wyniku operacjiszacunkowy rozmiar wyniku operacji

Wskazówki (hints) Specjalne komentarze zamieszczane przy zapytaniu Specjalne komentarze zamieszczane przy zapytaniu

wskazujące, jakiej metody obliczania ma użyć system. W wskazujące, jakiej metody obliczania ma użyć system. W komentarzu tym można zapisać:komentarzu tym można zapisać: jakiego optymalizatora ma użyć system (np. w Oracle jakiego optymalizatora ma użyć system (np. w Oracle

można wybrać oparty na kosztach lub rankingu można wybrać oparty na kosztach lub rankingu operacji),operacji),

jakiego indeksu użyć przy obliczaniu selekcji,jakiego indeksu użyć przy obliczaniu selekcji, w jakiej kolejności wykonać złączenia,w jakiej kolejności wykonać złączenia, jakiego algorytmu złączenia użyć.jakiego algorytmu złączenia użyć.

Np. Np. SELECT /*+ INDEX(wgMiasta)*/ nazwiskoSELECT /*+ INDEX(wgMiasta)*/ nazwisko

FROM Student WHERE miasto="Chełm"FROM Student WHERE miasto="Chełm"