select * from t where (a between 1 and 10000) and (b between 10000 and 50000) order by b limit 1;
这里系统选择索引b的原因上面有介绍,就是系统认为索引b虽然扫描行数多,但是可以减少排序带来的性能消耗,所以系统选了索引b; 那么我们可以将order by b改为order by b,a,这样一来,索引b和索引a都需要排序,那么排序就不在性能考虑范围之内了,剩下的考虑因素就是扫描行数,此时系统就会选择索引a了;
不过这种改法会修改原有的语义,比如上面的例子只是返回一个数据limit 1;所以将order by b改为order by b,a都是返回结果集中b最小的那一个;
但是如果没有limit限制,那么改了之后返回的结果集顺序就不一致了;
还有一个办法就是删除索引b,前提是确保其他地方没有用到索引b;
通用的解决办法:
上面两种是针对特定的场景而言,其实有一个通用的办法就是强制系统选择某个索引,命令为:
force index (a)
不过这种办法缺点也很明显,就是不够敏捷,比如发现问题、修改索引、测试上线整个过程会比较耗时;
总结
上面分析了可能选错索引的几种情况:扫描行数、临时表、排序、回表等等
也介绍了对应的几种解决办法:分析表analyze table t、修改排序规则order by b,a、强制选择索引 force index( k )等
不过系统选错索引的情况也不多见,基本上出现了就按照上面这几个思路去排查,问题应该就可以解决。
有时候感觉查询慢,就可以先通过explain可以查看系统使用的索引是不是预期的,然后再对症下药