字符串索引的优化

目录

前言

要不要给字符串加索引

前缀索引

字符串索引其他方式的优化

---

前言

字符串加索引无非就是两种方式，一是给整个字符串加索引，二是给部分字符加索引也就是前缀索引。这两种方式各有优点吧，他们的区别是什么？究竟怎么加索引，要不要加索引，怎么加索引，要根据具体的业务而定。这篇文章讲详细阐述这几个问题。

字符串索引和主键索引有很大区别，作为主键索引，我们一般会保证主键递增，也就不会有页的分裂。而字符串索引由于业务的原因，没法保证数据的顺序性，就会有页的分裂来保证索引的顺序性，还好非聚簇索引，叶子节点没有整行的数据。页的分裂成本相对来说比较低，确实存在，这是题外话，说这么多，就是要告诉大家，普通索引也是有成本的。

为了接下来讲这几个问题，比较明了，先举个例子，维护用户的Email 信息

create table user_email(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb; 

要不要给字符串加索引

如果这个字符串区分度很低，也就是重复度很大，加索引起不到任何作用，在查询时，不一定会用到索引。那什么是区分度呢？这个是可以计算的。可以这么计算：

1. select count(email ) as L from user_email;

2. select count(distinct email) as L1 from user_email;

如果L1/L>=95%以上就说明区分度很高，就可以用索引。如果字符串很长，对它索引这类的，也不适合加索引，需要用到Es

前缀索引

比如说Email 最后面几位都是域名 .com 什么的，都设置成域名，数据很多，会占用大量的磁盘空间。不仅不利于存储而且也不利于查找，我们就可以想想用前缀索引也就是说索引前n个字段，索引几个字段呢？大家可以用这种方法计算。

1. select count(distinct email) as L from user_email;

2. 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 user_email;

我们可以用L4、L5、L6、L7/L 看哪些满足95%，选择字段长度最小的那个就可以了。合适的前缀索引不仅有利于存储而且有利于查找。

选择不合适的前缀索引有什么影响，不合适意味着前缀索引重复度比较高，小于95%以下。

他们在查询时有什么区别，字符串索引是(index1)，前缀索引是（index2）

字符串索引查询：

1. 从index1找到满足where 一行记录

2. 根据id 到主键上找到那一行，判断email 是否正确，将这行记录加入结果集

3. 到index1 下一行，不满足条件就结束

前缀索引查询：

1. 从index2找打满足条件的一行

2. 根据id 到主键上找到那一行，判断email 是否正确，不正确丢弃那一行

3. 从index2找打满足条件的下一行，到主键找到那一行，如果email 准确，将这行记录加入结果集

4. 直到index2不满足条件那一行。

也就是说前缀索引是字符串前n个字符，索引的重复度可能比较高，会多扫描，index1 可能是 zhangsan1@163.com , index2 可能是zhangsan1，也可能是zhangsan。根据索引比较，回表确定where 条件。

这么描述不知道大家能不能理解。

前缀索引那么好，有什么缺点点，当然有，就是不能用到索引的覆盖的优化，不管条件如何，都会到主键回表查询。哪怕就是前缀索引覆盖了索引的字符串即email(18),也用不到。

字符串索引其他方式的优化

有时前缀索引的区分度不高，比如，我们国家的身份证号，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。

假设你维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为 6 的前缀索引的话，这个索引的区分度就非常低了。按照我们前面说的方法，可能你需要创建长度为 12 以上的前缀索引，才能够满足区分度要求。

但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。那么，如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法呢？这种方法，既可以占用更小的空间，也能达到相同的查询效率。

1. 倒叙存储，倒叙查询。身份证号倒叙存储，就可以用前缀索引，input_id_card_string(6)，那么查询，select field_list from t where id_card = reverse('input_id_card_string');用前缀索引一定要考虑用区分度去判断

2. 多加一个字段，把身份证号进行hash 存储，需要四个字节的 unsigned 就可以了，对这个新字段加索引，比如新加的字段是id_card_crc。select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这种方式适合区分度不高的情况，各有优势吧，看大家的选择，他们区别是

1. 磁盘消耗：倒叙存储不同加新字段，不会加入额外的空间存储，而hash 存储需要加入新字段。

2. cpu 消耗：他们都会用到函数计算，消耗cpu，reverse函数的计算复杂度低于 crc32

3. 稳定性：hash 虽然会有冲突，冲突不会太大，前缀索引，一些数据会有很多重复行，毕竟是前缀索引