Detaillierte Erklärung der Datentypen und Schemaoptimierung in MySQL

Ich lerne derzeit etwas über MySQL-Optimierung. Dieser Artikel stellt die Optimierung von Datentypen und Schemata vor.

1. Wählen Sie den optimierten Datentyp

MySQL unterstützt viele Datentypen und die Auswahl des richtigen Datentyps ist für die Leistung von entscheidender Bedeutung. Die folgenden Grundsätze können bei der Bestimmung des Datentyps hilfreich sein:

• Kleiner ist meist besser

Verwenden Sie nach Möglichkeit den kleinsten Datentyp, der die Daten korrekt speichern kann. Dadurch werden weniger Datenträger, Speicher und Cache verbraucht und die Verarbeitung dauert weniger lange.

• Mach es einfach

Wenn zwei Datentypen dasselbe Feld speichern können, ist die Auswahl des einfacheren Typs häufig die beste Option. Beispielsweise Ganzzahlen und Zeichenfolgen. Da die Operationskosten von Ganzzahlen geringer sind als die von Zeichen, ist die Leistung bei der Wahl zwischen beiden normalerweise besser, wenn man Ganzzahlen wählt.

• Versuchen Sie, NULL zu vermeiden

Wenn eine Spalte NULL sein kann, muss MySQL mehr Arbeit in Bezug auf Indizierung und Wertevergleich leisten. Obwohl die Auswirkungen auf die Leistung nicht signifikant sind, sollten Sie versuchen, NULL-Spalten zu vermeiden.

Zusätzlich zu den oben genannten Grundsätzen sind bei der Auswahl eines Datentyps folgende Schritte zu befolgen: Bestimmen Sie zuerst den geeigneten großen Typ, z. B. Daten, Zeichenfolge, Zeit usw.; wählen Sie dann den spezifischen Typ aus. Wir werden nun einige spezielle Typen dieser umfassenderen Kategorie besprechen und dabei mit Zahlen beginnen, die in zwei Typen vorkommen: ganze Zahlen und reelle Zahlen.

1.1 Integer-Typen

Die Ganzzahltypen und der von ihnen belegte Speicherplatz sind wie folgt:

Der Speicherbereich eines Integer-Typs hängt von der Größe des Speicherplatzes ab: -2^(N-1) bis 2^(N-1)-1, wobei N die Anzahl der Bits im Speicherplatz ist.

Der Integer-Typ hat das optionale Attribut UNSIGNED. Wenn es deklariert ist, bedeutet es, dass negative Zahlen nicht zulässig sind. Der Speicherbereich wird: 0 bis 2^(N)-1, was verdoppelt wird.

In MySQL kann man auch für Integer-Typen wie INT(1) eine Breite angeben, das ist allerdings nicht sehr aussagekräftig und schränkt den zulässigen Wertebereich nicht ein. Werte von -2^31 bis 2^31-1 können weiterhin gespeichert werden. Betroffen ist davon allerdings die Anzahl der Zeichen, die von den interaktiven Tools angezeigt werden, die mit MySQL interagieren.

1.2 Reelle Zahlentypen

Der Vergleich der reellen Zahlentypen sieht wie folgt aus:

Wie aus dem Obigen ersichtlich ist, haben sowohl FLOAT als auch DOUBLE feste Speicherplatzgrößen, können aber gleichzeitig nur annähernd berechnet werden, da sie Standard-Gleitkommaoperationen verwenden. Mit DECIMAL sind zwar genaue Berechnungen möglich, es benötigt jedoch mehr Platz und verursacht mehr Rechenaufwand.

Der von DECIMAL belegte Speicherplatz hängt von der angegebenen Genauigkeit ab, z. B. DECIMAL(M,D):

• M ist die maximale Länge der gesamten Zahl, der Wertebereich ist [1, 65] und der Standardwert ist 10;
• D ist die Länge nach dem Dezimalpunkt, der Wertebereich ist [0, 30] und D <= M. Der Standardwert ist 0.

Beim Speichern von DECIMAL-Daten speichert MySQL diese als Binärzeichenfolge und speichert 9 Ziffern für jeweils 4 Bytes. Wenn die Zahl weniger als 9 Ziffern hat, ist der von der Zahl belegte Speicherplatz wie folgt:

Die Ziffern vor und nach dem Dezimalpunkt werden separat gespeichert und der Dezimalpunkt belegt ebenfalls 1 Byte. Hier zwei Berechnungsbeispiele:

• DECIMAL(18, 9): Der ganzzahlige Teil ist 9 lang und belegt 4 Bytes. Der Dezimalteil ist 9 lang und belegt 4 Bytes. Addiert man 1 Byte für das Komma, sind insgesamt 9 Byte belegt.
• DECIMAL(20, 9): Der Integer-Teil ist 14 Bytes lang und belegt 7 (4+3) Bytes. Der Dezimalteil ist 9 lang und belegt 4 Bytes. Addiert man 1 Byte für das Komma, sind insgesamt 12 Byte belegt.

Es ist ersichtlich, dass DECIMAL immer noch viel Platz einnimmt. Daher wird DECIMAL nur benötigt, wenn genaue Berechnungen von Dezimalstellen erforderlich sind. Darüber hinaus können wir auch BIGINT anstelle von DECIMAL verwenden. Wenn Sie beispielsweise Berechnungen mit 5 Dezimalstellen sicherstellen müssen, können Sie den Wert mit 10 hoch 5 multiplizieren und als BIGINT speichern. Dadurch können die Probleme ungenauer Berechnungen mit Gleitkommaspeichern und die hohen Kosten präziser DECIMAL-Berechnungen vermieden werden.

1.3 Zeichenfolgentyp

Die am häufigsten verwendeten Zeichenfolgentypen sind VARCHAR und CHAR. Als Zeichenfolge mit variabler Länge verwendet VARCHAR 1 oder 2 zusätzliche Bytes, um die Länge der Zeichenfolge aufzuzeichnen. Wenn die maximale Länge 255 nicht überschreitet, wird nur 1 Byte zum Aufzeichnen der Länge benötigt. Wenn sie 255 überschreitet, werden 2 Bytes benötigt. VARCHAR ist geeignet für:

• Die maximale Länge ist viel größer als die durchschnittliche Länge;
• Um eine Fragmentierung zu vermeiden, werden Spalten seltener aktualisiert.
• Bei komplexen Zeichensätzen wie UTF-8 kann jedes Zeichen in einem anderen Byte gespeichert werden.

CHAR ist eine Zeichenfolge mit fester Länge. Entsprechend der definierten Zeichenfolgenlänge wird ausreichend Speicherplatz zugewiesen. Anwendbare Szenarien:

• Kurze Länge;
• Ähnliche Länge, wie MD5;
• Wird häufig aktualisiert.

Zusätzlich zu den Typen VARCHAR und CHAR können zum Speichern großer Zeichenfolgen auch die Typen BLOB und TEXT verwendet werden. Der Unterschied zwischen BLOB und TEXT besteht darin, dass BLOB im Binärformat gespeichert wird, während TEXT im Zeichenformat gespeichert wird. Dies bedeutet auch, dass Daten vom Typ BLOB kein Zeichensatzkonzept haben und nicht nach Zeichen sortiert werden können, während Daten vom Typ TEXT das Zeichensatzkonzept haben und nach Zeichen sortiert werden können. Die Verwendungsszenarien der beiden werden auch durch das Speicherformat bestimmt. Beim Speichern binärer Daten, z. B. Bilder, sollte BLOB verwendet werden, und beim Speichern von Text, z. B. Artikel, sollte der Typ TEXT verwendet werden.

1.4 Datums- und Zeittypen

Die minimale Zeitgranularität, die in MySQL gespeichert werden kann, beträgt Sekunden. Gängige Datumstypen sind DATETIME und TIMESTAMP.

Der von TIMESTAMP angezeigte Wert hängt von der Zeitzone ab. Dies bedeutet, dass der abgefragte Wert in verschiedenen Zeitzonen unterschiedlich ist. Zusätzlich zu den oben aufgeführten Unterschieden verfügt TIMESTAMP über eine besondere Eigenschaft. Wenn beim Einfügen und Aktualisieren der Wert der ersten TIMESTAMP-Spalte nicht angegeben ist, wird der Wert dieser Spalte auf die aktuelle Zeit gesetzt.

Während des Entwicklungsprozesses sollten wir versuchen, TIMESTAMP zu verwenden, hauptsächlich weil es nur halb so viel Platz wie DATETIME benötigt und platzsparender ist.

Was ist, wenn wir Datum und Uhrzeit sekundengenau speichern möchten? Da MySQL dies nicht bereitstellt, können wir BIGINT verwenden, um Zeitstempel auf Mikrosekundenebene zu speichern, oder DOUBLE, um die Dezimalstellen nach der Sekunde zu speichern.

1.5 Auswahl eines Bezeichners

Ganzzahlen sind im Allgemeinen die beste Wahl für Bezeichner, vor allem weil sie einfach und schnell zu berechnen sind und AUTO_INCREMENT verwenden können.

2. Paradigmen und Anti-Paradigmen

Einfach ausgedrückt ist ein Paradigma die Ebene der Entwurfsstandards, denen die Struktur einer Datentabelle entspricht. In der ersten Normalform sind Attribute untrennbar. Alle in aktuellen RDBMS-Systemen erstellten Tabellen entsprechen der ersten Normalform. Das zweite Paradigma eliminiert die teilweise Abhängigkeit von nicht-primären Attributcodes (die als Primärschlüssel verstanden werden können). Das dritte Paradigma eliminiert transitive Abhängigkeiten von nicht-primären Attributpaaren.

In einer streng normalisierten Datenbank kommen alle Sachdaten nur einmal vor und es kommt zu keiner Datenredundanz. Dies kann folgende Vorteile mit sich bringen:

• Aktualisierungsvorgänge sind schneller;
• Weniger Daten ändern;
• Tabellen sind kleiner, passen besser in den Speicher und führen Operationen schneller aus;
• Geringerer Bedarf für DISTINCT oder GROUP BY.
  

Da die Daten allerdings in verschiedenen Tabellen verstreut sind, müssen die Tabellen bei der Abfrage verknüpft werden. Der Vorteil der Antinormalisierung liegt darin, dass keine Assoziationen durchgeführt werden müssen und die Daten redundant gespeichert werden.

In tatsächlichen Anwendungen wird es weder eine vollständige Normalisierung noch eine vollständige Denormalisierung geben. Oft ist es notwendig, Normalisierung und Denormalisierung zu mischen. Die Verwendung eines teilweise normalisierten Schemas ist oft die beste Wahl. Zum Thema Datenbankdesign habe ich diesen Abschnitt im Internet gesehen, und man kann ihn nachvollziehen.

Der Datenbankentwurf sollte in drei Bereiche unterteilt werden:

Die erste Ebene: Wenn Sie gerade erst mit dem Datenbankdesign beginnen, ist die Bedeutung von Paradigmen noch nicht vollständig verstanden. Das zu dieser Zeit auftretende antiparadigmatische Design verursacht normalerweise Probleme.

Die zweite Ebene: Wenn Sie auf Probleme stoßen und diese lösen, werden Sie nach und nach die wahren Vorteile des Paradigmas verstehen und so in der Lage sein, schnell eine Datenbank mit geringer Redundanz und hoher Effizienz zu entwerfen.

Die dritte Ebene: Nach N Jahren Training werden Sie definitiv die Grenzen des Paradigmas entdecken. Zu diesem Zeitpunkt können wir das Paradigma durchbrechen und einen vernünftigeren Anti-Paradigma-Teil entwerfen.

Paradigmen sind wie die Bewegungen in den Kampfkünsten. Wenn ein Anfänger die Bewegungen nicht befolgt, wird er nur elend sterben. Schließlich sind die Spielzüge die von Meistern zusammengefasste und auf den Punkt gebrachte Essenz. Mit der Verbesserung Ihrer Kampfkunstfähigkeiten und der Beherrschung der Bewegungen werden Sie zwangsläufig die Grenzen der Bewegungen erkennen und diese entweder vergessen oder Ihre eigenen Bewegungen entwickeln.

Solange Sie noch einige Jahre hart arbeiten und durchhalten, werden Sie immer die zweite Ebene erreichen und immer das Gefühl haben, dass das Paradigma ein Klassiker ist. Zu diesem Zeitpunkt sind diejenigen, die sich nicht zu sehr auf das Paradigma verlassen können und die Beschränkungen des Paradigmas schnell durchbrechen, natürlich die Meister.

3. Cache-Tabellen und Übersichtstabellen

Zusätzlich zur oben erwähnten Antinormalisierung, bei der redundante Daten in der Tabelle gespeichert werden, können wir auch eine völlig unabhängige Übersichtstabelle oder Cache-Tabelle erstellen, um die Abrufanforderungen zu erfüllen.

Eine Cache-Tabelle ist eine Tabelle, in der Daten gespeichert werden, die aus anderen Tabellen im Schema abgerufen werden können, d. h. logisch redundante Daten. Eine Übersichtstabelle ist eine Tabelle, in der nicht redundante Daten gespeichert werden, die durch die Aggregation von Daten mithilfe von Anweisungen wie GROUP BY berechnet werden.

Cache-Tabellen können verwendet werden, um Such- und Abfrageanweisungen zu optimieren. Eine Technik, die hier verwendet werden kann, ist die Verwendung unterschiedlicher Speicher-Engines für Cache-Tabellen. Beispielsweise verwendet die Haupttabelle InnoDB, während die Cache-Tabelle MyISAM verwenden kann, um einen kleineren Indexspeicherplatz zu erhalten. Sie können die Cache-Tabelle sogar in ein dediziertes Suchsystem wie Lucene einfügen.

Übersichtstabellen sind so konzipiert, dass sie den hohen Aufwand für statistische Echtzeitberechnungen vermeiden. Der Aufwand ergibt sich aus zwei Aspekten: Zum einen muss der Großteil der Daten in der Tabelle gescannt werden, zum anderen müssen spezielle Indizes erstellt werden, die sich auf UPDATE-Vorgänge auswirken. Um beispielsweise die Anzahl der WeChat-Freunde in den letzten 24 Stunden abzufragen, können Sie stündlich die gesamte Tabelle scannen, nach den Statistiken einen Datensatz in die Übersichtstabelle schreiben und bei der Abfrage nur die letzten 24 Datensätze in der Übersichtstabelle abfragen, ohne bei jeder Abfrage die gesamte Tabelle nach Statistiken scannen zu müssen.

Beim Einsatz von Cache-Tabellen und Übersichtstabellen müssen Sie je nach Bedarf entscheiden, ob Sie die Daten in Echtzeit verwalten oder in regelmäßigen Abständen neu erstellen möchten. Im Vergleich zur Echtzeitwartung können durch regelmäßige Rekonstruktionen mehr Ressourcen gespart und die Tabellenfragmentierung verringert werden. Beim Umbau muss weiterhin sichergestellt werden, dass die Daten auch im laufenden Betrieb zur Verfügung stehen, was durch sogenannte Schattentabellen erreicht werden soll. Erstellen Sie hinter der realen Tabelle eine Schattentabelle. Nachdem Sie die Daten ausgefüllt haben, wechseln Sie durch eine atomare Umbenennungsoperation zwischen der Schattentabelle und der Originaltabelle.

4. Beschleunigen Sie ALTER TABLE-Operationen

Wenn MySQL eine ALTER TABLE-Operation ausführt, erstellt es häufig eine neue Tabelle, ruft Daten aus der alten Tabelle ab, fügt sie in die neue Tabelle ein und löscht dann die alte Tabelle. Wenn die Tabelle groß ist, dauert dieser Vorgang sehr lange und führt zu einer Unterbrechung des MySQL-Dienstes. Um Dienstunterbrechungen zu vermeiden, werden üblicherweise zwei Techniken verwendet:

Führen Sie den Vorgang ALTER TABLE auf einem Computer aus, der keine Dienste bereitstellt, und wechseln Sie dann zur primären Datenbank, die Dienste bereitstellt.
„Schattenkopie“ erstellt eine neue Tabelle, die nichts mit der Originaltabelle zu tun hat. Nachdem die Datenmigration abgeschlossen ist, wechseln Sie durch den Umbenennungsvorgang.
Allerdings führen nicht alle ALTER TABLE-Operationen zu einer Rekonstruktion der Tabelle. Wenn Sie beispielsweise den Standardwert eines Felds ändern, führt die Verwendung von MODIFY COLUMN zu einer Rekonstruktion der Tabelle, während die Verwendung von ALTER COLUMN dies nicht tut und die Operationsgeschwindigkeit sehr hoch ist. Dies liegt daran, dass beim Ändern des Standardwerts durch ALTER COLUMN direkt die FRM-Datei der vorhandenen Tabelle (die den Standardwert des Felds speichert) geändert wird, ohne die Tabelle neu zu erstellen.

siehe

Leistungsstarkes MySQL

MySQL DECIMAL-Datentyp

Oben finden Sie eine ausführliche Erläuterung der Datentypen und Schemaoptimierung in MySQL. Weitere Informationen zu MySQL-Datentypen und Schemaoptimierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!