大家好,我是北漂程序员,欢迎关注公众号:北漂程序员
问题:请问你了解索引下推吗
分析:该问题主要考察对mysql优化方面的理解
回答要点:
主要从以下几点去考虑,
1、mysql中索引的概念?
2、索引下推的理解及意义?
在面试过程中问到mysql,必然会问到索引,问到索引肯定会问索引失效有哪些情况,问到索引失效,必然会问到索引下推。如果可以抗住这样的连环追问,必然会给面试官留下深刻的影响。
索引下推是在非主键索引,或者说二级索引/联合索引的情形下,索引的匹配规则遵循最左原则,最左原则会因为like或范围判断而后面的索引失效,这时在mysql5.6后引入了索引下推的概念,来优化查询效率。
例,select id,name,age from student where name like '张%' and age=32;
在普通的无索引的查询情况下,mysql会进行全表扫描,逐行判断是否符合查询条件;
现在建立(name,age)的联合索引
在建立了索引后,mysql会先扫描索引树,根据索引树找到符合条件的记录主键(innodb引擎下),然后回表逐行判断age条件进而查询整行记录;由于使用了like且包含%,根据最左匹配的原则,只会使用联合索引中的name,而age会失效。但在5.6后由于使用了索引下推,那么仍然会通过索引树判断age的值,返回符合条件的主键进而返回整行记录;
索引下推的意义
在没有索引下推的情况下,假如通过索引树找到符合条件的记录为1000条,那么会通过回表的方式找到1000行的数据逐行判断age是否符合条件;
在使用索引下推的情况下,假如通过索引树判断符合name条件的记录为1000条,通过索引下推判断符合age条件的为100条,那么通过回表的方式找到100行记录即可;
通过上面的分析可以看到使用了索引下推,可以很好的避免回表查询的行数,也就是减少了随机IO的次数,通过索引判断肯定比通过逐行判断要快的多;
索引下推的使用场景
在联合索引下,like、范围(> < between and)的情况会遵循最左匹配的原则,遇到这样的字段后面的条件不会用到索引,而有了索引下推便可以继续利用索引进行判断。
例,student表的(name,ange)联合索引,
select name,age from student where name like '王%' and age=21;
select name,age from student where age >20 and name like '王%';
select name ,age from student where age between 16 and 21 and name ='tom';
上面的sql在mysql5.6及之后均会使用到索引下推,而且是覆盖索引。