文章目录

  • 怎么给字符串字段加索引？
  • 前缀索引
  • 问：什么是前缀索引？作用是什么？
  • 问：使用前缀索引需要注意什么？
  • 实战题
  • 问：如果要存储一个市的身份证信息，前6位相同，此时怎么设计身份证索引最好。
  • 问：上面两个方案有什么区别？



——————————————————————————————

前缀索引

问：什么是前缀索引？作用是什么？

答：指定字符字段的前n个字符带索引，即为前缀索引。其占用空间小，但可能会增加额外的记录扫描次数，因此使用时需要根据业务情况综合设置前缀索引的长度。

alter table SUser add index index2(email(6));

问：使用前缀索引需要注意什么？

答：增加扫描次数；

只要使用了前缀索引，查询覆盖索引的列时，不论定义的长度是否真实地将所有字符都截断，mysql都会进行回表操作。

索引长度和索引搜索效率也有关系（同大小数据页能否容纳更多的索引），所以尽量选取索引长度短，并且区分度高的数据。

实战题

问：如果要存储一个市的身份证信息，前6位相同，此时怎么设计身份证索引最好。

答：

1、身份证倒序存储，再结合前缀索引使用。

2、新增一列作为hash校验值存储，由于可能存在冲突，所以查询时需要额外判断是否精确匹配自己想要的值；

两者缺点：无法支持范围查询

问：上面两个方案有什么区别？

答：

空间上，倒序仅在主键树上额外占用空间，hash方式则额外在普通索引树上占用空间，但hash的字节长可以比倒序短，最终两者没有太大差别；

CPU上，倒序在读写时需要额外调用一次倒序的函数，hash则是额外调用一次crc32函数，倒序函数开销更多一点点；

查询效率上：hash的区分度比较倒序更高，因此其综合扫描次数要更短。