程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL 優化之 ICP (index condition pushdown:索引條件下推)，conditionpushdown

MySQL 優化之 ICP (index condition pushdown:索引條件下推)，conditionpushdown

編輯：MySQL綜合教程

MySQL 優化之 ICP (index condition pushdown:索引條件下推)，conditionpushdown

ICP技術是在MySQL5.6中引入的一種索引優化技術。它能減少在使用二級索引過濾where條件時的回表次數和減少MySQL server層和引擎層的交互次數。在索引組織表中，使用二級索引進行回表的代價相比堆表中是要高一些的。相關文檔地址：http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

Index Condition Pushdown optimization is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows. This strategy can be used for InnoDB and MyISAM tables. (Note that index condition pushdown is not supported with partitioned tables in MySQL 5.6; this issue is resolved in MySQL 5.7.) For InnoDB tables, however, ICP is used only for secondary indexes. The goal of ICP is to reduce the number of full-record reads and thereby reduce IO operations. For InnoDB clustered indexes(值主鍵索引), the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce IO.

要想深入理解 ICP 技術，必須先理解數據庫是如何處理 where 中的條件的。具體可以參考何登成博士的文章：http://hedengcheng.com/?p=577

對 where 中過濾條件的處理，根據索引使用情況分成了三種：index key, index filter, table filter

1. index key

用於確定SQL查詢在索引中的連續范圍(起始范圍+結束范圍)的查詢條件，被稱之為Index Key。由於一個范圍，至少包含一個起始與一個終止，因此Index Key也被拆分為Index First Key和Index Last Key，分別用於定位索引查找的起始，以及索引查詢的終止條件。也就是說根據索引來確定掃描的范圍。

2. index filter

在使用 index key 確定了起始范圍和介紹范圍之後，在此范圍之內，還有一些記錄不符合where 條件，如果這些條件可以使用索引進行過濾，那麼就是 index filter。也就是說用索引來進行where條件過濾。

3. table filter

where 中的條件不能使用索引進行處理的，只能訪問table，進行條件過濾了。

也就是說各種各樣的 where 條件，在進行處理時，分成了上面三種情況，一種條件會使用索引確定掃描的范圍；一種條件可以在索引中進行過濾；一種必須回表進行過濾；

如何確定哪些where條件分別是 index key, index filter, table filter，可以參考何博士的文章。

在 MySQL5.6 之前，並不區分Index Filter與Table Filter，統統將Index First Key與Index Last Key范圍內的索引記錄，回表讀取完整記錄，然後返回給MySQL Server層進行過濾。而在MySQL 5.6之後，Index Filter與Table Filter分離，Index Filter下降到InnoDB的索引層面進行過濾，減少了回表與返回MySQL Server層的記錄交互開銷，提高了SQL的執行效率。

所以所謂的 ICP 技術，其實就是 index filter 技術而已。只不過因為MySQL的架構原因，分成了server層和引擎層，才有所謂的“下推”的說法。所以ICP其實就是實現了index filter技術，將原來的在server層進行的table filter中可以進行index filter的部分，在引擎層面使用index filter進行處理，不再需要回表進行table filter。

4. ICP 技術啟用前後比較

To see how this optimization works, consider first how an index scan proceeds when Index Condition Pushdown is not used:

Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

When Index Condition Pushdown is used, the scan proceeds like this instead:

Get the next row's index tuple (but not the full table row).
Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.
If the condition is satisfied, use the index tuple to locate and read the full table row.
Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

When Index Condition Pushdown is used, the Extra column in EXPLAIN output shows Using index condition. It will not show Index only because that does not apply when full table rows must be read.

5. ICP 例子

官方文檔給出了一個例子：

Suppose that we have a table containing information about people and their addresses and that the table has an index defined as INDEX (zipcode, lastname, firstname). If we know a person's zipcode value but are not sure about the last name, we can search like this:

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

MySQL can use the index to scan through people with zipcode='95054'. The second part (lastname LIKE '%etrunia%') cannot be used to limit the number of rows that must be scanned, so without Index Condition Pushdown, this query must retrieve full table rows for all the people who have zipcode='95054'.

With Index Condition Pushdown, MySQL will check the lastname LIKE '%etrunia%' part before reading the full table row. This avoids reading full rows corresponding to all index tuples that do not match the lastname condition.

Index Condition Pushdown is enabled by default; it can be controlled with the optimizer_switch system variable by setting the index_condition_pushdown flag. See Section 8.9.2, “Controlling Switchable Optimizations”.

上面例子中的 lastername like '%etrunia%' 和 address like '%Main Street%' 本來是無法使用復合索引 index(zipcode, lastername, firstname) 進行過濾的，但是因為有了ICP技術，所以他們可以在 index filter 階段使用索引進行過濾，無需回表進行 table filter.

例子2：

role_goods 表上有組合索引 index(roleId,status,number)，下面的select語句，因為 “索引最左前綴原則”，只能使用到組合索引的 roleId 部分，但是因為 ICP 技術的存在，現在 number 條件過濾也可以在 index filter 階段完成了，無需像以前一樣需要進行 table filer 了：

mysql> explain select * from role_goods where roleId=100000001 and number=1;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | role_goods | ref  | roleId_2      | roleId_2 | 9       | const |   14 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

可以看到 key_len = 9, 因為 roleId 是big int 類型，所以 key_len = 8 + 1 = 9; 所以在 index key 階段中，並沒有使用到組合索引 index(roleId,status,number) 中的 number 字段(因為中間有一個status字段沒有出現在where 條件中)，但是 “Using index condition” 卻說明使用到了ICP技術，顯然是 number =1 條件過濾使用到了ICP技術。

參考：

http://hedengcheng.com/?p=577

http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html