如何给字符串字段添加索引

MySQL是支持前缀索引的，可以定义字符串的一部分作为索引，如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

alter table SUser add index index1(email);alter?table?SUser?add?index?index2(email(6));

如上两个创建索引的语句，index2(email(6))这个索引结构中只取邮箱字段的前6个字节，所以占用的空间会更小，这就是使用前缀索引的优势，但是也同时带来了损失，比如会增加额外的记录扫描次数。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。这个和实际存储的数据有关。也就是需要关注数据的区分度，区分度越高越好，区分度越高，意味着重复的键值越少。因此，可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

select count(distinct email) as L from SUser;

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%的值。

前缀索引也会影响覆盖索引，既前缀索引就用不上覆盖索引对查询性能的优化了。

如果业务需求上只有等值查询，在对身份证号（前6位是地址码，前缀区分度比较小）这类数据设置前缀索引时还可以采取以下两种方法：

1、使用倒序存储，查询的时候参考下述语句

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

2、使用hash字段，即在表上再创建一个整数字段，来保存身份证号的校验码，同时在这个字段上创建索引。

-- 添加字段并创建索引alter table t add id_card_crc int unsigned, add index(id_card_crc);-- 查询语句select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

使用倒序存储和hash字段都不支持范围查询。

正文止。

感兴趣的朋友，欢迎关注我的公众号哈，公众号上已经集成了AI大模型，大家可以过来聊天、问问题了