2 前缀索引对覆盖索引的影响

看如下SQL：

select id,email from SUser where email='zhangssxyz@xxx.com';

与前例SQL语句：

select id,name,email from SUser where email='zhangssxyz@xxx.com';

相比，该语句只要求返回id和email。

若使用index1，可利用覆盖索引，从index1查到结果后直接返回，不需回到ID索引再查一次。

而若使用index2（email(6)），得回ID索引再判断email字段值。

即使将index2定义改为email(18)，虽然index2已包含所有信息，但InnoDB还是要回id索引再查，因为系统并不确定前缀索引的定义是否截断了完整信息。

即前缀索引根本用不上覆盖索引对查询的优化，这也是选择是否使用前缀索引时需要考虑的因素。

3 其他方案

对类似邮箱这样字段，前缀索引可能还行。但遇到前缀区分度不好的，怎么办？

比如身份证号18位，前6位是地址码，所以同县人身份证号前6位一般相同。

假设维护数据库是个市公民信息系统，若对身份证号做长度6前缀索引，区分度非常低。

可能需创建长度12以上前缀索引，才能够满足区分度要求。

但索引选取越长，占磁盘空间越大，相同数据页能放下索引值越少，查询效率就越低。

• 若能确定业务需求只有按身份证进行等值查询的需求，还有没有别的处理方法，既可占用更小空间，也能达到相同查询效率？
  Yes！

第一种方式使用

3.1 倒序存储

如果存储身份证号时把它倒过来存，每次查询这么写：

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

由于身份证号最后6位没有地址码这样重复逻辑，所以最后6位可能提供足够的区分度。

实践中也别忘记使用count(distinct)验证区分度哦！

第二种方式是使用

3.2 hash字段

可在表再创建整数字段，保存身份证的校验码，同时在该字段创建索引。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

每次插新记录时，同时用crc32()函数得到校验码填到该新字段。

由于校验码可能存在冲突，即两不同身份证号crc32()所得结果可能相同（哈希冲突），所以查询语句where部分要判断id_card值是否精确相同。

select field_list from t where 
id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这索引长度变4字节，比原来小很多。

3.3 倒序存储和使用hash字段异同点

相同点

都不支持范围查询。

• 倒序存储的字段上创建的索引
  按倒序字符串的方式排序，已无法利用索引查出身份证号码在[ID_X, ID_Y]的所有市民
• hash字段也只支持等值查询