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，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。