innodb主键的长度为什么不能大于767字节

最新推荐文章于 2024-11-08 08:30:00 发布

原创最新推荐文章于 2024-11-08 08:30:00 发布 · 3.6k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #compact #dynamic #索引长度 #767

mysql 专栏收录该内容

61 篇文章

订阅专栏

本文解析MySQL中索引长度限制的由来，探讨不同行格式下主键及索引长度的变化，尤其针对767与3072字节限制进行深入分析。

文章目录

一、前言

这篇文章是基于之前的一篇：导入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