小白学习MySQL - 索引键长度限制的问题

最近在工作中,碰到了个很诡异的问题,需求是在两个MySQL数据库为同一张表增加一个二级索引(单键值字段(x varchar(500))),表结构和加索引的语法,都是相同的,但是一个库执行成功了,一个执行失败了,提示错误如下,

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


从字面的意思看,是说指定键超长,而且上限是767字节。这是什么意思?MySQL中还会对索引键的长度有限制?


我们首先从《MySQL 5.6 Reference Manual》的"CREATE INDEX"章节看起,他指出如果是字符串类型的字段,可以指定字符串前多少位创建索引键值,而且键值前缀是存在上限的,在CREATE TABLE、ALTER TABLE、CREATE INDEX语句中,对于非二进制的字符串类型(CHAR、VARCHAR、TEXT),前缀会按照字符个数计算,对二进制的字符串类型(BINARY、VARBINARY、BLOB),前缀会按照字节个数计算,因此,当对非二进制的字符串列明确前缀长度的时候,需要考虑多字节字符集的因素,

小白学习MySQL - 索引键长度限制的问题

P.S. https://dev.mysql.com/doc/refman/5.6/en/create-index.html


前缀的长度限制,是和存储引擎相关的。如果用的是InnoDB,前缀上限是767字节,当启用innodb_large_prefix时,上限可以达到3072字节。如果用的是MyISAM,前缀上限是1000字节。NDB存储引擎,则根本就不支持前缀这种形式。


之所以可以定义一个字段前缀作为键值,存储效率是考虑的一个因素,如果列名的前10个字符通常都是不同的,检索这10个字符创建的索引应该会比检索整个列作为索引的效率更高,使用列前缀作为索引会让索引树更小,不仅节省空间,还可能提高插入操作的速度。


再看一下《MySQL 5.7 Reference Manual》,相同章节中,多了这段描述,是说当使用CREATE INDEX时,如果指定的索引前缀长度超过了列定义的长度上限,则会出现以下两种场景,

  • 非唯一索引,如果设置innodb_strict_mode=on,该操作就会抛出一个错误,禁止执行,如果设置innodb_strict_mode=off,则索引会自动按照列定义的长度上限进行创建,只会提示一个warning。
  • 唯一索引,无论设置innodb_strict_mode与否,都会提示错误,禁止执行,因为这可能导致非唯一的值插入的到表中,违反唯一性约束。

小白学习MySQL - 索引键长度限制的问题

P.S. https://dev.mysql.com/doc/refman/5.7/en/create-index.html


《MySQL 8.0 Reference Manual》的内容和5.7相同,不再展示。这个问题在5.6上测,innodb_strict_mode=off,依然会提示错误,说明在5.7以上,对这个问题的容忍度降低了,

create table t1(id varchar(10));
alter table t1 add index idx_t1_01 (id(15));
SQL 错误 [1089] [HY000]: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys


再回到开始问题,一个库创建索引成功了,一个失败了,首先看下两个库的版本,确实不同,成功的是5.7,失败的是5.6.22。


我们先来看下5.7,开启了innodb_large_prefix,Row_format是Dynamic,表定义的字符集utf8,因为要加索引的字段定义是varchar(500),允许存储500个字符,utf8的一个字符是3个字节,500个字符就是1500个字节,从文档我们知道,因为设置了innodb_large_prefix,所以键值上限是3072个字节,1500<3072,加索引的操作,能正常执行,

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.01 sec)

Row_format: Dynamic

CHARSET=utf8


再来看5.6,小版本号是5.6.22,未开启innodb_large_prefix,Row_format是Compact,表定义的字符集utf8,因为要加索引的字段定义是varchar(500),允许存储500个字符,utf8的一个字符是3个字节,500个字符就是1500个字节,从文档我们知道,未设置innodb_large_prefix,所以键值上限是767个字节,1500>767,加索引的操作,不能执行,

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF    |
+---------------------+-------+
1 row in set (0.01 sec)

Row_format: Compact

CHARSET=utf8


但是更奇怪的,碰巧我在5.6.44小版本进行测试,这个和5.6.22相同的操作过程,竟然能执行,只是提示了warning,

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.44-log |
+------------+
1 row in set (0.00 sec)

mysql> create table t(id varchar(500))
Query OK, 0 rows affected (0.08 sec)

mysql> alter table t add index (id);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)


但是通过客户端,能看到这个索引键的长度,限制为了255,按照计算,255*3=765<767,这个应该是utf8字符集能支持字符个数上限,

小白学习MySQL - 索引键长度限制的问题小白学习MySQL - 索引键长度限制的问题

P.S. 请教一下,如果从命令行,看索引键值长度,应该执行什么?


说明5.6.44对超过索引键值上限的情况,允许增加索引,但是会自动截取。

在5.6.22和5.7对超过索引键值上限的情况,直接禁止执行。


个人理解,对待这种超过索引键值上限的情况,禁止执行,是合理的,因为如果自动对索引前缀进行截取,很可能出现截取的部分无法进行区分,不能起到过滤效果了,即使设置了innodb_strict_mode,都是只提示warning,不是错误error,因此很可能5.6.44对这个场景的支持是个bug,或者以后的版本,关闭了这个特性。


如果这个问题在5.6.22下要执行成功,可能有几种方案,

1. 缩小字段长度,例如x varchar(500),改为x varchar(255)。

2. 创建索引的时候,指定前缀长度,alter table ... add index ... (x(255)),能不能这么做,需要根据字段内容来决定。

3. 开启配置innodb_large_prefix,innodb_file_format改为Barracuda,row_format改为Dynamic,限制就从767改为了3072。

P.S. 这几个参数都是全局改的,能不能改,有什么影响,可能还得评估下。

mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%innodb_file_format%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Antelope  |
+--------------------------+-----------+
3 rows in set (0.00 sec)

mysql> alter table t1 row_format=dynamic;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 add index idx_t1_01(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


对这个问题,从另一个角度,即使是767字节限制,换算成utf8,或者utf8mb4,至少是255或者191个字符的长度,一个单键值索引,如果达到这长度,就得考虑下索引字段的选择是否合理了,当然如果是几个字段组成的复合索引,达到这个长度,合理不合理,就得实际评估了。


MySQL刚开始接触,从这个案例,能体会到他的参数很多很碎,而且环环相扣,这真是得靠经验积累了。

上一篇:使用ABAP代码创建S/4HANA里的Sales Order


下一篇:iOS开发-Block回调