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。

mysql (ICP) 索引條件下推對比ORACLE進行說明

相關文章