同事遇到一个奇葩的问题,一个表里某个字段建了索引,但是有的值走索引,有的值不走索引。因为一般情况一个字段要么完全不走索引,要么走索引,怎么会有的值走索引,有的不走索引。
select 条件非常简单,因为涉及到敏感信息就不贴表结构了。例如select * from order where status = 2; status为状值。分别为-1删除,1正常,2待定。因为sql比较简单,所以排除前缀原则、like 、in 及where条件等号左边有函数不走索引的情况。百思不得其解。
然后为了进一步定位问题,看是不是索引建的多的原因,又去其它表里看了一下,不是这个原因。
那是什么原因呢?因为只有2不索引,所以我又试了一下其它值都走索引,我将2改为3试一下,结果奇迹发生了,2居然走索引了。再试一下status=3。也走索引,我怀疑是边界的问题。然后google一下关于索引是否有边界这样的情况影响索引。也没有结果。
那我想再把所有2改为3,现试一下,是不是跟这些数据有关,奇迹发生了!status=3也没走索引。进一步缩小范围。为什么status=3也不走索引呢?然后我想到了是不是跟索引的稀疏性有关?也就是说因为重复的数据所以优化器判断索引无效所以不走索引呢?
我又把status=3改回去,观察一下重复数据分布,status=2的条数为3条,status=-1有两条,status=1有两条,我再把其中一条status=4,发现都走索引了。为了进一步验证自己的结论,又试了一下将其中一条status=2改为1,结果status=1不走索引了。更进一步验证了自己的判断!
所以这次的问题就是因为当数据表中数据比较稀疏时,而当字段个数比较少时,优化器会认为这个索引效果不明显,所以不走索引。
进一步我和同事又将status的索引去掉,发现expalin 的rows列显示所有行,无论status为任意值。而如果status建索引的话重复的比较多的不走索引。所以结果是建索引性能提升不大。具体根据自己的情况来建索引。