在日常开发中,我们经常给字符串添加索引,那么给字段添加索引有什么技巧吗,我们看看下面的例子,我们给一个邮箱添加索引,应该如何添加呢
看看下面这条sql
select * from user where email ='jiepi@qq.com'
如果我们不添加索引,肯定是要进行全表扫描的,那么我们如何添加呢有两种方式
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='zhangsan@qq.com'
如果使用index1,他的执行过程如下
如果使用index2,他的执行过程如下
我们发现使用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='zhangsan@qq.com'
select id,name,email from user where email='zhangsan@qq.com'
如果我们只要返回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'
上面两种共同点就是不支持范围查询,他们的主要区别如下
如果文章对您有一丝丝帮助,麻烦点个关注,也欢迎转发点赞,谢谢