前缀索引

  1. mysql> alter table SUser add index index2(email(6));

优势

  • 占用空间更小。

局限性

  • 影响性能。可能增加额外的记录扫描次数。
  • 损失覆盖索引对查询性能的优化。

倒序存储

  1. mysql> select field_list from t where id_card = reverse('input_id_card_string');

市公民系统存储身份证场景。

hash 存储

  1. mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
  2. mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

倒序和 hash 的异同点

分析角度 倒序 hash
占用的额外空间 无消耗 多一个字段的存储
CPU 消耗 reverse 消耗小 crc32 消耗大
查询效率 会增加扫描次数 查询更稳定，hash 值冲突少

小结

索引方式 优点 局限性
完整索引 方便快捷 可能占用空间
前缀索引 节省空间
1. 增加扫描次数
2. 不能使用覆盖索引
倒序存储&前缀索引 解决字符串本身前缀区分度不够问题 不支持范围扫描
hash 索引 查询性能稳定
1. 额外的存储和计算消耗
2. 不支持范围扫描

原文链接：https://time.geekbang.org/column/article/71492