詳解MySQL數(shù)據(jù)庫的Index Condition Pushdown(ICP)特性
概述
今天主要介紹一下mysql的ICP特性，可能很多人都沒聽過，這里用一個實驗來幫助大家加深一下理解。
一、Index_Condition_Pushdown
Index Condition Pushdown (ICP)是MySQL用索引去表里取數(shù)據(jù)的一種優(yōu)化。如果禁用ICP，引擎層會穿過索引在基表中尋找數(shù)據(jù)行，然后返回給MySQL Server層，再去為這些數(shù)據(jù)行進行WHERE后的條件的過濾。
ICP啟用，如果部分WHERE條件能使用索引中的字段，MySQL Server 會把這部分下推到引擎層。存儲引擎通過使用索引條目，然后推索引條件進行評估，使用這個索引把滿足的行從表中讀取出。ICP能減少引擎層訪問基表的次數(shù)和MySQL Server 訪問存儲引擎的次數(shù)?？傊?ICP的優(yōu)化在引擎層就能夠過濾掉大量的數(shù)據(jù)，這樣無疑能夠減少了對base table和mysql server的訪問次數(shù)。
ICP的優(yōu)化用于range, ref, eq_ref, and ref_or_null訪問方法，當這些需要訪問全表的行。這個策略可以用于INNODB和MyISAM表。
二、實驗
先從一個簡單的實驗開始直觀認識ICP的作用。
1、導入示例數(shù)據(jù)
這里使用Employees Sample Database，作為示例數(shù)據(jù)庫。
將下載的壓縮包解壓后，會看到一系列的文件，其中employees.sql就是導入數(shù)據(jù)的命令文件。執(zhí)行
- #yum -y install bzip2
- #tar -xvf employees_db-full-1.0.6.tar.bz2
- #mysql -uroot -p<employees.sql
就可以完成建庫、建表和load數(shù)據(jù)等一系列操作。此時數(shù)據(jù)庫中會多一個叫做employees的數(shù)據(jù)庫。庫中的表如下：
- mysql > SHOW TABLES;
下面將使用employees表做實驗。
2、建立聯(lián)合索引
employees表包含雇員的基本信息，表結(jié)構(gòu)如下：
- mysql > DESC employees.employees;
這個表默認只有一個主索引，因為ICP只能作用于二級索引，所以我們建立一個二級索引：
- ALTER TABLE employees.employees ADD INDEX first_name_last_name (first_name, last_name);
這樣就建立了一個first_name和last_name的聯(lián)合索引。
3、查詢(ICP啟用)
為了明確看到查詢性能，啟用profiling并關(guān)閉query cache：
- SET profiling = 1;
- SET query_cache_type = 0;
- SET GLOBAL query_cache_size = 0;
然后看下面這個查詢：
- mysql > SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
根據(jù)MySQL索引的前綴匹配原則，兩者對索引的使用是一致的，即只有first_name采用索引，last_name由于使用了模糊前綴，沒法使用索引進行匹配。我將查詢聯(lián)系執(zhí)行三次，結(jié)果如下：
- mysql> show profiles;
查看執(zhí)行計劃
- mysql> explain SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
4、查詢(ICP禁用)
關(guān)閉ICP：
- SET optimizer_switch='index_condition_pushdown=off';
在運行三次相同的查詢，結(jié)果如下：
有意思的事情發(fā)生了，關(guān)閉ICP后，同樣的查詢，耗時是之前的三倍以上。
下面我們用explain看看后者的執(zhí)行計劃：
- mysql> explain SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
從開啟ICP和關(guān)閉ICP的執(zhí)行計劃可以看到區(qū)別在于Extra，開啟ICP時，用的是Using index condition;關(guān)閉ICP時，是Using where。
其中Using index condition就是ICP提高查詢性能的關(guān)鍵。下面說明ICP提高查詢性能的原理。
三、原理
ICP的原理簡單說來就是將可以利用索引篩選的where條件在存儲引擎一側(cè)進行篩選，而不是將所有index access的結(jié)果取出放在server端進行where篩選。
以上面的查詢?yōu)槔跊]有ICP時，首先通過索引前綴從存儲引擎中讀出224條first_name為Mary的記錄，然后在server段用where篩選last_name的like條件;而啟用ICP后，由于last_name的like篩選可以通過索引字段進行，那么存儲引擎內(nèi)部通過索引與where條件的對比來篩選掉不符合where條件的記錄，這個過程不需要讀出整條記錄，同時只返回給server篩選后的6條記錄，因此提高了查詢性能。
1) 不使用ICP時，如何進行索引掃描
(1)當storage engine讀取下一行時，首先讀取索引元組(index tuple)，然后使用索引元組在基表中(base table)定位和讀取整行數(shù)據(jù)。
(2) sever層評估where條件，如果該行數(shù)據(jù)滿足where條件則使用，否則丟棄。
(3)執(zhí)行第1步，直到最后一行數(shù)據(jù)。
2)使用ICP時，如何進行索引掃描
(1)storage engine從索引中讀取下一條索引元組。
(2) storage engine使用索引元組評估下推的索引條件。如果沒有滿足where條件，storage engine將會處理下一條索引元組(回到上一步)。只有當索引元組滿足下推的索引條件的時候，才會繼續(xù)去基表中讀取數(shù)據(jù)。
(3)如果滿足下推的索引條件，storage engine通過索引元組定位基表的行和讀取整行數(shù)據(jù)并返回給server層。
(4)server層評估沒有被下推到storage engine層的where條件，如果該行數(shù)據(jù)滿足where條件則使用，否則丟棄。
用兩張圖來做說明：
關(guān)閉ICP
在不支持ICP的系統(tǒng)下，索引僅僅作為data access使用。
開啟ICP
在ICP優(yōu)化開啟時，在存儲引擎端首先用索引過濾可以過濾的where條件，然后再用索引做data access，被index condition過濾掉的數(shù)據(jù)不必讀取，也不會返回server端。
