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)添加单列索引,能够添加成功(报出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 |
+---------+------+----------------------------------------------------------+
<