mysql索引过长Specialed key was too long的解决方法

欢迎来到蓝队云技术小课堂，每天分享一个技术小知识。

在创建要给表的时候遇到一个有意思的问题，提示Specified key was too long; max key length is 767 bytes，从描述上来看，是Key太长，超过了指定的 767字节限制。通常出现在尝试创建一个过长的唯一键（UNIQUE KEY）或主键（PRIMARY KEY）时。MySQL对于InnoDB存储引擎有一个索引键长度的限制，这个限制基于字符集的不同而不同。

下面是产生问题的表结构

CREATE TABLE `test_table` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(1000) NOT NULL DEFAULT '',

  `link` varchar(1000) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

在使用utf8字符集时，每个字符可能占用3个字节，那么对于innodb表，索引键的最大长度大约为1000个字符左右（因为3072 / 3 ≈ 1024）。若字符集是utf8mb4，每个字符可能占用4个字节，所以最大长度会进一步减少到768个字符左右（3072 / 4 = 768）

uf8mb4字符集:

要在 Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集（mb4就是most bytes 4的意思，专门用来兼容四字节的unicode），但只有 5.5.3 版本以后的才支持。

为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。其实，utf8mb4是utf8的超集，理论上原来使用utf8，然后将字符集修改为utf8mb4，也会不会对已有的utf8编码读取产生任何问题。当然，为了节省空间一般情况下使用utf8也就够了。

对于name，我们设置长度为1000可变字符，因为采用utf8mb4编码, 所以它的大小就变成了 1000 * 4 > 767

所以再不修改其他配置的前提下，varchar的长度大小应该是 767 / 4 = 191

有兴趣的同学可以测试下，分别指定name大小为191, 192时，是不是前面的可以创建表成功，后面的创建表失败，并提示错误Specified key was too long; max key length is 767 bytes

解决办法

使用innodb引擎

启用innodb_large_prefix选项，修改约束扩展至3072字节

重新创建数据库

my.cnf配置

set global innodb_large_prefix=on;

set global innodb_file_per_table=on;

set global innodb_file_format=BARRACUDA;

set global innodb_file_format_max=BARRACUDA;

上面这个3072字节的得出原因如下

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。

所以一个记录最多不能超过8k。又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。

由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)。

在创建表的时候，加上 row_format=DYNAMIC

CREATE TABLE `test_table` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL DEFAULT '',

  `link` varchar(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 row_format=DYNAMIC;

代码解读

这个参数的作用如下

MySQL 索引只支持767个字节，utf8mb4 每个字符占用4个字节，所以索引最大长度只能为191个字符，即varchar(191)，若想要使用更大的字段，mysql需要设置成支持数据压缩，并且修改表属性 row_format ={DYNAMIC|COMPRESSED}

蓝队云官网上拥有完善的技术支持库可供参考，大家可自行查阅，更多技术问题，可以直接咨询。同时，蓝队云整理了运维必备的工具包免费分享给大家使用，需要的朋友可以直接咨询。

更多技术知识，蓝队云期待与你一起探索。