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字段索引等方式，但同样也会引入其他问题，比如不支持范围扫描等。因此需要慎用！
总结：合适的才是最好的！