一、前言
这篇文章是基于之前的一篇:导入sql文件报错:1071 Specified key was too long; max key length is 767 bytes ,当时只是知道主键长度不能超过767
字节,但是并不知道为什么会这么规定,这个767
有什么特殊的含义吗?这篇文章会尽量去解读一下这个767
是什么,为什么是它。
二、长度限制767的来源
1、compact行格式溢出
主键是聚集索引,也是其他二级索引的基础,所以每个主键要在一个数据页存储,不能分布在其他的溢出页,不能像其他大字段一样溢出数据页。
就是说博主认为,这个主键部分,包括单列索引的部分,它们都是一个整体的,不会像是其他数据文件一样溢出。而对于mysql5.7
之前,默认使用的行格式是compact
模式。对于这个模式大家可以百度学习下,这里不做详细解答。我们只需要知道这个行格式的溢出设定:当某一列中的数据非常多的话,在数据页中只会存储该列的前767个字节的数据和一个指向其他页的地址,然后把剩下的数据存放到其他页中,这个过程也叫做行溢出,存储超出767字节的那些页面也被称为溢出页。
如图(偷来的图):
这个768
字节是不是跟767
字节很接近了,博主看到这个概念的时候就在想,主键长度就像我们上面说的那样,超过这个阈值就会溢出到地址页,当然,这只是猜想,下面咱们尽量验证下。
2、查询之前报错的数据库,版本是小于5.7的,也就是使用的是compact行格式。
3、按照当时的计算
//这里是由于定义了多个字段共同组成主键索引
(255+10+10)*3 = 825 //在用utf8作为字符集的时候,超过了规定的767 bytes
(255+10+10)*2 = 550 //当该用gbk作为字符集的时候
(255+10+10)*4 = 1100 //当用utf8m4作为字符集的时候,也超标了
这里的主键长度,后来经过查询mysql
手册,并没有明确的说限制它的长度。但是我们发现了另外两个概念:单列索引和索引前缀
4、单列索引和索引前缀
翻阅诸多资料,大家的一致说法是当单列索引长度大于767
的时候,会取前767
个字节作为索引前缀,会有个warning
但不会直接报错。针对组合索引,比如a,b,c
三个字段的组合索引,要求索引长度加起来不能超过3072
字节。
767的另一种计算方式:256×3-1
这个3
是字符最大占用空间(utf8
)。但是在5.5
以后,开始支持4
个字节的utf8mb4
,因此单纯的767
在某些情况下就不是那么通用了,mysql
新增一个参数叫做 innodb_large_prefix
,可以允许索引达到3072
字节。
这里可以推断,我们一直说的主键长度,其实算是主键索引,而主键索引也是属于单列索引之内的,因为只有一列数据嘛。也就是符合以上mysql
对于单列索引长度的限制。
5、为什么主键长度超标会报错,单列索引只是warning呢
这部分,博主推断是由于主键部分是聚集索引,聚集索引的特性要求它必须是最为高效的结构。按照上面compact
行格式的定义,超过阈值之后,主键索引也会分布到其他的页面,这对于主键索引的完整性来说是不可接受的,因此主键长度超标就会报错。而普通的单列索引定义没有主键索引这么严格。
================ 2019/7/26更新 ================================
6、mysql手册证实行格式和索引关键字限制的关系
在手册中有一句话:记录的值的数据长度是由767个字节的索引关键字限制的限制 COMPACT和REDUNDANT排格式,并为3072个字节DYNAMIC和 COMPRESSED列格式。
链接:https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
这句话充分验证了我们的猜想,索引前缀的长度限制确实是和行格式有关的。
7、那么行格式的溢出阈值是768字节,为什么索引长度限制是767呢
本来这个问题博主也是百思不得其解,准备以后再来解答。但是群上有小伙伴对这个也有疑问,那就只能硬着头皮上了。在翻阅compact行格式定义的时候,在手册上发现一句话:
对于每个非NULL可变长度字段,记录头包含一个或两个字节的列长度。如果列的一部分存储在溢出页面的外部,或者最大长度超过255个字节且实际长度超过127个字节,则只需要两个字节。对于外部存储列,2字节长度表示内部存储部分的长度加上指向外部存储部分的20字节指针。内部部分为768字节,因此长度为768 + 20。20字节指针存储列的真实长度。
手册链接: https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
恍然大悟,如梦初醒。原来还要有1-2个字节要存储列长度,妥妥的。
新解释: 这个768
是行格式规定的溢出阈值,对于compact
行格式,我们存储一个varchar
的列,实际存储数据占用的空间包括:实际数据+真实数据占用字节的长度+null值信息
,当规定not null
的时候,null
值信息不占用空间,那么实际能存储的就是:实际数据 + 真实数据占用字节的长度
,这里的真实数据占用字节的长度一般是1-2
个字节代表列长度,所以:
767限制 = 768字节 - 1字节的列长度
也就是说不管是对于什么编码存储,最少是要有一个字节来标识列长度的,所以768-1 = 767
,也就是最多会留767
字节来存储实际数据,这也是mysql
规定索引前缀长度是767
的原因。
三、升级mysql5.7之后的长度限制为3072字节
1、mysql5.7的行格式dynamic
在mysql5.7
之后,默认的行格式变成了dynamic
行格式,它和compact
行格式的大部分特性都相同,只是针对于溢出页的定义是不一样的,如图:
2、dynamic行格式特点
对于dynamic
行格式而言,假如数据量大导致溢出的话,那么数据页中只会保存一个溢出页的地址,大概是20
个字节的指针,而对应的数据都只会存储在溢出页。
网上查询到的资料是主键长度扩展到了3072
字节,经过翻看手册,发现如果innodb_large_prefix
启用(默认值),则对于InnoDB
使用DYNAMIC
或 COMPRESSED
行格式的表, 索引键前缀限制为3072
字节。如果 innodb_large_prefix
禁用,则对于任何行格式的表,索引键前缀限制为767
字节。
mysql5.7
默认的innodb_large_prefix
是启用状态,所以对应的索引键前缀是可以设置为3072
字节的,我们也可以愉快的设置:varchar(255)
了。
3、对3072字节的猜想
首选我们知道,innodb
的每个数据页大小是16KB
,也就是16384
字节。innodb
规定每个数据页至少要有两行记录,而每行记录主要是:行数据+27个字节的额外信息。每个数据页还有页头页尾等信息,总计136字节,所以实际存储数据的N大小为:
136 + 2*(27+N) < 16384 N=8098字节
假设在数据量的大小不足以溢出数据页的时候,那么每行的长度至少要小于8098
字节,mysql
给出的索引长度小于这个值即可。至于为什么要是3072
字节,在手册上有这样一句话:
如果通过在创建MySQL实例时指定选项将InnoDB 页面大小减小到8KB或4KB innodb_page_size,则索引键的最大长度将按比例降低,基于16KB页面大小的3072字节限制。也就是说,当页面大小为8KB时,最大索引密钥长度为1536字节,当页面大小为4KB时,最大索引密钥长度为768字节。
我们可以理解为是为了当缩小数据页的大小为4KB
的时候,我们还可以按照原有的习惯去建索引,而不至于因为索引长度过小,无法正常建索引等。( PS: 这部分也是猜测,后续有新的理解就补充上去)
四、查看当前行格式,修改行格式等
1、查看当前的行格式
mysql> show table status like '表名'\G;
*************************** 1. row ***************************
Name: 表名
Engine: InnoDB
Version: 10
Row_format: Dynamic =============就是它
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 98304
Data_free: 0
Auto_increment: 62
Create_time: 2019-07-11 19:38:50
Update_time: 2019-07-22 09:59:27
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
2、新增行格式
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
3、修改行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称
======================================================================
高危预警: 这篇文章,博主经过各种谷歌之后,只是得到了心中的一部分答案。而且并没有较权威的例子来证明这些东西。顶多算是抛砖引玉吧,博主心中的疑惑解开了一些,但是并没有得到充分的验证。希望这篇文章能给你带来一点灵感,如果有什么更好的理解,请务必告诉我,非常感谢,一起学习!
end