您当前的位置:首页 > mysql

MySQL (ICP) 索引条件下推对比Oracle进行说明

第一次看到这个名词,与 FPD - filter push-down想到了一块,但是后来才发现他们根本同一个东西,简单的收ICP就是当索引包含所有的访问字段的时候,可以在根据前导列过滤掉条件的时候,同时过滤掉另外的条件,比如说sb1linux系统宝典
CREATE TABLE TESTICP(A INT,B INT,C NAME);sb1linux系统宝典
ALTER TABLE TESTTICP ADD KEY(A,B);sb1linux系统宝典

sb1linux系统宝典
SELECT * FROM TESTICP WHERE A=1 AND B <10sb1linux系统宝典
的时候,如果未使用ICP就是通过A=1的条件返回结果集然后通过回表操作后然后过滤掉B<10的条件,这种情况下额外的并不满足B<10的结果集通过回表操作,这样加大了离散读的压力,如果了解ORACLE的朋友一定记得CLUSTER_FACTOR这个概念,他用于描述索引相对表中数据的有序程度,其最大值为表的行数,最小值为表的块数,越小代表索引和表的数据越相似,也就是表中这列是比较有序的 ,如果越大那么回表的操作越耗时(离散读取越厉害),这点虽然在MYSQL还不太了解但是一定会受到这样的影响。sb1linux系统宝典

所以及早的过滤掉不需要的数据是非常必要的。在ORACLE中这也许不是问题,但是MYSQL知道5.6才引入了ICP。sb1linux系统宝典

我们先来看看ORACLE的执行计划sb1linux系统宝典
使用脚本:sb1linux系统宝典

CREATE TABLE TESTICP(A INT,B INT,C varchar2(20));sb1linux系统宝典
 declare  sb1linux系统宝典
    i number(10);sb1linux系统宝典
 begin sb1linux系统宝典
  for i in 1..1000sb1linux系统宝典
  loopsb1linux系统宝典
  insert into TESTICPsb1linux系统宝典
    values(i,i,'gaopeng');sb1linux系统宝典
  end loop;sb1linux系统宝典
 end;sb1linux系统宝典
 SELECT * FROM TESTICP WHERE A=1 AND B <10;sb1linux系统宝典

sb1linux系统宝典
 --------------------------------------------------------------------------------sb1linux系统宝典
 Plan hash value: 446810821sb1linux系统宝典
 --------------------------------------------------------------------------------sb1linux系统宝典
 | Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPUsb1linux系统宝典
 --------------------------------------------------------------------------------sb1linux系统宝典
 |  0 | SELECT STATEMENT            |              |    1 |    38 |    3  (0sb1linux系统宝典
 |  1 |  TABLE ACCESS BY INDEX ROWID| TESTICP      |    1 |    38 |    3  (0sb1linux系统宝典
 |*  2 |  INDEX RANGE SCAN          | TESTICP_INDEX |    1 |      |    2  (0sb1linux系统宝典
 --------------------------------------------------------------------------------sb1linux系统宝典
 Predicate Information (identified by operation id):sb1linux系统宝典
 ---------------------------------------------------sb1linux系统宝典
    2 - access("A"=1 AND "B"<10)sb1linux系统宝典

sb1linux系统宝典
非常加单我们只需要看到access("A"=1 AND "B"=1)就知道是通过"A"=1 AND "B"=1来访问索引的sb1linux系统宝典
 如果是FILTER B=1我们可以理解为访问索引后过滤的。sb1linux系统宝典
SQL> explain plan for select * from testicp where a=1 and c='gtest';sb1linux系统宝典
 Explainedsb1linux系统宝典

sb1linux系统宝典
 SQL> select * from table(dbms_xplan.display);sb1linux系统宝典
 PLAN_TABLE_OUTPUTsb1linux系统宝典
 --------------------------------------------------------------------------------sb1linux系统宝典
 Plan hash value: 446810821sb1linux系统宝典
 --------------------------------------------------------------------------------sb1linux系统宝典
 | Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPUsb1linux系统宝典
 --------------------------------------------------------------------------------sb1linux系统宝典
 |  0 | SELECT STATEMENT            |              |    1 |    38 |    3  (0sb1linux系统宝典
 |*  1 |  TABLE ACCESS BY INDEX ROWID| TESTICP      |    1 |    38 |    3  (0sb1linux系统宝典
 |*  2 |  INDEX RANGE SCAN          | TESTICP_INDEX |    1 |      |    2  (0sb1linux系统宝典
 --------------------------------------------------------------------------------sb1linux系统宝典
 Predicate Information (identified by operation id):sb1linux系统宝典
 ---------------------------------------------------sb1linux系统宝典
    1 - filter("C"='gtest')sb1linux系统宝典
    2 - access("A"=1)sb1linux系统宝典
 Notesb1linux系统宝典
 -----sb1linux系统宝典
    - dynamic sampling used for this statement (level=2)sb1linux系统宝典
 19 rows selectedsb1linux系统宝典

sb1linux系统宝典
如果我们改变为and c='gtest'sb1linux系统宝典
可以看到 filter("C"='gtest'),这就是所谓的过滤。是索引回表后过滤的。sb1linux系统宝典

但这一切在ORACLE认为理所当然的东西到了MYSQL到了5.6才实现。我们通过MYSQL来做一下脚本使用:sb1linux系统宝典

create table testicp(A INT,B INT,C varchar(20));sb1linux系统宝典
 delimiter //sb1linux系统宝典
 create procedure myproc3() sb1linux系统宝典
 begin sb1linux系统宝典
 declare num int; sb1linux系统宝典
 set num=1; sb1linux系统宝典
 while num <= 1000 do sb1linux系统宝典
  insert into testicp  values(num,num,'gaopeng'); sb1linux系统宝典
  set num=num+1;sb1linux系统宝典
 end while;sb1linux系统宝典
  end//sb1linux系统宝典
  call myproc3() //sb1linux系统宝典
  delimiter ;sb1linux系统宝典
  alter table testicp add key(a,b);sb1linux系统宝典
  sb1linux系统宝典
 explain select * from testicp where a=1 and b<10;sb1linux系统宝典
  mysql> explain select * from testicp where a=1 and b<10;sb1linux系统宝典
 +----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+sb1linux系统宝典
 | id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra                |sb1linux系统宝典
 +----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+sb1linux系统宝典
 |  1 | SIMPLE      | testicp | range | A            | A    | 10      | NULL |    1 | Using index condition |sb1linux系统宝典
 +----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+sb1linux系统宝典

这里使用关键字Using index condition加以说明,他受参数sb1linux系统宝典
optimizer_switch='index_condition_pushdown=on' sb1linux系统宝典
影响,如果我们设置optimizer_switch='index_condition_pushdown=off'再来看一下sb1linux系统宝典

set  optimizer_switch='index_condition_pushdown=off'sb1linux系统宝典
 mysql> explain select * from testicp where a=1 and b<10;sb1linux系统宝典
 +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+sb1linux系统宝典
 | id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra      |sb1linux系统宝典
 +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+sb1linux系统宝典
 |  1 | SIMPLE      | testicp | range | A            | A    | 10      | NULL |    1 | Using where |sb1linux系统宝典
 +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+sb1linux系统宝典
 1 row in set (0.01 sec)sb1linux系统宝典

可以看到这里变成了Using where,这代表没有使用icp。sb1linux系统宝典

更多Oracle相关信息见 专题页面 sb1linux系统宝典



沪ICP备10206494号-4