共计 2185 个字符，预计需要花费 6 分钟才能阅读完成。
自动写代码机器人，免费开通
MySQL 中字符串索引的创建规则有哪些？很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
如何更好的创建字符串索引
我们知道，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= zhangsh223@xxx.com
当使用 index1 索引时其执行步骤为：
1、从 index1 索引树查找索引值为 zhangsh223@xxx.com 的主键值 ID1；
2、根据 ID1 回表查到该行数据确实为 zhangsh223@xxx.com，将结果加入结果集；
3、继续查找 index1 索引树下一个索引值是否满足 zhangsh223@xxx.com，不满足则结束查询。
当使用 index2 索引时其执行步骤为：
1、从 index2 索引树查找索引值为 zhangs 的主键值 ID1；
2、根据 ID1 回表查到该行数据确实为 zhangsh223@xxx.com，将结果加入结果集；
3、继续查找 index2 索引树下一个索引值是否满足 zhangs，满足则继续回表查询该行数据是否为 zhangsh223@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= zhangsh223@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。
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注丸趣 TV 行业资讯频道，感谢您对丸趣 TV 的支持。
向 AI 问一下细节
