MySQL 经典案例分析:Specified key was too long

现在的时间是23:38,拖着疲惫的身躯,还是决定记录一下今天遇到的一个关于Mysql的”诡异”问题。(其实决定记录的原因:一是好久没有在KM上刷存在感了;二是星期五晚上的大好时光,竟然一晃又过去了,心中愤愤不平)

圆规正传,下面就详细的从问题的起因、发现、探索和解决,分享一下自己的心路历程。

一、 问题是这样来的

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时,最终生成的表与其建表语句中设置的一致。

这个问题暂且就当做经验来记吧,至于其中的原因估计对于我这样经验和学识不够老道的DBA来说,确实要慢慢研究和探索了。看了一看时间01:45,今天就分享到这里吧,各位晚安。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Star先生的专栏

Tensorflow 术语表

本文主要简要介绍了广播操作、Graph(图)、Session(会话)、Tensor 等13个 Tensorflow 术语表。希望对大家了解学习 Tensorfl...

1.2K10
来自专栏吕晟的专栏

机器学习库初探之 Caffe

Caffe 是一个清晰而高效的深度学习框架,其作者是博士毕业于 UC Berkeley 的贾扬清,目前在 Facebook 工作。Caffe 是纯粹的 C++/...

1.5K20
来自专栏织云平台团队的专栏

腾讯赵建春:AI浪潮下的高效运维思考及实践

腾讯 SNG 助理总经理、GOPS 金牌讲师赵建春老师受邀出席大会,并带来精彩演讲《AI 浪潮下的高效运维思考与实践》。

79410
来自专栏程飞翔的专栏

XGBoost 源码阅读笔记(2):树构造之 Exact Greedy Algorithm

本篇将继续向大家介绍 XGBoost 源码是如何构造一颗回归树,不过在分析源码之前,还是有必要先和大家一起推导下 XGBoost 的目标函数。

1.1K10
来自专栏社区的朋友们

一个 RNN 调研引发的点评推荐血案

在这篇文章里,作者基于用户的评论内容,评估了十多种不同的 RNN 框架。这些 RNN 框架包括多层双向 GRU 和 LSTM,同时有 attention-bas...

1.8K00
来自专栏社区的朋友们

机器学习概念总结笔记(一)

本部分介绍了机器学习算法的四大分类,即:监督学习、半监督学习、无监督学习和增强学习以及包括最小二乘回归、岭回归、LASSO回归、LARS回归在内的26大常见算法...

1.6K40
来自专栏腾讯移动品质中心TMQ的专栏

Tensorflow 的 word2vec 详细解释:basic篇

Word2Vec即Word to vector(词汇转向量)。我们希望词义相近的两个单词,在映射之后依然保持相近,词义很远的单词直接则保持很远的映射距离。

1.6K40
来自专栏利炳根的专栏

学习笔记DL001 : 数学符号、深度学习的概念

深度学习是机器学习拉出的分支,它试图使用包含复杂结构或由多重非线性变换构成的多个处理层对数据进行高层抽象的算法。本文主要介绍深度学习中的数学符号、数和数组。

59100
来自专栏腾讯AI实验室的专栏

腾讯AI Lab斩获知识图谱顶级赛事KBP 2017世界冠军

感谢阅读腾讯AI Lab微信号文章,恭喜知识图谱团队首次出战KBP大赛就获得实体发现与链接任务三语总分第一名好成绩!

18110
来自专栏腾讯AI实验室的专栏

腾讯AI Lab斩获知识图谱顶级赛事KBP 2017世界冠军

谢阅读腾讯AI Lab微信号文章,恭喜知识图谱团队首次出战KBP大赛就获得实体发现与链接任务三语总分第一名好成绩!

12110

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励