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

前言

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

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

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

有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.';

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

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

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

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

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

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

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

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

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.';时，不用回表直接就能查到id和email字段。

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

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

其他字符串索引创建方式

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

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

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

crc32select 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。

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对狼蚁SEO的支持。