ITPub博客

首页 > 数据库 > MySQL > Mysql Index Condition Pushdown

Mysql Index Condition Pushdown

原创 MySQL 作者:wzgchen 时间:2015-08-05 16:27:06 0 删除 编辑

ICP(Index Condition Pushdown Optimization):官方从5.6开始引入(percona5.6,mariadb5.5),
它是针对mysql通过索引取数据场景的优化,ICP之前,存储引擎通过索引定位到数据行,把他们返回
给server层,然后server层通过where条件(不能使用索引列的)做过滤;启用ICP后,如果where条件
可以通过索引列过滤,server层就“push this part of where condition down to storage engine”
引擎通过索引条目判断是否需要读某些行。ICP降低了存储引起访问底层表的次数和server层访问引擎
层的次数。

好处:
ICP 开启时整个sql 执行时间是未开启的2/3,sending data 环节的时间消耗前者仅是后者的1/4

需要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

注意一下ICP的使用条件:
只能用于二级索引(secondary index)。
explain显示的执行计划中type值(join 类型)为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据,即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
ICP可以用于MyISAM和InnnoDB存储引擎,不支持分区表(5.7将会解决这个问题)。




表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开始重复这个过程



还要通过server层进行判断过滤

有了ICP之后则是这样执行的
1. 从索引idx_custid_value索引里面取出下一条customer_id<4的记录,然后利用这个索引的其他字段条件进行判断,如果条件成立,执行第2步,否则第3步
2. 在上一步中筛选出来符合条件的才会利用order_id去主键索引里面找到这个完整行。
3. 从1开始重复这个过程





在存储层进行判断过滤,通往server层的数据将大大减少。Sending data时间将大大降低。

mysql> create index idx_salaries_sal on salaries(salary);


mysql> explain select * from salaries where  salary<40000 and  emp_no<262449;
+----+-------------+----------+-------+---------------------------------+------------------+---------+------+-------+-----------------------+
| id | select_type | table    | type  | possible_keys                   | key              | key_len | ref  | rows  | Extra                 |
+----+-------------+----------+-------+---------------------------------+------------------+---------+------+-------+-----------------------+
|  1 | SIMPLE      | salaries | range | PRIMARY,emp_no,idx_salaries_sal | idx_salaries_sal | 4       | NULL | 23380 | Using index condition |
+----+-------------+----------+-------+---------------------------------+------------------+---------+------+-------+-----------------------+


mysql> explain select * from salaries ignore index(idx_salaries_sal)  where  salary<40000 and emp_no<262449;
+----+-------------+----------+-------+----------------+---------+---------+------+---------+-------------+
| id | select_type | table    | type  | possible_keys  | key     | key_len | ref  | rows    | Extra       |
+----+-------------+----------+-------+----------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | range | PRIMARY,emp_no | PRIMARY | 4       | NULL | 1419459 | Using where |
+----+-------------+----------+-------+----------------+---------+---------+------+---------+-------------+


mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)


mysql> explain select * from salaries where  salary<40000 and  emp_no<262449;
+----+-------------+----------+-------+---------------------------------+------------------+---------+------+-------+-------------+
| id | select_type | table    | type  | possible_keys                   | key              | key_len | ref  | rows  | Extra       |
+----+-------------+----------+-------+---------------------------------+------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | salaries | range | PRIMARY,emp_no,idx_salaries_sal | idx_salaries_sal | 4       | NULL | 23380 | Using where |



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-1762810/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Mysql index_merge
请登录后发表评论 登录
全部评论

注册时间:2015-05-01

  • 博文量
    383
  • 访问量
    179259