MySQL Index Condition pushdown (ICP) optimization

Index Condition pushdown (ICP) indexing conditions push optimization applies to MySQL retrieving data rows through index in table, without ICP, the storage engine layer traverses the index to locate the base table (base table) and return them to the server layer, which is calculated by the server layer to filter where statements. With the ICP, and some of the filter criteria for the where statement can be detected by index, the MySQL server layer will speak part of the where Conditions to the storage engine layer. The storage engine evaluates the pushed index condition by using the index entry, and only reads rows of data that meet the index condition. The ICP can reduce the number of times the storage Engine access base table and server tier access storage engine;

The Index Condition Pushdown is optimized for use as Range,ref,eq_ref and ref_or_null to access all the rows of the method Access table. This policy can be used as InnoDB and MyISAM tables (note that the 5.6 version index Condition pushdown does not support partitioned tables, 5.7 support) for InnoDB tables, the ICP can only be used for two-level indexes, and the objective of the ICP is to reduce the base table access times, This reduces disk IO operations. For the InnoDB clustered index, all records have been read into InnoDB buffer, in which case the ICP does not reduce the number of IO.

　　　　　　　　　　　　　　　　　　　　

To understand how the optimizer works, consider how the index scan is done when the ICP is not being used?

1:server layer in order to get the next data row, the storage engine reads the index entry (tuple) corresponding to the data row, and the index entry is positioned to return the complete data row (base table);

The 2:server layer detects the returned data row with the while statement condition, and the accept or reject of the data row is based on the test result.

When the ICP is used, the index scanning process: (not only using index to locate the data row, but also using the index columns contained in the Where condition to filter the data, for example, according to MySQL index leftmost prefix principle, where ' xxx ' = ' Hello ' and ' yyy ' = '% xxx% ', the storage engine will only use the ' xxx ' column to locate the data, and ' yyy ' is a fuzzy match, will not be used, with no ICP, two cases Scan index is the same, but with the ICP, will be extra use '%yyy% ' match to the Federated Index ' YYY ', read and return only the full data rows satisfying ' hello ' and '%xxx% ', instead of the ICP, which reads and returns the data rows that meet the ' Hello ' column values individually.

1: The storage engine obtains the corresponding index entry (the data row of the non-base table).

2: Use the contained index columns in the Where condition to detect the corresponding index columns, and if condition is not satisfied, process the next index entry.

3: If condition is established, use the corresponding index entry to locate the complete data row of the base table.

4: Detects the data rows returned by storage engine with the remaining portion of the Where condition.

When the ICP is used, the explain extra column displays the using index condition.

ICP not used:

Use ICP:

　　　　　　　　　　　　　　　　　　

When ICP optimization is turned on, the storage engine side first uses the index to filter the where condition that can be filtered (where the Condition column contains the index column), and then uses the index to do data access, which is filtered out by index condition. It is also not returned to the server side, if there are columns in the Where Condition column that are not included in the index column, then the where judgment is made based on the data rows returned by the storage engine ICP (where the column of index columns is removed), so in this case the server The layer should be added using the where small block, ibid. see Example:

 Use Empolyees

Mysql> descemployees.employees;+------------+---------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+------------+---------------+------+-----+---------+-------+|Emp_no| int( One)|NO|Pri| NULL    |       ||Birth_date|Date|NO|     | NULL    |       ||First_Name| varchar( -)|NO|     | NULL    |       ||Last_Name| varchar( -)|NO|     | NULL    |       ||Gender|Enum'M','F')|NO|     | NULL    |       ||Hire_date|Date|NO|     | NULL    |       |+------------+---------------+------+-----+---------+-------+6Rowsinch Set(0.09Sec

MySQL>altertableaddindex0 rows affected (  3.980  0  0

Index Condition Pushdown Open case

Mysql>ExplainSelect *  fromEmployeeswhereFirst_Name='Mary'  andLast_Name like '%man';+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------------- ------+|Id|Select_type| Table     |Type|Possible_keys| Key       |Key_len|Ref|Rows|Extra|+----+-------------+-----------+------+---------------+-----------+---------+-------+------+------------------ -----+|  1 |Simple|Employees|Ref|Idx_fn_ln|Idx_fn_ln|  -      |Const|  224 |Using Index Condition |+----+-------------+-----------+------+---------------+-----------+---------+-------+------+------------------ -----+1Rowinch Set(0.00Sec

In case of shutdown:

Mysql> SetOptimizer_switch= 'Index_condition_pushdown=off';//Close index condition Pushdownquery OK,0Rows Affected (0.02sec) MySQL>ExplainSelect *  fromEmployeeswhereFirst_Name='Mary'  andLast_Name like '%man';+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+|Id|Select_type| Table     |Type|Possible_keys| Key       |Key_len|Ref|Rows|Extra|+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+|  1 |Simple|Employees|Ref|Idx_fn_ln|Idx_fn_ln|  -      |Const|  224 | Using where |+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+1Rowinch Set(0.00Sec

See where statement contains the above federated index and contains a non-indexed column:

Mysql> SetOptimizer_switch='Index_condition_pushdown=on'; Query OK,0Rows Affected (0.00sec)MySQL>ExplainSelect *  fromEmployeeswhereFirst_Name='Mary'  andLast_Name='%man'  andGender='M';+----+-------------+-----------+------+---------------+-----------+---------+-------------+------+----------- -------------------------+|Id|Select_type| Table     |Type|Possible_keys| Key       |Key_len|Ref|Rows|Extra|+----+-------------+-----------+------+---------------+-----------+---------+-------------+------+----------- -------------------------+|  1 |Simple|Employees|Ref|Idx_fn_ln|Idx_fn_ln|  the      |Const,const|    1 | Using index condition; Using where |+----+-------------+-----------+------+---------------+-----------+---------+-------------+------+------------ ------------------------+1Rowinch Set(0.01Sec

There is also a using index condition, but after the index filter, the server also according to gender column to determine the value of storage engine return;

The ICP can only be used for level two indexes and not for the primary index.

Not all the where condition can be filtered with ICP, if the field of a where condition is not in the index, of course, still want to read the entire record to do the filter, in this case, still want to go to the server side to do the where filter.

The acceleration effect of the ICP depends on the proportion of data that is filtered out of the ICP within the storage engine.

This article uses Employees database table and data here ************ download, the library function data is complete, employees_db-full-1.0.6.tar.bz2;

MySQL Index Condition pushdown (ICP) optimization