MySQL数据类型 - 数据类型存储要求

数据库杂货铺 2021-04-12

1028

数据类型存储要求

表数据在磁盘上的存储需求取决于几个因素。不同的存储引擎以不同的方式表示数据类型和存储原始数据。对于一列或整行，表数据可能会被压缩，这会使表或列的存储需求计算复杂化。

尽管磁盘上的存储布局有所不同，但内部MySQL API（用于通信和交换表行的信息）使用了一个适用于所有存储引擎的一致的数据结构。

本节包括MySQL支持的每种数据类型的存储要求的指导原则和信息，包括对数据类型使用固定大小表示的存储引擎的内部格式和大小。信息按类别或存储引擎列出。

即使存储引擎能够支持更大的行，但表的内部表示形式的最大行大小为65535字节。这个数字不包括BLOB或TEXT列，它们只占这个大小的9到12个字节。对于BLOB和TEXT数据，信息在内部存储在与行缓冲区不同的内存区域中。不同的存储引擎根据它们各自处理相应类型的方法，以不同的方式处理这些数据的分配和存储。

NDB表存储要求

重要

NDB表使用4字节对齐；所有NDB数据存储都是以4字节的倍数进行的。因此，通常15个字节的列值在NDB表中需要16个字节。例如，在NDB表中，由于对齐因子的原因，TINYINT、SMALLINT、MEDIUMINT和INTEGER（INT）列类型每个记录都需要4字节的存储空间。

每个BIT(M)列占用M位的存储空间。尽管单个BIT列不是4字节对齐的，但是NDB为每行的BIT列所需的前1-32位保留4个字节（32位），然后为第33-64位保留另外4个字节，依此类推。

虽然NULL本身不需要任何存储空间，但如果表定义包含了任何允许NULL的列，则NDB每行保留4个字节，最多支持32个NULL列。（如果NDB群集表定义了32个以上的空列，最多64个空列，则每行保留8个字节。）

使用NDB存储引擎的每个表都需要一个主键；如果不定义主键，NDB会创建一个"隐藏"主键。这个隐藏的主键为每个表记录消耗31-35字节。

你可以使用ndb_size.pl Perl脚本估算NDB存储需求。它连接到当前的MySQL（而不是NDB集群）数据库，并创建一个报告，说明如果使用NDB存储引擎，该数据库将需要多少空间。

数字型存储要求

数据类型	存储空间需求
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT, INTEGER	4 bytes
BIGINT	8 bytes
`FLOAT(` `p` `)`	4 bytes if 0 <= `p` <= 24, 8 bytes if 25 <= `p` <= 53
FLOAT	4 bytes
`DOUBLE [PRECISION]` , REAL	8 bytes
`DECIMAL(` `M` `,` `D` `)` , `NUMERIC(` `M` `,` `D` `)`	可变；参见下面的讨论
`BIT(` `M` `)`	大约(`M` +7)/8 bytes

DECIMAL（和NUMERIC）列的值使用二进制格式表示，将九个十进制（以10为基数）数压缩为四个字节。每个值的整数部分和小数部分的存储分别确定。每9位数的倍数需要4个字节，"剩余"位数需要4个字节的一部分。下表给出了多余数字所需的存储空间。

剩余位数	字节数
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之前创建的表与从5.6.4开始创建的表所需的存储空间不同。这是由于5.6.4中的一个更改，允许这些类型具有小数部分，这需要0到3个字节。

数据类型	MySQL 5.6.4 之前的存储要求	从MySQL 5.6.4 开始的存储要求
YEAR	1 byte	1 byte
DATE	3 bytes	3 bytes
TIME	3 bytes	3 bytes + 小数秒存储
DATETIME	8 bytes	5 bytes + 小数秒存储
TIMESTAMP	4 bytes	4 bytes + 小数秒存储

从MySQL5.6.4开始，YEAR和DATE的存储保持不变。但是，TIME、DATETIME和 TIMESTAMP的表示方式不同。DATETIME的压缩效率更高，非小数部分需要5个字节而不是8个字节，而且这三个都有小数部分，需要0到3个字节，这取决于存储值的小数秒精度。

小数秒精度	存储空间要求
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)相等，需要相同的存储空间。

字符串类型存储要求

在下表中，M表示声明的列长度（对于非二进制字符串类型，以字符为单位，对于二进制字符串类型，以字节为单位）。L表示给定字符串值的实际长度（以字节为单位）。

数据类型	存储空间要求
`CHAR(` `M` `)`	紧凑的InnoDB行格式优化了可变长度字符集的存储。请参阅紧凑行格式存储特性。此外，M × w字节，<= M <=255，其中< span="">w是字符集中最大长度字符所需的字节数。
`BINARY(` `M` `)`	`M` bytes, 0 `<=` `M` `<=< code=""> 255`
`VARCHAR(` `M` `)` , `VARBINARY(` `M` `)`	如果列值需要0−255字节，则L+1字节，如果值可能需要超过255字节，则L+2字节。
TINYBLOB, TINYTEXT	`L` + 1 bytes, 其中`L` < 2⁸
BLOB, TEXT	`L` + 2 bytes, 其中`L` < 2¹⁶
MEDIUMBLOB, MEDIUMTEXT	`L` + 3 bytes, 其中`L` < 2²⁴
LONGBLOB, LONGTEXT	`L` + 4 bytes, 其中`L` < 2³²
`ENUM('` `value1` `','` `value2` `',...)`	1或2个字节，取决于枚举值的数量（最多65535个值）
`SET('` `value1` `','` `value2` `',...)`	1、2、3、4或8个字节，取决于集合成员的数量（最多64个成员）

可变长度字符串类型使用长度前缀加数据存储。长度前缀需要1到4个字节，具体取决于数据类型，前缀的值是L（字符串的字节长度）。例如，存储MEDIUMTEXT值需要L个字节来存储该值，再加上三个字节来存储值的长度。

要计算用于存储特定CHAR、VARCHAR或TEXT列值的字节数，必须考虑用于该列的字符集以及该值是否包含多字节字符。特别是，使用utf8 Unicode字符集时，必须记住并非所有字符都使用相同的字节数。utf8mb3和utf8mb4字符集分别需要每个字符最多3个和4个字节。

VARCHAR、VARBINARY以及BLOB和TEXT类型是可变长度类型。对于每种情况，存储要求取决于以下因素：

●列值的实际长度

●列的最大可能长度

●用于列的字符集，因为某些字符集包含多字节字符

例如，VARCHAR(255)列可以包含最大长度为255个字符的字符串。假设列使用latin1字符集（每个字符一个字节），实际需要的存储空间是字符串的长度（L），再加上一个字节来记录字符串的长度。对于字符串'abcd'，L为4，存储空间要求5个字节。如果此列被声明为使用ucs2双字节字符集，则存储空间要求10个字节：'abcd'的长度为8个字节，该列需要两个字节来存储长度，因为最大长度大于255（最多510个字节）。

VARCHAR或VARBINARY列中可存储的最大有效字节数受65535字节的最大行大小限制，该行大小在所有列之间共享。对于存储多字节字符的VARCHAR列，有效存储的最大字符数会较少。例如，utf8mb4字符每个字符最多需要4个字节，因此使用utf8mb4字符集的VARCHAR列可以声明为最多16383个字符。

InnoDB将长度大于或等于768字节的固定长度字段编码为可变长度字段，可在页外存储。例如，如果字符集的最大字节长度大于3（就像utf8mb4一样），CHAR(255)列可以超过768字节。

NDB存储引擎支持可变宽度的列。这意味着NDB集群表中的VARCHAR列需要与任何其他存储引擎相同的存储量，但这些值是4字节对齐的。因此，使用latin1字符集存储在VARCHAR(50)列中的字符串'abcd'需要8个字节（而不是MyISAM表中相同列值的5个字节）。

TEXT列和BLOB列在NDB中的实现方式不同；TEXT列中的每一行都由两个独立的部分组成。其中一个是固定大小（256字节），实际上存储在原始表中，另一个由超过256字节部分的数据组成，这些数据存储在一个隐藏的表中。第二个表中的行总是2000字节长。这意味着，如果size<=256（其中size表示行的大小），则text列的大小为256；否则，大小为256+size+（2000×（size−256）%2000）。< span="">

ENUM对象的大小由枚举值的数目决定。一个字节最多用于255个可能值的枚举。两个字节用于可能值在256到65535之间的枚举。

SET对象的大小由集合成员的数目决定。如果集合大小为N，则对象占用(N+7)/8字节，四舍五入为1、2、3、4或8字节。一个SET最多可以有64个成员。

空间类型存储要求

MySQL使用4个字节存储SRID，后跟值的WKB表示。LENGTH()函数的作用是返回值存储所需的字节空间。

JSON存储要求

通常，JSON列的存储需求与LONGBLOB或LONGTEXT列的存储需求大致相同；也就是说，JSON文档所消耗的空间与存储在这些类型之一的列中的文档的字符串表示形式大致相同。但是，对于存储在JSON文档中的各个值，二进制编码（包括查找所需的元数据和字典）会带来开销。例如，存储在JSON文档中的字符串需要4到10个字节的额外存储，这取决于字符串的长度以及存储它的对象或数组的大小。

此外，MySQL对存储在JSON列中的任何JSON文档的大小都有限制，它不能大于max_allowed_packet的值。

ndb_size.pl Perl 脚本下载地址：

https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-programs-ndb-size-pl.html

官方文档地址：

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL数据类型 - 数据类型存储要求

评论