数据类型的存储要求

概述

• InnoDB 表的存储要求

• NDB 表的存储要求

• 数值类型的存储要求

• “日期和时间”的存储要求

• 字符串类型的存储要求

• 空间类型的存储要求

• JSON 的存储要求

磁盘上表数据的存储要求取决于几个因素。不同存储引擎表示的数据类型和存储的原始数据均不同。表数据可能会被压缩，无论是针对列还是整行，都会使得表或列的存储要求的计算复杂化。

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

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

表的内部表示形式的最大行大小是 65535 字节，即使存储引擎能够支持更大的行。以上表述不包括 BLOB 和 TEXT 列，它们仅占用该大小的 9 到 12 字节。对于 BLOB 和 TEXT 数据，该信息存储在与行缓冲区不同的内存区域中。不同存储引擎以不同方式处理此数据的分配和存储。有关更多信息，请查看 Chapter 16, Alternative Storage Engines 和 Section 8.4.7, “Limits on Table Column Count and Row Size”。

InnoDB 表的存储要求

关于 InnoDB 表的存储要求，请看 Section 15.10, “InnoDB Row Formats”。

NDB 表的存储要求

重要：NDB 表使用 4 字节对齐；所有 NBD 数据存储均以 4 字节的倍数完成。因此，通常需要 15 字节的列值在 NDB 表中则需要 16 字节。例如，在 NDB 表中，由于对齐的因素，TINTINT、SMALLINT、MEDIUMINT 和 INTEGER(INT) 类型列的每条记录都需要 4 字节。

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

尽管 NULL 本身不需要任何存储空间，但如果表定义包含允许为 NULL 的任意列（最多 32 个 NULL 列），则 NDB 为每行保留 4 字节。（如果定义的 NDB 集群表拥有超过 32 个 NULL 列（最多 64 个 NULL 列），则为每行保留 8 字节。）

使用 NDB 存储引擎的每个表都需要一个主键。若未定义主键，则 NDB 会创建一个“隐藏”主键。该隐藏主键消耗每行 31 - 35 字节。

可使用 ndb_size.pl Perl 脚本来估计 NDB 的存储要求。它会连接到当前的 MySQL 数据库（不是 NDB 集群），并创建有关使用 NDB 存储引擎时数据库需要多少空间的报告。有关更多信息，请看 Section 22.4.28, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator”。

数值类型的存储要求

Data Type	Storage Required
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT, INTEGER	4 bytes
BIGINT	8 bytes
FLOAT(p)	4 bytes（p [0, 24]）, 8 bytes（p [25, 53]）
FLOAT	4 bytes
DOUBLE [PERCISION], REAL	8 bytes
DECIMAL(M,D),NUMERIC(M,D)	不定，看下面讨论
BIT(M)	大约 (M+7)/8 bytes

DECIMAL（和 NUMERIC）列值使用二进制格式表示，它将 9 个十进制（基数 10）数字打包为 4 字节。每个值的整数和小数部分（的存储）需要分别独立确定。每 9 位数字需要 4 字节，而“剩余”数字需要小于或等于 4 字节。下表给出了多余数字的存储要求。

Leftover Digits	Number of Bytes
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

“日期和时间”类型的存储要求

从 MySQL 5.6.4 开始，TIME、DATETIME 和 TIMESTAMP 列允许拥有小数部分，这部分需要额外 0 到 3 字节。

Data Type	Storage Required Before MySQL 5.6.4	Storage Required as of 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 + 小数部分的存储空间

从 MySQL 5.6.4 开始，YEAR 和 DATE 的存储需求保存不变，但 TIME、DATETIME 和 TIMESTAMP 的表示形式变得不同。DATETIME 的存储效率更高，非小数部分从 8 字节变为 5 字节，并且这三者的小数部分需要 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) 是等价的，故需要相同的存储空间。

有关时间值内部表示的详情信息，请看 MySQL Internals: Important Algorithms and Structures。

字符串的存储要求

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

Data Type	Storage Required
CAHR(M)	紧凑的 InnoDB 行格式优化了拥有不同长度字符集的存储。详情请看 StartFragment COMPACT Row Format Storage CharacteristicsEndFragment。除此之外，就是 M x w 字节，M 的取值范围为 [0, 255]，w 是字符集中最长字符所需的字节数。
BINARY(M)	M 字节，M 的取值范围为 [0,255]。
VARCHAR(M),VARBINARY(M)	若列值需要 [0,255] 字节，则共需要 L + 1 字节；若列值需要大于 255 字节，则共需要 L + 2 字节。
TINYCHAR,TINYTEXT	L + 1 字节，其中 L < 2^8
BLOB,TEXT	L + 2 字节，其中 L < 2^16
MEDIUMBLOB,MEDIUMTEXT	L + 3 字节，其中 L < 2^24
LONGGLOB,LONGTEXT	L + 4 字节，其中 L < 2^32
ENUM('value1','value2',...)	1 或 2 字节，取决于枚举值的数量（最多为 65535 个值）
SET('value1', 'value2',...)	1、2、3、4 或 8 字节，取决于 set 成员的数量（最多 64 个成员）

可变长字符串类型存储的是：长度前缀 + 实际数据。长度前缀需要 [1, 4] 字节，这具体取决于数据类型，并且前缀的值为 L（字符串的字节长度）。例如，存储 MEDIUMTEXT 值需要 L 字节存储实际值，再加上 3 字节存储实际值的长度。

要计算用于存储特定 CHAR、VARCHAR 或 TEXT 列值的字节数，必须要考虑该列的字符集以及该值是否含有多字节字符。特别是 utf8 Unicode 字符集，因为它并非所有字符都使用相同数量的字节。utf8mb3 和 utf8mb4 字符集的每个字符分别最多需要 3 和 4 字节。有关用于不同类别的 utf8mb3 或 utf8mb4 字符的存储的详细信息，请看 Section 10.9, “Unicode Support”。

VARCHAR、VARBINARY、BLOB 和 TEXT 类型都是可变长类型。对于各自的存储要求，取决于以下因素：

• 列值的实际长度

• 列的最大可能长度

• 列的字符集，因为某些字符集包含多字节字符。

例如，VARCHAR(255) 列可容纳最大长度为 255 个字符的字符串。假设该列使用 latin1 字符集（每个字符均为 1 字节），则实际需要的存储空间是字符串长度（L）+ 用于记录字符串长度的 1 字节。对于字符串 'abcd'，L 是 4，所以存储要求为 5 字节。如果该列更改声明为 2 字节的 ucs2 字符集，则存储要求是 10 字节：'abcd' 的长度为 8 字节，并且需要 2 字节存储长度，因为最大长度大于 255 字节（最大为 510 个字节）。

VARCHAR 或 VARBINARY 列可存储的有效最大字节数取决于 65535 字节的最大行大小（row size），该大小是所有列之间共享。对于存储多字节字符的 VARCHAR 列，最大有效字符数会变少。例如，utf8mb4 字符集的每个字符最多需要 4 字节，因此使用 utf8mb4 字符集的 VARCHAR 列可声明为最多 16383 个字符。详情请看 Section 8.4.7, “Limits on Table Column Count and Row Size”。

InnoDB 会将大于或等于 768 字节的定长字段（Field）编码为可存储在 off-page 的可变长字段。例如，CHAR(255) 列可能会超过 768 字节，因为存在最大字节长度大于 3 的字符集，如 utf8mb4。

NDB 存储引擎支持可变宽列。这意味着 NDB 集群中的 VARCHAR 列和其他存储引擎一样，需要相同大小的存储空间，然而这些值是 4 字节对齐的。因此，对于存储在字符集为 latin1 的 VARCHAR(50) 列的 'abcd' 字符串，需要 8 字节（在相同条件下，MyISAM 表则需 5 字节）。

TEXT 和 BLOB 列在 NBD 中的实现方式有所不同；TEXT 列中的每一行（译者注：即 TEXT 列的每个值）都是由两个独立的部分组成。其中一部分是固定大小（256 字节），并且实际存储在原始表中，另一部分是存储在隐藏表中的，超出 256 字节的数据。第二个表的行大小始终为 2000 字节。这意味着，如果 size <= 256（size 表示行的大小），则 TEXT 列大小是 256；否则大小为 256 + size + (2000 × (size − 256) % 2000)。

ENUM 对象的大小由不同枚举值的数量决定。1 字节用于最多 255 个枚举值；2 字节用于 [256, 65535] 个枚举值。详情请看 See Section 11.3.5, “The ENUM Type”。

SET 对象的大小由不同 set 成员的数量决定。如果 set 大小为 N（译者注：要理解这个“大小”，需要先了解 SET 值的内部表示。1 个 set 成员占 1 bit，64 个成员，则占 64 bit），则对象占用 (N + 7)/8 字节，向上取整为 1、2、3、4 或 8 字节。一个 SET 最多可拥有 64 个成员。详情请看 Section 11.3.6, “The SET Type”。

空间类型的存储要求

暂未涉及，后续再翻译。

JSON 的存储要求

通常，JSON 列的存储需求与 LONGBLOB 或 LONGTEXT 列存储要求大致相同。也就是说，JSON 文档占用的空间与将该文档序列化后（译者注：即将 JSON 字符串表现形式）存储在这些类型中所用的空间大致相同。然而，存储在 JSON 文档的各个值的二进制编码会产生额外的开销，这其中包括需要查找的元数据和字典。例如，存储在 JSON 文档中的字符串需要 4 到 10 字节的额外存储空间，这具体取决于字符串的长度和存储该字符串的对象或数组的大小。

另外，MySQL 对存储在 JSON 列中的 JSON 文档的大小施加了限制，以使其不能大于 max_allowed_packet 的值。