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。
- 子查询无法优化
- 存储过程也无法优化,因为在引擎层无法处理存储过程。