倾斜的镜子

记录,分享,积累,收获,和你一起

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

有同学问到InnoDB的索引长度问题,简单说几个tips。


MySQL的每个单表中所创建的索引长度是有限制的,且对不同存储引擎下的表有不同的限制。

  • myisam表,单列索引,最大长度不能超过 1000 bytes,否则会报警,但是创建成功,最终创建的是前缀索引(取前333个字符)。
  • myisam表,组合索引,索引长度和不能超过 1000 bytes,否则会报错,创建失败;
  • innodb表,单列索引,超过 767 bytes的,给出warning,最终索引创建成功,取前缀索引(取前 255 字符)。
  • innodb表,组合索引,各列长度不超过 767 bytes ,如果有超过 767 bytes 的,则给出报警,索引最后创建成功,但是对于超过 767 字节的列取前缀索引,与索引列顺序无关,总和不得超过 3072 ,否则失败,无法创建。

测试:

作者只对mysql innodb 引擎,utf8字符集定义的表做了实际的测试,myisam留给读者

版本:

新建测试表:

组合索引中有大于 767 bytes的字段,产生告警

结合上边两个测试结果,可以看到组合索引长度之和大于 767 bytes并无影响,当有某个字段定义长度大于 767 bytes(1000*3)时,仅产生告警,但不影响创建,超长字段会取前 255 字符作为前缀索引,并且组合索引中字段出现的顺序并无关系。

可以看到,由于每个字段占用255*3, 因此这个索引的大小是3825>3072,报错。

为什么3072

InnoDB一个page的默认大小是 16 k。由于是Btree组织,要求叶子节点上一个page至少包含两条记录(否则就退化链表了)。所以一个记录最多不能超过 8 k。又由于InnoDB的聚簇索引结构,一个二级索引要包含主键索引,因此每个单个索引不能超过 4 k(极端情况,pk和某个二级索引都达到这个限制)。由于需要预留和辅助空间,扣掉后不能超过 3500 ,取个“整数”就是(1024*3)。

单列索引限制

默认情况下,InnoDB 引擎单一字段索引的长度最大为 767 字节,同样的,前缀索引也有同样的限制。当使用 UTF-8 字符集,每一个字符使用 3 字节来存储,767=256*3-1,在 TEXT 或者 VARCHAR 类型的字段上建立一个超过 255 字符数的前缀索引时就会遇到问题。至于为什么字符长度限制在 256 内,我猜是为提高索引效率,应为varchar类型需要额外的字节保留其长度信息,256 就将其限定在一个字节了。但是在5.5以后,开始支持4个字节的uutf8。255×4>767, 于是增加了一个参数叫做innodblargeprefix。这个参数默认值是OFF,当改为ON时,允许列索引最大达到 3072 字节。要求表的 row_format 需要使用 compressed 或者 dynamic。

主要字符集的计算方式:

  • latin1 = 1 byte = 1 character
  • uft8 = 3 byte = 1 character
  • gbk = 2 byte = 1 character

使用前缀索引带来的风险:

INNODB的索引会限制单独Key的最大长度为 767 字节,超过这个长度必须建立小于等于 767 字节的前缀索引。 此外,BLOB和TEXT类型的列只能创建前缀索引。 前缀索引能提高索引建立速度和检索速度,但是下面情况是无法使用前缀索引的:

  1. 索引覆盖扫描
  2. 通过索引的排序(order by, group by)

还是在上面的测试表上:

author:bill

2015年 12月 08日

参考

  1. mysql索引长度的一些限制
  2. 关于InnoDB索引长度限制的tips
posted on 2015-12-08 01:09  倾斜的镜子  阅读(9410)  评论(0编辑  收藏  举报