11.7 数据类型存储要求
磁盘上 table 数据的存储要求取决于几个因素。不同的存储引擎 table 示数据类型,并以不同的方式存储原始数据。table 数据可能会被压缩,无论是针对列还是整行,都会使 table 或列的存储需求的计算复杂化。
尽管磁盘上的存储布局有所不同,但内部 MySQL API 可以通信和交换有关 table 行的信息,但使用的一致性数据结构适用于所有存储引擎。
本节包含有关 MySQL 支持的每种数据类型的存储要求的准则和信息,包括使用固定大小 table 示形式的数据类型的存储引擎的内部格式和大小。信息按类别或存储引擎列出。
table 的内部 table 示形式的最大行大小为 65,535 字节,即使存储引擎能够支持更大的行也是如此。此图不包括BLOB或TEXT列,它们仅占该大小的 9 到 12 个字节。对于BLOB和TEXT数据,该信息内部存储在与行缓冲区不同的内存区域中。不同的存储引擎根据它们用于处理相应类型的方法,以不同的方式处理此数据的分配和存储。有关更多信息,请参见第 15 章,备用存储引擎和第 8.4.7 节“table 列数和行大小的限制”。
InnoDBtable 存储要求
有关InnoDB
个 table 的存储要求的信息,请参见第 14.11 节“ InnoDB 行格式”。
NDBtable 存储要求
Important
NDB个 table 使用 4 字节对齐;所有NDB数据存储均以 4 字节的倍数完成。因此,通常需要 15 个字节的列值在NDBtable 中需要 16 个字节。例如,在NDBtable 中,由于对齐因子,TINYINT,SMALLINT,MEDIUMINT和INTEGER(INT)列类型每个记录均需要 4 字节存储。
每个BIT(M)
列占用* M
位存储空间。尽管单独的BIT列不是* 4 字节对齐的,但是NDB为BIT
列所需的前 1-32 位保留每行 4 字节(32 位),然后为第 33-64 位保留另外 4 个字节,依此类推。
尽管NULL
本身不需要任何存储空间,但如果 table 定义包含定义为NULL
的列(最多 32 NULL
列),则NDB每行保留 4 个字节。 (如果 NDB 群集 table 定义的列超过 32 NULL
列,最多 64 NULL
列,则每行保留 8 个字节.)
每个使用NDB存储引擎的 table 都需要一个主键。如果您未定义主键,则NDB将创建“隐藏”主键。该隐藏的主键每个 table 记录消耗 31-35 个字节。
您可以使用ndb_size.pl Perl 脚本来估计NDB的存储需求。它连接到当前的 MySQL 数据库(不是 NDB 群集),并创建一个有关数据库使用NDB存储引擎所需空间的报告。有关更多信息,请参见第 21.4.29 节,“ ndb_size.pl — NDBCLUSTER 大小需求估计器”。
数字类型存储要求
Data Type | Storage Required |
---|---|
TINYINT | 1 byte |
SMALLINT | 2 bytes |
MEDIUMINT | 3 bytes |
INT, INTEGER | 4 bytes |
BIGINT | 8 bytes |
FLOAT(p) |
4 字节,如果 0 <= * p * <= 24,则 8 字节,如果 25 <= * p * <= 53 |
FLOAT | 4 bytes |
DOUBLE [PRECISION] , REAL |
8 bytes |
DECIMAL(M,D) , NUMERIC(M,D) |
变化;见下面的讨论 |
BIT(M) |
大约(* M * 7)/ 8 字节 |
DECIMAL(和NUMERIC)列的值使用二进制格式 table 示,该格式将九个十进制数字(以 10 为基数)包装到四个字节中。每个值的整数和小数部分的存储分别确定。九个数字的每个倍数需要四个字节,而“剩余”数字需要四个字节的一部分。下 table 列出了多余数字所需的存储空间。
Leftover Digits | 字节数 |
---|---|
0 | 0 |
1 | 1 |
2 | 1 |
3 | 2 |
4 | 2 |
5 | 3 |
6 | 3 |
7 | 4 |
8 | 4 |
日期和时间类型存储要求
对于TIME,DATETIME和TIMESTAMP列,在 MySQL 5.6.4 之前创建的 table 所需的存储与从 5.6.4 以后创建的 table 不同。这是由于 5.6.4 中的更改,允许这些类型具有小数部分,这需要从 0 到 3 个字节。
Data Type | MySQL 5.6.4 之前需要存储 | 从 MySQL 5.6.4 开始需要存储 |
---|---|---|
YEAR | 1 byte | 1 byte |
DATE | 3 bytes | 3 bytes |
TIME | 3 bytes | 3 字节小数秒存储 |
DATETIME | 8 bytes | 5 字节小数秒存储 |
TIMESTAMP | 4 bytes | 4 字节小数秒存储 |
从 MySQL 5.6.4 开始,YEAR和DATE的存储保持不变。但是,TIME,DATETIME和TIMESTAMPtable 示不同。 DATETIME的打包效率更高,非小数部分需要 5 个字节,而不是 8 个字节,并且这三个部分的小数部分都需要 0 到 3 个字节,具体取决于存储值的小数秒精度。
分数秒精度 | Storage Required |
---|---|
0 | 0 bytes |
1, 2 | 1 byte |
3, 4 | 2 bytes |
5, 6 | 3 bytes |
例如,TIME(0),TIME(2),TIME(4)和TIME(6)分别使用 3、4、5 和 6 个字节。 TIME和TIME(0)是等效的,并且需要相同的存储空间。
有关时间值的内部 table 示的详细信息,请参见MySQL 内部:重要的算法和结构。
字符串类型存储要求
在下 table 中,* M
*table 示非二进制字符串类型的声明的列长度,以字符 table 示,对于二进制字符串类型,以字节 table 示。 * L
*table 示给定字符串值的实际长度(以字节为单位)。
Data Type | Storage Required |
---|---|
CHAR(M) |
紧凑的 InnoDB 行格式系列优化了可变长度字符集的存储。参见COMPACT 行格式存储 Feature。否则,* M × w 字节,<= M <= 255,其中 w *是字符集中最大长度字符所需的字节数。 |
BINARY(M) |
* M *字节,0 <= M <= 255 |
VARCHAR(M) , VARBINARY(M) |
* L * 1 个字节(如果列值需要 0-255 个字节),* L * 2 个字节(如果值可能需要超过 255 个字节) |
TINYBLOB, TINYTEXT | * L * 1 个字节,其中* L * <28 |
BLOB, TEXT | * L * 2 个字节,其中* L * <216 |
MEDIUMBLOB, MEDIUMTEXT | * L * 3 个字节,其中* L * <224 |
LONGBLOB, LONGTEXT | * L * 4 字节,其中* L * <232 |
ENUM('value1','value2',...) |
1 或 2 个字节,具体取决于枚举值的数量(最大 65,535 个值) |
SET('value1','value2',...) |
1,2、3、4 或 8 个字节,具体取决于集合成员的数量(最多 64 个成员) |
可变长度字符串类型使用长度前缀加数据存储。长度前缀需要一到四个字节,具体取决于数据类型,并且前缀的值为* L
(字符串的字节长度)。例如,存储MEDIUMTEXT值需要 L
*个字节来存储值加上三个字节来存储值的长度。
要计算用于存储特定的CHAR,VARCHAR或TEXT列值的字节数,必须考虑用于该列的字符集以及该值是否包含多字节字符。特别是在使用utf8
Unicode 字符集时,必须记住,并非所有字符都使用相同数量的字节。 utf8mb3
和utf8mb4
字符集每个字符分别最多需要三个和四个字节。有关用于utf8mb3
或utf8mb4
字符的不同类别的存储的详细信息,请参见第 10.9 节“ Unicode 支持”。
VARCHAR,VARBINARY以及BLOB和TEXT类型是可变长度类型。对于每种存储需求,取决于以下因素:
-
列的实际长度值
-
列的最大可能长度
-
用于该列的字符集,因为某些字符集包含多字节字符
例如,VARCHAR(255)
列可以容纳最大长度为 255 个字符的字符串。假设该列使用latin1
字符集(每个字符一个字节),则实际所需的存储空间是字符串的长度(* L
),再加上一个字节来记录字符串的长度。对于字符串'abcd'
, L
*为 4,存储要求为 5 个字节。如果改为声明同一列使用ucs2
双字节字符集,则存储要求为 10 个字节:'abcd'
的长度为 8 个字节,并且该列需要两个字节来存储长度,因为最大长度大于 255(最大到 510 个字节)。
可以存储在VARCHAR或VARBINARY列中的有效最大字节数受所有列共享的最大行大小 65,535 字节的限制。对于存储多字节字符的VARCHAR列,字符的有效最大数量要少。例如,utf8mb3
个字符每个字符最多需要三个字节,因此使用utf8mb3
字符集的VARCHAR列可以声明为最多 21,844 个字符。参见第 8.4.7 节“table 列数和行大小的限制”。
InnoDB
将长度大于或等于 768 字节的固定长度字段编码为可变长度字段,可以将其存储在页面外。例如,如果字符集的最大字节长度大于 3,则CHAR(255)
列可以超过 768 个字节,就像utf8mb4
一样。
NDB存储引擎支持可变宽度的列。这意味着 NDB 群集 table 中的VARCHAR列所需的存储量与任何其他存储引擎相同,但这些值是 4 字节对齐的。因此,使用latin1
字符集存储在VARCHAR(50)
列中的字符串'abcd'
需要 8 个字节(而不是MyISAM
table 中相同列值的 5 个字节)。
TEXT和BLOB列在NDB存储引擎中的实现方式有所不同,其中TEXT列中的每一行都由两个独立的部分组成。其中之一具有固定大小(256 字节),并且实际上存储在原始 table 中。另一个包含超过 256 个字节的任何数据,这些数据存储在隐藏 table 中。第二个 table 中的行始终为 2000 个字节长。这意味着,如果* size
* <= 256(其中* size
*table 示行的大小),则TEXT列的大小为 256.否则,大小为 256 * size
(2000×( size
*-256)%2000)。
ENUM对象的大小由不同的枚举值的数量确定。一个字节用于最多 255 个可能值的枚举。两个字节用于可能值介于 256 和 65,535 之间的枚举。参见第 11.3.5 节“ ENUM 类型”。
SET对象的大小由不同集合成员的数量确定。如果设置的大小为* N
*,则对象占用(N+7)/8
字节,四舍五入为 1、2、3、4 或 8 个字节。 SET最多可以有 64 个成员。参见第 11.3.6 节“ SET 类型”。
空间类型存储要求
MySQL 使用 4 个字节存储几何值,以指示 SRID,后跟该值的 WKBtable 示。 LENGTH()函数返回值存储所需的字节空间。
有关 WKB 和空间值的内部存储格式的说明,请参见第 11.4.3 节“支持的空间数据格式”。
JSON 存储要求
通常,JSON列的存储要求与LONGBLOB
或LONGTEXT
列的存储要求大致相同;也就是说,JSON 文档占用的空间与存储在这些类型之一的列中的文档字符串 table 示形式所使用的空间大致相同。但是,二进制编码会产生开销,其中包括存储在 JSON 文档中的各个值的二进制数据,包括查找所需的元数据和字典。例如,存储在 JSON 文档中的字符串需要 4 到 10 个字节的额外存储空间,具体取决于字符串的长度以及存储该字符串的对象或数组的大小。
另外,MySQL 对存储在JSON
列中的任何 JSON 文档的大小施加了限制,以使其不能大于max_allowed_packet的值。