【MySQL】性能优化之 Index Condition Pushdown

 |

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

1 row in set (0.00 sec)

ICP 关闭时的执行计划显示use where.

1. mysql> explain select * from employees where first_name=‘Anneke‘ and last_name like ‘%nta‘ ;
2. +----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
3. | id | select_type | table     | type | possible_keys | key          | key_len | ref   | rows | Extra       |
4. +----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
5. | 1  | SIMPLE      | employees | ref  | idx_emp_fnln  | idx_emp_fnln | 44      | const | 224  | Using where |
6. +----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
7. 1 row in set (0.00 sec)

案例分析
以上面的查询为例关闭ICP 时,存储引擎通前缀index first_name 访问表中225条first_name 为Anneke的数据,并在MySQL server层根据last_name like ‘%sig‘ 进行过滤
开启ICP 时，last_name 的like ‘%sig‘条件可以通过索引字段last_name 进行过滤，在存储引擎内部通过与where条件的对比，直接过滤掉不符合条件的数据。该过程不回表,只访问符合条件的1条记录并返回给MySQL Server ,有效的减少了io访问和各层之间的交互。

ICP 关闭时 ，仅仅使用索引作为访问数据的方式。

ICP 开启时 ，MySQL将在存储引擎层 利用索引过滤数据，减少不必要的回表，注意 虚线的using where 表示如果where条件中含有没有被索引的字段，则还是要经过MySQL Server 层过滤。

四 ICP的使用限制 

1 当sql需要全表访问时,ICP的优化策略可用于range, ref, eq_ref,  ref_or_null 类型的访问数据方法 。
2 支持InnoDB和MyISAM表。
3 ICP只能用于二级索引，不能用于主索引。
4 并非全部where条件都可以用ICP筛选。
   如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤。
5 ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
6 5.6 版本的不支持分表的ICP 功能，5.7 版本的开始支持。
7 当sql 使用覆盖索引时，不支持ICP 优化方法。

1. mysql> explain select * from employees where first_name=‘Anneke‘ and last_name=‘Porenta‘ ;
2. +----+-------------+-----------+------+---------------+--------------+---------+-------------+------+-----------------------+
3. | id | select_type | table     | type | possible_keys | key          | key_len | ref         | rows | Extra                 |
4. +----+-------------+-----------+------+---------------+--------------+---------+-------------+------+-----------------------+
5. | 1  | SIMPLE | employees      | ref  | idx_emp_fnln  | idx_emp_fnln | 94      | const,const | 1    | Using index condition |
6. +----+-------------+-----------+------+---------------+--------------+---------+-------------+------+-----------------------+
7. 1 row in set (0.00 sec)
8. mysql> explain select first_name,last_name from employees where first_name=‘Anneke‘ and last_name=‘Porenta‘ ;
9. +----+-------------+-----------+------+---------------+--------------+---------+-------------+------+--------------------------+
10. | id | select_type | table     | type | possible_keys | key          | key_len | ref         | rows | Extra                    |
11. +----+-------------+-----------+------+---------------+--------------+---------+-------------+------+--------------------------+
12. | 1  | SIMPLE      | employees | ref  | idx_emp_fnln  | idx_emp_fnln | 94      | const,const | 1    | Using where; Using index |
13. +----+-------------+-----------+------+---------------+--------------+---------+-------------+------+--------------------------+
14. 1 row in set (0.00 sec)

5  参考文章
[1] http://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html [2] https://mariadb.com/kb/en/mariadb/mariadb-documentation/optimization-and-tuning/query-optimizations/index-condition-pushdown/ [3] http://olavsandstaa.blogspot.com/2011/04/mysql-56-index-condition-pushdown.html [4] http://www.mysqlperformanceblog.com/2013/03/14/mysql-5-6-10-optimizer-limitations-index-condition-pushdown/   转自：http://blog.itpub.net/22664653/viewspace-1210844/

【MySQL】性能优化之 Index Condition Pushdown

标签：warning   net   rmi   过滤   check   like   二级索引   can   准备