Post on 18-Aug-2019
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-1
Logische Optimierung
Höhere, nichtprozedurale Abfragesprachen (SQL,
QBE, ...) verlangen keine Kenntnisse des Benutzers
über die Implementierung, müssen aber in prozedurale
Form (z. B. Relationenalgebra) umgesetzt werden.
Um trotzdem effiziente Bearbeitung von Queries zu
erzielen wird die gestellte Anfrage intern umformuliert
und verbessert. Diesen Vorgang nennt man Query
Optimization.
Im Allgemeinen wird keine optimale Lösung erzielt,
sondern nur eine Verbesserung.
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-2
Query (z.B. SQL)
Query Prozessor
• Analyse • Umwandlung in relationale Algebra• Datenzugriff • Ausführung
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-3
Fragen:
Welche Operationen benötigen viel Zeit für ihre
Ausführung?
Können diese vermieden werden, indem man die
Anfrage neu formuliert?
Beispiel:
Gegeben sei folgender relationaler Ausdruck:
πA(σB=C∧D=”99”(AB × CD))
Dieser offensichtlich teuere Ausdruck (wg. kartesischem
Produkt) kann besser formuliert werden:
πA(σB=C(AB × σD=”99”(CD)))
Das kartesische Produkt in dieser Abfrage ist
offensichtlich durch die Selektion über B=C äquivalent
zu einem Gleichverbund:
πA(AB [B=C] σD=”99”(CD))
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-4
Grundlegende Aspekte
Zu betrachten sind für eine Optimierung die fünf
Grundoperationen. Wo liegt ihr Schwachpunkt und wie
können diese Schwachpunkte umgangen werden?
• Die auf jeden Fall aufwendigste Operation ist das
kartesische Produkt bzw. der Verbund:
Bei einfachster Implementierung eines Verbundes
zwischen A und B erfolgt ein Durchlauf aller Tupel von
B für jedes Tupel von A. Dies ist ein Aufwand mit
o(nm).
• Die Projektionen sind aufwendig durch das Entfernen
von Duplikaten.
• Die Selektionen sollte man so früh wie möglich
durchführen, da dies zu kleineren Zwischenresultaten
führt.
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-5
• Die unären Operationen (Projektion/Selektion)
bedingen je einen Durchlauf aller Tupel, daher
mehrere möglichst zusammenziehen oder mit einer
binären Operation zusammenfassen.
• Nach gemeinsamen Teilausdrücken suchen, damit
diese nur einmal abgearbeitet werden.
• Eventuell temporäre Verwendung bestimmter
Dateiorganisationen (Indizes, Sortieren) einführen
→ Physische Optimierung
Der Zeitaufwand für das Untersuchen der verschiedenen
Möglichkeiten ist im Allgemeinen viel geringer als für das
Durchführen einer ineffizienten Query. Daher wird die
Optimierung immer durchgeführt!
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-6
Algebraische Manipulation
• Gesetze der relationalen Algebra.
• Äquivalenz von Ausdrücken.
Es gilt E1 ≡ E2 falls sie dieselbe Abbildung
repräsentieren, d.h. falls dieselben Relationen für
identische Bezeichnungen in den beiden Ausdrücken
eingesetzt werden, erhallten wir gleiche Ergebnisse.
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-7
Vereinfachungen und gemeinsame Unterausdrücke
r ∪ r ≡ r r ∩ r ≡ r r r ≡ r
r - r ≡ ∅ r ∪ ∅ ≡ r r ∩ ∅ ≡ ∅ r ∅ ≡ ∅
r - ∅ ≡ r ∅ - r ≡ ∅
πx(∅) ≡ ∅ σc(∅) ≡ ∅ δN(∅) ≡ ∅ r[c]∅ ≡ ∅ ∅[c]r ≡ ∅
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-8
Beispiel:
(r - r) (r ∪ r)
≡ ∅ r
≡ ∅
Diese Regeln gelten auch für gemeinsame
Unterausdrücke:
(r s) ∪ (r s) ≡ r s
Operatorbäume oder Ausdrucksbäume
Mittels Operatorbäumen können gemeinsame
Unterausdrücke und äquivalente Ausdrücke leicht
erkannt werden.
Ein Operatorbaum ist folgendermaßen charakterisiert:
• Gerichteter Baum
• Die Knoten sind Operatoren
• Die Blätter sind Relationen oder Konstanten
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-9
Algebraische Optimierung Für die Durchführung der Optimierung wird die Anfrage zunächst in einen Operatorbaum umgewandelt: Beispiel: πx(s r) - πx(q r s)
–
πx
s
πx
r q
r s
Annahme: Bottom-up-Auswertung des Operatorbaumes
Aufwand:
Zeit- und Platzaufwand binärer Operationen steigen mit
o Anzahl der Tupel o Anzahl der Attribute
in den Argumentrelationen
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-10
(q r s)
assoziativ und kommutativ
s
r q
q
r s
srq
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-11
Grundprinzipien:
Es wird versucht, auf diesen Operatorbaum die
folgenden drei Grundprinzipien anzuwenden:
• Verlagerung unärer Operatoren in Richtung auf die
Blätter des Baumes
(Ziel: möglichst frühzeitige Reduktion der Größe
von Verbund-Operanden)
• Zusammenfassung unärer Operationen
• Da die Projektion Entfernung doppelter Tupel
bedingt, sollte man die Selektion möglichst vor der
Projektion durchführen (im Allgemeinen ist σ vor π
statistisch besser).
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-12
Zusammenfassung gleicher Teilausdrücke: –
πx
s
πx
r q
r s –
πx
s
πx
r
q
–
πx
s
πx
r
q
Das Problem bei der Feststellung gemeinsamer
Teilausdrücke ist, dass die algebraischen
Transformationen deren Existenz verschleiern können.
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-13
Umformungsregeln
Eine Reihe von Regeln, die auf der Relationalen Algebra
aufbauen, erlauben die Umformung der Anfrage:
Regeln für Verbund und kartesisches Produkt (1) Kommutativität E1 E2 ≡ E2 E1
E1 × E2 ≡ E2 × E1 (2) Assoziativität E1 (E2 E3) ≡ (E1 E2) E3 .
E1 × (E2 × E3) ≡ (E1 × E2) × E3 . Regeln für Selektion und Projektion (3) Zusammenfassung von Projektionen
Bedingungen: {Ai ⏐ i = 1, ..., n} ⊆ {Bi ⏐i = 1, ..., m}.
πAi,...,An(πB1,...,Bm(E)) ≡ πA1,...An(E). (3) Zusammenfassung/Kommutativität von
Selektionen: σF1(σF2(E)) ≡ σF2∧F1(E).
σF1(σF2(E)) ≡ σF2(σF1(E)).
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-14
(5) Kommutativität Selektion-Projektion Bedingung F bezieht sich nur auf Attribute Ai:
πA1,...,An(σF(E)) ≡ σF(πA1,...,An(E)). Bedingung F bezieht sich auf alle Attribute B und möglicherweise auf Attribute Ai:
πA1,...,An(σF(E)) ≡ πA1,...,An(σF(πA1,...,An,B1,...,Bm(E))).
(6) Kommutativität Selektion-Kartesisches Produkt Bedingung F = F1 ∧ F2 bezieht sich auf Attribute von E1 und E2
Fi bezieht sich nur auf Attribute von Ei σF(E1 x E2) ≡ σF1(E1) x σF2(E2).
F1 bezieht sich nur auf Attribute von E1, F2 bezieht sich auf Attribute von E1 und E2
σF(E1 x E2) ≡ σF2(σF1(E1) x E2). (7) Kommutativität Selektion-Vereinigung
σF(E1 ∪ E2) ≡ σF1(E1) ∪ σF2(E2). (8) Kommutativität Selekton-Mengendifferenz σF(E1 - E2) ≡ σF1(E1) - σF2(E2).
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-15
(9) Kommutativität Projektion-Kartesisches Produkt Bi sind Attribute von E1, Ci sind Attribute von E2.
{Ai | i = 1,..,n} = {Bi | i = 1,...,m} ∪ {Ci | i = 1,...,k}
πA1,...,An(E1 x E2) ≡ πB1,...,Bm(E1) x πC1,...,Ck(E2).
(10) Kommutativität Projektion-Vereinigung πA1,...,An(E1 ∪ E2) ≡ πA1,...,An(E1) ∪ πA1,...,An(E2).
Der Verbund ist hierbei darstellbar als Kombination von
kartesischem Produkt, Projektion und Selektion, deshalb
folgen die Regeln für Kommutativität von Selektion und
Verbund aus (4), (5) und (6).
Achtung: Es gilt keine Kommutativität zwischen
Mengendifferenz und Projektion!
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-16
Ein einfacher Optimierungsalgorithmus:
(1) Umformulierung der Anfrage, so daß nur noch Grundoperationen verwendet werden. (Auflösung der Verbünde und Divisionen)
(2) Zuordnung der Attribute zu den Relationen (Volle Qualifizierung)
(3) Aufstellen des Operatorbaumes
(4) Zerlegung der Selektionen der Art σF1 ∧ ... ∧ Fn(E)
nach Regel (4) in σF1(...(σFn(E))...).
(5) Verlagerung der Selektionen soweit wie möglich in Richtung Blätter mit Regeln (4) - (8). (Selektionen, die sich nur auf ein Attribut und eine Konstante beziehen, können fast immer zu ihrer Relation wandern)
(6) Zusammenfassung aller direkt aufeinanderfolgenden Selektionen zu einer einzigen Selektion
(7) Verlagerung der obersten Projektion mit Regeln (3), (5), (9) und (10) durch den Baum bis hin zu den Blättern. (Bei binären Operationen aufspalten)
(8) Ergebnisangabe als Relationaler Ausdruck.
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-17
Beispiele: BOOKS (TITLE,AUTHOR,PNAME,LC_NO)
PUBLISHERS (PNAME,PADDR,PCITY)
BORROWERS (NAME,ADDR,CITY,CARD_NO)
LOANS (CARD_NO,LC_NO,DATE)
PNAME = publisher’s name
LC_NO = Library of Congress number
PADDR = the street address of a publisher
PCITY = the city in which a publisher is located
CARD_NO = the library card number
DATE = the date on which a book was borrowed
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-18
Beispiel 1
( )σCITY Frankfurt DATE BOOKS LOANS BORROWERS= ∧ <' ' . .111997 >< ><
σ
BOOKS LOANS
BORROWERS
Assoziativität des Verbundes
(2) (6)
Kommutativität Selektion /
kartesisches Produkt
( )( )BOOKS LOANS BORROWERSCITY Frankfurt DATE>< ><σ = ∧ <' ' . .111997
σ
BOOKS
LOANS BORROWERS
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-19
Beispiel 1 (Fortsetzung)
( )( )BOOKS LOANS BORROWERSCITY Frankfurt DATE>< ><σ = ∧ <' ' . .111997
Kommutativität
Selektion / kartesisches
Produkt
(6)
( ) ( )( )BOOKS LOANS BORROWERSDATE CITY Frankfurt>< ><σ σ< =111997. . ' '
σ
BOOKS
LOANS BORROWERS
σ
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-20
Beispiel 2
( )(( ))
π σNAME,CARD_ NO,DATE TITLE 'DATABASES'= BOOKS
LOANS BORROWERS>< ><
π
BOOKS LOANS BORROWERS
σ
Assoziativität des Verbundes
(2) (3) (9)
Kommutativität Selektion
(Projektion)/ kartesisches
Produkt
(( )( )
)
π
π σNAME,CARD_NO,DATE
CARD_NO,DATE TITLE 'DATABASES'= BOOKS LOANS
BORROWERS
><
><
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-21
Beispiel 2 (Fortsetzung)
(( )( )
)
π
π σNAME,CARD_NO,DATE
CARD_NO,DATE TITLE 'DATABASES'= BOOKS LOANS
BORROWERS
><
><
π
BOOKS
LOANS
BORROWERS
σ
π
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-22
Beispiel 3 (Ullman)
( )( )XLOANS LOANS BORROWERS BOOKSS F= × ×π σ
mit:
F BORR CARD NO LOANS CARD NO
BOOKS LC NO LOANS LC NO≡ = ∧
=. _ . _
. _ . _
S TITLE AUTHOR PNAME LC NO NAME
ADDR CITY CARD NO DATE≡ , , , _ , ,
, , _ ,
( )( )π σTITLE DATE XLOANS<1/1/82
Schritt 1: Teilen der Selektion F in F1 und F2:
F1 BORR LC NO LOANS LC NO≡ =. _ . _
F2 BOOKS CARD NO LOANS CARD NO≡ =. _ . _
Schritt 2: Selektionen den Baum „hinunterbewegen“
( )( )( )( )( )π π σ σ σTITLE S F F DATE LOANS BORR BOOKS2 1 1/1/82< × ×
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-23
Schritt 3: Vereinigung der beiden Projektionen zu:
πTITLE
Schritt 4: Aufspalten der Folge
( )( )π σTITLE F2 K
mit Regel 5 in:
( )( )( )π σ πTITLE F TITLE BOOKS LC NO LOANS LC NO2 , . _ , . _ K
Dann Aufspalten der zweiten Projektion nach Regel 9:
πTITLE BOOKS LC NO, . _ und π LOANS LC NO. _
( ( ( ( ( ))) ( )))
π σ π σ σ
πTITLE F LOANS LC NO F DATE
TITLE BOOKS LC NO
LOANS
BORR Books2 1 1/1/82. _
, . _
<
× ×
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-24
Schritt 5: Aufspalten der Folge
( )( )π σLOANS LC NO F. _ 1 K
mit Regel 5 in:
( )( )( )π σ πLOANS LC NO F LOANS LC NO BORR CARD NO LOANS CARD NO. _ . _ , . _ , . _1 K
Schritt 6:Die zweite Projektion aufspalten und vor das kartesische Produkt bringen (Regeln 5 und 9):
( ( ( ( (
( )) ( )))( )))
π σ π σ π
σ π
π
TITLE F LOANS LC NO F LOANS LC NO LOANS CARD NO
DATE BORR CARD NO
TITLE BOOKS LC NO
LOANS BORR
Books
2 1
1/1/82
. _ . _ , . _
. _
, . _
< ×
×
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-25
Beispiel: Kartesisches Produkt
AB CD
nAB nCD (Datensätze)
Strategie: Lade so viele Blöcke von AB wie möglich in
den Hauptspeicher und lasse dabei Platz für
einen Block von BC.
AB CD
m Hauptspeicher-Blöcke
• n nAB CD, : Sätze.
• b bAB CD, : Sätze/Block.
• m : Anzahl der Blöcke im Hauptspeicher.
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-26
• Anzahl der Block-Zugriffe um AB zu lesen: AB ABn b .
• CD muss ( )n mAB ABb− 1 - mal gelesen werden.
Jedes Mal werden dazu n bCD CD Zugriffe benötigt.
Anzahl der Block-Zugriffe:
( ) =⋅⋅−
+CD
CD
AB
AB
AB
AB
bn
bmn
bn
1
( )nb
nm
AB
AB
CD
CDb⋅ +
−⎛
⎝⎜
⎞
⎠⎟1
1
Logische Optimierung
Grundlagen der Datenbanksysteme I VIII-27
Beispiel:
n nb bm
AB CD
AB CD
= == =
=
10 0005
100
Anzahl der Zugriffe = 42.400.
Bei 20 Block-Zugriffen pro Sekunde wird dieses
kartesische Produkt ca. 35 Minuten benötigen.
Wähle AB als die Relation, mit dem kleineren
Quotienten nb
AB
AB.
Das heißt, die Relation, die in weniger Blocks
passt.