读mysql45讲-字符串加索引

假设需要给一个邮箱字段添加索引，邮箱通常是存储为字符串类型的。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。而倒序存储 方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。