【MySQL】索引下推（5.6版本+）

索引下推

有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

对于user_table表，我们现在有（username,age）联合索引
如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息，语句如下：

select * from user_table where username like '张%' and age > 10

语句有两种执行可能：

1. 根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据。
2. 根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。

明显的，第二种方式需要回表查询的全行数据比较少，这就是mysql的索引下推。mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制
SET optimizer_switch = ‘index_condition_pushdown=off’;

假设表t有联合索引（a,b）,下面语句可以使用索引下推提高效率：

select * from t where a > 2 and b > 10;

小结

1. innodb引擎的表，索引下推只能用于二级索引（除了聚簇索引之外的索引都是二级索引(辅助索引)，每一个二级的记录中除了索引列的值之外，还包含主健值。通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键/聚簇索引找到相应的数据块。）。innodb的主键索引(聚簇索引)树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
2. 索引下推一般可用于所求查询字段（select列）不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。