MySQL 索引下推MySQL索引下推（Index Condition Pushdown ICP）是从 MySQL 5.

MySQL索引下推（Index Condition Pushdown ICP）是从 MySQL 5.6 开始引入的一种查询优化技术。

⒈ ICP 简介

ICP 是一种对通过索引查询数据的优化
如果不使用 ICP，存储引擎首先将通过索引定位到的行全部返回给 MySQL 服务端，服务端再通过 where 条件筛选符合条件的数据
开启 ICP 后，如果部分 where 条件可以通过索引中的列来筛选，则 MySQL 服务端会将这些 where 条件下推到存储引擎，存储引擎会通过索引对这些条件进行筛选，最后返回符合条件的行
ICP 可以降低 MySQL 服务端访问存储引擎的次数，也可以降低存储引擎访问数据表的次数

⒉ ICP 应用场景

当需要访问完整的行时，ICP 适用于 eq_ref、ref、ref_of_null、range 这四种访问方式

eq_ref：从表中读取一行（如果是联表查询，则是从各个表的笛卡尔积的结果中读取一行），是继 system 和 const 访问方式之后最好的访问方式。当索引的各个部分都会被使用，并且索引为主键或非空的唯一索引时会用到这种访问方式。当索引列与常量或表达式使用 = 操作符进行比较时会用到 eq_ref
ref：与索引值相匹配的所有的行都会被读出。如果查询条件中只使用了索引的最左前缀，或者用到的索引不是主键或唯一索引，此时会用到这种访问方式
ref_or_null：在 ref 的基础上，还对包含 NULL 的情况进行了查询。这种方式主要用于解析子查询
range：值在一定范围内的行会被读取。当索引通过操作符 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN() 与常量进行比较时会用到这种访问方式

ICP 适用于 InnoDB 和 MyISAM 类型的表，包括分区的 InnoDB 和 MyISAM 表
对于 InnoDB 类型的表，ICP 只适用于辅助索引。ICP的目的是为了减少 I/O，但 InnoDB 的主键为聚簇索引，此时所有的的数据都已被读入 InnoDB 缓冲，此时使用 ICP 不会减少 I/O

虽然 InnoDB 支持在虚拟生成的列上创建辅助索引，但 ICP 并不适用于这种情形
子查询中的条件无法被下推
与存储函数相关的条件无法被下推，因为存储引擎无法调用存储函数
触发条件无法被下推

⒊ ICP 的开启和关闭

ICP 默认为开启状态，如果想改变 ICP 的状态可以通过一下命令实现

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

⒋ ICP 的测试效果对比

⓵ 创建数据表并导入数据

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) COLLATE utf8mb4_general_ci NOT NULL,
  `last_name` varchar(16) COLLATE utf8mb4_general_ci NOT NULL,
  `gender` enum('M','F') COLLATE utf8mb4_general_ci NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

⓶ 建立联合索引

alter table employees add index full_name(first_name,last_name);

⓷ 开启 profile 并关闭查询缓存

set profiling = 1;
set global query_cache_type = 0;
set global query_cache_size = 0;

⓸ 默认开启 ICP 时，连续执行以下 SQL 语句 3 次，查看执行情况

SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';

⓹ 关闭 ICP 后，连续执行上述 SQL 语句 3 次，查看执行情况

通过以上对比可以看出，开启 ICP 的查询所消耗的时间明显比关闭 ICP 的查询要少。另外，分析 ICP 开启和关闭时的 SQL 语句，开启 ICP 时 Extra 为 Using index condition，关闭 ICP 时 Extra 为 Using where