背景
在一些业务场景下，我们可能需要给字符串类型的字段添加索引，例如：在学校中，一些用学生的编号进行登录的管理系统；一些支持邮箱登录的网站等。
在登录验证过程中，我们势必要通过编号、邮箱来进行查询；那么，在数据量特别大的时候，为了不对表进行全局扫描，给这些字段加上索引，是非常有必要的。
创建索引的方法以及优缺点
1.直接创建完整索引
alter table Customer add index index1(email);
缺点：受字符串的长度影响，可能比较占用空间。
2.创建前缀索引
alter table Customer add index index1(email(6));
优点：只取前n个字符作为索引，与创建完整索引相比，节约了空间。
缺点：
A.只取前n个字符作为索引，使得索引值并不唯一；
例如：buzhidao1@qq.com,buzhidao2@qq.com,buzhidao3@qq.com（邮箱唯一）;前6个字符均为“buzhidao”。
当使用该索引查到符合的主键后（前n位符合），用主键id去主键索引查询记录并比较email是否完成符合，若不符合，则回到email(6)的index找下一个索引，直到找到email完全符合的记录 或 email(6)不符合停止查找。
这样的查询过程，使得查询语句读数据的次数变多。
那么该如何选择合适的索引长度呢？
select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7
from SUser;
计算区分度，一般区分度在95%以上，我们认为是可以使用的。
B.除此之外，使用前缀索引，将无法使用覆盖索引，因为系统并不知道前缀索引截取的字符串是否完整；在执行过程中，仍然会去主键索引再去判断email是否符合。
3.对字符串灵活的进行处理
A.对于倒序区分度高的字符串，我们可以倒序存储字符串并建立前缀索引。
缺点：在读写时要进行reverse函数（倒过来）。
B.额外创建一个字符串的hash字段，对该字段创建索引。
缺点：额外增加了存储成本。
网友评论