侧边栏壁纸
  • 累计撰写 79 篇文章
  • 累计创建 84 个标签
  • 累计收到 7 条评论

目 录CONTENT

文章目录

MySQL学习-字符串字段如何添加索引

汤圆学Java
2022-02-07 / 0 评论 / 0 点赞 / 55 阅读 / 2,711 字
温馨提示:
本文最后更新于 2022-02-07,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

前言

给字符串字段添加索引有多种方式,最简单的就是给整个字段添加索引,这样的好处显而易见就是查询字符串字段时不需要回表操作;

当然还有其他的添加索引的方式,比如给字符串的前半部分添加索引、给字符串倒序处理后再添加索引、以及ha sh处理后再添加索引;

下面我们就挨个介绍,给字符串字段添加索引的这几种方式;

目录

  1. 整个字段添加索引
  2. 前部分字段添加索引
  3. 倒序字段添加索引
  4. hash字段添加索引

正文

这里我们以下面的SQL语句为例,进行介绍:id_card是用户的身份证号

select * from t_user where id_card = 142733xxxxx

1. 整个字段添加索引

如果简单的给整个字段id_card添加索引,那么查询也会很简单;

大致的一个查询步骤如下:

  1. 先去id_card索引树中定位到具体的索引值142733xxxxxx
  2. 然后根据索引中的主键值,回表查询所有的数据,并添加到结果集;
  3. 继续去索引树中查询下一条记录,如果满足则重复步骤2;不满足就结束;

这种方式最大的好处就是:回表查询时,查询的都是满足条件的行;

即都是精确查询,非模糊查询;

2. 前部分字段添加索引

即前缀索引,就是给字符串的前部分N个字符添加索引:add index index_card(id_card(6))

这里我们指定的6就是说给前6个字符添加索引;

这样的查询步骤如下:

  1. 先去id_card索引树中定位前缀为142733的索引值(可能有多个,这里会取第一个);
  2. 然后根据索引中的主键值,回表判断id_card的值是否和where条件的id_card值一致;
    1. 如果一致,则查询行记录的所有数据,并添加到结果集;
    2. 如果不一致,则重复步骤1,继续根据前缀142733定位索引;
  3. 继续去索引中查询下一条记录,如果满足则重复步骤2;不满足就结束;

这种方式跟第一种比起来,有一个很明显的区别就是:回表查询数据时,需要先比对id_card的完整字符串是否跟筛选条件where中的id_card值一致(因为索引是根据前缀6个字符索引的,所以后面的字符串不确定是不是一致);

即都是模糊查询,非精确查询;

这里的优点就是节省空间,比如这里的身份证号,默认有18位,但是用了前缀索引后只需要6位;

但是缺点也很明显:

1. 会扫描多行数据,而且是在回表操作之后,才能确定哪些数据是有效;
1. 会影响到覆盖索引:因为每次前缀索引,都要回表查看记录,比较查询的值是否相等(因为前缀索引只包括了前面几个字符);所以如果查询语句有用到覆盖索引,那么覆盖索引就会失效

覆盖索引:就是如果查询的数据只有索引值和主键值,那么就不用回表查询记录

问:那要怎么优化呢?

答:选择合适的前缀索引长度,确保前缀的索引值足够区分字符串,也就是索引的区分度越高,前缀索引的查询性能越好;

比如上面的身份证号的例子,前6位是根据省市县进行区分的(细节如下图所示),那就是说:如果是一个县城或者市区的人,那么他们的前6位都是一致的,这样就会大大增加查询的工作量;

这时我们就可以稍微往后加几位,将生日包括进去,这样索引的区分度就会大大增加,查询的工作量会减小很多;

查看源图像

3. 倒序字段添加索引

就是将字段中的字符串倒序存储,然后再给前部分字段添加索引;也就是先倒序、再前缀索引;

这样看起来会有点走弯路,但是对于上面的身份证号的例子来说,会很适用;

mysql中倒序处理对应的函数为:reverse();

因为身份证号的后面几位区分度远高于前面几位,这样我们在查询的时候只需要用reverse函数进行倒序查询就可以了,如下所示:

select * from t_user where id_card = reverse(142733xxxxx)

4. hash字段添加索引

就是先将字段值hash处理添加索引

这个跟上面的倒序存储添加索引有点类似,都是要对现有的字段值进行处理后再存储;

不过这个hash处理字段后,结果会有冲突的可能性,所以这里的hash处理结果需要额外添加一个字段用来存储;

mysql中hash处理对应的函数为:crc32();

此时插入数据时,需要对字段值进行crc32()处理后再插入;

查询数据时,需同时查询crc32()的结果和原有的字段:因为crc32的结果有冲突的可能(虽然几率比较小)

select * from t_user where id_card_crc=crc32('142733xxxx') and id_card='142733xxxx'

总结

上面介绍了四种存储方式,前两种比较简单,整个字段索引和前缀索引;

后面的两种会有点复杂,这里我们总结下他们两种的区别:

倒序存储hash存储
存储空间正常多一个字段,来存储hash值
CPU消耗调用reverse()函数调用crc32()函数,比reverse()消耗高一些
查询效率可能会扫描多行只要hash值不冲突,就只需要扫描一行
范围查找不支持不支持
0

评论区