文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL给字符串加一个高效索引的实现

2023-03-20 09:55

关注

需求

在日常需求中,用户使用手机号或者邮箱登录某一个系统,是一个很常见的操作,那如何在类似手机号或者邮箱这样的字段上建立一个合理的索引呢?

前缀索引

前缀索引,就是以一个字段值的一部分作为索引。我们在mysql中创建索引时,如果不指定索引字段的长度,那么就会以整个字符串来建立索引。

语句1:
alter table test add index idx(email);
语句2:
alter table test add index idx(email(8));

对于语句1,创建的索引中,会包含每条记录中的整个email字符串值。

对于语句2,创建的索引中,保存的是每条记录中email字段的前8个字节。

使用前缀索引的优势很明显,那就是索引占用的空间会更小,整个索引树会更紧凑,树的高度相对更低。

但是相应的,索引的区分度会变低,可能导致索引扫描行数增加。

在我们创建索引时,索引的区分度是一个很重要的指标。区分度越高,重复的值就越少,扫描的效率就越高。

在使用前缀索引时,合理规划使用的前缀长度,不仅可以节省空间,还可以不用额外增加扫描的行数。具体使用多少的长度,建议根据我们的实际业务场景来判断、测试。

倒序+前缀索引

倒序+前缀索引有一个经典的使用场景,就是对身份证号做索引。

假设我们现在要维护一个市县所有人的身份信息,其中按身份证号查询是一个高频场景。

身份证号码一共15位或者18位,一般来说,同一个市县的人身份证号前6位一般是相同的,如果直接对身份证号做全索引,那么会比较浪费空间,导致性能下降。直接前缀索引的话,前6位的区分度又很低(甚至可以说没有,因为大家前6位基本都一样)。

这时,使用倒序+前缀索引的好处就体现出来了。

我们先将身份证倒序存储,或者冗余一个倒序的身份证号字段,然后取前6位做前缀索引。

身份证后6位的区分度已经基本上够我们使用了,如果你还觉得查询速度不够,那取前8位也是没问题的。

总结

我们之所以尽量避免对字符串加全值索引,是因为字符串字段的长度不好预估,可能会变得很大。在一个值很大的字段上做索引,会额外的占用更多的空间,数据页上可存储的索引值会变少,导致MySQL树的高度变高,这样查询数据时,IO增加,性能下降。

但是,凡事都有例外,有时我们也不能为了优化而优化。

比如我们现在要维护一个高校的师生信息,按整个学校2万人,每年新增1万新学生来计算,10年时间也不过12万数据而已。即使在一个身份证号字段上加上全字段索引又能多占用多少空间呢?能省出多少性能呢?

有时,从业务量预估优化和收益比,也是一个很好的习惯。

到此这篇关于MySQL给字符串加一个高效索引的实现的文章就介绍到这了,更多相关MySQL字符串高效索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯