如何给字符串字段加索引

水深无声 2023-06-12 05:25 8阅读 0赞

如果有这样一个场景，某个字段保存的是邮箱，然后需要给这个字段建立索引，有如下几种解决方案：

#### 全字段索引 ####

alter table user add index index1(email);

不建议用这种方式，因为索引树需要存储字段的全部值造成不必要的空间浪费

#### 使用前缀索引 ####

alter table user add index index1(email(6));

这种方式可以节约索引的存储空间，但是如何合理的设置前缀的长度呢？  
（1）可以先计算email这个字段上有多少行不重复的行数

select count(distinct email) as email_num from user;

（2）然后依次选取不同长度的前缀来看这个值，再做适当的设计

select count(distinct left(email,4)) as email_num4, 
            count(distinct left(email,5)) as email_num5,
            ...

使用前缀索引的最大的一个缺点是一定会回表，如果设置的前缀长度小于字段实际长度，肯定是需要回表做精确比较的，但是即使把前缀的长度设置为字段的实际大小，也就是辅助索引已经存储了该字段的全部值，但是也会进行一次回表比对，因为系统并不确定前缀的长度是否包含了完整信息。也就是说，使用前缀索引就无法使用覆盖索引。

> 对于邮箱这样的前缀在一定长度上重复率不大的情况下，使用前缀索引是不错的选择，但是如果有这样一个需求，需要对身份证号码做索引，而且这个表存储的只是一个市的居民身份证号码，身份证号码规则是这样的：一个18位，前6位的地址码，所以同一个地区的人的身份证号码前6位是一样的，这种情况下前缀索引就不好用了，下面给出解决方案。

#### 字符串逆序之后再使用前缀索引 ####

这种方式很适合上面提到的一个市的身份证号码字段，还有一种情况是该字段时一个时间戳，那前几个数字相似度很高等等。

#### 使用hash字段 ####

在数据库表上再增加一个字段存储需要建立的索引字段的hash值，对应的hash值可以用数据库（MySQL）的crc32()或者crc64()函数来计算，这种方式会有不同的字段值对应的hash值相同，因此每次查询的时候where后面加上索引比较之后还要进行精确值的比较：

select  field from t where id_card_crc=crc32('input_id_card') and id_card=id_input_id_card

#### 总结 ####

（1）从占用额外的空间上看，采用哈希值字段索引会占用一个额外的字段空间，但是如果前缀的长度比较长的话，这个差距就可以忽略不计。  
（2）从cpu消耗上看，使用哈希索引需要调用crc32()函数。  
（3）从查询效率上看，使用hash值字段索引性能更稳定，因为crc64()结合了md5算法将冲突的概率降到了很低，因此平均扫描次数接近1。使用前缀索引会增加扫描次数。  
（4）无论使用哪种凡是，都无法进行覆盖索引，也无法使用范围查找