Mysql如何给字符串添加索引(前缀索引)

vlambda
2020-09-21

Mysql如何给字符串添加索引(前缀索引)

在日常开发中，我们经常给字符串添加索引，那么给字段添加索引有什么技巧吗，我们看看下面的例子，我们给一个邮箱添加索引，应该如何添加呢

看看下面这条sql

select *  from user where email ='[email protected]'

如果我们不添加索引，肯定是要进行全表扫描的，那么我们如何添加呢有两种方式

alter table user add index index1(email)alter table user add index index2(email(6))

上面两种方式都是在添加索引，不同点就是第二种添加的仅仅是邮箱的前缀索引，那么他的结构表现如下图

index2每个索引中仅仅存储了字段的前6个字符，而index1存储了整个字符串,我们分别在不同索引下执行下面的语句

select id,name,email  from user where email='[email protected]'

如果使用index1,他的执行过程如下

在index1中找到符合条件的记录，获取id=5
然后使用id=1,在主键索引上获取整行记录
在index1寻找下一条记录，直到发现不满足位置，循环结束

如果使用index2,他的执行过程如下

在index2中找到符合条件的值，获取到id=1
然后在主键索引中找到id=1的数据，发现不符合
在index2继续寻找，id=2,然后在主键上找到行记录，发现不符合
循环上面步骤，直到符合的记录

我们发现使用index2虽然占用的空间小，但是他对搜索的性能没有更好的提高，而是要多扫描几行记录。

是不是发现前缀索引不是很好呢，然而并不是的，我们如果把索引的长度修改成8，然后在index2中仅仅找到一条符合记录，因此只需要扫描一行就够了。

于是，我们发现选择合适的前缀长度，既可以节省空间，也可以不用增加更多的查询成本，

那么如何选择合适的前缀长度呢

建立索引之前，我们要关注字段的区分度，区分度越大，性能越高，意味着重复的值就越少。查看某列的值不同的值

select count(distinct email) as L from user

然后，我们分别取不同长度的前缀，比如我们看一下4-7个字节的前缀索引

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 user

当前使用前缀索引会丢失区分度，索引我们要预定一个可以接受的损失比例，比如5%，然后，计算出L4-L7中，找到不小于L*95%。假设L6,L7,满足，你就可以选择长度为6.

前缀索引对覆盖索引的影响

上面我们分析了，前缀索引影响扫描的行数，其实，他也是会影响覆盖索引的，正如下面例子

select id,email from user where email='[email protected]'select id,name,email from user where email='[email protected]'

如果我们只要返回id,email,因此使用index1索引，找到符合记录利用覆盖索引,直接返回索引的值，就可以满足需求，但是如果我们使用index2,即使我们使用email(8)可以找到唯一一行数据，但是我们还是要进行那id的值去主键索引判断是不是email的值，再不济，我们使用email(18),完全包含字段的长度，但是我们依然要回表查找主键索引，因为系统并确定前缀索引是否有截断完成信息，

总结就是使用前缀索引，就无法使用覆盖索引。

其他方式

对于邮箱类型的字段，使用前缀索引是一个不错的选择，但是我们要是遇到前缀区分度不大的字段应该怎么办呢，

第一种，我们可以使用倒序存储，如果存储身份证，一般身份中前6位，一个省的值都是一样，而后几位区分度就还可以，索引我们可以使用下面语句查询

select name from user where id_card=reverse('id_card_string')

第二种，我们使用hash字段，我们创建一个字段，直接把身份证的值进行hash,存储在这个字段中，同时建立索引,由于可能存在多个身份证可能有一样的hash值，因此还要判断id_card的值是否准确

select name from user where id_card_crc=crc32('id_card_string') and id_card='id_card_string'

上面两种共同点就是不支持范围查询，他们的主要区别如下

从占用的空间方面说，倒序存储方式在主键上不会消耗额外的空间，而hash索引要所创建一个字段，但是如果倒排存储长度过长的话，消耗的空间和hash差不多
两种方式都要使用额外函数，如果从两个函数的复杂度说的话，reverse函数额外消耗的CPU小点
查询效率上，hash字段的方式更稳定一些，虽然可能有冲突，但是这种概率很小，且认为平均扫描一行，而倒序存储毕竟还要使用前缀索引方式，也就是会增加扫描的行数

如果文章对您有一丝丝帮助，麻烦点个关注，也欢迎转发点赞,谢谢

扫码二维码

了解更多精彩

vlambda博客
学习文章列表