官方文档地址: 11.7 Data Type Storage Requirements
在磁盘上对表数据的存储要求取决于几个因素。不同的存储引擎表示数据类型以不同的方式存储原始数据。对于一个列或整个行,表数据可能会被压缩,从而使表或列的存储需求计算变得更加复杂。
尽管磁盘上的存储布局不同,内部的 MySQL APIs 使用一致的数据结构通信和交换关于表行的信息,,适用于所有存储引擎。
本节包括 MySQL 支持的每种数据类型的存储要求的指南和信息,包括使用固定大小表示数据类型的存储引擎的内部格式和大小。信息按类别或存储引擎列出。
一个表的内部表示的最大的行大小为65,535
字节,即使存储引擎能够支持更大的行。这个数字排除了BLOB
或TEXT
列,它们只占这个大小的9
到12
个字节。对于BLOB
和TEXT
数据,信息存储在与行缓冲区不同的内存区域中。不同的存储引擎根据它们处理相应类型的方法,以不同的方式处理这些数据的分配和存储。有关更多信息,请参阅 第16章 可供选择的存储引擎 以及 8.4.7 对表列数和行大小的限制。
InnoDB 表存储要求
关于InnoDB
表的存储要求,请参见 15.10 InnoDB 行格式。
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 Cluster
表定义了超过32
个NULL
列,最多64
个NULL
列,那么每行保留8
个字节。)
每个使用NDB
存储引擎的表都需要一个主键;如果您没有定义一个主键,NDB
将创建一个“隐藏的”主键。这个隐藏的主键每条表记录消耗31-35
个字节。
您可以使用ndb_size.pl
Perl 脚本来估计NDB
存储需求。它连接到当前的 MySQL(不是NDB Cluster
)数据库,并创建一个报告,说明如果使用NDB
存储引擎,数据库需要多少空间。更多信息请参见 23.4.28 ndb_size.pl - NDBCLUSTER 大小存储评估器。
数值类型存储要求
数据类型 | 存储要求 |
---|---|
TINYINT | 1 字节 |
SMALLINT | 2 字节 |
MEDIUMINT | 3 字节 |
INT , INTEGER | 4 字节 |
BIGINT | 8 字节 |
FLOAT(p) | 如果 0 <= p <= 24,4 字节 如果 25 <= p <= 53,8 字节 |
FLOAT | 4 字节 |
DOUBLE [PRECISION] , REAL | 8 字节 |
DECIMAL(M,D) , NUMERIC(M,D) | 变化的,看下面的讨论 |
BIT(M) | 约为 (M+7)/8 字节 |
DECIMAL
(和NUMERIC
)列的值使用二进制格式表示,该格式将9
个十进制(以10
为基数)数字压缩为4
个字节。每个值的整数部分和小数部分的存储是单独确定的。每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 字节 | 1 字节 |
DATE | 3 字节 | 3 字节 |
TIME | 3 字节 | 3 字节 + 小数秒存储 |
DATETIME | 8 字节 | 5 字节 + 小数秒存储 |
TIMESTAMP | 4 字节 | 4 字节 + 小数秒存储 |
从 MySQL 5.6.4 开始,存储的YEAR
和DATE
保持不变。但是,TIME
、DATETIME
和TIMESTAMP
的表示方式不同。DATETIME
的打包效率更高了,对于非小数部分需要5
个字节而不是8
个字节,并且所有三个部分都有一个小数部分,需要从0
到3
个字节,这取决于存储的小数秒值精度。
精确到小数部分的秒 | 存储要求 |
---|---|
0 | 0 字节 |
1,2 | 1 字节 |
3,4 | 2 字节 |
5,6 | 3 字节 |
例如,TIME(0)
、TIME(2)
、TIME(4)
和TIME(6)
分别使用3
、4
、5
和6
个字节。TIME
和TIME(0)
是等价的,需要相同的存储空间。
字符串类型的存储要求
在下表中,M
表示声明的列长度,以字符表示非二进制字符串类型,以字节表示二进制字符串类型。L
表示给定字符串值的实际长度(以字节为单位)。
数据类型 | 存储要求 |
---|---|
CHAR(M) | 紧凑的 InnoDB 行格式家族优化了可变长度字符集的存储。见 15.10 InnoDB 行格式。否则,M × w 字节,0 <= 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 < 2E8 |
BLOB ,TEXT | L + 2 字节,其中 L < 2E16 |
MEDIUMBLOB ,MEDIUMTEXT | L + 3 字节,其中 L < 2E24 |
LONGBLOB ,LONGTEXT | L + 4 字节,其中 L < 2E32 |
ENUM('value1','value2',...) | 1 或 2 字节,取决于枚举值的数量(最大为 65,535 个值) |
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
字符的存储,请参见 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
列,有效最大字符数更少。例如,utf8mb4
字符每个字符最多需要4
个字节,因此可以将使用utf8mb4
字符集的VARCHAR
列声明为最大16383
个字符。参见 8.4.7 对表列数和行大小的限制。
InnoDB
将长度大于等于768
字节的定长字段编码为可变长字段,可以在页外存储。例如,如果字符集的最大字节长度大于3
,例如utf8mb4
,CHAR(255)
列就可以超过768
字节。
NDB
存储引擎支持可变宽度列。这意味着NDB Cluster
表中的VARCHAR
列需要与任何其他存储引擎相同的存储量,只是这些值是4
字节对齐的。因此,使用latin1
字符集存储在VARCHAR(50)
列中的字符串'abcd'
需要8
个字节(而不是MyISAM
表中的相同列值需要5
个字节)。
TEXT
和BLOB
列在NDB
中实现不同;TEXT
列中的每一行都由两个单独的部分组成。其中一个具有固定大小(256
字节),实际上存储在原始表中。另一个由超过256
字节的数据组成,这些数据存储在一个隐藏的表中。第二个表中的行总是2000
字节长。这意味着如果size <= 256
(其中size
表示行的大小),则文本列的大小为256
;否则为256 + size + (2000 × (size−256)% 2000)
。
ENUM
对象的大小由不同枚举值的数量决定。一个字节用于最多255
个可能值的枚举。两个字节用于256
到65,535
个可能值之间的枚举。参见 11.3.5 ENUM 类型。
SET
对象的大小由不同SET
成员的数量决定。如果设置的大小为N
,则对象占用(N+7)/8
个字节,四舍五入到1
、2
、3
、4
或8
个字节。一个集合最多可以有64
个成员。参见 11.3.6 SET 类型。
空间类型存储要求
MySQL 使用4
个字节存储几何值,以指示 SRID,后面是值的 WKB 表示。LENGTH()
函数的作用是以字节为单位返回存储值所需的空间。
关于 WKB 和空间值的内部存储格式的描述,请参见 11.4.3 支持的空间数据格式。
JSON 类型存储要求
一般来说,JSON
列的存储需求与LONGBLOB
或LONGTEXT
列的存储需求大致相同;也就是说,JSON
文档所消耗的空间与存储在这些类型之一的列中的文档字符串表示所消耗的空间大致相同。但是,存储在JSON
文档中的单个值的二进制编码(包括查找所需的元数据和字典)会带来额外的开销。例如,存储在JSON
文档中的字符串需要4
到10
个字节的额外存储,这取决于字符串的长度和存储它的对象或数组的大小。
另外,MySQL 对JSON
列中存储的任何JSON
文档的大小施加了限制,不能超过max_allowed_packet
的值。