Mysql为什么会选错索引

mysql经常在执行某些sql时会选错索引,导致和预期的执行时间差距甚大,这其实不是mysql的bug,mysql选择索引是一个非常复杂的算法,所以选错也时有发生。

选择索引是优化器的工作,优化器会根据多种情况做为衡量因素来选择一个mysql认为最优的索引,通常一个主要因素就是扫描行数。在执行sql之前,mysql会预估一个扫描行数,(既然是预估那么肯定和实际的扫描行数有差距),而如何预估是取决于一个索引基数,索引值的重复率越低,基数越大,那基数是如何确定的?mysql是采取采样统计,即在索引段中取出N个页,统计重复率然后得到每页不重复值的平均值,再乘上这个索引一共有多少页(其实这里我有个疑问没想明白,索引扫描是很快的,而且不涉及到回表,何不直接扫描索引统计,这样更加准确。猜测mysql对基数的使用弱化了准确性的严格程度,第二个采样统计可能效率更高,更少的磁盘io,特别是索引比较大的情况下)。

show index from table

可以查看每个索引的基数值(cardinality列),这个值是可以重新统计的

analyze table t;

可以手动重新采样统计,或者索引表更比较频繁(1/M),会自动重新统计

刚刚说了扫描行数是通过基数预估出来的,第一个是预估,第二个是基数也不是非常准确,所以这个扫描行数可能会很不准确,从而导致选错索引,除了这点,还有一些其他原因可能会导致选错索引:

  1. 考虑回表的代价:比如possible_keys有主键id和a,对id扫描10万次的代价可能比索引a扫描50000次的代价小,因为在没有索引覆盖的情况下,50000次回表的随机io代价不可忽略。
  2. 排序的影响,mysql会创建临时表对无序的数据进行排序,这个开销也是不可忽略的,所以优化器在索引选择时会考虑到这点,比如possible_keys有索引a和b,sql最后需要按照b排序,那么b扫描100000次的代价可能比a扫描50000次的代价小,因为索引的有序性,所以选择索引a会重新对数据进行排序。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注