MySQL45讲之前缀索引 - flowers

前言

本文介绍字符串的前缀索引优缺点，以及字符串区分度不高情况下，可以考虑如何建立索引。

前缀索引

对于像SELECT * FROM t WHERE email = "xxxx@163.com"的查询语句，如果不对email列建立索引，那么将会进行全表扫描。

对字符串建立索引，可以对整个字符串建立索引，也可以只对前几个字符建立索引，第二者就是所谓的前缀索引方式。

优点

节省存储空间。如果字符串长度比较长，需要对整个字符串建立索引，那么索引树占用的存储空间时很大的。而如果只是对前一部分字符建立索引，那么可以很好的节省存储空间。

缺点

可能会增加扫描行数，影响查询性能。因为整个字符串可能不相同，但是前缀可能是相同的，导致使用前缀索引时，需要更多的回表扫描行。

所以，建立前缀索引前需要找到区分度最高的前缀。可以通过SELECT COUNT(distinct email(n)) FROM t;计算前缀不同的行数，和表的总行数比较，得到区分度最高的前缀。

索引覆盖失效。比如SELECT id, email FROM t WHERE email = "xxx@163.com"，可以使用覆盖索引，但因为索引树只有字符串部分数据，必须回表拿数据，即索引覆盖失效。

字符串区分度不高时

对于只存在字符串的等值查询场景，且整个字符串区分度不高的情况，可以考虑如何建立索引？

存储逆序字符串并建立索引。适用于原始字符串前缀的区分度不高，但字符串末尾区分度高的场景，查询语句为SELECT * FROM t WHERE email = reverse(xxx@163.com)。

新建立一列，内容为原始字符串的crc32哈希值，并对哈希值这列建立索引。适用于整个字符串前后部分区分度都不高的场景，查询语句为SELECT * FROM t WHERE crc_col = crc32("xxx@163.com") and email = "xxx@163.com"。因为可能发生哈希冲突，所以需要再比较下字符串值。

注意，前缀索引目的在于节省存储空间，但相应地也增加了开发转换的复杂度和错误的风险。存储资源足够的情况下，建议优先考虑整个字符串索引方式。

参考

• [1] 讲怎么给字符串字段加索引