《丁奇-MySQL45讲-11》之归纳总结

11 | 怎么给字符串字段加索引？

• 前缀索引：使用字符串的一部分作为索引。使用前缀索引后，回表的次数可能会增加，因为要去主索引上获取完整的字符串来判断是否满足条件，注意，即使定义的前缀索引的长度包含了字符串的所有信息，InnoDB仍然会去主索引上在差一下，因为系统并不确定前缀索引的定义是否截断了完整信息，而如果定义好前缀索引的长度就可以做到既节省空间，又不用额外增加回表的次数。使用前缀索引的话就没办法使用覆盖索引的优势了，前缀索引是无论如何都会回表的，而覆盖索引在只需要获取覆盖索引的相关信息下是不需要回表的。

• 对于长字符串，可以使用如下方式建立索引：

1. 前缀索引。

2. 字符串倒序存储 + 前缀索引（身份证号），涉及到reverse函数。

3. 添加hash字段，并为其建立索引，涉及到crc32函数。

• 比较上面提到的字符串倒序存储和添加hash字段的异同点：

1. 倒序存储自然是按照倒序字符串的方式进行排序的，所以它无法利用索引查出指定的区间的内容；哈希索引本身就是无序的，想要获取指定范围内的数据只能通过遍历的方式。

2. 针对函数的使用，reverse函数消耗的CPU资源会更小。

3. 从查询效率上看，crc32函数出现冲突的概率很小，也就是说它的查询效率基本保持在接近1，而倒序使用的仍然是前缀索引的方式，会扫描更多的行数，相当于前者是精准匹配，而后者是模糊匹配。