mysql实战(十一)如何给字符串加索引

1、前缀索引

这里有这样一张用户表：

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), //邮箱字段
... 
)engine=innodb; 

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

上面的语句，假设是分别给SUser这张表的邮箱字段添加索引，一个邮箱的字符可能很长，使用第二种方式，占用的空间会更小。

不过这里同时带来的损失是，可能会增加额外的记录扫描次数。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。但是在建立前缀索引时，关注的时区分度。区分度越高，意味着重复的键值越少，查询时扫描B+搜索树的次数越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

首先，你可以使用下面这个语句，算出这个列上有多少个不同的值：

mysql> select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引，可以用这个语句：

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 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

tips：前缀索引会导致覆盖索引不可用，因为虽然就算在非主键索引树上找到这个键，但是依然需要回表去主键索引上查找一次，系统并不能确定前缀索引的定义是否截断了完整信息。

其他方式

对于邮箱这样的字段来说，使用前缀索引的效果可能还不错，但是对于身份证这样前缀区分度不好的字段来说，如何建立索引呢？
1、利用倒叙存储。
查询时可以这么写：

mysql> select * from t where id_card = reverse('身份证号码');

2、使用hash字段。
可以再创建一个整数字段，保存身份证的校验码，同时在该字段上创建索引。
然后每次插入新记录的时候，都同时用 某个hash函数（例如crc32() ）得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

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

不过这两种方式都不支持范围查询了。

总结

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。