Mysql - 前缀索引

前缀索引的优劣

很多情况下,我们需要根据一个长字符串类型的字段去查找记录,比如身份证、邮箱,为了避免全表扫描,就需要为字符串字段添加索引。
由于Mysql支持前缀索引,所以我们可以选择将整个字段添加索引,或者只将前一部分的字符串加上索引:
#整个字段
alter table T add index index1(email);
#一部分字段(前缀索引)
alter table T add index index2(email(6));
假设我们执行一条查询sql:
select id,name,email from T where email='zhangssxyz@xxx.com';

对于完整索引:
从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录,取得 ID2 的值;
到主键上查到主键值是 ID2 的行,判断 email 的值是正确的,将这行记录加入结果集;
取 index1 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email='zhangssxyz@xxx.com’的条件了,循环结束。

而对于前缀索引:
从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;
到主键上查到主键值是 ID1 的行,判断出 email 的值不是’zhangssxyz@xxx.com’,这行记录丢弃;
取 index2 上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;
重复上一步,直到在 index2 上取到的值不是’zhangs’时,循环结束。

缺点
1)索引覆盖失效:由于前缀索引在命中以后,必须再回主键索引树确定一次(需要回表进行判断),所以索引覆盖对前缀索引来说是无效的回表次数多,使用前缀索引后,可能会导致查询语句读数据的次数变多。 

2)无法使用前缀索引进行order by 和 group by

优点: 

能使索引更小,查询速度更快

如何选择合适的长度

前缀索引需要有足够的区分度才能提高查找效率。比如有ABCC,ABDD,ABEE三条数据,选前两个个字符作为索引就等同于没加索引,选前三个字符作为索引就很合适。当然,实际情况肯定会更复杂,需要更具体的分析。
首先,算出这个列上有多少个不同的值:
select count(distinct email) as L from T;
依次选取不同长度的前缀来看这个值,比如要看一下 4~7 个字节的前缀索引,可以用这个语句:
复制代码
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;
复制代码
使用前缀索引必然会损失一部分区分度,所以我们需要预先设定一个可以接受的损失比例比如 5%,然后在返回的 L4~L7 中,找出不小于 L * 95% 的值,然后选择最短的长度。

其他优化方式

  对于邮箱,前缀索引效果还比较明显,因为@之前的字符串一般不会有太多的相似度,但是对于比如身份证,同一个地方的市民只有后几位才会有较大区别的长字符串,可能就需要设置一个非常长的前缀索引了,这显然不是我们乐意见到的。

倒序存储

借助reverse()函数实现倒序存储。比如身份证存入的时候可以倒序存储,查找的时候也先反转在查找,这样加索引以后只需要选择前几位辨识度高的即可。

Hash字段

借助crc32/64()函数去获取长字符串的校验码,在表上另开一个字段用于存储对应的校验码,以长度较短的校验码作为索引。不过由于crc32仍然会出现值重复的情况,所以查询的时候还需要判断拿到的记录是否与条件字段完全一致。

相同点

  都不支持范围查找,只能等值查询

不同点

1)占用空间:倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段。
2)额外消耗:倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数,而 hash 字段的方式需要额外调用一次 crc32() 函数。从函数的复杂度来看,reverse 函数额外消耗的 CPU 资源会更小些。
3)查询效率:使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32() 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式还是用的前缀索引的方式,也就是说还是会增加扫描行数。

适合场景

  • 字符串列(varchar,char,text等),需要进行全字段匹配或者前匹配,也就是='xxx' 或者 like 'xxx%'
  • 字符串本身可能比较长,而且前缀字符不相同。比如我们对中国人的姓名使用前缀索引就没啥意义,因为中国人名字都很短,另外对收件地址使用前缀索引也不是很实用,因为一方面收件地址一般都是以XX省开头,也就是说前几个字符都是差不多的,而且收件地址进行检索一般都是like ’%xxx%’,不会用到前匹配。相反对外国人的姓名可以使用前缀索引,因为其字符较长,而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
  • 前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20,索引选择性为0.9,而我们对前10个字符建立前缀索引其选择性也只有0.5,那么我们需要继续加大前缀字符的长度,但是这个时候前缀索引的优势已经不明显,没有太大的建前缀索引的必要了。

总结

在向字符串类型的字段加索引的时候,需要考虑前缀索引是否合适,实在不行再加全字段索引

1)、全字段索引相比于前缀索引占用的空间多些
2)、创建前缀索引节省空间,但是会增加查询的扫描行数,并且加了之后不能使用覆盖索引
3)、倒序存储是基于前缀索引的改良版,用于字符串本身区分度不高的情况下
4)、创建hash字段索引,查询稳定但需增加一个额外的字段 

Mysql - 字符串字段如何创建索引

posted @   李若盛开  阅读(521)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
点击右上角即可分享
微信分享提示