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

港控/mmm° 2023-10-05 12:29 48阅读 0赞

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

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

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

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

下面通过具体示例来说明字段长度与索引长度的关系，为了测试建立"index\_test"表，SQL如下：

1.  ``CREATE TABLE `index_test` (``
2.  `` `id` int NOT NULL,``
3.  `` `num` bigint,``
4.  `` `achar` char(50),``
5.  `` `avarchar` varchar(2000),``
6.  `` `adate` date,``
7.  `` `adatetime` datetime,``
8.  ``PRIMARY KEY (`id`)``
9.  `) 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如下：

1.  ``ALTER TABLE `index_test` ADD COLUMN `bvarchar` VARCHAR(255),``
2.  ``ADD COLUMN `cvarchar` VARCHAR(255),``
3.  ``ADD COLUMN `dvarchar` VARCHAR(255),``
4.  ``ADD COLUMN `evarchar` VARCHAR(255);``

接下来为“index\_test”增加一个复合索引，创建索引时不指定索引前缀的长度，SQL如下：

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

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

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

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

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

1.  ``ALTER TABLE `index_test` ADD INDEX `ind_composite_3072` (`bvarchar`,`cvarchar`,`dvarchar`,`evarchar`,`num`,`adatetime`)``
2.  `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中，字符类型的长度指的是字符数，而不是字节数，每个字符占用的字节数和使用的字符集相关。

来源：[https://blog.csdn.net/houyefeng/article/details/81628781][https_blog.csdn.net_houyefeng_article_details_81628781]

[InnoDB_tips]: http://dinglin.iteye.com/blog/1681332
[https_blog.csdn.net_houyefeng_article_details_81628781]: https://blog.csdn.net/houyefeng/article/details/81628781