• MySQL不同存储引擎的索引长度竟然不一样?
  • 发布于 1周前
  • 52 热度
    0 评论
前言
我只是创建个索引而已,没想到还这些遇到个诡异的问题!话说你平时创建mysql时遇到过以下创建索引报错的情况吗?报错提示如下:
Specified key was too long; max key length is 767 bytes 
//需要添加索引的字段的长度太长,超过了767字节
什么?索引长度限制?我从没遇到过啊,MySQL还会对索引键的长度有限制?是的,一般都不会遇到,不过这个问题我一个同事就遇到了!他用同样表结构和加索引的语法,都是相同的,但是一个测试环境和一个线上MySQL版本,一个执行成功一个执行失败!

事故现场
同事A的迭代开发测试完了,准备将环境放到预发布了,在添加索引的时候居然创建索引报错了,瞬间一脸郁闷,我测试过得了啊!是的,没错,虽然测过了,但是因为版本不同,在为数据库字段类型(varchar(255))创建索引的时候,指定的字段有可能会超过了存储引擎默认的长度!当然你会问,为啥要为varchar(255)创建索引啊,这个就不多追问了!不过刚好趁对这个问题有印象,今天可以聊聊MySQL单表对索引限制的知识点!

不同存储引擎索引长度
既然MySQL 对每个单表中所创建的索引长度是有限制,我们先看下不同的存储引擎下对表的限制是什么样的。

MyISAM
• 单列索引:最大长度不能超过 1000 bytes,否则会报警,但是创建成功,最终创建的是前缀索引(取前333个字符)。
• 联合索引:索引长度和不能超过 1000 bytes,否则会报错,创建失败

InnoDB
• 单列索引:超过 767 bytes的,给出warning,最终索引创建成功,取前缀索引(取前 255 字符)
• 联合索引:各列长度不超过 767 字节 ,如果有超过 767 bytes 的,则给出报警,索引最后创建成功,但是对于超过 767 字节的列取前缀索引,与索引列顺序无关,总和不得超过 3072 ,否则失败,无法创建

默认情况下utf编码一个字符占三个字节,也就是说在InnoDB引擎中,4个varchar(255)字段就把单表索引长度给占满了哦!3072 / 767 约等于 4, 而varchar(255),在utf8编码下是765字节,因此四个varchar(255)字段就快超过综合3072了。

MySQL版本对索引长度限制:

MySQL5.5 版本:引入了 innodb_large_prefix,用来禁用大型前缀索引,以便与不支持大索引键前缀的早期版本的 InnoDB 兼容开启 innodb_large_prefix 可以使单索引的长度限制达到 3072 字节(但是联合索引总长度限制还是 3072 字节),禁用时单索引的长度限制为 767 字节。
MySQL5.7: MySQL5.5版本与MySQL5.6 版本,innodb_large_prefix 是默认关闭的,在 MySQL5.7 及以上版本则默认开启。
MySQL8.0 版本:innodb_large_prefix 已被移除,从版本 8.0 开始,索引长度限制由行格式决定,若行格式为 DYNAMIC 或 COMPRESSED 时,限制值为 3072,而行格式 REDUNDANT 或 COMPACT 时,限制值为 767。

MySQL在5.7及以上版本在InnoDB引擎中默认行格式是 DYNAMIC,所以限制长度为3072字节。

字符集对索引长度影响
了解完存储引擎和不同MySQL版本对索引长度的限制,我们以InnoDB引擎为例,看MySQL不同的字符集对索引长度有啥影响。

未开启 innodb_large_prefix

未开启 innodb_large_prefix
MySQL5.7默认开启了innodb_large_prefix,或者更高版本,比如MySQL8

UTF8编码占用3个字节,能表示除了表情符之外的其他字符,UFT8mb4占用4个字节,既能表示汉字也能表示表情符。

解决方案
针对这个问题,一般来说可以考虑一下几种方式去处理
• 将varchar(255)字段改成更小的字符长度类型
• 如果是MySQL5.5版本与MySQL5.6 版本,可以启用innodb_large_prefix参数,来使得单个索引字段的长度突破767
• 这种大字段类型可以考虑前缀索引
毕竟varchar(255)这种长度的类型作为索引相对来说并不是那么合适!

··············  END  ··············
用户评论