MySQL索引 Error1071

MySQL创建索引错误 Error 1071:Specified key was too long

目录

• 1. 起因
• 2. 使用环境
• 3. 查找问题的原因
• 4. 解决方案
• 5. 另外的解决方案
• 6. 前缀索引

起因

事情的起因在一次生产环境部署启动服务，发现启动服务时间特别长，超过了5分钟的时间。于是，连接上服务器查看了启动的日志，发现有多个如下错误：

Error 1071：Specified key was too long; max key length is 767 bytes.

直白的讲，这个错误的原因是创建索引的字段长度，超过了最大的限制767byte，因此创建失败。

使用环境

MySQL：5.7.24
引起：InnoDB

查找问题的原因

首先，根据错误的提示查看了出错的表的索引字段的类型：

# desc tableName
Field	Type            Null    Key
BE_ID	varchar(300)    YES	MUL

发现创建索引的字段BE_ID长度为varchar(300)，创建的索引为非主键索引。紧接着，查看了数据表使用的字符集：

# show table status from dbName like 'tableName'
Name        Engine  Version    Collation
tableName   InnoDB  10         utf8_general_ci

我们知道，utf-8字符集每个字符占用1~3个字节不等，但是MySQL在计算最大长度时，在不知道数据长度的情况下只能用3个字节进行计算(个人推断)，因此300个字符至少需要300*3=900的长度才能创建出来索引，而我们使用的当前数据库所有的最大长度限制为767，因此超长了，创建索引错误也在所难免。

解决方案

既然找到的原因，那么我们是不是尝试去掉这个限制就可以了呢？经过一番网上查找，发现MySQL有个开关innodb_large_prefix就是控制这个长度的。

innodb_large_prefix	on	off
最大长度	3072	767

查看我自己的数据库，发现其状态确实是关闭状态，打开后也确实能解决此问题，于是有可以愉快的开始写bug！

另外的解决方案

打开innodb_large_prefix的开关后，确实解决了此次的问题，但是这确实是唯一的正确的解吗？不一定是的，从数理上来看，既然你超过了最大长度限制，那么减小你的索引长度是不是也是一种方案呢？

是的，后经仔细的排查，发现在字段BE_ID的长度根本不需要300字符，120个就是极限情况了，因此若吧varchar(300)=>varchar(120)，同样可以解决这里的问题。后来，经开发同学回忆(因为时间太久)，可能是考虑了未来某种扩容的情况，因此给的长度比较大，而实际上已经过去3年了，仍然没有这种扩容字段的情况发生！

那如果BE_ID的长度确确实实需要300的长度呢，我们还有什么别的办法吗？还是有点，那就是前缀索引。

前缀索引

前缀索引的创建方式

mysql> alter table tableName add index index_name_1(BE_ID);
或
mysql> alter table tableName add index index_name_2(BE_ID(20));

• index_name_1: 普通索引，即使用BE_ID全字符串作为索引对象；
• index_name_2: 前缀索引，即只使用BE_ID字符串的前20位作为索引对象；
  前缀索引，不光能解决创建索引超长的问题，也能减少索引的存储空间。但是前缀索引也不是完全没有代价的：会增加查询的扫码次数，并且不能使用覆盖索引。前缀索引会存在前缀区分度不够的情况，比如身份证的前6位，同一个县的完全一致，这时候和不用索引没甚区别了。

解决前缀索引区分度不够的问题，可以考虑倒序存储或者hash字段索引等方式，但同样也会引入其他问题，比如不支持范围扫描等。因此需要慎用！

总结：合适的才是最好的！