mysql (ICP) 索引条件下推对比ORACLE进行说明-白红宇

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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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;

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

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

| 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;

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

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

| 1 | SIMPLE | testicp | range | A | A | 10 | NULL | 1 | Using where |

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

1 row in set (0.01 sec)