6. Basisalgorithmen für DB-Operationen Einordnung

6. Basisalgorithmen für DB-Operationen
Einordnung
Datenbankparameter
MOS
Komplexität von Grundalgorithmen
Mengenorientierte Schnittstelle
Datensystem
Unäre Operationen (Scan, Selektion, Projektion)
Binäre Operationen: Mengenoperationen
SOS
Satzorientierte Schnittstelle
Zugriffssystem
Berechnung von Verbunden
ISS
Interne Satzschnittstelle
Speichersystem
SPS
Systempufferschnittstelle
Pufferverwaltung
DS
Dateischnittstelle
Betriebssystem
GS
Geräteschnittstelle
VL Datenbank-Implementierungstechniken – 6–1
Datenbankparameter
VL Datenbank-Implementierungstechniken – 6–2
Datenbankparameter (II)
Komplexitätsbetrachtungen (O(n2 ))
nr : Anzahl Tupel in Relation r
Aufwandsabschätzungen (konkret)
br : Anzahl von Blöcken (Seiten), die Tupel aus r
beinhalten
Datenbankparameter als Grundlage
müssen im Katalog des Datenbanksystems
sr : (mittlere) Größe von Tupeln aus r (s für size)
fr : Blockungsfaktor (Tupel aus r pro Block)
gespeichert werden
fr =
bs
,
sr
mit bs Blockgröße
Tupel einer Relation kompakt in Blöcken:
nr
br =
fr
VL Datenbank-Implementierungstechniken – 6–3
VL Datenbank-Implementierungstechniken – 6–4
Datenbankparameter (III)
Komplexität von Grundalgorithmen
V (A, r): Anzahl verschiedener Werte für das Attribut A
in der Relation r (V für values): V (A, r) = |πA (r)|
Grundannahmen
Indexe B+ -Bäume
A Primärschlüssel: V (A, r) = nr
dominierender Kostenfaktor: Blockzugriff
SC(A, r): Selektionskardinalität (selection cardinality );
Zugriff auf Hintergrundspeicher auch für
durchschnittliche Anzahl von Ergebnistupeln bei
σA=x (r) für x ∈ πA (r)
Schlüsselattribut A: SC(A, r) = 1
Zwischenrelationen
Zwischenrelationen zunächst für jede Grundoperation
Zwischenrelationen hoffentlich zum großen Teil im
Allgemein:
Puffer
nr
SC(A, r) =
V (A, r)
einige Operationen (Mengenoperationen) auf
Adreßmengen (TID-Listen)
Weiterhin: Verzweigungsgrad bei B-Baum-Indexen, Höhe
des Baums, Anzahl von Blätterknoten
VL Datenbank-Implementierungstechniken – 6–5
Hauptspeicheralgorithmen
VL Datenbank-Implementierungstechniken – 6–6
Zugriffe auf Datensätze
wichtig für den Durchsatz des Gesamtsystems, da sie sehr
oft eingesetzt werden
Tupelvergleich
Relationen: interner Identifikator RelID
Indexe: interner Identifikator IndexID
Primärindex, etwa I(Personen(PANr)))
(Duplikate erkennen, Sortierordnung angeben, . . . )
iterativ durch Vergleich der Einzelattribute, Attribute mit
großer Selektivität zuerst
TID-Zugriff
bei A = a wird maximal ein Tupel pro Zugriff
Sekundärindex, etwa I(Ausleihe(PANr)))
Bsp.: PANr = 4711 liefert i.a. mehrere Tupel
Indexzugriffe: Ergebnis TID-Listen
TID innerhalb des Hauptspeichers: übliche
Vorgehensweise bei der Auflösung indirekter Adressen
VL Datenbank-Implementierungstechniken – 6–7
VL Datenbank-Implementierungstechniken – 6–8
Zugriffe auf Datensätze (II)
Beispiel in SQL
fetch-tupel Direktzugriff auf Tupel mittels TID-Wertes
holt Tupel in Tupel-Puffer
fetch-tupel(RelID, TID) → Tupel-Puffer
select *
from KUNDE
where KName =’Meier’
Gleichheitsanfrage über einen Schlüssel
fetch-TID: TID zu (Primärschlüssel-)Attributwert
bestimmen
put: hier Anzeige des Ergebnisses
fetch-TID(IndexID, Attributwert) → TID
weiterhin auf Relationen und Indexen: Scans
aktuellerTID :=
fetch-TID(KUNDE-KName-Index, ‘Meyer’);
aktuellerPuffer:=
fetch-tupel(KUNDE-RelationID, aktuellerTID);
put(aktuellerPuffer);
VL Datenbank-Implementierungstechniken – 6–9
Externe Sortieralgorithmen
Unäre Operationen
Scan durchläuft Tupel einer Relation
sort
Andreas 24
Dieter
4
Gunter
42
Dieter
4
7
Berta
77
77
Chris
7
Elle
36
Elle
36
Tamara
99
Dieter
2
Dieter
2
partition
Chris
Berta
Berta
77
Chris
7
Dieter
4
Elle
36
Gunter
42
Andreas 21
Andreas 24
Berta
merge
42
Andreas 24
Andreas 24
merge
Gunter
Andreas 21
77
7
2
Index-Scan nutzt Index zum Auslesen der Tupel in
Dieter
4
Dieter
11
Elle
36
Sortierreihenfolge
Aufwand: Anzahl der Tupel plus Höhe des Indexes
42
Mario
9
Dieter
2
Gunter
Tamara
99
Dieter
11
Mario
9
Dieter
11
Mario
9
Peer
43
Peer
43
Tamara
99
Tamara
99
11
Peer
43
merge
9
43
Dieter
einer Relation in beliebiger Reihenfolge
Aufwand: br
Chris
Peer
Andreas 21
Relationen-Scan (full table scan) durchläuft alle Tupel
Dieter
Mario
Andreas 21
VL Datenbank-Implementierungstechniken – 6–10
Vergleich
Relationen-Scan besser durch Ausnutzung der
Blockung
Index-Scan besser, falls wenige Daten benötigt, aber
Externes Sortieren durch Mischen; Komplexität O(n log n)
Vertauschoperationen
VL Datenbank-Implementierungstechniken – 6–11
schlechter beim Auslesen vieler Tupel
VL Datenbank-Implementierungstechniken – 6–12
Operationen auf Scans
Beispiel: Scan
Relationen-Scan öffnen
open-rel-scan(RelationenID) → ScanID
liefert ScanID zurück, die bei folgenden Operationen
zur Identifikation genutzt wird
select *
from Personen
where Nachname between ’Heuer’ and
’Jagellowsk’
Index-Scan initialisieren
open-index-scan(IndexID, Min, Max) → ScanID
liefert ScanID zurück; Min und Max bestimmen
Bereich einer Bereichsanfrage
next-TID liefert nächsten TID; Scan-Cursor
weitersetzen
end-of-scan liefert true, falls kein TID mehr im Scan
abzuarbeiten
close-scan schließt Scan
VL Datenbank-Implementierungstechniken – 6–13
Beispiel: Relationen-Scan
VL Datenbank-Implementierungstechniken – 6–14
Beispiel: Index-Scan
aktuellerScanID := open-rel-scan(Personen-RelationID);
aktuellerTID := next-TID(aktuellerScanID);
while not end-of-scan(aktuellerScanID) do
begin
aktuellerPuffer :=
fetch-tupel(Personen-RelationID,aktuellerTID);
if aktuellerPuffer.Nachname >= ’Heuer’
and aktuellerPuffer.Nachname <= ’Jagellowsk’
then put (aktuellerPuffer);
endif;
aktuellerTID := next-TID(aktuellerScanID);
end;
close (aktuellerScanID);
VL Datenbank-Implementierungstechniken – 6–15
aktuellerScanID :=
open-index-scan(Personen-Nachname-IndexID,
’Heuer’,’Jagellowsk’);
aktuellerTID := next-TID(aktuellerScanID);
while not end-of-scan(aktuellerScanID) do
begin
aktuellerPuffer :=
fetch-tupel(Personen-RelationID,aktuellerTID);
put(aktuellerPuffer);
aktuellerTID := next-TID(aktuellerScanID);
end;
close (aktuellerScanID);
VL Datenbank-Implementierungstechniken – 6–16
Selektion
Selektion: Konjunktive Normalform
exakte Suche, Bereichsselektionen, komplex
zusammengesetzte Selektionskriterien
Zugriffspfade bei komplexen Prädikaten einsetzen ⇒ ϕ
analysieren und geeignet umformen
zusammengesetztes Prädikat ϕ aus atomaren
Prädikaten (exakte Suche, Bereichsanfrage) mit and,
or, not
etwa ϕ in konjunktive Normalform KNF überführen;
bestehend aus Konjunkten
heuristisch Konjunkt auswählten, das sich besonders
gut durch Indexe auswerten laßt (etwa bei A = c und
über A Index)
Tupelweises Vorgehen
Gegeben σϕ (r)
ausgewähltes Konjunkt auswerten; für
Relationen-Scan: für alle t ∈ r auswerten ϕ(t)
Aufwand O(nr ), genauer br
Ergebnis-TID-Liste andere Konjunkte tupelweise
oder mehrere geeignete Konjunkte auswerten und die
sich ergebenden TID-Listen schneiden
VL Datenbank-Implementierungstechniken – 6–17
Selektion: Filtermethoden
VL Datenbank-Implementierungstechniken – 6–18
Projektion
bei Filtermethode alle Bedingungen auf true setzen,
die nicht durch eine Zugriffsmethode unterstützt
werden
resultierendes Prädikat: ϕ0 .
Relationenalgebra: mit Duplikateliminierung
SQL: keine Duplikateliminierung, wenn nicht mit
distinct gefordert (modifizierter Scan)
mit Duplikateliminerung:
r 0 = σϕ0 (r) unter Ausnutzung der Indexe auswerten
0
σϕ (r ) auf dem (hoffentlich viel kleineren)
0
Zwischenergebnis r mittels tupelweisem Vorgehen
auswerten
sortierte Ausgabe eines Indexes hilft bei der
Duplikateliminierung
Projektion auf indexierte Attribute ohne Zugriff auf
gespeicherte Tupel
Filtermethoden nur gut, wenn ϕ0 tatsächlich
Datenvolumen reduziert (Vorsicht bei Disjunktionen)
VL Datenbank-Implementierungstechniken – 6–19
VL Datenbank-Implementierungstechniken – 6–20
Projektion (II)
Scan-Semantik
Projektion πX (r):
bei Scan-basierten (positionalen)
1. r nach X sortieren
2. t ∈ r werden in das Ergebnis aufnehmen, für die
t(X) 6= previous(t(X)) gilt
Änderungsoperationen: Festlegung einer
Scan-Semantik ; Wirkungsweise nachfolgender
Scan-Operationen
Zeitaufwand: O(nr log nr )
Beispiel: Löschen des aktuellen Satzes
Falls r schon sortiert nach X: O(nr )
Zustände: vor dem ersten Satz, auf einem Satz, in
Lücke zwischen zwei Sätzen, hinter dem letzten Satz,
in leerer Menge
Schlüssel K ⊆ X: O(nr )
weiterhin: Übergangsregeln für Zustände
VL Datenbank-Implementierungstechniken – 6–21
Scan-Semantik (II)
VL Datenbank-Implementierungstechniken – 6–22
Binäre Operationen: Mengenoperationen
Helloween-Problem (System R):
Binäre Operationen meist auf Basis von tupelweisem
Vergleich der einzelnen Tupelmengen
SQL-Anweisung:
Nested-Loops-Technik oder Schleifeniteration
update employee e
set salary = salary * 1.05
für jedes Tupel einer äußeren Relation s wird die
satzorientierte Auswertung mittels Index-Scan über
Iemployee (salary) und sofortige Index-Aktualisierung
ohne besondere Vorkehrungen: unendliche Anzahl von
Gehaltserhöhungen
innere Relation r komplett durchlaufen
Aufwand: O(ns ∗ nr )
Merge-Technik oder Mischmethode
r und s (sortiert) schrittweise in der vorgegebenen
Tupelreihenfolge durchlaufen
Aufwand: O(ns + nr )
Falls Sortierung noch vorzunehmen:
Sort-Merge-Technik
Aufwand nr log nr und/oder ns log ns
VL Datenbank-Implementierungstechniken – 6–23
VL Datenbank-Implementierungstechniken – 6–24
Mengenoperationen (II)
Klassen binärer Operationen
r
Hash-Methoden
kleinere der beiden Relationen in Hash-Tabelle
s
Tupel der zweiten Relation finden ihren
Vergleichspartner mittels Hash-Funktion
idealerweise Aufwand O(ns + nr )
A
B
C
VL Datenbank-Implementierungstechniken – 6–25
Vereinigung mit Duplikateliminierung
Klassen binärer Operationen (II)
Ergebnisextensionen
A
Übereinstimmung
auf allen Attributen
Differenz r − s
B
Schnitt r ∩ s
C
Differenz s − r
A∪B
A∪C
B∪C
A∪B∪C
symmetrische Differenz
(r − s) ∪ (s − r)
Vereinigung r ∪ s
VL Datenbank-Implementierungstechniken – 6–26
Übereinstimmung
auf einigen Attributen
Anti-SemiVerbund
Verbund,
SemiVerbund
Anti-SemiVerbund
Left Outer Join
Anti-Verbund
Right Outer Join
Full Outer Join
VL Datenbank-Implementierungstechniken – 6–27
Vereinigung durch Einfügen
Variante der Nested-Loops-Methoden
Kopie einer der beiden Relationen r2 unter dem
Namen r20 anlegen, dann Tupel t1 ∈ r1 in r20 einfügen
(Zeitaufwand abhängig von Organisationsform der
Kopie)
Spezialtechniken für die Vereinigung
r und s verketten
Projektion auf alle Attribute der verketteten Relation
Zeitaufwand: O((nr + ns ) × log(nr + ns )) (wie Projektion)
VL Datenbank-Implementierungstechniken – 6–28
Berechnung von Verbunden
Vereinigung (II)
Vereinigung durch Merge-Techniken (merge-union)
Varianten
1. r und s sortieren, falls nicht bereits sortiert
Nested-Loops-Verbund
2. r und s mischen
tr ∈ r kleiner als ts ∈ s: tr in das Ergebnis, nächstes
tr ∈ r lesen
tr ∈ r größer als ts ∈ s: ts in das Ergebnis, nächstes
ts ∈ s lesen
ts = tr : tr in das Ergebnis, nächste tr ∈ r bzw. ts ∈ s
lesen
Block-Nested-Loops-Verbund
Merge-Join
Hash-Verbund
...
Zeitaufwand: O(nr × log nr + ns × log ns ) mit
Sortierung, O(nr + ns ) ohne Sortierung
VL Datenbank-Implementierungstechniken – 6–29
Nested-Loops-Verbund
VL Datenbank-Implementierungstechniken – 6–30
Nested-Loops-Verbund mit Scan
doppelte Schleife iteriert über alle t1 ∈ r und alle t2 ∈ s bei
einer Operation r ./ s
r ./ϕ s:
for each tr ∈ r do
begin
for each ts ∈ s do
begin
if ϕ(tr , ts ) then put(tr · ts ) endif
end
end
VL Datenbank-Implementierungstechniken – 6–31
R1ScanID := open-rel-scan(R1ID);
R1TID := next-TID(R1ScanID);
while not end-of-scan(R1ScanID) do
begin
R1Puffer := fetch-tupel(R1ID,R1TID);
R2ScanID := open-rel-scan(R2ID);
R2TID := next-TID(R2ScanID);
while not end-of-scan(R2ScanID) do
begin
.../* Scan über innere Relation */
end;
close (R2ScanID);
R1TID := next-TID(R1ScanID);
end;
close (R1ScanID);
VL Datenbank-Implementierungstechniken – 6–32
Block-Nested-Loops-Verbund
Nested-Loops-Verbund mit Scan II
/* Scan über innere Relation */
R2Puffer := fetch-tupel(R2ID,R2TID);
if R1Puffer.X = R2Puffer.Y
then insert into ERG
(R1.Puffer.A1, ..., R1.Puffer.An, R1.Puffer.X,
R2.Puffer.B1, ..., R1.Puffer.Bm);
endif;
R2TID := next-TID(R2ScanID);
Verbesserung: Nested-Loops-Verbund verbindet alle t1 ∈ r
mit Ergebnis von σX=t1 (X) (s) (gut bei Index auf X in r2 )
statt über Tupel über Blöcke iterieren
for each Block Br of r do
begin
for each Block Bs of s do
begin
for each Tupel tr ∈ Br do
begin
for each Tupel ts ∈ Bs do
begin
if ϕ(tr , ts ) then put(tr · ts ) endif
end
end
end
end
Aufwand: br ∗ bs
VL Datenbank-Implementierungstechniken – 6–33
Merge-Techniken
VL Datenbank-Implementierungstechniken – 6–34
Merge-Techniken: Aufwand
X := R ∩ S; falls nicht bereits sortiert, zuerst Sortierung
von r und s nach X
1. tr (X) < ts (X), nächstes tr ∈ r lesen
alle Tupel haben den selben X-Wert: O(nr × ns )
X Schlüssel von R oder S: O(nr log nr + ns log ns )
bei vorsortierten Relationen sogar: O(nr + ns )
2. tr (X) > ts (X), nächstes ts ∈ s lesen
3. tr (X) = ts (X), tr mit ts und allen Nachfolgern von ts ,
die auf X mit ts gleich, verbinden
4. beim ersten t0s ∈ s mit t0s (X) 6= ts (X) beginnend mit
ursprünglichem ts mit den Nachfolgern t0r von tr
wiederholen, solange tr (X) = t0r (X) gilt
VL Datenbank-Implementierungstechniken – 6–35
VL Datenbank-Implementierungstechniken – 6–36
Merge-Join mit Scan
Merge-Join mit Scan (II)
Verbund-Attribute auf beiden Relationen
Schlüsseleigenschaft
min(X) und max(X): minimaler bzw. maximaler
gespeicherter Wert für X
R1ScanID := open-index-scan(R1XIndexID,
min(X), max(X));
R1TID := next-TID(R1ScanID);
R1Puffer := fetch-tupel(R1ID,R1TID);
R2ScanID := open-index-scan(R2YIndexID,
min(Y), max(Y));
R2TID := next-TID(R2ScanID);
R2Puffer := fetch-tupel(R2ID,R2TID);
while not end-of-scan(R1ScanID)
and not end-of-scan(R2ScanID) do
begin
.../* merge */
end;
close (R1ScanID);
close (R2ScanID);
VL Datenbank-Implementierungstechniken – 6–37
VL Datenbank-Implementierungstechniken – 6–38
Verbund durch Hashing
Merge-Join mit Scan (III)
/* merge */
if R1Puffer.X < R2Puffer.Y
then R1TID := next-TID(R1ScanID);
R1Puffer := fetch-tupel(R1ID,R1TID);
else if R1Puffer.X > R2Puffer.y
then R2TID := next-TID(R2ScanID);
R2Puffer := fetch-tupel(R2ID,R2TID);
else insert into ERG
(R1.Puffer.A1, ..., R1.Puffer.An, R1.Puffer.X,
R2.Puffer.B1, ..., R1.Puffer.Bm);
R1TID := next-TID(R1ScanID);
R1Puffer := fetch-tupel(R1ID,R1TID);
R2TID := next-TID(R2ScanID);
R2Puffer := fetch-tupel(R2ID,R2TID);
endif;
endif;
VL Datenbank-Implementierungstechniken – 6–39
Idee:
Ausnutzung des verfügbaren Hauptspeichers zur
Minimierung der Externspeicherzugriffe
Finden der Verbundpartner durch Hashing
Anfragen der Form r 1r.A=s.B s
VL Datenbank-Implementierungstechniken – 6–40
Classic Hashing
Partitionierung mittels Hashfunktion
Vorbereitung: kleinere Relation wird r
Tupel aus r und s über X in gemeinsame Datei mit k
Blöcken (Buckets) „gehasht“
Ablauf
1. Tupel von r mittels Scan in Hauptspeicher lesen
und mittels Hashfunktion h(r.A) in Hashtabelle H
einordnen
2. wenn H voll (oder r vollständig gelesen):
Scan über S und mit h(s.B) Verbundpartner suchen
3. falls Scan über r nicht abgeschlossen:
H neu aufbauen und erneuten Scan über S
durchführen
Tupel in gleichen Buckets durch Verbundalgorithmus
verbinden
Aufwand: O(br + p ∗ bs ) mit p ist Anzahl der Scans über
s
VL Datenbank-Implementierungstechniken – 6–41
Partitionierung mittels Hashfunktion (II)
0
join
1
join
1
join
2
h
s
max
...
2
...
...
0
...
h
r
join
VL Datenbank-Implementierungstechniken – 6–42
Partitionierung mittels Hashfunktion (III)
for each tr in r do
begin
i := h(tr (X));
Hir := Hir ∪ tr (X);
end;
for each ts in s do
begin
i := h(ts (X));
His := His ∪ ts (X);
end;
for each k in 0 . . . max do
Hkr ./ Hks ;
max
VL Datenbank-Implementierungstechniken – 6–43
VL Datenbank-Implementierungstechniken – 6–44
Vergleich der Techniken
s
r
Aggregation & Gruppierung
s
r
s
Anfragen:
select A, count(*)
from T
group by A
r
Algebraoperator: γcount(*),A (r(t))
Implementierungsvarianten:
Nested-Loops-Join
Merge-Join
Hash-Join
Nested Loops
Sortierung
Hashing
VL Datenbank-Implementierungstechniken – 6–45
VL Datenbank-Implementierungstechniken – 6–46