MySQL優化之ICP(indexconditionpushdown:索引條件下推)_MySQL

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 中的條件的。

對 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。

3. table filter

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

如何確定 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技術。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com