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