11 | 怎么给字符串字段加索引？

[git-zjx]

前缀索引

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引

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

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本，但如果长度不合适就会导致查询的次数过多，可以通过统计索引上有多少个不同的值来判断要使用多长的前缀

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;

而且使用前缀索引之后，就无法使用覆盖索引，因为系统并不确定前缀索引的定义是否截断了完整信息，所以还得去 ID 索引查询一遍

其他方式

倒序存储

遇到前缀的区分度不够好的情况时，可以使用倒序存储

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

hash 字段

在表上新建一个 hash 字段，存储字符串的 hash 值

使用倒序存储和使用 hash 字段这两种方法的异同点

相同点

都不支持范围查询，倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，hash 只支持等值查询

不同点

• 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
• 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
• 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。