MySQL:Specified key was too long

距上一篇更新又一个多月了，再次感觉“坚持”这个品质的不容易与可贵。

最近没研究什么新的，这次就分享一个近期遇到的关于 MySQL 的知识点。

某项目用到 xxl-job（一个开源的定时任务管理项目），其中有一个表其建表语句为

CREATE TABLE `xxl_job_registry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registry_group` varchar(50) NOT NULL,
  `registry_key` varchar(255) NOT NULL,
  `registry_value` varchar(255) NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_g_k_v` (`registry_group`,`registry_key`,`registry_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在本地测试环境部署项目创建表时没发现什么问题，上线时在线上数据库执行建表时报如下错

Specified key was too long; max key length is 767 bytes

字面意思是指定的索引列太长，最大长度为 767 个字节。

通过查资料（其实就是B度 'Specified key was too long'）了解，MySQL 的 varchar 类型的索引列只支持不超过 767 个字节， 或者 768/2=384 个双字节， 或者 767/3=255 个三字节， 或者 767/4=191 个四字节 的字段。

Mysql 不同编码字符对应的字节数如下表

其中 utf8mb4 通常用于需要存储 emoji 表情的场景，如微信那些乱七八糟的昵称。

前面表格使用的 utf8mb4 编码， 所以索引列最多只能 767/4=191 个字符，其中两列定义 255 个字符，因而报错。

事情到这里就完了吗？前面说在本地测试环境创建没出现什么问题，在线上数据库执行就报错，事情好像还没完。

莫非是测试数据库用的编码不一样？一样的建表语句指定编码，应该是一致的。可通过命令 SHOW CREATE TABLE xxl_job_registry;
确认。

继续探索，发现 InnoDB 存储引擎对联合索引（多列索引）的限制为：每个列的长度不能大于 767 bytes，所有组成索引列的长度之和不能大于 3072 bytes。

这个限制可以通过 innodb_large_prefix 参数来修改，这个参数可设置的值及含义如下：

• ON: InnoDB 表的行记录格式是 Dynamic 或 Compressed 的前提下，单列索引及联合索引的总长度上限不能大于 3072 个字节
• OFF: InnoDB 表的单例索引长度最多为 767 个字节，索引长度超出后，主键索引会创建失败，辅助索引会被截断成为前缀索引；联合索引总长度最大为 3072 个字节，且组成联合索引的各列最大长度为 767 个字节

innodb_large_prefix 在 MySQL 5.7 默认为 ON， 在 MySQL 5.6 中默认为 OFF。

查看测试环境与线上环境数据库，版本皆为 5.7， 再查看 innodb_large_prefix 参数值，

show variables like 'innodb_large_prefix';

发现测试环境为 ON， 线上环境为 OFF。这就是为啥测试环境没问题，线上报错的原因！

知道了问题的原因，如何来解决？结合场景考虑，至少有三种方案：

1. 减小字段长度，如将 registry_key， registry_value 都改为 varchar(191)。该方案限制了数据长度，可能对数据造成影响，不太妥。
2. 将 innodb_large_prefix 设置为 ON，放开对单列的限制。该方案涉及到改线上库的配置，风险可能较大。
3. 使用前缀索引，将联合索引的 registry_key， registry_value 字段只取前面 191 个字符作为索引列。该方案影响最小。

于是将建表语句改为

CREATE TABLE `xxl_job_registry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registry_group` varchar(50) NOT NULL,
  `registry_key` varchar(255) NOT NULL,
  `registry_value` varchar(255) NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_g_k_v` (`registry_group`,`registry_key`(191),`registry_value`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

成功完成建表。

就不凑字数了。