like查询后置% 索引竟然也失效

背景

昨天早上接到一个任务,查询某类商品,数据库用的是mysql,版本是5.6.37,es_product 表数据不多,两万多条。执行语句我简化了一下,类似下面的这条sql:

SELECT * FROM es_product WHERE goods_code LIKE 'meiju%';

goods_code是建了索引的,它的值是由类型名称拼音+’_’+编码组成(这里是导致后面问题的关键),索引类型Normal。
语句执行的很慢,我们看下它的执行计划是什么:

EXPLAIN
SELECT * FROM es_product WHERE goods_code LIKE 'meiju%'

在这里插入图片描述
正常情况下模糊查询%后置索引是有效的,%前置的话,会导致索引失效。

可能导致的原因

因为它的值是由类型名称拼音+’_’+编码组成,所以想到的就是字段值的区分度以及重复率,区分度太小或重复率过高会使索引失效,进而走全表扫描。

例如性别字段,它一般就男,女,这就不建议建立索引了,区分度大小了。

es_product 总的数据量:24426
meiju字符串长度为5,我们就来看看长度为5的字符串的重复率:
执行下面的sql:

SELECT (count(*) / 24426 ) percent,count(*) cnt,LEFT(goods_code,5) prefix
FROM es_product GROUP BY prefix ORDER BY percent DESC LIMIT 0,10

在这里插入图片描述
可以看到,前缀为meiju的重复率竟然占到了0.268,区分度大小了,mysql选择了全表扫描。
我们来试试百分比为0.0135前缀为LEBAZ的执行计划
在这里插入图片描述
type=range,索引范围扫描。由此可见当重复率高到某个百分比值时,mysql会走全表扫描
后面我继续增大LEFT(str,length)函数中截取的length的大小,观察重复率。
在这里插入图片描述
在索引生效的情况,取到了本次实验重复率的最大值0.1022,前缀为liber-V,长度是7,
我们来看下它的执行计划:
在这里插入图片描述

解决

因为我这边要查的就是某类商品,如果用googs_code(类型名拼音+%)去查,重复率肯定会蛮高,然后就是导致索引失效,问了下同事,可以用别的表关联也可以查出某类商品,避开goods_code。

总结

通过这个例子,我们应该明白了,把类型拼音和编码放到一个字段里面也确实有问题,这样放也是为了查询某类商品是方便,但是这样也导致了用模糊查询大概率会导致索引失效,字段值的重复率过高会导致索引失效,我们没法保证查询前缀的值是什么,应当尽量去保证索引列高的区分度以及低的重复率。

  • 5
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值