如何给字符串字段加索引

如何给字符串字段加索引

几乎所有的系统都支持邮箱登录，所以会遇到类似：

select f1, f2 from user_email where email=‘xxx’;

那么如何去给邮箱字段(字符串)添加索引呢？如果没有索引我们只能通过全表扫描来查找数据，效率会非常差。如果直接建立索引，占用的空间又是不可忽视的。

前缀索引

MySQL支持前缀索引，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串，例如：

alter table user_email add index index_email(email(6));

这里创建的索引,只取email的前6个字节来创建的。

可能有同学会问了：通常我们的邮箱在数据库中保存需要保证它的唯一性，如果只取邮箱的前一部分创建索引，那么多个邮箱的前缀是一样的怎么办呢？

例如：

是的，这样一来，我们在通过email的前缀索引进行查询时，可能会获取到多于1条的主键（id），我们还需要拿着这些id回表去判断这个邮箱是不是我们要查找的邮箱信息。

所以，前缀索引虽然一方面帮我们减轻了索引的空间占用，但是另一方面也造成了回表的次数增多。

那有同学就会说了，如果我们增加前缀索引的长度，是不是就可以减轻上面的问题。

是的，定义好前缀索引的长度，既可以节省间，又不会带来过多了回表次数。

当我们在实际项目中，通常会选择一些读多写少、区分度高的字段建立索引，因为区分度越高，查询时获取到重复的id就会越少，回表次数也相较减少。

我们可以通过以下语句获取一个列上基数（不同值的个数）

select count(distinct email) as cnt from user_email;

当然，我们就可以对一个字段的不同长度来进行分析一个列上的基数

select count(distinct left(email,8)）as L8,

​ count(distinct left(email,9)）as L9,

from user_email;

所以我们可以选择email前缀为9建立索引。

这样一来我们就可以通过修改前缀索引的长度就可以保证前缀索引的优点（降低索引空间的占用），有降低缺点的影响（造成回表次数增多）。

但是：

虽然可以通过修改前缀长度来降低前缀索引数据的重复，但是并不能一定保证不重复，所以还是需要回表进行判断，这样就造成了覆盖索引失效。

select id, email from user_email where email=‘zhangsan111@xxx.com’;

l from user_email where email=‘zhangsan111@xxx.com’;

如果直接通过email整个字符串建立索引的话，这句SQL语句就不需要回表（覆盖索引）。但是如果我们建立了前缀索引，即使我们使用的前缀索引包含了email索引的信息，仍不得不需要回表查询（验证邮箱是否正确），所以就用不到覆盖索引的优化。

https://funnylog.gitee.io/mysql45/11%E8%AE%B2%E6%80%8E%E4%B9%88%E7%BB%99%E5%AD%97%E7%AC%A6%E4%B8%B2%E5%AD%97%E6%AE%B5%E5%8A%A0%E7%B4%A2%E5%BC%95.html