从 innodb 的索引结构分析，为什么索引的 key 长度不能太长？

key 太长会导致一个页当中能够存放的 key 的数目变少，间接导致索引树的页数目变多，索引层次增加，从而影响整体查询变更的效率。

一、myisam存储引擎

1 . 测试的表结构信息

mysql> show create table tb2
Table: tb2
Create Table: CREATE TABLE `tb2` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

1. 测试加索引

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

mysql> alter table tb2 add index idx1 (d1);
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 long; max key length is 1000 bytes |
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
+---------+------+----------------------------------------------------------+
2 rows in set (0.00 sec)

表结构信息：

mysql> show create table tb2
Table: tb2
Create Table: CREATE TABLE `tb2` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL,
KEY `idx1` (`d1`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

mysql> alter table tb2 add index idx1 (a1,b1);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

1. 分析

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

主要字符集的计算方式：
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character

二、innodb存储引擎

1. 表结构信息：

mysql> create table tb1 (a1 varchar(255), b1 varchar(255), c1 varchar(255), d1 varchar(1000));
Query OK, 0 rows affected (0.01 sec)

2. 添加组合索引，报出waring，实际在某个单列上添加的是前缀索引

mysql> alter table tb1 add index idx1(a1,b1,c1,d1);

*Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0*

mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

3. 添加单列索引，报出waring，实际添加的是前缀索引

mysql> alter table tb1 add index idx2(d1);
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table tb1
Table: tb1
Create Table: CREATE TABLE `tb1` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL,
KEY `idx1` (`a1`,`b1`,`c1`,`d1`(255)),
KEY `idx2` (`d1`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

1. 分析：

默认情况下，InnoDB 引擎单一字段索引的长度最大为 767 字节，同样的，前缀索引也有同样的限制。当使用 UTF-8 字符集，每一个字符使用 3 字节来存储，在 TEXT 或者 VARCHAR 类型的字段上建立一个超过 255 字符数的前缀索引时就会遇到问题。可以启用服务器选项 innodb_large_prefix 使得这个限制增加到 3072 字节，而且表的 row_format 需要使用 compressed 或者 dynamic。

三、使用前缀索引带来的风险：

INNODB的索引会限制单独Key的最大长度为767字节，超过这个长度必须建立小于等于767字节的前缀索引。

此外，BLOB和TEXT类型的列只能创建前缀索引。

前缀索引能提高索引建立速度和检索速度，但是下面情况是无法使用前缀索引的：

• 索引覆盖扫描
• 通过索引的排序（order by, group by）

还是在上面的测试表上：

mysql> explain select * from tb1 order by d1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> explain select * from tb1 group by d1;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

参考链接： 

https://www.jianshu.com/p/bea78290722c

https://github.com/debitCrossBlockchain/interview__reference/blob/master/01.%E9%98%BF%E9%87%8C%E7%AF%87/1.1.6%20%E4%BB%8Einnodb%E7%9A%84%E7%B4%A2%E5%BC%95%E7%BB%93%E6%9E%84%E5%88%86%E6%9E%90%EF%BC%8C%E4%B8%BA%E4%BB%80%E4%B9%88%E7%B4%A2%E5%BC%95%E7%9A%84%20key%20%E9%95%BF%E5%BA%A6%E4%B8%8D%E8%83%BD%E5%A4%AA%E9%95%BF.md