场景导入
当下有一个需要维护的支持邮箱登录的系统，用户表的定义如下：
create table SUser(ID bigint unsigned primary key,email varchar(64), ... )engine=innodb;
因为登录方式是邮箱，所以必然会有类似这样的业务操作：
select f1,f2 from SUser where email='xxx';
对于上述语句，既可以不对email字段设置索引，也可以给email建立普通索引，还能建立前缀索引。建立索引的语句如下：
alter table SUser add index index1(email);
alter table SUser add index index2(email(6));
这两种不同建立方式在数据结构和存储方面的区别如下：
由于前缀索引只截取规定字节数，所以占用空间更小。
不过使用前缀索引可能会增加额外的记录扫描次数。例如要执行下面这个查询：
select id,name,email from SUser where email='zhangssxyz@xxx.com'
如果使用普通索引，执行流程是：
-
从index1索引树中找到符合条件的记录，获取主键为ID2；
-
在主键索引树上找到对应的行，若email值正确，就把这行记录加入结果集；
-
从index1索引树上查找下一条记录，发现不满足条件，结束。
因为只回主键索引取一次数据，所以系统认为只扫描了一行。
如果使用前缀索引，执行流程是：
-
从index2索引树中找到符合条件的记录，第一个是ID1；
-
在主键索引树上找到对应的行，发现email值不正确，丢弃这行记录；
-
从index2索引树中找到满足条件的下一条记录，获取主键为ID2；
-
在主键索引树上找到对应的行，若email值正确，将这行记录加入结果集；
-
重复上述步骤，直到index2上匹配不到正确的前缀。
这个过程需要回主键索引取4次数据，也就是扫描了4行。
但是，在这个场景下，如果把index2定义为email(7)，会发现在index2上能直接取到ID2，这样就只需要扫描一行。所以当使用前缀索引时，只要定义好长度，就能做到既节省空间，又不会额外增加太多查询成本。
那前缀的长度该怎么确定呢？
建立索引时，需要关注字段的区分度，区分度越高，重复的键值越少。
可以用下面的语句计算列上有多少不同的值：
select count(distinct email) as L from SUser;
然后，可以依次选取不同前缀长度来看：
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里需要选取≥95% L的值。
前缀索引对覆盖索引的影响
前缀索引除了可能增加扫描行数外，还有其他影响。比如下面这两个语句：
select id,email from SUser where email='zhangssxyz@xxx.com';
如果使用index1（整个字符串的索引结构），可以利用覆盖索引，在index1查到结果就直接返回，不需要回表；而如果使用前缀索引，就必须回表，即使使用整个字符串的长度email(18)，依然需要回表，因为系统不确定前缀索引的定义是否截断了完整信息。
在这个例子中，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是在选择是否使用前缀索引时需要考虑的一个因素。
其他方式
对于邮箱这类字段，由于用户名差异较大，使用前缀索引的效果可能不错。但有些字段前缀区分度不好，比如身份证号，这时候如果使用前缀索引，可能需要创建长度较长的前缀索引，才能满足区分度要求。但这也意味着索引占用磁盘空间越大，搜索效率会越低。
假如确定业务需求里只有按照身份证进行等值查询的需求，那么有更好的处理方式，既可以占用更小空间，又能达到相同查询效率：
（1）使用倒序存储
把身份证号倒过来存储，这样可能取6位就有足够的区分度。每次查询时，用下面的方式：
select field_list from t where id_card = reverse('input_id_card_string');
（2）使用hash字段
可以在表上再创建一个整数字段，比如每次插入新纪录时用crc32()函数得到一个校验码字段。因为校验码可能冲突，在查询时候需要判断id_card字段是否相同。
看上面两种方法的异同：相同点是都不支持范围查询，不同点有：
-
占用额外空间不同。倒序存储不消耗额外存储空间，而Hash字段需要增加一个字段。
-
CPU消耗不同。倒序存储方式每次读写都需要额外调用一次reverse函数，而Hash方式需要额外调用一次
crc32()函数，从两个函数的计算复杂度看前者更小。 -
查询效率不同。使用Hash方式的查询性能相对更稳定，因为
crc32()冲突概率小，可以认为每次查询的平均扫描行数接近1，而倒序存储方式毕竟还是使用前缀索引方式，会增加扫描行数。
相关文章
