MySQL前缀索引的正确使用

前缀索引的使用场景主要集中在一长段字符串的完全匹配上,但是前缀索引的添加创建以及长短的选择也是有很多学问的。长度选择过长会丢失前缀索引的优势,太短会增加回表的次数。

特点

  • 优势
  1. 节约索引空间
  2. 增加索引查询效率
  • 劣势
  1. 覆盖索引失效
  2. 增加回表次数

前缀索引添加

命令语句

1
alter table SUser add index index_name(email(6));

eg

假设优化一个线上的业务场景,我们有张2千万用户的user表,需要通过email定位到这条数据。那么这个场景可以知道是email 结尾的会是 xxxx@qq.com 后面几位 @qq.com 基本是固定状态。我们去掉空白和重复的数据得到

1
SELECT count(DISTINCT(email)) FROM xxx_userinfo

user表去重数据

这是一个比较经典的前缀索引使用的场景,我们现在需要确定的是前缀索引创建时我们的指定长度是多少,这边有个通用的方法。

我们知道前缀索引太长,会增加索引体积损耗查询性能。索引太短,区分度会降低增加回表次数。那在前缀索引的长度选择上我们该怎么做呢?

我们可以先预估一个可以接受的一个区分度损耗范围,如10%。那么我们用总数除余下的百分比得到具体数字

1
82178 x 0.9 = 73960

我们通过不同的前缀长度查询去重后的数据条数,条数越多区分度越大

1
2
3
4
5
6
7
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 xxx_userinfo;

区间

由上图可知我们选择前缀索引长度6就可以满足10%的损耗。

注意

前缀索引使用后就无法利用到覆盖索引了,如果只是单独查询ID,email 可以刚好满足,但有其他检索字段的时候就需要重新回表。这块损耗需要结合业务场景综合考虑得失。

其他方法

如果遇见一些特定场景比如 前几位都是区分度很小的字符后几位是区分度较大的字符,或者一串较长的字符串那么上述的方法可能就有点行不通了。这里有2个取巧的方法解类似的场景,只做思路扩展。

  1. 字符串反转存入,读出
  2. 新建一个字段已要匹配字段为模板进行MD5或者hash算法
  • 版权声明: 本博客所有文章除特别声明外,著作权归作者所有。转载请注明出处!

请我喝杯咖啡吧~