1.MySql优化器是如何选择索引的?
(1)通过采样数据来估算需要扫描的行数,如果扫描的行数多那可能io次数会更多,对cpu的消耗也更大;
(2)是否会使用临时表,如果使用临时表也会影响查询速度;
(3)是否需要排序,如果需要排序则也会影响查询速度。
综合(1)(2)(3)以及其它的一些因素,MySql优化器会选出它自己认为最合适的索引。
2.MySql优化器在什么情况下会选错索引?
(1)上面提到MySql优化器是通过采样来预估要扫描的行数的,所谓采样就是选择一些数据页来进行统计预估,这个会有一定的误差;
(2)由于MVCC会有多个版本的数据页,比如删除一些数据,但是这些数据由于还在其它的事务中可能会被看到,索引不是真正的删除,这种情况也会导致统计不准确,从而影响优化器的判断;
(3)优化器本身的一些判断机制不够完善,例如有如下sql查询语句:explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
比如有a和b两个索引,a索引需要扫描的行数为1000,b索引需要扫描的行数为50000,但是查询语句会根据b来进行排序并且limit 1,这个时候优化器可能会选择b,优化器认为通过索引b可以避免排序并且只需要找到1条记录然后回表查询数据如果a也满足条件即可终止扫描,优化器认为值得冒险,但是实际执行速度可能没有选择索引a快;
3.MySql优化器选错了索引,我们应该如何处理呢?
(1)使用force index语句,强制指定索引,不过这个的缺点挺明显的,首先限制了优化器的一些其它可能的选择使得优化器不够灵活,然后代码扩展性不好,如果索引的名字修改还得修改代码,实际工作中用得并不多;
(2)考虑建立新的更加合适的索引;
(3)考虑是否可以删除错误的索引。