Systemy zarządzania bazami danych

(1)

Systemy zarządzania bazami danych

5. Przetwarzanie zapytań

(2)

Przetwarzanie zapytań

• Zapytanie  Plan wykonania

• Plan wykonania  Wynik

• Przykład:

Select B,D From R,S

Where R.A = “c”  S.E = 2  R.C=S.C

(3)

R A B C S C D E

a 1 10 10 x 2

b 1 20 20 y 2

c 2 10 30 z 2

d 2 35 40 x 1

e 3 45 50 y 3

Wynik B D

(4)

Jaki plan wykonania tego zapytania?

- Oblicz produkt kartezjański - Wybierz pasujące krotki

- Wykonaj rzutowanie

Plan naiwny

(5)

RXS R.A R.B R.C S.C S.D S.E a 1 10 10 x 2 a 1 10 20 y 2 .

.

C 2 10 10 x 2 . .

Bingo!

Mamy jednego...

(6)

Algebra relacji = logiczny plan wykonania

Plan naiwny

B,D



R.A=“c” S.E=2  R.C=S.C

X

R S

(7)

Plan sprytniejszy

B,D



R.A = “c”



S.E = 2

R S

Złączenie naturalne

(8)

R S

A B C (R) (S) ^{C D E}

a 1 10 A B C C D E 10 x 2 b 1 20 c 2 10 10 x 2 20 y 2 c 2 10 20 y 2 30 z 2 d 2 35 30 z 2 40 x 1 e 3 45 50 y 3

(9)

Plan z użyciem indeksów

Wykorzystaj indeksy na R.A i S.C

(1) Użyj indeksu na R.A żeby wybrać te krotki R, dla których R.A = “c”

(2) Dla każdej znalezionej wartości R.C użyj indeksu na S.C by znaleźć pasujące krotki (3) Odrzuć te krotki S, w których S.E  2

(4) Sklej pasujące krotki z R i S

(5) Zrzutuj pasujące pary na atrybuty BD i

(10)

R S

A B C ^{C D E}

a 1 10 10 x 2

b 1 20 20 y 2

c 2 10 30 z 2 d 2 35 40 x 1 e 3 45 50 y 3

A C

I¹ I²

=“c”

<c,2,10> <10,x,2>

czy =2?

wynik: <2,x>

następna krotka:

<c,7,15>

(11)

Analiza składniowa

Transformacja

Zastosuj reguły

Oszacuj wielkość wyniku

Wygeneruj plany fizyczne Oszacuj koszty Wybierz najtańszy

Wykonaj

{(P₁,C₁),(P₂,C₂)...}

P_i

wynik zapytanie SQL

drzewo składni

logiczny plan zapytania

poprawiony l.p.z.

l.p.z. + szacunki

statystyki

Schemat

optymalizatora

(12)

Przykładowe zapytanie SQL

SELECT title FROM StarsIn

WHERE starName IN ( SELECT name FROM MovieStar

WHERE birthdate LIKE ‘%1960’

);

(13)

Drzewo składni

<SFW>

SELECT <SelLista> FROM <FromLista> WHERE <Warunek>

title StarsIn <Atrybut> (< Zapytanie>) starName <SFW>

SELECT <SelLista> FROM <FromLista> WHERE <Warunek>

(14)

Otrzymane wyrażenie „algebry relacji”



^title



StarsIn <warunek>

<krotka> IN



^name



birthdate LIKE ‘%1960’

(15)

Wstępny logiczny plan zapytania



^title



starName=name

StarsIn



name



MovieStar



(16)

Poprawiony logiczny plan zapytania



^title

starName=name

StarsIn



name



Pytanie:

Przesunąć rzutowanie do StarsIn?

(17)

Szacowanie wielkości wyniku

Dobrze znać wielkość StarsIn

MovieStar





(18)

Pierwszy plan fizyczny

Parametry: kolejność złączania, ilość RAM, wynikowe atrybuty...

Hash join

Full scan Index unique

scan Parametry:

warunek selekcji,...

StarsIn MovieStar

(19)

Szacowanie kosztów

L.P.Z

P₁ P₂ … P_n C₁ C₂ … C_n Wybierz najniższy!

(20)

Optymalizacje w algebrze relacji

• Reguły transformacji ...

• ... zachowujące semantykę

• Które transformacje są dobre?

– Zachowują semantykę

– Dają szansę na poprawę efektywności – Nie obniżą efektywności

(21)

Złączenie naturalne

• Przemienność

R S = S R

• Łączność

(R S) T = R (S T)

• Podobne właściwości dla

– Iloczynu kartezjańskiego – Sumy mnogościowej

– Przecięcia

(22)

Właściwości

• Przenoszą nazwy atrybutów do wyniku, więc ich porządek nie ma znaczenia

• Można je zapisywać także jako drzewa

T R

R S S T

(23)

R x S = S x R

(R x S) x T = R x (S x T) R U S = S U R

R U (S U T) = (R U S) U T

Łączność i przemienność

R S = S R

(R S) T = R (S T)

(24)

Selekcja



^p1p2^{(R) =}



^p1vp2^{(R) =}



^p1^[



^p2^(R)]

[



^p1^{(R)] U [}



^p2^(R)]

(25)

Zbiory a wielozbiory

R = {a,a,b,b,b,c}

S = {b,b,c,c,d}

R U S = ?

• Opcja 1 SUM

R U S = {a,a,b,b,b,b,b,c,c,c,d}

• Opcja 2 MAX

R U S = {a,a,b,b,b,c,c,d}

(26)

Opcja 2 (MAX) powoduje prawdziwość:



^p1vp2^{(R) =}



^p1^{(R) U}^MAX



^p2^(R)

Przykład: R={a,a,b,b,b,c}

p1 spełnione przez a,b; p2 przez b,c



^p1vp2 (R) = {a,a,b,b,b,c}



^p1(R) = {a,a,b,b,b}



^p2(R) = {b,b,b,c}

(27)

SUM też ma sens (może nawet większy?)

Senators (……) Rep (……)

Ile jest parlamentarzystów z Kalifornii?

T1 =



^yr,state Senators; T2 =



^yr,state^Reps

T1 Yr State T2 Yr State 97 CA 99 CA

99 CA 99 CA 98 AZ 98 CA

(28)

Trzeba podjąć decyzję

-> Używamy opcji SUM do sumowania wielozbiorów

-> Niektórych reguł nie da się zastosować dla wielozbiorów (np. dla selekcji

alternatywą)

(29)

Rzutowanie

Niech: X = zbiór atrybutów Y = zbiór atrybutów XY = X U Y



^xy^{(R) =}



^x^[



^y^(R)]

(30)

p = warunek tylko na atrybutach R q = warunek tylko na atrybutach S

m = warunek na atrybutach R i S łącznie



^p^(R⋈ S) =[



^p^(R)]⋈ S



^(R⋈ S) = R ⋈ [



^(S)]

Reguły dla i ⋈

(31)



^pq^(R⋈ S) =[



^p^(R)]⋈



^q^(S)]



^pqm^(R_{⋈ S) =}



^m^([



^p^(R)]_{⋈ [}



^q^(S)])



^pvq^(R⋈ S) = [^p(R) ⋈ S] U [R ⋈ ^q (S)]

Reguły dla i ⋈

(32)

Reguły dla i

x = podzbiór atrybutów R

z = atrybuty w warunku P (też fragment R)



^x

^[ 

^{p (}^R⁾

^{] =}  

^x

{

^p

^[  

^xz^{x (}^R⁾

^] }

(33)

x = podzbiór atrybutów R y = podzbiór atrybutów S

z = przecięcie zbiorów atrybutów R i S



^xy^(R

⋈

^S)

⁼



^xy

{ ^[ 

^{xz (}^R⁾

^] ⋈ ^[ 

^{yz (}^S⁾

^] }

Reguły dla i ⋈

(34)



^xy

{

^p ^(R

⋈

^S)

} =



^xy

{

^p ^[



^xz’^(R)

_⋈ 

^yz’^(S)]

^}

z’ = z U

{

atrybuty użyte w p

}

(35)

Iloczyn kartezjański, selekcja i rzut

• Analogicznie, bo to szczególny

przypadek złączenia naturalnego, np.



^p (R  S) =[



^p^{(R)]  S}



^q (R  S) = R  [



^q^(S)]

(36)



^p^{(R  S)} ⁼



^p^{(R) }



^p^(S)



^p^{(R  S)} ⁼



^p^{(R)  S =}



^p^{(R) }



^p^(S)

Reguły dla ,, 

(37)



^p1p2^{(R) }



^p1^[



^p2^(R)]



^p^(R

_⋈

^{S)  [}



^p^(R)]

_⋈

^S

R

⋈

^{S  S}

⋈

^R



^x^[



^p ^{(R)] }



^x

{

^p^[



^xz ^(R)]

^}

Które transformacje są do dobre?

(38)

Na zdrowy rozum: rób rzuty wcześnie

R(A,B,C,D,E) x={E}

P: (A=3)  (B=“cat”)



^E ^{



^p (R)} czy



^E

{

^p^{



^ABE ^(R)}

}

(39)

A co jeśli mamy indeks(y) na A i B?

B = “cat” A=3

Przetnij zbiory wskaźników żeby dostać zbiór pasujących krotek

(40)

Podsumowanie transformacji

• Mało transformacji zawsze dobrych

• Zwykle dobre: wczesna selekcja

• Inne możliwości

– Eliminacja wspólnych podwyrażeń – Eliminacja duplikatów lub ...

– ... Eliminacja eliminacji duplikatów

(41)

Szacowanie kosztu planu wykonania

(1) Szacowanie rozmiaru wyniku

(2) Szacowanie liczby operacji wejścia-wyjścia

(42)

Szacowanie wielkości wyniku

• Statystyki dla relacji R

– T(R) : liczba krotek R

– S(R) : liczba bajtów w każdej krotce R

– B(R): liczba bloków zajmowanych przez R – V(R, A) : liczba różnych wartości atrybutu A

(43)

Przykład

R A: 20-bajtowy napis B: 4-bajtowy integer C: 8-bajtowa data

D: 5-bajtowy napis

A B C D cat 1 10 a cat 1 20 b dog 1 30 a dog 1 40 c bat 1 50 d

T(R) = 5 S(R) = 37

V(R,A) = 3 V(R,C) = 5 V(R,B) = 1 V(R,D) = 4

(44)

Szacunki rozmiaru dla W = R1  ^R2

T(W) = S(W) =

T(R1)  T(R2) S(R1) + S(R2)

(45)

S(W) = S(R) T(W) = ?

Szacunki rozmiaru dla W = 

^A=a

^(R)

(46)

Przykład

R V(R,A)=3

V(R,B)=1 V(R,C)=5 V(R,D)=4

W =



(R) T(W)

A B C D cat 1 10 a cat 1 20 b dog 1 30 a dog 1 40 c bat 1 50 d

T(R)

(47)

Założenie 1: równomierny rozkład

W = 

_Z=val

(R)

• Rozkład wartości atrybutu selekcjonującego Z jest równomierny wśród V(R,Z)

przyjmowanych wartości

(48)

Założenie 2: równomiernie w dziedzinie

W = 

_Z=val

(R)

• Rozkład wartości atrybutu selekcjonującego Z jest równomierny wśród DOM(R,Z)

wartości z dziedziny atrybutu Z

(49)

Przykład

R Wg założenia 2:

V(R,A)=3 DOM(R,A)=10 V(R,B)=1 DOM(R,B)=10 V(R,C)=5 DOM(R,C)=10 V(R,D)=4 DOM(R,D)=10 A B C D

cat 1 10 a cat 1 20 b dog 1 30 a dog 1 40 c bat 1 50 d

W =



^z=val(R) T(W) = T(R)

DOM(R,Z)

(50)

C=val  T(W) = (1/10)1 + (1/10)1 + ...

= (5/10) = 0.5

B=val  T(W)= (1/10)5 + 0 + 0 = 0.5

A=val  T(W)= (1/10)2 + (1/10)2 + (1/10)1 = 0.5

(51)

Selektywność selekcji ;)

SC(R,A) = średnia liczba rekordów

spełniających warunek równościowy na R.A T(R)

V(R,A) SC(R,A) =

T(R)

DOM(R,A)

(52)

A co z W = 

_{z  val}^{(R) ?}

T(W) = ?

• Rozwiązanie 1:

T(W) = T(R)/2

• Rozwiązanie 2:

(53)

• Rozwiązanie 3: Oszacuj wartości w zakresie

Przykład R _Z

Min=1 V(R,Z)=10 W= _{z  15}^(R)

Max=20

f = 20-15+1 = 6 (ułamek zakresu) 20-1+1 20

T(W) = f  T(R)

(54)

Szacowanie zakresu

• fV(R,Z) = liczba różnych wartości

T(W) = = f  T(R)

• Bardziej dokładnie histogramy wartości

– Problemy z ponownym użyciem planu

[f  V(Z,R)] T(R) V(Z,R)

(55)

Szacunki rozmiaru dla W = R1 ⋈ ^R2

x = atrybuty R1 y = atrybuty R2

• Przypadek 1: X  Y = 

– Tak samo jak R1



^R2

(56)

Przypadek 2: W = R1 ⋈R2 gdy XY={A}

R1 A B C R2 A D

Założenie (zależność inkluzyjna):

V(R1,A)  V(R2,A)  Każda wartość R1.A jest także w R2.A

V(R2,A)  V(R1,A)  Każda wartość R2.A jest

(57)

R1 A B C R2 A D

Szacunek T(W)

gdy V(R1,A)  V(R2,A)

Weź1 krotkę Dopasuj

1 krotka pasuje do T(R2)krotek V(R2,A)

więc T(W) = T(R2)  T(R1) V(R2, A)

(58)

Wielkość złączenia

• V(R1,A)  V(R2,A) T(W) = T(R2) T(R1)

V(R2,A)

• V(R2,A)  V(R1,A) T(W) = T(R2) T(R1) V(R1,A)

(59)

Przy rownomierności w dziedzinie

R1 A B C R2 A D

Ta krotka pasuje do T(R2)/DOM(R2,A) krotek, więc

T(W) = T(R2) T(R1) = T(R2) T(R1)

DOM(R2, A) DOM(R1, A)

(60)

Wielkość krotki wyniku (zawsze)

S(W) = S(R1) + S(R2) - S(A)

rozmiar atrybutu A

(61)

Pośrednie szacunki T,S,V też potrzebne

Np. W = [



^A=a^{(R1) ]}

⋈

^R2

Relacja U

T(U) = T(R1)/V(R1,A) S(U) = S(R1) Potrzebne też V (U, *) !!

(62)

Przykład

R1 V(R1,A)=3

V(R1,B)=1 V(R1,C)=5 V(R1,D)=3

U =



^A=a^(R1)

A B C D cat 1 10 10 cat 1 20 20 dog 1 30 10 dog 1 40 30 bat 1 50 10

V(U,A) =1 V(U,B) =1 V(U,C) = T(R1)

V(R1,A)

(63)

Przybliżenia dla U = 

_A=a

(R)

• Atrybut selekcji V(U,A) = 1

• Klucz

V(U,K) = T(U)

• Inne: ?

V(U,B) = V(R,B)

(64)

Dla złączeń U = R1(A,B) ⋈ ^R2(A,C)

• Zachowywanie zbiorów wartości:

V(U,A) = min { V(R1, A), V(R2, A) } V(U,B) = V(R1, B)

V(U,C) = V(R2, C)

(65)

Z = R1(A,B) ⋈ R2(B,C) ⋈ R3(C,D)

• Przykładowe statystyki

R1: T(R1) = 1000 V(R1,A)=50 V(R1,B)=100 R2: T(R2) = 2000 V(R2,B)=200 V(R2,C)=300 R3: T(R3) = 3000 V(R3,C)=90 V(R3,D)=500

(66)

T(U) = 10002000 V(U,A) = 50 200 V(U,B) = 100

V(U,C) = 300

Częściowy wynik U = R ⋈ ^S

(67)

Z = U ⋈ ^R3

T(Z) = 100020003000 V(Z,A) = 50 200300 V(Z,B) = 100

V(Z,C) = 90 V(Z,D) = 500

☺

Na szczęście wynik też nie zależy od kolejności złączeń

(68)

Podsumowanie szacowania

• Szacowanie wielkości wyniku jest sztuką (trochę tajemną)

• Nie zapomnieć o:

– okresowej aktualizacji statystyk (koszty?)

Systemy zarządzania bazami danych