背景

在一些业务场景下,我们可能需要给字符串类型的字段添加索引,例如:
在学校中,一些用学生的编号进行登录的管理系统;
一些支持邮箱登录的网站等。
在登录验证过程中,我们势必要通过编号、邮箱来进行查询;那么,在数据量特别大的时候,为了不对表进行全局扫描,给这些字段加上索引,是非常有必要的。

创建索引的方法以及优缺点

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字段,对该字段创建索引。
缺点:额外增加了存储成本。

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议

一个喜欢拔刀的萌新Coder