mysql InnoDB引擎索引超过长度限制

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

为什么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

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

索引覆盖扫描
通过索引的排序(order by, group by)
修改索引限制长度需要在my.ini配置文件中添加以下内容,并重启:

#修改单列索引字节长度为767的限制,单列索引的长度变为3072

innodb_large_prefix=1
# Antelope(羚羊)是Built-in-InnoDB(MySQL内置的InnoDB)支持文件格式的代号,有两种“数据表格式”(row_format):Redundant(冗余)、Compact(紧凑)
#Barracuda(梭子鱼)是InnoDB Plugin支持的文件格式,在原来的基础上新增了两种数据表格式的支持:Dynamic 和 Compressed
#innodb_file_format在配置文件中指定;row_format则在创建数据表时指定
#同时Barracude也支持 old redundant and compact row formats
innodb_file_format=BARRACUDA
#InnoDB Plugin引入的新的文件格式,也引入较为完整的文件兼容性检查,以防止误操作非兼容的文件格式。兼容性检查一共有三类:启动数据库时、创建数据表时、访问数据表时。
#当数据库启动时候,参数innodb_file_format_check(>=5.1.38)会要求InnoDB在启动时检查当前数据表的格式。设置为ON时,如果检测到不支持的格式,那么InnoDB会启动失败;设置为OFF时,检测到不支持的仅会给出警告,并不会导致启动失败。
#当创建数据表时,InnoDB会依据参数InnoDB_file_format进行检查,如果创建的数据表格式高于InnoDB_file_format,则创建会失败。
#当访问某个数据表(table-access)时,InnoDB也会进行兼容性检查。只要当前运行的InnoDB版本能够支持的格式,都能够被访问,无论参数InnoDB_file_format的配置。
#把innodb_file_format_check设置为OFF是很危险的。在InnoDB启动后,一般需要做一些恢复工作,例如Double write buffer/Insert buffer中的数据处理(这依赖于innodb_fast_shutdown参数),试想如果成功启动,但是某些表是不支持的格式,但是InnoDB仍然安装旧版本做恢复,这可能会毁掉相关数据。
#所以,一般建议innodb_file_format_check设置为ON。如果是OFF,关闭InnoDB的innodb_fast_shutdown参数务必设置成0
innodb_file_format_check=1

在创建表的时候需要指定表的行格式为dynamic  ROW_FORMAT=DYNAMIC;
---------------------

上一篇:项目开发中如何规范自己的CSS


下一篇:spring boot 中active的profile会和标准配置合并吗