MySQL 索引之ICP

Index Condition Pushdown(IPC)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种 优化方式。
下面是普通的模式下索引访问数据的方式
即当关闭ICP时，index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤；
当ICP打开时，如果部分where条件能使用索引的字段，MySQL server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。


查看我的版本及优化器参数，index_condition_pushdown是打开的

点击(此处)折叠或打开

mysql> show variables like 'optimizer_switch%' \G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

点击(此处)折叠或打开

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+

我们在scott中新建立一张表

点击(此处)折叠或打开

CREATE TABLE employees AS SELECT * FROM employees.employees

点击(此处)折叠或打开

mysql> show create table employees \G;
*************************** 1. row ***************************
       Table: employees
Create Table: 

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

建立个索引

点击(此处)折叠或打开

mysql> alter table employees add key(birth_date);
Query OK, 0 rows affected (3.37 sec)
Records: 0 Duplicates: 0 Warnings: 0

我们看下执行计划

点击(此处)折叠或打开

DESC
SELECT * FROM employees e
WHERE e.birth_date >='1962-01-01'
AND e.birth_date < '1964-01-01' AND gender='F';

当家单列索引的时候，是没有的~

我们再添加一个索引，让两个列都有索引。

点击(此处)折叠或打开

ALTER TABLE employees ADD KEY(gender)

SHOW INDEX FROM employees;

点击(此处)折叠或打开

DESC
SELECT /*ICP*/ * FROM employees e
WHERE e.birth_date >='1962-01-01'
AND e.birth_date < '1964-01-01'
AND e.gender='F'

-------------------------------------------------------------------------------------------------------------------------------------------
然后我们只建立组合索引，看看

点击(此处)折叠或打开

ALTER TABLE employees ADD KEY(gender,birth_date);
DESC
SELECT /*ICP*/ * FROM employees e
WHERE e.birth_date >='1962-01-01'
AND e.birth_date < '1964-01-01'
AND e.gender='F'

当基数低的列在前的时候走 index  condition
---------------------------------------------------------------------------------------------------------------------------------------------------
然后我们把这个索引删除掉，重新建立

点击(此处)折叠或打开

ALTER TABLE employees drop index gender; ALTER TABLE employees ADD KEY(birth_date,gender);
DESC
SELECT /*ICP*/ * FROM employees e
WHERE e.birth_date >='1962-01-01'
AND e.birth_date < '1964-01-01'
AND e.gender='F'

ICP不见了,意思是说优化器很快能定位出数据，那么就不用ICP。

思考：为什么会出现ICP这种算法呢？
  Index Condition Pushdown (ICP)是MySQL用索引去表里取数据的一种优化。如果禁用ICP，引擎层会穿过索引在基表中寻找数据行，然后返回给MySQL Server层，再去为这些数据行进行WHERE后的条件的过滤。ICP启用，如果部分WHERE条件能使用索引中的字段，MySQL Server 会把这部分下推到引擎层。存储引擎通过使用索引条目，然后推索引条件进行评估，使用这个索引把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。总之是 ICP的优化在引擎层就能够过滤掉大量的数据，这样无疑能够减少了对base table和mysql server的访问次数。

结论：
建议在业务不明确的时候，建立组合索引基数低的在前。组合索引基数高的在前也不是，基数低的在前也不是，折中的办法就是建立两个单列的索引(对业务不熟悉)，如果你确定这两个列只有一个可能：是组合在一起的，那你就建立个组合索引。如果是两个索引，那么两个索引都用上了。


参考：http://www.ruzuojun.com/topic/860.html

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

转载于:http://blog.itpub.net/29990276/viewspace-1786675/