漫谈MySQL索引与字段儿长度的关系

在数据库开发中,索引是优化查询性能的重要手段,本文是对使用MySQL 5.6.28进行索引优化时遇到的问题一个总结,目的是为了加深对索引长度和字段长度的理解,理清这两个长度的关系,本文件均使用InnoDB存储引擎、utf8字符集、索引方法为btree。

在使用MySQL时常用的数据类型有int、bigint、char、varchar、date、datetime、timestamp、float、double,每种类型和长度见下表:

常用数据类型和长度
数据类型长度(byte)
int4
bigint8
char(n)n表示字符数,字节数为n*3,n<=255
varchar(n)n表示字符数,字节数为n*3,n<=21845
date3
datetime8
timestamp4
float4
double8

在MySQL中为表建立索引时经常会遇到“Error : Specified key was too long; max key length is 767 bytes”和“Error : Duplicate key name ind_composite_3072”错误,767和3072的由来参考官方文档或《关于InnoDB索引长度限制的tips》。这两个错误都和MySQL对索引长度的默认设置有关,可通过设置innodb_large_prefix,修改767为3072。由于MySQL使用前缀索引,所谓前缀索引,指的是只用字段的前n个字符建立索引。除text、blob在建索引时必须要指定前缀长度外,大部分类型是不需要指定长度的,指定前缀的方法见后面的示例。

下面通过具体示例来说明字段长度与索引长度的关系,为了测试建立"index_test"表,SQL如下:

CREATE TABLE `index_test` (
	`id` int NOT NULL,
	`num` bigint,
	`achar` char(50),
	`avarchar` varchar(2000),
	`adate` date,
	`adatetime` datetime,
	PRIMARY KEY (`id`)
) ENGINE INNODB DEFAULT CHARACTER SET utf8 COMMENT='测试索引长度与字段长度的关系';

接下来为表“index_test”在“avarchar”字段上增加一个索引,创建索引时不指定索引前缀的长度,SQL如下:

ALTER TABLE `index_test` ADD INDEX `ind_single_varchar` (avarchar) COMMENT '单一varchar字段的索引,不指定索引前缀的长度';

执行上面的SQL时,MySQL给出了错误提示“Error : Specified key was too long; max key length is 767 bytes”,这句话的意思是索引的最大长度为767字节(byte),而“index_test”的“avarchar”的长度为2000字符,由于使用的是utf8字条集,一个字符占3个字节,长度为2000*3=6000字节,所以创建失败。接下来我们为索引指写前缀长度,索引前缀的长度单位为字符,SQL如下:

ALTER TABLE `index_test` ADD INDEX `ind_single_varchar_pre` (avarchar(255)) COMMENT '单一varchar字段的索引,指定索引前缀长度为255字符';

 “avarchar(255)”是MySQL创建索引语法的一部分,形式为“字段名(索引前缀长度)”,单位为字符,255*3=765<767,所以上面的语句能够正常执行。实际使用中text、blob使用的不多,所以“Error : Specified key was too long; max key length is 767 bytes”通常在对varchar类型字段建索引出现。

在对单个字段创建索引时,主要的限制是字段长度,如果字段长度不超过767字节,不会出现问题。但复合索引的情况与单字段索引有所不同。复合索引中除了单字段长度不能超过767字节外,索引中所有字段长度的总合不能超过3072字节。

为了进一步测试复合索引与字段长度的关系,让我们增加几个varchar类型的字段,SQL如下:

ALTER TABLE `index_test` ADD COLUMN `bvarchar` VARCHAR(255),
ADD COLUMN `cvarchar` VARCHAR(255),
ADD COLUMN `dvarchar` VARCHAR(255),
ADD COLUMN `evarchar` VARCHAR(255);

接下来为“index_test”增加一个复合索引,创建索引时不指定索引前缀的长度,SQL如下:

ALTER TABLE `index_test` ADD INDEX `ind_composite` (`id`,`num`,`achar`,`adate`,`adatetime`,`avarchar`) 
COMMENT '不指定前缀长度的复合索引';

由于“avarchar”的长度为2000字符,2000*3>767,这违反了MySQL的约束,如期给出错误提示“Error : Specified key was too long; max key length is 767 bytes”。接下来为“avarchar”指定索引的前缀长度,SQL如下:

ALTER TABLE `index_test` ADD INDEX `ind_composite_pre` (`id`,`num`,`achar`,`adate`,`adatetime`,`avarchar`(255)) 
COMMENT '指定avarchar前255个字符用于索引';

由于指定了前255个字符用于索引,所以上面的语句得以正常执行。

通过上面的例子我们验证了在复合索引中,对单个字段的约束条件,这一点和单字段索引是一样的。下面的例子将用于验证MySQL对复合索引总长度为3072字节的限制。用下面的SQL为“index_test”表创建复合索引,如下:

ALTER TABLE `index_test` ADD INDEX `ind_composite_3072` (`bvarchar`,`cvarchar`,`dvarchar`,`evarchar`,`num`,`adatetime`)
COMMENT '复合索引,总长度不能超过<=3072字节';

由于“bvarchar”、“cvarchar”、“dvarchar”、“evarchar”长度均为255字符,即255*3=3060字节,“num”、“adatetime”为8字节,所以这个索引的总长度为3060+8+8=3076字节>3072,执行上面语句时MySQL会给出错误提示“Error : Duplicate key name ind_composite_3072”。

通过上面一系列的试验,我们明确知道MySQL创建索引时,单字段索引的字段长度不能超过767字节,超过时需要指定索引前缀;创建复合索引时,单字段长度不能超过767字节,且索引中所有字段的总长度不能超过3072字节,违反这些约束时需要删减字段或是为长度较大的字段指定索引前缀。在MySQL 5.6.28中,字符类型的长度指的是字符数,而不是字节数,每个字符占用的字节数和使用的字符集相关。

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值