索引下推,全程,索引条件下推(Index Condition Pushdown)简称ICP。
SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'
创建了索引:idx_name_age_position,联合索引。
正常情况(MYSQL5.6之前),这条sql根据最左前缀匹配原则,name会走索引,age和position不一定会走索引。name是like模糊匹配,age和postion在b+tree中不能保证顺序。MYSQL5.6之后,like利用了索引下推优化后。
在MYSQL5.6之前,这个sql只能在联合索引中匹配到name like 'LiLei%' 开头的索引,然后拿着叶子结点的主键一个个回表,到主键索引去查找相应的记录,再去对比age和position的两个字段的值是否符合。整个过程回表多次。
在MYSQL5.6之后引入了索引下推优化,可以在索引遍历过程中,对索引中包含的所有字段做判断,过滤掉不符合条件的记录之后在回表,整个过程是只回表一次。有效减少回表次数。
使用了索引下推优化后,上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后,同时还会在索引里过 滤age和position这两个字段,拿着过滤完剩下的索引对应的主键id再回表查整行数据。
对于innodb引擎,索引下推只能用在二级索引,也就是非主键索引。主键索引的叶子节点上保留的是完整的数据,无需回表,索引下推不会起到减少查询全行数据的效果。
explain 中extra列为 using index condition时,就是表示使用了索引下推。
关闭索引下推命令
set optimizer_switch='index_condition_pushdown=off';
为什么范围查找没有使用索引下推?
因为mysql优化的是认为范围查找的过滤结果集依然很大,like的过滤结果集绝大多数看起来比较小,所以mysql给like使用索引下推,范围查找没有,但是不一定,like有时候也不会走索引下推。