Viele Mechanismen zur Optimierung im SQL Server sind verbunden mit Datenträgerlasten bzw. Datenträgerantastung.
Die Entlastung des Datenträgers basiert auf effiziente Speichern und Komprimieren der Daten. Somit sinkt der Platzbedarf, und die Performance von Datenbank- Applikationen steigt.
Je weniger Daten in Form von Bytes benötigt werden, umso geringer sind die Anforderungen an die Datenbank-Server. Die Performance des Systems wird verbessert, Zugriffszeiten verringert und die IO-Last allgemein minimiert.
|
Die Zugriffseinheit einer Datei sind Seiten oder auch Blöcke. Ein Block besteht aus mehreren Sätzen (Records). Typischerweise hat eine Seite eine Größe von 4KB bis 8KB bzw. besteht ein Block aus aufeinanderfolgenden Seiten mit einer Größe bis zu 32 KB.
Im SQL Server sind Seiten immer 8192 Bytes, und ein Block besteht aus 8 Seiten, also 64KB.
Eine Seite kann dabei einen oder viele Datensätze enthalten.
|
Ich stelle euch in diesem Artikel verbreitete Techniken vor, um Daten in SQL-Datenbanken effizient zu speichern. Den Schwerpunkt bildet dabei die Kompression. (Aber davor bisschen was zu Redundanz und Normalisierung)
Redundanz bedeutet in der Datenbanklogik, dass Informationen mehrfach gespeichert werden. Folglich müssten sie auch an mehreren Stellen gepflegt werden. Redundanzen sind also doppelte Informationen in einer Datenbank bzw. Datenbank-Tabelle. Eine Datenbank ist dann redundanzfrei, wenn alle doppelten Informationen entfernt werden können, ohne dass ein Informationsverlust stattfindet.
Redundanzen können mittels der Normalisierung entfernt werden. (aus der Vorlesung Datenbanken im 3.Semester habe ich folgendes gelernt: hier ein paar Bsp.-Tabellen:
Quelle: Lehrbuch: R. Elmasri, S. B. Navathe: Grundlagen von Datenbanksystemen)
Man könnte fast folgendes sagen: Normalisierung macht ohne Primärschlüssel-Fremdschlüsselbeziehung keinen Spaß. Die sog. Referentielle Integrität sorgt dafür dass kein Primärschlüsselwert gelöscht werden kann, solange Fremdschlüsselwerte vorliegen oder nur dann ein Fremdschlüsselwert eingefügt werden kann, wenn ein entsprechender Primary Key (PK) vorliegt. Oder anders gesagt: wir wollen immer wissen, welcher „KD2345“ schuldet uns eigentlich die 100000 Euro?
Redundanzen und Anomalien:
Insert-Anomalie: Neue Abteilungen sind nicht ohne Mitarbeiter einfügbar, da der Primärschlüssel manr einen Wert erhalten muss.
Delete-Anomalie: Wenn der letzte Mitarbeiter einer Abteilung gelöscht wird, verschwinden alle Informationen über diese Abteilung.
Update-Anomalie: Die Änderung einer Abteilungsinformation muss im Tupel jedes Mitarbeiters dieser Abteilung erfolgen.
Datenbankanforderung – Redundanzkontrolle: Informationen sollen in einer Datenbank möglichst nur einmal gespeichert sein. Gründe dafür sind zum einen die Speicherplatzeffizienz und auch die einfache Änderbarkeit von Daten, sowie die Datenkonsistenz (Datenspeicherung).
Entgegen der Regel, verwendet man gerne mal Redundanz. Nehmen wir mal das Beispiel „Rechnungssumme“. Laut Normalisierung gehört das nicht ins Datenbank-Design rein, aber Abfragen sparen sich sehr viel, wenn der Wert vorliegt.
Warum?
Nehmen wir mal an: 1 Mio. Kunden, die ca. 2 Mio. Bestellungen getätigt haben. Pro Bestellungen werden 2 Produkte bestellt. Dann würde man laut Normalisierung 1 Mio. Kunden mit 2 Mio. Bestelldaten und diese wiederum mit 4 Mio. Bestelldetails joinen müssen, sofern man die Umsätze pro Kunde wissen möchte. Würde man entgegen der Normalisierung die Rechnungssumme in Bestellungen mitführen, könnte man auf 4 Mio. Datenzeilen in Abfragen verzichten. Anders gesagt: Statt 7 Mio. Datenzeilen nur noch 3 Mio.
Anders als gemäß der Theorie, verfolgt man in der Praxis auch die Betrachtung der Physik. Hier unterliegt die Normalisierung den Performanceregeln. Redundanz bedeutet durchaus Geschwindigkeit. Gerade Datawarehouse-Szenarien profitieren von einer solchen Betrachtungsweise. Nehmen wir mal folgendes Beispiel an – Rechnungssumme –
Kompression:
Heutzutage wachsen die Datenmengen schnell an. Somit steigen die Anforderungen an den Datendurchsatz und die Skalierbarkeit der darunter liegenden Datenbanken. Um große Datenmengen in den Griff zu bekommen, gilt es zunächst, einige Techniken zu verwenden. Ein Beispiel ist die Datenkompression. Datenkompression und das Minimieren von Datenredundanzen verringern den Storage-Bedarf stark.
Datenkomprimierung kommt in zwei verschiedenen Formen vor:
· Row-Ebene-Datenkomprimierung: Bei der Zeilenebene Datenkomprimierung geht es darum, dass der Datentyp mit fester Länge in ein Datentyp mit variabler Länge umgewandelt wird, der den leeren Speicherplatz freigibt. Es hat auch die Möglichkeit, NULL-Werte zusätzliche platzsparend zu ignorieren. Wiederum können mehr Zeilen auf eine einzelne Datenzugriffsseite passen.
· Page-Ebene-Datenkomprimierung: Datenkompression auf Seitenebene beginnt mit Zeilenebene Datenkomprimierung und fügt zusätzlich zwei Komprimierungsfunktionen, nämlich den Präfix- und Dictionary -Komprimierung.
Durch Komprimierung verbessert sich häufig die Datenbankleistung. Der Grund: Es müssen weniger Daten von der Platte gelesen werden. Da Datenseiten, so wie sie sind, in den Arbeitsspeicher gelangen, können mehr Datensätze im Hauptspeicher abgelegt werden. Abfragen und andere Aktivitäten lassen sich deutlich schneller ausführen. Allerdings ist das Ganze nicht gratis: Die Komprimierung kostet CPU-Ressourcen, denn irgendjemand muss ja die Daten dekomprimieren, sobald sie an den Client geleitet werden, und es kostet die Enterprise-Lizenz des SQL Servers.
Die Kompression ist umso höher, je häufiger ein Wert oder ein Muster auftritt. Folglich könnte man den Rückschluss ziehen, dass gerade bei gezielter Redundanz im Datenbank Design sich einerseits sehr gute Kompressionsraten einstellen würden, und andererseits Abfragen auf teure Joins verzichten könnten.
Verwenden von Spalten mit geringer Dichte – Sparse Columns in SQL Server:
Der Trend geht deutlich zu breiten Tabellen hin (siehe vs. Normalisierung). Spalten mit geringer Dichte sind gewöhnliche Spalten, die einen optimierten Speicher für NULL-Werte haben (im Prinzip wird ein NULL Wert gar nicht mehr gespeichert). Sie reduzieren die Speicherplatzanforderungen von NULL-Werten auf Kosten eines erhöhten Aufwands, um Werte ungleich NULL abzurufen. So zum Beispiel würde eine Spalte mit Datentyp decimal 42% NULL enthalten müssen, damit sich der Einsatz von Sparse Columns lohnt. Die Kosten des NULL Wertes sind abhängig vom Datentyp. Im Falle einer bit-Spalte würden es mehr als 98% NULL bedürfen, um den Einsatz von Sparse Columns vertreten zu können. Spalten mit geringer Dichte und Spaltensätze werden mit der CREATE TABLE-Anweisung oder der ALTER TABLE-Anweisung definiert.
Diese können auch mit Spaltensätzen und gefilterten Indizes verwendet werden:
Spaltensätze:Die Anweisungen INSERT, UPDATE und DELETE können anhand des Namens auf die Spalten mit geringer Dichte verweisen. Sie können jedoch auch alle Spalten mit geringer Dichte in einer Tabelle anzeigen und mit ihnen arbeiten, wenn sie zu einer einzelnen XML-Spalte zusammengeschlossen werden. Diese Spalte wird als Spaltensatz bezeichnet.
Gefilterte Indizes:Da Spalten mit geringer Dichte viele Zeilen mit NULL-Werten haben, sind sie besonders für gefilterte Indizes geeignet. Ein gefilterter Index für eine Spalte mit geringer Dichte kann nur die Zeilen indizieren, die Werte enthalten. Dadurch wird ein kleinerer und effizienterer Index erstellt.
Mithilfe von Spalten mit geringer Dichte und von gefilterten Indizes können Anwendungen wie Windows SharePoint Services große Mengen an benutzerdefinierten Eigenschaften mit SQL Server 2012 speichern und darauf zugreifen.
Vorteile einer Spalte mit geringer Dichte:
· INSERT-, UPDATE- und DELETE-Anweisungen können die Spalten mit geringer Dichte nach Namen verweisen.
· kann gefilterte Indizes nutzen, natürlich explizit nur Zeilen mit NOT NULL Werten
· spart viel Speicherplatz, wenn NULL oder null-Werte in der Datenbank vorhanden sind.
Nachteile einer Spalte mit geringer Dichte:
· Sparse-Spalte kann nicht auf Text, Ntext, Image, Timestamp und Geometrie Geographie angewendet werden.
· Sparse Spalte kann keinen Standardwert oder Regel oder eine berechnete Spalte haben.
· Sie kann auch nicht als Feld für einen zusammengesetzten Primärschlüssels oder auch eines gruppierten Indexschlüssels sein.
Hinweis: Ebenso gibt es eine weitere Methode – Columnstore-Indizies, die schnellere Ergebnisse lieferen können als Sparse Columne.
Siehe Link: http://technet.microsoft.com/de-de/library/gg492088.aspx
Fazit: Je weniger Daten, umso effizienter arbeitet das Datenbank-System in der Regel.
Daher sollten die Fähigkeiten von Datenbanksystemen genutzt werden, um große Datenmengen durch eine möglichst effiziente Speicherung zu minimieren.