一文搞懂MySQL前缀索引

引入

通常在开发中我们需要定义字符串类型的字段，例如用户名或者用户邮箱等。
假设我们在维护一个用户登录系统，用户表的定义：

create table User(
ID bigint unsigned primary key,
email varchar(64)

)engine=Innodb;

如果使用邮箱登录的话，查询语句可能这样写：

select ID  from User where email='xxx';

如果email字段没有加索引，那么这个语句只能做全表扫描。

前缀索引

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。如果不指定前缀索引，那么索引就是整个字符串。

例子：

alter table User add index index1(email);
alter table User add index index2(email(6));

第一句SQL创建的索引就是将email整个字符串作为索引；第二个SQL语句创建的索引，只取email字符串的前6个字节作为索引。

存储过程中的具体区别如下图所示

显然可以从图中知道，email(6)这个索引结构中每个邮箱字段只取前6个字节，所以占用的空间更少，这就是使用前缀索引的优势。

缺点：
可能会额外的增加记录扫描的次数。

这个该怎么理解呢？

select id,name,email from User where email =' zhangsan@xx.com';

使用的是将整个字符串作为索引结构。
过程如下：

1. 从index1索引树上找到索引值是"zhangsan@xx.com"的这条记录，去的ID2的值
2. 到主键中查ID2的这一行，判断email的值是否是正确的，将这行记录装入结果集中；
3. 再回到index1这个索引树上，继续判断下一条记录，发现不满足where条件，结束循环。

这个过程中只需要从主键索引树上查找一次数据，系统自认为扫描了一行。

使用前缀索引的执行过程

1. 从index2的索引树上，找到满足索引值是“zhangs”的记录，找到第一个是ID1；
2. 到主键索引树上查到ID1这一行，判断email的值满不满足where后的条件，不满足这一行丢弃。
3. 继续回到index2这个索引树上查下一条记录，发现如果还是"zhangs"，取出ID2，再回到ID2索引树上进行判断，如果值正确，将结果返回结果集中。
4. 重复执行以上流程，直到从index2索引树上取出的数据不是“zhangs”,循环结束。

通过以上执行流程的分析你就可以知道，前缀索引会导致扫描的行数变多，这和你所指定前缀的长度有关。或许email(7)中的区分度就比email(6)高，就不会扫描那么多行。

也即是说使用前缀索引，定义好长度，就可以节省空间又不用额外增加太多的查询成本

那怎样定义前缀索引长度比较好呢？

实际上，建立索引时关注的是区分度，区分度越高，越能体现索引的价值和他的优势。因此我们可以通过统计索引上有不同的值来判断要使用多长的前缀。

select count(distinct email) as L from User;

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

前面我们说了使用前缀索引可能会增加扫描行数，这会影响性能。其实前缀索引的影响不止如此：

select id ,email from User where email='zhangsan@xx.com';
select id , name, email from User where email='zhangsan@xx.com';

第一句SQL相比于第二条SQL，只返回了id和email。如果使用email整个字符串作为索引的话，可以利用覆盖索引，从index1查到结果直接返回，不需要回表。但是如果使用前缀索引的话，是需要回表进行判断的。

倒序存储与Hash存储

在选取索引的时候，我们需要明白：索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越小，搜索的效率也就会越低。

如果我们在区分度不是很高的场景下，前缀索引的效果就不明显了，我们该如何才去措施提高查询效率。

采用倒序存储方式

select filed_list from t where id_card = reverse('input_id_card')

因为字符串正序的区分度不够明显所以可以看看如果采用倒序的话情况如何，如果倒序的区分度更高，可以采用这种方式。

采用Hash字段

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

这里在表t中多加入了一个字段 id_card_crc并把它作为索引。
然后每次插入新纪录的时候，都用crc32函数得到校验码填充到这个新字段中。由于产生的校验码也有可能冲突（相同）所以查询条件部分需要判断id_card的值是否相同。

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

两者的对比

相同点

1. 都不支持范围查询，只能等值查询。
   不同点
2. 从查询效率上看，使用的hash字段方式的查询性能相对稳定一点，因为crc_32算出的值虽然有可能冲突，但是概率还是很小的。而倒序方式其实还是用的前缀索引的方式还会增加扫描行数。
3. 从存储空间上看，倒序存储不会在主键上消耗额外的空间，但hash字段需要增加一个新字段。
4. 从CPU消耗来讲，倒序每次写和读的时候都需要调用reverse函数；hash字段的方式需要嗲用crc32函数。从函数的复杂度讲，reverse效率更高一些。

总结

在向字符串类型的字段加索引的时候，需要考虑前缀索引是否合适，实在不行再加全字段索引。

1. 全字段索引相比于前缀索引占用的空间多些。
2. 创建前缀索引节省空间，但是会增加查询的扫描行数，并且加了之后不能使用覆盖索引。
3. 倒序存储是基于前缀索引的改良版，用于字符串本身区分度不高的情况下。
4. 创建hash字段索引，查询稳定但需增加一个额外的字段。