MySQL （ICP）索引条件下推对比Oracle进行说明

第一次看到这个名词，与Oracle FPD - filter push-down想到了一块，但是后来才发现他们根本同一个东西，简单的收ICP就是当索引包含所有的访问字段的时候，可以在根据前导列过滤掉条件的时候，同时过滤掉另外的条件，比如说
CREATE TABLE TESTICP（A INT,B INT,C NAME）;
ALTER TABLE TESTTICP ADD KEY（A,B）;
SELECT * FROM TESTICP WHERE A=1 AND B <10
的时候，如果未使用ICP就是通过A=1的条件返回结果集然后通过回表操作后然后过滤掉B<10的条件，这种情况下额外的并不满足B<10的结果集通过回表操作，这样加大了离散读的压力，如果了解ORACLE的朋友一定记得CLUSTER_FACTOR这个概念，他用于描述索引相对表中数据的有序程度，其最大值为表的行数，最小值为表的块数，越小代表索引和表的数据越相似，也就是表中这列是比较有序的，如果越大那么回表的操作越耗时（离散读取越厉害），这点虽然在MYSQL还不太了解但是一定会受到这样的影响。所以及早的过滤掉不需要的数据是非常必要的。在ORACLE中这也许不是问题，但是MYSQL知道5.6才引入了ICP。我们先来看看ORACLE的执行计划
使用脚本：CREATE TABLE TESTICP（A INT,B INT,C varchar2（20））;
declare
i number（10）;
begin
for i in 1..1000
loop
insert into TESTICP
values（i,i,"gaopeng"）;
end loop;
end;
SELECT * FROM TESTICP WHERE A=1 AND B <10;
--------------------------------------------------------------------------------
Plan hash value: 446810821
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 （0
| 1 | TABLE ACCESS BY INDEX ROWID| TESTICP | 1 | 38 | 3 （0
|* 2 | INDEX RANGE SCAN | TESTICP_INDEX | 1 | | 2 （0
--------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
2 - access（"A"=1 AND "B"<10）
非常加单我们只需要看到access（"A"=1 AND "B"=1）就知道是通过"A"=1 AND "B"=1来访问索引的
如果是FILTER B=1我们可以理解为访问索引后过滤的。
SQL> explain plan for select * from testicp where a=1 and c="gtest";
Explained
SQL> select * from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 446810821
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 （0
|* 1 | TABLE ACCESS BY INDEX ROWID| TESTICP | 1 | 38 | 3 （0
|* 2 | INDEX RANGE SCAN | TESTICP_INDEX | 1 | | 2 （0
--------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
1 - filter（"C"="gtest"）
2 - access（"A"=1）
Note
-----
- dynamic sampling used for this statement （level=2）
19 rows selected
如果我们改变为and c="gtest"
可以看到 filter（"C"="gtest"），这就是所谓的过滤。是索引回表后过滤的。但这一切在ORACLE认为理所当然的东西到了MYSQL到了5.6才实现。我们通过MYSQL来做一下脚本使用：create table testicp（A INT,B INT,C varchar（20））;
delimiter //
create procedure myproc3（）
begin
declare num int;
set num=1;
while num <= 1000 do
insert into testicp values（num,num,"gaopeng"）;
set num=num+1;
end while;
end//
call myproc3（） //
delimiter ;
alter table testicp add key（a,b）;

explain select * from testicp where a=1 and b<10;
mysql> explain select * from testicp where a=1 and b<10;
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | testicp | range | A | A | 10 | NULL | 1 | Using index condition |
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+这里使用关键字Using index condition加以说明，他受参数
optimizer_switch="index_condition_pushdown=on"
影响，如果我们设置optimizer_switch="index_condition_pushdown=off"再来看一下set optimizer_switch="index_condition_pushdown=off"
mysql> explain select * from testicp where a=1 and b<10;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | testicp | range | A | A | 10 | NULL | 1 | Using where |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
1 row in set （0.01 sec）可以看到这里变成了Using where，这代表没有使用icp。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址

首页 / 数据库 / MySQL / MySQL （ICP） 索引条件下推对比Oracle进行说明

首页 / 数据库 / MySQL / MySQL （ICP）索引条件下推对比Oracle进行说明