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

bisal的个人杂货铺 2021-04-07

3153

最近在工作中，碰到了个很诡异的问题，需求是在两个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)，前缀会按照字节个数计算，因此，当对非二进制的字符串列明确前缀长度的时候，需要考虑多字节字符集的因素，

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与否，都会提示错误，禁止执行，因为这可能导致非唯一的值插入的到表中，违反唯一性约束。

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字符集能支持字符个数上限，

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