[转]MySQL 经典案例分析：Specified key was too long索引字段长度过长
原文地址:https://segmentfault.com/p/1210000010686697/read
一、 问题是这样来的
BG内部要进行数据库的容灾演习，需要模拟线上实际的环境进行测试，这就需要copy一份线上的数据库到测试库中，其实也就是重建一个线上数据库。要完成这个任务其实有N种不同的做法，但是我当时一想：“好久没有测试一下数据备份是否正常了。”就决定利用备份数据进行重建测试库，于是乎就导出了一份XXXX.sql文件，直接往测试库里面导入，由于线上库的实际数据量还不小，所以当我按完“Enter”后就决定下班回家，估计等我到家了就差不多完成了。
结果竟然出问题了，屏幕上出现：
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
二、 苦逼的探索
对于报错信息“ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”，其实意思就是“索引字段长度太长，超过了767bytes”。
mysql的varchar主键只支持不超过767个字节或者768/2=384个双字节 或者767/3=255个三字节的字段 而GBK是双字节的，UTF8是三字节的。
那么问题来了，为什么线上业务库中正常的数据直接移植到另一个库，竟然会报设置表中字段不合法的错！
(1)字符集设置不同？
第一个想法是查看两个库的字符集设置是不是不一样，果然线上库的字符集character_set_database=lantin1，而测试库的字符集character_set_database= utf8。这时候，我很天真的以为可能是因为建表的时候没有指定字符集，所以在导入测试库的时候默认使用utf-8字符集，导致Specified key was too long。所以就更改了测试库中默认的字符集，但是导入数据的时候依然还是报同样的错误。而且我在建表的语句中发现了，其实原来就已经指定了字符集，都是UTF8(CHARSET=UTF8)，所以字符集设置不同并不是问题的症结所在。
(2)更改索引字段长度？
定位到出错的建表语句，果然是使用UTF8字符集，而且长度是256（如下图所示）。
为了验证索引字段的字符集设置为utf8时，varchar（256）确实是超长了，做了如下的测试：
那么问题简单了，更改tagvarchar（256）为tagvarchar（255），然后再进行导入数据，竟然成功了。但是依然后两个困惑在我脑海里，
① 为什么线上库可以设置tagvarchar（256）？
② 要是tag字段刚好有256个字节的数据，那么转存的时候，数据不就丢失了？
（3）存储引擎惹的祸？
当我去检查刚刚那张被我手动修改过的表的状态时，意想不到的发现了一个问题，为什么建表的语句明明写的是“ENGINE=MYISAM”，但是导入后的表变成了“ENGINE=InnoDB”。（如下图所示）
再仔细一回想，刚刚竟然只记得索引字段长度不能超过768，但是忘了innodb和myisam的区别了。
① innodb存储引擎，多列索引的长度限制如下：
每个列的长度不能大于767 bytes；所有组成索引列的长度和不能大于3072 bytes
② myisam存储引擎，多列索引长度限制如下：
每个列的长度不能大于1000 bytes，所有组成索引列的长度和不能大于1000 bytes
原来是两张表的存储引擎不同，这样就解释了刚刚的两个疑问，但是又一个疑问就出现了，为什么建表语句中明明写的是MYISAM表，怎么导入之后就变成了INNODB。（经过对比，发现所有的MYISAM表都被转换成了INNODB表）
这个问题暂且先搁置，紧急的是要怎么规避。先分别查了下两个数据库的默认存储引擎，果然发现在线上库中default_storage_engine和storage_engine都是MYISAM，而测试库却均是INNODB。（如下图所示）
通过修改默认的存储引擎:
1)在my.cnf中的 [mysqld] 下加入default-storage-engine=INNODB ，保存（需重启）。
2）set global default_storage_engine="InnoDB"
通过这种方法终于保证了不修改源数据文件的情况下，能正确的重建线上数据至测试库，并且核对了数据和存储引擎，均与线上库一致。
（4）InnoDB存储优化选项？
问题虽然是解决了，但是难道InnoDB中单个索引字段的长度真的只能小于767？
又经过一番探索，发现在InnoDB中，可以启用启用innodb_large_prefix参数，来使得单个索引字段的长度突破767。
注意：
①启用innodb_large_prefix参数能够取消对于索引中每列长度的限制(但是无法取消对于索引总长度的限制)
②启用innodb_large_prefix必须同时指定innodb_file_format=barracuda，innodb_file_per_table=true，并且建表的时候指定表的row_format为dynamic或者compressed(mysql 5.6中row_format默认值为compact)
具体的操作如下：
① 查看innodb_large_prefix，innodb_file_format参数
mysql> show variables like 'innodb_large_prefix';
+-------------------------+---------------+
| Variable_name | Value |
+--------------------------+--------------+
| innodb_large_prefix | OFF |
+--------------------------+--------------+
② 查看mysql> show variables like 'innodb_file_format';
+-------------------------+---------------+
| Variable_name | Value |
+-------------------------+---------------+
| innodb_file_format | Antelope |
+-------------------------+---------------+
③ 建索引测试(innodb_large_prefix,innodb_file_format都为默认值的情况下)
mysql> create table test (id varchar(256),key (id));
mysql>create table test (id varchar(255),name varchar(255),name1 varchar(255),name2 varchar(255),name3 varchar(5),key (id,name,name1,name2,name3));
##索引列大于767 bytes时报错，组成索引列总长度大于3072 bytes时报错
④ 修改innodb_large_prefix,innodb_file_format参数
mysql> set global innodb_large_prefix=1;
mysql> set global innodb_file_format=BARRACUDA;
⑤ 对row_format为dynamic格式表创建索引测试
mysql>create table test(id varchar(256),key (id)) row_format=dynamic;
mysql>create table test(id varchar(255),name varchar(255),name1 varchar(255),name2 varchar(255),name3 varchar(5),key (id,name,name1,name2,name3)) row_format=dynamic;
##innodb_large_prefix=1并且innodb_file_format=BARRACUDA时，对于row_format为dynamic的表可以指定索引列长度大于767 bytes。但是索引列总长度的不能大于3072 bytes的限制仍然存在。
三、 有待深思的细节
相信大家一定还记得上面我们跳过的一个问题：为什么建表语句中明明写的是MYISAM表，怎么导入之后就变成了INNODB。于是乎我做个2组简单的测试：
① 设置数据库default_storage_engine="InnoDB"，将含有InnoDB和MYISAM的库导入。
② 设置数据库default_storage_engine=”MYISAM"，将含有InnoDB和MYISAM的库导入。
实验结果是，default_storage_engine设置成InnoDB时，不论建表语句指定的是InnoDB或MYISAM，最终生成的表均为InnoDB；设置成MYISAM时，最终生成的表与其建表语句中设置的一致。
