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

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

  1. 问题

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

1
2
3
4
5
6
7
8
9
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 个字节。

  1. MySQL 索引列限制与字符编码

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

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

编码 字符占的字节数 汉字占的字符数 说明
latin1 1 2 varchar(100),表明它可以存储 50 个汉字或者 100 个字母
gbk 2 1 varchar(100),表明它可以存储 100 个汉字或者 100 个字母
utf8 3 1 varchar(100),表明它可以存储 100 个汉字或者 100 个字母
utf8mb4 4 1 varchar(100),表明它可以存储 100 个汉字或者 100 个字母

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

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

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

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

  1. InnoDB 存储引擎索引列限制

继续探索,发现 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. 解决方案

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

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

于是将建表语句改为

1
2
3
4
5
6
7
8
9
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;

成功完成建表。

  1. 总结

就不凑字数了。

评论