一、Index Condition Pushdown簡介

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開啟和關閉時數據訪問和提取過程對比

優化器沒有使用ICP時，數據訪問和提取的過程如下：

1）：MySQL Server發出讀取數據的命令，這是在執行器中執行如下代碼段，通過函數指針和handle接口調用存儲引擎的索引讀或全表表讀。此處進行的是索引讀。

if (in_first_read){in_first_read= false;error= (*qep_tab->read_first_record)(qep_tab); //設定合適的讀取函數，如設定索引讀函數/全表掃描函數}elseerror= info->read_record(info);

2、3）：進入存儲引擎，讀取索引樹，在索引樹上查找，把滿足條件的（經過查找，紅色的滿足）從表記錄中讀出（步驟④，通常有IO），從存儲引擎返回⑤標識的結果。此處，不僅要在索引行進行索引讀取（通常是內存中，速度快。步驟③），還要進行進行步驟④，通常有IO。

6）：從存儲引擎返回查找到的多條元組給MySQL Server，MySQL Server在⑦得到較多的元組。

7、8）：⑦到⑧依據WHERE子句條件進行過濾，得到滿足條件的元組。注意在MySQL Server層得到較多元組，然后才過濾，最終得到的是少量的、符合條件的元組。

?

優化器使用ICP時，server層將會把能夠通過使用索引進行評估的where條件下推到storage engine層。

數據訪問和提取過程如下：

1)??? storage engine從索引中讀取下一條索引元組。

2)??? storage engine使用索引元組評估下推的索引條件。如果沒有滿足where條件，storage engine將會處理下一條索引元組（回到上一步）。只有當索引元組滿足下推的索引條件的時候，才會繼續去基表中讀取數據。

3)??? 如果滿足下推的索引條件，storage engine通過索引元組定位基表的行和讀取整行數據并返回給server層。

4)??? server層評估沒有被下推到storage engine層的where條件，如果該行數據滿足where條件則使用，否則丟棄。

三、ICP測試

3.1? 對比執行計劃的差別

聯合索引的第一個條件可以使用索引，第二個不能使用索引

root@localhost:mysql.sock? 15:33:47 [test]>explain select? *?? from person? where postadlcode between 300000 and 400000 and age > 40;+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+| id | select_type | table? | partitions | type? | possible_keys | key???? | key_len | ref? | rows | filtered | Extra???????????????? |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+|? 1 | SIMPLE????? | person | NULL?????? | range | idx_p_a?????? | idx_p_a | 7?????? | NULL |??? 1 |??? 33.33 | Using index condition |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.11 sec)

關閉ICP后

root@localhost:mysql.sock? 15:35:42 [test]>set optimizer_switch = "index_condition_pushdown=off";Query OK, 0 rows affected (0.00 sec)root@localhost:mysql.sock? 15:39:48 [test]>explain select? *?? from person? where postadlcode between 300000 and 400000 and age > 40;+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table? | partitions | type? | possible_keys | key???? | key_len | ref? | rows | filtered | Extra?????? |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|? 1 | SIMPLE????? | person | NULL?????? | range | idx_p_a?????? | idx_p_a | 7?????? | NULL |??? 1 |??? 33.33 | Using where |+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

where條件包含索引字段但用不到索引

root@localhost:mysql.sock? 15:39:49 [test]>explain select? *?? from person? where age > 40;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table? | partitions | type | possible_keys | key? | key_len | ref? | rows | filtered | Extra?????? |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+|? 1 | SIMPLE????? | person | NULL?????? | ALL? | NULL????????? | NULL | NULL??? | NULL |??? 7 |??? 33.33 | Using where |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)root@localhost:mysql.sock? 15:41:03 [test]>set optimizer_switch = "index_condition_pushdown=on";Query OK, 0 rows affected (0.00 sec)root@localhost:mysql.sock? 15:41:09 [test]>explain select? *?? from person? where age > 40;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table? | partitions | type | possible_keys | key? | key_len | ref? | rows | filtered | Extra?????? |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+|? 1 | SIMPLE????? | person | NULL?????? | ALL? | NULL????????? | NULL | NULL??? | NULL |??? 7 |??? 33.33 | Using where |+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)

結論：

需要index condition pushdown 的query通常索引的字段出現where子句里面都是范圍查詢。比如：

select * from tb where tb.key_part1 < x and tb.key_part2 = y????? ?
select * from tb where tb.key_part1 = x andtb.key_part2 like '%yyyy%'
select * from tb where tb.key_part1 > x and tb.key_part1 < y and tb.key_part1 > xx and tb.key_part2 < yy

但是需要注意的是：
1. 如果索引的第一個字段的查詢就是沒有邊界的比如 key_part1 like '%xxx%'，那么不要說ICP，就連索引都會沒法利用。
2. 如果select的字段全部在索引里面，那么就是直接的index scan了，沒有必要什么ICP。

為了方便大家交流，本人開通了微信公眾號，和QQ群1（291519319）和QQ群2（659336691）。喜歡技術的一起來交流吧