当MySQL版本不一致,尤其是发生较大的版本变动时,总是容易引发一些灾难。今天我们就来谈一谈不同版本下 MySQL 索引引发的问题。


MySQL脚本崩溃现场


下面是一段看起来人畜无害的优良代码,有编码有注释,完全符合SQL规范。

create table if not exists `todo_msg` (
`id` varchar(32) not null COMMENT '主键ID',
`code` varchar(20) default null COMMENT '任务编码',
`name` varchar(255) default null COMMENT '任务名称',
primary key (`id`),
key `idx` (`name`)
) ENGINE = InnoDB default CHARSET = utf8mb4 COMMENT = '待办信息表';


该段代码在测试环境、生产环境运行毫无问题。然而运维人员将这段代码交付到客户现场时,发生了如下悲剧:


FBI WARNING - Specified key was too long; max key length is 767 bytes

事故分析


从上面异常日志,我们能够看出是索引字段超长引发的灾难。接下来,我们对索引字段进行分析。


索引字段name的字符长度为 255,而MySQL异常信息提示 最大允许的字节长度为:767 字节。所以​我们要分析一下,字符与字节的关系。


字符与字节

在不同的编码中,一个字符所占的字节长度不同。

  • gbk编码:一个汉字占2个字节
  • utf8编码:一个汉字占3个字节
  • utf8mb4编码: 一个汉字占4个字节


那么767字节能够存储多少个字符呢?


经计算,utf8mb4编码下的767字节大约能够存储 767/4 = 191 个字符, 而utf8编码下767字节大约能够存储 255个字节。


因为gbk编码无法解决汉子的存储问题,utf8解决了大部分的汉子存储问题,所以在utf8mb4编码出现以前,很长一段时间数据库都是以 utf8 字符集进行编码的。 受限于默认索引的最大字节长度为 767, 所以大部分时间,我们都将 MySQL的字段长度设置为 255个字符。


切换到 utf8mb4字符集后,767个字节最大只能存储191个字符,所以导致了上面的SQL灾难。


MySQL的最大索引长度


MySQL的InnoDB存储引擎存在索引长度限制,该限制与参数 innodb_large_prefix 相关。

系统变量innodb_large_prefix开启了,则对于使用DYNAMIC或COMPRESSED行格式的InnoDB表,索引键前缀限制为3072字节

如果禁用innodb_large_prefix,不管是什么表,索引键前缀限制为767字节


可以通过 show variables; 命令系统的环境变量。


敲黑板


MySQL 5.6 版本引入 innodb_large_prefix, 该版本 innodb_large_prefix 默认值是关闭的。

MySQL 5.7 版本, innodb_large_prefix 默认值是 ON。

MySQL 8.X 版本,移除了 innodb_large_prefix 值。 当ROW_FORMAT为 DYNAMIC 或 COMPRESSED 时,索引前缀限制为3072字。节。 当当ROW_FORMAT为 REDUNDANT 或 COMPACT 时, 索引前缀长度为 767字节。

MySQL 从5.7.9 版本后,ROW_FORMAT 默认为 DYNAMIC。


终极解决方案


解决方案一

针对 MySQL 5.6版本,修改 innodb_file_format 为 Barracuda,同时修改表的 row format 为 DYNAMIC,使得最大索引长度增加到 3072 字节。


alter table t row_format=dynamic


解决方案二


创建表结构时,指定索引的最大长度范围, 如190


create table if not exists `todo_msg` (
`id` varchar(32) not null COMMENT '主键ID',
`code` varchar(20) default null COMMENT '任务编码',
`name` varchar(255) default null COMMENT '任务名称',
primary key (`id`),
key `idx` (`name`)(190))
) ENGINE = InnoDB default CHARSET = utf8mb4 COMMENT = '待办信息表';


悟空编辑器体验


上面的问题总结方案都是在工作中随手记录的,记录在工程中的 MD 文件中,原来要将 MD 中的文档发布到博客中,需要手动字体标题转换,这次使用新的悟空编辑器,直接拷贝粘贴就可以了。能够保留MD 中的格式和样式,可以大大减少发布博客的时间。


另外,刚刚使用了编辑器中的 格式刷 功能,统一使用一致的标题样式。然后在 格式刷 时,发现只是刷新了字体和字号, 颜色没有刷到。 不知道这是编辑器的Bug,还是所有的格式刷规范。


悟空编辑器,越来越给力!