数据库innodb_large_prefix参数使用详解

转自：http://m.liuyyy111.cn/view.php?aid=1060

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表的行记录格式是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_mat = Barracuda

测试过程
建表语句
innodb_large_prefix=OFF

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

 innodb_large_prefix=ON 
 
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 3072 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 3072 bytes
建表失败报错信息：Specified key was too long; max key length is 3072 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 3072 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.

总结建议

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

建议

在数据库迁移和数据库版本升级的过程中，需要对齐源库和目标库的innodb_large_prefix参数取值，否则可能导致建表失败
在设计数据库表结构的过程中，对于一个可能包含很长字符串的列上创建索引时尽量使用前缀索引