MySql – 给字符串建立索引

平常开发中，字符串字段使用的场景应该是非常多了，用户名、邮箱等等，针对字符串的长短，使用场景，如何合理的建立索引是非常重要的，下面来简单探讨下，以邮箱登陆这个场景为例：

假如用户的邮箱长度平均在20个字节，直接给email字段建立索引，索引占据的空间会变大，每页所能保存的索引值就减少，那么在查询时读取进内存的页变多，增加了磁盘io，影响效率。

mysql是支持前缀索引的，也就是可以给字段的前N个字节创建索引，比如取前4个字节为索引值：

alter table user add index email_index(email(4))

那么前缀索引和包含整个字符串的索引除了索引文件大小不一样，还有一些查询效率上的区别。

假如我们的邮箱有xxxx1@gmail.com，xxxx2@gmail.com，yyyy1@gmail.com三个值，那么在做查询

select * from user where email = "xxxx1@gmail.com"

1. 先在索引树中找到xxxx（left email 4）这个值，拿到主键id，再回表查询行数据，判断email是xxxx1@gmail.com，然后将结果加入到结果集中

2. 接着从刚刚找到的位置上找到下一条记录，结果还是xxxx，同样的拿到id回表查询，发现email值不符合，丢弃

3. 接着从刚刚的位置找下一条，结果是yyyy不符合，查询结束

整个过程回表两次

1. 先从索引树中找到xxxx1@gmail.com这个值，拿到id，回表取行数据，将该行加入到结果集

2. 从刚刚的位置取下一条判断索引值不符合，查询结束

整个过程只回表了一次

由此可见，选好前缀索引的长度非常重要，一个合适的值N，不但可以减少索引占据的空间，还能减少回表次数。通常选取N值是按照区分度来做，比如先查询该字段的非重复值的次数

select count(distinct(email)) from user

再依次选取不同的长度做测试：

select count(distinct(left(email, 4))) from user
select count(distinct(left(email, 5))) from user
select count(distinct(left(email, 6))) from user

选差距最小的长度。

前缀索引虽然减少了索引数据占据的空间，但是某些场景下会增加查询的回表次数，而且前缀索引无法用到索引覆盖这个优点，还是刚刚的例子：

如果查询语句改成

select id,email from user where email = “xxxx1@gmail.com”

那么对于前缀索引，必须要回表检查email的值是否匹配，也就是说前缀索引还是需要回表两次，但是第二种直接从索引中取值即可，不需要回表。同样的like查询即使前缀索引满足最左前缀原则，同样需要回表判断

如果对于某些字符串很长，但是前缀区分度不高，比如一共20个字节长度，主要区分是在最后几个字符，这时候有以下两种做法：

可以在存储的时候倒序存储，然后创建前缀索引，查询的时候

select * from user where `field` = reverse("your search key");

可以再创建一个字段，保存该字符串的hash值，然后给hash值创建索引，hash值一般int类型保存即可，查询时

select * from user where `field_crc` = crc32("your search key");

hash值可能会出现冲突，所以查询语句改一下：

select * from user where `field_crc` = crc32("your search key") and field = "your search key"

这两种方式都能解决字符串很长但是前缀区分度不高的情况，二者都不能利用索引进行范围查询和排序且二者在空间和时间上效率相当。

1. 前缀索引可以针对字符串特别长且区分度足够的情况下，减少索引文件占据的空间，但是可能会增加回表次数，影响查询效率，并且不能使用覆盖索引

2. 倒序存储+前缀索引 / hash 的方式可以解决字符串很长但是前缀区分度不够的问题。但是查询有限制，比如排序和范围查询，且有额外的性能和存储消耗，比如函数计算，多一个字段和增加主键索引空间

参考：《mysql实战45讲》

Leave a comment