Speicheranforderungen für MySQL-Datentypen und Auswahl des richtigen Typs

1. Speicheranforderungen für Spaltentypen

Listet die Speicheranforderungen jedes von MySQL unterstützten Spaltentyps nach Kategorie auf.

Die maximale Größe einer Zeile in einer MyISAM-Tabelle beträgt 65.534 Bytes. Jede BLOB- und TEXT-Spalte macht davon nur 5 bis 9 Byte aus.

Wenn die MyISAM-Tabelle Spaltentypen variabler Länge enthält, ist das Datensatzformat ebenfalls längenvariabel. Beim Erstellen einer Tabelle kann MySQL unter bestimmten Bedingungen eine Spalte von einem Typ mit variabler Länge in einen Typ mit fester Länge oder umgekehrt ändern.

Speicheranforderungen für numerische Typen

Speicheranforderungen für Spaltentypen

TINYINT 1 Byte

SMALLINT 2 Bytes

MEDIUMINT 3 Bytes

INT, INTEGER 4 Bytes

BIGINT 8 Bytes

FLOAT(p) Wenn 0 <= p <= 24 ist 4 Bytes, wenn 25 <= p <= 53 ist 8 Bytes

FLOAT 4 Bytes

DOUBLE [PRECISION], item REAL 8 Bytes

DECIMAL(M,D), NUMERIC(M,D) Variable Länge; siehe Diskussion unten

BIT(M) Über (M 7)/8 Bytes

Die Speicheranforderungen für DEZIMAL (und NUMERISCH) sind versionenspezifisch:

verwendet zum Komprimieren das Binärformat 9 Dezimalzahlen (basierend auf 10) in 4 Bytes, um den DECIMAL-Spaltenwert darzustellen. Die Speicherung der ganzzahligen und gebrochenen Teile jedes Werts wird separat festgelegt. Jedes Vielfache von 9 Ziffern erfordert 4 Bytes und die „verbleibenden“ Bits benötigen einen Teil der 4 Bytes. Die folgende Tabelle gibt die Speicheranforderungen für überschüssige Bits an:

Verbleibende Bytes

Anzahl der Bits

0 0

1 1

2 1  

3 2  

4 2  

5 3  

6 3  

7 4

8 4

9 4

Speicheranforderungen für Datums- und Uhrzeittypen

Spaltentyp Speicheranforderungen

DATE 3 Bytes

DATETIME 8 Bytes

TIMESTAMP 4 Bytes

TIME 3 Bytes

YEAR 1 Byte

Speicheranforderungen für String-Typen

Speicheranforderungen für Spaltentypen

CHAR(M) M Bytes, 0 <= M <= 255

VARCHAR(M) L 1 Byte , wobei L <= M und 0 <= M <= 65535 (siehe Hinweis unten)

BINARY(M) M Bytes, 0 <= M <= 255

VARBINARY(M) L 1 Byte, wobei L <= M und 0 <= M <= 255

TINYBLOB, TINYTEXT L 1 Byte, wobei L < 28

BLOB, TEXT L 2 Bytes, wobei L < 216

MEDIUMBLOB, MEDIUMTEXT L 3 Bytes, wobei L < 224

LONGBLOB, LONGTEXT L 4 Bytes, wobei L < 232

ENUM('value1','value2',...) 1 oder 2 Bytes, je nach Anzahl der Aufzählungswerte (bis zu 65.535 Werte)

SET('value1','value2',...) 1, 2, 3, 4 oder 8 Bytes, je nach die Anzahl der Mengenmitglieder (bis zu 64 Mitglieder)

VARCHAR-, BLOB- und TEXT-Klassen sind Typen variabler Länge. Der Speicherbedarf jedes Typs hängt von der tatsächlichen Länge des Spaltenwerts ab (in der vorherigen Tabelle mit L gekennzeichnet), nicht von der maximal möglichen Größe des Typs. Beispielsweise kann eine VARCHAR(10)-Spalte eine Zeichenfolge mit einer maximalen Länge von 10 enthalten. Der tatsächliche Speicherbedarf ist die Länge der Zeichenfolge (L) plus ein Byte, das die Länge der Zeichenfolge aufzeichnet. Für die Zeichenfolge „abcd“ ist L 4 und die Speicherung erfordert 5 Bytes.

Für die Typen CHAR, VARCHAR und TEXT sollten die Werte L und M in der vorherigen Tabelle als Anzahl der Zeichen interpretiert werden, und die Länge dieser Typen in der Spaltendefinition stellt die Anzahl der Zeichen dar. Um beispielsweise einen TINYTEXT-Wert zu speichern, sind L Zeichen 1 Byte erforderlich.

Um die Anzahl der Bytes zu berechnen, die zum Speichern des Werts einer bestimmten CHAR-, VARCHAR- oder TEXT-Spalte verwendet werden, müssen Sie den von der Spalte verwendeten Zeichensatz berücksichtigen. Im konkreten Fall müssen Sie bei der Arbeit mit Unicode bedenken, dass alle Unicode-Zeichen die gleiche Anzahl an Bytes verwenden.

Hinweis: Die effektive maximale Länge einer VARCHAR-Spalte beträgt 65.532 Zeichen.

Die NDBCLUSTER-Engine unterstützt nur Spalten mit fester Breite. Das bedeutet, dass sich VARCHAR-Spalten in Tabellen im MySQL-Cluster wie Typ-CHAR-Spalten verhalten (außer dass jeder Datensatz noch über ein zusätzliches Byte an Speicherplatz verfügt). Beispielsweise belegt in einer Cluster-Tabelle jeder Datensatz in einer als VARCHAR(100) deklarierten Spalte beim Speichern 101 Bytes, unabhängig von der Länge der Zeichenfolge im tatsächlich gespeicherten Datensatz.

BLOB- und TEXT-Klassen erfordern 1, 2, 3 oder 4 Bytes, um die Länge des Spaltenwerts aufzuzeichnen, abhängig von der maximal möglichen Länge der Klasse.

In der NDB-Cluster-Speicher-Engine ist die Implementierung der TEXT- und BLOB-Spalten unterschiedlich, wobei jeder Datensatz in der TEXT-Spalte aus zwei separaten Teilen besteht. Eine davon hat eine feste Größe (256 Byte) und wird tatsächlich in der Originaltabelle gespeichert. Die andere umfasst alle Daten über 256 Byte, die in einer impliziten Tabelle gespeichert sind. Datensätze in der zweiten Tabelle sind immer 2.000 Byte lang. Dies bedeutet, dass bei Größe<= 256 die Größe der TEXT-Spalte 256 beträgt (wobei Größe die Größe des Datensatzes darstellt); andernfalls beträgt die Größe 256 Größe (2000–(Größe–256) 00).

Die Größe eines ENUM-Objekts wird durch die Anzahl der verschiedenen Enumerationswerte bestimmt. Die Aufzählung verwendet ein Byte und kann 255 mögliche Werte haben. Wenn der Aufzählungswert zwischen 256 und 65.535 liegt, werden zwei Bytes verwendet.

Die Größe des SET-Objekts wird durch die Anzahl der verschiedenen Set-Mitglieder bestimmt. Wenn die festgelegte Größe N ist, belegt das Objekt (N 7)/8 Bytes, gerundet auf 1, 2, 3, 4 oder 8 Bytes. Ein SET kann bis zu 64 Mitglieder haben.

2. Wählen Sie den richtigen Datentyp

Um die Speicherung zu optimieren, sollte in jedem Fall der genaueste Typ verwendet werden. Wenn die Spaltenwerte beispielsweise zwischen 1 und 99999 liegen, ist MEDIUMINT UNSIGNED ein guter Typ, wenn Sie Ganzzahlen verwenden. Dieser Typ benötigt von allen Typen, die den Spaltenwert darstellen können, den geringsten Speicher.

Führt alle grundlegenden Berechnungen ( , -, *, /) für die DECIMAL-Spalte mit einer Genauigkeit von 65 Dezimalstellen durch (basierend auf 10).

Verwenden Sie Operationen mit doppelter Genauigkeit, um DEZIMAL-Werte zu berechnen. Wenn die Genauigkeit nicht zu wichtig ist oder die Geschwindigkeit höchste Priorität hat, ist der Typ DOUBLE ausreichend. Um eine hohe Präzision zu erreichen, kann eine Konvertierung in in BIGINT gespeicherte Festkommatypen durchgeführt werden. Dadurch können alle Berechnungen mit 64-Bit-Ganzzahlen durchgeführt und die Ergebnisse bei Bedarf wieder in Gleitkommawerte umgewandelt werden.

3. Verwenden Sie Spaltentypen von anderen Datenbank-Engines

Um von anderen Anbietern geschriebenen SQL-Ausführungscode zu verwenden, ordnet MySQL Spaltentypen zu, wie in der folgenden Tabelle gezeigt. Diese Zuordnungen erleichtern den Import von Tabellendefinitionen aus anderen Datenbank-Engines in MySQL:

Andere Verkäufertypen MySQL-Typen

BOOL, TINYINT

BOOLEAN TINYINT

CHAR VARYING(M) VARCHAR(M)

DEC DECIMAL

FIXED DECIMAL

FLOAT4 FLOAT

FLO AT8 DOUBLE

INT1 TINYINT

INT2 SMALLINT

INT3 MEDIUMINT

INT4 INT

INT8 BIGINT

LONG VARBINARY MEDIUMBLOB

LONG VARCHAR MEDIUMTEXT

LONG MEDIUMTEXT

MIDDLEINT MEDIUMINT

NUMERIC DECIMAL

Ordnen Sie den Spaltentyp beim Erstellen der Tabelle zu und verwerfen Sie dann die ursprüngliche Typdefinition. Wenn Sie eine Tabelle mit dem Typ eines anderen Anbieters erstellen und dann die Anweisung DESCRIBE tbl_name ausführen, verwendet MySQL den entsprechenden MySQL-Typ, um die Struktur der Tabelle zu melden. Zum Beispiel:

mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG, d NUMERIC);Query OK, 0 rows affected (0.08 sec);
mysql> DESCRIBE t;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | tinyint(1)    | YES  |     | NULL    |       |
| b     | double        | YES  |     | NULL    |       |
| c     | mediumtext    | YES  |     | NULL    |       |
| d     | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)