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

最新推荐文章于 2024-04-07 16:29:02 发布

水猴007

最新推荐文章于 2024-04-07 16:29:02 发布

阅读量6.5k

点赞数 5

分类专栏： MySQL 文章标签： MySQL 索引

本文链接：https://blog.csdn.net/houyefeng/article/details/81628781

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

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

常用数据类型和长度
数据类型	长度（byte）
int	4
bigint	8
char(n)	n表示字符数，字节数为n*3，n<=255
varchar(n)	n表示字符数，字节数为n*3，n<=21845
date	3
datetime	8
timestamp	4
float	4
double	8

在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中，字符类型的长度指的是字符数，而不是字节数，每个字符占用的字节数和使用的字符集相关。