MySQL怎么给字符串加一个高效索引

需求
在日常需求中，用户使用手机号或者邮箱登录某一个系统，是一个很常见的操作，那如何在类似手机号或者邮箱这样的字段上建立一个合理的索引呢？

前缀索引
前缀索引，就是以一个字段值的一部分作为索引。如果在MySQL中创建索引时不指定索引字段的长度，则会使用整个字符串来建立索引。



语句1：

alter table test add index idx（email）;

语句2：

alter table test add index idx（email（8））;

对于语句1，创建的索引中，会包含每条记录中的整个email字符串值。

对于语句2，创建的索引中，保存的是每条记录中email字段的前8个字节。

使用前缀索引的优势很明显，那就是索引占用的空间会更小，整个索引树会更紧凑，树的高度相对更低。

但是相应的，索引的区分度会变低，可能导致索引扫描行数增加。

在我们创建索引时，索引的区分度是一个很重要的指标。区分度越高，重复的值就越少，扫描的效率就越高。

合理规划前缀索引的使用长度可以不仅节省空间，同时也避免需要额外扫描行数。具体使用多少的长度，建议根据我们的实际业务场景来判断、测试。

倒序+前缀索引
倒序+前缀索引有一个经典的使用场景，就是对身份证号做索引。

假设我们现在要维护一个市县所有人的身份信息，其中按身份证号查询是一个高频场景。

身份证号码一共15位或者18位，一般来说，同一个市县的人身份证号前6位一般是相同的，如果直接对身份证号做全索引，那么会比较浪费空间，导致性能下降。如果使用直接前缀索引，前6位的区分度非常低（甚至可以说没有），因为大多数值的前6位都相同。

这时，使用倒序+前缀索引的好处就体现出来了。

我们先将身份证倒序存储，或者冗余一个倒序的身份证号字段，然后取前6位做前缀索引。

身份证后6位的区分度已经基本上够我们使用了，如果你还觉得查询速度不够，那取前8位也是没问题的。

小二CMS介绍

我们立足合肥，业务覆盖安徽、全国及全球市场。我们凭借一支经验丰富、富有创意、协作无间的专业技术团队，专注于将前沿技术通过高效简捷的途径呈现给客户，量身打造优质解决方案。我们致力于通过持续努力，成为客户在信息化领域值得托付、共创价值的长期战略合作伙伴，协助客户在新经济时代敏锐捕捉商机，拓展发展空间，构筑强大竞争力。小二CMS专注企业数字化转型的智能建站与营销系统，提供从官网搭建、品牌推广到运营增长的一站式服务，助力中小企业低门槛拥有专业级互联网阵地。

网站建设 移动相关开发 营销推广与优化 域名主机邮箱 产品与技术 企业服务

了解全部业务

扫码咨询 · 关注微信