另一篇文章讲叙了 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
3.http://jorgenloland.blogspot.com/2011/08/mysql-range-access-method-explained.html