在极客时间的MySQL45讲的第10讲中,为什么说下面的两个SQL语句会让优化器选择不同的索引?

t表有id a b三列
a 和 b列都有索引,且有10w行,每行的值都是相同的(1,1,1) (2,2,2) ....(100000,100000,100000)
CREATE TABLE t(
id int(11) NOT NULL,
a int(11) NOT NULL,
b int(11) NOT NULL,
PRIMARY KEY (·id·),
KEY 'a'('a'),
KEY 'b'('b')
)ENGINE=InnoDB;

select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1.

select from (select from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100) alias limit 1;

第一条使用的是索引B,原因是优化器认为使用索引b可以避免排序(b本身是索引,已经是有序的了,如果选择索引b的话,不需要再做排序,只需要遍历),所及即使扫描行数多,也判定为代价更小;
上面的说法没问题,但是第二条SQL使用limit 100让优化器意识到,使用b索引代价是很高的,根据数据特征诱导了一下优化器,请问为什么这样能诱导到优化器呢?

阅读 421
评论
    2 个回答
    nick
    • 257

    limit 在order by语句中,有一定优化作用,具体可以参照(文档:LIMIT Query Optimization 章节)。limit n
    其中n值的大小,也会导致优化器选择不同的执行策略,但优化器本身的选择也不一定是效率最高的方式,通常是选择开支较小的方式。当然你在实际环境下,觉得使用排序字段的索引,可以进一步提高效率,可以在查询中增加 use index(索引)进行声明

    评论 赞赏

      谢邀。
      这里说句扩展话题的:mysql的优化器大部分时候都是选择效率最高的sql执行方式,但并不绝对,特别是有些两个表的关联查询时,有时优化器效率低(选错了驱动表,也不能说是选错,只是按照优化器的规则是对的),这时可以用STRAIGHT_JOIN改表优化器对于联表查询的执行顺序。
      你说的为什么能诱导到优化器,建议从不同sql来用explain进行分析,收获更大。

      评论 赞赏
        撰写回答

        登录后参与交流、获取后续更新提醒