Index Condition Pushdown(ICP)是针对mysql使用索引从表中检索行数据时的一种优化方法。
原理：
在没有ICP特性之前，存储引擎根据索引去基表查找并将数据返回给mysql server，mysql server再根据where条件进行数据过滤。
有了ICP之后，在取出索引的同时，判断是否可以根据索引中的列进行where条件过滤，也就是将where的部分过滤操作放在了存储引擎层。这样就会减少上层sql层对记录的获取。
当sql使用覆盖索引时，不支持ICP优化方法。
ICP优化支持range、ref、eq_ref、ref_or_null类型的查询。查询优化器会给出相应的提示：Using index condition。当开启ICP后，在执行计划的extra列会显示：Using index condition。
ICP支持innodb、myisam表。对于innodb表，ICP只是用于辅助索引。
在5.6中，ICP不支持分区表。这个问题在mysql 5.7中得到解决。
优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层。数据访问和提取过程如下：
1) storage engine从索引中读取下一条索引元组。
2) storage engine使用索引元组评估下推的索引条件。如果没有满足where条件，storage engine将会处理下一条索引元组（回到上一步）。只有当索引元组满足下推的索引条件的时候，才会继续去基表中读取数据。
3) 如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。
4) server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃。
没有ICP之前：
开启ICP之后，就变成：
默认是开启ICP的，手动开启/关闭ICP：
set optimizer_switch = 'index_condition_pushdown=off';
set optimizer_switch = 'index_condition_pushdown=on';
测试过程
1.环境准备|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
#mysql 5.6.25
#关闭结果缓存
mysql>
set
global
query_cache_size=0;
mysql>
set
query_cache_type=
off
;
#查看表结构
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
,
PRIMARY
KEY
(`emp_no`),
KEY
`idx_first_last_name` (`first_name`,`last_name`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8
1 row
in
set
(0.00 sec)
mysql>
|
2.开启ICP后进行测试
|
1
2
3
4
5
|
mysql>
set
profiling = 1;
mysql>
select
*
from
employees
where
first_name=
'Anneke'
and
last_name
like
'%sig'
;
mysql> explain
select
*
from
employees
where
first_name=
'Anneke'
and
last_name
like
'%sig'
;
mysql> show profiles;
mysql> show profile cpu,block io
for
query 1;
|
3.关闭ICP后进行测试
|
1
2
3
4
5
6
|
mysql>
set
optimizer_switch=
'index_condition_pushdown=off'
;
mysql>
set
profiling = 1;
mysql>
select
*
from
employees
where
first_name=
'Anneke'
and
last_name
like
'%sig'
;
mysql> explain
select
*
from
employees
where
first_name=
'Anneke'
and
last_name
like
'%sig'
;
mysql> show profiles;
mysql> show profile cpu,block io
for
query 1;
|
4.结果比较
开启ICP后的执行计划：执行计划中extra部分的内容是"using index condition"
|
1
2
3
4
5
6
|
mysql> explain
select
*
from
employees
where
first_name=
'Anneke'
and
last_name
like
'%sig'
;
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-----------------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employees | ref | idx_first_last_name | idx_first_last_name | 44 | const | 224 | Using
index
condition |
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-----------------------+
|
关闭ICP后的执行计划：执行计划中extra部分的内容是"using where"
|
1
2
3
4
5
6
|
mysql> explain
select
*
from
employees
where
first_name=
'Anneke'
and
last_name
like
'%sig'
;
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-------------+
| 1 | SIMPLE | employees | ref | idx_first_last_name | idx_first_last_name | 44 | const | 224 | Using
where
|
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+-------------+
|
开启ICP后的profile内容：Sending data部分的值是0.000212s
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> show profile cpu,block io
for
query 1;
+
----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+
----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000114 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
|
statistics
| 0.000383 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000212 | 0.000000 | 0.000000 | 0 | 0 |
|
end
| 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| query
end
| 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000020 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
+
----------------------+----------+----------+------------+--------------+---------------+
|
关闭ICP后的profile内容：Sending data部分的值是0.010990s
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> show profile cpu,block io
for
query 1;
+
----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+
----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000165 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000022 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000027 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000039 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000037 | 0.001000 | 0.000000 | 0 | 0 |
|
statistics
| 0.000483 | 0.001000 | 0.000000 | 0 | 0 |
| preparing | 0.000022 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.010990 | 0.007999 | 0.002000 | 0 | 0 |
|
end
| 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| query
end
| 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000028 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
+
----------------------+----------+----------+------------+--------------+---------------+
|
其它：
当sql使用覆盖索引时，不支持ICP优化方法
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> explain
select
first_name,last_name
from
employees
where
first_name=
'Anneke'
and
last_name=
'Porenta'
;
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+--------------------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | employees | ref | idx_first_last_name | idx_first_last_name | 94 | const,const | 1 | Using
where
; Using
index
|
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+--------------------------+
mysql> explain
select
*
from
employees
where
first_name=
'Anneke'
and
last_name=
'Porenta'
;
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | employees | ref | idx_first_last_name | idx_first_last_name | 94 | const,const | 1 | Using
index
condition |
+
----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+-----------------------+
|
290
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
