文章详情

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

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL中字符串索引的创建规则有哪些

2024-04-02 19:55

关注

MySQL中字符串索引的创建规则有哪些?很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。

如何更好的创建字符串索引

我们知道,MySQL中,数据和索引都是在一颗 B+树 上,我们建立索引的时候,这棵树所占用的空间越小,检索速度就会越快,而varchar格式的字符串有些会很长,那么在效率为上的今天,我们如何更加合理的建立字符串的索引呢?
假如说我们一张表中存在 email 字段,现在要给 email 字段创建索引,email 字段值的格式为:zhangsan@qq.com。

有2种建立索引的方式:

1、直接给 email 字段建立索引:alter table t add index index1(email);

索引树结构为:

MySQL中字符串索引的创建规则有哪些

2、建立 email 的前缀索引:alter table t add index index2(email(6));

索引数据结构为:

MySQL中字符串索引的创建规则有哪些

此时我们的查询语句为:select id,name,email from t where email='zhangsh223@xxx.com';

当使用index1索引时其执行步骤为:

1、从index1索引树查找索引值为zhangsh223@xxx.com的主键值ID1;

2、根据ID1回表查到该行数据确实为zhangsh223@xxx.com,将结果加入结果集;

3、继续查找index1索引树下一个索引值是否满足zhangsh223@xxx.com,不满足则结束查询。

当使用index2索引时其执行步骤为:

1、从index2索引树查找索引值为zhangs的主键值ID1;

2、根据ID1回表查到该行数据确实为zhangsh223@xxx.com,将结果加入结果集;

3、 继续查找index2索引树下一个索引值是否满足zhangs,满足则继续回表查询该行数据是否为zhangsh223@xxx.com,不是则跳过继续查找;

4、持续查找index2索引树,直到索引值不是zhangs为止。

从以上分析中我们可以看出,全字段索引相比前缀索引来说,减少了回表的次数,但是如果我们将前缀从6个增加到7个8个的话,前缀索引回表的次数就会减少,也就是说,只要定义好前缀的长度,我们就能既节省空间又保证效率。

那么问题来了,我们怎么衡量使用前缀索引的长度呢?

1、使用 select count(distinct email) as L from t; 查询字段不同值的个数;

2、依次选取不同的前缀长度查看不同值的个数:

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 t;

然后根据实际可接受的损失比例,选取适合的最短的前缀长度。

前缀的长度问题我们解决了,但是一个问题是,如果使用前缀索引,那我们索引覆盖的特性就用不到了。
用全字段索引时,当我们查询select id,email from t where email='zhangsh223@xxx.com';时,不用回表直接就能查到id和email字段。

但是用前缀索引时,MySQL并不清楚前缀是否会整个覆盖email的值,无论是否全包含都会根据主键值回表查询判断。

所以说,使用前缀索引虽然能节省空间保证效率但是却不能用到覆盖索引的特性,是否使用就在于具体考虑了。

其他字符串索引创建方式

实际情况实际考虑,并不是所有的字符串都能使用前缀截取的方式创建索引,如身份证号或者ip这些字符串使用前缀索引就不合理了,身份证号一般同一个地区的人前几位都是一模一样的,使用前缀索引就不合理了,而ip值我们一般在实际中将其转化为数字去存储。

针对身份证号,我们可以使用倒叙存储,取前缀创建索引或者使用crc32()函数来获取一个hash校验码(int值)当做索引。

倒叙:select field_list from t where id_card = reverse('input_id_card_string');

crc32:select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这两种方式相对来说效率都差不多,都不支持范围查找,支持等值查找。

在倒叙方式中,需要使用reverse函数,但是回表次数可能比hash方式多。

在hash方式中,需要新建一个索引字段并调用crc32()函数。(注意:crc32()函数获取的结果不保证能唯一,可能存在重复的情况,但是这种情况概率较小),回表次数少,几乎1次就行。

最后

针对字符串索引,一般有以下几种创建方式:

1、字符串较短,直接全字段索引

2、字符串较长,且前缀区分度较好,创建前缀索引

3、字符串较长,前缀区分度不好,倒叙或hash方式创建索引(这种方式范围查询就不行了)

4、根据实际情况,遇到特殊字符串,特殊对待,如ip。

看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。

阅读原文内容投诉

免责声明:

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

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

软考中级精品资料免费领

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

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

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

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

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

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

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