一、myisam存储引擎
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: tb2Create 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=utf81 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: 0mysql> 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\G
*************************** 1. row *************************** Table: tb2Create 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=utf81 row in set (0.00 sec)
（2）添加组合索引，会执行失败。
mysql> alter table tb2 add index idx1 (a,b);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes4. 分析
myisam存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，所有索引键最大长度总和不能超过1000，而且不是实际数据长度的总和，而是索引键字段定义长度的总和。
主要字符集的计算方式：
latin1 = 1 byte = 1 characteruft8 = 3 byte = 1 charactergbk = 2 byte = 1 character
二、innodb存储引擎
1. 表结构信息：
mysql> create table tb1 (a varchar(255), b varchar(255), c varchar(255), d varchar(1000));
Query OK, 0 rows affected (0.01 sec)
2. 添加组合索引，报出waring，实际在某个单列上添加的是前缀索引
mysql> alter table tb1 add index idx1(a,b,c,d);
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(d);
Query OK, 0 rows affected, 2 warnings (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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\G
*************************** 1. row *************************** Table: tb1Create Table: CREATE TABLE `tb1` ( `a` varchar(255) DEFAULT NULL, `b` varchar(255) DEFAULT NULL, `c` varchar(255) DEFAULT NULL, `d` varchar(1000) DEFAULT NULL,KEY `idx1` (`a`,`b`,`c`,`d`(255)), KEY `idx2` (`d`(255))) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
4. 分析：
默认情况下，InnoDB 引擎单一字段索引的长度最大为 767 字节，同样的，前缀索引也有同样的限制。当使用 UTF-8 字符集，每一个字符使用 3 字节来存储，在 TEXT 或者 VARCHAR 类型的字段上建立一个超过 255 字符数的前缀索引时就会遇到问题。可以启用服务器选项 innodb_large_prefix 使得这个限制增加到 3072 字节，而且表的 row_format 需要使用 compressed 或者 dynamic。
三、使用前缀索引带来的风险：
- 索引覆盖扫描
- 通过索引的排序（order by, group by）
还是在上面的测试表上：
mysql> explain select * from tb1 order by d;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| 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 d;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+| 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)