07-字符串字段加索引

加索引语句：

alter table table_name add index index_name(field_name);

表示给table_name表的field_name字段加一个名为index_name的索引。

但是如果field_name是字符串类型，我们也可以使用

alter table table_name add index index_name(field_name(n));

表示给table_name表的field_name的前n位加一个名为index_name的索引。

给字段的部分值加索引的意义在哪？

比如一个user表，保存了个人的邮箱地址email，一般email的格式为xx@xxx.com，一般来说，我们使用@前的部分作为查询条件就已经足够了。这个时候我们如果设置了email的前8位作为索引，可以有效的减小索引树的大小，增加查询的效率。

我们在设置索引的时候通常考虑的就是索引的区分度。区分度越大，索引的效率越好。这也是我们通常不对type等字段设置索引的原因，因为区分度太小。

如何查看索引的区分度？所谓的区分度就是当前索引占所有数据的比例。比如user表中有100W条数据，如果邮件不得重复，那么就有100W个邮箱地址。这个时候如果把整个email作为索引，那么它的区分度就是100W/100W * 100%=100%。这样的区分度就很高了。

但是如果我们使用前8位作为区分度的话，user表里可能会存在例如：leliuabc111,leliuabc222这样的数据，如果这样数据很多，那么使用email(8)来建索引的区分度就不高，索引效率自然不好。

如何查看区分度？

就是查看前n位的数据量。

select count(distinct left(email,6)） as L6,count(distinct left(email,7)） as L7,count(distinct left(email,8)） as L8,count(distinct left(email,9)） as L9,from User;

这样我们查看前n位的数据量。如果我们要求区分度>95%,那么我们只要查前n位满足此值，那么我们就可以加索引到前n位。

想办法减少索引树的大小，能够增加索引查询的效率。

这种方式有什么限制呢？

由于只对字段加了部分索引，所以匹配到值之后还得回表取数据再判断email是否正确，无法使用覆盖索引减少回表的优化方案。

以上加索引的方式有什么限制呢？

1、以上索引方式，都无法通过索引覆盖避免回表；

2、无法区间范围查询

以上的限制也是我们选择如何建立索引的重要考量。

总结加索引方式：

• 正常全字段索引，可能会导致索引占用空间大问题
• 前n位建立索引，但是不能使用覆盖索引优化
• 倒序保存，无法区间查询，有额外函数消耗
• hash值索引，无法区间查询，有额外函数消耗