[MySQL] MySQL-Datentypoptimierung

Wählen Sie optimierte Datentypen

MySQL unterstützt viele Datentypen, und die Auswahl des richtigen Datentyps ist entscheidend für das Erreichen einer hohen Leistung. Unabhängig davon, welche Art von Daten Sie speichern, finden Sie hier einige Grundsätze, die Ihnen dabei helfen, bessere Entscheidungen zu treffen.

Kleiner ist normalerweise besser

Im Allgemeinen sollten Sie versuchen, den kleinsten Datentyp zu verwenden, der Daten korrekt speichern kann (z. B. müssen Sie nur 0-200 speichern, Tinyint ohne Vorzeichen ist 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.

Einfach ist gut

Operationen an einfachen Datentypen erfordern im Allgemeinen weniger CPU-Zyklen. Ganzzahloperationen sind beispielsweise kostengünstiger als Zeichenoperationen, da Zeichensätze und Kollatierungen (Kollatierungen) Zeichenfolgenvergleiche komplexer machen als Ganzzahlvergleiche. Hier sind zwei Beispiele: Zum einen sollten die in MySQL integrierten Typen (z. B. Datum, Uhrzeit, Datum/Uhrzeit) anstelle von Zeichenfolgen zum Speichern von Datum und Uhrzeit verwendet werden, zum anderen sollten Ganzzahlen zum Speichern von IP-Adressen verwendet werden.

Vermeiden Sie die Verwendung von NULL

Viele Tabellen enthalten NULL-fähige Spalten, auch wenn die Anwendung NULL nicht speichern muss, da NULL das Standardattribut der Spalte ist. Normalerweise ist es am besten, Spalten als NOT NULL anzugeben, es sei denn, Sie müssen wirklich NULL-Werte speichern.

Es ist für MySQL schwieriger zu optimieren, wenn die Abfrage NULL-fähige Spalten enthält, da NULL-fähige Spalten Indizes, Indexstatistiken und Wertvergleiche komplexer machen. Spalten, die NULL sein können, benötigen mehr Speicherplatz und erfordern eine spezielle Behandlung in MySQL. Wenn eine NULL-fähige Spalte indiziert wird, benötigt 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.

Normalerweise ist die Leistungsverbesserung, die durch die Änderung des NULL-Werts in NOT NULL erzielt wird, relativ gering, sodass (beim Optimieren) keine Notwendigkeit besteht, diese Situation zuerst im vorhandenen Schema zu finden und zu ändern, es sei denn, dies wird festgestellt Probleme verursachen. Wenn Sie jedoch vorhaben, einen Index für eine Spalte zu erstellen, sollten Sie vermeiden, die Spalte so zu gestalten, dass sie NULL-Werte zulässt.

Natürlich gibt es Ausnahmen. Erwähnenswert ist beispielsweise, dass InnoDB ein separates Bit zum Speichern von NULL-Werten verwendet, sodass es eine gute Leistung für spärliche Daten bietet (die meisten Werte sind NULL und nur wenige). Zeilen sind Nicht-NULL-Werte). Dies gilt jedoch nicht für MyISAM.

---

Ganzzahltypen

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. Ihr Speicherbereich reicht von -2 hoch (N-1) bis 2 hoch (N-1) -1, wobei N die Anzahl der Bits im Speicherplatz ist.

Der Integer-Typ verfügt über das optionale UNSIGNED-Attribut, was bedeutet, dass negative Werte nicht zulässig sind, was die Obergrenze positiver Zahlen ungefähr verdoppeln kann. Der Bereich, den TINYINT UNSIGNED speichern kann, ist beispielsweise 0-. 255 und der Speicherbereich von TINYINT beträgt -128~127.

MySQL kann eine Breite für Ganzzahltypen wie INT(11) angeben. Dies ist für die meisten Anwendungen bedeutungslos: Es schränkt den zulässigen Wertebereich nicht ein Befehlszeilen-Client), der zur Anzeige der Anzahl der Zeichen verwendet wird. Für die Speicherung sind INT(1) und INT(20) gleich.

Realer Typ

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. MySQL unterstützt sowohl präzise als auch ungenaue Typen.

Die Typen FLOAT und DOUBLE unterstützen Näherungsberechnungen mit Standard-Gleitkomma-Arithmetik. Wenn Sie wissen möchten, wie Gleitkommaoperationen berechnet werden, müssen Sie die spezifische Implementierung von Gleitkommazahlen auf der von Ihnen verwendeten Plattform untersuchen.

Der Typ DECIMAL wird zum Speichern präziser Dezimalzahlen verwendet. Da die CPU jedoch keine direkte Berechnung von DECIMAL unterstützt, implementiert der MySQL-Server in MySQL5.0 und späteren Versionen selbst die hochpräzise Berechnung von DECIMAL. Relativ gesehen ist dies langsamer als die CPU, die native Gleitkommaoperationen direkt unterstützt.

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.

Gleitkommatypen benötigen normalerweise weniger Platz als DECIMAL, wenn sie denselben Wertebereich speichern. FLOAT verwendet 4 Byte Speicher. DOUBLE belegt 8 Bytes und hat eine höhere Präzision und einen größeren Bereich als FLOAT.

Aufgrund des zusätzlichen Platz- und Rechenaufwands sollten Sie versuchen, DECIMAL nur zu verwenden, wenn Sie präzise Berechnungen mit Dezimalzahlen durchführen – beispielsweise beim Speichern von Finanzdaten. Wenn die Datenmenge jedoch relativ groß ist, können Sie BIGINT anstelle von DECIMAL verwenden und die zu speichernde Währungseinheit entsprechend der Anzahl der Dezimalstellen mit dem entsprechenden Vielfachen multiplizieren. Angenommen, Sie möchten Finanzdaten mit einer Genauigkeit von einem Zehntausendstel speichern, können Sie alle Beträge mit 1 Million multiplizieren und die Ergebnisse dann in BIGINT speichern. Dadurch können die Probleme ungenauer Gleitkomma-Speicherberechnungen und hohe Kosten für genaue DECIMAL-Berechnungen vermieden werden.

String-Typ

Die folgende Beschreibung geht davon aus, dass die verwendete Speicher-Engine InnoDB/oder MyISAM ist. Wenn es von diesen beiden Speicher-Engines nicht unterstützt wird, lesen Sie bitte die Dokumentation der verwendeten Speicher-Engine.

VARCHAR und CHAR

VARCHAR: Es ist platzsparender als Typen mit fester Länge, da es nur den erforderlichen Platz belegt. VARCHAR spart Platz und verbessert somit auch die Leistung. Da die Länge der Zeile jedoch variabel ist, kann es sein, dass die Zeile beim UPDATE länger als das Original wird, was zusätzliche Arbeit erfordert.

In den folgenden Situationen ist die Verwendung von VARCHAR sinnvoll: Die maximale Länge der Zeichenfolge ist viel größer als die durchschnittliche Länge der Spalte, sodass Fragmentierung kein Problem darstellt -8 wird verwendet und jedes Zeichen verwendet eine andere Anzahl von Bytes.

In den Versionen 5.0 oder höher behält MySQL beim Speichern und Abrufen nachfolgende Leerzeichen bei. InnoDB ist flexibler. Es kann lange VARCHAR-Werte als BLOB speichern.

CHAR: Beim Speichern von CHAR-Werten löscht MySQL alle nachfolgenden Leerzeichen. Der CHAR-Typ mit fester Länge ist weniger anfällig für Fragmentierung, und bei sehr kurzen Spalten ist CHAR auch effizienter im Speicherplatz als VARCHAR, das über ein oder zwei zusätzliche Bytes an Datensatzlänge verfügt. CHAR eignet sich zum Speichern sehr kurzer Zeichenfolgen oder wenn alle Werte nahezu die gleiche Länge haben. Beispiel: CHAR eignet sich sehr gut zum Speichern des MD5-Werts eines Passworts, da es sich um einen Wert fester Länge handelt. CHAR wird nach Bedarf mit Leerzeichen aufgefüllt, um den Vergleich zu erleichtern.

Ähnliche Typen wie CHAR und VARCHAR sind BINARY und VARBINARY, die Binärzeichenfolgen speichern. Binäre Zeichenfolgen speichern Bytecodes anstelle von Zeichen.

Der Vorteil des binären Vergleichs liegt nicht nur in der Berücksichtigung der Groß-/Kleinschreibung. MySQL vergleicht BINARY-Strings Byte für Byte und vergleicht sie basierend auf dem Wert des Bytes. Daher sind Binärzahlen viel einfacher als Zeichen und daher schneller.

BLOB- und TEXT-Typen

BLOB- und TEXT-Typen: BLOB und TEXT sind beide Zeichenfolgendatentypen, die zum Speichern großer Datenmengen entwickelt wurden und im Binär- bzw. Zeichenmodus gespeichert werden. Wenn die BLOB- und TEXT-Werte zu groß sind, verwendet InnoDB einen dedizierten „externen“ Speicherbereich zur Speicherung. Der ursprüngliche Tabellenfeldspeicherzeiger zeigt auf den externen Speicherbereich.

MySQL sortiert BLOB- und TEXT-Spalten anders als andere Typen: Es sortiert nur die ersten max_sort_length Bytes der Spalte und nicht die gesamte Zeichenfolge. Wenn Sie nur einen kleinen Teil der ersten Zeichen sortieren müssen, können Sie die Konfiguration von max_sort_length reduzieren oder ORDER BY SUSTRING(column, length) verwenden.

MySQL kann weder die Zeichenfolgen voller Länge von BLOB- und TEXT-Spalten indizieren, noch kann es diese Indizes verwenden, um die Sortierung zu eliminieren.

Aufzählung (ENUM) anstelle des String-Typs verwenden

Sie können Aufzählung (ENUM) anstelle des String-Typs verwenden. Oft wird empfohlen, Aufzählungsspalten anstelle häufig verwendeter Zeichenfolgentypen zu verwenden.

(1) Die Aufzählungsspalte kann einige eindeutige Zeichenfolgen in einer vordefinierten Sammlung speichern.
(2) MySQL ist beim Speichern von Aufzählungen sehr kompakt und wird abhängig von der Anzahl der Listenwerte auf ein oder zwei Bytes komprimiert.
(3) MySQL speichert intern die Position jedes Werts in der Liste als Ganzzahl und speichert die „Nachschlagetabelle“ der Zuordnungsbeziehung „Zahlenzeichenfolge“ in der .frm-Datei der Tabelle.

Hinweis: Überraschend ist, dass die Enum-Felder nach intern gespeicherten Ganzzahlen und nicht nach definierten Zeichenfolgen sortiert sind.

Hinweis: Das Schlimmste an Aufzählungen ist, dass zum Hinzufügen oder Löschen von Zeichenfolgen ALTER TABLE verwendet werden muss. Verwenden Sie daher für eine Reihe von Zeichenfolgen, die sich in Zukunft ändern können, keine gute Idee es sei denn, Sie akzeptieren, dass Sie Elemente nur am Ende der Liste hinzufügen können.

Hinweis: Für Aufzählungsspalten entsteht ein gewisser Overhead, da MySQL jeden Aufzählungswert als Ganzzahl speichert und eine Suche durchführen muss, um ihn in eine Zeichenfolge umzuwandeln.

Datums- und Uhrzeittyp

Einzelheiten zu Datentypen und Verwendung finden Sie unter: http://www.php.cn/

Mysql verfügt über viele Typen, die speichern können Datums- und Zeitwerte wie JAHR und DATUM.

Die minimale Zeitgranularität, die MySQL speichern kann, beträgt Sekunden (MariaDB unterstützt Ereignistypen auf Mikrosekundenebene). Aber MySQL kann auch Ad-hoc-Operationen mit einer Granularität im Mikrosekundenbereich ausführen.

Meistens gibt es keine Alternativen zu dem Typ, sodass es keine Frage gibt, was die beste Wahl ist.

Die einzige Frage ist dann, was beim Speichern von Datum und Uhrzeit zu tun ist.

DATETIME

(1) Dieser Typ kann einen breiten Wertebereich von 1001 bis 9999 mit einer Genauigkeit von Sekunden speichern. (2) DATETIME kapselt Zeit und Datum in Ganzzahlen im Format YYYYMMDDHHMMSS, unabhängig von der Zeitzone. (3) DATETIME belegt 8 Byte Speicherplatz.

TIMESTAMP

(1) Der Typ TIMESTAMP speichert die Anzahl der Sekunden seit Mitternacht am 1. Januar 1970, was mit dem UNIX-Zeitstempel identisch ist. (2) TIMESTAMP benötigt nur 4 Byte Speicherplatz, daher ist sein Bereich viel kleiner als der von DATETIME. (3) Der von TIMESTAMP angezeigte Wert hängt von der Zeitzone ab.

Vergleich zwischen DATETIME und TIMESTAMP:

(1) Wenn der Wert der ersten TIMESTAMP-Spalte beim Einfügen nicht angegeben wird, setzt MySQL standardmäßig den Wert dieser Spalte auf die aktuelle Zeit. (Dies ist eine Funktion, die DATETIME nicht hat) (2) Beim Einfügen einer Zeile mit Datensätzen aktualisiert MySQL standardmäßig auch den Wert der ersten TIMESTAMP-Spalte. (3) Die TIMESTAMP-Spalte ist standardmäßig NOT NULL, was sich von anderen Datentypen unterscheidet.

Zusammenfassung

(1) Zusätzlich zum besonderen Verhalten sollte TIMESTAMP nach Möglichkeit generell verwendet werden, da es platzsparender als DATETIME ist. (2) Im Allgemeinen wird nicht empfohlen, UNIX-Zeitstempel als Ganzzahlwerte zu speichern. Das Speichern von Zeitstempelformaten mit Ganzzahlen ist normalerweise unpraktisch. (3) Wenn Sie Datums- und Uhrzeitwerte mit einer kleineren Granularität als Sekunden speichern müssen, können Sie den Typ BIGINT verwenden, um Zeitstempel auf Mikrosekundenebene zu speichern, oder DOUBLE, um den Dezimalteil nach Sekunden zu speichern. Sie können auch MariaDB verwenden anstelle von MySQL.

Bit-Datentypen

MySQL verfügt über einige Speichertypen, die kompakte Bits zum Speichern von Daten verwenden. Alle diese Bittypen sind unabhängig vom zugrunde liegenden Speicherformat und der zugrunde liegenden Verarbeitung technisch gesehen String-Typen.

BIT

Die BIT-Spalte kann verwendet werden, um einen oder mehrere Wahr/Falsch-Werte in einer Spalte zu speichern. BIT(1) definiert ein Feld, das ein einzelnes Bit enthält, BIT(2) speichert 2 Bits und so weiter. Die maximale Länge einer BIT-Spalte beträgt 64 Bit.

Wenn Sie einen wahren/falschen Wert auf wenig Speicherplatz speichern möchten, besteht eine andere Methode darin, eine CHAR(0)-Spalte zu erstellen, die null sein kann. Diese Spalte kann einen Nullwert (NULL) oder eine Zeichenfolge der Länge Null (leere Zeichenfolge) enthalten.

SET

Wenn Sie viele Wahr/Falsch-Werte speichern müssen, können Sie erwägen, diese Spalten in einem SET-Datentyp zusammenzuführen, der intern von MySQL als Satz gepackter Bits dargestellt wird. Dadurch wird der Speicherplatz effektiv genutzt, und MySQL verfügt über Funktionen wie FIND_IN_SET() und FIELD() zur einfachen Verwendung in Abfragen. Der Hauptnachteil besteht darin, dass das Ändern von Spaltendefinitionen teuer ist: ALTER TABLE ist erforderlich, was bei großen Tabellen ein sehr teurer Vorgang ist. Generell ist es auch nicht möglich, Indizes auf SET-Spalten zu durchsuchen.

Eine Alternative zu SET besteht darin, eine Reihe von Bits mit einer Ganzzahl zu umschließen. Beispielsweise können 8 Bits in ein TINYINT gepackt und in bitweisen Operationen verwendet werden. Sie können dies vereinfachen, indem Sie für jedes Bit in Ihrer Anwendung benannte Konstanten definieren.

Im Vergleich zu SET besteht der Hauptvorteil dieser Methode darin, dass Sie den durch das Feld dargestellten „Aufzählungswert“ ändern können, ohne ALTER TABLE zu verwenden. Der Nachteil besteht darin, dass die Abfrageanweisung schwieriger zu schreiben ist zu verstehen (wenn das 5. Bit gesetzt ist. Was bedeutet es, wenn ein Bit gesetzt ist?). Manche Menschen fühlen sich mit diesem Ansatz sehr wohl, andere wiederum nicht. Ob man diese Technik anwendet oder nicht, hängt also von den persönlichen Vorlieben ab.

Wählen Sie den Bezeichner

Es ist sehr wichtig, den geeigneten Datentyp für den Bezeichner (Identitätsspalte) auszuwählen.

Im Allgemeinen ist es wahrscheinlicher, die Identitätsspalte zum Vergleich mit anderen Werten zu verwenden oder andere Spalten über die Identitätsspalte zu finden.

Bei der Auswahl des Typs der Identitätsspalte müssen Sie nicht nur den Speichertyp berücksichtigen, sondern auch, wie MySQL Berechnungen und Vergleiche für diesen Typ durchführt.

Sobald Sie einen Typ ausgewählt haben, stellen Sie sicher, dass Sie in allen zugehörigen Tabellen denselben Typ verwenden.

Unter der Voraussetzung, dass die Bereichsanforderungen des Werts erfüllt werden können und Raum für zukünftiges Wachstum reserviert ist, sollte der kleinste Datentyp ausgewählt werden.

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

• ENUM und SET sind die schlechtesten Optionen.

• Vermeiden Sie nach Möglichkeit die Verwendung von Zeichenfolgen als Identitätsspalten, da diese im Allgemeinen sehr viel Platz beanspruchen langsamer als numerische Klassen.

Spezielle Typdaten

Einige Datentypen stimmen nicht direkt mit integrierten Typen überein. Ein Beispiel sind Zeitstempel mit einer Genauigkeit von weniger als einer Sekunde.

Ein weiteres Beispiel ist, dass Menschen häufig VARCHAR(15) zum Speichern von IP-Adressen verwenden. Tatsächlich handelt es sich jedoch um vorzeichenlose 32-Bit-Ganzzahlen und nicht um Zeichenfolgen. Der Dezimalpunkt wird verwendet, um das Feld zur besseren Lesbarkeit in vier Segmente zu unterteilen. Daher sollten IP-Adressen als vorzeichenlose Ganzzahlen gespeichert werden. MySQL bietet die Funktionen INET_ATON() und INET_NTOA() zum Konvertieren zwischen diesen beiden Darstellungsmethoden.

Wählen Sie optimierte Datentypen

MySQL unterstützt viele Datentypen und die Auswahl des richtigen Datentyps ist entscheidend für das Erreichen einer hohen Leistung. Unabhängig davon, welche Art von Daten Sie speichern, finden Sie hier einige Grundsätze, die Ihnen dabei helfen, bessere Entscheidungen zu treffen.

Kleiner ist normalerweise besser

Im Allgemeinen sollten Sie versuchen, den kleinsten Datentyp zu verwenden, der Daten korrekt speichern kann (z. B. müssen Sie nur 0-200 speichern, Tinyint ohne Vorzeichen ist 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.

Einfach ist gut

Operationen an einfachen Datentypen erfordern im Allgemeinen weniger CPU-Zyklen. Beispielsweise sind Ganzzahloperationen kostengünstiger als Zeichenoperationen, da Zeichensätze und Kollatierungen (Kollatierungen) Zeichenfolgenvergleiche komplexer machen als Ganzzahlvergleiche. Hier sind zwei Beispiele: Zum einen sollten die in MySQL integrierten Typen (z. B. Datum, Uhrzeit, Datum/Uhrzeit) anstelle von Zeichenfolgen zum Speichern von Datum und Uhrzeit verwendet werden, zum anderen sollten Ganzzahlen zum Speichern von IP-Adressen verwendet werden.

Vermeiden Sie die Verwendung von NULL

Viele Tabellen enthalten NULL-fähige Spalten, auch wenn die Anwendung NULL nicht speichern muss, da die NULL-Fähigkeit das Standardattribut der Spalte ist. Normalerweise ist es am besten, Spalten als NOT NULL anzugeben, es sei denn, Sie müssen wirklich NULL-Werte speichern.

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM中甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为NULL的值改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有的schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建立索引，就应该避免设计成可为NULL的列。

当然也有例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据（大部分值为NULL，只有少数行为非NULL的值）有良好的空间效率。但这一点不适用于MyISAM。

---

整数类型

如果存储整数，可以使用这几种整数类型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分别使用8,16,24,32,64位存储空间。它们的存储范围从 -2的(N-1)次方 到 2的(N-1)次方-1，其中N为存储空间的位数。

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍，例如TINYINT UNSIGNED可以存储的范围是0-255，而TINYINT 的存储范围是-128~127。

MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：他不会限制值得合法范围，知识规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算时怎么计算的，则需要研究所使用的平台的浮点数的具体实现。

DECIMAL 类型用于存储精确的小数。但因为CPU不支持对DECIMAL的直接计算，所以在MySQL5.0及更高版本中，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，这比CPU直接支持原生浮点数运算要慢。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数点的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以100W，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

下面的描述假设使用的存储引擎是InnoDB/或者MyISAM。如果不是这两种存储引擎的，请参考所使用的存储引擎的文档。

VARCHAR und CHAR

VARCHAR: Es ist platzsparender als Typen mit fester Länge, da es nur den erforderlichen Platz belegt. VARCHAR spart Platz und verbessert somit auch die Leistung. Da die Länge der Zeile jedoch variabel ist, kann es sein, dass die Zeile beim UPDATE länger als das Original wird, was zusätzliche Arbeit erfordert.

In den folgenden Situationen ist die Verwendung von VARCHAR sinnvoll: Die maximale Länge der Zeichenfolge ist viel größer als die durchschnittliche Länge der Spalte, sodass Fragmentierung kein Problem darstellt -8 wird verwendet und jedes Zeichen verwendet eine andere Anzahl von Bytes.

In den Versionen 5.0 oder höher behält MySQL beim Speichern und Abrufen nachfolgende Leerzeichen bei. InnoDB ist flexibler. Es kann lange VARCHAR-Werte als BLOB speichern.

CHAR: Beim Speichern von CHAR-Werten löscht MySQL alle nachfolgenden Leerzeichen. Der CHAR-Typ mit fester Länge ist weniger anfällig für Fragmentierung, und bei sehr kurzen Spalten ist CHAR auch effizienter im Speicherplatz als VARCHAR, das über ein oder zwei zusätzliche Bytes an Datensatzlänge verfügt. CHAR eignet sich zum Speichern sehr kurzer Zeichenfolgen oder wenn alle Werte nahezu die gleiche Länge haben. Beispiel: CHAR eignet sich sehr gut zum Speichern des MD5-Werts eines Passworts, da es sich um einen Wert fester Länge handelt. CHAR wird nach Bedarf mit Leerzeichen aufgefüllt, um den Vergleich zu erleichtern.

Ähnliche Typen wie CHAR und VARCHAR sind BINARY und VARBINARY, die Binärzeichenfolgen speichern. Binäre Zeichenfolgen speichern Bytecodes anstelle von Zeichen.

Der Vorteil des binären Vergleichs liegt nicht nur in der Berücksichtigung der Groß-/Kleinschreibung. MySQL vergleicht BINARY-Strings Byte für Byte und vergleicht sie basierend auf dem Wert des Bytes. Daher sind Binärzahlen viel einfacher als Zeichen und daher schneller.

BLOB- und TEXT-Typen

BLOB- und TEXT-Typen: BLOB und TEXT sind beide Zeichenfolgendatentypen, die zum Speichern großer Datenmengen entwickelt wurden und im Binär- bzw. Zeichenmodus gespeichert werden. Wenn die BLOB- und TEXT-Werte zu groß sind, verwendet InnoDB einen dedizierten „externen“ Speicherbereich zur Speicherung. Der ursprüngliche Tabellenfeldspeicherzeiger zeigt auf den externen Speicherbereich.

MySQL sortiert BLOB- und TEXT-Spalten anders als andere Typen: Es sortiert nur die ersten max_sort_length Bytes der Spalte und nicht die gesamte Zeichenfolge. Wenn Sie nur einen kleinen Teil der ersten Zeichen sortieren müssen, können Sie die Konfiguration von max_sort_length reduzieren oder ORDER BY SUSTRING(column, length) verwenden.

MySQL kann weder die Zeichenfolgen voller Länge von BLOB- und TEXT-Spalten indizieren, noch kann es diese Indizes verwenden, um die Sortierung zu eliminieren.

Aufzählung (ENUM) anstelle des String-Typs verwenden

Sie können Aufzählung (ENUM) anstelle des String-Typs verwenden. Oft wird empfohlen, Aufzählungsspalten anstelle häufig verwendeter Zeichenfolgentypen zu verwenden.

(1) Die Aufzählungsspalte kann einige eindeutige Zeichenfolgen in einer vordefinierten Sammlung speichern.
(2) MySQL ist beim Speichern von Aufzählungen sehr kompakt und wird abhängig von der Anzahl der Listenwerte auf ein oder zwei Bytes komprimiert.
(3) MySQL speichert intern die Position jedes Werts in der Liste als Ganzzahl und speichert die „Nachschlagetabelle“ der Zuordnungsbeziehung „Zahlenzeichenfolge“ in der .frm-Datei der Tabelle.

Hinweis: Überraschend ist, dass die Enum-Felder nach intern gespeicherten Ganzzahlen und nicht nach definierten Zeichenfolgen sortiert sind.

Hinweis: Das Schlimmste an Aufzählungen ist, dass zum Hinzufügen oder Löschen von Zeichenfolgen ALTER TABLE verwendet werden muss. Verwenden Sie daher für eine Reihe von Zeichenfolgen, die sich in Zukunft ändern können, keine gute Idee es sei denn, Sie akzeptieren, dass Sie Elemente nur am Ende der Liste hinzufügen können.

Hinweis: Für Aufzählungsspalten entsteht ein gewisser Overhead, da MySQL jeden Aufzählungswert als Ganzzahl speichert und eine Suche durchführen muss, um ihn in eine Zeichenfolge umzuwandeln.

Datums- und Uhrzeittyp

Einzelheiten zu Datentypen und Verwendung finden Sie unter: http://www.php.cn/

Mysql verfügt über viele Typen, die speichern können Datums- und Zeitwerte wie JAHR und DATUM.

Die minimale Zeitgranularität, die MySQL speichern kann, beträgt Sekunden (MariaDB unterstützt Ereignistypen auf Mikrosekundenebene). Aber MySQL kann auch Ad-hoc-Operationen mit einer Granularität im Mikrosekundenbereich ausführen.

Meistens gibt es keine Alternativen zu dem Typ, sodass es keine Frage gibt, was die beste Wahl ist.

Die einzige Frage ist dann, was beim Speichern von Datum und Uhrzeit zu tun ist.

DATETIME

(1) Dieser Typ kann einen breiten Wertebereich von 1001 bis 9999 mit einer Genauigkeit von Sekunden speichern. (2) DATETIME kapselt Zeit und Datum in Ganzzahlen im Format YYYYMMDDHHMMSS, unabhängig von der Zeitzone. (3) DATETIME belegt 8 Byte Speicherplatz.

TIMESTAMP

(1) Der Typ TIMESTAMP speichert die Anzahl der Sekunden seit Mitternacht am 1. Januar 1970, was mit dem UNIX-Zeitstempel identisch ist. (2) TIMESTAMP benötigt nur 4 Byte Speicherplatz, daher ist sein Bereich viel kleiner als der von DATETIME. (3) Der von TIMESTAMP angezeigte Wert hängt von der Zeitzone ab.

Vergleich zwischen DATETIME und TIMESTAMP:

(1) Wenn der Wert der ersten TIMESTAMP-Spalte beim Einfügen nicht angegeben wird, setzt MySQL standardmäßig den Wert dieser Spalte auf die aktuelle Zeit. (Dies ist eine Funktion, die DATETIME nicht hat) (2) Beim Einfügen einer Zeile mit Datensätzen aktualisiert MySQL standardmäßig auch den Wert der ersten TIMESTAMP-Spalte. (3) Die TIMESTAMP-Spalte ist standardmäßig NOT NULL, was sich von anderen Datentypen unterscheidet.

Zusammenfassung

（1）除了特殊行为之外，通常也应该尽可能使用TIMESTAMP，因为它比DATETIME空间效率更高。 （2）一般来讲不建议把UNIX时间戳保存为整数值，这不会带来任何收益，用整数保存时间戳格式通常不方便处理。 （3）如果需呀存储比秒更小粒度的日期和时间值，可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分，也可以用MariaDB替代Mysql。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型的。

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义了一个包含单个位的字段，BIT(2)存储2个位，依次类推。BIT列的最大长度是64位。

如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值(NULL)或者长度为零的字符串(空字符串)。

SET

Wenn Sie viele Wahr/Falsch-Werte speichern müssen, sollten Sie erwägen, diese Spalten in einem SET-Datentyp zusammenzuführen, der intern von MySQL als Satz gepackter Bits dargestellt wird. Dadurch wird der Speicherplatz effektiv genutzt, und MySQL verfügt über Funktionen wie FIND_IN_SET() und FIELD() zur einfachen Verwendung in Abfragen. Der Hauptnachteil besteht darin, dass das Ändern von Spaltendefinitionen teuer ist: ALTER TABLE ist erforderlich, was bei großen Tabellen ein sehr teurer Vorgang ist. Generell ist es auch nicht möglich, Indizes auf SET-Spalten zu durchsuchen.

Eine Alternative zu SET besteht darin, eine Reihe von Bits mit einer Ganzzahl zu umschließen. Beispielsweise können 8 Bits in ein TINYINT gepackt und in bitweisen Operationen verwendet werden. Sie können dies vereinfachen, indem Sie für jedes Bit in Ihrer Anwendung benannte Konstanten definieren.

Im Vergleich zu SET besteht der Hauptvorteil dieser Methode darin, dass Sie den durch das Feld dargestellten „Aufzählungswert“ ändern können, ohne ALTER TABLE zu verwenden. Der Nachteil besteht darin, dass die Abfrageanweisung schwieriger zu schreiben ist zu verstehen (wenn das 5. Bit gesetzt ist. Was bedeutet es, wenn ein Bit gesetzt ist?). Manche Menschen fühlen sich mit diesem Ansatz sehr wohl, andere wiederum nicht. Ob man diese Technik anwendet oder nicht, hängt also von den persönlichen Vorlieben ab.

Wählen Sie den Bezeichner

Es ist sehr wichtig, den geeigneten Datentyp für den Bezeichner (Identitätsspalte) auszuwählen.

Im Allgemeinen ist es wahrscheinlicher, die Identitätsspalte zum Vergleich mit anderen Werten zu verwenden oder andere Spalten über die Identitätsspalte zu finden.

Bei der Auswahl des Typs der Identitätsspalte müssen Sie nicht nur den Speichertyp berücksichtigen, sondern auch, wie MySQL Berechnungen und Vergleiche für diesen Typ durchführt.

Sobald Sie einen Typ ausgewählt haben, stellen Sie sicher, dass Sie in allen zugehörigen Tabellen denselben Typ verwenden.

Unter der Voraussetzung, dass die Bereichsanforderungen des Werts erfüllt werden können und Raum für zukünftiges Wachstum reserviert ist, sollte der kleinste Datentyp ausgewählt werden.

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

• ENUM und SET sind die schlechtesten Optionen.

• Vermeiden Sie nach Möglichkeit die Verwendung von Zeichenfolgen als Identitätsspalten, da diese im Allgemeinen sehr viel Platz beanspruchen langsamer als numerische Klassen.

Spezielle Typdaten

Einige Datentypen stimmen nicht direkt mit integrierten Typen überein. Ein Beispiel sind Zeitstempel mit einer Genauigkeit von weniger als einer Sekunde.

Ein weiteres Beispiel ist, dass Menschen häufig VARCHAR(15) zum Speichern von IP-Adressen verwenden. Tatsächlich handelt es sich jedoch um vorzeichenlose 32-Bit-Ganzzahlen und nicht um Zeichenfolgen. Der Dezimalpunkt wird verwendet, um das Feld zur besseren Lesbarkeit in vier Segmente zu unterteilen. Daher sollten IP-Adressen als vorzeichenlose Ganzzahlen gespeichert werden. MySQL bietet die Funktionen INET_ATON() und INET_NTOA() zum Konvertieren zwischen diesen beiden Darstellungen.

Das Obige ist der Inhalt der [MySQL] MySQL-Datentypoptimierung. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!