MySQL索引下推

ICP介绍

Index Condition Pushdown(ICP)是MySQL 5.6 中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

• 如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE中的条件是否保留行。
• 启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数，但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据比例。

ICP开启与关闭

默认情况下索引下推是开启的，可以通过设置系统变量 optimizer_switch 控制。

-- 关闭
SET optimizer_switch = 'index_condition_pushdown=off';
-- 打开
SET optimizer_switch = 'index_condition_pushdown=on';

当使用索引下推时，EXPLAIN语句输出结果中Extra列内容显示为 Using index condition。

ICP使用条件

1、如果表访问的类型为range、ref、eq_ref 和 ref_or_null 可以使用ICP
2、ICP可以用于InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表
3、对于InnoDB 表，ICP 仅用于二级索引，即查询中必须存在回表操作才有可能会使用到ICP。ICP的目标是减少全行读取次数，从而减少I/O操作
4、当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少I/O
5、相关子查询的条件不能使用ICP