MySql 数据类型所占存储空间

最近在折腾指标监控报警,对指标监控来说,海量的数据存储肯定是考验,业内比较好的方案应该是存储到时序数据库。对我们来说刚开始起步,存储到最熟悉的 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 类型都是可变长类型。对于每一个,存储需求取决于以下因素:

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

例如,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

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 215,294评论 6 497
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,780评论 3 391
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 161,001评论 0 351
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,593评论 1 289
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,687评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,679评论 1 294
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,667评论 3 415
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,426评论 0 270
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,872评论 1 307
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,180评论 2 331
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,346评论 1 345
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,019评论 5 340
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,658评论 3 323
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,268评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,495评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,275评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,207评论 2 352