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%'BETWEENOR NULL等方法且需要获取所有字段时方可触发。
  • Innodb只用于二级索引,因为聚簇索引会直接将完整的数据读入InnDB Buffer,这时候无法通过减少从引擎从读入服务层的IO。
  • 子查询无法优化
  • 存储过程也无法优化,因为在引擎层无法处理存储过程。

发表评论

电子邮件地址不会被公开。

27 − 22 =