Datentyp- und Schemaoptimierung in MySQL

Ich habe kürzlich etwas über MySQL-Optimierung gelernt. In der Spalte „MySQL-Tutorial“ wird die Optimierung von Datentypen und Schemata vorgestellt.

1. Wählen Sie optimierte Datentypen

MySQL unterstützt viele Datentypen und die Auswahl des richtigen Datentyps ist entscheidend für die Leistung. Die folgenden

Prinzipien

können dabei helfen, den Datentyp zu bestimmen:

1. Kleiner ist in der Regel besser

   Der kleinste Datentyp, der Daten korrekt speichern kann, sollte so oft wie möglich verwendet werden, solange er ausreicht. Dies beansprucht weniger Festplatte, Speicher und Cache und nimmt weniger Zeit für die Verarbeitung in Anspruch.

2. Einfach ist besser

   Wenn zwei Datentypen ein Feld speichern können, ist die Wahl des einfacheren oft die beste Wahl. Zum Beispiel Ganzzahlen und Zeichenfolgen, da die Operationskosten von Ganzzahlen geringer sind als die von Zeichen. Wenn Sie also zwischen beiden wählen, führt die Auswahl von Ganzzahlen normalerweise zu einer besseren Leistung.

3. Versuchen Sie, NULL zu vermeiden.

   Wenn eine Spalte NULL sein kann, muss für MySQL mehr Arbeit in Bezug auf die Indizierung und den Wertevergleich geleistet werden. Obwohl die Auswirkungen auf die Leistung nicht groß sind, sollte dies versucht werden Vermeiden Sie es so weit wie möglich, NULL zu entwerfen.

Zusätzlich zu den oben genannten Grundsätzen müssen Sie bei der Auswahl eines Datentyps die folgenden

Schritte befolgen: Bestimmen Sie zunächst den geeigneten großen Typ, z. B. Daten, Zeichenfolge, Zeit usw., und wählen Sie dann den spezifischen Typ aus. Im Folgenden werden einige spezifische Typen unter den großen Typen besprochen. Der erste Typ sind Zahlen, die zwei Typen haben: ganze Zahlen und reelle Zahlen.

1.1 Integer-Typ

Der Integer-Typ und der belegte Platz sind wie folgt:

Integer-Typ Speicherplatzgröße (Bit) TINYINT8SMALLINT16 MEDIUMINT24INT32BIGINT64

Der Bereich, den der Integer-Typ speichern kann, hängt von der Raumgröße ab: -2^(N-1) bis 2^(N-1)-1, wobei N die Anzahl der Ziffern in der Raumgröße ist.

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

In MySQL können Sie auch die Breite für Ganzzahltypen wie INT(1) angeben, dies ist jedoch von geringer Bedeutung und schränkt den zulässigen Wertebereich nicht ein. Es können weiterhin -2^31 bis 2^31 gespeichert werden -1 Der Wert beeinflusst die Anzahl der Zeichen, die das interaktive Tool mit MySQL anzeigt. 1.2 Typ reeller Zahlen

4

Negative Zahlen: -3,4E+38～-1,17E-38; nicht negative Zahlen: 0, 1,17E-38~3,4E+38

Näherungsweise BerechnungDOPPELT8Negative Zahlen : -1.79e+308 ～ -2,22 E-308; Genaue BerechnungWie aus dem Obigen ersichtlich ist, haben FLOAT und DOUBLE beide feste Raumgrößen, können aber gleichzeitig nur annähernd berechnet werden, da sie Standard-Gleitkommaoperationen verwenden. DECIMAL kann präzise Berechnungen durchführen, nimmt aber gleichzeitig mehr Platz ein und verbraucht mehr Rechenaufwand. M ist die maximale Länge der gesamten Zahl, der Wertebereich ist [1, 65] und der Standardwert Wert ist 10; D Es ist die Länge nach dem Komma, der Wertebereich ist [0, 30] und D MySQL speichert den Typ DECIMAL als Binärzeichenfolge. Alle 4 Bytes speichern 9 Zahlen. Wenn die Zahl weniger als 9 Stellen hat, ist der von der Zahl belegte Platz wie folgt: Anzahl der Zahlen Speicherplatz belegt (Byte)2

Der von DECIMAL belegte Platz hängt von der angegebenen Genauigkeit ab, zum Beispiel DECIMAL(M,D):
1, 2	1		3, 4

5, 6

3

• 7, 8

4

Die Zeichen vor und nach dem Dezimalpunkt werden separat gespeichert und der Dezimalpunkt belegt ebenfalls 1 Byte. Hier sind zwei Berechnungsbeispiele: DECIMAL(20, 9): Die Länge des ganzzahligen Teils beträgt 14 und belegt 7 (4+3) Bytes. Die Länge des Dezimalteils beträgt 9 und belegt 4 Bytes. Gleichzeitig werden durch Hinzufügen von 1 Byte für den Dezimalpunkt insgesamt 12 Bytes belegt. Es ist ersichtlich, dass DECIMAL immer noch viel Platz einnimmt, sodass DECIMAL nur benötigt wird, wenn genaue Berechnungen von Dezimalstellen erforderlich sind. Darüber hinaus können wir auch BIGINT anstelle von DECIMAL verwenden. Wenn wir beispielsweise die Berechnung von 5 Nachkommastellen sicherstellen müssen, können wir den Wert mit 10 hoch multiplizieren und ihn als BIGINT speichern Ungenaue Gleitkomma-Speicherberechnungen und DECIMAL gleichzeitig. 1.3 String-TypDie am häufigsten verwendeten String-Typen sind VARCHAR und CHAR. VARCHARAls werden 1 oder 2 zusätzliche Bytes zum Aufzeichnen der Länge des Strings verwendet. Wenn die maximale Länge 255 nicht überschreitet, ist nur 1 Byte zum Aufzeichnen der Länge erforderlich , 2 Bytes. VARCHARs : Verwenden Sie komplexe Zeichensätze, wie z. B. UTF-8, und jedes Zeichen kann anders sein Byte-Speicher. ist eine String mit fester Länge, die entsprechend der definierten Stringlänge ausreichend Platz zuweist:

	DECIMAL(18, 9): Die Länge des ganzzahligen Teils beträgt 9 und belegt 4 Bytes. Die Länge des Dezimalteils beträgt 9 und belegt 4 Bytes. Gleichzeitig werden durch Hinzufügen von 1 Byte für den Dezimalpunkt insgesamt 9 Bytes belegt.
String mit variabler Länge	anwendbare Szenarien
Die maximale Länge ist viel größer als die durchschnittliche Länge;	Die Spalten werden weniger aktualisiert, um Fragmentierung zu vermeiden;
	CHAR

kurze Länge;

1. häufig aktualisiert .
2. Zusätzlich zu VARCHAR und CHAR können auch BLOB- und TEXT-Typen zum Speichern großer Zeichenfolgen verwendet werden. Der Unterschied zwischen BLOB und TEXT besteht darin, dass

BLOB

in

binär

gespeichert wird, während

TEXT

in Zeichen gespeichert wird. Dies führt auch dazu, dass Daten vom Typ BLOB nicht das Konzept eines Zeichensatzes haben und nicht nach Zeichen sortiert werden können, während der Typ TEXT das Konzept eines Zeichensatzes hat und nach Zeichen sortiert werden kann. Die Verwendungsszenarien beider werden auch durch das Speicherformat bestimmt. Beim Speichern von Binärdaten wie Bildern sollte BLOB verwendet werden, und beim Speichern von Texten wie Artikeln sollte der Typ TEXT verwendet werden. 1.4 Datums- und UhrzeittypenDie minimale Zeitgranularität, die in MySQL gespeichert werden kann, beträgt Sekunden. Zu den häufig verwendeten Datumstypen gehören DATETIME und TIMESTAMP.

2. Typ
3. Speicherinhalt

Speicherplatzgröße (Byte)

Zeitzonenkonzept

DATETIME

1. Eine Ganzzahl im Format YYYYMMDDHHMMSS
2. 8
3. Keine

TIMESTAMP von 1970 Die Anzahl der Sekunden seit Mitternacht am 1. Januar des Jahres4hat

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

Wir sollten versuchen, TIMESTAMP während des Entwicklungsprozesses zu verwenden, vor allem, weil seine Speicherplatzgröße nur halb so groß ist wie die von DATETIME und seine Speicherplatzeffizienz höher ist.

Was wäre, wenn wir Datum und Uhrzeit sekundengenau speichern möchten? Da MySQL dies nicht bereitstellt, können wir BIGINT verwenden, um Zeitstempel auf Mikroebene zu speichern, oder DOUBLE, um den Dezimalteil nach Sekunden zu speichern.

1.5 Auswahl von Bezeichnern

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

2. Normalform und Anti-Normalform

Einfach ausgedrückt ist Normalform die Ebene eines bestimmten Designstandards, dem die Tabellenstruktur einer Datentabelle entspricht. In der ersten Normalform sind Attribute untrennbar miteinander verbunden. Die vom aktuellen RDBMS-System erstellten Tabellen stimmen alle mit der ersten Normalform überein. Die zweite Normalform eliminiert die teilweise Abhängigkeit nicht-primärer Attribute von Codes (die als Primärschlüssel aufgefasst werden können). Die dritte Normalform eliminiert die transitive Abhängigkeit nicht-primärer Attribute von Codes. Für eine spezifische Einführung können Sie diese Antwort auf Zhihu (https://www.zhihu.com/question/24696366/answer/29189700) lesen.

StrengnormalisiertIn der Datenbank erscheinen alle Sachdaten nur einmal Es wird keine Datenredundanz geben und die Vorteile, die dies mit sich bringen kann, sind:

1. weniger Daten ändern;
2. kleinere Tabelle, bessere Platzierung im Speicher,
3. Weniger Bedarf für DISTINCT oder GROUP BY.
4. Da die Daten jedoch in verschiedenen Tabellen verstreut sind, müssen die Tabellen bei der Abfrage verknüpft werden. Der Vorteil von

Anti-Paradigma

besteht darin, dass keine Zuordnung erforderlich ist und die Daten redundant gespeichert werden. In praktischen Anwendungen kommt es nicht zu einer vollständigen Normalisierung oder vollständigen Denormalisierung. Oft ist die Verwendung eines teilweise normalisierten Schemas die beste Wahl. Bezüglich des Datenbankdesigns habe ich diesen Absatz im Internet gesehen, und Sie können ihn spüren.

Das Datenbankdesign sollte in drei Bereiche unterteilt werden:

Erster Bereich: Ich habe gerade mit dem Datenbankdesign begonnen und habe die Bedeutung von Paradigmen noch nicht vollständig verstanden. Das zu diesem Zeitpunkt auftretende Anti-Paradigma-Design wird im Allgemeinen Probleme verursachen.

Zweite Ebene: Wenn Sie auf Probleme stoßen und diese lösen, verstehen Sie nach und nach die wahren Vorteile des Paradigmas, sodass Sie schnell eine Datenbank mit geringer Redundanz und hoher Effizienz entwerfen können.

Der dritte Bereich: Nach N Jahren Training werden Sie definitiv die Grenzen des Paradigmas erkennen. Brechen Sie zu diesem Zeitpunkt das Paradigma und entwerfen Sie einen vernünftigeren Anti-Paradigma-Teil.

Das Paradigma ist wie die Bewegungen in der Kampfkunst. Anfänger, die versuchen, den Bewegungen nicht zu folgen, werden nur in Verlegenheit sterben. Schließlich sind die Tricks die Essenz, die von den Meistern zusammengefasst wird. Wenn Sie Ihre Kampfkünste verbessern und die Bewegungen beherrschen, werden Sie unweigerlich die Grenzen der Bewegungen entdecken und sie entweder vergessen oder Ihre eigenen entwickeln.

Solange Sie hart arbeiten und noch ein paar Jahre durchhalten, können Sie immer den zweiten Zustand erreichen und werden immer das Gefühl haben, dass das Paradigma ein Klassiker ist. Zu diesem Zeitpunkt sind diejenigen, die die Beschränkungen des Paradigmas schnell durchbrechen können, ohne sich zu sehr auf das Paradigma zu verlassen, von Natur aus Experten.

4. Cache-Tabelle und Zusammenfassungstabelle

Zusätzlich zum oben erwähnten Anti-Paradigma und der Speicherung redundanter Daten in der Tabelle können wir auch eine völlig unabhängige Zusammenfassungstabelle oder Cache-Tabelle erstellen, um den Anforderungen des Abrufs gerecht zu werden.

Cache-Tabelle

bezieht sich auf eine Tabelle, die Daten speichert, die aus anderen Tabellen im Schema abgerufen werden können, also logisch redundante Daten. Die

Zusammenfassungstabelle

bezieht sich auf das Speichern nicht redundanter Daten, die durch Aggregieren von Daten mithilfe von Anweisungen wie GROUP BY berechnet werden. Cache-Tabellen können verwendet werden, um Such- und Abrufanweisungen zu optimieren. Dazu gehört die Verwendung verschiedener Speicher-Engines für Cache-Tabellen. Beispielsweise verwendet die Haupttabelle InnoDB, während die Cache-Tabelle zum Abrufen verwendet werden kann ein kleinerer Index Nimmt Platz ein. Sie können die Cache-Tabelle sogar in ein spezielles Suchsystem wie Lucene einfügen. Mit der Übersichtstabelle sollen

die hohen Kosten vermieden werden, die durch die Echtzeitberechnung statistischer Werte entstehen. Zum einen müssen die meisten Daten in der Tabelle gescannt werden, zum anderen Es werden bestimmte Indizes eingerichtet, die sich auf den UPDATE-Vorgang auswirken. Um beispielsweise die Anzahl der WeChat-Momente in den letzten 24 Stunden abzufragen, können Sie jede Stunde die gesamte Tabelle scannen und nach der Statistik einen Datensatz in die Zusammenfassungstabelle schreiben. Bei der Abfrage müssen Sie nur die letzten 24 Datensätze in der Zusammenfassung abfragen table statt every Bei jeder Abfrage wird die gesamte Tabelle nach Statistiken durchsucht.

Bei der Verwendung von Cache-Tabellen und Übersichtstabellen müssen wir abhängig von unseren Anforderungen entscheiden, ob wir „Daten in Echtzeit verwalten“ oder „regelmäßig neu erstellen“ möchten. Im Vergleich zur Echtzeitwartung kann eine regelmäßige Rekonstruktion mehr Ressourcen einsparen und eine geringere Tabellenfragmentierung verursachen. Während des Umbaus müssen wir weiterhin sicherstellen, dass die Daten während des Betriebs verfügbar sind, was durch „Schattentabelle

“ erreicht werden muss. Erstellen Sie eine Schattentabelle hinter der realen Tabelle und wechseln Sie nach dem Ausfüllen der Daten die Schattentabelle und die Originaltabelle durch eine atomare Umbenennungsoperation.

5. Beschleunigen Sie die ALTER TABLE-Operation.

Wenn MySQL die ALTER TABLE-Operation ausführt, erstellt es häufig eine neue Tabelle, checkt dann die Daten aus der alten Tabelle aus, fügt sie in die neue Tabelle ein und löscht dann die alte Wenn die Tabelle sehr groß ist, dauert dies lange und führt zu einer Unterbrechung des MySQL-Dienstes. Um eine Dienstunterbrechung zu vermeiden, können Sie normalerweise zwei Techniken verwenden:

1. Führen Sie den ALTER TABLE-Vorgang auf einem Computer aus, der keine Dienste bereitstellt, und wechseln Sie dann zur Hauptbibliothek, die Dienste bereitstellt
2. „Schattenkopie“, Erstellen Eine neue Tabelle, die nichts mit der ursprünglichen Tabelle zu tun hat, kann nach Abschluss der Datenmigration durch den Umbenennungsvorgang umgeschaltet werden.

Aber nicht alle ALTER TABLE-Vorgänge führen zu einer Tabellenrekonstruktion Wenn Sie beispielsweise den Standardwert eines Felds ändern, führt die Verwendung von MODIFY COLUMN zu einer Tabellenrekonstruktion, während die Verwendung von ALTER COLUMN keine Tabellenrekonstruktion verursacht schnell. . Dies liegt daran, dass bei der Änderung des Standardwerts durch ALTER COLUMN direkt die .frm-Datei der vorhandenen Tabelle (in der der Standardwert des Felds gespeichert wird) geändert wird, ohne dass die Tabelle neu erstellt werden muss.

Weitere verwandte kostenlose Lernempfehlungen: MySQL-Tutorial(Video)

Das obige ist der detaillierte Inhalt vonDatentyp- und Schemaoptimierung in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!