专栏首页洁癖是一只狗Mysql如何给字符串添加索引(前缀索引)

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

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

看看下面这条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,他的执行过程如下

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

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

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

我们发现使用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'

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

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

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

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-09-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql索引解密(下)

    上面ID索引树进行查找记录的过程叫回表,可以看出k树索引树进行了三次查询,Id索引树进行了两次查询。查询数据过程中是否可以避免回表查询呢,

    小土豆Yuki
  • Mysql索引解密(上)

    索引是数据库概念最重要的概念之一,也是我们经常要使用的优化手段,索引的出现其实就是为了提高数据查询的效率,就像书的目录一样

    小土豆Yuki
  • 面试Mybatis之类型处理器​(typeHandlers)

    无论是MyBatis在预处理语句(PreparedStatement)中设置一个参数时,还是从结果集中取出一个值时, 都会用类型处理器将获取的值以合适的方式转换...

    小土豆Yuki
  • 怎么给字符串加索引

    如果 email 不建索引,那么就只能全表扫描,如果 email 这个字段是哪个没有索引,那么这个语句只能做全表扫描。

    王小明_HIT
  • 聊聊PostgreSQL中的几种索引类型

    索引是增强数据库性能的利器,在检索某些特定行的时候效率会有很大提升,postgresql中索引类型丰富,每种索引有着不同的应用场景,下面简单介绍一下。

    数据库架构之美
  • mysql8.0新特性--隐藏索引

    我们有时候想删除掉冗余索引,但是又怕删除之后影响到查询性能,这时候再回退就需要一定的时间。MySQL8.0开始支持隐藏索引(invisible inde...

    MySQL数据库技术栈
  • 【DB笔试面试548】在Oracle中,索引有哪3大特性?

    一般来说索引有3大特性,索引高度比较低、索引存储列值及索引本身有序,对这3大特性的应用如下表所示:

    小麦苗DBA宝典
  • 在有TableView的页面一行代码收起键盘

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u010105969/article/details/...

    用户1451823
  • 标题党将失效,小程序对运营的 7 大挑战

    知晓君
  • 小程序社区经典问题集锦(上)

    最近一直在使用灰度发布的功能,觉得挺好,当用户量大时,一个小改动,就有可能影响很多用户,所以,灰度发布,是个必须的功能。

    连胜

扫码关注云+社区

领取腾讯云代金券