Kapitel 4 Anfrageauswertung

4. Anfrageoptimierung
Es gibt zwei Stufen für die Optimierung einer SELECT-FROM-WHERE-Anfrageauswertung:
a) Optimierung jeder einzelnen Operation
=> systematische Schätzung der Kosten versch. Ausführungsstrategien, Auswahl eines
Plans mit den geringsten Kosten (s. Kap. 4.1)
b) Optimierung der Reihenfolge der einzelnen Operationen einer Anfrage
=> heuristische Regeln durch die die Operatoren in einem Anfragebaum vertauscht
werden (s. Kap. 4.2)
4.1. Optimierung einzelner Operationen
‣
Optimierung bei der Auswertung von
• Selektion
• Verbund (Join)
‣
Runtime-DB-Prozessor bestimmt
• welche Auswertungsstrategien für Selektion / Verbund möglich sind (es gibt i.d.R.
viele) und
• was diese kosten würden (Ausführungszeit in Anzahl Blockzugriffen)
‣
billigste (effizienteste) Strategie wird genommen
• stellt Schätzung dar, keine exakte mathematische Bestimmung
• die optimale Strategie zu berechnen ist normalerweise zu zeitaufwändig
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
1
4. Anfrageoptimierung
Für die Aufwandsschätzung relevante Informationen aus dem Katalog des Datenbanksystems:
‣ je Datei/ Relation:
•
•
•
•
•
•
Anzahl Sätze/ Tupel r
(durchschnittliche) Datensatzgröße R
Anzahl Datenblöcke b (oder guter Schätzwert)
Block-Faktor bfr für die Datei (= Blockgröße/R)
Art der Primärordnung und Ordnungsattribut
Arten der Sekundärordnung (Primär-, Cluster-, Sekundärindexe; jeweils mit Anzahl x der
Level/ Stufen/ Ebenen und der Anzahl Index-Blöcke für die unterste Index-Ebene bI1)
‣ ferner je Attribut einer Relation:
sl=1 alle Datensätze erfüllen die Bedingung,
sl=0 kein Datensatz erfüllt die Bedingung
• Anzahl unterschiedlicher Werte d eines Attributes
Für Schlüsselattribut gilt d = r, s = 1
und somit sl = 1/r
• Selektivität sl (zwischen 0 und 1) eines Attributes
- Verhältnis der Anzahl von Datensätzen (Tupeln) die die Bedingung erfüllen, zur
Gesamtzahl der Datensätze (Tupeln) in der Datei (Relation)
• Selektionskardinalität s = sl*r, die Anzahl von Datensätzen, die eine Bedingung auf Gleichheit
auf diesem Attribut erfüllen
- Anzahl Sätze, die bestimmte Gleichheitsabfrage erfüllen
Der Anfrageoptimierer benötigt möglichst aktuelle Werte dieser Parameter (Statistiken) für die
Schätzung der Kosten versch. Ausführungsstrategien.
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
2
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen
‣ Suchmethoden für Selektion:
• lineares Suchen („Brute-Force-Ansatz“)
-
erforderlich, falls keine (Primär-/ Sekundär-) Ordnung vorhanden
jeden Satz der Datei lesen und Attribut gemäß Bedingung prüfen
Auf jede Datei
anwendbar
4.1.1. Selektion
-
bei Gleichheits- und Bereichs-Suche über Ordnungsfeld einer
geordneten Datei
• Hash-Suche
-
geht nur bei Gleichheitsbedingung über Hash-Attribut einer Hash-Datei
• Index-Suche (B+ - Baum)
-
bei Gleichheits- und Bereichs-Suche über indexiertes Attribut
‣ vereinfachende Annahmen für die Kostenabschätzung:
•
•
Abhängig von Zugriffspfad
auf gewähltes Attribut
• binäres Suchen
Die Datensätze sind gleichverteilt.
Bei einer Bereichssuche qualifizieren sich im Schnitt die Hälfte aller Datensätze.
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
3
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen
‣ Formeln zur Schätzung der Zugriffskosten für SELECT (= Anzahl Blockzugriffe - AnzBZ)
• lineares Suchen („Brute-Force-Ansatz“)
-
alle Dateiblöcke (b) durchsuchen, um Datensätze auszuwählen die die
Selektionsbedingungen erfüllen (für s > 1): AnzBZ = b
für eine Bedingung auf Gleichheit auf einen Schlüssel (für s=1), wird im Durchschnitt die
Hälfte der Dateiblöcke durchsucht: AnzBZ = b /2
Falls Gleichheitsbedingung ein
erfüllt kein Datensatz die Bedingung gilt: AnzBZ = b
einziges Schlüsselattribut betrifft gilt
s=1 und damit AnzBZ= log2 b
• binäres Suchen
-
es gilt durchschnittlich: AnzBZ = log2 b + ⌈(s/ bfr)⌉ - 1
• Index-Suche (B+ - Baum)
-
s Datensätze erfüllen Gleichheitsbedingung, wobei s die Selektionskardinalität des
Indexattributs ist
jeder der s Datensätze kann in einem anderen Block gespeichert sein (= worst-case),
so dass: AnzBZ = x + s
bei einem Schlüsselindexierattribut (= eindeutig) gilt s =1, so dass: AnzBZ = x + 1
ist die Vergleichbedingung >, >=, <, <=, und wird ferner angenommen, dass die Hälfte
der Datensätze die Bedingung erfüllen, dann wird auf die Hälfte der Indexblöcke der
ersten Stufe, und über den Index auf die Hälfte der Dateidatensätze zugegriffen, so
dass ungefähr: AnzBZ = x + (bI1/2) + (r/2)
(r/2 kann verfeinert werden wenn besserer Selektivitätsschätzungen verfügbar sind)
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
4
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen → 4.1.1. Selektion
‣ Beispiel 4-1 zur Kostenschätzung:
•
•
•
•
Angestellte
mit r = 10.000 Sätzen (Tupeln)
AngNr Name GebDatum Adresse
Geschlecht
Gehalt AbtNr
in b = 2.000 Datenblöcken
…
…
…
…
…
…
…
und bfr = 5 Datensätze/ Block
…
…
…
…
…
…
…
Vorhandene Zugriffspfade:
- Cluster-Index auf Gehalt (Angestellte also physisch nach Gehalt geordnet) mit
ð x = 3 Stufen
ð einer Selektionskardinalität s = 20
- Sekundärindex auf Schlüsselattribut AngNr mit
ð x = 4 Stufen (s = 1)
- Sekundärindex auf Nichtschlüsselattribut AbtNr mit
ð x = 2 Stufen
ð bI1 = 4 Indexblöcke auf 1. Ebene
ð d = 125 verschiedenen Werten (Selektionskardinalität s = (r/d) = 80)
Frage: Wie sollen die folgenden Anfragen ausgeführt werden?
(1) SELECT * FROM Angestellte WHERE AngNr = 4711
(2) SELECT * FROM Angestellte WHERE AbtNr = 99
(3) SELECT * FROM Angestellte WHERE AbtNr > 99
(4) SELECT * FROM Angestellte WHERE AbtNr = 99 AND Gehalt > 30.000
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
5
4. Anfrageoptimierung
zu (1):
→ 4.1. Einzelne Operationen → 4.1.1. Selektion
SELECT * FROM Angestellte WHERE AngNr = 4711
AngNr Name
…
…
…
…
Angestellte
GebDatum Adresse Geschlecht
…
…
…
…
…
…
Gehalt AbtNr
…
…
…
…
- lineare Suche (sequentielles Lesen/ Brute-Force):
Anzahl Blockzugriffe = b/2
= 1.000
= 2.000
falls der Lösungssatz gefunden wird
falls kein Lösungssatz gefunden wird
- Verwendung des Sekundärindexes (B+-Baum):
Anzahl Blockzugriffe = x + 1
=5
=4
=> Sekundärindex wählen
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
falls der Lösungssatz gefunden wird
falls kein Lösungssatz gefunden wird
Gleichheitsbedingung auf ein
einziges Schlüsselattribut:
AngNr = 4711 und damit gilt s=1
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
6
4. Anfrageoptimierung
zu (2):
→ 4.1. Einzelne Operationen → 4.1.1. Selektion
SELECT * FROM Angestellte WHERE AbtNr = 99
AngNr Name
…
…
…
…
Angestellte
GebDatum Adresse Geschlecht
…
…
…
…
…
…
Gehalt AbtNr
…
…
…
…
- lineare Suche (sequentielles Lesen):
Anz.BZ = b
= 2.000
da normalerweise mehrere Lösungssätze (MAs in Abteilung 99)
(oder keine Lösung – falls es die AbtNr nicht gibt)
- Verwendung des Sekundärindexes auf AbtNr:
Anz.BZ = x + s
= x + r/d
= 2 + 80
= 82
=2
Für AbtNr gibt es d=125
mit s = r/d
unterschiedliche Werte, so dass die
x + 10.000/ 125
Selektionskardinalität s=r/d = 80 ist.
falls es die AbtNr gibt
(im Schnitt 80 MAs je Abteilung - je MA neuen Block lesen)
falls es die AbtNr nicht gibt
=> Sekundärindex wählen
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Für die Gleichheitsbedingung
AbtNr = 99 gilt eine
Selektionskardinalität s > 1
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
7
4. Anfrageoptimierung
zu (3):
→ 4.1. Einzelne Operationen → 4.1.1. Selektion
SELECT * FROM Angestellte WHERE AbtNr > 99
AngNr Name
…
…
…
…
Angestellte
GebDatum Adresse Geschlecht
…
…
…
…
…
…
Gehalt AbtNr
…
…
…
…
- sequentielles Lesen:
Anz. BZ = b
= 2.000
da mehrere Lösungen (s > 1)
- Verwendung des Sekundärindexes auf AbtNr:
Anz. BZ = x + (bI1/2) + r/2
= 2 + (4/2) + (10.000/2)
= 5.004
im Schnitt; je Satz neuen Block lesen
=> sequentielles Lesen wählen
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
8
4. Anfrageoptimierung
zu (4):
→ 4.1. Einzelne Operationen → 4.1.1. Selektion
SELECT * FROM Angestellte WHERE AbtNr = 99 AND
Gehalt > 30.000
AngNr Name
…
…
…
…
Angestellte
GebDatum Adresse Geschlecht
…
…
…
…
…
…
Gehalt AbtNr
…
…
…
…
- sequentielles Lesen:
Anz.BZ = b
= 2.000
da mehrere Lösungen (oder evtl. keine)
- einzelne Selektionsbedingungen betrachten:
•
•
Sekundärindex auf AbtNr (eindeutig):
Anz.BZ = 82 (im Allgemeinen) bzw. 2 (falls es die AbtNr nicht gibt)
--> siehe (2)
Gehalt (Datei physisch nach Gehalt sortiert):
-- binäres Suchen (ohne Index - Suche erst mit Gleichheit, dann sequentiell die Daten-Blöcke):
Im Durchschnitt qualifizieren sich die
Anz.BZ = log2 b + (b/2 - 1)
Hälfte aller Datensätze, Annahme: jeder
= 11 + 999 = 1.010
Datensatz in einem sep. Block => s = b/2
-- Verwendung des Clusterindexes (Suche erst mit Gleichheit, dann sequentiell die Daten-Blöcke):
Anz.BZ = x + (b/2)
= 3 + 1.000 = 1.003
=> Sekundärindex über AbtNr wählen
→ dann Lösungssätze mit restlichen Bedingung(en) prüfen
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
9
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen
4.1.2. Verbund (Join)
ANG.AngNr = ABT.Leiter
(PS)
(FS)
‣ Verbund ist der aufwendigste relationale Operator
‣ meist wird Equi / Natural Join benötigt (zwischen zwei Relationen, ANG ►◄ ABT)
‣ Join-Methoden:
•
Nested Loop Join
- erforderlich, falls keine (Primär-/ Sekundär-) Ordnung vorhanden
- jeden Satz der einen Relation (outer loop) mit jedem Satz der anderen Relation (inner loop)
vergleichen
•
Single Loop Join
- möglich, falls für eines der beiden Attribute eine Zugriffsstruktur (Index oder Hash-Ordnung)
existiert
- lese eine Relation sequentiell (single loop) und ermittle über die Zugriffsstruktur der anderen
Relation die Lösungssätze
•
Sort Merge Join
- möglich, falls beide Relationen über das Join-Attribut physisch sortiert sind oder
Zugriffsstrukturen existieren
- parallel für beide Relationen die sortierten Blöcke sequentiell lesen und die Sätze vergleichen
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
10
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen → 4.1.2. Verbund (Join)
4.1.2.1. Aufwandsabschätzung für Nested Loop Join
‣
ANG.AngNr = ABT.Leiter
Beispiel 4-2:
•
(PS)
•
Nested Loop Join zwischen den Relationen ANG und ABT
Relation ANG mit rANG = 6.000 Sätze, verteilt auf 2.000 Datenblöcke
•
Relation ABT mit rABT = 50 Sätzen, verteilt auf 10 Datenblöcke
(FS)
‣ Vorgehen:
a) erforderliche Mindestanzahl Hauptspeicher-Blöcke: bHS = 3
• jeden ANG-Datenblock einlesen und für diesen jeden ABT-Datenblock einlesen und
mit ANG-Datenblock vergleichen:
➡ 2.000 x 10 = 20.000 Blockzugriffe für die Vergleiche (ABT Blöcke werden 20.000x eingelesen)
➡ insg. 22.000 Zugriffe (bzw. 20.010 falls ABT zuerst eingelesen)
• dritter Hauptspeicherblock für Ergebnisaufnahme der resultierenden Datensätze nach JOIN
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
11
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen → 4.1.2. Verbund (Join)
→ Nested Loop Join
b) Annahme: bHS = 7 freie Blöcke im Hauptspeicher
➡ Heuristik: möglichst viele Blöcke der outer loop-Relation in den Hauptspeicher laden
‣ Alternative 1: ANG als outer loop-Relation
• Blockzugriffe, um ANG einzulesen = 2.000
(jeweils in 5er-Paketen)
• ABT-Blöcke werden 400 Mal eingelesen (immer einzeln)
• Blockzugriffe für die ABT-Relation = 400 x 10 = 4.000
➡ Blockzugriffe insgesamt = 2.000 + 4.000 = 6.000
‣ Alternative 2: ABT als outer loop-Relation
• Blockzugriffe, um ABT einzulesen = 10
( bHS = 7 )
(jeweils in 5er-Paketen)
• ANG-Blöcke werden 2 Mal eingelesen (immer einzeln)
• Blockzugriffe für die ANG-Relation = 2 x 2.000 = 4.000
➡ Blockzugriffe insgesamt = 10 + 4.000 = 4.010
‣ Erkenntnisse:
• mit mehr Hauptspeicher geht Nested Loop Join schneller
• wähle die Relation mit den wenigsten Datenblöcken für outer loop
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
12
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen → 4.1.2. Verbund (Join)
→ Nested Loop Join → Hauptspeicheranpassung DB2
Dynamischer Hauptspeicher Beispiel DB2
• Kontinuierliche Systemüberwachung um geteilten Hauptspeicher bestmöglich zwischen den
unterschiedlichen DB2 Speicher und Bufferpools je nach Bedarf zu verteilen
• Iterativer Überwachungsprozess wird durch Kontrollalgorithmen gestützt, die eine Oszillation
der Speicherbereiche vermeiden
Datenbankspeicher
BufferPool1
BufferPool1
BufferPool1
Sorts and Hash
Joins
Lock List
Package Cache
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
13
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen → 4.1.2. Verbund (Join)
4.1.2.2. Aufwandsabschätzung für Single Loop Join
‣
(PS)
Beispiel 4-3:
(FS)
unique
not null
•
Single Loop Join zwischen ANG und ABT (Relationen aus Bsp. 4-2)
•
Zusatzinformation zur Join-Selektivität (= Info aus Fremdschlüsselattribut-Definition):
-
•
jeder Satz der ABT-Relation hat genau ein eigenes Gegenstück in der ANG-Relation
für die Join-Attribute existiert jeweils ein mehrstufiger Sekundärindex
-
‣
ANG.AngNr = ABT.Leiter
für ANG ist dieser 4-stufig, für ABT 2-stufig
Vorgehen:
a) ANG sequentiell lesen und mittels ABT-Index Gegenstück in ABT suchen
•
Blockzugriffe, um ANG seq. einzulesen = 2.000 (6.000 Datensätze)
•
erfolgreiche Suchvorgänge im ABT-Index = 50
(jeweils 2 Index-Block-Zugriffe und 1 Daten-Block-Zugriff)
•
erfolglose Suchvorgänge im ABT-Index = 5.950 (6.000 - 50)
(jeweils 2 Index-Block-Zugriffe)
=> Blockzugriffe insgesamt = 2.000 + (2+1) x 50 + 2 x 5.950 = 14.050
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
14
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen
→ 4.1.2. Verbund (Join)
→ Single Loop Join
b) ABT sequentiell lesen und mittels ANG-Index Gegenstück in ANG suchen
•
Blockzugriffe, um ABT seq. einzulesen = 10
•
erfolgreiche Suchvorgänge im ANG-Index = 50
(jeweils 4 Index-Block-Zugriffe und 1 Daten-Block-Zugriff)
Hohe JOIN Selektivität
=> Blockzugriffe insgesamt = 10 + (4+1) x 50 = 260
‣
Erkenntnisse:
• mit kleiner Anfangsrelation und insbesondere mit hoher Join-Selektivität
(= viele Treffer) geht es schneller
=> (kleinere) Relation mit hoher Join-Selektivität sequentiell lesen
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
15
4. Anfrageoptimierung
→ 4.1. Einzelne Operationen
→ 4.1.2. Verbund (Join)
4.1.2.3. Aufwandsabschätzung für Sort Merge Join
‣
(PS)
Beispiel 4-4:
•
•
Beispiel 4-5:
•
(FS)
Sort Merge Join zwischen ANG und ABT (Relationen aus Bsp. 4-2)
ANG und ABT jeweils nach dem Join-Attribut sortiert
Durch jede Datei genügt ein einziger
=> Anz. BZ: 2.000 + 10 = 2.010 Blockzugriffe
‣
ANG.AngNr = ABT.Leiter
Durchlauf. Folglich entspricht die
Anzahl der Blockzugriffe der Summe
der Anzahl von Blöcken in beiden
Dateien.
•
Sort Merge Join zwischen ANG und ABT (Relationen aus Bsp. 4-3)
=> Sort Merge Join über Index-Blöcke möglich
Anzahl Index-Blöcke von ANG auf unterster Ebene: bI1ANG = 300
•
Anzahl Index-Blöcke von ABT auf unterster Ebene: bI1ABT = 3
=> Anz. BZ:
•
•
Index-Blöcke von ANG lesen + Index-Blöcke von ABT lesen
je Index-Lösungssatz 1 ANG-Datenblock und 1 ABT-Datenblock lesen
= 300 + 3 + 50 + 50 = 403 Blockzugriffe insgesamt
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
16
4. Anfrageoptimierung
Übungsaufgabe zur Kostenschätzung
not NULL
Gegeben:
• rMitarbeiter = 30.000 Sätzen (Tupeln)
• bMitarbeiter = 1.000 Datenblöcken
PersNr
…
…
• rAbteilung = 1.000 Sätzen (Tupeln)
• bAbteilung = 100 Datenblöcken
• Zugriffspfade:
- Cluster-Index auf Gehalt mit
AbtNr
…
…
ð x = 2 Stufen
ð Selektionskardinalität s = 15
Mitarbeiter
Name
Gehalt
…
…
…
…
AbtNr
…
…
Abteilung
Name
Beschreibung
…
…
…
…
- Sekundärindex auf Schlüsselattribut PersNr mit
ð x = 3 Stufen
- Sekundärindex auf Schlüsselattribut Abteilung.AbtNr mit
ð x = 2 Stufen
Aufgabe:
Welche JOIN Methode bietet sich für folgende Anfrage an und wie viele Blockzugriffe sind dafür
notwendig?
SELECT * FROM Mitarbeiter, Abteilung
WHERE Abteilung.AbtNr = Mitarbeiter.AbtNr
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
17
4. Anfrageoptimierung
Lösung
• Single-Loop-Join, da ein Join Attribut indexiert ist (AbtNr von Abteilung)
i. AnzBZ um Mitarbeiter Datensätze zu lesen = 1.000
ii. Je Mitarbeiter genau ein zugehöriger Abteilungs-Datensatz
==> 30.000 Suchanfragen an Relation Abteilung
==> 2 Indexstufen + 1 Datenblockzugriff sind notwendig um erfolgreich auf Abteilung
zuzugreifen
==> AnzBz = 1.000 + (2 + 1) * 30.000 = 91.000
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
18
4. Anfrageoptimierung
4.2. Optimierung der Auswertungsreihenfolge
‣ ein SQL-Anfrageausdruck durchläuft mehrere Stufen
•
Scanner, Parser, Validation
-
Prüfung Syntax und relationale Semantik
•
Umwandlung in einen Anfragebaum
•
Optimizer
-
Bestimmung eines effizienten Ausführungsplans
meist nicht optimal, da dessen Bestimmung zu teuer
•
Code-Generator
•
Datenbank-Prozessor
-
Ausführung interpretierter / compilierter Code
Bestimmung effiziente Knotenauswertung
Quelle: Fig. 18.1,
Elmasri, R. / Navathe, S.B. (2000)
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
19
4. Anfrageoptimierung
→ 4.2. Anfragebaum
‣ aus jedem SELECT-FROM-WHERE-Block entsteht 1 Anfragebaum
•
•
•
Relationen als Blätter und relationale Operationen (= Selektionen und Joins) als innere Knoten
Views werden in Basisrelationen aufgelöst
geschachtelte SQL-Statements werden getrennt ausgewertet / optimiert
(innere Anfrage zuerst)
‣ relationale Operatoren (= Knoten des Anfragebaums):
•
•
•
•
Projektion: π
Selektion: σ
Verbund/Join: ►◄
kartesisches Produkt: X
‣ Beispiel 4-6:
Liste aus den Relationen:
•
•
•
Employee (SSN, FName, LName, BDate, Address)
Works_On (ESSN, PNo, Hours)
Project (PNumber, PName, Budget),
{ESSN referenziert SSN; PNo referenziert PNumber}
die Namen derjenigen Angestellten, die nach 1957 geboren wurden und im Projekt ‚Aquarius‘
mitgearbeitet haben.
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
20
4. Anfrageoptimierung
→ 4.2. Anfragebaum
‣ entsprechender SQL-Ausdruck:
SELECT LName
FROM
Employee, Works_On, Project
WHERE PName = ‘Aquarius‘ AND PNumber = PNo
AND ESSN = SSN AND BDate > ‚1957-12-31‘ ;
Projektion
‣ initialer (kanonischer) Anfragebaum:
Selektion
Leserichtung
Die direkte Ausführung des initialen
Anfragebaums ist auf Grund der
kartesischen Produkt Operationen sehr
ineffizient.
Aufgabe des heuristischen Optimieres ist
die Erstellung eines endgültigen
Anfragebaums zwecks effizienter
Ausführung
kartesisches
Produkt
Relationen als
Blätter
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Quelle: Fig. 18.5,
Elmasri, R. / Navathe, S.B. (2000)
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
21
4. Anfrageoptimierung
‣
die Auswertung des Baumes erfolgt von den Blättern zur Wurzel
•
•
‣
→ 4.2. Anfragebaum
Beginn auf der untersten Ebene
auf einer Ebene von links nach rechts auswerten
Regeln für die Optimierung des Anfragebaumes:
1
möglichst frühzeitig Selektionen ausführen
ð Selektionen im Baum möglichst weit nach unten schieben
=> weniger Sätze => kleinere Relationen
2
sofern möglich, stelle Blätter bzw. Teilbäume um,
damit sehr restriktive Selektionen / Joins noch frühzeitiger ausgeführt werden
ð Selektionen / Joins weiter nach links bzw. weiter nach unten bringen
3
sofern möglich, beseitige kartesische Produkte durch Joins
(Join = kartesisches Produkt mit entsprechender Selektionsbedingung)
4
möglichst frühzeitig Projektionen ausführen
ð Projektionen im Baum möglichst weit nach unten schieben,
d.h. für Relationen nur diejenigen Attribute führen, die weiter oben im Anfragebaum noch
benötigt werden
=> kürzere Sätze => kleinere Relationen
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
22
4. Anfrageoptimierung
→ 4.2. Anfragebaum
→ Beispiel
‣ Fortsetzung Beispiel 4-6:
Schritt 1:
Selektionen nach unten schieben
=> Relationen Employee und Project
frühzeitig reduzieren
Quelle: Fig. 18.5,
Elmasri, R. / Navathe, S.B. (2000)
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
23
4. Anfrageoptimierung
→ 4.2. Anfragebaum
→ Beispiel
Schritt 2:
restriktive Selektionen / Joins
nach links bzw. nach unten
schieben
=> Employee (viele qualifizierende Datensätze)
und Project (wenig qualifizierende Datensätze)
samt Selektionen tauschen
=> Works-On wird zuerst
mit der kleineren Relation
Project verknüpft
(daher auch die Umstellungder Selektionen für die
kartesischen Produkte)
Quelle: Fig. 18.5,
Elmasri, R. / Navathe, S.B. (2000)
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
24
4. Anfrageoptimierung
→ 4.2. Anfragebaum
→ Beispiel
Schritt 3:
kartesische Produkte beseitigen
‣ dafür deren „Selektionsbedingungen“
als Join-Bedingungen
einsetzen
Quelle: Fig. 18.5,
Elmasri, R. / Navathe, S.B. (2000)
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
25
→ 4.2. Anfragebaum
→ Beispiel
4. Anfrageoptimierung
Schritt 4:
5b)
Projektionen nach unten schieben
=> Project, Works_On, deren JoinErgebnis und Employee
werden frühzeitig reduziert
5a)
3b)
4b)
3a)
1b)
4a)
2)
1a)
Quelle: Fig. 18.5,
Elmasri, R. / Navathe, S.B. (2000)
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
26
4. Anfrageoptimierung
Übungsaufgabe zur Optimierung der Auswertungsreihenfolge
Gegeben:
• rMitarbeiter = 30.000 Sätzen (Tupeln)
• rAbteilung = 1.000 Sätzen (Tupeln)
PersNr
…
…
Mitarbeiter
Name
Gehalt
…
…
…
…
AbtNr
…
…
Abteilung
Bezeichnung
Beschreibung
…
…
…
…
• Anfrage: Welcher Mitarbeiter der Abteilung „Marketing“ verdient
mehr als 60.000 EUR im Jahr?
• Initialer Anfragebaum:
π Name, Gehalt
AbtNr
…
…
σ Gehalt > 60.000 AND MITARBEITER.AbtNr = ABTEIUNG.AbtNr AND Bezeichnung = „Marketing“
X
MITARBEITER
ABTEILUNG
Aufgabe:
Optimieren Sie den initialen Anfragebaum und markieren Sie die Knoten mit fortlaufenden Nummern, um
die Reihenfolge der Abarbeitung anzugeben (Zwischenschritte müssen nicht gezeichnet werden).
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
27
4. Anfrageoptimierung
Lösung:
3 b)
3 a)
►◄ MITARBEITER.AbtNr = ABTEIUNG.AbtNr
1 b)
1 a)
π Name, Gehalt
π AbtNr
σ Bezeichnung = „Marketing“
ABTEILUNG
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
π Name, AbtNr, Gehalt
2 b)
σ Gehalt > 60.000
2 a)
MITARBEITER
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
28
4. Anfrageoptimierung
→ 4.3. Beispiel DB2
§ System-R (70er Jahre)
– Das IBM System-R Projekt entwickelte einen Optimierer der den einzelnen
Zugriffsoperationen geschätze „Kosten” zuweist. Auf Basis dieser Kosten wird dann der
optimale Plan ausgewählt.
§ Starburst (1984 – 1992)
– Starburst (das Query Graph Model) ist eine alternative Architektur, bzw. eine
Weiterentwicklung von System-R. Jede Anfrage wird in Form eines Graphen dargestellt. Der
Graph wird manipuliert (Query Rewrite) um den optimalsten Zugriff zu finden.
§ System-RX (2000 - 2005)
– System-RX ist eine Weiterentwicklung von System-R/ Starburst zu einem hybriden
Datenbanksystem das neben einer relationalen Speicherung auch XML Daten nativ verwalten
kann. Dazu ist es erforderlich den Optimierer um entsprechende Laufzeitoperatoren zu
erweitern, die eine Navigation in der hierarchischen XML Struktur ermöglichen.
§ System-S (2004 - 2009)
– System-S ist ein völlig neues Paradigma der Datenverarbeitung - Stream Computing. Massive
Datenmengen werden in Echtzeit analysiert und ausgewertet. Völlig neue Optimierungswege
werden implementiert, die sich im Wesentlichen in einer sich selbst optimierenden
Laufzeitumgebung widerspiegeln.
Der DB2 Optimierer ist ein kostenbasierter Optimierer
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
29
4. Anfrageoptimierung
→ 4.3. Beispiel DB2
Alles dreht sich um Kosten...
§ Beide, QGM und System-R/X Optimierer nutzen eine kostenbasierte Optimierung
§ Kosten entsprechen dem Aufwand eine SQL Anfrage zu beantworten
§ Kosten werden in einer künstlichen Einheit „timerons” ausgedrückt, die die Nutzung von
Ressourcen repräsentiert. Timerons sind nicht als absolute Werte zu verstehen, sondern
sind innerhalb eines Plans relativ zueinander zu bewerten. Timerons entsprechen nicht der
tatsächlichen Zeit.
§ Kosten werden auf Basis von Statistiken berechnet. Diese Statistiken ergeben sich aus
der Anzahl der Zeilen („Kardinalität“), Anzahl der eindeutigen Werte in einer Tabelle,
Informationen über CPU- (# von Instruktionen) und I/O- (# von seeks und page transfers) Nutzung
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
30
4. Anfrageoptimierung
→ 4.3. Beispiel DB2
Die Optimierungsphase innerhalb des DB2 Compilers
SQL/ XQuery
Gegeben:
DB2 Compiler
§ ein QGM (Query Graph Model)
Parse Query
§ Datenbank-Konfiguration
Check
Semantics
§ Statistik über Datenverteilung
Rewrite
Query
Zu bestimmen:
Pushdown
Analytics
§ der optimale QEP (Query Execution Plan)
Optimize
Access Plan
Unter Verwendung von:
Access
Plan
§ alternativen QEPs
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Remote SQL
Generation
Generate
Executable Code
§ Modellierung von IO, CPU, Speicher
pro Alternative
§ Auswahl des effizientesten QEPs
Query
Graph
Model
Visual Explain
Execute Plan
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
31
4. Anfrageoptimierung
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
→ 4.3. Beispiel DB2
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
32
4. Anfrageoptimierung
→ 4.3. Beispiel DB2
DB2 Besonderheiten:
§ Just in Time Statistics (JIT)
– Liefert online Statistiken die vom Optimierer benötigt werden
– Analysiert die Anfrage, ermittelt bei Bedarf Statistiken welche dem Optimierer beim
Finden des optimalen Plans helfen
§ Erfassen von Statistiken in 3-Ebenen:
– Ist die automatisierte Statistik aktuell? Nein …
– JIT – früh genug fertig? Nein …
– sog. „Enhanced statistics fabrication“
© DHBW Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Vorlesung Datenbanktechnik (Informationstechnologie) V2.0
33