1 / 54

Kapitel 11

Kapitel 11. Anfragebearbeitung. Lernziele. . Relationenalgebra Ablauf der Anfrageoptimierung Logische Optimierung Umwandlung von SQL in Relationenalgebra Heuristische Optimierung Physische Optimierung Unterschiedliche JOIN Algorithmen Kostenbasierte Optimierung.

denis
Download Presentation

Kapitel 11

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Kapitel 11 Anfragebearbeitung

  2. Lernziele  • Relationenalgebra • Ablauf der Anfrageoptimierung • Logische Optimierung • Umwandlung von SQL in Relationenalgebra • Heuristische Optimierung • Physische Optimierung • Unterschiedliche JOIN Algorithmen • Kostenbasierte Optimierung Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  3. Optimierung von Datenbank- Anfragen: Grundsätze • SehrhohesAbstraktionsniveau der mengenorientiertenSchnittstelle (SQL). • Sieistdeklarativ, nicht-prozedural, d.h. eswirdspezifiziert, was man findenmöchte, abernichtwie. • Das wiebestimmtsichaus der Abbildung der mengenorientiertenOperatoren auf Schnittstellen-Operatoren der internenEbene (Zugriff auf Datensätze in Dateien, Einfügen/EntferneninternerDatensätze, ModifiziereninternerDatensätze). • Zueinemwaskanneszahlreichewie‘sgeben: effizienteAnfrageauswertungdurchAnfrageoptimierung. • i.A. wirdabernicht die optimaleAuswertungsstrategiegesucht (bzw. gefunden) sonderneineeinigermaßeneffizienteVariante • Ziel: „avoiding the worst case“ Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  4. Ablauf der Anfrageoptimierung Scanner Parser Sichtenauflösung Anfrage- Optimierer Codeerzeugung Ausführung Deklarative Anfrage Algebraischer Ausdruck Auswertungs- Plan (QEP) Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  5. Relationenmodell revisited: Grundbegriffe  DB: Relationen (tables) Ausprägung • dom (PersNr)  dom (Name)  ... dom (Raum) Schema Attribut (Column) Tupel (Record, Row) Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  6. Relationenmodell revisited: Definitionen • Gegeben sei eine Menge von Wertebereichen primitiver Datentypen {D1, ..., Dm}, die als "Domains" • bezeichnet werden. • Eine RelationRist ein PaarR = (s,v) mit • einem Schemas = sch(R) = {A1, ..., An}, das aus einer Menge von • Attributen(Attributnamen) besteht undfür jedes AttributAieinen Domaindom(Ai)  {D1, ..., Dm} festlegt, und • einer Ausprägungv = val(R)dom(A1) dom(A2)  ... dom(An). Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  7. Relationenalgebra(RA): Operationen • Eine Operation der RA hat eine oder mehrere Relationen als Operanden und liefert eine Relation als Ergebnis(Abgeschlossenheit der Algebra) • Operationen in RA: • Mengenoperationen (Vereinigung, Durchschnitt, …) • Zuweisung / Umbenennung • Selektion, Projektion • Joins • Division Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  8. Mengenoperationen • Für zwei Relationen R, S mit sch(R) = sch(S)sind die üblichen Mengenoperationen definiert: • Vereinigung (Union)R  S: • Durchschnitt (Intersection)R S: • Differenz (Difference) R  S: Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  9. Zuweisung • Idee: • Umbenennung von Relationen und/oder einzelnen Attributen Seien R, S Relationen mit sch(R) = { A1, ..., An } und sch(S) = { B1, ..., Bn }, so dass für alle i gilt: dom(Ai) = dom(Bi). Die ZuweisungR := Sbedeutet: val(R) = val(S). Ausführlicher schreibt man auch R(A1, ..., An) := S(B1, ..., Bn). Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  10. Umbenennung • Vereinfachte Form der Zuweisung:explizite Umbenennung von einzelnen Attributen oder Relationen • Umbennung von einzelnen Attributen •  [Voraussetzung  Vorgänger](voraussetzen) (.. Attribut "Vorgänger" wird in "Voraussetzung" umbenannt) •  • Umbenennung von Relationen  [V1] (voraussetzen) (.. Relation "voraussetzen" wird in "V1" umbenannt) Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  11. Selektion • Selektion  (Filterung, Auswahl von Zeilen einer Tabelle): • Das Resultat einer Selektion [F](R) auf einer Relation R ist: • Die Menge der möglichen Filterformeln F ist: • Für Attribute A, B aus sch(R) mit dom(A) = dom(B), Konstante cdom(A) und Vergleichsoperationen  {=, , , , , } sind AB und Ac zulässige Filterbedingungen. • 2) Falls F1 und F2 zulässige Filterbedingungen sind, dann sind auch • F1F2, F1F2, F1 und (F1) zulässig. • 3) Nur die von 1) und 2) erzeugten Filterbedingungen sind zulässig. Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  12. Projektion Projektion  (Auswahl von Spalten einer Tabelle): Sei Asch(R). Das Resultat einer Projektion [A](R) ist: Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  13. Selektion vs. Projektion Projektion Selektion Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  14. Selektion / Projektion: Beispiel Selektion:  [Semester > 10] (Studenten) Projektion:  [Rang] (Professoren) Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  15. Beispiel: Selektion/Projektion mit Umbenennung • Mengendurchschnitt nur auf zwei Argumentrelationen mit gleichem Schema anwendbar. Deshalb ist die Umbenennung des Attributes gelesenVon in PersNr in der Relation Vorlesungen notwendig. Anfrage: PersNr aller C4-Professoren, die mindestens eine Vorlesung halten. Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  16. Natural Join Natural Join: || Natürlicher Verbund von Relationen über gleiche Attributnamen und Attributwerte. Das Resultat vonR || SmitA = sch(R) undB = sch(S) ist: P (X1, ..., Xm, Y1, ..., Yk) Q (Y1,..., Yk, Z1, ..., Zn) P |×| Q P  Q Q  P P  Q Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  17. Natural Join: Beispiel ||  Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  18. Kartesisches Produkt Kartesisches Produkt:  Seien R, S Relationen mit Schemata A = sch(R) und B = sch(S). Sei A' ein Schema, bei dem alle Ai, die auch in B vorkommen, unbenannt sind in R.Ai, und sei B' ein analoges Schema mit Attributnamen S.Ai. Das Resultat von RS ist: P (X1,..., Xm, Y1,..., Yk) Q (Y1,..., Yk, Z1,..., Zn) P × Q Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  19. Kartesisches Produkt: Beispiel × sch(Professoren) sch(hören) Problem: potentiell riesige Zwischenergebnisse Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  20. Äquivalenzregeln ("Rechenregeln") der RA • Kommutativitätsregeln: • 1) falls P nur R1-Attribute enthält • 2) • Assoziativitätsregel: • 3) • Idempotenzregeln: • 4) • 5) • Distributivitätsregeln: • 6) • 7) • 8) falls P nur R-Attribute enthält • 9) falls Joinattribute • 10) R1S1 • Invertierungsregel: • 11) Grundlagen der Datenbanken, WS 08/09 Kapitel 5: Relationale Algebra

  21. Ausdrucksmächtigkeit der RA Die Menge der relationenalgebraischen Ausdrücke über einer Menge von RelationenP1, ..., Pnist wie folgt definiert: (i) P1, ..., Pnsind Ausdrücke. (ii) WennR, S, T, QAusdrückesind, F eineFilterformelübersch(P) ist, A sch(R), sch(S)=sch(T) undsch(R) sch(Q) gilt, dannsind [F](R), [A](R), R |×| S, R × S, R |*| S, ST, ST, S - T, RQ auch Ausdrücke. (iii) Nur die von (i) und (ii) erzeugten Ausdrücke sind RA-Ausdrücke. • Satz: • , , , , –, bilden eine minimale Menge von Operationen, mit denen sich alle Operationen der RA ausdrücken lassen. Eine Anfragesprache heißt relational vollständig, wenn sich damit alle Anfragen der (minimalen) Relationenalgebra ausdrücken lassen. Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  22. Eigenschaften der Relationenalgebra (1/2)  • Satz: Alle Ergebnismengen von Operationen der relationalen Algebra sind endlich. • Beweisskizze: Man zeige für alle Operationen ×, , , , – und, dass für endliche Eingaberelationen und beliebige Filterformeln das Ergebnis auch endlich ist. Beispiel: R × S: Sei die Anzahl der Tupel in R: r = |val(R)| und in S: s=|val(S)|. Dann ist sowohl r als auch s endlich (s. oben). Die Anzahl der Tupel in R × S: rxs = |val(R × S)| kann bestimmt werden als rxs ≤ r*s, da für jedes Element in val(R) höchstens s Ausprägungen in R × S entstehen können. rxs ist also endlich. □ Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  23. Eigenschaften der Relationenalgebra (2/2)  • Mit ähnlichem Beweisschema gilt auch: • Die Größe der Ergebnismenge von Operationen der relationalen Algebra kann sicher nach oben abgeschätzt werden, wenn die Größe der Eingaberelationen bekannt ist • Die Laufzeit zur Auswertung eines Ausdrucks der Relationenalgebra kann sicher nach oben abgeschätzt werden, wenn sowohl die Größe der Eingaberelationen bekannt ist als auch die nach oben abgeschätzte Laufzeit des Berechnungsalgorithmen der eingesetzten Einzeloperationen Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  24. Offene Fragen:  • Kann SQL die Relationenalgebra abbilden? • JA, SQL ist relational vollständig • Kann die Relationenalgebra SQL abbilden? • JEIN • Es kommt auf den Dialekt an. • Was bei den meisten fehlt sind • Multimengen, • Aggregationen, • Gruppierung und • transitive Hülle • Diese können allerdings mit geringem Zusatzaufwand eingefügt werden Grundlagen der Datenbanken, WS 10/11 Kapitel 5: Relationale Algebra

  25. Logische Optimierung • Übersetzen der SQL-Anfrage in die Relationenalgebra • Äquivalentes Umformen der RA um die Anfragebearbeitung zu beschleunigen Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  26. AllgemeingültigeÜbersetzung SELECT A1, ..., An FROM R1, ..., Rk WHERE P Rk R3 R1 R2 Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  27. AllgemeingültigeÜbersetzung (Beispiel) SELECTTitel FROMProfessoren, Vorlesungen WHERE Name = ´Popper´ AND PersNr = gelesenVon Vorlesungen Professoren Problem: ProfessorenVorlesunghat isteinsehrgroßesZwischenergebnise Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  28. Erste Optimierungsidee SELECTTitel FROMProfessoren, Vorlesungen WHERE Name = ´Popper´ AND PersNr = gelesenVon Vorlesungen Professoren Das Zwischenergebnisist nun deutlichkleiner. Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  29. Zur Erinnerung: Äquivalenzregeln der RA • Kommutativitätsregeln: • 1) falls P nur R1-Attribute enthält • 2) • Assoziativitätsregel: • 3) • Idempotenzregeln: • 4) • 5) • Distributivitätsregeln: • 6) • 7) • 8) falls P nur R-Attribute enthält • 9) falls Joinattribute • 10) R1S1 • Invertierungsregel: • 11) Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  30. Heuristische Anwendung der Transformationsregeln • 1. Mittels Regel 1 werdenkonjunktiveSelektionsprädikate in Kaskaden von -Operationenzerlegt. • 2. MittelsRegeln 2, 4, 6, und 9 werdenSelektionsoperationensoweit "nachunten" propagiertwiemöglich. • 3. Mittels Regel 8 werden die Blattknoten so vertauscht, dassderjenige, der das kleinsteZwischenergebnisliefert, zuerstausgewertetwird. • 4. Formeeine-Operation, die von einer-Operation gefolgtwird, wennmöglich in eine Join-Operation um • 5. MittelsRegeln 3, 4, 7, und 10 werdenProjektionensoweitwiemöglichnachuntenpropagiert. • 6. VersucheOperationsfolgenzusammenzufassen, wennsie in einemDurchlaufausführbarsind(z.B. Anwendung von Regel 1, Regel 3, aberauchZusammenfassungaufeinanderfolgenderSelektionen und Projektionenzueiner Filter-Operation). Selektion nach unten Kleine Zwischenergebnisse Besser Join als Kartesisch Projektionen nach unten(jedoch über die Selektionen) Operationen zusammenfassen Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  31. Anwendung der Transformationsregeln SELECT distinct s.Semester FROM Studenten s, hören h Vorlesungen v, Professoren p WHERE p.Name = ´Sokrates´ AND v.gelesenVon = p.PersNr AND v.VorlNr = h.VorlNr AND h.MatrNr = s.MatrNr p v s h Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  32. Aufspalten der Selektionsprädikate p v p s h v h s Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  33. Verschieben der Selektionsprädikate v p p s h v h s Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  34. Zusammenfassung von Selektionen und Kreuzprodukten zu Joins v p p v s h s h Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  35. Optimierung der JoinreihenfolgeKommutativität und Assoziativität ausnutzen s h p v v s h p Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  36. Was bringt das ? 4 4 13 3 13 1 s h p v v s h p Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  37. Einfügen von (zusätzlichen) Projektionen s s h h v v p p Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  38. Physische Optimierung • Ziel ist es gute Algorithmen für die Ausführung auswählen • Bei Join: • Nested-Loop-Join • Index-Nested-Loop-Join • Merge-Join • Hash-Join • Indexe und Vorsortierungen sollten möglichst gut ausgenutzt werden. • Nach Bedarf werden Indexe und Sortierungen akut erstellt. Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

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

  40. Implementierungvon Join: Strategien • J1 nested (inner-outer) loop • „brute force“-Algorithmus foreachrR foreachs  S ifs.B= r.AthenRes := Res (r s) • Laufzeit: O(|R|*|S|) ~ O(n²) • KeineVorbedingungen • Auchgeeignetfür das kartesischeProdukt • Fazit: Der einfachste und flexibelste Join Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  41. Implementierungvon Join: Strategien • J2 Zugriffsstruktur auf S • Index Nested Loop Join • in jedemDurchlauf von Rwerdennur die in S qualifizierendenTupelgelesen • Vorbedingung: ein Index auf B foreachrR • foreachs  S[B=r.A] • Res := Res (r s) • Laufzeit: O(|R|*query(S[B=r.A])*|S[B=r.A]|) • Queryzeitist O(log n) für B-Bäume und O(1) für Hash • Wenn B Schlüsselist, dannist |S[B=r.A]|= 1 • O(n) imbesten Fall bis O(n²) imschlechtesten (r,s: r.A = s.B) • Meistens O(n log n) Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  42. Implementierungvon Join: Strategien • J3 Merge-Join • Vorbedingung: BeideRelationensindpassendsortiert. • 2 pointer r, s =0 • while (R[r].exists() && S[s].exists()) { if (R[r] == S[s]) { output.add(R[r]);r++;s++; } else if (r< s) r++; else s++; } • falls AoderBSchlüsselattributist, wirdjedesTupel in R und Snurgenaueinmalgelesen-> O(|R|+|S|) ~O(n) • Kannsichsogarlohnen, wenn R oder S erstsortiertwerdenmüssen Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  43. Implementierungvon Join: Strategien • J4 Hash-Join • R und Swerdenmittels der gleichenHashfunktionh – angewendet auf R.A und S.B – auf (dieselben) Hash-Buckets abgebildet • Hash-Buckets sindi.Allg. auf demHintergrundspeicher (abhängig von der Größe der Relationen) • ZuverbindendeTupelbefindensichdannimselben Bucket • Wird (nachpraktischen Tests) nur von J3 „geschlagen“, wenn die Relationenschonvorsortiertsind Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  44. Übersetzung der logischen Algebra AR.A=S.B R S NestedLoopR.A=S.B MergeJoinR.A=S.B R [Bucket] [SortR.A] [SortS.B] S R S IndexJoinR.A=S.B HashJoinR.A=S.B R [HashS.B | TreeS.B] R S Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung S

  45. Implementierung von Select: Strategien • S1 Select • Brute-Force: Jedes Tupel wird einzeln überprüft • O(n) • S2 IndexSelect • Der Index wird benutzt um die Anfrage schneller zu beantworten • O(1) bei Hash Index und Gleichheit • O(log n) bei B-Baum und Gleichheit • O(log n) bei B-Baum und Bereichsanfrage Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  46. Übersetzung der logischen Algebra IndexSelectP R P R SelectP R Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  47. Implementierung der Projektion • Wenn mit dem Projektionsergebnis weitergerechnet wird, muss das Zwischenergebnis dupliziert werden. • Die Duplikation sollte nach Möglichkeit einen Index oder eine Sortierung erhalten, wenn diese noch weiter benötigt werden. Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  48. Übersetzung der logischen Algebra [IndexDup] [Hash | Tree] Projectl R [SortDup] Sort Projectl R l R [NestedDup] Projectl R Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  49. Wiederholung der Optimierungsphasen s.Semester select distinct s.Semester from Studenten s, hören h Vorlesungen v, Professoren p where p.Name = ´Sokrates´ and v.gelesenVon = p.PersNr and v.VorlNr = h.VorlNr and h.MatrNr = s.MatrNr p.Name = ´Sokrates´ and ...  p   v s h Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

  50. s s h h v v Datenbanken für Mathematiker, WS 11/12 Kapitel 11: Anfragebearbeitung

More Related