Normalisieren einer extrem großen Tabelle

8

Ich habe folgendes Problem. Ich habe einen extrem großen Tisch. Dieser Tisch ist ein Erbe der Menschen, die zuvor an dem Projekt gearbeitet haben. Die Tabelle befindet sich in MS SQL Server.

Die Tabelle hat die folgenden Eigenschaften:

  1. es hat ungefähr 300 Spalten. Alle von ihnen haben "Text" -Typ, aber einige von ihnen sollten schließlich andere Typen (z. B. Integer oder Datetime) darstellen. Also muss man diese Textwerte in entsprechende Typen konvertieren, bevor man sie benutzt
  2. Die Tabelle hat mehr als 100 Milliom Zeilen. Der Platz für den Tisch würde bald 1 Terabyte erreichen
  3. Die Tabelle hat keine Indizes
  4. Die Tabelle hat keine implementierten Partitionierungsmechanismen.

Wie Sie sich vorstellen können, ist es unmöglich, eine vernünftige Abfrage für diese Tabelle auszuführen. Jetzt fügen Leute nur neue Datensätze in die Tabelle ein, aber niemand benutzt sie. Also muss ich es neu strukturieren. Ich plane, eine neue Struktur zu erstellen und die neue Struktur mit den Daten aus der alten Tabelle aufzufüllen. Natürlich werde ich Partioning implementieren, aber es ist nicht das einzige, was zu tun ist.

Eines der wichtigsten Merkmale der Tabelle besteht darin, dass diejenigen Felder, die rein textuell sind (d. h. sie müssen nicht in einen anderen Typ konvertiert werden), häufig häufig wiederholte Werte aufweisen. Die tatsächliche Vielfalt der Werte in einer gegebenen Spalte liegt also im Bereich von 5-30 verschiedenen Werten. Dies führt zu der Idee, eine Normalisierung vorzunehmen: für jede solche textuelle Spalte erstelle ich eine zusätzliche Tabelle mit der Liste aller verschiedenen Werte, die in dieser Spalte erscheinen können, dann erstelle ich einen (tinyint) Primärschlüssel in dieser zusätzlichen Tabelle und Dann wird ein entsprechender Fremdschlüssel in der ursprünglichen Tabelle verwendet, anstatt diese Textwerte in der ursprünglichen Tabelle beizubehalten. Dann werde ich einen Index auf diese Fremdschlüsselspalte setzen. Die Anzahl der auf diese Weise zu verarbeitenden Spalten beträgt etwa 100.

Es wirft die folgenden Fragen auf:

  1. Würde diese Normalisierung wirklich die Geschwindigkeit erhöhen, mit der die Damen auf einige dieser 100 Felder Bedingungen stellen? Wenn wir die Größe vergessen, die zum Speichern dieser Spalten erforderlich ist, gibt es dann eine Leistungssteigerung aufgrund der Ersetzung der ersten Textspalten durch Tinyint-Spalten? Wenn ich keine Normalisierung vornehme und einfach einen Index für diese ersten Textspalten anlege, ist die Performance die gleiche wie für den Index der geplanten tinyint-Spalte?
  2. Wenn ich die beschriebene Normalisierung durchführe, dann muss für die Erstellung einer Ansicht mit den Textwerten die Haupttabelle mit etwa 100 zusätzlichen Tabellen verknüpft werden. Ein positiver Moment ist, dass ich diese Joins für Paare "Primärschlüssel"="Fremdschlüssel" mache. Aber trotzdem sollte eine recht große Anzahl von Tabellen zusammengefügt werden. Hier ist die Frage: ob die Leistung der an dieser Ansicht vorgenommenen Abfragen im Vergleich zur Leistung der Abfragen an die anfängliche nicht normalisierte Tabelle nicht schlechter wird? Ob der SQL Server Optimizer wirklich in der Lage ist, die Abfrage so zu optimieren, dass die Vorteile der Normalisierung genutzt werden können?

Entschuldigung für solch einen langen Text.

Danke für jeden Kommentar!

PS Ich habe eine verwandte Frage zum Beitritt zu 100 Tischen erstellt. An 100 Tabellen teilnehmen

    
iCoffee 07.02.2013, 19:03
quelle

4 Antworten

5

Sie werden andere Vorteile finden, um die Daten zu normalisieren, neben der Geschwindigkeit der Abfragen, die dagegen laufen ... wie Größe und Wartbarkeit, die allein die Normalisierung rechtfertigen sollten ...

Es wird jedoch wahrscheinlich auch die Geschwindigkeit von Abfragen verbessern; Derzeit ist eine einzelne Zeile mit 300 Textspalten sehr umfangreich und liegt mit hoher Wahrscheinlichkeit hinter dem 8.060-Byte-Limit für die Speicherung der Zeilendatenseite ... und wird stattdessen in den% allocation Units ROW_OVERFLOW_DATA oder LOB_DATA gespeichert.

Indem Sie die Größe jeder Zeile durch Normalisierung reduzieren, z. B. redundante Textdaten durch einen Fremdschlüssel TINYINT ersetzen und Spalten, die nicht vom Primärschlüssel dieser großen Tabelle abhängig sind, in eine andere Tabelle entfernen, sollten die Daten nicht mehr überlaufen, und Sie können auch mehr Zeilen pro Seite speichern.

Soweit der Overhead durch das Ausführen von JOIN hinzugefügt wurde, um die normalisierten Daten zu erhalten ... Wenn Sie Ihre Tabellen richtig indizieren, sollte dies keinen erheblichen Overhead verursachen. Wenn es jedoch einen inakzeptablen Overhead hinzufügt, können Sie die Daten nach Bedarf de-normalisieren.

    
Michael Fredrickson 07.02.2013, 19:21
quelle
4

Ob sich das lohnt, hängt davon ab, wie lange die Werte sind. Wenn die Werte beispielsweise Abkürzungen (2 Zeichen) oder Ländercodes (3 Zeichen) sind, wäre die resultierende Tabelle sogar größer als die vorhandene Tabelle. Denken Sie daran, dass Sie den Primärschlüssel der Referenztabelle einbeziehen müssen. Das wäre normalerweise eine Ganzzahl und belegt vier Bytes.

Es gibt andere gute Gründe, dies zu tun. Wenn Sie Referenztabellen mit gültigen Wertelisten verwenden, bleibt die Konsistenz der Datenbank erhalten. Die Referenztabellen können sowohl zur Validierung von Eingaben als auch zu Berichtszwecken verwendet werden. Zusätzliche Informationen können hinzugefügt werden, z. B. ein "langer Name" oder etwas Ähnliches.

Außerdem wird SQL Server Varchar-Spalten auf zusätzliche Seiten übertragen. Es verschüttet keine anderen Typen. Sie haben nur 300 Spalten, aber möglicherweise erreichen Ihre Datensatzdaten die 8-KB-Grenze für Daten auf einer einzelnen Seite.

Und wenn Sie sich entscheiden, weiterzumachen, würde ich vorschlagen, dass Sie nach "Themen" in den Spalten suchen. Es kann Gruppen von Spalten geben, die zusammen gruppiert werden können. . . Detaillierter Stop-Code und Stopp-Kategorie, kurzer Geschäftsname und vollständiger Firmenname. Sie gehen den Weg der Modellierung der Daten (eine gute Sache). Aber seien Sie vorsichtig, wenn Sie Dinge auf einer sehr niedrigen Ebene tun (100 Referenztabellen verwalten), anstatt eine vernünftige Menge von Entitäten und Beziehungen zu identifizieren.

    
Gordon Linoff 07.02.2013 19:18
quelle
2

1) Das System muss derzeit einen vollständigen Tabellenscan bei sehr signifikanten Datenmengen durchführen, was zu Leistungsproblemen führt. Es gibt viele Aspekte der Optimierung, die diese Leistung verbessern könnten. Die Konvertierung von Spalten in die richtigen Datentypen würde nicht nur die Leistung signifikant verbessern, indem die Größe jedes Datensatzes reduziert wird, sondern auch die Daten korrekt gemacht werden können. Wenn Sie eine Spalte abfragen, sehen Sie sich gerade den Text an, der mit dem Text im Feld verglichen wird. Mit nur Indexierung könnte dies verbessert werden, aber wenn man zu einem Lookup wechselt, kann man den ID-Wert aus einer Tabelle heraussuchen, die klein genug ist, um im Speicher zu bleiben und dann nur ganzzahlige Werte zu scannen, was ein viel schnellerer Prozess ist. 2) Wenn Daten auf die dritte Normalform o.ä. normalisiert werden, können Sie im Namen der Datenintegrität Fälle erkennen, in denen die Leistung leidet. Dies ist am meisten ein Problem, wenn die Engine nicht herausfinden kann, wie die Zeilen beschränkt werden, ohne zuerst die Daten zu projizieren. Wenn dies jedoch geschieht, kann der Ausführungsplan dies identifizieren und die Abfrage kann geändert werden, um die Wahrscheinlichkeit dafür zu verringern.

Ein weiterer Punkt ist, dass es sich anhört, als ob die Datenbank, wenn sie richtig strukturiert ist, möglicherweise zwischengespeichert werden kann, da die Datenmenge stark reduziert wäre. Wenn dies der Fall ist, würde die Leistung stark verbessert werden.

Der schnelle Weg zur Verbesserung der Performance wäre wahrscheinlich das Hinzufügen von Indizes. Dies würde jedoch die Gesamtgröße der Datenbank weiter erhöhen und das Problem der Speicherung doppelter Daten und möglicher Datenintegritätsprobleme nicht beheben.

Es gibt einige andere Änderungen, die vorgenommen werden können - wenn viele der Daten nicht immer benötigt werden, dann kann dies in eine verwandte Tabelle getrennt und nur nach Bedarf nachgeschlagen werden. Felder, die nicht für Nachschlagevorgänge in anderen Tabellen verwendet werden, sind besondere Kandidaten dafür, da die Joins dann in einer viel kleineren Tabelle sein können, während eine recht einfache Struktur erhalten bleibt, die nur die zusätzlichen Daten nachschlägt, wenn Sie die Daten tatsächlich identifiziert haben brauchen. Dies ist offensichtlich keine ordnungsgemäß normalisierte Struktur, aber kann eine schnelle und schmutzige Möglichkeit sein, die Leistung zu verbessern (nach dem Hinzufügen der Indizierung).

    
David Burton 07.02.2013 19:16
quelle
1
  1. Konstruieren Sie in Ihrem Kopf und auf Papier eine normalisierte Datenbankstruktur
  2. Erstellen Sie die Datenbank (mit Indizes)
  3. Konstruiere diesen Monolithen. Die Dinge werden nicht so schlecht aussehen. Ich würde vermuten, dass A LOT (ich meine viel) von Daten wiederholt wird
  4. Erstellen Sie SQL-Einfügeanweisungen, um die Daten in die Datenbank einzufügen
  5. Geh zu den Personen, die diesen Albtraum mit einer Schrotflinte gebaut haben. Viel Spaß.
Ed Heal 07.02.2013 19:29
quelle