Denormalisierung von Tabellen in der Datenbank

8.1 Warum ist eine Denormalisierung notwendig?

Der rechenintensivste Vorgang zwischen großen Tabellen ist der Join. Wenn dementsprechend in einer Abfrage mehrere Tabellen, die aus vielen Millionen Zeilen bestehen, „belüftet“ werden müssen, wird das DBMS viel Zeit für eine solche Verarbeitung aufwenden.

Der Benutzer kann zu diesem Zeitpunkt weggehen, um Kaffee zu trinken. Die Interaktivität der Verarbeitung verschwindet praktisch und nähert sich der der Stapelverarbeitung. Schlimmer noch: Im Batch-Modus erhält der Benutzer morgens alle am Vortag angeforderten Daten und arbeitet in aller Ruhe damit, um neue Anfragen für den Abend vorzubereiten.

Um die Situation schwerer Verknüpfungen zu vermeiden, werden Tabellen denormalisiert. Aber jedenfalls nicht. Es gibt einige Regeln, die es Ihnen ermöglichen, transaktionsdenormalisierte Tabellen gemäß den Regeln zum Erstellen von Tabellen für Data Warehouses als „normalisiert“ zu betrachten.

Es gibt zwei Hauptschemata, die in der analytischen Verarbeitung als „normal“ gelten: „Schneeflocke“ und „Stern“. Die Namen spiegeln das Wesentliche gut wider und ergeben sich direkt aus dem Bild der zugehörigen Tabellen.

In beiden Fällen sind die sogenannten Faktentabellen das zentrale Element des Schemas und enthalten die Ereignisse, Transaktionen, Dokumente und andere interessante Dinge, die für den Analysten von Interesse sind. Wenn jedoch in einer Transaktionsdatenbank ein Dokument über mehrere Tabellen (mindestens zwei: Kopfzeilen und Inhaltszeilen) „verschmiert“ ist, dann entspricht in der Faktentabelle ein Dokument, genauer gesagt, jede seiner Zeilen oder ein Satz gruppierter Zeilen zu einem Datensatz.

Dies kann durch Denormalisierung der beiden obigen Tabellen erfolgen.

8.2 Denormalisierungsbeispiel

Jetzt können Sie beurteilen, wie viel einfacher es für das DBMS sein wird, eine Abfrage beispielsweise des folgenden Typs auszuführen: um das Volumen der Mehlverkäufe an die Kunden von Pirozhki LLC und Vatrushki CJSC für den Zeitraum zu ermitteln.

In einer normalisierten Transaktionsdatenbank:

SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name
FROM
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc
   INNER JOIN customers c ON d.id customer = c.id customer
   INNER JOIN products p ON dl.id product = p.id product
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

In der analytischen Datenbank:

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Anstelle eines schweren Joins zwischen zwei Dokumententabellen und deren Zusammensetzung mit Millionen von Zeilen erhält das DBMS direkte Arbeit mit der Faktentabelle und leichten Joins mit kleinen Hilfstabellen, auf die Sie bei Kenntnis der Bezeichner auch verzichten können.

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Kehren wir zu den Schemata „Stern“ und „Schneeflocke“ zurück. Hinter den Kulissen des ersten Bildes befanden sich Tische mit Kunden, ihren Gruppen, Geschäften, Verkäufern und tatsächlich Waren. Bei der Denormalisierung werden diese Tabellen, Dimensionen genannt, auch mit der Faktentabelle verknüpft. Wenn sich die Faktentabelle auf Dimensionstabellen bezieht, die Links zu anderen Dimensionen (Dimensionen der zweiten Ebene und höher) haben, wird ein solches Schema als „Schneeflocke“ bezeichnet.

Wie Sie sehen, müssen Sie für Abfragen, die eine Filterung nach Kundengruppen beinhalten, eine zusätzliche Verbindung herstellen.

SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

In diesem Fall kann die Denormalisierung fortgesetzt und die Dimension der zweiten Ebene auf die erste zurückgeführt werden, wodurch die Abfrage der Faktentabelle einfacher wird.

Ein Schema, bei dem sich eine Faktentabelle nur auf Dimensionen bezieht, die keine zweite Ebene haben, wird als Sternschema bezeichnet. Die Anzahl der Messtabellen entspricht der Anzahl der „Strahlen“ im Stern.

Das Star-Schema eliminiert vollständig die Hierarchie der Dimensionen und die Notwendigkeit, die entsprechenden Tabellen in einer einzigen Abfrage zusammenzuführen.

SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

Der Nachteil der Denormalisierung ist immer die Redundanz , die sowohl bei transaktionalen als auch bei analytischen Anwendungen zu einer Vergrößerung der Datenbank führt. Berechnen wir ein ungefähres Delta für das obige Beispiel der Konvertierung von „Schneeflocke“ in „Stern“.

In einigen DBMS, beispielsweise Oracle, gibt es auf der Ebene der Datenbankschemadefinitionen keine speziellen Ganzzahltypen. Sie müssen den generischen booleschen Typ verwenden numeric(N), wobei N die Anzahl der gespeicherten Bits ist. Die Speichergröße einer solchen Zahl wird anhand einer speziellen Formel berechnet, die in der Dokumentation zur physischen Datenspeicherung angegeben ist, und übersteigt in der Regel die von Low-Level-Typen wie „16-Bit-Integer“ um 1-3 Bytes.

Angenommen, die Verkaufstabelle verwendet keine Datenkomprimierung und enthält etwa 500 Millionen Zeilen und die Anzahl der Kundengruppen beträgt etwa 1000. In diesem Fall können wir eine kurze Ganzzahl (shortint, smallint) mit 2 Bytes als Bezeichnertyp verwenden id_customer_group.

Wir gehen davon aus, dass unser DBMS einen Zwei-Byte-Integer-Typ unterstützt (z. B. PostgreSQL, SQL Server, Sybase und andere). Wenn Sie dann die entsprechende Spalte id_customer_groupzur Verkaufstabelle hinzufügen, erhöht sich deren Größe um mindestens 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Wann ist eine Denormalisierung erforderlich?

Schauen wir uns einige häufige Situationen an, in denen eine Denormalisierung nützlich sein kann.

Große Anzahl von Tabellenverknüpfungen

Bei Abfragen an eine vollständig normalisierte Datenbank müssen Sie häufig bis zu einem Dutzend oder sogar mehr Tabellen verknüpfen. Und jede Verbindung ist ein sehr ressourcenintensiver Vorgang. Infolgedessen verbrauchen solche Anfragen Serverressourcen und werden langsam ausgeführt.

In einer solchen Situation kann es helfen:

• Denormalisierung durch Reduzierung der Tabellenanzahl. Es ist besser, mehrere kleine Tabellen zu einer einzigen zusammenzufassen, die selten geänderte (wie man oft sagt, bedingt konstante oder Referenz-) Informationen und Informationen enthalten, die in ihrer Bedeutung eng miteinander verbunden sind.
• Wenn Sie in einer großen Anzahl von Abfragen mehr als fünf oder sechs Tabellen verknüpfen müssen, sollten Sie im Allgemeinen eine Denormalisierung der Datenbank in Betracht ziehen.
• Denormalisierung durch Hinzufügen eines zusätzlichen Felds zu einer der Tabellen. In diesem Fall tritt Datenredundanz auf und es sind zusätzliche Maßnahmen erforderlich, um die Integrität der Datenbank aufrechtzuerhalten.

Geschätzte Werte

Abfragen sind oft langsam und verbrauchen viele Ressourcen, wobei einige komplexe Berechnungen durchgeführt werden, insbesondere bei der Verwendung von Gruppierungen und Aggregatfunktionen (Summe, Max usw.). Manchmal ist es sinnvoll, der Tabelle 1-2 zusätzliche Spalten hinzuzufügen, die häufig verwendete (und schwer zu berechnende) berechnete Daten enthalten.

Angenommen, Sie möchten die Gesamtkosten jeder Bestellung ermitteln. Dazu müssen Sie zunächst die Kosten für jedes Produkt ermitteln (gemäß der Formel „Anzahl der Produkteinheiten“ * „Stückpreis des Produkts“ – Rabatt). Danach müssen Sie die Kosten nach Bestellungen gruppieren.

Die Ausführung dieser Abfrage ist recht komplex und kann, wenn in der Datenbank Informationen über eine große Anzahl von Bestellungen gespeichert sind, lange dauern. Anstatt eine solche Abfrage auszuführen, können Sie deren Kosten bereits bei der Bestellung ermitteln und in einer separaten Spalte der Bestelltabelle speichern. Um das gewünschte Ergebnis zu erhalten, reicht es in diesem Fall aus, die vorberechneten Werte aus dieser Spalte zu extrahieren.

Das Erstellen einer Spalte, die vorberechnete Werte enthält, spart beim Ausführen einer Abfrage viel Zeit, erfordert jedoch, dass Sie die Daten in dieser Spalte rechtzeitig aktualisieren.

lange Krempe

Wenn wir große Tabellen in der Datenbank haben, die lange Felder (Blob, Long usw.) enthalten, können wir die Ausführung von Abfragen an eine solche Tabelle erheblich beschleunigen, wenn wir die langen Felder in eine separate Tabelle verschieben. Wir möchten beispielsweise einen Katalog von Fotos in der Datenbank erstellen und die Fotos selbst in Blob-Feldern speichern (professionelle Qualität, hohe Auflösung und angemessene Größe). Aus Sicht der Normalisierung wäre folgender Tabellenaufbau absolut korrekt:

• Lichtbildausweis
• Autoren-ID
• Kameramodell-ID
• das Foto selbst (Blobfeld)

Stellen wir uns nun vor, wie lange die Abfrage dauern wird, wenn wir die Anzahl der von einem beliebigen Autor aufgenommenen Fotos zählen ...

Die richtige Lösung (wenn auch gegen die Prinzipien der Normalisierung) wäre in einer solchen Situation die Erstellung einer weiteren Tabelle, die nur aus zwei Feldern besteht – dem Lichtbildausweis und einem Blob-Feld mit dem Foto selbst. Dann werden Auswahlen aus der Haupttabelle (in der es kein riesiges Blob-Feld mehr gibt) sofort übernommen, aber wenn wir das Foto selbst sehen wollen, warten wir mal ...

Wie kann festgestellt werden, wann eine Denormalisierung gerechtfertigt ist?

8.4 Vor- und Nachteile der Denormalisierung

Eine Möglichkeit festzustellen, ob bestimmte Schritte gerechtfertigt sind, besteht darin, eine Analyse hinsichtlich der Kosten und des möglichen Nutzens durchzuführen. Wie viel kostet ein denormalisiertes Datenmodell?

Bestimmen Sie die Anforderungen (was wir erreichen wollen) → Bestimmen Sie die Datenanforderungen (was wir befolgen müssen) → Finden Sie den Mindestschritt, der diese Anforderungen erfüllt → Berechnen Sie die Implementierungskosten → Implementieren.

Zu den Kosten zählen physische Aspekte wie Speicherplatz, die zur Verwaltung dieser Struktur erforderlichen Ressourcen und verpasste Chancen aufgrund der mit der Aufrechterhaltung dieses Prozesses verbundenen Zeitverzögerungen. Sie müssen für die Denormalisierung bezahlen. Eine denormalisierte Datenbank erhöht die Datenredundanz, was die Leistung verbessern kann, aber mehr Aufwand für die Kontrolle der zugehörigen Daten erfordert. Der Prozess der Antragserstellung wird schwieriger, da die Daten wiederholt werden und schwerer nachzuverfolgen sind. Darüber hinaus ist die Implementierung der referenziellen Integrität nicht einfach – verwandte Daten werden in verschiedene Tabellen aufgeteilt.

Zu den Vorteilen gehören eine schnellere Abfrageleistung und die Möglichkeit, eine schnellere Antwort zu erhalten. Darüber hinaus können Sie von weiteren Vorteilen profitieren, darunter einem höheren Durchsatz, einer höheren Kundenzufriedenheit und Produktivität sowie einer effizienteren Nutzung externer Entwicklertools.

Anforderungsrate und Leistungskonsistenz

Beispielsweise handelt es sich bei 72 % der 1.000 von einem Unternehmen täglich generierten Abfragen um Abfragen auf Zusammenfassungsebene und nicht um Drilldown-Abfragen. Bei Verwendung einer Übersichtstabelle dauern Abfragen etwa 6 Sekunden statt 4 Minuten, was zu einer um 3.000 Minuten kürzeren Verarbeitungszeit führt. Selbst unter Berücksichtigung der 100 Minuten, die jede Woche für die Pflege der Pivot-Tabellen aufgewendet werden müssen, werden dadurch 2.500 Minuten pro Woche eingespart, was die Erstellung der Pivot-Tabelle rechtfertigt. Im Laufe der Zeit kann es vorkommen, dass die meisten Abfragen nicht auf zusammenfassende Daten, sondern auf detaillierte Daten gerichtet sind. Je weniger Abfragen die Übersichtstabelle verwenden, desto einfacher ist es, sie zu löschen, ohne andere Prozesse zu beeinträchtigen.

Und auch…

Bei der Entscheidung über den nächsten Optimierungsschritt sind nicht nur die oben aufgeführten Kriterien zu berücksichtigen. Weitere Faktoren müssen berücksichtigt werden, darunter Geschäftsprioritäten und Endbenutzerbedürfnisse. Benutzer müssen verstehen, wie aus technischer Sicht die Systemarchitektur durch die Anforderungen der Benutzer beeinflusst wird, die alle Anfragen in wenigen Sekunden erledigen möchten. Der einfachste Weg, dieses Verständnis zu erlangen, besteht darin, die Kosten zu erläutern, die mit der Erstellung und Verwaltung solcher Tabellen verbunden sind.

8.5 So implementieren Sie die Denormalisierung kompetent.

Speichern Sie detaillierte Tabellen

Um die für das Unternehmen wichtigen Funktionen der Datenbank nicht einzuschränken, ist es notwendig, eine Strategie der Koexistenz und nicht der Ersetzung zu verfolgen, d. h. detaillierte Tabellen für eine gründliche Analyse zu führen und ihnen denormalisierte Strukturen hinzuzufügen. Zum Beispiel der Trefferzähler. Für geschäftliche Zwecke müssen Sie die Anzahl der Besuche auf einer Webseite kennen. Aber für die Analyse (nach Zeitraum, nach Land...) benötigen wir höchstwahrscheinlich detaillierte Daten – eine Tabelle mit Informationen zu jedem Besuch.

Verwenden von Triggern

Es ist möglich, eine Datenbankstruktur zu denormalisieren und dennoch die Vorteile der Normalisierung zu nutzen, indem Datenbank-Trigger verwendet werden, um die integrityIntegrität doppelter Daten zu bewahren.

Wenn Sie beispielsweise ein berechnetes Feld hinzufügen, wird jede der Spalten, von denen das berechnete Feld abhängt, mit einem Trigger versehen, der eine einzelne gespeicherte Prozedur aufruft (das ist wichtig!), die die erforderlichen Daten in das berechnete Feld schreibt. Es ist lediglich erforderlich, keine der Spalten zu überspringen, von denen das berechnete Feld abhängt.

Software-Unterstützung

Wenn Sie keine integrierten Trigger und gespeicherten Prozeduren verwenden, sollten Anwendungsentwickler darauf achten, die Konsistenz der Daten in einer denormalisierten Datenbank sicherzustellen.

Analog zu Triggern sollte es eine Funktion geben, die alle Felder aktualisiert, die von dem zu ändernden Feld abhängen.

Schlussfolgerungen

Bei der Denormalisierung ist es wichtig, ein Gleichgewicht zu wahren zwischen der Erhöhung der Geschwindigkeit der Datenbank und der Erhöhung des Risikos inkonsistenter Daten, zwischen der Erleichterung des Schreibens für Programmierer Select-sund der Komplikation der Aufgabe derjenigen, die Datenbankbestückung und Datenaktualisierungen bereitstellen. Daher ist es notwendig, die Datenbank sehr sorgfältig und sehr selektiv zu denormalisieren, und zwar nur dort, wo es unbedingt erforderlich ist.

Wenn es nicht möglich ist, die Vor- und Nachteile einer Denormalisierung im Voraus zu berechnen, muss zunächst ein Modell mit normalisierten Tabellen implementiert und erst dann zur Optimierung problematischer Abfragen eine Denormalisierung durchgeführt werden.

Es ist wichtig, die Denormalisierung schrittweise und nur in den Fällen einzuführen, in denen wiederholt verwandte Daten aus verschiedenen Tabellen abgerufen werden. Denken Sie daran, dass beim Duplizieren von Daten die Anzahl der Datensätze zunimmt, die Anzahl der Lesevorgänge jedoch abnimmt. Es ist auch praktisch, berechnete Daten in Spalten zu speichern, um unnötige aggregierte Auswahlen zu vermeiden.