MySQL optimized ICP (index condition pushdown: Index condition push)

ICP Technology is an index optimization technique introduced in MySQL5.6. It reduces the number of back tables when using the two-level index to filter where conditions and reduces the number of interactions between the MySQL server layer and the engine layer. In the Index organization table, the cost of using a two-level index to back up the table is higher than in the heap table. Related Documents address: http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

Index Condition pushdown optimization is used for the range , ref< /code>, eq_ref , and ref_or_null access methods when there was A need to access full table rows. This strategy can is used for innodb and myisam tables. (Note that the index condition pushdown is not supported with partitioned tables in MySQL 5.6; This issue was 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 (value primary key index), the complete record was already read into the innodb buffer. Using ICP In the case does not reduce IO.

To understand the ICP technology in depth, you must first understand how the database handles the conditions in the where. Refer to Dr. Ho Dengcheng's article for details: http://hedengcheng.com/?p=577

The processing of the filter conditions in where is divided into three types according to the Index usage: Index key, index filter, table filter

1. Index key

the query condition used to determine the contiguous range (start range + END range) of an SQL query in the index is called the index Key. Because a range contains at least one start and one termination, index key is also split into index first key and index last key, respectively, to locate the start of the index lookup, and the termination criteria for the index query.

2. Index Filter

After using the index key to determine the starting range and introduction scope, there are some records in this range that do not meet the Where condition, and if these conditions can be filtered using an index, then the index filter.

3. Table Filter

The condition in where cannot be processed using an index, only the table can be accessed, and the condition is filtered.

How to determine index key, index filter, table filter, you can refer to Dr. Ho's article.

Prior to MySQL5.6, the index filter and table filter were not distinguished, and all indexes were recorded in the range indexed by first key and index last key, the full record was read back to the table, and then returned to the MySQL server layer for filtering. After MySQL 5.6, the index filter is separated from the table filter, and the index filter is filtered to the innodb level, reducing the cost of the record interaction between the back table and the return MySQL server layer, which improves the efficiency of SQL execution.

so the so-called ICP technology, in fact, is the index filter technology only . Just because of the architecture of MySQL, divided into the server layer and the engine layer, there is a so-called "push down" argument. so the ICP is actually the implementation of the index filter technology, the original table filter in the server layer can be used in the index filter portion, at the engine level using the index filter for processing, It is no longer necessary to return tables for table filter.

4. ICP Technology before and after the comparison

Optimization works, consider first how a index scan proceeds when index Condition pushdown are not used:

1. Get the next row, first by reading the index tuple, and then by using the-the index tuple to locate and read the full table ro W.

2. Test the part of the WHERE condition, applies to this table. Accept or reject the row based on the test result.

When Index Condition pushdown was used, the scan proceeds like this instead:

1. Get the next row ' s index tuple (but not the full table row).

2. test the part of the where condition that applies to the this table and can is checked using only index columns . If the condition is not a satisfied, proceed to the index of the tuple for the next row.

3. If the condition is satisfied, use the index tuple to locate and read the full table row.

4. Test the remaining part of the where condition, applies To This table. Accept or reject the row based on the test result.

When Index Condition pushdown was used, the Extra column in EXPLAIN output shows Using index condition . It won't show because that does isn't apply when the full Index only table rows must is read.

5. ICP Example

An example is given in the official documentation:

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

 select  *    people  where  Zipcode=   " 95054    

MySQL can use the "Index to scan" through people with zipcode=‘95054‘ . the second part ( lastname LIKE ‘%etrunia%‘ ) cannot is used to limit the number of rows that must is scanned, so without Index Condition Pu Shdown, this query must retrieve full table rows for all the people zipcode=‘95054‘ .

with Index Condition pushdown, MySQL would check the part lastname LIKE ‘%etrunia%‘ before reading the full table row. This avoids reading full rows corresponding to all index tuples, that does not match the lastname condition.

Index Condition pushdown is enabled by default; It can controlled with the optimizer_switch system variable by setting the index_condition_pushdown flag. See section 8.9.2, "Controlling switchable optimizations".

In the example above, lastername like '%etrunia% ' and address like '%main street% ' would have been unable to filter using the composite index index (zipcode, Lastername, FirstName), However, because of the ICP technology, they can be filtered using the index filter stage, without having to return the table to the tables filter.

Example 2:

The Role_goods table has a combined index (Roleid,status,number), the following SELECT statement, because "index leftmost prefix principle", can only be used to the Roleid portion of the combined index, but because of the existence of ICP technology, now number Conditional filtering can also be done in the index filter phase without the need for table filer as before:

Mysql>ExplainSelect *  fromRole_goodswhereRoleid=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|    - |UsingIndexCondition|+----+-------------+------------+------+---------------+----------+---------+-------+------+------------------ -----+1Rowinch Set(0.01Sec

You can see Key_len = 9, because Roleid is the big int type, so Key_len = 8 + 1 = 9; Therefore, in the index key stage, there is no use to the number field in the composite Index (Roleid,status,number) (Because a status field in the middle does not appear in the Where condition), but "using index cond Ition "But the use of ICP Technology, is clearly the number =1 conditional filtering used to the ICP technology.

Reference:

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

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

MySQL optimized ICP (index condition pushdown: Index condition push)