MySQL5.6 index Condition pushdown (ICP, index condition push)-using index Condition

http://blog.itpub.net/22664653/viewspace-1210844/--This blog is written in a finer, later look

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 checks the where condition of the filtered field in the index (pushed index condition, push indexing condition) when the index is accessed, and if the data in the index tuple does not meet the pushed index condition, 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. Using the ICP (prior to MySQL version 5.6) and the data access and extraction process without using the ICP are 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 wehere 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. Can only be used for level two indexes (secondary index).
2. Explain displays the type value (join type) in the execution plan as 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. The 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.

The table structure is defined as follows :

1234567891011

CREATE TABLE `person` (`person_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,`postadlcode` int(11) DEFAULT NULL,`age` tinyint(4) DEFAULT NULL,`first_name` varchar(45) NOT NULL,`last_name` varchar(45) NOT NULL,`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`person_id`),KEY `idx_p_a` (`postadlcode`,`age`),KEY `idx_f_l` (`first_name`,`last_name`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8

Turn off ICP optimization, extra information as "Using Where"

1234567

mysql> set optimizer_switch = "index_condition_pushdown=off";mysql> explain select*   from person  where postadlcode between 300000 and 400000 and age > 40;+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table| type  | possible_keys | key | key_len | ref  | rows | Extra       |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+|  1 | SIMPLE      | person | range | idx_p_a       | idx_p_a | 7       | NULL |   21 | Using where |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

After you open the ICP, the extra information is "Using Index Condition"

1234567

mysql> set optimizer_switch = "index_condition_pushdown=on";mysql> explain select*   from person  where postadlcode between 300000 and 400000 and age > 40;+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+| id | select_type | table| type  | possible_keys | key| key_len | ref  | rows | Extra                 |+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+|  1 | SIMPLE      | person | range | idx_p_a       | idx_p_a | 7       | NULL |   21 | Using index condition |+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+

MySQL5.6 index Condition pushdown (ICP, index condition push)-using index Condition