MySQL 5.6 Index Condition pushdown

ICP (Index condition pushdown) is an optimization of MySQL's use of index (level two index) tuples and sieve fields to extract data records from a table in the Where condition in the index. The idea of ICP is that the storage engine examines the where condition of the filtered field in the index when it accesses the index (pushed index condition , push the index condition), if the data in the index tuple does not meet the push index criteria, then filter out the data record . The ICP (optimizer) pushes the processing of the index condition from the server layer to the storage engine layer as much as possible. Storage engine uses indexes to filter irrelevant data, returning only data that conforms to the index condition criteria to the server tier. It is also said that data filtering is done as far as possible on the storage engine layer, rather than returning all the data to the server layer, and then filtering based on the Where condition. Use the ICP (prior to MySQL 5.6 version) and the data access and extraction process without using the ICP as follows (illustrations come in mariadb Blog)

When the optimizer does not use the ICP, the data access and extraction process is as follows:

1) When storage engine reads the next line, it reads the index tuple first, and then uses the index tuple to locate and read the entire row of data in the base table.

2) The sever layer evaluates the Where condition, if the row data satisfies the where condition is used, otherwise discarded.

3) Execute 1) until the last row of data.

when the optimizer uses the ICP, the server layer pushes the storage engine layer into a where condition that can be evaluated by using the index .

The data access and extraction process is as follows:

1) Storage engine reads the next index tuple from the index.

2) storage engine uses an index tuple to evaluate the pushed index criteria. If the Where condition is not met, storage engine will process the next index tuple (back to the previous step). Only the index tuple will continue to read the data in the base table when it satisfies the pushed index condition.

3) If the index condition is satisfied, storage engine locates the rows of the base table and reads the entire row of data through the index tuple and returns it to the server layer.

4) The server layer evaluation is not pushed down to the storage engine layer's where condition if the row data satisfies the where condition is used, otherwise discarded.

When using the ICP, if part of the Where condition can be evaluated by using the fields in the index, then MySQL server pushes this part of the Where condition to the storage engine (storage engines layer). The storage engine filters data rows that do not meet the push index criteria by indexing the indexed column data of the tuple.

The index condition pushes the meaning that the filter field in the index is pushed from the server layer to the storage engine layer so that the data can be filtered at the storage engine layer. Thus, the ICP can reduce the number of times the storage engine accesses the base table and the number of times that MySQL server accesses the storage engine.

Note the conditions of use of the ICP:
1. Only for level two indexes (secondary index).
The type value (join type) in the execution plan shown in 2.explain is range, ref, Eq_ref, or Ref_or_null. And the query needs to access the entire row of data in the table, that is, the query results (index overrides) cannot be obtained directly from the tuple data of level two indexes.
3.ICP can be used for the MyISAM and Innnodb storage engines, and the partition table is not supported (5.7 will solve this problem).

ICP opening optimization function and shutdown

The MySQL5.6 can be turned on or off by setting the Optimizer_switch ([global|session],dynamic) variable to turn on or off the Index_condition_push optimization function.

MySQL > Set optimizer_switch= ' Index_condition_pushdown=on|off '

When you view the execution plan with explain, the extra information in the execution plan is "using index condition", which represents the index condition pushdown used by the optimizer.

Prior to mysql5.6, there was no such query optimization using the ICP, where the index condition in the where query condition did not take full advantage of the index filtering data in some cases. Suppose a composite index (multicolumn index) k contains (C1,c2,..., cn) n columns, if there is a scope scan on C1, then the remaining C2,..., cn this n-1 index cannot be used to extract and filter data (whether it is a unique lookup or a range lookup). Index records are not fully utilized. That is, when a range query exists on the previous field of a composite index, the subsequent part of the index will not be used because the index data in the latter part is unordered. For example, the tuple data in index key (A, B) is (0,100), (1,50), (1,100), where the query condition is a < 2 and B = 100. Since the index data on B is not contiguous, since reading (1,50) is no longer read (1,100), the MySQL optimizer does not scan the next part of the composite index after performing an index interval scan.

Now let's take a look at an example:

Mysql> select version (); +-------------+| Version ()   

Mysql> Show CREATE TABLE rental\g*************************** 1. Row *************************** table:rentalcreate table:create Table ' rental ' (' rental_id ' int (one-by-one) not NULL AUT O_increment, ' rental_date ' datetime NOT NULL, ' inventory_id ' mediumint (8) unsigned not NULL, ' customer_id ' smallint (5) unsigned not NULL, ' Return_date ' datetime DEFAULT NULL, ' staff_id ' tinyint (3) unsigned not NULL, ' Last_update ' Timestam P not NULL DEFAULT current_timestamp on UPDATE current_timestamp, PRIMARY key (' rental_id '), UNIQUE key ' rental_date ' (' Rental_date ', ' inventory_id ', ' customer_id '), key ' idx_fk_inventory_id ' (' inventory_id '), key ' idx_fk_customer_id ' (' customer_id '), key ' idx_fk_staff_id ' (' staff_id '), CONSTRAINT ' Fk_rental_customer ' FOREIGN KEY (' customer_id ') referenc  ES ' Customer ' (' customer_id ') on UPDATE CASCADE, CONSTRAINT ' fk_rental_inventory ' FOREIGN KEY (' inventory_id ') REFERENCES ' Inventory ' (' inventory_id ') on UPDATE CASCADE, CONSTRAINT ' Fk_rental_staff ' ForeigN KEY (' staff_id ') REFERENCES ' staff ' (' staff_id ') on UPDATE CASCADE) engine=innodb auto_increment=16050 DEFAULT charset=u  Tf81 row in Set (0.03 sec) mysql>

In the case of non-use of ICP query:

Mysql> explain select * from rental where rental_date = ' 2006-02-14 15:16:03 ' and customer_id >= and customer_id <= 400;+----+-------------+--------+------+--------------------------------+-------------+---------+-------+ ------+-------------+| ID | Select_type | Table  | type | Possible_keys                  | key         | key_len | ref   | rows | Extra       |+----+-------------+--------+------+--------------------------------+-------------+---------+------- +------+-------------+|  1 | Simple      | rental | ref  | rental_date,idx_fk_customer_id | rental_date | 8       | const |  

The optimizer first uses the first field of the composite index Rental_date rental_date to filter out records that meet the criteria rental_date= ' 2006-02-14 15:16:03 ' (The key field in the execution plan shows rental_date), The final query results are then filtered according to the criteria customer_id >= and customer_id <= 400来 based on the composite index rental_date back to the table (the extra field in the execution plan is displayed as a using where

Here's how to open the ICP (MySQL 5.6 Support)

Mysql> select version (); +-----------+| Version () |+-----------+| 5.6.10    

Mysql> explain select * from rental where rental_date = ' 2006-02-14 15:16:03 ' and customer_id >= and customer_id <= 400;+----+-------------+--------+------+--------------------------------+-------------+---------+-------+ ------+-----------------------+| ID | Select_type | Table  | type | Possible_keys                  | key         | key_len | ref   | rows | Extra                 |+----+-------------+--------+------+--------------------------------+-------------+---------+------- +------+-----------------------+|  1 | Simple      | rental | ref  | rental_date,idx_fk_customer_id | rental_date | 5       | const |  

Using index condition means that MySQL uses the ICP to optimize the query and, when retrieved, pushes the filter of the condition customer_id to the storage engine layer. This can reduce unnecessary IO access.

1. First Storage engine according to condition rental_date = ' 2006-02-14 15:16:03 ' and customer_id >= and customer_id <= 400来 Filter index, filter on index Customer_ ID condition

2. Get data based on indexed filtered records

Resources

Http://olavsandstaa.blogspot.co.uk/2011/04/mysql-56-index-condition-pushdown.html

https://mariadb.com/kb/en/index-condition-pushdown/

MySQL 5.6 Index Condition pushdown