字符串加索引

如何使用前缀索引

一．前缀索引占用的空间会更小：

email建立索引，email(6)来构建索引。

二．查询语句读数据的次数可能会变多：查询email = “zhangsan@xxx.com”

1. 索引为email，在email索引树上查到这条记录，然后到主键索引树上查询到这一行，判断email是否满足，然后查索引树的下一条记录

2. 索引为email(6)，在email(6)索引树上查到满足zhangs的记录，然后到主键索引树上查询到这一行，判断Email是否满足，然后从索引树查询下一条记录，发现还是满足zhangs，继续，直到在索引树上查到不是zhangs时，结束

用email(6)来构建的话，可能重复的比较多，可能用email(7)来构建重复的就不多了

总结：使用前缀索引，定义好长度，可以既省空间，查询成本也不会增加太多。

三．如何定义前缀索引的长度

通过统计索引上有多少个不同的值来判断要使用多长的前缀。

select count(distinct email) from user;

然后选取不同的长度的前缀索引

select count(distinct left(email,4)), count(distinct left(email,5)), count(distinct left(email,6)), count(distinct left(email,7)) from user;

使用前缀做因会损失区分度，在可接受的比例范围内就可以选取使用该长度的前缀索引。

四．前缀索引会对覆盖索引有影响

Select id,email from user where email = ‘zhangsan@xxx.com’;

Select id,name,email from user where email = ‘zhangsan@xxx.com’;

第一个查询语句只要求返回id和email，如果使用email真个字符串作为索引，可以利用覆盖索引，从索引树查到结果就直接返回

第二个查询语句还需要到主键索引树上再去判断email的值，然后返回结果

其他索引方式

比如身份证号，前6位是地址码，如果是同一个地区的人，用长度为6的前缀索引区分度很低。

1. 可以使用倒叙存储

2. 使用 hash 字段

1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。

2. 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。

3. 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。