MySQL Index Condition Pushdow!n(ICP)性能优化方法实例

 　　这篇文章主要介绍了MySQL Index Condition Pushdown(ICP)性能优化方法实例,本文讲解了概念介绍、原理、实践案例、案例分析、ICP的使用限制等内容,需要的朋友可以参考下

　　一 概念介绍

　　Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。

　　a 当关闭ICP时,index 仅仅是data access 的一种访问方式，存储引擎通过索引回表获取的数据会传递到MySQL Server 层进行where条件过滤。

　　b 当打开ICP时,如果部分where条件能使用索引中的字段,MySQL Server 会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行数据过滤,而非将所有通过index access的结果传递到MySQL server层进行where过滤.

　　优化效果:ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数,减少io次数，提高查询语句性能。

　　二 原理

　　Index Condition Pushdown is not used:

　　1 Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.

　　2 Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

　　Index Condition Pushdown is used

　　1 Get the next row s index tuple (but not the full table row).

　　2 Test the part of the WHERE condition that applies to th

　　If the condition is not satisfied, proceed to the index tuple for the next row.

　　3 If the condition is satisfied, use the index tuple to locate and read the full table row.

　　4 est the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

　　三 实践案例

　　a 环境准备

　　数据库版本 5.6.16

　　关闭缓存

　　代码如下:

　　set query_cache_size=0;

　　set query_cache_type=OFF;

　　测试数据下载地址

　　b 当开启ICP时

　　代码如下:

　　mysql> SET profiling = 1;

　　Query OK, 0 rows affected, 1 warning (0.00 sec)

　　mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;

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

　　| emp_no | birth_date | first_name | last_name | gender | hire_date |

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

　　| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |

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

　　1 row in set (0.00 sec)

　　mysql> show profiles;

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

　　| Query_ID | Duration | Query |

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