2019獨角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

一、Index Condition Pushdown簡介

ICP（index condition pushdown）是mysql利用索引（二級索引）元組和篩字段在索引中的where條件從表中提取數(shù)據(jù)記錄的一種優(yōu)化操作。ICP的思想是：存儲引擎在訪問索引的時候檢查篩選字段在索引中的where條件（pushed index condition，推送的索引條件），如果索引元組中的數(shù)據(jù)不滿足推送的索引條件，那么就過濾掉該條數(shù)據(jù)記錄。ICP（優(yōu)化器）盡可能的把index condition的處理從server層下推到storage engine層。storage engine使用索引過過濾不相關(guān)的數(shù)據(jù)，僅返回符合index condition條件的數(shù)據(jù)給server層。也是說數(shù)據(jù)過濾盡可能在storage engine層進(jìn)行，而不是返回所有數(shù)據(jù)給server層，然后后再根據(jù)where條件進(jìn)行過濾。

二、ICP開啟和關(guān)閉時數(shù)據(jù)訪問和提取過程對比

優(yōu)化器沒有使用ICP時，數(shù)據(jù)訪問和提取的過程如下：

1）：MySQL Server發(fā)出讀取數(shù)據(jù)的命令，這是在執(zhí)行器中執(zhí)行如下代碼段，通過函數(shù)指針和handle接口調(diào)用存儲引擎的索引讀或全表表讀。此處進(jìn)行的是索引讀。

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

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

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

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

?

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

數(shù)據(jù)訪問和提取過程如下：

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

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

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

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

三、ICP測試

3.1? 對比執(zhí)行計劃的差別

聯(lián)合索引的第一個條件可以使用索引，第二個不能使用索引

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)

關(guān)閉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)

結(jié)論：

需要index condition pushdown 的query通常索引的字段出現(xiàn)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）。喜歡技術(shù)的一起來交流吧

轉(zhuǎn)載于:https://my.oschina.net/u/3023401/blog/1535362

總結(jié)

以上是生活随笔為你收集整理的MySQL Index Condition Pushdown的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。