上云无忧 > 文档中心 > 百度智能云云数据库RDS innodb_large_prefix参数使用详解
云数据库 RDS
百度智能云云数据库RDS innodb_large_prefix参数使用详解

文档简介:
MySQL的InnoDB存储引擎的表存在一系列的限制条件,其中比较常见的一种是表的字段索引长度限制,该限制与参数innodb_large_prefix相关。 问题现象: 执行如下两个建表SQL操作,都返回了报错信息,导致建表失败。 for MySQL 版
*此产品及展示信息均由百度智能云官方提供。免费试用 咨询热线:400-826-7010,为您提供专业的售前咨询,让您快速了解云产品,助您轻松上云! 微信咨询
  免费试用、价格特惠

背景

MySQL的InnoDB存储引擎的表存在一系列的限制条件,其中比较常见的一种是表的字段索引长度限制,该限制与参数innodb_large_prefix相关。

问题现象

执行如下两个建表SQL操作,都返回了报错信息,导致建表失败:

  • 报错信息: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 3072 bytes

原因分析

导致上面报错的原因是由于InnoDB表的索引长度限制,在MySQL5.6版本后引入了参数innodb_large_prefix可以解决这个问题。该参数控制是否允许单列的索引长度超过767字节,有ON和OFF两个取值:

  • ON :Innodb表的行记录格式(row format)是Dynamic或Compressed的前提下,单列索引长度上限扩展到3072个字节
  • OFF:Innodb表的单例索引长度最多为767个字节,索引长度超出后,主键索引会创建失败,辅助索引会被截断成为前缀索引

解决方案

  • 使用前缀索引,减少字段索引长度
  • 设置MySQL的全局参数innodb_large_prefix=ON,将InnoDB表的索引长度上限扩大到3072个字节

案例复现

测试环境

MySQL内核版本:5.7
 ROW_FORMAT         = Dynamic | Compressed
 innodb_page_size   = 16K
 innodb_file_format = Barracuda

测试过程

建表语句 innodb_large_prefix=OFF innodb_large_prefix=ON
CREATE TABLE tb_01(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id,column1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
建表失败
报错:Specified key was too long; max key length is 767 bytes
建表成功
CREATE TABLE tb_02(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY idx01(column1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
建表成功
索引字段被截断为前缀索引:
KEY idx01 (column1(255))
建表成功
CREATE TABLE tb_03(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
column2 varchar(256) NOT NULL DEFAULT '',
column3 varchar(256) NOT NULL DEFAULT '',
column4 varchar(256) NOT NULL DEFAULT '',
column5 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id,column1,column2,column3,column4,column5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
建表失败
报错信息:Specified key was too long; max key length is 767 bytes
建表失败
报错信息:Specified key was too long; max key length is 767 bytes
CREATE TABLE tb_04(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
column2 varchar(256) NOT NULL DEFAULT '',
column3 varchar(256) NOT NULL DEFAULT '',
column4 varchar(256) NOT NULL DEFAULT '',
column5 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY idx01(column1,column2,column3,column4,column5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
建表失败
报错信息:Specified key was too long; max key length is 767 bytes
建表失败
报错信息:Specified key was too long; max key length is 767 bytes
CREATE TABLE tb_05(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
column2 varchar(256) NOT NULL DEFAULT '',
column3 varchar(256) NOT NULL DEFAULT '',
column4 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id,column1,column2,column3,column4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
建表失败
报错信息:Specified key was too long; max key length is 767 bytes
建表失败
报错信息:Specified key was too long; max key length is 767 bytes
CREATE TABLE tb_06(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
column2 varchar(256) NOT NULL DEFAULT '',
column3 varchar(256) NOT NULL DEFAULT '',
column4 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY idx01(column1,column2,column3,column4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
建表成功
索引字段被截断为前缀索引:
KEY idx01(,column1(255),column2(255),column3(255),column4(255))
建表成功
CREATE TABLE tb_07(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(255) NOT NULL DEFAULT '',
column2 varchar(255) NOT NULL DEFAULT '',
column3 varchar(255) NOT NULL DEFAULT '',
column4 varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id,column1,column2,column3,column4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
建表成功 建表成功
CREATE TABLE tb_08(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(255) NOT NULL DEFAULT '',
column2 varchar(255) NOT NULL DEFAULT '',
column3 varchar(255) NOT NULL DEFAULT '',
column4 varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY idx01(id,column1,column2,column3,column4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
建表成功 建表成功

说明

字段varchar(N)中的N是指字符的长度,不是字节数,需要结合字符集换算得出字节数。例如:utf8字符集一个字符占3个字节,若单列索引限制767,则最大可以支持N=255.

总结建议

总结

  1. innodb表的单列索引长度最大字节数是767字节(开启innodb_large_prefix可扩大到3072字节),辅助索引超出会被截断,主键索引超出不会截断直接报错导致建表失败
  2. innodb表的联合索引中每个字段的最大字节数是767字节(开启innodb_large_prefix可扩大到3072字节),并且要求联合索引的总长度不超过3072字节
  3. 主键索引不允许被截断,辅助索引可以被截断
  4. 设置参数innodb_large_prefix=ON只能把单列索引长度扩大到3072字节,联合索引总长度的上限不变,仍然是3072个字节

建议

  • 在数据库迁移和数据库版本升级的过程中,需要对齐源库和目标库的innodb_large_prefix参数取值,否则可能导致建表失败
  • 在设计数据库表结构的过程中,对于一个可能包含很长字符串的列上创建索引时尽量使用前缀索引
相似文档
  • 在数据库的使用过程中,经常需要进行批量的数据导入操作,MySQL数据库批量数据导入可以使用LOAD DATA命令,实现大批量数据的快速入库需求。 问题现象: 在使用LOAD DATA命令可能会遇到如下报错情况: 使用 LOAD DATA LOCAL INFILE 命令批量导入数据时,报错: ERROR 1148 (42000): The used command is not allowed with this MySQL version. for MySQL 版
  • 百度云支持更改数据库表的大小写设置参数,该参数修改后需要重启实例生效: lower_case_table_names=0,表示区分大小写,创建的库表会原样大小写保存在磁盘上。 lower_case_table_names=1,表示不区分大小写,创建库表时MySQL会将所有的库表名转换成小写字母保存在磁盘上,且SQL语句中涉及到库表无论写成大写或者小写,都会被转化成小写进行查询和写入。 for MySQL 版
  • MySQL中有这样的一个默认行为:一行数据中的某些列被更新了,如果这一行中有timestamp类型的列,那么这个timestamp列会自动更新时间戳。这个行为是由explicit_defaults_for_timestamp参数控制的。在实际使用过程中经常会遇到一些非预期的异常情况,这给数据库使用过程带来诸多不便,本文重点介绍参数explicit_defaults_for_timestamp使用过程中的常见问题和注意事项。 for MySQL 版
  • MySQL数据库可以通过参数sql_mode来控制数据库的行为,本文介绍sql_mode的取值之一:NO_ENGINE_SUBSTITUTION。它的用途是:当CREATE TABLE或ALTER TABLE之类的语句指定一个已禁用或未编译的存储引擎时,控制默认存储引擎的自动替换。 for MySQL 版
  • disabled_storage_engines参数是5.7.8版本新引入的参数,它用来设置在CREATE TABLE时被禁用的存储引擎。 举例:创建新表时禁用MyISAM和MEMORY引擎。 [mysqld] disabled_storage_engines="MyISAM,MEMORY" for MySQL 版
官方微信
联系客服
400-826-7010
7x24小时客服热线
分享
  • QQ好友
  • QQ空间
  • 微信
  • 微博
返回顶部