mysql索引条件下推原理

mysql5.6增加了一个针对索引性能的优化方案，可以利用索引把本来由服务层做的过滤操作下推到引擎层完成，较少了数据的传输，提升索引效率。今天讨论一下这个优化方案——索引条件下推（Index Condition Pushdown, ICP）

假设存在一张表结构如下：

create table people
(
	zipcode varchar(11) null,
	lastname varchar(20) null,
	address varchar(20) null
);

create index people_zipcode_lastname_address_index
	on people (zipcode, lastname, address);

以下面这条SQL为例，当没有索引条件下推优化的时候，由于联合索引只能命中zipcode(最左原则+模糊匹配字符串不支持索引过滤)，导致引擎层只能将zipcode='95054'的数据全部取出来交给服务器层再做两个LIKE过滤。而有了联合索引过滤，这个条件过滤操作被下推到引擎层直接执行，优化性能。

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

使用条件

要使用ICP特性，必须满足一下几个条件：

• 过滤条件必须是通过LIKE '%str%'，BETWEEN，OR NULL等方法且需要获取所有字段时方可触发。
• Innodb只用于二级索引，因为聚簇索引会直接将完整的数据读入InnDB Buffer，这时候无法通过减少从引擎从读入服务层的IO。
• 子查询无法优化
• 存储过程也无法优化，因为在引擎层无法处理存储过程。