MySQL索引和字段类型，字段长度

首先是两个表， user_info 和 user_identify_info ，这两个表通过 user_id 关联。

user_info 中 user_id 字段是int(11)类型，主键索引

user_identify_info 中 user_id 是varchar类型，普通索引

执行结果如图，未使用到索引

将 user_identify_info 字段改为int(11)或者bigint类型，使用到索引

最后查到

1、字段是varchar类型，参数是int类型，不走索引

2、字段是varchar类型，参数是字符串，走索引

3、字段是int类型，参数是int类型，走索引

4、字段是int类型，参数是字符串，走索引

添加列delivery_address 字段扩展到1000

为这个字段添加单列索引，报异常出来，索引最大长度是3072 bytes

mysql5.7不报错，但会自动将索引长度限制在768

mysql8单列索引最长也是768

复合索引总长度不能超过768

1. 数据库版本：阿里云RDS MySQL5.1

mysql>select @@version

+-------------------------------+

| @@version |

+-------------------------------+

| 5.1.61-Alibaba-rds-201404-log |

+-------------------------------+

1 row in set (0.00 sec)

2. 测试的表结构信息

mysql>show create table tb2\G

*************************** 1. row ***************************

Table: tb2

Create Table: CREATE TABLE `tb2` (

`a` varchar(255) DEFAULT NULL,

`b` varchar(255) DEFAULT NULL,

`c` varchar(255) DEFAULT NULL,

`d` varchar(1000) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

3. 测试加索引

（1）添加单列索引，能够添加成功（报出warning），但实际添加的是前缀索引。

mysql>alter table tb2 add index idx1 (d)

Query OK, 0 rows affected, 2 warnings (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>show warnings

+---------+------+----------------------------------------------------------+

| Level | Code | Message |

+---------+------+----------------------------------------------------------+

| Warning | 1071 | Specified key was too longmax key length is 1000 bytes |

| Warning | 1071 | Specified key was too longmax key length is 1000 bytes |

+---------+------+----------------------------------------------------------+

2 rows in set (0.00 sec)

表结构信息：

mysql>show create table tb2\G

*************************** 1. row ***************************

Table: tb2

Create Table: CREATE TABLE `tb2` (

`a` varchar(255) DEFAULT NULL,

`b` varchar(255) DEFAULT NULL,

`c` varchar(255) DEFAULT NULL,

`d` varchar(1000) DEFAULT NULL,

KEY `idx1` (`d`(333))

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

（2）添加组合索引，会执行失败。

mysql>alter table tb2 add index idx1 (a,b)

ERROR 1071 (42000): Specified key was too longmax key length is 1000 bytes

4. 分析

myisam存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，所有索引键最大长度总和不能超过1000，而且不是实际数据长度的总和，而是索引键字段定义长度的总和。

主要字符集的计算方式：

latin1 = 1 byte = 1 character

uft8 = 3 byte = 1 character

gbk = 2 byte = 1 character

大家应该知道InnoDB单列索引长度不能超过767bytes，联合索引还有一个限制是长度不能超过3072。

mysql>CREATE TABLE `tb` (

-> `a` varchar(255) DEFAULT NULL,

-> `b` varchar(255) DEFAULT NULL,

-> `c` varchar(255) DEFAULT NULL,

-> `d` varchar(255) DEFAULT NULL,

-> `e` varchar(255) DEFAULT NULL,

-> KEY `a` (`a`,`b`,`c`,`d`,`e`)

->) ENGINE=InnoDB DEFAULT CHARSET=utf8

ERROR 1071 (42000): Specified key was too longmax key length is 3072 bytes

可以看到，由于每个字段占用255*3, 因此这个索引的大小是3825>3072，报错。

为什么3072

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。

所以一个记录最多不能超过8k。

又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。

由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)。

单列索引限制

上面有提到单列索引限制767，起因是256×3-1。这个3是字符最大占用空间（utf8）。但是在5.5以后，开始支持4个字节的uutf8。255×4>767, 于是增加了一个参数叫做 innodb_large_prefix。

这个参数默认值是OFF。当改为ON时，允许列索引最大达到3072。

可以看到默认行为是建表成功，报一个warning，并且将长度阶段为255。

注意要生效需要加row_format=compressed或者dynamic 。

如果确实需要在单个很大的列上创建索引，或者需要在多个很大的列上创建联合索引，而又超过了索引的长度限制，解决办法是在建索引时限制索引prefix的大小：

例如：create index yarn_app_result_i4 on yarn_app_result (flow_exec_id(100), another_column(50))

这样，在创建索引时就会限制使用的每个列的最大长度。如上的例子中，在创建联合索引时，最多使用列flow_exec_id中前100个字符创建索引，最多使用another_column中前

50个字符创建索引。这样子，就可以避免索引长度过大的问题。

---