Dr. Brigitte Mathiak Kapitel 11 Anfragebearbeitung.

Dr. Brigitte Mathiak

Kapitel 11

Anfragebearbeitung

2

Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

Lernziele

• Relationenalgebra

• Ablauf der Anfrageoptimierung

• Logische Optimierung• Umwandlung von SQL in Relationenalgebra• Heuristische Optimierung

• Physische Optimierung • Unterschiedliche JOIN Algorithmen

• Kostenbasierte Optimierung

3


Sehr hohes Abstraktionsniveau der mengenorientierten Schnittstelle (SQL).

Sie ist deklarativ, nicht-prozedural, d.h. es wird spezifiziert, was man finden möchte, aber nicht wie.

Das wie bestimmt sich aus der Abbildung der mengenorientierten Operatoren auf Schnittstellen-Operatoren der internen Ebene (Zugriff auf Datensätze in Dateien, Einfügen/Entfernen interner Datensätze, Modifizieren interner Datensätze).

Zu einem was kann es zahlreiche wie‘s geben: effiziente Anfrageauswertung durch Anfrageoptimierung.

i.A. wird aber nicht die optimale Auswertungsstrategie gesucht (bzw. gefunden) sondern eine einigermaßen effiziente Variante Ziel: „avoiding the worst case“

Optimierung von Datenbank- Anfragen: Grundsätze

4


Ablauf der Anfrageoptimierung

ScannerParser

Sichtenauflösung

Anfrage-Optimierer

CodeerzeugungAusführung

DeklarativeAnfrage

AlgebraischerAusdruck

Auswertungs-Plan (QEP)

Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra 5

Professoren

PersNr Name Ran

gRaum

2125 Sokrates C4 226

2126 Russel C4 232

2127 Kopernikus C3 310

2133 Popper C3 52

2134 Augustinus C3 309

2136 Curie C4 36

2137 Kant C4 7prüfen

MatrNr VorlNr Pers

Nr Note

28106 5001 2126 1

25403 5041 2125 2

Relationenmodell revisited: Grundbegriffe

DB: Relationen(tables)

Ausprägung

Í dom (PersNr) dom (Name) ... dom (Raum)

Schema

Tupel (Record, Row)

Attribut (Column)


Relationenmodell revisited: Definitionen

Gegeben sei eine Menge von Wertebereichen primitiver Datentypen {D1, ..., Dm}, die als "Domains" bezeichnet werden.

Eine Relation R ist ein Paar R = (s,v) mit

• einem Schema s = sch(R) = {A1, ..., An}, das aus einer Menge von Attributen (Attributnamen) besteht und für jedes Attribut Ai

einen Domain dom(Ai) {D1, ..., Dm} festlegt, und

• einer Ausprägung v = val(R) dom(A1) dom(A2) ... dom(An).


Relationenalgebra (RA): Operationen

Eine Operation der RA hat eine oder mehrere Relationen als Operanden und liefert eine Relation als Ergebnis(Abgeschlossenheit der Algebra)

Operationen in RA:

Mengenoperationen (Vereinigung, Durchschnitt, …) Zuweisung / Umbenennung Selektion, Projektion Joins Division


Mengenoperationen

Für zwei Relationen R, S mit sch(R) = sch(S) sind die üblichen Mengenoperationen definiert:

• Vereinigung (Union) R S:

• Durchschnitt (Intersection) R S:

• Differenz (Difference) R S:


Zuweisung

Idee: Umbenennung von Relationen und/oder einzelnen Attributen

Seien R, S Relationen mit

sch(R) = { A1, ..., An } und sch(S) = { B1, ..., Bn },

so dass für alle i gilt: dom(Ai) = dom(Bi).

Die Zuweisung R := S bedeutet: val(R) = val(S).

Ausführlicher schreibt man auch R(A1, ..., An) := S(B1, ..., Bn).


Umbenennung

Vereinfachte Form der Zuweisung:explizite Umbenennung von einzelnen Attributen oder Relationen

• Umbennung von einzelnen Attributen

[Voraussetzung Vorgänger] (voraussetzen)

(.. Attribut "Vorgänger" wird in "Voraussetzung" umbenannt)

• Umbenennung von Relationen

[V1] (voraussetzen)

(.. Relation "voraussetzen" wird in "V1" umbenannt)


Selektion

Selektion (Filterung, Auswahl von Zeilen einer Tabelle):

Das Resultat einer Selektion [F](R) auf einer Relation R ist:

Die Menge der möglichen Filterformeln F ist:

1) Für Attribute A, B aus sch(R) mit dom(A) = dom(B), Konstante c dom(A) und Vergleichsoperationen {=, , , , , } sind A B und A c zulässige Filterbedingungen.

2) Falls F1 und F2 zulässige Filterbedingungen sind, dann sind auch F1 F2, F1 F2, F1 und (F1) zulässig.

3) Nur die von 1) und 2) erzeugten Filterbedingungen sind zulässig.


Projektion

Projektion (Auswahl von Spalten einer Tabelle):

Sei A sch(R). Das Resultat einer Projektion [A](R) ist:


Selektion vs. Projektion

Selektion

Projektion


Selektion / Projektion: Beispiel

MatrNr Name Semester

24002 Xenokrates 18

25403 Jonas 12

Selektion:

Rang

C3

C4

Projektion:

[Semester > 10] (Studenten)

[Rang] (Professoren)

Studenten


24002 Xenokrates 18

25403 Jonas 12

26120 Fichte 10

26830 Aristoxenos 8

27550 Schopenhauer

6

28106 Carnap 3

29120 Theophrastos 2

29555 Feuerbach 2

Professoren

PersNr Name Rang Raum


2126 Russel C4 232


2133 Popper C3 52


2136 Curie C4 36

2137 Kant C4 7


Beispiel: Selektion/Projektion mit Umbenennung

Mengendurchschnitt nur auf zwei Argumentrelationen mit gleichem Schema anwendbar. Deshalb ist die Umbenennung des Attributes gelesenVon in PersNr in der Relation Vorlesungen notwendig.

Anfrage:

PersNr aller C4-Professoren, die mindestens eine Vorlesung halten.

Professoren



2126 Russel C4 232


2133 Popper C3 52


2136 Curie C4 36

2137 Kant C4 7

Vorlesungen

VorlNr Titel SWS gelesenVon

5001 Grundzüge 4 2137

5041 Ethik 4 2125

5043 Erkenntnistheorie 3 2126

5049 Mäeutik 2 2125

4052 Logik 4 2125

5052 Wissenschaftstheorie 3 2126

5216 Bioethik 2 2126

5259 Der Wiener Kreis 2 2133

5022 Glaube und Wissen 2 2134

4630 Die 3 Kritiken 4 2137


Natural Join

Natural Join: ||

Natürlicher Verbund von Relationen über gleiche Attributnamen und Attributwerte.

Das Resultat von R || S mit A = sch(R) und B = sch(S) ist:

P (X1, ..., Xm, Y1, ..., Yk)

Q (Y1,..., Yk, Z1, ..., Zn)

X1 X2 ... Xm Y1 Y2 ... Yk Z1 Z2 ... Zn

P |×| Q

P Q P Q Q P


Natural Join: Beispiel

||

Studenten


24002 Xenokrates 18

25403 Jonas 12

26120 Fichte 2

26830 Aristoxenos 8

27550 Schopenhauer

1

hören

MatrNr VorlNr

24002 5001

25403 5001

24002 4052

24002 5041

26830 5052

MatrNr Name Semester VorlNr

24002 Xenokrates 18 5001



25403 Jonas 12 5001

26830 Aristoxenos 8 5052


Kartesisches Produkt

Kartesisches Produkt: Seien R, S Relationen mit Schemata A = sch(R) und B = sch(S). Sei A' ein Schema, bei dem alle Ai, die auch in B vorkommen, unbenannt sind in R.Ai, und sei B' ein analoges Schema

mit Attributnamen S.Ai.

Das Resultat von R S ist:

X1 X2 ... Xm

P.Y1

P.Y2

…P.Y

k

Q.Y1

Q.Y2

…Q.Yk

Z1 Z2 ... Zn

P (X1,..., Xm, Y1,..., Yk)

Q (Y1,..., Yk, Z1,..., Zn)P × Q


Kartesisches Produkt: Beispiel

PersNr Name Rang Raum MatrNr VorlNr

2125 Sokrates C4 226 26120 5001

... ... ... ... ... ...

2125 Sokrates C4 226 29555 5001

... ... ... ... ... ...

2137 Kant C4 7 29555 5001

×

Problem: potentiell riesige Zwischenergebnisse

sch(Professoren) sch(hören)

Professoren



2126 Russel C4 232


2133 Popper C3 52


2136 Curie C4 36

2137 Kant C4 7

hören

MatrNr VorlNr

26120 5001

27550 5001

27550 4052

28106 5041

28106 5052

28106 5216

... ...


Äquivalenzregeln ("Rechenregeln") der RA

Kommutativitätsregeln:1) falls P nur R1-Attribute enthält2)

Assoziativitätsregel:3) Idempotenzregeln:4)

5)Distributivitätsregeln:6) 7) 8) falls P nur R-Attribute enthält9) falls Joinattribute10) R1 S1

Invertierungsregel:11)


Ausdrucksmächtigkeit der RA

Die Menge der relationenalgebraischen Ausdrücke über einer Menge von Relationen P1, ..., Pn ist wie folgt definiert:

(i) P1, ..., Pn sind Ausdrücke.

(ii) Wenn R, S, T, Q Ausdrücke sind, F eine Filterformel über sch(P) ist,

A sch(R), sch(S)=sch(T) und sch(R) sch(Q) gilt, dann sind

[F](R), [A](R), R |×| S, R × S, R |*| S, S T, S T, S - T, R Q

auch Ausdrücke.

(iii) Nur die von (i) und (ii) erzeugten Ausdrücke sind RA-Ausdrücke.

Satz:´ , , , , –, bilden eine minimale Menge von Operationen, mit denen sich alle Operationen der RA ausdrücken lassen.

Eine Anfragesprache heißt relational vollständig, wenn sich damit alle Anfragen der (minimalen) Relationenalgebra ausdrücken lassen.

Eigenschaften der Relationenalgebra (1/2)

Satz: Alle Ergebnismengen von Operationen der relationalen Algebra sind endlich. Beweisskizze:


Man zeige für alle Operationen ×, , , , – und , dass für endliche Eingaberelationen und beliebige Filterformeln das Ergebnis auch endlich ist. Beispiel: R × S: Sei die Anzahl der Tupel in R: r = |val(R)| und in S: s=|val(S)|. Dann ist sowohl r als auch s endlich (s. oben). Die Anzahl der Tupel in R × S: rxs = |val(R × S)| kann bestimmt werden als rxs ≤ r*s, da für jedes Element in val(R) höchstens s Ausprägungen in R × S entstehen können. rxs ist also endlich. □

Eigenschaften der Relationenalgebra (2/2)

• Mit ähnlichem Beweisschema gilt auch:

• Die Größe der Ergebnismenge von Operationen der relationalen Algebra kann sicher nach oben abgeschätzt werden, wenn die Größe der Eingaberelationen bekannt ist

• Die Laufzeit zur Auswertung eines Ausdrucks der Relationenalgebra kann sicher nach oben abgeschätzt werden, wenn sowohl die Größe der Eingaberelationen bekannt ist als auch die nach oben abgeschätzte Laufzeit des Berechnungsalgorithmen der eingesetzten Einzeloperationen


Offene Fragen:

• Kann SQL die Relationenalgebra abbilden?• JA, SQL ist relational vollständig

• Kann die Relationenalgebra SQL abbilden?• JEIN• Es kommt auf den Dialekt an. • Was bei den meisten fehlt sind

• Multimengen, • Aggregationen,• Gruppierung und• transitive Hülle

• Diese können allerdings mit geringem Zusatzaufwand eingefügt werden


25


Logische Optimierung

1. Übersetzen der SQL-Anfrage in die Relationenalgebra

2. Äquivalentes Umformen der RA um die Anfragebearbeitung zu beschleunigen

26


Allgemeingültige Übersetzung

SELECT A1, ..., An

FROM R1, ..., Rk

WHERE P

R1 R2

R3

Rk

27


Allgemeingültige Übersetzung (Beispiel)

SELECT Titel

FROM Professoren, Vorlesungen

WHERE Name = ´Popper´ AND

PersNr = gelesenVon

Professoren Vorlesungen

Problem:

Professoren Vorlesung hat ist ein sehr großes Zwischenergebnise

28


Erste Optimierungsidee

Professoren

Vorlesungen

SELECT Titel

FROM Professoren, Vorlesungen

WHERE Name = ´Popper´ AND

PersNr = gelesenVon

Das Zwischenergebnis ist nun deutlich kleiner.

29


Zur Erinnerung: Äquivalenzregeln der RA

Kommutativitätsregeln:1) falls P nur R1-Attribute enthält2)

Assoziativitätsregel:3) Idempotenzregeln:4)

5)Distributivitätsregeln:6) 7) 8) falls P nur R-Attribute enthält9) falls Joinattribute10) R1 S1

Invertierungsregel:11)

30


1. Mittels Regel 1 werden konjunktive Selektionsprädikate in Kaskaden von -Operationen zerlegt.

2. Mittels Regeln 2, 4, 6, und 9 werden Selektionsoperationen soweit "nach unten" propagiert wie möglich.

3. Mittels Regel 8 werden die Blattknoten so vertauscht, dass derjenige, der das kleinste Zwischenergebnis liefert, zuerst ausgewertet wird.

4. Forme eine -Operation, die von einer -Operation gefolgt wird, wenn möglich in eine Join-Operation um

5. Mittels Regeln 3, 4, 7, und 10 werden Projektionen soweit wie möglich nach unten propagiert.

6. Versuche Operationsfolgen zusammenzufassen, wenn sie in einem Durchlauf ausführbar sind (z.B. Anwendung von Regel 1, Regel 3, aber auch Zusammenfassung aufeinanderfolgender Selektionen und Projektionen zu einer Filter-Operation).

Heuristische Anwendung der Transformationsregeln

Selektion nach unten

Kleine Zwischenergebnisse

Besser Join als Kartesisch

Projektionen nach unten(jedoch über die Selektionen)

Operationen zusammenfassen

31


Anwendung der Transformationsregeln

SELECT distinct s.Semester

FROM Studenten s, hören h

Vorlesungen v, Professoren p

WHERE p.Name = ´Sokrates´ AND

v.gelesenVon = p.PersNr AND

v.VorlNr = h.VorlNr AND

h.MatrNr = s.MatrNr

s h

v

p

32


Aufspalten der Selektionsprädikate

s hv

ps h

v

p

33


Verschieben der Selektionsprädikate

s h

vp

s hv

p

34


Zusammenfassung von Selektionen und Kreuzprodukten zu Joins

s h

vp

s h

vp

35


Optimierung der JoinreihenfolgeKommutativität und Assoziativität ausnutzen

s

h

v

p

s h

v p

36


Was bringt das ?

s

h

v

p

s h

v p

13

13

4

1

3

4

37


Einfügen von (zusätzlichen) Projektionen

s

h

v

p

s

h

v

p

38


Physische Optimierung

• Ziel ist es gute Algorithmen für die Ausführung auswählen

• Bei Join: • Nested-Loop-Join• Index-Nested-Loop-Join• Merge-Join• Hash-Join

• Indexe und Vorsortierungen sollten möglichst gut ausgenutzt werden.

• Nach Bedarf werden Indexe und Sortierungen akut erstellt.

39


40


J1 nested (inner-outer) loop

• „brute force“-Algorithmus

foreach r Rforeach s S

if s.B = r.A then Res := Res (r s)

• Laufzeit: O(|R|*|S|) ~ O(n²)

• Keine Vorbedingungen

• Auch geeignet für das kartesische Produkt

• Fazit: Der einfachste und flexibelste Join

Implementierung von Join: Strategien

41


J2 Zugriffsstruktur auf SIndex Nested Loop Join

in jedem Durchlauf von R werden nur die in S qualifizierenden Tupel gelesen

Vorbedingung: ein Index auf B

foreach r Rforeach s S[B=r.A]

Res := Res (r s)

Laufzeit: O(|R|*query(S[B=r.A])*|S[B=r.A]|) Queryzeit ist O(log n) für B-Bäume und O(1) für Hash Wenn B Schlüssel ist, dann ist |S[B=r.A]|= 1 O(n) im besten Fall bis O(n²) im schlechtesten (r,s: r.A = s.B) Meistens O(n log n)


42


J3 Merge-Join• Vorbedingung: Beide Relationen sind passend sortiert. • 2 pointer r, s =0• while (R[r].exists() && S[s].exists())

{ if (R[r] == S[s]) { output.add(R[r]);r++;s++; }

else if (r< s) r++; else s++; }

• falls A oder B Schlüsselattribut ist, wird jedes Tupel in R und S nur genau einmal gelesen -> O(|R|+|S|) ~O(n)

• Kann sich sogar lohnen, wenn R oder S erst sortiert werden müssen


1 3 6 7 9 11 13

2 4 6 7 8 11 13

43


J4 Hash-Join

R und S werden mittels der gleichen Hashfunktion h – angewendet auf R.A und S.B – auf (dieselben) Hash-Buckets abgebildet

Hash-Buckets sind i.Allg. auf dem Hintergrundspeicher (abhängig von der Größe der Relationen)

Zu verbindende Tupel befinden sich dann im selben BucketWird (nach praktischen Tests) nur von J3 „geschlagen“, wenn

die Relationen schon vorsortiert sind


44


Übersetzung der logischen Algebra

R S

AR.A=S.B

R S

HashJoinR.A=S.B

R S

MergeJoinR.A=S.B

[SortR.A] [SortS.B]

R

S

IndexJoinR.A=S.B

[HashS.B | TreeS.B]

R

S

NestedLoopR.A=S.B

[Bucket]

45

Implementierung von Select: Strategien

• S1 Select • Brute-Force: Jedes Tupel wird einzeln überprüft• O(n)

• S2 IndexSelect• Der Index wird benutzt um die Anfrage schneller zu beantworten• O(1) bei Hash Index und Gleichheit• O(log n) bei B-Baum und Gleichheit• O(log n) bei B-Baum und Bereichsanfrage


46



P

R

SelectP

R

IndexSelectP

R

47

Implementierung der Projektion

• Wenn mit dem Projektionsergebnis weitergerechnet wird, muss das Zwischenergebnis dupliziert werden.

• Die Duplikation sollte nach Möglichkeit einen Index oder eine Sortierung erhalten, wenn diese noch weiter benötigt werden.


48



l

R

[NestedDup]

Projectl

R

[SortDup]

Sort

Projectl

R

[IndexDup]

[Hash | Tree]

Projectl

R

49


Wiederholung der Optimierungsphasen

select distinct s.Semester

from Studenten s, hören h

Vorlesungen v, Professoren p

where p.Name = ´Sokrates´ and

v.gelesenVon = p.PersNr and

v.VorlNr = h.VorlNr and

h.MatrNr = s.MatrNr

s h

v

p

p.Name = ´Sokrates´ and ...

s.Semester

50


s

h

v

s

h

v

51


Kostenbasierte Optimierung

Generiere alle denkbaren Anfrageauswertungspläne Enumeration

Bewerte deren Kosten Kostenmodell Statistiken Histogramme Kalibrierung gemäß verwendetem Rechner Abhängig vom verfügbaren Speicher Aufwands-Kostenmodell

- Durchsatz-maximierend- Nicht Antwortzeit-minimierend

Behalte den billigsten Plan

52


Tuning von Datenbanken

Statistiken (Histogramme, etc.) müssen explizit angelegt werdenAnderenfalls liefern die Kostenmodelle falsche Werte

In Oracle … analyze table Professoren compute statistics analyze table Professoren estimate statistics

In DB2 … runstats on table …

53


Analysieren von Leistungsengpässen

Geschätzte Kosten von

Oracle

54

Fazit


Datenbankoptimierung ist recht kompliziert

Die Besonderheiten der Anfragesprache erlauben es weitgehende Optimierungen durchzuführen.

Dabei ist vor allem die Reihenfolge der Operationen relevant.

Indexe ermöglichen es schnellere Operationen zu verwenden.

Über Verschachtelungen hinweg, kann es allerdings schneller sein, sie nicht zu benutzen.

Dr. Brigitte Mathiak Kapitel 11 Anfragebearbeitung.

Documents

Transcript of Dr. Brigitte Mathiak Kapitel 11 Anfragebearbeitung.