MySQL 5.6 New Features--Index Condition pushdown

Index Condition pushdown (ICP) is an optimization method for MySQL to retrieve row data from a table using an index. Before the ICP attribute is found, the storage engine finds and returns data to the MySQL Server,mysql server based on the index to the database and then filters the data based on the Where condition. With the ICP, when the index is removed, it is possible to determine if the where condition can be filtered according to the columns in the index, that is, to place a part of the filter operation on the storage engine layer. This reduces the access of the upper SQL layer to the record. ICP optimization supports queries of range, ref, EQ_REF, Ref_or_null type. The query optimizer gives you the appropriate hint: Using index condition. When the ICP is turned on, the extra column in the execution plan is displayed: Using index condition. ICP Support InnoDB, MyISAM table. For InnoDB tables, the ICP is for secondary indexes only. In 5.6, the ICP does not support partitioned tables. This problem is solved in MySQL 5.7. 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. before you have an ICP: After you open the ICP, it becomes: The default is to open the ICP, manually open/Close the ICP:

Set optimizer_switch = ' Index_condition_pushdown=off '; set optimizer_switch = ' Index_condition_pushdown=on ';

　　

Testing process

1. Environmental preparedness

#mysql 5.6.25# Close Result Cache mysql> set global query_cache_size=0; Mysql> set Query_cache_type=off; #查看表结构mysql > Show CREATE TABLE employees\g*************************** 1. Row ***************************       table:employeescreate table:create Table ' employees ' (  ' emp_no ' int (one) not NULL,  ' birth_date ' date not null,  ' first_name ' varchar (+) NOT NULL,  ' last_name ' varchar (+) NOT NULL,  ' Gender ' enum (' M ', ' F ') is not null,  ' hire_date ' date is not null,  PRIMARY KEY (' Emp_no '),  

2. Test after opening the ICP

Mysql> Set profiling = 1;mysql> SELECT * FROM Employees where first_name= ' Anneke ' and last_name like '%sig '; MYSQL&G T Explain select * FROM employees where first_name= ' Anneke ' and last_name like '%sig ';mysql> show profiles;mysql> Sho W profile cpu,block io for query 1;

3. Test after closing the ICP

Mysql> set optimizer_switch= ' Index_condition_pushdown=off ';mysql> set profiling = 1;mysql> SELECT * FROM Employees where first_name= ' Anneke ' and last_name like '%sig ';mysql> explain select * FROM Employees where first_name= ' Anneke ' and last_name like '%sig ';mysql> show profiles;mysql> show profiles Cpu,block io for query 1;

4. Comparison of results

Execution plan after opening ICP: The extra part of the execution plan is "using index condition"

Mysql> Explain select * FROM employees where first_name= ' Anneke ' and last_name like '%sig '; +----+-------------+------- ----+------+---------------------+---------------------+---------+-------+------+-----------------------+| ID | Select_type | Table     | type | Possible_keys       | key                 | key_len | ref   | rows | Extra                 |+----+-------------+-----------+------+---------------------+---------------------+---------+------- +------+-----------------------+|  1 | Simple      | employees | ref  | idx_first_last_name | idx_first_last_name | |      | const |  224 | Using index Condition |+----+-------------+-----------+------+---------------------+---------------------+------- --+-------+------+-----------------------+

Execution plan after closing ICP: the content of the extra part of the execution plan is "using where"

Mysql> Explain select * FROM employees where first_name= ' Anneke ' and last_name like '%sig '; +----+-------------+------- ----+------+---------------------+---------------------+---------+-------+------+-------------+| ID | Select_type | Table     | type | Possible_keys       | key                 | key_len | ref   | rows | Extra       |+----+-------------+-----------+------+---------------------+---------------------+---------+------- +------+-------------+|  1 | Simple      | employees | ref  | idx_first_last_name | idx_first_last_name | |      | const |  224 | Using where |+----+-------------+-----------+------+---------------------+---------------------+---------+------ -+------+-------------+

　

Profile content after opening the ICP: the value of the sending data section is 0.000212s

Mysql> Show profile Cpu,block io for query 1;+----------------------+----------+----------+------------+---------- ----+---------------+| Status | Duration | Cpu_user | Cpu_system | block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| Starting | 0.000114 |   0.000000 |            0.000000 |             0 | 0 | | Checking Permissions | 0.000007 |   0.000000 |            0.000000 |             0 | 0 | | Opening Tables | 0.000018 |   0.000000 |            0.000000 |             0 | 0 | | init | 0.000034 |   0.000000 |            0.000000 |             0 | 0 | | System Lock | 0.000008 |   0.000000 |            0.000000 |             0 | 0 | | Optimizing | 0.000023 |   0.000000 |            0.000000 |             0 | 0 | | Statistics | 0.000383 |   0.000000 |            0.000000 |             0 | 0 | | Preparing | 0.000019 |   0.000000 | 0.000000 |             0 | 0 | | Executing | 0.000002 |   0.000000 |            0.000000 |             0 | 0 | | Sending Data | 0.000212 |   0.000000 |            0.000000 |             0 | 0 | | End | 0.000004 |   0.000000 |            0.000000 |             0 | 0 | | Query End | 0.000004 |   0.000000 |            0.000000 |             0 | 0 | | Closing Tables | 0.000006 |   0.000000 |            0.000000 |             0 | 0 | | Freeing items | 0.000020 |   0.000000 |            0.000000 |             0 | 0 | | Cleaning Up | 0.000011 |   0.000000 |            0.000000 |             0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+

Profile content after the ICP is closed: The value of the sending data section is 0.010990s

Mysql> Show profile Cpu,block io for query 1;+----------------------+----------+----------+------------+---------- ----+---------------+| Status | Duration | Cpu_user | Cpu_system | block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| Starting | 0.000165 |   0.000000 |            0.000000 |             0 | 0 | | Checking Permissions | 0.000022 |   0.000000 |            0.000000 |             0 | 0 | | Opening Tables | 0.000027 |   0.000000 |            0.000000 |             0 | 0 | | init | 0.000039 |   0.000000 |            0.000000 |             0 | 0 | | System Lock | 0.000008 |   0.000000 |            0.000000 |             0 | 0 | | Optimizing | 0.000037 |   0.001000 |            0.000000 |             0 | 0 | | Statistics | 0.000483 |   0.001000 |            0.000000 |             0 | 0 | | Preparing | 0.000022 |   0.000000 | 0.000000 |             0 | 0 | | Executing | 0.000002 |   0.000000 |            0.000000 |             0 | 0 | | Sending Data | 0.010990 |   0.007999 |            0.002000 |             0 | 0 | | End | 0.000009 |   0.000000 |            0.000000 |             0 | 0 | | Query End | 0.000005 |   0.000000 |            0.000000 |             0 | 0 | | Closing Tables | 0.000008 |   0.000000 |            0.000000 |             0 | 0 | | Freeing items | 0.000028 |   0.000000 |            0.000000 |             0 | 0 | | Cleaning Up | 0.000014 |   0.000000 |            0.000000 |             0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+

　　

Other:

ICP optimization method is not supported when SQL uses overwrite index

Mysql> Explain select First_name,last_name from employees where first_name= ' Anneke ' and last_name= ' porenta '; +----+-- -----------+-----------+------+---------------------+---------------------+---------+-------------+------+----- ---------------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-----------+------+---------------------+---------------------+---------+---  ----------+------+--------------------------+| 1 | Simple | Employees | Ref | Idx_first_last_name | Idx_first_last_name | 94 |    Const,const | 1 | Using where; Using index |+----+-------------+-----------+------+---------------------+---------------------+---------+------ -------+------+--------------------------+mysql> Explain select * FROM employees where first_name= ' Anneke ' and last_ Name= ' Porenta ', +----+-------------+-----------+------+---------------------+---------------------+---------+-------------+------+-----------------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-----------+------+---------------------+---------------------+---------+------  -------+------+-----------------------+| 1 | Simple | Employees | Ref | Idx_first_last_name | Idx_first_last_name | 94 |    Const,const | 1 | Using index Condition |+----+-------------+-----------+------+---------------------+---------------------+------- --+-------------+------+-----------------------+

　　

MySQL 5.6 New Features--Index Condition pushdown