8. Datenbanktuning

8. Datenbanktuning
Motivation
§ Fokus bisher lag bisher auf sinnvoller Abbildung
eines Ausschnitts der realen Welt
in einer relationalen Datenbank
§ Beliebige SQL-Anfragen können auf den Daten
ausgewertet werden, sobald sie einmal in
relationaler Datenbank gespeichert sind
§ RDBMSs bieten verschiedene Möglichkeiten, um die
typischen Anfragen, die aus einer Anwendung
heraus gestellt werden, in kürzerer Zeit
beantworten zu können
Datenbanken / Kapitel 8: Datenbanktuning
2
Inhalt
§ 8.1 Indizes
§ 8.2 Statistiken
§ 8.3 Systemparameter
Datenbanken / Kapitel 8: Datenbanktuning
3
8.1 Indizes
§ RDBMS speichern die Datensätze (Tupel) einer
Tabelle (Relation) in den Seiten einer Datendatei
§ Indizes erlauben eine Beschleunigung des Zugriffs
auf die Datensätze anhand ihres Primärschlüssel
oder anhand von Attributwerten
§ RDBMS muss angelegte Indizes synchron mit den Daten
halten und Änderungen an den Daten in die
entsprechenden Indizes übernehmen
Datenbanken / Kapitel 8: Datenbanktuning
4
Anfragearten
§ Finde Tupel anhand von Primärschlüssel
(z.B. Student mit der Matrikelnummer 196678)
§ Finde Tupel anhand eines Attributwerts
(z.B. Studenten mit Name Müller)
§ Finde Tupel anhand mehrerer Attributwerte
(z.B. Studenten mit Name Müller und Vorname Thomas)
Datenbanken / Kapitel 8: Datenbanktuning
5
Anfragearten
§ Finde Tupel anhand eines Attributwertebereichs
(z.B. Studenten zwischen vierten und sechsten Semester)
§ Finde Tupel anhand mehrerer Attributwertebereiche
(z.B. Studenten mit Name M* und Vorname T*)
Datenbanken / Kapitel 8: Datenbanktuning
6
Indizes in SQL
§ Anlegen von Indizes mittels CREATE INDEX Kommando
1
2
CREATE INDEX < Name des Index>
ON < Name der Tabelle > ( < Attribute >)
§ Beispiele:
1
2
CREATE INDEX StudentenSemesterName
ON Studenten ( Semester ASC , Name ASC )
1
2
1
2
CREATE INDEX VorlesungenSWSTitel
ON Vorlesungen ( SWS DESC , Titel )
CREATE INDEX Vo rl es un gen Ti te lV orl Nr
ON Vorlesungen ( Titel , VorlNr )
Datenbanken / Kapitel 8: Datenbanktuning
7
Indexstrukturen
§ RDBMSs unterstützen meist verschiedene
Indexstrukturen zur Umsetzung von Indizes
§ Suchbäume
§ Hashbasierte Indexstrukturen
§ Bitmaps für Attribute mit wenigen möglichen Werten
§ Indexstrukturen für mehrdimensionale Daten (z.B. R-Bäume)
§ Indexstrukturen für Textdaten (z.B. invertierte Datei)
Datenbanken / Kapitel 8: Datenbanktuning
8
Rückblick: Binäre Suchbäume
§ Binäre Suchbäume zur effizienten Suche im Hauptspeicher
6
8
4
2
5
7
§ Balancierte binäre Suchbäume
(z.B. AVL-Bäume und Rot-Schwarz-Bäume)
§ Jeder Knoten hat bis zu zwei Kinder
§ Suchen, Einfügen, Löschen in O(log2 n)
§ Wieso nicht unverändert geeignet als Indexstrukturen?
Datenbanken / Kapitel 8: Datenbanktuning
9
B-Bäume
§ B-Baum ist ein balancierter Suchbaum, bei dem sich die
Gestalt der Knoten an der Seitengröße orientiert
§ Knoten muss in eine Seite passen
§ Knoten bestehen aus
§ mehreren Werten als Schlüssel
§ mehreren Verweisen auf Kindknoten
§ mehreren Tupelnummern (oder gar den Tupeln selbst)
12
(2,1)
Datenbanken / Kapitel 8: Datenbanktuning
20
(7,2)
34
(9,3)
42
(2,3)
10
B-Bäume
§ B-Bäume haben damit typischerweise einen höheren
Verzweigungsgrad (fan out) als binäre Suchbäume
§ Balancierung eines B-Baums wird mittels seiner
Ordnung m und den Füllgraden der Knoten erreicht
§ jeder Knoten außer der Wurzel
enthält mindestens m Einträge
§ jeder Knoten enthält höchstens 2*m Einträge
§ jeder Knoten ist entweder Blattknoten ohne Kinder
oder hat ein Kind mehr als Einträge
§ alle Blattknoten liegen auf der gleichen Stufe
Datenbanken / Kapitel 8: Datenbanktuning
11
B-Bäume
§ Beispiel: B-Baum der Ordnung m=1
12
2
4
13
Datenbanken / Kapitel 8: Datenbanktuning
20
15
50
25
35
27
31
65
37
55
77
79
12
Suche in B-Bäumen
§ Suche in B-Baum
§ Beginne beim Wurzelknoten
§ Falls der gesuchte Wert einem Schlüssel entspricht,
gibt die zugehörige Tupelnummer zurück
§ Verfolge ansonsten Verweis zu Kindknoten, der
§ links vom kleinsten Schlüssel größer als gesuchter Wert steht
§ rechts vom größten Schlüssel kleiner als gesuchter Wert steht
§ Fahre mit Kindknoten analog fort
§ Höhe des B-Baums ist in O(logm n)
§ Suche auf B-Baum benötigt O(logm n) Zugriffe
Datenbanken / Kapitel 8: Datenbanktuning
13
Suche in B-Bäumen
§ Beispiel: Suche nach dem Wert 22 im B-Baum
12
2
4
20
13
15
50
25
35
27
31
65
37
55
77
79
Wert 22 nicht enthalten
Datenbanken / Kapitel 8: Datenbanktuning
14
Einfügen in B-Bäumen
§ Einfügen in B-Baum
§ Suche Blattknoten, in dem der einzufügende Wert stünde
§ Enthält der Blattknoten wenigstens m und weniger als 2*m
Einträge, dann füge den Wert im Blattknoten ein
§ Enthält der Blattknoten bereits genau 2*m Einträge,
dann zerteile diesen in zwei Blattknoten mit je m Einträgen
und reiche mittleres Element an Elternknoten hoch;
dort wird analog fortgefahren
§ Einfügen in B-Baum benötigt O(logm n) Zugriffe
Datenbanken / Kapitel 8: Datenbanktuning
15
Einfügen in B-Bäumen
§ Beispiel: Einfügen des Werts 7
12
2
4
13
Datenbanken / Kapitel 8: Datenbanktuning
20
15
50
25
35
27
31
65
37
55
77
79
16
Einfügen in B-Bäumen
§ Beispiel: Einfügen des Werts 7
12
2
4
13
Datenbanken / Kapitel 8: Datenbanktuning
20
15
50
25
35
27
31
65
37
55
77
79
17
Einfügen in B-Bäumen
§ Beispiel: Einfügen des Werts 7
12
2
4
7
13
20
15
50
25
35
27
31
65
37
55
77
79
Knoten voll
Datenbanken / Kapitel 8: Datenbanktuning
18
Einfügen in B-Bäumen
§ Beispiel: Einfügen des Werts 7
4
12
2
13
20
15
50
25
35
27
31
65
37
55
77
79
7
Datenbanken / Kapitel 8: Datenbanktuning
19
Löschen in B-Bäumen
§ Löschen im B-Baum
§ Suche Knoten, in dem der gesuchte Wert steht
§ Handelt es sich dabei um einen Blattknoten, so entferne
den Eintrag und behandle evtl. Unterlauf des Knotens, falls
Knoten danach weniger als m Einträge enthält
§ Handelt es sich um einen inneren Knoten, so ersetze den
Eintrag durch den Eintrag von einem Blattknoten mit
nächstkleineren Schlüssel und behandle evtl. Unterlauf
Datenbanken / Kapitel 8: Datenbanktuning
20
Löschen in B-Bäumen
§ Behandlung eines Unterlaufs (weniger als m Einträge)
durch Ausgleichen oder Verschmelzen mit Nachbarknoten
§ Hat der Nachbarknoten mehr als m Einträge, werden diese
und die Einträge des untergelaufenen Knotens
auf beide Knoten verteilt
§ Hat der Nachbarknoten genau m Einträge, so werden
die beiden Knoten verschmolzen und der mittlere
Eintrag, wie beim Einfügen, zum Elternknoten weitergereicht
§ Löschen im B-Baum benötigt O(logm n) Zugriffe
Datenbanken / Kapitel 8: Datenbanktuning
21
Löschen in B-Bäumen
§ Beispiel: Löschen des Werts 25
12
2
4
13
Datenbanken / Kapitel 8: Datenbanktuning
20
15
50
25
35
27
31
65
37
55
77
79
22
Löschen in B-Bäumen
§ Beispiel: Löschen des Werts 25
12
2
4
20
13
15
50
25
35
27
31
65
37
55
77
79
25 in innerem Knoten,
ersetze durch 20 aus Blattknoten
Datenbanken / Kapitel 8: Datenbanktuning
23
Löschen in B-Bäumen
§ Beispiel: Löschen des Werts 25
12
2
4
15
50
20
35
27
13
31
65
37
55
77
79
Unterlauf des Blattknotens
Datenbanken / Kapitel 8: Datenbanktuning
24
Löschen in B-Bäumen
§ Beispiel: Löschen des Werts 25
12
2
4
13
20
15
50
27
35
31
65
37
55
77
79
Ausgleichen mit Nachbarknoten
Datenbanken / Kapitel 8: Datenbanktuning
25
B-Bäume
§ Im B-Baum enthalten sowohl innere Knoten als auch
Blattknoten Tupelnummer (oder gar ganze Tupel)
§ B-Bäume unterstützen Suche nach einem bestimmten
Wert effizient; die Suche nach allen Einträgen mit einem
Schlüssel in einem bestimmten Wertebereich wird
jedoch nicht effizient unterstützt, da eine Traversierung
des entsprechenden Unterbaums notwendig wäre
Datenbanken / Kapitel 8: Datenbanktuning
26
B+-Bäume
§ B+-Bäume sind eine verbesserte Variante der B-Bäume,
die in der Praxis sehr häufig eingesetzt werden
§ B+-Bäume sind hohle Bäume, d.h. Tupelnummern (oder
Tupel selbst) befinden sich nur in den Blattknoten; die
inneren Knoten enthalten nur Schlüssel und Verweise, sie
dienen somit nur der Navigation
§ B+-Bäume verketten die Blattknoten doppelt,
wodurch Bereichsanfragen effizient
ermöglicht werden
Datenbanken / Kapitel 8: Datenbanktuning
27
B+-Bäume
§ Beispiel: B+-Baum der Ordnung m=1
55
31
1
15
5
12
2
4
12
13
Datenbanken / Kapitel 8: Datenbanktuning
55
31
15
20
25
27
31
35
37
50
55
65
28
Bereichsanfragen in B+-Bäumen
§ Bereichsanfrage in B+-Bäumen
§ Suche nach linker Grenze des Wertebereichs
§ Durchlaufe Blattknoten bis ein Schlüssel mit
größerem Wert als rechte Grenze
des Wertebereichs erreicht
§ Bereichsanfrage benötigt O(logm n + r/m) Zugriffe,
mit r als Anzahl der zurückgelieferten Einträge
Datenbanken / Kapitel 8: Datenbanktuning
29
Bereichsanfragen in B+-Bäumen
§ Beispiel: Suche Einträge mit Schlüssen im Bereich [13,31]
55
31
1
15
5
12
2
4
12
13
Datenbanken / Kapitel 8: Datenbanktuning
55
31
15
20
25
27
31
35
37
50
55
65
30
Rückblick: Hashverfahren
§ Hashtabellen zur effizienten Suche im Hauptspeicher
§ Werte werden mittels Hashfunktion
auf Buckets eines Arrays abgebildet
0
31
1
§ Typische Hashfunktion:
h(k) = (k mod p) mod n
mit Primzahl p und Kapazität n
§ Kollisionen können z.B.
durch Verkettung behandelt werden
§ Suche, Einfügen, Löschen in O(1) (erwartet)
Datenbanken / Kapitel 8: Datenbanktuning
2
3
10
24
4
5
6
13
p = 31 n = 7
31
Hashbasierte Indizes
§ Hashbasierte Indizes in Datenbanksystemen
§ Werte des Primärschlüssels oder anderer Attribute
werden mittels einer Hashfunktion
auf Buckets abgebildet
§ Jedes Bucket entspricht initial einer Seite,
in der die Tupel oder Tupelnummern
gespeichert werden
§ Kollisionen können wie im Hauptspeicher
z.B. durch Verkettung von Seiten
behandelt werden
Datenbanken / Kapitel 8: Datenbanktuning
32
Hashbasierte Indizes
§ Hashbasierte Indizes unterstützen Suche, Einfügen und
Löschen effizient, sofern vorab bekannt ist, wie viele
Datensätze indiziert werden und die Zahl der Buckets
entsprechend gewählt werden kann
§ Verwendet man zu viele Buckets (Seiten) wird
übermäßig viel Speicher verwendet; bei zu wenigen
häufen sich die Kollisionen und die Effizienz nimmt ab
§ Hashbasierte Indizes unterstützen nur die Suche nach
einem bestimmten Wert, jedoch keine Bereichsanfragen
Datenbanken / Kapitel 8: Datenbanktuning
33
Indizes in RDBMSs
§ RDBMSs (z.B. PostgreSQL und Oracle) implementieren
verschiedene Arten von Indizes und erlauben dem
Benutzer, die Art des Index sowie zusätzliche
Parameter (z.B. den Füllgrad) anzugeben
Beispiel: Hashbasierter Index in PostgreSQL
1
2
CREATE INDEX S t ud e n t e n_ M a t r Nr
ON Studenten USING hash ( MatrNr )
§ Beispiel: Index (B+-Baum) über mehrere Attribute
1
2
CREATE INDEX Studenten_Name_Vorname
ON Studenten ( Name , Vorname )
Datenbanken / Kapitel 8: Datenbanktuning
34
Trick #1: Sortieren mit Indizes
§ Indizes können verwendet werden, um Daten vorsortiert
zu speichern und dem RDBMS ein Sortieren zu sparen
§ Beispiel:
1
2
3
SELECT *
FROM Studenten
ORDER BY Name , Vorname
lässt sich durch einen Index folgender Art unterstützen
1
2
CREATE INDEX S t u d e n t e n N a m e V o r n a m e
ON Studenten ( Name ASC , Vorname ASC )
Datenbanken / Kapitel 8: Datenbanktuning
35
Trick #2: Vorberechnen mit Indizes
§ Einige RDBMSs erlauben Funktionsausdrücke in Indizes,
so dass diese zum Vorberechnen genutzt werden können
§ Beispiel:
1
2
3
SELECT *
FROM Studenten
WHERE lower ( Name ) = ’m ü ller ’ AND lower ( Vorname ) LIKE ’ th % ’
lässt sich durch einen Index folgender Art unterstützen
1
2
CREATE INDEX S t u d e n t e n L o w e r N a m e L o w e r V o r n a m e
ON Studenten ( lower ( Name ) ASC , lower ( Vorname ) ASC )
Datenbanken / Kapitel 8: Datenbanktuning
36
Trick #3: Anfragen nur mit Indizes
§ Sofern alle benötigten Attribute gespeichert sind, können
einige RDBMSs Anfragen vollständig Index auswerten
§ Beispiel:
1
2
3
4
SELECT
FROM
WHERE
ORDER BY
MatrNr , Name , Vorname
Studenten
Semester = 3
Name , Vorname
lässt sich durch einen Index folgender Art unterstützen
1
2
CREATE INDEX S t u d e n t e n S e m e s t e r M a t r N r N a m e V o r n a m e
ON Studenten ( Semester , Name , Vorname , MatrNr )
Datenbanken / Kapitel 8: Datenbanktuning
37
8.2 Statistiken
§ SQL ist deklarativ, d.h. RDBMS muss selbst einen
geeigneten Auswertungsplan zu einer gegebenen
Anfrage bestimmen
§ RDBMS muss Kosten verschiedener Auswertungspläne
abschätzen und miteinander vergleichen können
§ Statistiken über die gespeicherten Daten sind essentiell,
um Kosten realistisch schätzen zu können und
günstigen Auswertungsplan auszuwählen
Datenbanken / Kapitel 8: Datenbanktuning
38
Histogramme
Table 1
§ Histogramme als vorherrschendes Verfahren in RDBMSs
unterteilen Wertebereich eines Attributs in β Buckets
Semester
Anzahl
1
2
3
4
5
6
1.000
800
700
600
500
450
[1,3]
2500
[4,6]
1550
7[7,9]
400
[10,12]
8
9
390
380
[13,15]
10
11
12
13
14
15
280
250
150
75
20
5
1170
680
100
§ Equiwidth-Histogramme
§ Buckets haben gleiche Breite
3,000
2,250
—=5
1,500
750
0
Datenbanken / Kapitel 8: Datenbanktuning
[1,3]
[4,6]
[7,9]
[10,12]
[13,15]
39
Statistiken in SQL
§ RDBMSs benötigen Statistiken, um effiziente
Auswertungspläne auswählen zu können
§ Syntax zum Berechnen von Statistiken systemabhängig
§ MS SQL Server
1
2
UPDATE STATISTICS < Tabelle >
WITH SAMPLE 10 PERCENT
berechnet Statistiken auf Tabelle mittels 10% Stichprobe
§ PostgreSQL
1
ANALYZE < Tabelle >
berechnet Statistiken auf Tabelle
Datenbanken / Kapitel 8: Datenbanktuning
40
8.3 Systemparameter
§ RDBMSs bieten systemspezifische Parameter, um
Einfluss auf Verhalten des Systems zu nehmen, z.B.
§ Maximale Größe des verwendeten Hauptspeichers
§ Maximale Anzahl paralleler Threads
§ Größe verschiedener Puffer
§ Verfügbare Operatoren
§ etc.
§ Tuning der Systemparameter (z.B. Hauptspeicher) ist
essentiell, aber leider hochgradig systemabhängig
und wird von daher hier nicht weiter vertieft
Datenbanken / Kapitel 8: Datenbanktuning
41
Zusammenfassung
§ Indizes erlauben die Beschleunigung von Anfragen,
die aus einer Anwendung heraus gestellt werden
§ B+-Bäume und Hashing als gängigste Indexstrukturen
§ Indizes können nicht nur das Finden von Tupeln, sondern
auch Sortieren, Funktionsberechnung oder
ganze Anfragen beschleunigen
§ Statistiken sind essentiell, damit RDBMS zu gegebener
Anfrage effizienten Auswertungsplan bestimmen kann
Datenbanken / Kapitel 8: Datenbanktuning
42
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (Kapitel 7)
[2]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 7)
Datenbanken / Kapitel 8: Datenbanktuning
43