Kapitel 9 - Datenbanksysteme Tübingen

Kapitel 9
Anfrageoptimierung
• Überblick
• Übersetzung von SQL in
relationale Algebra
• Vorauswahl logischer
Anfragepläne
• Auswahl eines physischen
Anfrageplans
1
Architektur und Implementierung von Datenbanksystemen | WS 2009/10
Melanie Herschel | Universität Tübingen
Überblick
Architektur eines DBMS
Figure inspired by Ramakrishnan/Gehrke: “Database Management Systems”, McGraw-Hill 2003.
Web Forms
Applications
SQL Interface
SQL Commands
Executor
Operator Evaluator
Transaction
Manager
Lock Manager
Parser
!
Optimizer
File and Access Methods
Buffer Manager
Disk Space Manager
!
data, files, indices, ...
!
!
!
Recovery
Manager
DBMS
Database
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
2
Überblick
SQL Anfrage
Parsing
• Parsen der Anfrage (Syntax)
• Überprüfung der Elemente (Semantik)
Parsing
• Parserbaum
Wahl des logischen Anfrageplans
• Baum mit logischen Operatoren
• Potentiell exponentiell viele
• Wahl des optimalen Plans
• Logische Optimierung
Wahl des logischen
Anfrageplans
• Regelbasierte Optimierung
• Kostenbasierte Optimierung
Wahl des physischen Anfrageplans
• Ausführbar
• Programm mit physischen Operatoren (siehe Kapitel 6 und 7)
Wahl des physischen
Anfrageplans
• Wahl des optimalen Plans
• physische Optimierung
Anfrageplan ausführen
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
3
Allgeimener Prozess am Beispiel
Tabellen und Anfrage
Beispiel Schema und Anfrage
SQL Anfrage
Schema
Sailors(sid:integer, sname:string, rating:integer, age:real)
Boats(bid:integer, bname:string, color:string)
Reserves(sid:integer, bid:integer, day:dates, rname:string)
Parsing
Anfrage
SELECT
FROM
WHERE
S.sid, MIN(R.day)
Sailors S, Reserves R, Boats B
S.sid = R.sid AND R.bid = B.bid
AND B.color = ‘red’
AND S.rating = (SELECT MAX(S2.rating)
FROM Sailors S2)
GROUP BY S.sid
HAVING
COUNT(*) > 1
Wahl des logischen
Anfrageplans
Wahl des physischen
Anfrageplans
Anfrageplan ausführen
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
4
Allgeimener Prozess am Beispiel
Aufteilen der SQL Anfrage in Blöcke
Ein Block einer SQL Anfrage entspricht einer SQL Anfrage
ohne Schachtelung mit exakt einer SELECT/FROM/WHERE/
GROUP BY/HAVING Klausel.
SQL Anfrage
Parsing
Aufteilen der Anfrage in Blöcke
Blöcke
Innerer Block B1:
SELECT MAX(S2.rating)
FROM Sailors S2
Äußerer Block:
SELECT
S.sid, MIN(R.day)
FROM
Sailors S, Reserves R, Boats B
WHERE
S.sid = R.sid AND R.bid = B.bid
AND B.color = ‘red’
AND S.rating = Referenz zu B1
GROUP BY S.sid
HAVING
COUNT(*) > 1
Wahl des logischen
Anfrageplans
Wahl des physischen
Anfrageplans
Anfrageplan ausführen
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
5
Allgeimener Prozess am Beispiel
Übersetzung in die relationale Algebra
Übersetzen der Blöcke in relationale Algebra
SQL Anfrage
Am Beispiel von Block B2
Parsing
!S.sid, MIN(R.day)(
HAVINGCOUNT(*) > 2 (
GROUP BYS.sid(
"S.sid = R.sid # R.bid = B.bid # B.color = ‘red’ # S.rating = Wert von B1 (
Sailors ╳ Reserves╳Boats)
)
Wahl des logischen
Anfrageplans
)
)
Weitere Möglichkeiten der Übersetzung?
Wahl des physischen
Anfrageplans
Anfrageplan ausführen
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
6
Allgeimener Prozess am Beispiel
Vorauswahl logischer Anfragepläne
• Es gibt exponentiell viele Möglichkeiten für einen logischen
Anfrageplan.
• Die Auswahl wird reduziert, indem wir eine Vorauswahl
“guter” Pläne durchführen.
• Auch hier gilt: wir wollen Kardinalitäten minimieren.
Dafür werden Heuristiken verwendet.
SQL Anfrage
Parsing
Beispiele für Heuristiken bei Wahl logischer Anfragepläne
1. Selection pushen
...
"S.sid = R.sid # R.bid = B.bid # S.rating = Wert von B1 (
" S.rating = Wert von B1(Sailors) ╳ Reserves╳ " B.color = ‘red’ (Boats)
Wahl des logischen
Anfrageplans
)
...
2. Selection und Kreuzprodukt = Join
...
" B.color = ‘red’ # S.rating = Wert von B1 (
Sailors
⑅
S.sid = R.sid
Reserves
⑅
R.bid = B.bid
Boats)
Wahl des physischen
Anfrageplans
...
Anfrageplan ausführen
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
7
Allgeimener Prozess am Beispiel
Auswahl eines geeigneten physischen Anfrageplans
Diverse Freiheitsgrade für Wahl eines physischen Plans
SQL Anfrage
"S.sid = R.sid # R.bid = B.bid # B.color = ‘red’ # S.rating = Wert von B1 (
" S.rating = Wert von B1(Sailors) Reserves Boats)
Parsing
⑅
⑅
• Wahl eines Algorithmus für jeden Operator
⑅
--> Index Nested Loops Join oder Sort-Merge Join?
• Reihenfolge und Gruppierung von Operatoren.
⑅
⑅
(Reserves
Boats)
" S.rating = Wert von B1(Sailors) vs.
(" S.rating = Wert von B1(Sailors) Boats) Reserves
⑅
⑅
Wahl des logischen
Anfrageplans
• Zusätzliche Operatoren, die im logischen Plan nicht auftauchen.
Scan einer Tabelle,
Sortierung als Vorstufe zur Gruppierung
• Modus des Datentransports zwischen Operatoren
Temporäre Tabelle,
Pipeline mit Iterator
" Abschätzung der Kosten eines physischen Plans.
"Um nicht alle Pläne aufzählen zu müssen, Verwendung von
dynamischer Programmierung.
Wahl des physischen
Anfrageplans
Anfrageplan ausführen
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
8
Kapitel 9
Anfrageoptimierung
• Überblick
• Übersetzung von SQL in
relationale Algebra
• Vorauswahl logischer
Anfragepläne
• Auswahl eines physischen
Anfrageplans
9
Architektur und Implementierung von Datenbanksystemen | WS 2009/10
Melanie Herschel | Universität Tübingen
Übersetzung von SQL in die Relationale Algebra
Eine Grammatik für einen Teil von SQL
Anfragen
•<Anfrage> :: = <SFW>
•<Anfrage> :: = ( <SFW> )
•Mengenoperatoren fehlen
SFWs
•<SFW> ::= SELECT <ProjListe> FROM <FromListe> WHERE <Bedingung>
•Gruppierung, Sortierung etc. fehlen
Listen
•<ProjListe> ::= <Attribut>, <ProjListe>
•<ProjListe> ::= <Attribut>
•<FromListe> ::= <Relation>, <FromListe>
•<FromListe> ::= <Relation>
Bedingungen (Beispiele)
•<Bedingung> ::= <Bedingung> AND <Bedingung>
•<Bedingung> ::= <Tupel> IN <Anfrage>
•<Bedingung> ::= <Attribut> = <Attribut>
•<Bedingung> ::= <Attribut> LIKE <Muster>
Vollständig z.B. hier: http://docs.openlinksw.com/virtuoso/GRAMMAR.html
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
10
Übersetzung von SQL in die Relationale Algebra
Prüfung der Semantik
Während der Übersetzung wird semantische Korrektheit überprüft
• Existieren die Relationen und Sichten der FROM-Klausel?
• Existieren die Attribute in den genannten Relationen?
• Sind Attributnamen eindeutig?
• Korrekte Typen für Vergleiche?
• Aggregation korrekt?
• ...
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
11
Der SQL Parser
• Neben syntaktischer uns semantischer Analyse erstellt des Parser eine interne
Repräsentation der Anfrage.
• Diese interne Repräsentation ähnelt der Originalanfrage. Jede SELECT-FROM-WHERE
Klausel wird in einen Anfrageblock (query block) übersetzt.
• Quelldaten eines Anfrageblocks sind entweder Quelltabellen oder weitere Anfrageblöcke.
Anfrageblock
πProjListe
SELECT
FROM
WHERE
GROUP BY
HAVING
σhavingListe
ProjListe
R1, R2, ..., Rn
Bedingung
groupbyListe
havingListe
grpbygroupbyListe
σBedingung
×
R1
R2
...
Rn
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
12
Kapitel 9
Anfrageoptimierung
• Überblick
• Übersetzung von SQL in
relationale Algebra
• Vorauswahl logischer
Anfragepläne
• Auswahl eines physischen
Anfrageplans
13
Architektur und Implementierung von Datenbanksystemen | WS 2009/10
Melanie Herschel | Universität Tübingen
Vorauswahl logischer Anfragepläne
Ziel: Transformation der internen Darstellung
• Ohne die Semantik der Anfrage zu verändern.
• Zur effizienten Ausführung, insb. durch kleine Zwischenergebnisse.
Methode: Äquivalente Umformung einer relationalen Anfrage
• Zwei Ausdrücke der relationalen Algebra sind äquivalent, falls sie
1.Die gleichen Operanden (=Relationen) verwenden und
2.Die Antwortrelation für jede mögliche Instanz der Datenbank identisch ist.
• Heuristiken bestimmen, welche Form eines relationalen Ausdrucks potentiell zu der geringsten
Kardinalität führt.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
14
Äquivalente Umformungen der relationalen Algebra
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
15
Äquivalente Umformungen der relationalen Algebra
Kommutativität & Assoziativität Selektion
R!S=S!R
(R ! S) ! T = R ! (S ! T)
R∪S=S∪R
(R ∪ S) ∪ T = R ∪ (S ∪ T)
R∩S=S∩R
(R ∩ S) ∩ T = R ∩ (S ∩ T)
R⋈S=S⋈R
(R ⋈ S) ⋈ T = R ⋈ (S ⋈ T)
Projektion
σc1 ∧ c2(R) = σc1(σc2(R))
#L(R ⋈ S) = #L(#M(R) ⋈ #N(S))
σc1 ∨ c2(R) = σc1(R) ∪ σc2(R)
gilt nicht bei Multimengen
#L(R ! S) = #L(#M(R) ! #N(S))
σc1(σc2(R)) = σc2(σc1(R))
σc(R op S) ≡ (σc(R)) op (σc(S))
für op ∈ {∪, ∩,"}
#L(σc (R)) = #L(σc (#MR)
#L(σc (R)) = σc (#L(R))
wenn c nur Attribute aus L
referenziert
σc(R ⋈ S) ≡ σc(R) ⋈ S
wenn c nur Attribute von R referenziert
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
15
Vorauswahl logischer Anfragepläne
Schritt 1
• Subanfragen auflösen.
Schritt 2
• Umformung des Baums gemäß Transformationsregeln.
• Heuristiken, die eine Vorauswahl “guter” logischer Anfragepläne treffen
• Selektion pushen
• Projektion pushen
• Selektion + Kreuzprodukt = Join
• Duplikateliminierung verschieben
• ...
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
16
Vorauswahl logischer Anfragepläne
Schritt 1: Subanfragen auflösen
• SQL erlaubt verschiedene Arten, geschachtelte Anfragen zu formulieren.
• Wir unterscheiden zwischen korrelierten und unkorrelierten Anfragen.
• Eine korrelierte Anfrage ist oft zu einer unkorrelierten Anfrage äquivalent.
Korrelierte Anfrage
Unkorrelierte Anfrage
SELECT S.sname
FROM
Sailors S
WHERE EXISTS (
SELECT *
FROM Reserves R
WHERE R.bid = 103
AND S.sid = R.sid
)
SELECT S.sname
FROM
Sailors S
WHERE S.sid IN (
SELECT R.sid
FROM Reserves R
WHERE R.bid = 103
)
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
17
Vorauswahl logischer Anfragepläne
Schritt 1: Subanfragen auflösen
• Die Anfrageschachtelung bei der Anfragebearbeitung beizubehalten kann teuer werden.
• Für jedes Tupel der äußeren Anfrage wird die innere Anfrage ausgeführt (Nested Loops)
• Oft wird Schachtelung verwendet, um einen Join oder Semi-Join auszudrücken.
• Beim Umschreiben eines logischen Anfrageplans wird versucht, solche Situationen zu erkennen und aus
dem impliziten Join einen expliziten Join zu machen.
• Somit können Subanfragen aufgelöst werden, so dass wir auf die Optimierung eines einzigen SELECTPROJECT-JOIN Blocks zurückgreifen können.
Äquivalente Anfrage ohne Schachtelung
SELECT S.sname
FROM
Sailors S
WHERE EXISTS (
SELECT *
FROM Reserves R
WHERE R.bid = 103
AND S.sid = R.sid
)
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
18
Vorauswahl logischer Anfragepläne
Schritt 2: Heuristiken anwenden
Übersetzen der Blöcke in relationale Algebra - Selektion pushen
!S.sid, MIN(R.day)(
HAVINGCOUNT(*) > 2 (
GROUP BYS.sid(
"S.sid = R.sid # R.bid = B.bid # B.color = ‘red’ # S.rating = Wert von B1 (
Sailors ╳ Reserves╳Boats)
)
πS.sid, MIN(R.day)
)
)
σCOUNT(*) > 2
πS.sid, MIN(R.day)
grpbyS.sid
σCOUNT(*) > 2
"S.sid = R.sid # R.bid = B.bid
grpbyS.sid
×
"S.sid = R.sid # R.bid = B.bid # B.color = ‘red’ # S.rating = Wert von B1
×
Sailors
Reserves
" S.rating = Wert von B1
Boats
Sailors
"B.color = ‘red’
Reserves
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
Boats
19
Vorauswahl logischer Anfragepläne
Schritt 2: Heuristiken anwenden
Übersetzen der Blöcke in relationale Algebra - Selektion + Kreuzprodukt = Join
!S.sid, MIN(R.day)(
HAVINGCOUNT(*) > 2 (
GROUP BYS.sid(
"S.sid = R.sid # R.bid = B.bid # B.color = ‘red’ # S.rating = Wert von B1 (
Sailors ╳ Reserves╳Boats)
)
)
πS.sid, MIN(R.day)
)
πS.sid, MIN(R.day)
σCOUNT(*) > 2
σCOUNT(*) > 2
grpbyS.sid
grpbyS.sid
"S.sid = R.sid # R.bid = B.bid
⋈
×
" S.rating = Wert von B1
Sailors
⋈
"B.color = ‘red’
Reserves
Boats
" S.rating = Wert von B1
Sailors
"B.color = ‘red’
Reserves
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
Boats
20
Vorauswahl logischer Anfragepläne
Schritt 2: Heuristiken anwenden
Übersetzen der Blöcke in relationale Algebra - Projektion pushen
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
21
Kapitel 9
Anfrageoptimierung
• Überblick
• Übersetzung von SQL in
relationale Algebra
• Vorauswahl logischer
Anfragepläne
• Auswahl eines physischen
Anfrageplans
22
Architektur und Implementierung von Datenbanksystemen | WS 2009/10
Melanie Herschel | Universität Tübingen
Ermitteln des “Besten” Physischen Anfrageplans
• In diesem Schritt der Optimierung werden prinzipiell
1. Alle möglichen Pläne entlang aller Freiheitsgrade (siehe Folie 8) enumeriert (die
nicht bereits von Heuristiken auf logischer Ebene gefiltert wurden).
2. Die Kosten jedes Plans geschätzt.
3. Der Plan mit den geringsten Kosten als endgültiger Plan ausgewählt.
• Ein DBMS betrachtet verschiedenste Faktoren in seinem Kostenmodell
• Die Anzahl Disk-I/Os, die zur Evaluation des Plans nötig sind,
• Die CPU Kosten,
• Die bei einem Datenbank-Client beobachtete Antwortzeit,
• Die Gesamtlaufzeit
• Ein kostenbasierter Optimieren versucht, diese Kosten zu antizipieren um den
günstigsten Plan zu bestimmen.
• Die oben genannten Faktoren hängen maßgeblich von der Größe eines
(Zwischen-)Ergebnisses einer Anfrage ab.
• Aus diesem Grund ist das Schätzen von Operator- und Anfragekardinalitäten
(siehe Kapitel 8) zentral für die Kostenbestimmung.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
23
Join Optimization
• We’ve now translated the query into a graph of query
blocks.
• Query blocks essentially are a multi-way Cartesian
Join-Optimierung
product with a number of selection predicates on top.
• We can logischer
estimate the cost of a given execution plan.
Ebene “voroptimierter” Plan eines Anfrageblocks.
• Gegeben ein• auf
Use result size estimates in combination with the cost
Joins (statt Kreuzprodukt + Selektion) eingeführt.
• Unter Anderemforwurden
individual
join algorithms discussed in previous
Regel teuerster Operator.
• Join ist in der chapters.
sich die
beste Join-Reihenfolge.
• Daher
We konzentriert
are now ready
to Optimierung
enumerateauf
alldie
possible
execution plans, i.e.,
enumerieren
dafür alle
möglichen
2-Weg-Join-Reihenfolgen
pro Anfrageblock.
• Wirall
possible 2-way
join
combinations
for each query block.
Query Optimization
Torsten Grust
Query Optimization
Search Space Illustration
Dynamic Programming
Example: Four-Way Join
Algorithm
Discussion
Left/Right-Deep vs. Bushy
Greedy join enumeration
Ways of building a 3-way join from two 2-way joins
Möglichkeiten, 3 Tabellen mittels Kombinationen von 2-Weg-Joins zu joinen (--> 3-Weg-Join)
�
�
�
S
R
�
T
S
�
S
�
T
R
R
�
S
T
R
�
S
S
T
�
�
�
R
�
�
T
T
�
R
T
T
�
�
R
S
T
�
S
S
�
�
T
R
R
S
T
R
�
�
S
�
�
�
T
S
S
R
10.9
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
24
Anzahl Join-Kombinationen
• Ein Join über n+1 Relationen R1, ..., Rn+1 benötigt
⋈
n binäre (2-Weg-)Joins.
2 Subpläne, k Joins
• Der Wurzeloperator des Plans joinedCatalan
Numbers
die jeweils k und n - k - 1 Join-Operatoren
enthalten (0 $ k $ n -1)
n - k - 1 Joins
R1, ..., Rk
Rk+1, ..., Rn+1
This recurrence relation is satisfied by Catalan numbers:
• Sei Ci die Anzahl Möglichkeiten, einen binären
Baum mit i inneren Knoten (Join-Operatoren) zu
erstellen:
Cn =
n−1
�
k=0
Ck · Cn−k−1 =
(2n)!
,
(n + 1)!n!
describing the number of ordered binary trees with n + 1 leaves.
Fornoch
each of these trees, we can permute the input relations
• Diese Anzahl Kombinationen beinhaltet
(2n)!
(2n)!
nicht die möglichen Permutationen der
(why?) R1 , . . . , Rn+1 , leading to:
· (n + 1)! =
(n
+
1)!n!
n + 1 Basisrelationen!
n!
Number of possible join trees for an (n + 1)-way relational join
(2n)!
(2n)!
· (n + 1)! =
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie(n
Herschel
| Universität Tübingen
+ 1)!n!
n!
25
Anzahl Join-Kombinationen
Die Anzahl Join-Kombinationen ist enorm, und wir haben alle
weiteren Freiheitsgrade noch nicht berücksichtigt!
Anzahl Join-Kombinationen von n Relationen
n
Cn-1
Anzahl Join-Bäume
2
1
2
3
2
12
4
5
120
5
14
1,680
6
42
30,240
7
132
665,280
8
429
17,297,280
10
4,862
17,643,225,600
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
26
Anzahl Join-Kombinationen
Einschränkung des Suchraums mittels dynamischer Programmierung
Der traditionelle Ansatz, den durch die Anzahl möglicher Join-Bäume gegebenen
Suchraum zu bewältigen verwendet das Prinzip der dynamischen
Programmierung (System R).
Idee
• Finde den günstigsten Plan für einen n-Weg-Join in n Passes.
• In jedem Pass k, finde den besten Plan für alle Subanfragen mit k Relationen.
• Erstelle die Pläne in Pass k aus den besten Subplänen für i Relationen und
k - i Relationen, die in vorhergehenden Passes (1 $ i < k) identifiziert wurden.
Annahme
• Um den besten globalen Plan zu identifizieren reicht es aus, nur die optimalen
Pläne desses Subanfragen zu betrachten (“Prinzip der Optimalität).
Weitherführende Literatur
P. G. Selinger, M. M. Astrahan, D. D. Chamberlin, R. A. Lorie, T. G. Price. Access Path
Selection in a Relational Database Management System. SIGMOD 1979
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
27
Anzahl Join-Kombinationen
Einschränkung des Suchraums mittels dynamischer Programmierung
Beispiel: 4-Weg-Join der Tabellen R1, ..., R4
Pass 1 (beste Pläne mit einer Relation)
Finde den besten Zugriffspfad zu jeder Relation Ri (betrachtet Index-Scan, Table-Scan)
Pass 2 (beste Pläne mit zwei Relation)
Für jedes Paar von Relationen Ri und Rj (wobei i % j und 1 $ i $ 4, 1 $ j $ 4) identifiziere die beste
Reihenfolge für den Join zwischen Ri und Rj (also Ri ⋈ Rj oder Rj ⋈ Ri?).
optPlan({Ri, Rj}) = bestPlan(Ri ⋈ Rj, Rj ⋈ Ri)
#12 Pläne sind zu betrachten.
Pass 3 (beste Pläne mit drei Relation)
Für jedes Tripel von Relationen Ri, Rj und Rk (wobei i, j, k unterschiedlich und zwischen 1 und 4)
identifiziere die beste Reihenfolge für den Join zwischen drei Tabellen, basierend auf bisher
identifizierten Subplänen (mit 1 bzw. 2 Relationen)
optPlan({Ri, Rj, , Rk}) = bestPlan(Ri ⋈ optPlan({Rj, Rk}),
optPlan({Rj, Rk}) ⋈ Ri,
Rj ⋈ optPlan({Ri, Rk}),
... )
# 6 Pläne z.B. für i = 1, j = 2, k = 3. Weitere Kobinationen von (i, j, k) sind (1, 3, 4), (2, 3, 4) und
(1, 2, 4) # 24 Pläne sind insgesamt zu betrachten.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
28
Anzahl Join-Kombinationen
Einschränkung des Suchraums mittels dynamischer Programmierung
Beispiel: 4-Weg-Join der Tabellen R1, ..., R4
Pass 4 (beste Pläne mit vier Relationen)
Für jede Menge aus vier Relationen Ri, Rj, Rk und Rl, identifiziere die beste Reihenfolge für den
Join zwischen vier Tabellen, basierend auf bisher identifizierten Subplänen.
optPlan({Ri, Rj, , Rk, , Rl}) = bestPlan( Ri ⋈ optPlan({Ri , Rk, Rl}), ...
optPlan({Ri, Rj}) ⋈ optPlan({Rk, Rl}), ... )
#24 Pläne sind zu betrachten.
• Insgesamt betrachten wir 60 Pläne, statt der 120 Pläne, die für einen Join von 4 Tabellen
möglich sind (siehe Folie 26).
• Zwischenergebnisse vohergehender Passes werden in einer temporären Tabelle
gespeichert, so dass optPlan( ) nicht bei jedem Aufruf neu evaluiert werden muss.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
29
Anzahl
Join-Kombinationen
Sharing Under the Optimality Principle
Query Optimization
Einschränkung des Suchraums mittels dynamischer Programmierung
Torsten Grust
Sharing optimal sub-plans Join Ordering Dynamic Programming
Wiederverwendung optimaler Subbäume
Search Space with Sharing under Optimality Principle
{R1 , R2 , R3 , R4 }
Query Optimization
Search Space Illustration
Dynamic Programming
Example: Four-Way Join
{R1 , R2 , R4 }
{R1 , R3 , R4 }
{R1 , R2 , R3 }
{R2 , R3 , R4 }
{R1 , R4 }
{R1 , R3 }
{R1 , R2 }
{R2 , R3 }
{R2 , R4 }
{R3 , R4 }
R1
Drawing by Guido Moerkotte, U Mannheim
R2
R3
R4
Algorithm
Discussion
Left/Right-Deep vs. Bushy
Greedy join enumeration
151 / 575
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
30
10.16
Algorithmus zur Dynamische Programmierung
Finde optimalen n-Weg Join-Baum mittels dynamischer Programmierung
function: findeJoinBaumDP( q(R1, R2, ..., Rn) )
for i = 1 to n do
begin
optPlan({Ri}) := Mögliche Zugriffspläne für Ri;
optPlan({Ri}) := bestPlan(optPlan({Ri}));
end
for i = 2 to n do
begin
foreach S ⊆ {R1, R2, ..., Rn} such that |S| = i do
begin
optPlan(S) := ∅;
foreach O ⊂ S mit O ≠ ∅ do
optPlan(S) := optPlan(S) ∪ moeglicheJoins(optPlan(O), optPlan(S \ O) );
optPlan(S) := bestPlan(optPlan(S));
end
end
return optPlan({R1, R2, ..., Rn});
•
moeglicheJoins(R, S) enumeriert alle möglichen Joins zwischen R und S (Nested Loops Join,
Merge Join, etc. ).
•
bestPlan(Menge) verwirft alle Pläne der Eingabemenge, bis auf die besten Pläne.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
31
Anzahl Join-Kombinationen
Weitere Einschränkungen des Suchraums
Bisher: Einschränkung durch dynamische Programmierung
• findeJoinBaumDP() schränkt den Suchraum frühzeitig ein.
• In unserem Beispiel filtern wir bereits in Pass 2 die Hälfte aller Möglichkeiten.
• In Pass 3 wird der Suchraum um einen weiteren Faktor 6 verringert.
Zusätzlich: Einschränkung durch Heuristiken
• Vermeide Kreuzprodukte
• Betrachte nur left-deep Bäume (siehe nächste Folie)
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
32
Left-Deep, Right-Deep, und Bushy Trees
⋈
⋈
Rn
⋈
⋈
⋈
...
⋈
R1
⋈
Rn-1
Rn-2
left-deep
...
... ...
...
bushy
(alles andere)
⋈
⋈
R2
R3
...
right-deep
• In der Praxis werden oft left-deep Bäume bevorzugt.
• Die innere (rechte) Relation ist immer eine Basisrelation.
• Erlaubt die Verwendung von Index Nested Loops Joins.
• Einfacher, Pipelining zu implementieren.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
33
Kosten der Dynamischen Programmierung
• Dynamische Programmierung hat immer noch exponentielle Komplexität
• Laufzeit: O(3n)
• Speicherbedarf: O(2n)
• Dies kann immer noch zu teuern sein, insb. wenn
• Wir eine große Anzahl Relationen joinen (∼ 10-20 und mehr),
• Für einfache Anfragen über indizierte Daten (wo die “richtige” Lösung leicht zu
finden sein sollte)
• In solchen Fällen empfiehlt sich der Algorithmus zur greedy join enumeration.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
34
Greedy Join Enumeration
• Grundidee ist, in der i-ten Iteration den günstigsten Plan auszuwählen, der aus
Ergebnissen der vorhergehenden Iterationen ( 1 bis i -1) gebildet werden kann.
Greedy Join Enumeration für einen n-Weg-Join
function: findeJoinBaumGreedy( q(R1, R2, ..., Rn) )
bisherigeOptPlaene := ∅;
for i = 1 to n do
bisherigeOptPlaene := bisherigeOptPlaene ∪ bester Zugriffspfad zu Ri;
for i = n downto 2 do
begin
//bisherigeOptPlaener = {P1, P2, ..., Pn} bei erster Iteration
finde Kombination zweier Pläne Pi, Pj, so dass die Kosten von Pi ⋈ Pj minimal sind;
bisherigeOptPlaene := bisherigeOptPlaene \ {Pi, Pj} ∪ {Pi ⋈ Pj};
end
// nun ist bisherigeOptPlaene = {P1}
return verbleibender Plan in bisherigeOptPlaene;
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
35
Anzahl Join-Kombinationen
Einschränkung des Suchraums durch Greedy Join Enumeration
Wiederverwendung von Subbäumen mit minimalen Kosten
(R1 ⋈ R2) ⋈ (R3 ⋈ R4)
hat minimale Kosten
in Pass 3
R3 ⋈ R4
hat minimale Kosten
in Pass 2
R1 ⋈ R2
hat minimale Kosten
in Pass 1
R1
R2
R3
R4
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
36
Kosten der Greedy Join Enumeration
• Der Greedy-Algorithmus hat eine Gesamtlaufzeit von O(n3)
• Die Schleife hat: O(n) Iterationen
• Jede Iteration betrachtet alle Paare in O(n2) innerhalb der verbleibenden
Menge von optimalen Plänen.
• Der Greedy-Algorithmus ist somit effizienter als der exponentielle
Algorithmus der dynamischen Programmierung.
• Durch die “greedy” Eigenschaft ist der endgültige Plan nicht unbedingt
der optimale (ist aber oft eine ausreichend gute Approximation).
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
37
Beispiel für Physische Anfragepläne
Annahmen
πsname
⋈sid
Selektion muss nicht separat
vor Join durchgeführt werden
sondern kann Teil des
optimalen Zugriffspfads zu
Reserves-Tupeln sein
(matching selection
condition, siehe Kapitel 7,
Folie 5)
" bid = 100
Reserves
"rating >
5
Sailors
Existierende Indizes (unculstered,
Speicheralternative (2) ):
•B+ Index über Sailors.rating
•Hash-Index über Sailors.sid
•B+ Index über Reserves.bid
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
38
Beispiel für Physische Anfragepläne
Pass 1
Wir betrachten 3 Zugriffspfade für Sailors, σrating > 5 mit einbezogen:
•B+ Baum
•Hash-Index
•Sequential Scan
" B+ Index entspricht Selektionskriterium, und dieser Zugriffspfad
ist mit hoher Wahrscheinlichkeit effizienter als die anderen beiden.
"Der gewählte Zugriffspfad für Sailors ist der B+ Baum, der Daten
bereits in sortierter Reihenfolge nach rating zurückgibt.
πsname
⋈sid
" bid = 100
Reserves
"rating >
5
Sailors
Zugriffspfade für Reserves in Pfad 1
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
39
Beispiel für Physische Anfragepläne
Pass 2
Join-Reihenfolge 1: Reserves ⋈ Sailors
• Wir benötigen nur Sailors, die die Selektionskriterien "rating > 5 und
" sid = Wert erfüllen, wobei Wert ein Wert der äußeren Relation Reserves
ist.
• sid = Wert entspricht dem Hash-Index über Sailors.sid.
• rating > 5 entspricht dem B+ Index über Sailors.rating.
" bid = 100
" Das Gleichheitsprädikat sid = Wert reduziert die Kardinalität
stärker als das Ungleichheitsprädikat, so dass der Hash-Index der
günstigere Zugriffspfad für die Kombination von Reserves mit
Sailors ist.
πsname
⋈sid
"rating >
Reserves
5
Sailors
Wir betrachten nun alle möglichen Join-Algorithmen (unter Berücksichtigung aller möglichen
Zugriffspfade):
•Nested Loops Join Varianten
•Hash-Join
•Sort-Merge Join
Join-Reihenfolge 2: Sailors ⋈ Reserves (analog zu Join-Reihenfolge 1)
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
40
Interessante Reihenfolgen
• In einigen Fällen (z.B. wenn wir einen B+ Index als Zugriffspfad verwenden oder
Sort-Merge Join anwenden) werden Daten sortiert zurückgegeben.
• Eine solche Sortierung kann für den späteren Verlauf der Anfrage interessant
sein (z.B. für eine Gruppierung, ORDER BY, folgende Joins).
• Die Sortierung ist somit eine für die Optimierung interessante physische
Eigenschaft und Optimierer annotieren Pläne mit solchen interessanten
Eigenschaften.
• In System-R wird das Konzept der interessanten Reihenfolgen eingeführt.
Diese sind bestimmt durch
• ORDER BY und GROUP BY Klauseln in der Anfrage
• Join-Attribute von Folge-Joins (ermöglicht effizientere Sort-Merge-Join)
• Beim Enumerieren von Anfrageplänen behalten wir sowohl
• Den günstigsten “unsortierten” Plan und
• Den günstigsten Plan für jede interessante Reihenfolge
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
41
“Picasso” Plan Diagrams
Optimierer in der Praxis
“Picasso” Diagramme
Generated by “Picasso”: SQL join query
parameterizable selectivities (0 . . . 100) a
• SQL Join Anfrage mit Selektionen
variabler Selektivität (0 - 100%) für
zwei Eingabetabellen.
• Eine farbige Fläche steht für einen
spezifischen physischen Anfrageplan,
der laut Optimierer optimal ist.
• Auf der Abbildung rechts sehen wir
klar definierte Flächen pro Plan, wie zu
erwarten wäre.
• Die Realität sieht leider nicht immer so
ideal aus (siehe folgende Folien)
Weitherführende Literatur
(a) Plan Diagram
Naveen Reddy and Jayant Haritsa. Analyzing Plan Diagrams of Database Query
Optimizers. VLDB 2005
Figure 1: Smooth Plan and Cost Di
42
� Naveen Reddy and Jayant Haritsa.
Analy
theh
The
Picasso ToolQuery Optimizers. VLDB 2005.
made
Database
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
coa
therefo
Que
“Picasso” Plan Diagrams
Optimierer in der Praxis
“Picasso” Diagramme
Generated by “Picasso”: SQL join query with filters of
parameterizable selectivities (0 . . . 100) aginst both join inputs
• Irreguläre Abgrenzung
• Komplexe Erscheinungspattern.
• Unzählig viele Pläne
Query
Searc
Dynam
Exam
Algo
Discu
Left/
Greed
(a) Plan Diagram
(a) Complex
Diagram
(b) CostPlan
Diagram
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
43
2: Complex Plan and Reduced Plan D
Figure 1: Smooth Plan and Cost Diagram (Query Figure
7)
� Naveen Reddy and Jayant Haritsa. Analyzing Plan Diagrams of
Optimierer in der Praxis
“Picasso” Diagramme
!"#$%&'()*+',-+.*$',-*+/01 +2#(34
!"#$%&'()*+',-+.*$',-*+/015+2#(34
!""
3#$%&&'()*+,-./&0
Inseln von Plan P3
12 34 5$,-$ .%&
12'346'5$,-$'.%&
,0+.7(0'5,/$,7'3!
innerhalb von P1
8+,(&09':.;&&7'<&((=')7('>.=.7/'?.%,/0.9'@Analyzing Plan Diagrams of Database Query OptimizersAB'CDEF'G""H
6)$%7+8'"9',,+:+;<+!'(),=',>*?*()9)+..+:+@@+ABBC
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
44
sso” Plan Diagrams
Optimierer in der Praxis
“Picasso” Diagramme
erated by “Picasso”: each distinct color represent a distinct plan
ure 7: Plan Switch-Point (Query 9, OptA)
Figure 9: Footprint Pattern (Query 7, OptA)
idered by the DBMS
“Interferenzen” in P2
8: Venetian Blinds Pattern (Query 9, OptB)
Figure 10: Speckle Pattern (Query 17, OptA)
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
ss the NATION , SUPPLIER and LINEITEM relaoth variations have almost equal estimated cost,
45
5.4 Speckle Pattern
Operating Picasso with Q17 on OptA (at its highest opti-
step-down
Query
at the 26
su
(on ps Tor
creases the
P2 to P3
creases by
to P1 at th
jumps up
Step-fu
ing input
when one
Querythe
O
within
Search
in the
aboS
Dynamic
switch-po
Example
havior
ma
Algorithm
optimizer,
space
eval
Discussio
The
ab
Left/Righ
arising
Greedyou
jo
we have a
non-mono
A specific
with OptA
duction in
result card
that all of
mated cos
put relatio
in the ass
non-mono
mizers.
Zusammenfassung
Parser
• Syntaktische und Semantische Analyse.
• Zerlegung der Anfrage in einzelne Blöcke, die als Baum von Operatoren der relationalen
Algebra dargestellt werden.
Optimierungen auf logischer Ebene
• Äquivalente Umformungen der logischen Baumrepräsentation.
• Auflösen geschachtelter Anfragen, wenn möglich.
• Vorfiltern möglicher Pläne basierend auf Heuristiken.
Physische Optimierung
• Enumerieren aller Möglichkeiten nicht machbar, da zu viele.
• Einschränkung des Suchraums z.B. mittels dynamischer Programmierung oder
Greedy Enumeration.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
46