之前遇到一个问题“怎么评估一行MySQL记录占据了多少字节?”。自己当时也不是特别清楚。当时只是简单的知道,具体的长度跟编码相关。前几天系统研究了一下MySQL中的编码问题,铺垫已经做好,那就研究下长度的问题。
1 类型及长度
1.1 char
char 最长255个字符
(自从了解了MySQL的字符编码,每次写字符还是字节,都得考虑3秒)
看报错信息:
1.2 varchar
1.2.1 varchar的最大长度
VARCHAR的最大长度 = (最大行大小 - NULL标识列占用字节数 - 长度标识字节数) / 字符集单字符最大字节数。
1.2.2 最大行大小:
MySQL行默认最大65535字节,是所有列共享的,所以VARCHAR的最大值受此限制。
1.2.3 可空列标识位
如果有一个列允许为空,则需要1 bit来标识,每8 bits的标识会组成一个字段,该字段会存放在每行最开始的位置。
如果有N个NULL 字段,则标识符占用的长度就是N/8 向上取整。
来个例子:
create table testMysql4(v varchar(65533));
结果再用show create table testMysql4 ,发现建表之后的语句是这样的:
CREATE TABLE `testMysql4` (
`v` mediumtext COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
又遇到 mediumtext 跟 varchar(200+) 的问题: wubx.net/varchar-vs-… (里面讲到了varchar200+ 跟text等在存储上的关系,不过没太看懂innoDB的存储,得补一下。)
根据存储的实现: 可以考虑用varchar替代tinytext
如果需要非空的默认值,就必须使用varchar
如果存储的数据大于64K,就必须使用到mediumtext , longtext varchar(255+)和text在存储机制是一样的
需要特别注意varchar(255)不只是255byte ,实质上有可能占用的更多。
特别注意,varchar大字段一样的会降低性能,所以在设计中还是一个原则大字段要拆出去,主表还是要尽量的瘦小
1.2.4 varchar 的长度标识位
存储开销是小于255只要1字节、大于255后使用两字节。是因为按照可能的数据大小,分为0 - 255(28)、256 - 65535(216),刚好对应1字节和2字节。
但要注意,
其计算根据的是字段声明的字符长度、计算可能的字节数(取编码的最大长度,如utf8 是3字节,而utf8mb4 是4字节),再决定长度标志的字节数
。如VARCHAR(100),字符集为UTF8,可能的字节数为300`,长度标识则为2字节。
MySQL中声明的类型长度都是字符数,而计算长度时需要转换成字节。
1.3 TEXT
TEXT最大长度为65535(2^16 − 1)个字符。如果是多字节字符,则有效最大长度会更少。存储时会增加2字节的前缀、标识长度。
TEXT还有几个特性,我们只列一下需要注意的一个点:
TEXT无法使用临时表。因为TEXT列可能很大,临时表空间会膨胀的非常快,所以MYSQL的MEMORY引擎不支持这类大的数据类型。 如果临时表列包括了TEXT类型,MySQL会直接用磁盘上的表、而不是内存中的表。 磁盘比内存的I/O效率低很多,这就意味着性能急剧降低。
建议:
- 避免使用SELECT *,它会选择所有列,而是在已经确定结果集范围后、左联获取对应的TEXT字段;
- 可以将TEXT单独拆出一个表,这样读写时减少与该列发生关系的可能,性能也会提升。
1.4 整数类型
整数是定长类型,长度为字节数(比如int 固定长度为4字节)。我们常见的int(11) 只是为了设置显示宽度。[8.0 版本之后,不建议使用显示宽度了,确实也鸡肋哈]
该宽度指示器并不会影响int列存储字段的大小,也就是说,超过显示宽度它不会自动截取,依然会存储,只有超过它本身的存储范围才会截取;此处宽度指示器的作用在于该字段是否有zerofill,如果有就未满足显示宽度的部分就会用0来填充)
我们来验证下。先创建一个表:
create table testMysql9 (id int(4) UNSIGNED ZEROFILL);
然后插入一下数据:
insert into testMysql9 values (1), (22222);
然后查询一下,看看具体怎么显示:
从上述截图可以看到,1这个数字长度不足显示宽度4, 则会以0补齐(如果不指定zerofill ,则默认以空格填充),22222 长度已经大于显示宽度4, 也已经存储进去了。 下面我们插入一个超过int 所表示范围的数字:(int 的最大范围是20几亿,我们插入一个大于这个数的值)
insert into testMysql9 values (33333222224444);
再查一下,发现是这样的,结果被截断了。
1.5 定点类型和浮点类型
1.5.1 Decimal(M,D) 定点类型。
M: 最大位数,包括整数跟小数部分。 D: 小数部分的最大位数。
Decimal 以二进制存储,因此实际存储长度、表达的数据范围 都是需要换算的。 其实很简单,举个例子 10 要转换成1010,占用了4bit (注意是小B),所以10 使用1个字节就能存储。如果是256 呢,转换成二进制就是 100000000 ,占用了9bit, 此时就得两个字节了。
另外,在计算存储时,decimal的整数部分跟小数部分是分开计算的。
1.5.2 浮点类型
float跟double, 因为涉及到精度问题,所以从8.0 开始将逐步去掉对浮点数的支持,所以我们就不讲了。如果想了解float跟double 为什么有精度问题,可以看我之前写的一篇文章: 由浮点数精度问题引发的思考-BigDecimal与IEEE754
1.6 布尔类型
MySQL实际上没有内置的布尔类型,声明为布尔类型,会使用TINYINT(1)存储。 看个例子:
显示创建成功,那再使用show create table 看下。CREATE TABLE `testMysql8` (
`id` int(11) DEFAULT NULL,
`booltest` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
booltest 字段确实是tinyint(1) 类型。那我们尝试往里面插入一条true 跟false ,然后select 一下看看。
insert into testMysql8 values (1, true), (2, false);
结果如下,我们看到true 存入了1, false 存入了0.
1.7 日期与时间
为什么从5.6.4 分开,这是因为从这个版本开始支持小数的秒。 那么小数秒的存储占用几个字节,取决于精度:
YEAR
: 年;
DATE
: 日期;
TIME
: 时间(不包括日期): 时分秒;
DATETIME
: 日期 + 时间;
TIMESTAMP
:UTC时区 的 日期 + 时间。从1970-01-01 00:00:01 开始。
至于底层存储为什么占用这么多字节,就不分析了,如果感兴趣,可以看官方的文档:dev.mysql.com/doc/interna…
2 innoDB索引长度限制
innodb 索引最大长度为767字节。如果是utf-8 就是 255个字符,这也恰恰是能建索引情况下的最大值。
如果是utf8mb4 , 则只能767 / 4 = 191 个字符。
疑问: (1)innodb 索引最大长度为什么是767字节?
INNODB的索引会限制单独Key的最大长度为 767 字节,超过这个长度必须建立小于等于 767 字节的前缀索引。
至于为什么是767字节,是依赖于具体的存储引擎实现的,找了官方文档,也没说为啥。 https://dev.mysql.com/doc/refman/8.0/en/create-index.html (文章中搜索767 直接跳转到这部分)
来个实验:
如果建表时就指定索引长度大于191 ,我们看下会有什么结果?
create table testMysql2 (
id varchar(256),
name varchar(255),
index idx_id(id(192))
)Engine = INNODB;
执行上述操作,结果建表成功了,那我们看下到底建表时索引建了多少呢?
还是191.
当然,也可以修改单个索引的最大长度(官方已经不推荐使用了),操作如下,我们就不实验了。
修改索引限制长度需要在my.ini配置文件中添加以下内容,并重启:
#修改单列索引字节长度为767的限制,单列索引的长度变为3072
innodb_large_prefix=1
但是开启该参数后还需要开启表的动态存储或压缩:
系统变量innodb_file_format为Barracuda
ROW_FORMAT为DYNAMIC或COMPRESSED
3 总结
本文总结了MySQL中几种常见的数据类型,以及其所占据的字节数,最后还简单介绍了innoDB中索引长度的问题。本来想讲一下Java中跟MySQL类型对应的具体类型,但是感觉直接看一下参考文献中的文章即可,无需再进一步复述。