MySQL優(yōu)化之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 技術(shù)，必須先理解數(shù)據(jù)庫是如何處理 where 中的條件的。

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

1. index key

用于確定SQL查詢在索引中的連續(xù)范圍(起始范圍+結(jié)束范圍)的查詢條件，被稱之為Index Key。由于一個(gè)范圍，至少包含一個(gè)起始與一個(gè)終止，因此Index Key也被拆分為Index First Key和Index Last Key，分別用于定位索引查找的起始，以及索引查詢的終止條件。

2. index filter

在使用 index key 確定了起始范圍和介紹范圍之后，在此范圍之內(nèi)，還有一些記錄不符合where 條件，如果這些條件可以使用索引進(jìn)行過濾，那么就是 index filter。

3. table filter

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

如何確定 index key, index filter, table filter，可以參考何博士的文章。

在 MySQL5.6 之前，并不區(qū)分Index Filter與Table Filter，統(tǒng)統(tǒng)將Index First Key與Index Last Key范圍內(nèi)的索引記錄，回表讀取完整記錄，然后返回給MySQL Server層進(jìn)行過濾。而在MySQL 5.6之后，Index Filter與Table Filter分離，Index Filter下降到InnoDB的索引層面進(jìn)行過濾，減少了回表與返回MySQL Server層的記錄交互開銷，提高了SQL的執(zhí)行效率。

所以所謂的 ICP 技術(shù)，其實(shí)就是 index filter 技術(shù)而已。只不過因?yàn)镸ySQL的架構(gòu)原因，分成了server層和引擎層，才有所謂的“下推”的說法。所以ICP其實(shí)就是實(shí)現(xiàn)了index filter技術(shù)，將原來的在server層進(jìn)行的table filter中可以進(jìn)行index filter的部分，在引擎層面使用index filter進(jìn)行處理，不再需要回表進(jìn)行table filter。

4. ICP 技術(shù)啟用前后比較

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 例子

官方文檔給出了一個(gè)例子：

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%' 本來是無法使用復(fù)合索引 index(zipcode, lastername, firstname) 進(jìn)行過濾的，但是因?yàn)橛辛薎CP技術(shù)，所以他們可以在 index filter 階段使用索引進(jìn)行過濾，而不需要回表進(jìn)行 table filter.

例子2：

role_goods 表上有組合索引 index(roleId,status,number)，下面的select語句，因?yàn)?“索引最左前綴原則”，只能使用到 組合索引的 roleId 部分，但是因?yàn)?ICP 技術(shù)的存在，現(xiàn)在 number 條件過濾也可以在 index filter 階段完成了，無需像以前一樣需要進(jìn)行 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, 因?yàn)?roleId 是big int 類型，所以 key_len = 8 + 1 = 9; 所以在 index key 階段中，并沒有使用到 組合索引 index(roleId,status,number) 中的 number 字段(因?yàn)橹虚g有一個(gè)status字段沒有出現(xiàn)在where 條件中)，但是 “Using index condition” 卻說明使用到了ICP技術(shù)，顯然是 number =1 條件過濾使用到了ICP技術(shù)。

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