今天在测试环境中加一个索引时候发现一警告
root@test 07:57:52>alter table article drop index ind_article_url;query ok, 144384 rows affected (16.29 sec)records: 144384 duplicates: 0 warnings: 0root@test 07:58:40>alter table article add index ind_article_url(url);query ok, 144384 rows affected, 1 warning (19.52 sec)records: 144384 duplicates: 0 warnings: 0root@test 07:59:23>show warnings;+———+——+———————————————————+| level | code | message |+———+——+———————————————————+| warning | 1071 | specified key was too long; max key length is 767 bytes |+———+——+———————————————————+1 row in set (0.00 sec)
用show create table article查看索引以及表结构的信息：
`url` varchar(512) default null comment ‘外链url',……key `ind_article_url` (`url`(383))…..default charset=gbk……drop table test;create table test(test varchar(767) primary key)charset=latin5;
– 成功
接下来未测试，在不同的字符集：
drop table test;create table test(test varchar(768) primary key)charset=latin5;
– 错误
–
error 1071 (42000): specified key was too long; max key length is 767 bytesdrop table test;create table test(test varchar(383) primary key)charset=gbk;
– 成功
drop table test;create table test(test varchar(384) primary key)charset=gbk;
– 错误
–
error 1071 (42000): specified key was too long; max key length is 767 bytesdrop table test;create table test(test varchar(255) primary key)charset=utf8;
– 成功
drop table test;create table test(test varchar(256) primary key)charset=utf8;
– 错误
–
error 1071 (42000): specified key was too long; max key length is 767 bytes
mysql的varchar索引只支持不超过768个字节 或者 768/2=384个双字节 或者 768/3=256个三字节的字段
而 gbk是双字节的，utf-8是三字节的。
那么上面出现的原因就明了，我的字符集是为gbk为双字节，而url为512个字符，1024个字节，所以超过字符串索引的限制，报出了警告，mysql默认创建了383（766字节）长度的前缀索引。
我们知道小的索引大小不仅对空间存储，内存的降低和性能的提升有重大作用，那么在计算前缀索引的长度的时候，需要我们做出明智的选择，怎么明智？
全索引列的选择性：
root@test 08:10:35>select count(distinct(url))/count(*) from article;+——————————-+| count(distinct(url))/count(*) |+——————————-+| 0.0750 |+——————————-+
对各种长度的前缀列计算其选择性：
root@test 08:16:41>select count(distinct left(url,76))/count(*) url_76,-> count(distinct left(url,77))/count(*) url_77,-> count(distinct left(url,78))/count(*) url_78,-> count(distinct left(url,79))/count(*) url_79,-> count(distinct left(url,80))/count(*) url_80,-> count(distinct left(url,81))/count(*) url_81,-> count(distinct left(url,82))/count(*) url_82,-> count(distinct left(url,83))/count(*) url_83,-> count(distinct left(url,84))/count(*) url_84,-> count(distinct left(url,85))/count(*) url_85-> from article;+——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+| url_76 | url_77 | url_78 | url_79 | url_80 | url_81 | url_82 | url_83 | url_84 | url_85 |+——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+| 0.0747 | 0.0748 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0750 |+——–+——–+——–+——–+——–+——–+——–+——–+——–+——–+1 row in set (1.82 sec)
我们看到选择85的长度的时候，该前缀列的选择性和全列的选择性相当了：
alter table article add index ind_article_url(url(85)),而不必选择383个字节作为前缀；
但是前缀索引还是有一点不足的地方，就是在查询语句中order by 和group by不能使用到前缀索引
root@test 08:49:24>explain select id,url,deleted from article group by url;+—-+————-+————-+——+—————+——+———+——+——–+———————————+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+—-+————-+————-+——+—————+——+———+——+——–+———————————+| 1 | simple | article | all | null | null | null | null | 139844 | using temporary; using filesort |+—-+————-+————-+——+—————+——+———+——+——–+———————————+1 row in set (0.00 sec);