浅析index condition pushdown

原文链接：http://blog.csdn.net/zbszhangbosen/article/details/7463382

另一篇文章讲叙了 MRR和BKA 

什么是indexcondition pushdown(ICP)?

在数据库中pushdown表示某些操作“下推”，也就是某些操作提前执行，在生成执行计划时某些操作下推可以大大提升效率（为什么叫下推，因为优化器在生成的计划叫做执行计划树，操作从叶子节点开始往根上执行，下推就意味着提前执行）。举个最简单的例子，某些投影操作下推可以大大减小在执行过程中的数据量，而这里说的index condition pushdown也是这一种类似的下推操作。也就是将利用索引判断的这一步提前执行，让我们来看个例子：

表orders

CREATE TABLE orders (

  order_id INT NOT NULL PRIMARY KEY,

  customer_id INT,

  value INT,

  order_date DATE,

  KEY idx_custid_value (customer_id, value)

)

query ： select * fromorders where customer_id<4 and value=290;

在没有ICP之前它是这样执行的

1. 从索引idx_custid_value索引里面取出下一条customer_id<4的记录，然后利用主键字段读取整个行

2. 然后对这个完整的行利用value=290这个进行判断看是否符合条件。

3. 从1开始重复这个过程

 

有了ICP之后则是这样执行的

1. 从索引idx_custid_value索引里面取出下一条customer_id<4的记录，然后利用这个索引的其他字段条件进行判断，如果条件成立，执行第2步，否则第3步

2. 在上一步中筛选出来符合条件的才会利用order_id去主键索引里面找到这个完整行。

3. 从1开始重复这个过程

 

也就是说将判断条件提前（或者称作下推），这对在customer_id上有很多符合条件的行，而value上却很具有选择性的这种查询是很大的改进。

 

下面看看上面举的例子的具体情况：

mysql5.5(没有实现 index conditionpushdown)

mysql> explain select * from orderswhere customer_id<4 and value=290;

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+

| id | select_type | table  | type | possible_keys | key          |key_len | ref  | rows | Extra       |

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+

|  1| SIMPLE      | orders | range |idx_custid_value  | idx_custid_value |5       | NULL |    1 | Using where |

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+

 

mysql5.6(实现了 index conditionpushdown)

mysql> explain select * from orderswhere customer_id<4 and value=290;

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+

| id | select_type | table  | type | possible_keys | key          |key_len | ref  | rows | Extra                 |

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+

|  1| SIMPLE      | orders | range | idx_custid_value  | idx_custid_value | 5       | NULL |    1 | Using indexcondition |

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+

 

我觉得ICP的核心在于：它并不是影响优化器选择哪个索引，而是在于怎么利用这个索引。在上面的两个执行计划可以看出无论是否ICP两个都是利用idx_custid_value索引的customer_id列(key_len=5,而customer_id为int，占4个字节，还有1个字节用来判断是否null)两者使用的索引完全相同，只是处理方式不同。

 

因为上面的customer_id上只有一个索引，很有可能之前是因为在customer_id上只有这一个选择，所以为customer_id再创建一个单独的索引，这恰好是上面那个组合索引利用到的部分

mysql> create index idx_custid onorders(customer_id);

Query OK, 0 rows affected (0.09 sec)

然后在mysql 5.5 和mysql 5.6里面再次分别执行

mysql5.5

mysql> explain select * from orderswhere customer_id<4 and value=290;

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+-------------+

| id | select_type | table  | type | possible_keys           |key          | key_len | ref  | rows | Extra       |

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+-------------+

|  1| SIMPLE      | orders | range |idx_custid_value,idx_custid | idx_custid_value | 5       | NULL |    1 | Using where |

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+-------------+

1 row in set (0.00 sec)

 

mysql5.6

mysql> explain select * from orderswhere customer_id<4 and value=290;

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+-----------------------+

| id | select_type | table  | type | possible_keys           |key          | key_len | ref  | rows | Extra                 |

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+-----------------------+

|  1| SIMPLE      | orders | range |idx_custid_value,idx_custid | idx_custid_value | 5       | NULL |    1 |Using indexcondition |

+----+-------------+--------+-------+-------------------------+--------------+---------+------+------+-----------------------+

1 row in set (0.00 sec)

 

从上面可以看出，即使在customer_id上有一个单独的索引，但是mysql 5.5还是利用了idx_custid_value这个组合索引。但在mysql 5.5明显利用不到ICP的特性(也就是说利用不到idx_custid_value索引的value列部分)，为什么不选择idx_custid这个索引？这或许又是mysql优化器不完善的一个体现。

 

总结：

需要index condition pushdown 的query通常索引的字段出现where子句里面都是范围查询。比如：

select * from tb where tb.key_part1 < x and tb.key_part2 = y       

select * from tb where tb.key_part1 = x andtb.key_part2 like '%yyyy%'

select * from tb where tb.key_part1 > x and tb.key_part1 < y and tb.key_part1 > xx and tb.key_part2 < yy

但是需要注意的是：

1. 如果索引的第一个字段的查询就是没有边界的比如 key_part1 like '%xxx%'，那么不要说ICP，就连索引都会没法利用。

2. 如果select的字段全部在索引里面，那么就是直接的index scan了，没有必要什么ICP

3. 没有做性能对比测试，因为现在的mysql 5.6都还只是开发版，等有GA版本再测不迟

参考资料：

1.http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

2. http://www.mysqlperformanceblog.com/2012/03/12/index-condition-pushdown-in-mysql-5-6-and-mariadb-5-5-and-its-performance-impact/

3.http://jorgenloland.blogspot.com/2011/08/mysql-range-access-method-explained.html