MySql 数据类型所占存储空间

作者: 鱼蛮子9527 | 来源:发表于2022-06-13 19:45 被阅读0次

最近在折腾指标监控报警，对指标监控来说，海量的数据存储肯定是考验，业内比较好的方案应该是存储到时序数据库。对我们来说刚开始起步，存储到最熟悉的 MySql 肯定是最快最稳妥的方案。做啥都是应该先跑起来，一下子吃成胖子很容易撑坏。做大做强，再创辉煌，应该是取得一定成绩后的目标。

虽说初期小业务量，但是对最基本的存储来说，还是应该能省就省。现在外部大环境不好，集团也特别关注成本的控制。放到程序上来说，每行更小的数据 size，意味着数据读取的更块，系统跑的更快。

所以就整体复习了下 MySql 各种字段的存储需求，MySql 版本是 5.7。

总体说明

表数据在磁盘上的存储取决于几个因素，不同的存储引擎表示数据类型及对原始数据的存储都是不同的。表数据可能会被压缩，无论是针对一列还是整行，这会使表或列的存储需求计算复杂化。我们就不去关注其他引擎了，都是基于 InnoDB 引擎。

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

数值类型

数据类型	存储要求	数据范围
TINYINT	1 byte	-128 到 127
SMALLINT	2 bytes	-32768 到 32767
MEDIUMINT	3 bytes	-8388608 到 8388607
INT, INTEGER	4 bytes	-2147483648 到 2147483647
BIGINT	8 bytes	-9223372036854775808 到 9223372036854775807
FLOAT(p)	当 0 <= p <= 24 , 4 bytes 当 25 <= p <= 53 , 8 bytes
FLOAT	4 bytes	-3.402823466E+38 到 -1.175494351E-38 0 1.175494351E-38 到 3.402823466E+38.
DOUBLE [PRECISION], REAL	8 bytes	-1.7976931348623157E+308 到 -2.2250738585072014E-308 0 2.2250738585072014E-308 到 1.7976931348623157E+308.
DECIMAL(M,D), NUMERIC(M,D)	请看：DECIMAL 特殊说明	M 长度最大为 65
BIT(M)	约为 (M+7)/8 bytes	M 长度 1 到 64

DECIMAL

在MySQL中，NUMERIC 是作为 DECIMAL 实现的，所以下面所有 DECIMAL 的说明同样适用于 NUMERIC。

DECIMAL 类型的值使用二进制格式表示，将 9 个十进制数字装入 4 个字节。每个值的整数和小数部分的存储是单独确定的。每 9 位数就需要 4 个字节，而 "剩余 "的数字需要至少 4 个字节来存储。多余的数字所需的存储量由下表中给出。

剩余数字个数	存储需求
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes
7, 8	4 bytes

可以看出奇数个位数跟 +1 后的偶数位数所需的空间是一样的，如果只考虑存储成本，我们在使用的时候，可以尽量让剩余数字个数为偶数值。

在标准 SQL 中，DECIMAL(M) 的语法等同于 DECIMAL(M,0)。类似地，DECIMAL 语法等同于 DECIMAL(M,0)，其中允许执行者决定 M 的值。M 的默认值是10。

DECIMAL 的最大位数是 65，但是一个给定的 DECIMAL 列的实际范围会受到给定列的精度或比例的限制。当这样的一列被分配到一个小数点后面的数字多于指定比例所允许的数值时，该数值将被转换为该比例。(精确的行为是由操作系统决定的，但通常的效果是截断到允许的位数)。

日期和时间类型

数据类型	存储需求
YEAR	1 byte
DATE	3 bytes
TIME	3 bytes + 小数秒存储
DATETIME	5 bytes + 小数秒存储
TIMESTAMP	4 bytes + 小数秒存储

从 MySQL 5.6.4 开始，对 TIME、DATETIME 和 TIMESTAMP 改为固定大小 + 小数秒存储方式，需要 0 到 3 个字节，这取决于存储的小数秒值精度。

小数秒精度	存储需求
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

可以看到与 DECIMAL 类似，如果只考虑存储成本，我们在使用的时候，可以尽量设置成所需的偶数值。

例如，DATETIME(0)、DATETIME(2)、DATETIME(4) 和 DATETIME(6) 分别使用 5、6、7 和 8 个字节。DATETIME 和 DATETIME(0) 是等价的，需要相同的存储空间。

字符串类型

可变长度字符串类型使用长度前缀加数据存储。根据数据类型，长度前缀需要 1 到 4 个字节，字符串的字节长度为 L。例如，存储一个 MEDIUMTEXT 值需要 L 个字节来存储该值，再加上 3 个字节来存储该值的长度。

在下表中，M 表示非二进制字符串类型的声明列长度（以字符为单位）和二进制字符串类型的字节数。 L 表示给定字符串值的实际长度（以字节为单位），特别注意，这里不是字符长度，开始时候很容易弄混。即 LENGTH() 方法计算出来的长度，而不是 CHAR_LENGTH() 方法计算出来的长度。

数据类型	存储需求
CHAR(M)	InnoDB 紧凑的行格式，优化了可变长度字符集的存储,见下面对 CHAR 类型的说明。否则应该是 M × w 字节，0 <= M <= 255，其中 w 是字符集中最大长度字符所需的字节数。
BINARY(M)	M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)	当 0 <= L <= 255 bytes : L + 1 bytes 当 L > 255 bytes : L + 2 bytes
TINYBLOB, TINYTEXT	L + 1 bytes， L < $2^8$
BLOB, TEXT	L + 2 bytes， L < $2^{16}$
MEDIUMBLOB, MEDIUMTEXT	L + 3 bytes， L < $2^{24}$
LONGBLOB, LONGTEXT	L + 4 bytes， L < $2^{32}$
ENUM('value1','value2',...)	1 或 2 bytes，取决于枚举值的数量（最大为 65,535 个值）
SET('value1','value2',...)	1、2、3、4 或 8 个 bytes，取决于 set 成员的数量（最大 64 个成员）

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

CHAR

在 MySql 内部，对于非可变长度字符集如 latin1，固定长度字符列，如 CHAR(10) 以固定长度格式存储。

对于如 utf8mb3 和 utf8mb4 等可变长度字符集，InnoDB 至少为 CHAR(N) 保留 N 个字节，并尝试通过修剪尾随空格将 CHAR(N) 存储在 N 个字节中。如果 CHAR(N) 列值的字节长度超过 N 个字节，则尾随空格将被修剪为列值字节长度的最小值。 CHAR(N) 列的最大长度是最大字符字节长度 × N。

在许多情况下，保留最小空间 N 可以在不导致索引页碎片的情况下就地完成列更新。相比之下，CHAR(N) 列在使用 REDUNDANT 行格式时占用最大字符字节长度 × N。

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

可变长类型

VARCHAR、VARBINARY 以及 BLOB 和 TEXT 类型都是可变长类型。对于每一个，存储需求取决于以下因素：

列值的实际长度
列的最大可能长度
用于列的字符集，因为一些字符集包含多字节字符

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

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

ENUM

ENUM 对象的大小由不同枚举值的数量决定。1 个字节用于最多 255 个可能值的枚举。2 个字节用于 256 到65,535 个可能值之间的枚举。

SET

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

空间类型

空间类型在 MySQL 内部以一种与 WKT 及 WKB 格式不完全相同的格式存储空间类型数据，格式是： 4 字节 SRID + WKB。

WKB 格式

WKB 使用 1 字节的无符号 Integer 标识字节顺序，4 字节的无符号 Integer 标识类型，及 8 字节的双精度 Double来记录坐标。

例如，对一个 POINT(1 -1) 的 WKB 数据就是由 21 个字节组成，每个字节由两个十六进制数字表示。

0101000000000000000000F03F000000000000F0BF

组成部分	长度	值
Byte order	1 bytes	01
WKB type	4 bytes	01000000
X coordinate	8 bytes	000000000000F03F
Y coordinate	8 bytes	000000000000F0BF

空间类型存储空间

mysql> SET @g = ST_GeomFromText('POINT(1 -1)');
mysql> SELECT LENGTH(@g);
+------------+
| LENGTH(@g) |
+------------+
|         25 |
+------------+
mysql> SELECT HEX(@g);
+----------------------------------------------------+
| HEX(@g)                                            |
+----------------------------------------------------+
| 000000000101000000000000000000F03F000000000000F0BF |
+----------------------------------------------------+

知道了 WKB 数据的组成方式，自然也就知道了 MySql 中对空间类型的存储大小了。

组成部分	长度	二进值	实际值
SRID	4byte	00000000	0
Byte order	1 bytes	01	little-endian
WKB type	4 bytes	01000000	Point
X coordinate	8 bytes	000000000000F03F	1
Y coordinate	8 bytes	000000000000F0BF	-1

JSON 类型

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

另外，MySQL 对 JSON 列中存储的任何 JSON 文档的大小施加了限制，不能超过 max_allowed_packet 的值。

Data Type Storage Requirements

Supported Spatial Data Formats

网友评论

本文标题：MySql 数据类型所占存储空间

本文链接：https://www.haomeiwen.com/subject/rnzomrtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！