ICP(index condition pushdown)是mysql利用索引(二級索引)元組和篩字段在索引中的where條件從表中提取資料記錄的一種優化操作。ICP的思想是:存儲引擎在通路索引的時候檢查篩選字段在索引中的where條件(pushed index condition,推送的索引條件),如果索引元組中的資料不滿足推送的索引條件,那麼就過濾掉該條資料記錄。ICP(優化器)盡可能的把index condition的處理從server層下推到storage engine層。storage engine使用索引過過濾不相關的資料,僅傳回符合index condition條件的資料給server層。也是說資料過濾盡可能在storage engine層進行,而不是傳回所有資料給server層,然後後再根據where條件進行過濾。
使用ICP(mysql 5.6版本以前)和沒有使用ICP的資料通路和提取過程如下:
1) 當storage engine讀取下一行時,首先讀取索引元組(index tuple),然後使用索引元組在基表中(base table)定位和讀取整行資料。
2) sever層評估where條件,如果該行資料滿足where條件則使用,否則丢棄。
3) 執行1),直到最後一行資料。
優化器使用ICP時,server層将會把能夠通過使用索引進行評估的where條件下推到storage engine層。資料通路和提取過程如下:
1) storage engine從索引中讀取下一條索引元組。
2) storage engine使用索引元組評估下推的索引條件。如果沒有滿足wehere條件,storage engine将會處理下一條索引元組(回到上一步)。隻有當索引元組滿足下推的索引條件的時候,才會繼續去基表中讀取資料。
3) 如果滿足下推的索引條件,storage engine通過索引元組定位基表的行和讀取整行資料并傳回給server層。
4) server層評估沒有被下推到storage engine層的where條件,如果該行資料滿足where條件則使用,否則丢棄(第二圖虛線的using where 表示如果where條件中含有沒有被索引的字段,則還是要經過MySQL Server 層過濾)。
ICP的開啟優化功能與關閉
MySQL5.6可以通過設定optimizer_switch([global|session],dynamic)變量開啟或者關閉index_condition_push優化功能,預設開啟。
mysql > set optimizer_switch=’index_condition_pushdown=on|off’
用explain檢視執行計劃時,如果執行計劃中的Extra資訊為“using index condition”,表示優化器使用的index condition pushdown。
在mysql5.6以前,還沒有采用ICP這種查詢優化,where查詢條件中的索引條件在某些情況下沒有充分利用索引過濾資料。假設一個組合索引(多列索引)K包含(c1,c2,…,cn)n個列,如果在c1上存在範圍掃描的where條件,那麼剩餘的c2,…,cn這n-1個上索引都無法用來提取和過濾資料(不管不管是唯一查找還是範圍查找),索引記錄沒有被充分利用。即組合索引前面字段上存在範圍查詢,那麼後面的部分的索引将不能被使用,因為後面部分的索引資料是無序。比如,索引key(a,b)中的元組資料為(0,100)、(1,50)、(1,100) ,where查詢條件為 a < 2 and b = 100。由于b上得索引資料并不是連續區間,因為在讀取(1,50)之後不再會讀取(1,100),mysql優化器在執行索引區間掃描之後也不再掃描組合索引其後面的部分。
示範執行個體
當開啟ICP時(預設開啟):
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------+
| 1 | 0.00060275 | select * from employees where first_name='Anneke' and last_name like '%sig' |
+----------+------------+--------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000094 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000044 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000093 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000189 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
根據MySQL的最左字首原則, first_name 可以使用索引,last_name采用了like 模糊查詢,不能使用索引。查詢時間耗時0.00060275秒,sending data耗時0.000189秒。
當關閉ICP時:
mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------+
| 2 | 0.00097000 | select * from employees where first_name='Anneke' and last_name like '%sig' |
+----------+------------+--------------------------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000049 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000735 | 0.001000 | 0.000000 | 0 | 0 |
| end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
此時查詢耗時0.00097000秒,sending data耗時0.000735秒。可以看出ICP 開啟時整個sql執行時間是未開啟的2/3,sending data環節的時間消耗前者僅是後者的1/4。
開啟ICP時:
mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 44 | const | 224 | Using index condition |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
關閉ICP時:
mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
| 1 | SIMPLE | employees | ref | idx_emp_fnln | idx_emp_fnln | 44 | const | 224 | Using where |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
ICP 開啟時的執行計劃 含有 Using index condition 标示 ,表示優化器使用了ICP對資料通路進行優化。ICP 關閉時的執行計劃顯示use where。
以上面的查詢為例關閉ICP 時,存儲引擎通字首index first_name 通路表中225條first_name 為Anneke的資料,并在MySQL server層根據last_name like '%sig' 進行過濾
開啟ICP 時,last_name 的like '%nta'條件可以通過索引字段last_name進行過濾,在存儲引擎内部通過與where條件的對比,直接過濾掉不符合條件的資料。該過程不回表,隻通路符合條件的1條記錄并傳回給MySQL Server ,有效的減少了io通路和各層之間的互動。
參考文章:《MySQL Index Condition Pushdown(ICP)性能優化方法執行個體》、《MySQL5.6之Index Condition Pushdown(ICP,索引條件下推)》