1. 首页
  2. IT资讯

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
——————————————————————————–
| 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。

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

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183402.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code