近期对部分业务系统数据做迁移,全量同步到数据仓库的OLTP层
(业务系统:mysql-5.7 阿里云polarDB / 数据仓库:mysql-5.7 阿里云RDS)
一、正常的操作流程:
1.单事务对数据库做 mysqldump
2.load到数仓的MySQL部分
二、出现的问题:
dump正常,在load到数仓mysql时失败
查看错误日志发现:1071 - Specified key was too long; max key length is 767 bytes, Time: 0.012000s
整个处理花了半天的时间才找到原因。。。。
三、尝试解决:
首先看报错的解释:key,索引超长,最大支持767,明显我超出了767这个长度?
接着我需要定位到底是那张表引起了这个问题,明显是很多表都有这种情况,那就先找第一个:
DROP TABLE IF EXISTS `admin_player`;
CREATE TABLE `admin_player` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`nick` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`tel` varchar(255) DEFAULT NULL,
`role` int(11) DEFAULT NULL,
`auth` int(11) DEFAULT '0',
`state` int(11) DEFAULT '1',
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB;
这里看到了,唯一索引是username,这个username长度是255,可是我之前建表也用了这个长度的唯一索引,从来没出过问题,接下来就要测试一下
测试发现确实不能建表,还是报1071 - Specified key was too long; max key length is 767 bytes
第一我明显确定这个长度是255,不是256,那么网上的一些答案对我是不适用的,当时另一个想法,可能是MySQL版本不一致,于是比较发现都是5.7.xx,也不会是版本原因造成的
第二 我查了数据库字符集:show variables like '%char%'; 这里来看,业务库和数仓MySQL都是utf8mb4,没有差异,显然不是字符问题引起的,查看引擎,也是一致的没有差异
第三 我大概可以判断出,不是版本问题,不是字符集问题,那么一定是数仓MySQL的某些参数限定了这个建表的唯一索引,因为我之前在其他的MySQL使用都是没问题的,通过各种查看数据库的参数发现,数仓的MySQL有一个参数配置:innodb_large_prefix
这个参数的解释是:表的字段索引长度限制,他有两个值:(ON、OFF),可以控制是否允许单列的索引长度超过767字节。
分析一下这个限制跟我有啥关系?`username` varchar(255),众所周知在utf8mb4的情况下存储会多耗用空间,mb4就是most bytes 4,这个字符格式是可以用来支持4字节的unicode的,varchar最大存放65535 bytes,对于username这种一段一看就是存用户名的,用户名存在较长的可能,假如这个开关是OFF,那么它最大能支持存的字节数是767,而varchar(255)最大可以存65535缺的一位表示的是符号,那么明显已经超了,所以需要打开这个开关。但是打开真的有效吗?
四、总结:
Mysql只是去SET GLOBAL innodb_large_prefix = ON 是可以的,不过阿里云RDS需要在运维后台手动修改这个参数,然后提交并重启,找OPS同事开启这个参数为ON,重启,发现是可以建表了,说明使用上是没啥问题了
不过继续刚才的问题,打开ON,就可以支持存65535字节的索引了吗,并不是,这个参数深一点的含义是扩展单列索引的长度,在Mysql 5.7版本中ON的话可以支持3072长度的单列索引,复合索引长度不变(复合索引最大长度也是3072)。
所以,以后在使用过程中如果发现建表就报Specified key was too long; max key length is 767 bytes,需要想到会不会是限定了字段索引长度导致的,这在做数据同步和迁移时可能还会再遇到。