假设需要给一个邮箱字段添加索引,邮箱通常是存储为字符串类型的。mysql是支持前缀索引的,也就是也可以将邮箱的前几个字符作为索引,如果不设置长度,则默认为整个字段。
前一个是将整个字段都作为索引,后一种则是将name的前两个字符作为索引;后一种的索引结构因为只有name的前两个字符,所以占用的内存更小,这是前缀索引的优势,但也有不好的地方。
select id,name,address from tableName where name = ‘张一二’
如果是使用的是前一种索引:
- 在索引树上找到name满足条件的索引项,
- 根据索引项中的id值,去表中查询这条id记录对应的name,判断是否真的满足条件,满足就加入结果集
- 在满足条件的索引项的下一条记录,判断是否也符合条件,不符合的话就结束,符合就重复步骤,一直找到不符合条件的时候终止。
如果使用的是后一种索引:
- 在索引树上找到满足条件的索引项,即前两个字符是【张一】的就认为满足条件
- 根据找到的索引项的id,到主表中判断是否是真的全符合条件,不是就丢弃,是的话就加入结果集
- 在找到的索引项的下一条记录中,判断是否前面两个字符也是【张一】,是的话就重复步骤;不是则就结束
可以看到,两者的区别就是满足条件的不一样;因为后一种索引的字段短了点,所以可能满足条件的记录就多一点,就需要多次判断,这样回表的次数会变多。
那如果后一种索引结构的长度再长一点,变为3,那也可以满足这次的查询条件,所以关键在于,如何找到这个合适的长度。
在建立索引的时候,可以先查询当前表的name字段中所有值的分布情况。然后截取前几个字符,查看重复的情况。
之前查出所有name假设为100个,L4是70个,L5是80个,L6是90个,L7是96个;那如果为了准确率是90%以上,那就索引的长度就可以截取前6个。
如果查询的sql是
select id,name,from tableName where name = ‘张一二’
这个sql和前面的sql的区别就是查询字段少了address;根据覆盖索引,那这个sql就不需要再回表根据id去查询adress的值;
但是如果使用的是前缀索引,索然后一种前缀已经包含了id,和name字段,但是因为name字段上存的不是完整的值,所以还是需要根据id去回表查询一下,确认name字段是否是真的符合条件的。
也就是说,使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是在选择是否使用前缀 索引时需要考虑的一个因素。
如果在对身份证使用前缀索引的时候,就会有一些问题,因为正常一个省或一个城市的身份证的开头几位都是相同的,而且年份和月份也都是都很多相同的,所以对身份证号码字段使用前缀索引的话,就需要长度超过12位以上的才行,但是那用占用的空间也会很大。
倒叙存储
可以在将身份证号码入库的时候倒序存储,尾部数字通常是有识别性的,那用前缀索引的长度就可以比正序存储的短很多。
hash
也可以在表中添加一个hash字段,专门存储身份证号码经过特定的hash算法之后得到的值。
这两种方法都不支持范围查询,只能做等值查询。它们的区别,主要体现在以下三个方面:
- 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而hash字 段方法需要增加一个字段。当然,倒序存储方式使用4个字节的前缀长度应该是不够的,如 果再长一点,这个消耗跟额外这个hash字段也差不多抵消了。
- 在CPU消耗方面,倒序方式每次写和读的时候,都需要额外调用一次reverse函数,而hash 字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的 话,reverse函数额外消耗的CPU资源会更小些。
- 从查询效率上看,使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽 然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近1。而倒序存储 方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。