MySQL5.6优化---索引下推

什么是索引下推？

Index Condition Pushdown（ICP）是针对MySQL使用索引从表中检索行的情况的优化。如果不使用ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给MySQL服务器，后者将评估WHERE行的条件。启用ICP后，如果WHERE可以仅使用索引中的列来评估部分 条件，则MySQL服务器会将这部分条件压入WHERE条件下降到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。

索引下推的条件

• ICP用于 range， ref， eq_ref，和 ref_or_null访问方法时，有一个需要访问的全部表行。
• ICP可用于InnoDB 和MyISAM表。（例外：MySQL 5.6中的分区表不支持ICP； MySQL 5.7中已解决此问题。）
• 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取的次数，从而减少I / O操作。对于 InnoDB聚集索引，完整的记录已被读入InnoDB 缓冲区。在这种情况下使用ICP不会减少I / O。
• 引用子查询的条件不能下推。
• 涉及存储功能的条件不能下推。存储引擎无法调用存储的功能。
• 触发条件不能下推。

索引下推演示

数据准备，创建一张表，建立一个联合索引 idx_age_name (age,name)

CREATE TABLE `t_user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=38958 DEFAULT CHARSET=utf8

EXPLAIN  SELECT * FROM `t_user` WHERE `age` = 12 and `name` like 'w%' ;

在没有索引下推优化时，查询的流程如下。

第一步：先通过二级索引查询age=12的数据，第一条id为1的满足，然后再用id=1去聚集索引中查询对应行数据，判断name是否满足，name=wang，满足条件。
第二步：扫描下一行，age=12满足条件，再继续用id=2去聚集索引中查询对应行数据，name=jing，不满足条件。
。
。
。
依次类推，整个过程去聚集索引查询了2次，也就是产生了2次IO。

二级索引中，name不会参与过滤，就当索引中没有name字段

使用了索引下推优化后，查询流程如下。

第一步：获取一行索引数据。
第二步：检查age和name是否符合where条件，如果不满足继续检索下一行数据。
第三步：如果满足条件，用id去聚集索引查询整行数据。
第四步：看看当前行数据是否匹配where中的其他条件。

直接在二级索引中完成了过滤

可以看出，通过索引下推优化后，在二级索引中就完成了name和age的过滤，这样就只需去聚集索引中查询一次其他完整数据即可，IO次数为1。

没有索引下推时，MySQL只能拿到从二级索引中过滤出来的部分数据，比如上面例子中的id为1,2的两条数据，然后再通过回表的方式，用id为1,2的两个数据去聚集索引中查询整行数据，再过滤其他的where条件，使用索引下推后，有些数据就可以提前过滤出来，这样就减少了回表的次数。

默认情况下，索引条件下推处于启用状态。可以使用optimizer_switch系统变量通过设置 index_condition_pushdown标志来控制它 ：

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';