3、给字符串加索引

  • Z时代
  • 2024-01-10
  • 分类：综合

编程

使用前缀索引的优点
1) 占用空间小
2) 定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本
使用前缀索引的缺点
1) 不能使用覆盖索引
2) 长度定义不好,会带来额外的记录扫描次数

于是,你就有个问题:当要给字符串创建前缀索引时,有什么方法能够确定我应该使用多长的前缀呢？实际上,我们在建立索引时关注的是区分度,区分度
越高越好.因为区分度越高,意味着重复的键值越少.因此,我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀.

mysql> 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 SUser;

使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比例,比如 5%.然后,在返回的 L4~L7 中,找出不小于 L * 95% 的值,
假设这里 L6、L7 都满足,你就可以选择前缀长度为 6

2、倒序存储

对于类似于邮箱这样的字段来说,使用前缀索引的效果可能还不错.但是,遇到前缀的区分度不够好的情况时,我们要怎么办呢? 
比如,我们国家的身份证号,一共 18 位,其中前 6 位是地址码,所以同一个县的人的身份证号前 6 位一般会是相同的.
假设你维护的数据库是一个市的公民信息系统,这时候如果对身份证号做长度为 6 的前缀索引的话,这个索引的区分度就非常低了.
按照我们前面说的方法,可能你需要创建长度为 12 以上的前缀索引,才能够满足区分度要求.
但是,索引选取的越长,占用的磁盘空间就越大,相同的数据页能放下的索引值就越少,搜索的效率也就会越低.
那么,如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求,还有没有别的处理方法呢？这种方法,既可以占用更小的空间,也能达到
相同的查询效率.

第一种方式是使用倒序存储
存储身份证号的时候把它倒过来存,每次查询的时候,你可以这么写
mysql> select field_list from t where id_card = reverse("input_id_card_string");

3、使用Hash字段

你可以在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候,都同时用 crc32() 这个函数得到校验码填到这个新字段.由于校验码可能存在冲突,也就是说两个不同的身份证号通过 
crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 id_card 的值是否精确相同
这样,索引的长度变成了 4 个字节,比原来小了很多
mysql> select field_list from t where id_card_crc=crc32("input_id_card_string") and id_card="input_id_card_string"

1) 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段.当然,倒序存储方式使用4个
   字节的前缀长度应该是不够的,如果再长一点,这个消耗跟额外这个 hash 字段也差不多抵消了.
2) 在 CPU 消耗方面,倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数,而 hash 字段的方式需要额外调用一次 crc32() 函数.如果
   只从这两个函数的计算复杂度来看的话,reverse 函数额外消耗的 CPU 资源会更小些.
3) 从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些.因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询
   的平均扫描行数接近 1.而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数

1) 直接创建完整索引,这样可能比较占用空间
2) 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引
3) 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题
4) 创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描

以上是 3、给字符串加索引 的全部内容， 来源链接： utcz.com/z/516033.html

回到顶部