ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

问题背景

zabbix监控导入schema.sql文件时报错，经查看找到如下对应的语句。

MySQL数据库版本：8.0.24

 

创建语句

create database zabbix;

CREATE TABLE `items` (
	`itemid`        bigint unsigned                 NOT NULL,
	`type`          integer         DEFAULT '0'     NOT NULL,
	`snmp_oid`      varchar(512)    DEFAULT ''      NOT NULL,
	`hostid`        bigint unsigned                 NOT NULL,
	`name`          varchar(255)    DEFAULT ''      NOT NULL,
	`key_info`      varchar(2048)   DEFAULT ''      NOT NULL,
	PRIMARY KEY (itemid)
) ENGINE=InnoDB;

CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));

 

报错信息：指定的key长度超过了3072bytes。

(root@localhost) [zabbix]> CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

解决步骤

查看数据库默认字符集是utf8mb4

(root@localhost) [zabbix]> select @@global.character_set_server;
+-------------------------------+
| @@global.character_set_server |
+-------------------------------+
| utf8mb4                       |
+-------------------------------+

 

utf8mb4字符集一个字符占4个字节

(root@localhost) [zabbix]> select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME='utf8mb4';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8mb4            | utf8mb4_0900_ai_ci   | UTF-8 Unicode |      4 |
+--------------------+----------------------+---------------+--------+

 

表的行格式（ROW_FORMAT）绝对索引长度：

• ROW_FORMAT= COMPACT 或 REDUNDANT，单列索引支持的最大长达为767bytes。
• ROW_FORMAT= COMPRESSED 或 DYNAMIC，单列索引支持的最大长度为3072bytes。

 

索引长度计算：

• 索引字段如果没有设置NOT NULL，则需要添加一个字节。
• 定长字段，如INT占4个字节、DATE占3个字节、CHAR(N)占N个字符。（注意字符与字节的区别，字符的长度取决于当前的字符集）
• 变长字段，如VARCHAR(N)占用N个字符+2个字节。

 

        MySQL 8.X版本单个索引列支持最大长度是3072bytes。如果创建的索引列是变长字段VARCHAR，所以支持的最大长度是(3072-1-2)/4≈767。然后需要创建的索引index_items_key指定的索引长度是1021，我又不能更改索引的长度，所以将zabbix数据库的字符集设置成utf8（也可以单独指定表的字符集，但是需要保证相同业务类型表的字符集都一样，否则会导致索引失效），然后重新导入SQL。

 

Utf8一个字符占3个字节，单个索引最大长度是(3072-1-2)/3=1023

(root@localhost) [zabbix]> select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME='utf8';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8               | utf8_general_ci      | UTF-8 Unicode |      3 |
+--------------------+----------------------+---------------+--------+

 

创建语句

create database zabbix character utf8;

CREATE TABLE `items` (
	`itemid`        bigint unsigned                 NOT NULL,
	`type`          integer         DEFAULT '0'     NOT NULL,
	`snmp_oid`      varchar(512)    DEFAULT ''      NOT NULL,
	`hostid`        bigint unsigned                 NOT NULL,
	`name`          varchar(255)    DEFAULT ''      NOT NULL,
	`key_info`      varchar(2048)   DEFAULT ''      NOT NULL,
	PRIMARY KEY (itemid)
) ENGINE=InnoDB;

CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));

 

索引创建成功

(root@localhost) [zabbix]> CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

PS：

    MySQL 8.X版本单列索引支持的最大长度默认就是3072bytes。

    MySQL 5.7版本单列索引支持的最大长度默认是768bytes，如需单列索引最大长度支持3072bytes，需配置参数innodb_large_prefix=ON。

 

参考：InnoDB Limits