MySQL字符串索引更合理的创建规则讨论

前言

针对使用MySQL的索引，我们之前介绍过索引的最左前缀规则，索引覆盖，唯一索引和普通索引的使用以及优化器选择索引等概念，今天我们讨论下如何更合理的给字符串创建索引。

如何更好的创建字符串索引

我们知道，MySQL中，数据和索引都是在一颗 B+树 上，我们建立索引的时候，这棵树所占用的空间越小，检索速度就会越快，而varchar格式的字符串有些会很长，那么在效率为上的今天，我们如何更加合理的建立字符串的索引呢？
假如说我们一张表中存在 email 字段，现在要给 email 字段创建索引，email 字段值的格式为：zhangsan@qq.com。

有2种建立索引的方式：

1、直接给 email 字段建立索引：alter table t add index index1(email);

索引树结构为：

2、建立 email 的前缀索引：alter table t add index index2(email(6));

索引数据结构为：

此时我们的查询语句为：select id,name,email from t where email='zhangsh123@xxx.com';

当使用index1索引时其执行步骤为：

1、从index1索引树查找索引值为zhangsh123@xxx.com的主键值ID1；

2、根据ID1回表查到该行数据确实为zhangsh123@xxx.com，将结果加入结果集；

3、继续查找index1索引树下一个索引值是否满足zhangsh123@xxx.com，不满足则结束查询。

当使用index2索引时其执行步骤为：

1、从index2索引树查找索引值为zhangs的主键值ID1；

2、根据ID1回表查到该行数据确实为zhangsh123@xxx.com，将结果加入结果集；

3、 继续查找index2索引树下一个索引值是否满足zhangs，满足则继续回表查询该行数据是否为zhangsh123@xxx.com，不是则跳过继续查找；

4、持续查找index2索引树，直到索引值不是zhangs为止。

从以上分析中我们可以看出，全字段索引相比前缀索引来说，减少了回表的次数，但是如果我们将前缀从6个增加到7个8个的话，前缀索引回表的次数就会减少，也就是说，只要定义好前缀的长度，我们就能既节省空间又保证效率。

那么问题来了，我们怎么衡量使用前缀索引的长度呢？

1、使用 select count(distinct email) as L from t; 查询字段不同值的个数；

2、依次选取不同的前缀长度查看不同值的个数：

select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from t;

然后根据实际可接受的损失比例，选取适合的最短的前缀长度。

前缀的长度问题我们解决了，但是一个问题是，如果使用前缀索引，那我们索引覆盖的特性就用不到了。
用全字段索引时，当我们查询select id,email from t where email='zhangsh123@xxx.com';时，不用回表直接就能查到id和email字段。

但是用前缀索引时，MySQL并不清楚前缀是否会整个覆盖email的值，无论是否全包含都会根据主键值回表查询判断。

所以说，使用前缀索引虽然能节省空间保证效率但是却不能用到覆盖索引的特性，是否使用就在于具体考虑了。

其他字符串索引创建方式

实际情况实际考虑，并不是所有的字符串都能使用前缀截取的方式创建索引，如身份证号或者ip这些字符串使用前缀索引就不合理了，身份证号一般同一个地区的人前几位都是一模一样的，使用前缀索引就不合理了，而ip值我们一般在实际中将其转化为数字去存储。

针对身份证号，我们可以使用倒叙存储，取前缀创建索引或者使用crc32()函数来获取一个hash校验码（int值）当做索引。

倒叙：select field_list from t where id_card = reverse('input_id_card_string');

crc32：select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这两种方式相对来说效率都差不多，都不支持范围查找，支持等值查找。

在倒叙方式中，需要使用reverse函数，但是回表次数可能比hash方式多。

在hash方式中，需要新建一个索引字段并调用crc32()函数。（注意：crc32()函数获取的结果不保证能唯一，可能存在重复的情况，但是这种情况概率较小），回表次数少，几乎1次就行。

最后

针对字符串索引，一般有以下几种创建方式：

1、字符串较短，直接全字段索引

2、字符串较长，且前缀区分度较好，创建前缀索引

3、字符串较长，前缀区分度不好，倒叙或hash方式创建索引（这种方式范围查询就不行了）

4、根据实际情况，遇到特殊字符串，特殊对待，如ip。

来源：https://segmentfault.com/a/1190000021086051