MySQL(ICP)索引條件下推對比Oracle進行說明

第一次看到這個名詞，與ORACLE FPD - filter push-down想到了一塊，但是后來才發現他們根本同一個東西，簡單的收ICP就是當索引包

第一次看到這個名詞，與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'再來看一下

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com