So entwerfen Sie eine MySQL-Hochleistungstabelle

Gutes logisches Design und physisches Design sind die Eckpfeiler einer hohen Leistung. Das Schema sollte entsprechend den Abfrageanweisungen entworfen werden, die das System ausführen wird, was häufig eine Abwägung verschiedener Faktoren erfordert.

1. Wählen Sie den optimierten Datentyp aus

MySQL unterstützt viele Datentypen. Die Auswahl des richtigen Datentyps ist entscheidend für das Erreichen einer hohen Leistung.

Kleiner ist normalerweise besser

Kleinere Datentypen sind im Allgemeinen schneller, da sie weniger Festplatte, Speicher und CPU-Cache belegen und weniger CPU-Zyklen für die Verarbeitung erfordern.

Halten Sie es einfach

Operationen an einfachen Datentypen erfordern im Allgemeinen weniger CPU-Zyklen. Beispielsweise sind Ganzzahloperationen kostengünstiger als Zeichenoperationen, da Zeichensätze und Sortierungen Zeichenvergleiche komplexer machen als Ganzzahlvergleiche.

Versuchen Sie, NULL zu vermeiden

Wenn die Abfrage NULL-fähige Spalten enthält, ist die Optimierung für MySQL schwieriger, da NULL-fähige Spalten Indizes, Indexstatistiken und Wertevergleiche komplexer machen. Spalten, die NULL sein können, benötigen mehr Speicherplatz und erfordern eine spezielle Behandlung in MySQL. Wenn NULL-fähige Spalten indiziert werden, erfordert jeder Indexdatensatz ein zusätzliches Byte, was in MyISAM sogar dazu führen kann, dass ein Index fester Größe (z. B. ein Index mit nur einer ganzzahligen Spalte) zu einem Index variabler Größe wird.

Natürlich gibt es Ausnahmen. Beispielsweise verwendet InnoDB ein separates Bit zum Speichern von NULL-Werten, sodass es eine gute Speicherplatzeffizienz für spärliche Daten bietet.

1. Ganzzahltyp

Es gibt zwei Arten von Zahlen: ganze Zahlen und reelle Zahlen. Wenn Sie Ganzzahlen speichern, können Sie diese Ganzzahltypen verwenden: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Verwenden Sie jeweils 8, 16, 24, 32 und 64-Bit-Speicherplatz.

Integer-Typen haben ein optionales **UNSIGNED**-Attribut, was bedeutet, dass negative Werte nicht zulässig sind, was die Obergrenze positiver Zahlen ungefähr verdoppelt. Beispielsweise kann TINYINT.UNSIGNED den Bereich 0–255 speichern, während der Speicherbereich von TINYINT -128–127 beträgt.

Signierte und nicht signierte Typen nutzen denselben Speicherplatz und haben die gleiche Leistung, sodass Sie je nach tatsächlicher Situation den geeigneten Typ auswählen können.

Ihre Wahl bestimmt, wie MySQL Daten im Speicher und auf der Festplatte speichert. Ganzzahlberechnungen verwenden jedoch im Allgemeinen 64-Bit-BIGINT-Ganzzahlen, selbst in einer 32-Bit-Umgebung. (Die Ausnahme bilden einige Aggregatfunktionen, die für Berechnungen DECIMAL oder DOUBLE verwenden.)

MySQL kann die Breite für Ganzzahltypen wie INT(11) angeben, was für die meisten Anwendungen bedeutungslos ist: Es schränkt den zulässigen Wertebereich nicht ein, sondern spezifiziert nur einige interaktive Tools von MySQL (wie den MySQL-Befehlszeilen-Client). ) Wird verwendet, um die Anzahl der Zeichen anzuzeigen. Für Speicher- und Berechnungszwecke sind INT(1) und INT(20) gleich.

2.Realer Zahlentyp

Reelle Zahlen sind Zahlen mit einem Dezimalteil. Sie dienen jedoch nicht nur zum Speichern von Dezimalteilen, DECIMAL kann auch zum Speichern von ganzen Zahlen größer als BIGINT verwendet werden.

FLOAT- und DOUBLE-Typen unterstützen Näherungsberechnungen mit Standard-Gleitkommaoperationen.

Der Typ DECIMAL wird zum Speichern präziser Dezimalzahlen verwendet.

Sowohl Gleitkomma- als auch DECIMAL-Typen können die Genauigkeit angeben. Für DECIMAL-Spalten können Sie die maximal zulässige Anzahl von Ziffern vor und nach dem Dezimalpunkt angeben. Dies wirkt sich auf den Platzverbrauch der Spalte aus.

Es gibt verschiedene Möglichkeiten, die erforderliche Genauigkeit für Gleitkommaspalten anzugeben, was dazu führt, dass MySQL einen anderen Datentyp wählt oder den Wert beim Speichern rundet. Da diese Genauigkeitsdefinitionen nicht dem Standard entsprechen, empfehlen wir, nur den Datentyp und nicht die Genauigkeit anzugeben.

Gleitkommatypen benötigen normalerweise weniger Platz als DECIMAL, wenn Werte im gleichen Bereich gespeichert werden. FLOAT verwendet 4 Byte Speicher. DOUBLE belegt 8 Bytes und hat eine höhere Präzision und einen größeren Bereich als FLOAT. Wie bei ganzzahligen Typen können Sie nur den Speichertyp auswählen; MySQL verwendet DOUBLE als Typ für interne Gleitkommaberechnungen.

Aufgrund des zusätzlichen Platz- und Rechenaufwands sollten Sie versuchen, nur DECIMAL zu verwenden, wenn Sie präzise Berechnungen mit Dezimalzahlen durchführen. Wenn die Daten jedoch relativ groß sind, können Sie BIGINT anstelle von DECIMAL verwenden. Multiplizieren Sie einfach die zu speichernde Währungseinheit mit dem entsprechenden Vielfachen entsprechend der Anzahl der Dezimalstellen.

3. String-Typ

VARCHAR

Wird zum Speichern variabler Zeichenfolgen verwendet, die Länge wird bis 65535 unterstützt
Erfordert 1 oder 2 zusätzliche Bytes, um die Länge der Zeichenfolge aufzuzeichnen
Geeignet: Die maximale Länge der Zeichenfolge ist viel größer als die durchschnittliche Länge; Aktualisierungen sind selten

CHAR

Feste Länge, der Längenbereich liegt zwischen 1 und 255
Geeignet für: Speichern sehr kurzer Zeichenfolgen oder aller Werte, die sich häufig ändern;

Großzügigkeit ist unklug

Der Speicherplatzaufwand beim Speichern von „Hallo“ mit VARCHAR(5) und VARCHAR(200) ist der gleiche. Gibt es also Vorteile bei der Verwendung kürzerer Spalten?

Es stellt sich heraus, dass es große Vorteile hat. Längere Spalten verbrauchen mehr Speicher, da MySQL normalerweise Speicherblöcke mit fester Größe für die Speicherung interner Werte zuweist. Dies ist besonders schlimm, wenn temporäre In-Memory-Tabellen zum Sortieren oder für Vorgänge verwendet werden. Ebenso schlimm ist es beim Sortieren mithilfe temporärer Festplattentabellen.

Die beste Strategie besteht also darin, nur den Platz zuzuweisen, den Sie wirklich benötigen.

4.BLOB- und TEXT-Typen

BLOB und TEXT sind beide Zeichenfolgendatentypen, die zum Speichern großer Datenmengen konzipiert sind und im Binär- bzw. Zeichenmodus gespeichert werden.

Im Gegensatz zu anderen Typen behandelt MySQL jeden BLOB- und TEXT-Wert als unabhängiges Objekt. Speicher-Engines führen beim Speichern normalerweise eine spezielle Verarbeitung durch. Wenn die BLOB- und TEXT-Werte zu groß sind, verwendet InnoDB einen dedizierten „externen“ Speicherbereich. Zu diesem Zeitpunkt müssen für jeden Wert 1 bis 4 Bytes in der Zeile gespeichert werden .

Der einzige Unterschied zwischen BLOB und TEXT besteht darin, dass der BLOB-Typ Binärdaten speichert und keine Sortierung oder Zeichensatz hat, während der TEXT-Typ über einen Zeichensatz und eine Sortierung verfügt

5.Datums- und Uhrzeittyp

Meistens gibt es keine Alternativen zu dem Typ, sodass es keine Frage gibt, was die beste Wahl ist. Das einzige Problem besteht darin, was beim Speichern von Datum und Uhrzeit zu tun ist. MySQL bietet zwei ähnliche Datumstypen: DATE TIME und TIMESTAMP.

Aber derzeit bevorzugen wir die Methode der Speicherung von Zeitstempeln, daher werden DATE TIME und TIMESTAMP hier nicht allzu ausführlich erläutert.

6.Andere Typen

6.1 Bezeichner auswählen

Der kleinste Datentyp sollte unter der Voraussetzung ausgewählt werden, dass er den Anforderungen des Wertebereichs gerecht wird und Raum für zukünftiges Wachstum lässt.

• Integer-Typ

Ganzzahlen sind normalerweise die beste Wahl für Identitätsspalten, da sie schnell sind und AUTO_INCREMENT verwenden können.

• ENUM- und SET-Typen

Die EMUM- und SET-Typen sind im Allgemeinen eine schlechte Wahl für Identitätsspalten, obwohl sie für einige statische „Definitionstabellen“, die nur feste Zustände oder Typen enthalten, in Ordnung sein können. Die Spalten ENUM und SET eignen sich zum Speichern fester Informationen wie Bestellstatus, Produkttyp und Geschlecht der Person.

• String-Typ

Wenn möglich, sollten String-Typen als Identitätsspalten vermieden werden, da sie Platz beanspruchen und im Allgemeinen langsamer als numerische Typen sind.

Sie müssen auch vollständig „zufälligen“ Zeichenfolgen mehr Aufmerksamkeit schenken, z. B. Zeichenfolgen, die von MDS(), SHAl() oder UUID() generiert werden. Die von diesen Funktionen generierten neuen Werte werden willkürlich über einen großen Raum verteilt, was dazu führen kann, dass INSERT- und einige SELECT-Anweisungen sehr langsam sind. Wenn UUID-Werte gespeichert werden, sollte das „-“-Zeichen entfernt werden.

6.2 Spezielle Typdaten

Einige Arten von Datenbrunnen entsprechen nicht direkt den integrierten Typen. Ein weiteres Beispiel sind Zeitstempel mit geringer Kilosekundengenauigkeit. Es werden häufig VARCHAR(15)-Spalten zum Speichern von IP-Adressen verwendet. Dabei handelt es sich jedoch tatsächlich um 32-Bit-Ganzzahlen ohne Vorzeichen. Die durch Dezimalpunkte in vier Segmente unterteilte Darstellung der Adresse dient lediglich der leichteren Lesbarkeit. Daher sollten IP-Adressen als vorzeichenlose Ganzzahlen gespeichert werden. MySQL bietet die Funktionen INET_ATON() und INET_NTOA() zum Konvertieren zwischen diesen beiden Darstellungsmethoden.

2. Tischstrukturdesign

1. Paradigma und Anti-Paradigma

Normalerweise gibt es viele Möglichkeiten, bestimmte Daten darzustellen, von vollständig normalisiert bis vollständig denormalisiert, und es gibt einen Kompromiss zwischen beiden. In einer normalisierten Datenbank kommt jeder Fakt genau einmal vor. Im Gegensatz dazu sind in einer denormalisierten Datenbank Informationen redundant und können an mehreren Orten gespeichert werden.

Vor- und Nachteile des Paradigmas

Wenn eine Leistungsverbesserung in Betracht gezogen wird, wird häufig empfohlen, das Schema zu normalisieren, insbesondere in schreibintensiven Szenarien.

• Normalisierte Aktualisierungsvorgänge sind normalerweise schneller als denormalisierte.
• Wenn die Daten gut normalisiert sind, gibt es nur wenige oder keine doppelten Daten, sodass weniger Daten geändert werden müssen.
• Normalisierte Tabellen sind normalerweise kleiner und passen besser in den Speicher, sodass Vorgänge schneller ausgeführt werden.
• Weniger redundante Daten bedeuten, dass beim Abrufen von Listendaten weniger DISTINCT- oder GROUP BY-Anweisungen erforderlich sind.

Vor- und Nachteile des Anti-Paradigmas

Es besteht kein Bedarf an verwandten Tabellen, daher ist das Worst-Case-Szenario für die meisten Abfragen – selbst wenn die Tabelle keinen Index verwendet – ein vollständiger Tabellenscan. Dies kann viel schneller als assoziativ sein, wenn die Daten größer als der Speicher sind, da zufällige E/A vermieden werden.

Einzelne Tabellen können auch effizientere Indizierungsstrategien verwenden.

Mischung von Normalisierung und Denormalisierung

In praktischen Anwendungen müssen sie häufig gemischt werden, und es können teilweise normalisierte Schemata, Cache-Tabellen und andere Techniken verwendet werden.

Fügen Sie der Tabelle entsprechend redundante Felder hinzu, z. B. Leistungspriorität, dies erhöht jedoch die Komplexität. Tabellenverknüpfungsabfragen können vermieden werden.

Einfach und vertraut mit dem Datenbankparadigma

<br> Erste Normalform (1NF): Feldwerte sind atomar und können nicht geteilt werden (alle relationalen Datenbanksysteme erfüllen die erste Normalform);<br> Beispiel: Namensfeld, bei dem der Nachname und der Vorname ein Ganzes sind. Wenn der Nachname und der Vorname unterschieden werden, müssen zwei unabhängige Felder eingerichtet werden;

Zweite Normalform (2NF): Eine Tabelle muss einen Primärschlüssel haben, d. h. jede Datenzeile kann eindeutig unterschieden werden;
Hinweis: Zuerst muss die erste Normalform erfüllt sein;

Dritte Normalform (3NF): Eine Tabelle darf keine Informationen über Nicht-Schlüsselfelder in anderen zugehörigen Tabellen enthalten, d. h. die Datentabelle darf keine redundanten Felder enthalten;
Hinweis: Zuerst muss die zweite Normalform erfüllt sein;

2. Tabellenfelder sind weniger raffiniert

• E/A-effizient
• Felder lassen sich leicht trennen und pflegen
• Einzeltabellen-1G-Volumen-500-W-Zeilenbewertung
• Eine einzelne Zeile sollte 200 Byte nicht überschreiten
• Nicht mehr als 50 INT-Felder in einer einzelnen Tabelle
• Nicht mehr als 20 CHAR(10)-Felder in einer einzelnen Tabelle
• Es wird empfohlen, die Anzahl der Felder in einer einzelnen Tabelle auf 20 zu beschränken
• Aufteilung von TEXT/BLOB, die Verarbeitungsleistung des TEXT-Typs ist viel geringer als die von VARCHAR, wodurch die Generierung temporärer Festplattentabellen erzwungen wird und mehr Platz verschwendet wird.

Das obige ist der detaillierte Inhalt vonSo entwerfen Sie eine MySQL-Hochleistungstabelle. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!