5-Anfrageoptimierung

Frühjahrsemester 2014
CS243 Datenbanken
Kapitel 5: Anfrageoptimierung
H. Schuldt
Anfrageoptimierung – Ausgangslage
•
Deklarative Anfragesprachen wie z.B. SQL beschreiben das gewünschte Resultat,
aber nicht, wie dieses erhalten werden soll („what, not how“)
•
Physische Datenunabhängigkeit erlaubt es einem Datenbanksystem,
konzeptuelle/logische Datenstrukturen physisch unterschiedlich zu implementieren
•
Das DBMS hat daher die Freiheit, zu einer Anfrage und physischen Datenstruktur
einen entsprechenden, möglichst optimalen Ausführungsalgorithmus zu finden
•
Allgemeine Formulierung des Anfrageverarbeitungsproblems (NP vollständig)
– gegeben:
• Anfrage Q
• Ausführungsraum E (Menge der möglichen Ausführungspläne)
• Kostenfunktion C: E # cost
– gesucht
• Ausführung e œ E(Q)
so dass C(e) = mine’ œ E(Q) C(e’)
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-2
1
Anfrageoptimierung und –ausführung: Übersicht
•
•
•
Eine gegebene Benutzeranfrage (nach
Übersetzung der Anfrage in einen Ausdruck
der Relationenalgebra) wird zunächst
algebraisch optimiert
– z.B. Umordnung / Austausch von
Operatoren)
– Input: Äquivalenzregeln, unabhängig
vom Datenbankzustand
Danach erfolgt die nicht-algebraische
Optimierung
– Auswahl geeigneter
Ausführungsprimitive
– Input: Statistiken und Kostenfunktionen
(abhängig vom DB-Zustand)
Der vom Optimierer schliesslich erzeugte
Ausführungsplan ist ein Operatorbaum,
dessen Knoten den Ausführungsprimitiven
des DBS-Laufzeitsystems entsprechen.
FS 2014
SQL-Anfrage
Syntaxanalyse
und Zerlegung
Algebraischer Operatorbaum
Algebraische Transformation / Optimierung
Verbesserter algebraischer
Operatorbaum
Generierung von
Ausführungsplänen;
Kostenschätzung & Auswahl
Ausführungsplan
(“physischer” Operatorbaum)
Datenbanken (CS243) – Anfrageoptimierung
5-3
Anfrageoptimierung und –ausführung
•
Man kann in den meisten DBS die generierten Ausführungspläne mit dem
EXPLAIN-Kommando anschauen und analysieren.
•
Bei ad hoc-Anfragen findet die Optimierung zum Anfragezeitpunkt statt.
Im Fall von ESQL-Programmen erfolgt die Optimierung in fast allen Produkten
zur Compile-Zeit statt. Dadurch müssen wiederholt ausgeführte parametrisierte
Queries werden nur einmal optimiert werden.
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-4
2
Beispielanfrage – Algebraischer Operatorbaum
SELECT DISTINCT Bez
FROM Produkte P, Bestellungen B
WHERE B.PNr = P.PNr
AND Lagerort = :ort
AND Gewicht > :gew
AND Menge > :m
Algebraischer Operatorbaum
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-5
Wichtige Ausführungsprimitive …
Grundlage für die Erstellung des physischen Operatorbaums sind die grundlegenden
Ausführungsprimitive des Datenbanksystems.
Das physische Datenbankschema ist zum Zeitpunkt der Query-Optimierung fest,
so dass nicht immer alle Ausführungsprimitive anwendbar sind.
Relationen-Scan (TABLE SCAN, TABLE ACCESS FULL):
– Sequentielles Lesen aller Tupel einer Relation, wobei auf jedem Tupel ein
„Single-Scan“-Filterprädikat überprüft und eine Attributprojektion (ohne
Duplikateliminierung) vorgenommen werden kann.
TID-Zugriff (TABLE ACCESS BY ROWID):
– Direkter Zugriff auf alle Tupel einer Liste von TIDs, wobei auf jedem Tupel
ein „Single-Scan“-Filterprädikat überprüft und eine Attributprojektion
(ohne Duplikateliminierung) vorgenommen werden kann.
Die TID-Liste sollte nach Seitennummern sortiert sein; Seiten mit kleinem
„Abstand“ auf derselben Platte (z.B. im selben Zylinder) sollten mit einem
einzigen Plattenzugriff gelesen werden (multi-block I/O, list prefetch).
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-6
3
… Wichtige Ausführungsprimitive …
Index-Scan (INDEX RANGE SCAN oder INDEX UNIQUE SCAN):
– Zugriff auf alle TIDs von Tupeln, die ein Index-Suchprädikat der Form
wert1 § KEY § wert2 erfüllen, wobei KEY der Suchschlüssel des Index ist
(dies kann evtl. auch eine Attributkombination sein).
Realisiert ist dies durch eine Indexsuche nach wert1 mit einem nachfolgenden
„Blatt-Scan“ bis zum Erreichen des ersten Schlüssels, der grösser als wert2 ist.
Sortieren (SORT):
– Sortieren einer Tupelmenge nach einem Attribut bzw. einer Attributkombination.
Dabei werden Sortieralgorithmen für Externspeicher verwendet
(z.B. Mehrwegemischen).
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-7
… Wichtige Ausführungsprimitive
Nested-Loop-Join (NESTED LOOPS):
– Berechnung eines Joins zwischen zwei Tupelmengen (mit Angabe einer
expliziten Join-Bedingung) mittels einer Doppelschleife
Merge-Join (MERGE JOIN):
– Berechnung eines Equi-Joins zwischen zwei nach dem Join-Attribut sortierten
Tupellisten mittels Mischen der Listen. Dieses Join-Berechnungs-Verfahren
heisst häufig auch Sort-Merge-Join, weil unter Umständen vor dem Mischen
noch Sortierschritte stattfinden müssen.
Hash-Join:
– Berechnung eines Equi-Joins zwischen zwei Tupelmengen durch Abbilden beider
Mengen auf eine Hash-Tabelle (mit einer für beide Tupelmengen identischen
Hash-Funktion über dem Join-Attribut), so dass Tupel mit gleichem Attributwert
auf denselben Eintrag (bzw. Bucket) der Hash-Tabelle abgebildet werden.
Durchschnitt, Vereinigung, Differenz (INTERSECTION, UNION, MINUS):
– Anwenden von Mengenoperationen auf Tupelmengen oder TID-Listen.
Dies wird in der Regel erst nach vorhergehendem Sortieren angewandt.
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-8
4
Beispielanfrage – physischer Operatorbaum
Ausführungsplan nach Kostenschätzung und Wahl der Ausführungsprimitiven
physischer Operatorbaum
SELECT DISTINCT Bez
FROM Produkte P, Bestellungen B
WHERE B.PNr = P.PNr
AND Lagerort = :ort
AND Gewicht > :gew
AND Menge > :m
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-9
5.1 Algebraische Optimierung …
Ziele der algebraischen Optimierung
• Vereinfachung der Anfrage (logische Anfrageoptimierung) durch syntaktische
Anfragetransformation
– Manipulation der Anfrage unter Erhaltung der Semantik
– englische Bezeichnung: Query Rewrite
• Elimination unnötiger Operationen
• Anwendung einfacher Heuristiken zur Effizienzverbesserung
Problemstellung
• Unter allen äquivalenten Formulierungen einer gegebenen Anfrage soll die
günstigste Methode ausgewählt werden
Methode
• Anwendung der Äquivalenzregeln der Relationenalgebra
(siehe Kapitel DB-3 aus cs261 bzw. ausführlich später)
• Dadurch kann die Unabhängigkeit vom aktuellen Datenbankzustand
gewährleistet werden
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-10
5
… Algebraische Optimierung
Ergebnis: ein Operatorbaum mit
• Blattknoten: beteiligte Basisrelationen
• Innere Knoten: logische Operatoren (der Relationenalgebra), z.B. Selektion,
Projektion, kartesisches Produkt, …
• Kanten: Ausführungsreihenfolge
Heuristik
• Operatoren-Output soll so klein wie möglich sein
• Reduktion der Grösse von Zwischenresultaten
• Sammlung von syntaktischen Transformationsregeln, die i. A. eine Verbesserung
der Effizienz bewirken, z.B.
– Möglichst frühe Berechnung von Selektionen
– Kombination von Selektionen und kartesischem Produkt zu Joins
– Möglichst frühe Berechnung von Projektionen
– Bestimmung der Join-Reihenfolge so, dass möglichst kleine
Zwischenergebnisse entstehen
– Kombination von unären Operationen (select, project) zu Kaskaden
– Erkennung gemeinsamer Teilausdrücke
– Entfernung redundanter Teilausdrücke
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-11
Übersetzung SQL-Anfrage in Operatorbaum
Zu SQL-Anfragen des Typs
SELECT …
FROM …
WHERE …
ist eine kanonische Übersetzung in einen Operatorbaum realisiert, die aus jeder
Basisrelation in der FROM-Klausel einen Blattknoten macht (falls eine View verwendet
wird, dann wird die View-Definition anstelle des Blattknotens eingesetzt).
Die Prädikate der WHERE-Klausel (u.a. auch die Join-Prädikate) werden in
Selektionsknoten übersetzt. Zuletzt werden die Attribute in der SELECT-Klausel in
einen Projektionsknoten überführt.
p
SELECT A1, ..., An
FROM R1, ..., Rk
WHERE P
A1, … An
sP
ä
ä
R1
FS 2014
Rk
R2
Datenbanken (CS243) – Anfrageoptimierung
5-12
6
Wiederholung: Äquivalenzregeln der Relationenalgebra …
Seien R, S, T Relationen, P, P1, P2, Q Prädikate, R1, R2, S1 Teilmengen der Attribute
von R bzw. S. Es gelten die folgenden Äquivalenzregeln
(z.T. bekannt aus Kapitel DB-3 von cs261):
Kommutativitätsregeln:
1.
RS=SR
2.
RS=SR
3.
RS=SR
4.
RäS=SäR
Vertauschung von Selektionen:
5.
s[P] (s[Q] (R)) = s[Q] (s[P] (R))
Assoziativitätsregeln:
6.
R(ST)=(RS)T
7.
R(ST)=(RS)T
8.
R(ST)=(RS)T
9.
Rä(SäT)=(RäS)äT
FS 2014
…
Datenbanken (CS243) – Anfrageoptimierung
5-13
Wiederholung: Äquivalenzregeln der Relationenalgebra …
Zusammenführen/Trennen von Selektionen:
10.
s[P1  P2  …  Pn] (R) = s[P1] (s[P2] (… (s[Pn] (R))…))
Eliminieren geschachtelter Projektionen:
11.
p[Q1] (p[Q2](…(p[Qn](R))…)) = p[Q1](R) falls Q1 Œ Q2 Œ … Œ Qn Œ sch(R)
12.
p[Q1] (p[Q2](…(p[Qn](R))…)) = p[att(Q1)  att(Q2)  …  att(Qn)] (R) allg.
Vertauschen von Projektion und Selektion:
13.
p[Q] s[P] (R) = s[P] p[Q] (R) falls P nur Q-Attribute enthält
Vertauschen von Selektion und Join/Kartesisches Produkt:
14.
s[P] (R  S) = s[P] (R)  S
falls P nur Attribute aus R enthält
15.
s[P] (R ä S) = s[P] (R) ä S
falls P nur Attribute aus R enthält
Vertauschen von Projektion und Join:
16.
p[Q] (R [P] S) = p[Q] (p[Q1] (R) [P] p[Q2] (S))
Join-Attribute müssen bis zum Join erhalten bleiben, also
Q1 = {A | A œ sch(R)  Q}  {A | A œ sch(R)  att(P)}
Q2 = {A | A œ sch(S)  Q}  {A | A œ sch(S)  att(P)}
wobei att(P) die Attribute des Join-Prädikats P sind
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-14
7
…
Wiederholung: Äquivalenzregeln der Relationenalgebra …
Vertauschung von Selektion und Mengenoperationen:
17.
s[P] (R  S) = s[P](R)  s[P](S)
18.
s[P] (R  S) = s[P](R)  s[P](S)
19.
s[P] (R – S) = s[P](R) – s[P](S)
Vertauschung von Projektion und Vereinigung:
20.
p[P] (R  S) = p[P](R)  p[P](S)
Zusammenfassung von Selektion und kartesischem Produkt:
21.
s[R.A = S.A] (R ä S) = R  S
falls sch(R)  sch(S) = A
22.
s[R.A = S.B] (R ä S) = R [R.A = S.B] S
Kombination aus Join und Vereinigung
23.
R  (S  T) = (R  S)  (R  T)
Konjunktionen und Disjunkitonen in Selektionsprädikaten
24.
s[P1  P2] (R) = s[P1] (R)  s[P2] (R)
25.
s[P1 ¤ P2] (R) = s[P1] (R)  s[P2] (R)
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-15
… Wiederholung: Äquivalenzregeln der Relationenalgebra
Idempotenzen:
26.
RR=R
27.
RR=R
28.
RR=R
29.
R–R=«
Verknüpfungen mit leeren Relationen:
30.
R«=R
31.
R«=«
32.
R«=«
33.
R–«=R
34.
«–R=«
Zudem: Umwandlung von Bedingungen gemäss den Äquivalenzen der
Boole‘schen Algebra
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-16
8
Algebraische Optimierung: Beispiel …
1. Gegebene Anfrage:
SELECT *
FROM Produkte P, Bestellungen B
WHERE P.Lagerort = 'Basel' AND B.Menge > 100 AND P.PNr = B.PNr;
2. Umwandlung in Relationenalgebra
3. Optimierung:
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-17
… Algebraische Optimierung: Beispiel
•
Optimierter vs. nicht-optimierter algebraischer Operatorbaum
Annahme:
FS 2014
100‘000
1‘000‘000
100
50
Produkte
Bestellungen
Lagerorte
Ergebnistupel
Datenbanken (CS243) – Anfrageoptimierung
5-18
9
Verwendung funktionaler & mehrwertiger Abhängigkeiten
Gegeben sei die Relation R mit Schema sch(R) und A, B, C Πsch(R).
Ferne gelte in R die funktionale Abhängigkeit B  C. Dann gilt auch:
[AB](R)  [BC](R) = [ABC](R)
Satz: verlustfreier Join
Sei R eine Relation mit sch(R) = X  Y und X  Y ∫ « .
Falls gilt X  Y T X oder X  Y T Y, dann gilt auch: [X](R)  [Y] (R)  R
Falls gilt X  Y Tz X oder X  Y Tz Y , dann gilt auch [X](R)  [Y] R  R
Dieser Satz kann verwendet werden, um redundante Joins (teuer!) zu entfernen.
Woher kommen überhaupt redundante Operationen, insbesondere Joins, in
Anfragen?
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-19
Verwendung von Integritätsbedingungen
Beispiel:
• In einer Relation Angestellte sei folgende Integritätsbedingung I definiert:
I = „kein Angestellter verdient mehr als 100 kFr“
• Es sei die Anfrage Q gegeben mit
Q= „wer verdient mehr als 120 kFr / weniger als 80 kFr?“
•
•
Schema: Angestellte (Name, Adresse, Abteilung, Gehalt)
Integrität: … check Gehalt < 100000
•
Anfrage:
s[Gehalt > 120‘000 ¤ Gehalt < 80‘000] (Angestellte)
= s[Gehalt > 120‘000 ¤ Gehalt < 80‘000] (s[Gehalt < 100‘000 (Angestellte))
(Wegen Integritätsbedingung in Anfrage Q)
= s[Gehalt > 120‘000] (s[Gehalt < 100‘000] (Angestellte)) »
s[Gehalt < 80‘000] (s[Gehalt < 100‘000] (Angestellte))
= s[Gehalt > 120‘000 ⁄ Gehalt < 100‘000](Angestellte) »
s[ Gehalt < 80‘000 ⁄ Gehalt < 100‘000](Angestellte)
=  » s[ Gehalt < 80‘000](Angestellte) = s[ Gehalt < 80‘000](Angestellte)
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-20
10
Algebraische Transformation bei View-Ersetzung
Beispiel:
• Es seien die folgenden Views gegeben:
Preise (PNr, Preis) := p[PNr, Preis] (Produkte)
Lager (PNr, Lagerort, Vorrat) := p[PNr, Lagerort, Vorrat] (Produkte)
Query-Transformation:
s[Lagerort = 'Basel'  Preis > 100] (Preise  Lager)
= s[Lagerort = 'Basel'  Preis > 100]
(p[PNr, Preis] (Produkte)  p[PNr, Lagerort, Vorrat] (Produkte))
(aufgrund der Viewdefinition)
= s[Lagerort = 'Basel'  Preis > 100]
(p[PNr, Preis, Lagerort, Vorrat] (Produkte  Produkte))
(nach Regeln 12 und 16 und weil PNr Primärschlüssel von Produkte ist)
= s[Lagerort = 'Basel'  Preis > 100] (p[PNr, Preis, Lagerort, Vorrat] (Produkte))
(nach Regel 28)
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-21
5.2 Nicht-Algebraische Optimierung
Ziele der nicht-algebraischen Optimierung
• Auswahl der Ausführungsstrategie (physische Anfrageoptimierung)
• Suche eines guten Ausführungsplans unter Berücksichtigung von
Kostenparametern, physischer Datenorganisation, Zugriffspfaden, etc.
– englische Bezeichnung: Query Planning
– abhängig vom aktuellen DB-Zustand
Ergebnis der nicht-algebraischen Optimierung
• ist ein Ausführungsplan (Operatorenbaum) mit
– Knoten: ausführbare Operationen (Algorithmen), z.B. IndexScan, Nested
Loop-Join, …
– Kanten: Eingabe-Ausgabe-Beziehung zwischen Operationen
•
Teilaufgaben
– Optimierung einfacher Anfragen (ohne Joins)
– Bestimmung von Join-Reihenfolgen
– Query Compilation
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-22
11
Bestimmung von Join-Reihenfolgen
•
Bei Anfragen mit mehreren Joins hat die Reihenfolge (d.h. Klammerung) unter
Umständen grösseren Einfluss als die konkreten Implementierungsstrategien
– Daher: Entscheidung über die Reihenfolge der Join-Berechnungen ist wichtig
– Grundlage ist Kommutativität und Assoziativität von Joins
•
Vorgehen
– Bestimmung (aller ?) möglichen Join-Reihenfolgen und Darstellung als
Join-Trees
– Bestimmung für jeden Join-Tree die Kosten der gewählten Implementierung
und Auswahl der kostengünstigsten Alternative
•
Exponentiell grosser Suchraum
– Daher: Einschränkung über die Form der betrachteten Join-Trees
•
Bemerkung
– Typische SQL-Anfragen haben eher wenige Joins
– Das kann sich allerdings ändern, wenn massiv Views benutzt werden oder bei
automatisch erzeugten Anfragen (z.B. von Frontends oder in Decision Support
bzw. Data Mining-Anwendungen)
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-23
Kostenbasierte Optimierung …
Grundlage der kostenbasierten Optimierung sind Kostenfaktoren, also eine
Abschätzung der bei der Anfragebearbeitung entstehenden Kosten auf der Grundlage
von physischen Operationen:
I/O-Kosten:
– Diese werden als proportional zur Anzahl der benötigten Seiten angenommen
(aus Komplexitätsgründen ohne Berücksichtigung der Pufferung)
CPU-Kosten:
– Diese sind proportional zur Anzahl der Tupel im (Zwischen-) Ergebnis
Speicherkosten:
– Zeit-Platz-Produkt für den benutzten Hauptspeicher (wird aber typischerweise
vernachlässigt)
Kommunikationskosten:
– Diese sind nur in verteilten Datenbanksystemen relevant
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-24
12
… Kostenbasierte Optimierung
Ziel der Optimierung ist das Auffinden denjenigen Ausführungsplans, für den die
Schätzung der gewichteten Kosten
w1 * I/O-Kosten + w2 * CPU-Kosten + w3 * Speicherkosten
minimal ist, wobei w1, w2, w3 vom Hersteller technologieabhängig gewählte
Gewichte sind (die unter Umständen vom Datenbank-Administrator verändert werden
können).
Das Gewicht w3 ist oftmals Null und auch w2 wird häufig vernachlässigt.
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-25
Optimierungsmethode
•
Falls möglich werden
– alle überhaupt möglichen Ausführungspläne erzeugt,
– die jeweiligen Kosten geschätzt und
– der insgesamt beste Ausführungsplan ausgewählt (erschöpfende Suche
mit der so genannten “Generate-and-Test”-Strategie)
•
Wenn es sehr viele mögliche Ausführungspläne gibt (etwa bei Anfragen mit sehr
vielen Joins), dann wird der Suchraum durch geeignete Heuristiken für
kombinatorische Optimierungsprobleme eingeschränkt.
– In diesem Fall ist allerdings die Optimalität des ausgewählten
Ausführungsplans nicht mehr garantiert.
– Trotzdem führen solche Heuristiken in aller Regel zu einem guten
Ausführungsplan.
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-26
13
Kostenschätzungen
•
Die Basis für die nicht-algebraische Optimierung stellen Kostenschätzungen dar.
•
Kostenschätzungen basieren auf Statistiken über den Daten.
•
Damit die Aktualisierung der Statistiken den laufenden Betrieb nicht behindert,
werden die Statistiken typischerweise nur auf expliziten Wunsch des DatenbankAdministrators aktualisiert (in Oracle mit dem Kommando ANALYZE).
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-27
Kostenschätzung: relevante statistische Information
Für jede Relation R:
– Card(R)
– NPages(R)
die Anzahl der Tupel von R
die Anzahl der Seiten, in denen R gespeichert ist
Für jedes Attribut A einer Relation:
– ColCard(A)
die Anzahl der verschiedenen vorkommenden Attributwerte
– Low(A)
der kleinste vorkommende Attributwert
– High(A)
der grösste vorkommende Attributwert
– ColLen(A)
die (durchschnittliche) Länge eines Attributwerts
Für jeden Index I:
– NLevels(I)
– NLeaves(I)
FS 2014
die “Höhe” des Index
die Anzahl der Blattknoten des Index
Datenbanken (CS243) – Anfrageoptimierung
5-28
14
I/O-Kosten-Schätzung für einige Ausführungsprimitive
Beispiel: Table-Scan über Relation R
– I/O-Kosten: NPages(R) · Kosten_sequentieller_Seitenzugriff
bzw.:
NPages(R) da nicht die eigentlichen abgeschätzten Kosten,
sondern nur die Anzahl Zugriffe berücksichtigt wird
Index-Scan über mittelbaren Index IA für Attribut A der Relation R mit Filterformel f
– I/O-Kosten: (NLevels(IA) – 1) · Kosten_wahlfreier_Seitenzugriff +
FF (f) · NLeaves(IA) · Kosten_wahlfreier_Seitenzugriff
bzw.:
(NLevels(IA) – 1) + FF (f) · NLeaves(IA)
– Dabei schätzt der Ausdruck FF(f) die Selektivität (bzw. den Filterfaktor) des
Filterprädikats f ab. Der Filterfaktor für ein Prädikat f auf den Attributen einer
einzigen Relation R ist wie folgt definiert:
FF(f) :
FS 2014
| {r  R | f(r)} |
|R |
Datenbanken (CS243) – Anfrageoptimierung
5-29
Beispiel (Fortsetzung)
TID-Zugriff nach Index-Scan mit Filterprädikat f
•
Fall 1: Der vorhergehende Index-Scan bezieht sich auf einen Clustered Index
– I/O-Kosten: NPages(R) · FF(f)
•
Fall 2: Der vorhergehende Index-Scan bezieht sich auf einen Unclustered Index
– I/O-Kosten: Card(R) · FF(f)
Dies ist eine pessimistische Schätzung der I/O-Kosten:
Es wird angenommen, dass alle Tupel der TID-Liste auf verschiedenen
Seiten liegen.
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-30
15
Schätzung von Filterprädikaten
•
Typische Formeln für die Schätzung von (einfachen) Filterprädikaten sind die
folgenden (unter Annahme einer Gleichverteilung):
FF(A = v)
=
1 / ColCard(A)
FF(A < v) = (A § v)
=
( v-Low(A) / (High(A) - Low(A) )
falls High(A) ∫ Low(A)
FF(v1 § A § v2)
•
=
(v2 - v1 ) / (High(A) - Low(A))
falls High(A) ∫ Low(A)
Kostenschätzung für komplexe Selektionen: Hier wird der Filterfaktor mit Hilfe von
wahrscheinlichkeitstheoretischen Formeln geschätzt, wobei unabhängige
Verteilungen angenommen werden:
FS 2014
FF(f ⁄ g)
=
FF(f) * FF(g)
FF(f ¤g)
=
FF(f) + FF(g) – FF (f) * FF(g)
FF(¬ f)
=
1 – FF(f)
Datenbanken (CS243) – Anfrageoptimierung
5-31
Konjunktive Filterprädikate
Bei Filterprädikaten der Form f1 ⁄ ... ⁄  fn bestehen folgende Möglichkeiten:
•
Falls alle Bedingungen fi (1 § i § n) der Form Ai = vi sind und falls für eine
Permutation von A1, ... , An ein zusammengesetzter Index existiert, dann
kann ein Index-Scan auf dem zusammengesetzten Index verwendet werden.
•
Falls für jede Bedingung fi (1 § i § n) ein separater (indirekter, unclustered) Index
über Ai verwendet werden kann, dann können die beim Index-Scan
zurückgelieferten TID-Listen miteinander geschnitten werden. (Im allgemeinen
wird dabei angenommen, dass alle TID-Listen im Hauptspeicher Platz haben.)
Die I/O-Kosten sind dann:
•
•
S(NLevels(IAi)-1+ FF(A=vi) · NLeaves(IAi)) + P FF(Ai=vi) · Card(R)
Falls nur für einige der Bedingungen ein Index verwendet werden kann, dann
können die aus der Index-gestützten Suche resultierenden TID-Listen verwendet
werden, um die entsprechenden Tupel zu holen und die restlichen Bedingungen
auf diesen Tupeln explizit zu überprüfen.
Der Optimierer sollte die Benutzung von Indizes in absteigender Reihenfolge ihrer
Selektivität erwägen (kleinste Selektivität zuerst!) und einen Index dann nicht
benutzen, wenn er nur noch einen marginalen Nutzen bringen würde.
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-32
16
Beispiele für die Kostenschätzung …
Annahme: alle Indizes indirekt und unclustered:
• Statistiken des DB-Systems:
Tabelle
Card
NPages
Bestellungen
10‘000‘000
250‘000
Spalte
ColCard
KNr
100‘000
PNr
Monat
Tag
MonatTag
•
Low
High
ColLen
NLevels NLeaves
1 100‘000
24
3
25‘000
10‘000
1
10‘000
24
3
20‘000
12
1
12
12
3
21‘600
31
1
31
12
300
101
1231
24
Zusammengesetzter Index
I/O-Kosten der Anfrage s[KNr = :c ](Bestellungen)
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-33
… Beispiele für Kostenschätzung
•
I/O-Kosten der Anfrage s[Monat=11 ⁄ Tag=11] (Bestellungen)
•
I/O-Kosten der Anfrage
s[KNr = :k ⁄ PNr = :p ⁄ Monat = :d ⁄ Tag=:t] (Bestellungen)
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-34
17
Verfahren für Equi-Joins: Nested-Loop …
Zu den wichtigsten Ausführungsprimitiven in Datenbanksystemen gehören
Algorithmen zur Berechnung von Joins zwischen zwei oder mehr Relationen.
Der einfachste Algorithmus ist der Nested-Loop-Join
Nested-Loop-Join: In einer geschachtelten Schleife wird über beide Relationen
iteriert. Zunächst muss also festgelegt werden, welche Relation die äussere
(outer) und welche die innere (inner) ist
for each Tupel r in outer do {
/* Finde alle Tupel s in inner mit s.Joinattribut =
r.Joinattribut */
for each Tupel s in inner do {
if r.Joinattribut = s.Joinattribut
then Füge kombiniertes Tupel (r,s) in Resultatmenge ein
} /* for */
} /* for */
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-35
… Verfahren für Equi-Joins: Nested-Loop …
•
Beobachtungen
– Es wird ein Tupel nach dem anderen gelesen („one-tuple-at-a-time“)
– die Reihenfolge der ri und sj ist beliebig
– die Form der Join-Bedingung F ist im Prinzip beliebig; es können auch andere
als Equi-Joins unterstützt werden
– Zugriffspfade auf outer und inner S wären hilfreich
– Der Nested-Loop-Join lässt sich leicht auf > 2 Relationen erweitern
(mehrfache Schachtelung)
•
Prinzipielle Verbesserungsmöglichkeiten
– Zugriffspfade ausnutzen: Nested Loop mit Index (Join Index)
– Pufferung („set-at-a-time“ Modus): Nested Block Methode
– Sortierung ausnutzen bzw. zuvor sortieren: Merge Join (Sort-Merge-Join)
– Bessere Ausnutzung der Form der Join-Bedingung (EquiJoin): Hash-Joins
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-36
18
… Verfahren für Equi-Joins: Nested-Loop
Nested-Loop-Join mit Indexzugriff auf die innere Relation:
• Zunächst: wiederum Festlegung der äusseren (outer) und der inneren (inner)
Relation
for each Tupel r in outer do {
Finde mittels Indexzugriff die TIDs aller Tupel s mit
s.Joinattribut = r.Joinattribut
for each TID do {
Hole Tupel s und füge kombiniertes Tupel (r,s) in
Resultatmenge ein
} /* for */
} /* for */
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-37
Verfahren für Equi-Joins: Nested Block Join
Der Nested Block Join stellt eine verbesserte Variante für Nested Loops
(ohne Indexunterstützung) dar:
• Zunächst: wiederum Festlegung der äusseren (outer) und der inneren (inner)
Relation
for each Seite (bzw. Menge benachbarter Seiten) von outer do {
for each Seite (bzw. Menge benachbarter Seiten) von inner do {
/* berechne den Join der Tupel, die in diesen Seiten
liegen*/
for each Tupel r in der outer-Seite do {
for each Tupel s in der inner-Seite do {
if r.Joinattribut = s.Joinattribut then Füge
kombiniertes Tupel (r,s) in Resultatmenge ein
} /* do (Iteration inner) */
} /* do (Iteration outer) */
} /* do (inner block) */
} /* do (outer block) */
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-38
19
Verfahren für Equi-Joins: Merge Join …
Merge-Join: (wiederum Festlegung von outer und inner benötigt)
r := erstes Tupel von outer (sortiert bzgl. Joinattribut)
s := erstes Tupel von inner (sortiert bzgl. Joinattribut)
while (nicht alle Tupel von outer bearbeitet) or
(nicht alle Tupel von inner bearbeitet) do {
if r.Joinattribut = s.Joinattribut then {
s1 := s
while r.Joinattribut = s.Joinattribut do {
Füge kombiniertes Tupel (r,s) in Resultatmenge ein
s := nächstes Tupel von inner
} /* while */
s := s1 /* Gehe zurück zum ersten inner-Tupel mit
diesem Joinattributwert */
r := nächstes Tupel von outer
} /* then */
else {
if r.Joinattribut < s.Joinattribut
then r := nächstes Tupel von outer
else s := nächstes Tupel von inner
} /* else */
} /* while */
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-39
… Verfahren für Equi-Joins: Merge Join
•
Bemerkung: Die Schleife über die beiden Relationen ist häufig als Index-Scan
über dem Joinattribut der jeweiligen Relation realisiert.
•
Der Merge Join wird zum Teil auch als Merge-Scan-Join bezeichnet
•
Eine Variante des Merge Joins, der Sort-Merge-Join, sortiert zunächst die beiden
am Join beteiligten Relationen, bevor der Join durchgeführt wird
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-40
20
Zusammenfassung Kapitel 5
Anfrageoptimierung besteht aus folgenden Schritten
1. Algebraische Optimierung: Anwendung von Äquivalenzregeln der
Relationenalgebra
2. Auswahl der geeigneten Operatoren basierend auf den verfügbaren
Zugriffspfaden
FS 2014
Datenbanken (CS243) – Anfrageoptimierung
5-41
21