前缀索引两宗罪 超长字符串字段

前缀索引并不是一个难了解的物品，但是这外面触及到的一些细节，我置信很多同窗都没有去深化了解过。

老规矩，前缀索引关系面试题的背诵版在文末。点击阅读原文可以中转我收录整顿的各大厂面试真题

InnoDB 表中每一列索引的最大长度不能超越 767字节，所以，关于某些比拟长的字段，假设确实有树立索引的必要，经常使用前缀索引不只能够防止索引长度超越限度，而且相关于个别索引来说，占用的空间和查问老本更小。

至于为什么说前缀索引占用的空间和查问老本更小，咱们来间接上个例子：

假定表中存在一个邮箱 email 字段，咱们在这个字段上方区分创立个别索引和前缀索引：

1)个别索引，蕴含了每行 email 记载的的整个字符串：alter table user add index index1(email);

2)前缀索引，取每行 email 记载的前 6 个字节：alter table user add index index2(email(6));

你可以看到，由于 email(6) 这个索引结构中每个 email 字段都只取前 6 个字节zhangs，所以占用的空间比个别索引更小，这就是经常使用前缀索引的长处。

很好了解，对吧。

前缀索引一宗罪

但是，前缀索引这个占用空间更小的长处或者会带来额外的记载扫描次数。

举个例子，口头如下 sql 语句：

1)关于个别索引 email 来说，口头顺序如下：

这个环节中，只有要回表一次性

2)关于前缀索引 email(6) 来说，口头顺序如下：

可以看到，这个环节中，须要回表四次

这就是前缀索引的第一宗罪：经常使用前缀索引或者会参与记载扫描次数与回表次数，影响性能

不过呢，咱们做一些纤细的扭转，就能让这个前缀索引回表次数大大缩小。

把 index2-email(6) 这个前缀索引改成 index3-email(7)：

再来看上方这个例子，口头顺序如下：

可以看到，相关于个别索引，email(7) 这个前缀索引雷同只有要回表一次性，并且占用更少的索引空间。

前缀索引二宗罪

看上方这条 SQL 语句：

假设经常使用 index1 索引(即 email 整个字符串的索引结构)的话，可以应用上笼罩索引，从 index1索引树上查到结果后就可以前往了，不须要启动回表。

而假设经常使用 index2(即 email(6) 前缀索引结构)的话，就不得不再次依据主键值去回表判别 email 字段的值能否真的是'zhangs2001'。也就是说，经常使用前缀索引就用不上笼罩索引对查问性能的提升了。

那有同窗就要问了，假设是 email(10) 呢，这个前缀索引不就齐全蕴含了 zhangs2001 的一切消息了嘛，还须要回表吗?

答案是并不能阻止 InnoDB 的回表，由于 InnoDB 并不能确定前缀索引的定义能否截断了完整消息。谁知道你会不会又参与一个'zhangs20012' 的记载呢，对吧。

如何定义前缀索引的长度

索引选取的越长，占用的磁盘空间就越大，相反的数据页能放下的索引值就越少，搜查的效率也就会越低。

在上方的例子中咱们提到，只有要把前缀索引从 email(6) 改成email(7)，就可以大大缩小记载扫描和回表的次数，所以，在定义前缀索引的时刻，咱们须要在占用空间和搜查效率之间做一个掂量 trade-off。

理想上，咱们在树立前缀索引时关注的是区分度，区分度越高，象征着重复的键值越少，所以区分度越高越好。

关于索引来说，什么是区分度呢，很便捷，就是这个索引上有多少个不同的值。树立进去的索引上领有越多不同的值，那么这个索引的区分度就越高。

因此，咱们可以经过统计索引上有多少个不同的值来判别要经常使用多长的前缀。可以经常使用上方这个语句，计算出 email 列上有多少个不同的值，记作email_length：

而后，依次选取不同长度的前缀来看区分度，比如咱们要看前缀索引的长度是 6~10 时刻的区分度，可以用这个语句：

当然了，既然咱们经常使用了前缀索引，那么就无法防止的会损失区分度，就像咱们前面所说的，谁也不知道会不会又新增出一条记载齐全踩中前缀但是又不满足判别条件。所以咱们须要预先设定一个可以接受的区分度损失比例，比如5%。而后找出不小于 email_length * (1 - 5%) 的值，假定这里 email_length_8、email_length_9都满足，咱们就可以选用前缀长度为 8。

前缀索引的区分度不够高怎样办

我过后实习的时刻就遇到过这个疑问，字段(假定这个字段名是 a)超级超级长，远大于 InnoDB 的限度 767字节，个别索引必需是无法能了，前缀索引就算是长度定义成 767 都还是存在区分度不高的状况，但是又存在依据这个字段启动查问的挺频繁的一个需求。

一个很经常出现的处置手腕就是 Hash。

对这个超长字段 a 启动 hash(假定命名为 a_hash) 存入数据库，而后对这个 hash 值树立索引，由于 hash值雷同或者存在抵触，也就是说两个不同的 a 经过 Hash 函数失掉的结果或者是相反的，所以咱们在查问语句的 where 局部还须要启动一次性准确判别

不过经常使用 Hash 这种方式有个妇孺皆知的缺陷，那就是不支持范畴查问了，只能等值查问。

最后放上这道题的背诵版：

面试官：前缀索引了解吗，为什么要建前缀索引

小牛肉：前缀索引就是选取字段的前几个字节树立索引。首先，InnoDB 限度了每列索引的最大长度不能超越 767字节，所以，关于某些比拟长的字段，假设确实有树立索引的必要，经常使用前缀索引不只能够防止索引长度超越限度，而且相关于个别索引来说，占用的空间和查问老本更小。

不过前缀索引或者会造成两个疑问：

第一个，经常使用前缀索引或者会参与记载扫描次数与回表次数，影响性能。针对这一点呢，其实前缀索引长度的选取还是很关键的，或者前缀定义的长一点，就能够大幅缩小记载扫描次数和回表次数，所以，在树立前缀索引的时刻，咱们须要在占用空间和搜查效率之间做一个掂量

第二个，经常使用前缀索引其实就没法用笼罩索引对查问性能的提升了，由于 InnoDB并不能确定前缀索引的定义能否截断了完整消息，就算是齐全踩中了前缀索引，InnoDB 还得回表确认一次性究竟是不是满足条件了。